效能優化部分——高階SQL優化2

sufeizi發表於2013-12-18

優化SQL

Oracle資料完整性和鎖機制

索引及優化之表分析

表分析、約束及表間關係

Oracle體系結構1

Oracle體系結構2

海量資料庫及分割槽1

海量資料庫及分割槽2

海量資料庫及分割槽3

海量資料庫及分割槽4

高階SQL優化(一)

高階SQL優化(二)

高階SQL優化(三) 常用優化工具

PPT和原始碼下載： http://sishuok.com/forum/posts/list/6365.html

配套視訊課程

Oracle效能優化 http://sishuok.com/product/601

海量資料庫和高階SQL優化 http://sishuok.com/product/602

充分利用索引

索引的限制

1. 索引對不等號和NOT的限制

如果WHERE條件中出現!=或者<>，即使該列建立了索引，則該索引也不會被使用；如果不恰當的使用了NOT，則索引也不會被使用。

Oracle 10g起，在基於CBO的優化器模式下Oralce會進行自動優化，但在基於RBO（基於規則）的優化器模式下，依然保持此規則。

1. 索引對不等號和NOT的限制

RBO模式下，執行計劃如下：

1. 索引對不等號和NOT的限制

RBO模式下，執行情況如下：

此時使用變通寫法的耗費為：0.407/2.187=1.60%!

1. 索引對不等號和NOT的限制

CBO模式下，執行情況如下：

此時使用變通寫法的耗費節約不到0.03秒，但依然更優，故此推薦此種變通寫法，再看此時使用NOT：

1. 索引對不等號和NOT的限制

CBO模式下，在JYJE列的索引上使用NOT：

為使用<>的：0.156/0.329=47.42%!為變通寫法的使用0.156/0.297=52.53%！因 此這種寫法最優！

1. 索引對不等號和NOT的限制

一般，WHERE條件中，如果索引列是字元列，使用NOT往往也不會使用索引：

結論：如果索引列是數字，則對於不等號的處理可以變更為NOT的方式或者(大於 OR 小於)的方式① ；對於確實無法不使用不等號的方式，可以使用預設值② ；如果可以建立點陣圖索引則使用點陣圖索引③ ；否則可以考慮使用分割槽等方法進行優化④ ，具體是情況而定。

2. 索引對IS NULL的限制

一般來說，如果WHERE子句基於的列是可空的列，且其建立了索引，如果使用了IS NULL，由於NULL的列本身不包含在索引中，因此無法利用索引。

所以一般對要建立索引的列不要設定為可空，如果確實含有空值，建議使用預設值代替空值，具體參見前面章節“SQL優化技巧”部分的“使用預設值”。

3. 索引對函式的限制

基於索引IDX_BIGTAB_OBJECTNAME，執行情況如下：

執行計劃情況如下：

這是因為該索引是常規b-tree索引，對該列在WHERE子句中使用了函式，則不能使用索引。因此，對在WHERE子句中經常要使用函式時，應該建立基於函式的索引，且 只有當查詢語句包含該函式或者表示式時，基於函式的索引才會被調 用。詳情請參見索引部分的理論講解！

建立並使用函式索引：

建立並使用函式索引：

此時使用基於函式的索引效率是原來的2.782/0.188＝14.78！唄！

4. 索引對不匹配資料型別的限制

先看執行情況：

不匹配的型別執行的時間是匹配的型別的 2.187/0.266=8.2 倍！

再看執行計劃：

原因分析：

因為ACCOUNT_TRADE表的欄位YKKH是CHAR，因此在對其指定的值是數字時，Oracle雖然能隱式的執行數字和字元的轉換，但不會呼叫其索引。而當對其指定是字元時，則不存在此問題，索引可以呼叫。

注意：因為資料型別的不匹配和Oracle對資料型別的隱式轉換，此種型別的低效程式碼在任何專案中均可能因為大意而存在，因此建議開發人員和管理人員要定期抽查相應的程式碼，以杜絕此類低效程式碼！

