SQL最佳化(一) 索引

對於sql的執行效率而言，有兩個非常重要的因素，一個是索引，另外一個是關聯(join)。絕大多數的sql效能問題和不當的索引和關聯相關。本篇先說說索引。
一、 索引型別1. B* tree index，即普通索引
2. 點陣圖索引
3. Function based index函式索引
其他索引還包括bitmap join index，應用索引等，很少使用。下面介紹這3種最常用索引。

[@more@]

二、 B*tree index
(一) 索引結構
1. 索引結構包括branch block和leaf block（leaf nodes）。最上面的branch block稱為root block。Leaf block(葉子節點)包含index key和rowid資訊。Root block到leaf block的層次稱為索引高度(level，or height)
2. 根據索引讀一行記錄的過程是，從root block遍歷到leaf block，再根據leaf block的index key找到rowid，再讀出對應block找到相應的row。如果height是3,則需要3+1個block的io
3. 索引的所有leaf block高度相同，這說明不管索引值是什麼，遍歷索引的開銷是一致的。大多數表的索引高度是2-3層，檢索的開銷是2-3個block的io。這說明對於各種不同量級的表，b*tree的效率都是很高的。下面是**系統中幾張不同數量級的表的索引高度
Table_name index_name 記錄數 BLEVEL
Rate_discount RATE_DISCOUNT_XDISC_ID 100961 2
PRODUCT PRODUCT_PK 37693660 2
CDR_BILLED_2012_06_01 CDR_BILLED_PK 146877600 3(二) b*tree index的適用/不適用場合
1. 當訪問一個表的少部分記錄時應該用B*tree索引。前面說過索引檢索2-3 block的io，然後根據rowid讀取表中的記錄。這種情況下比全表掃描效率高很多。
'少部分'能否確切定義？不能。一個可參考的經驗值是：
對於thin table，即每行位元組數較少的表，2-3%
對於fat table，即每行位元組數較多的表，20%以內
2. 如果表記錄數很少，使用索引效率反而低。例如，只有幾十條記錄，所有資料在一個block內。則全表掃描只需1個block的io，而索引讀可能需要幾個block
3. 如果訪問一個大表的較大部分記錄，使用索引效率反而低。
4. 對於第3點，例外情況是如果索引鍵值已經包含了查詢的要求。如index on t(a,b)
Select count(*) from t;
Select a from t;
這種情況下，索引可以看作是'瘦身'的table，oracle會使用index full scan代替table full scan，畢竟索引比table小。
(三) 調優例子：不走索引反而效能提高！
update product set no_bill=1 where parent_account_no = 48003823 and parent_subscr_no …;
由於parent_account_no上有索引，因此oracle會選擇index range scan。但由於這個account_no的記錄數約4000萬，整個product表大約9500萬，sql執行超過4小時還出不來。
update product set no_bill=1 where parent_account_no+0 = 48003823 and parent_subscr_no …;
強制不走parent_account_no上的索引，執行速度反而快了，時間小於2小時。
三、 點陣圖索引
(一) 索引結構
1. 和普通索引相比，點陣圖索引只有少量index entry
2. 每個index entry指向很多行，用一個bit表示表一行，0表示不匹配，1表示匹配
(二) 點陣圖索引適用場合
1. 欄位值low distinct cardinality ，即唯一值相對於總行數的比例低，例如一個欄位只有T/F兩個值。
2. 適合ad hoc query（資料倉儲領域有一個概念叫Ad hoc queries，中文一般翻譯為"即席查詢"。即席查詢是指那些使用者在使用系統時，根據自己當時的需求定義的查詢）。
這類查詢在OLAP或報表系統中是很常見的，where欄位有各種組合，如
select *from t
where ( ( gender = 'M' and location = 20 )
or ( gender = 'F' or location = 22 ))
and age_group = '18 and under';
select count(*) from t where age_group = '41 and over' and gender = 'F';
如果是普通索引需要建多種組合的複合索引以便不同查詢使用，索引空間會很大。而點陣圖索引多個索引可以很方便地進行AND/OR操作，只需在欄位上各建一個點陣圖索引即可