索引型別總結

型別	，描述
b-tree索引	最常最多使用的索引，其樹結構與二叉樹比較類似，根據ROWID快速定位所訪問的行
bitmap索引	使用點陣圖來管理與資料行的對應關係，適用於基數比較少的列
降序索引	降序索引在葉子節點中的儲存從左到右是按照從大到小排序的；一般是針對逆向排序較多的查詢時才使用該型別索引
函式索引	針對要頻繁對列使用函式的索引，只有當查詢語句包含該函式或者表示式時，基於函式的索引才會被呼叫
反轉索引	反轉了b*tree索引碼中的位元組，使索引條目分配更均勻，多用於並行伺服器環境下，用於減少索引的競爭
分割槽索引	分割槽表的索引，又包括本地分割槽索引(本地字首分割槽索引和本地非字首分割槽索引)和全域性索引，一般建議使用本地分割槽索引，因其與基表具有良好的資料均衡性和可維護性

訪問路徑

1. 全表掃描

全表掃描（FULL TABLE SCANS）時所有行、所有資料塊均會被讀到，是 效率最 低的一種，一般會在表 缺少索引、 讀取大量資料、 訪問小表或 高併發時發生。

2. ROWID掃描

ROWID掃描（ROWID SCANS）是通過ROWID中資料檔案和塊位置訪問資料行。一般作為訪問索引後的第二步，如果訪問的列全部包含在索引中，則不會執行ROWID掃描。

作為索引訪問後的第二步：

訪問的列全部在索引中不再執行ROWID掃描

3. 索引掃描

索引掃描(INDEX SCANS)包含全索引掃描（full index scan、FIS）、快速全索引掃描(fast full index scan、FFIS)、索引範圍掃描(index range scan)、索引唯一掃描 (index unique scan)、索引跳躍式掃描 (index skip scan)、點陣圖索引掃描(bitmap index scan)， 其中前5種在本系列課程的索引章 節部分已經講解了其理論和示例。點陣圖索引示例如下：

3. 索引掃描

型別	方式	發生條件
1.FULL INDEX SCANS	逐一讀取索引中的所有塊，由於索引中資料已按索引鍵排序，因此會忽略掉排序	1.ORDER BY中的列全部在該索引中時 2.ORDER BY中列的順序滿足索引中前導列的順序時 3.使用GROUP BY且該子句中的列在索引中時
2.FAST FULL INDEX SCANS	只掃描索引中的資料，不會掃描表中的資料；由於索引中資料未按索引鍵排序，因此不能忽略掉排序	當同時滿足下列條件是，Oracle用FFIS替代FIS: 1.查詢的所有列均包含在索引中 2.索引中的列至少一個具有not null約束
3.INDEX RANGE SCANS	訪問選擇性資料最常用的掃描方式；按順序的對某個索引進行掃描，返回資料是升序排列的，可以使用唯一索引和非唯一索引;如果對索引列使用ORDER BY/GROUP BY則可省略排序	1.在唯一索引上使用範圍操作符(>、=、<=、<>、BETWEEN) 2.在組合索引上使用部分列進行查詢，導致查出多行
4.INDEX UNIQUE SCANS	掃描唯一索引或主鍵，要麼返回一行資料要麼返回0行資料	1.當使用唯一索引時 2.當使用主鍵時
5.INDEX SKIP SCANS	其實質是將索引分解成多個小的子索引來提高效率，系從9i開始引入	複合索引中前導列的取值是列舉的從而可以分拆為多個子索引,並且查詢條件中不含前導列時

為了 加深鞏固前面的知識，本處對前五種索引掃描複習總結如下：

(1).全索引掃描

逐一讀取索引中的所有塊，由於索引中資料已按索引鍵排序，因此會忽略掉排序，可能發生的情況如下：

A. ORDER BY中的列全部在某個索引中

全部在某個索引中：

(1).全索引掃描

B. ORDER BY中列的順序滿足索引中前導列的順序時

下面分別是滿足和不滿足前導列順序時：

C. 使用GROUP BY且該子句中的列在索引中時

(2).快速全索引掃描

只掃描索引中的資料，不會掃描表中的資料；由於索引中資料未按索引鍵排序，因此不能忽略掉排序。當同時滿足下列條件時，Oracle用FFIS替代FIS或FTS:

1.查詢的所有列均包含在索引中

2.索引中的列至少一個具有not null約束（10g開始的，原低版本的系統中為查詢的列中不包含任何null值）

全部列均在索引中：

有列不在索引中：

刪除該索引，建立新索引，兩個列均為可空：

此時即使全部列在該索引中， 也不會發生FFIS

索引範圍掃描是訪問選擇性資料最常用的掃描方式；按順序的對某個索引進行掃描，返回資料是升序排列的，可以使用唯一索引和非唯一索引;如果對索引列使用ORDER BY/GROUP BY則可省略排序。

下列情形中會發生索引範圍掃描：

A.在唯一索引上使用範圍操作符(>、=、<=、<>、BETWEEN)

B.在組合索引上使用部分列進行查詢，導致查出多行

示例請參考本系列課程的索引章節部分

(4).索引唯一掃描

當使用主鍵或唯一索引時發生。 示例請參考本系列課程的索引章節部分。

(5).索引跳躍掃描

複合索引中前導列的取值是列舉的從而可以分拆為多個子索引,並且查詢條件中不含前導列時。示例如下：

create table customers as select * from sh.customers;

CREATE INDEX customers_gender_email

ON customers (cust_gender, cust_email);

(5).索引跳躍掃描

沒進行表分析前：

進行表分後：

analyze table customers compute statistics;

何時需要索引

一般地，對於從表的總行中的大部分查詢只查詢不到10%資料（有的稱為2%-4%）的表，可以考慮建立索引。一般考慮的索引的原則包括：

l對於經常以查詢關鍵字為基礎的表，並且該表中的資料行是均勻分佈的

l以查詢關鍵字為基礎，表中的資料行隨機排序

l表中包含的列數相對比較少（僅僅是相對，需要根據實際情況確定)

l表中的大多數查詢都包含相對簡單的WHERE子句

l表的記錄數比較少的，不建議使用索引，如資料不超過1萬行的表不要建立索

為索引選擇列和表示式

一般遵循的原則包括：

l經常在WHERE子句中使用的列

lSQL語句中經常用於表之間連線的列

l重複性少（可選擇性高）的關鍵字，如主鍵

l不宜將經常UPDATE的列作為索引列

l不宜將經常在WHERE子句中使用，但與函式或操作符相結合的列作為索引列

l對於取值較少的列，應考慮建立點陣圖索引，而不應該採用B樹索引

l如果經常訪問的列上要使用函式，應使用基於函式的索引

本處舉例說明取值較少的列使用bitmap索引和b-tree的對比分析，B-tree時：

bitmap時：

使用複合索引

多個列聯合起來組成的索引稱為複合索引、或聯合索引或者組合索引，往往聯合索引比單個索引具有更好的效能。建立聯合索引一般遵循的原則包括：

l經常在WHERE子句中使用的列且這些列之間使用AND連線

l查詢條件可能包括n個列的AND關係，而大多數情況下使用m個列是(n>m)，應該考慮複合索引，且n個列為前導列

l某幾個列聯合起來能夠組成唯一索引，應堅決建立聯合唯一索引

l複合索引中，建議至少一個不能為null，且如果可能儘量將只是存在null的列對其null值採用其它預設值代替

本處舉例說明Where中包含AND時使用多個索引效能低於聯合索引的示例，使用多個索引時：

本處舉例說明Where中包含AND是使用多個索引效能低於聯合索引的示例，使用複合索引時：

結論：

專案	多個索引	複合索引	複合索引是多個索引的
一、執行時間	0.281	0.11	39.15%
二、執行計劃
1.總耗費	1658	464	27.99%
2.I/O耗費	1562	462	29.58%
3.時間	19	6	31.58%