以下測試顯示，bitmap index兩個索引能進行AND操作，而普通索引則不會
使用普通索引只用到一個索引
create table test_table as select owner,object_type,object_name from dba_objects;
create index test_idx1 on test_table(owner);
create index test_idx2 on test_table(object_name);
select count(*) from test_table where owner='HSS' AND object_name='TEST_TABLE';
Execution Plan
----------------------------------------------------------
Plan hash value: 3928831041

-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 29 | 4 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 29 | | |
|* 2 | TABLE ACCESS BY INDEX ROWID| TEST_TABLE | 1 | 29 | 4 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | TEST_IDX2 | 2 | | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
使用點陣圖索引兩個索引的檢索結果能進行AND操作
drop index test_idx1;
drop index test_idx2;
create bitmap index test_idx1 on test_table(owner);
create bitmap index test_idx2 on test_table(object_type);
select count(*) from test_table where owner='HSS' AND object_type='TEST_TABLE';
Execution Plan
----------------------------------------------------------
Plan hash value: 1409243622

------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 2 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
| 2 | BITMAP CONVERSION COUNT | | 55 | 715 | 2 (0)| 00:00:01 |
| 3 | BITMAP AND | | | | | |
|* 4 | BITMAP INDEX SINGLE VALUE| TEST_IDX2 | | | | |
|* 5 | BITMAP INDEX SINGLE VALUE| TEST_IDX1 | | | | |

(三) 什麼時候不宜用bitmap index
點陣圖索引適用在大量讀的場合，但不適合大量寫的環境，特別是併發寫的環境。因為當一個index entry被修改時，這個index entry指向的所有行都會被鎖，oracle無法鎖住單獨的bit，而是鎖住整個bitmap index entry。因此一個update可能導致幾百行被鎖。因此點陣圖索引在OLAP系統中較常見，而OLTP系統中幾乎不用。
四、 函式索引
(一) 函式索引有以下特點:
1. 函式索引在index entry中儲存的是函式的計算結果,固化函式計算結果，提升效能
select ename, hiredate
from emp
where my_soundex(ename) = my_soundex('Kings')
如果沒有函式索引，假如有n行，會呼叫my_soundex函式n次。如果有，則只需1次
2. 使用方便，不需要改寫現有表結構和程式
3. 效能上，對insert/update會有些負面影響，但對查詢效能提高很多，需要進行權衡。通常來說insert一條記錄只要1次，但查詢可能會進行很多次，因而是值得建索引的。
4. 能在某些行上面建索引而忽略其他行，以節省空間。某些情景下可代替點陣圖索引，比點陣圖索引有更好的併發行，而且空間也小(二) 應用例子
1. 函式索引僅在某些行上建索引的例子。
場景：假設1個表的欄位process_flag只有兩個值，N表示新記錄未處理，處理後變為Y。大多數記錄為Y。主要操作是查詢process_flag='N'的記錄進行處理，然後將process_flag值改為'Y'
如果使用B*tree索引，索引空間大，BLEVEL高。如果使用點陣圖索引,併發修改效能又差。這時可使用函式索引（只在值為N的記錄上）：
create index processed_flag_idx
on big_table( case temporary when 'N' then 'N' end );
2. 用函式索引在某些值上實現完整性約束的例子
場景：project表（name,status）。對於status='ACTIVE'的project,name必須唯一。但status='INACTIVE'則可以有多條重複記錄。
Create unique index active_projects_must_be_unique
On projects ( case when status = 'ACTIVE' then name end );

五、 未能走索引的幾種情況

以下是使用索引不當所引起的不走索引的幾種常見情況：
1. Index on t(x,y)但where 條件中只有y欄位。通常情況會進行全表掃描。
2. select count(*) from t通常由於索引比table小，oracle會進行index full scan。但如果索引欄位含有NULL值，則不會走索引，因為索引值不包含null，如果進行index full scan統計值就不準確了。
3. select * from t where f(index_column)=value 如果不是函式索引，where條件在索引欄位上進行函式操作則不走索引
4. select * from t where indexed_column=5 欄位型別需轉換。例如indexed_column是字元但where條件中用了數字
5. oracle最佳化器認為全表掃描比走索引效率更高。這種情況下oracle選擇全表掃描。如果開發人員覺得有必要走索引，可以使用hint強制走索引
6. 未及時對錶進行analyze，statistics不準確。例如原先是小表，後來資料量大增。由於statistics仍是舊的，oracle最佳化器會選擇不走索引