復(fù)合索引使用的先決條件

PS：懶得重新編輯圖片了，直接把我從51上的日志拷過來了。

背景：

今天，接到一個(gè)項(xiàng)目的項(xiàng)目經(jīng)理電話，告之說生產(chǎn)環(huán)境有幾個(gè)查詢超級(jí)慢，就是查詢單張表的數(shù)據(jù)，查詢條件也很簡(jiǎn)單，但是加了索引以后并沒有走索引，依然還是走的全表掃描。
聽到該問題描述，我開始浮想聯(lián)翩，統(tǒng)計(jì)信息太舊？存在隱式轉(zhuǎn)換？索引樹傾斜度太高，導(dǎo)致oracle認(rèn)為走索引的成本更高？
帶著各種可能的原因猜想，火速趕到了現(xiàn)場(chǎng)，發(fā)現(xiàn)原來都是我想多了。不走索引單純是建立的索引不合理，查詢條件是多個(gè)字段，應(yīng)該建立復(fù)合索引，現(xiàn)場(chǎng)維護(hù)人員只對(duì)其中單個(gè)字段建立了索引，ORACLE認(rèn)為不如走全表掃描開銷小，所以沒走索引。
汗~~~~~~
添加索引的時(shí)候發(fā)現(xiàn)，幾個(gè)不同的查詢，查詢條件字段都一樣，但是寫的順序卻不一樣（開發(fā)寫SQL太隨意了?。。。∽侄我粯?，順序也寫成一樣啊！?。。。。。。Y(jié)果先劇透一下，同樣也是可以走索引的。而由此聯(lián)想到些問題，于是在解決完效率問題后，在個(gè)人環(huán)境上做了一個(gè)驗(yàn)證。
這個(gè)就是完整的背景。

---------------------------------------------------
驗(yàn)證內(nèi)容：
ORACLE 11GR2 復(fù)合索引的使用條件。
前提條件：
創(chuàng)建一張表，并對(duì)字段A、B建立組合索引，順序?yàn)閕ndex（A、B）；
測(cè)試場(chǎng)景：
針對(duì)以下6個(gè)場(chǎng)景進(jìn)行測(cè)試：
1、查詢條件為：A='XXX' and B='YYY';  順序完全一致的情況；
2、查詢條件為：B='YYY' and A='XXX';  順序不一致的情況；
3、查詢條件為：A='XXX';              單個(gè)字段且為復(fù)合索引前導(dǎo)列的情況；
4、查詢條件為：B='YYY';              單個(gè)字段且不是復(fù)合索引前導(dǎo)列的情況；
5、查詢條件為：C='ZZZ' and A='XXX';  查詢條件既包含其它字段，也包含復(fù)合索引前導(dǎo)列的情況；
6、查詢條件為：C='ZZZ' and B='YYY';  查詢條件既包含其它字段，也包含復(fù)合索引非前導(dǎo)列字段的情況；
7、查詢條件為：C='ZZZ' and A='XXX' and B='YYY';  查詢條件除復(fù)核索引字段外還包括其它字段,且索引外字段在第一位；

---------------------------------------------------
執(zhí)行過程：

創(chuàng)建測(cè)試數(shù)據(jù)：
create table test_index_demo(recid  RAW(16) not null,customer_id RAW(16) not null,product_id  RAW(16) not null)
create unique index index_test on test_index_demo (CUSTOMER_ID, PRODUCT_ID)
根據(jù)查詢字段做笛卡爾積準(zhǔn)備了將近3000萬數(shù)據(jù)；
exec dbms_stats.gather_index_stats(ownname => 'gboss',indname =>'test_index_demo');

分別針對(duì)每個(gè)場(chǎng)景進(jìn)行測(cè)試，查看執(zhí)行計(jì)劃如下：
場(chǎng)景一：
select * from TEST_INDEX_DEMO T WHERE T.CUSTOMER_ID=HEXTORAW('01540EAA1D8E690099261D41257815D9') AND T.PRODUCT_ID=HEXTORAW('01540EAB83EC6900E30EAD424661D792');

此處是走索引的，符合預(yù)期；

場(chǎng)景二：
select * from TEST_INDEX_DEMO T WHERE T.PRODUCT_ID=HEXTORAW('01540EAB83EC6900E30EAD424661D792') AND T.CUSTOMER_ID=HEXTORAW('01540EAA1D8E690099261D41257815D9');

此處可以看到ORACLE的優(yōu)化器實(shí)際上把查詢條件的順序進(jìn)行了調(diào)整，所以同樣走了索引，符合預(yù)期；

場(chǎng)景三：
select * from TEST_INDEX_DEMO T WHERE T.CUSTOMER_ID=HEXTORAW('01540EAA1D8E690099261D41257815D9');

此處因?yàn)椴樵儣l件為復(fù)合索引的前導(dǎo)列，所以走了索引，符合預(yù)期；

場(chǎng)景四：
select * from TEST_INDEX_DEMO T WHERE T.PRODUCT_ID=HEXTORAW('01540EAB83EC6900E30EAD424661D792');

此處沒有走索引，走的全表掃描，我原本以為即便不是復(fù)合索引的前導(dǎo)列，也會(huì)走索引的，看來我原來的認(rèn)識(shí)是錯(cuò)誤的。
針 對(duì)這個(gè)我特意臨時(shí)添加了index（B、A）驗(yàn)證了一下，是因?yàn)椴樵儣l件字段不是復(fù)合索引前導(dǎo)列導(dǎo)致，還是因?yàn)槌鲇谄渌矫娴某杀究紤]，測(cè)試發(fā)現(xiàn)，添加了 index（B、A）順序的索引后，該SQL就可以走索引了，所以應(yīng)該可以認(rèn)為是查詢條件字段不是復(fù)合索引前導(dǎo)列導(dǎo)致的。

場(chǎng)景五：
select * from TEST_INDEX_DEMO T WHERE
T.RECID=HEXTORAW('01540ED5DA7069465FB7E42D07EDC156') AND T.CUSTOMER_ID=HEXTORAW('01540EAA1D8E690099261D41257815D9');

ORACLE優(yōu)化器并沒有調(diào)整查詢條件字段的順序，但是依然走了索引。走索引是符合預(yù)期的，但是我原本以后應(yīng)該會(huì)改變字段順序的，這塊我有點(diǎn)不太理解，如果有看到這篇日志的大牛請(qǐng)幫忙解惑一下，謝謝。

場(chǎng)景六：
select * from TEST_INDEX_DEMO T WHERE
T.RECID=HEXTORAW('01540ED5DA7A69461D878CAE1CED2B7E') AND T.PRODUCT_ID=HEXTORAW('01540EAB83EC6900E30EAD424661D792');

此處沒有走索引，倒是符合預(yù)期，汗~~~~

場(chǎng)景七：
select * from TEST_INDEX_DEMO T WHERE
T.RECID=HEXTORAW('01540ED5DA7A69461D878CAE1CED2B7E') AND T.CUSTOMER_ID=HEXTORAW('01540EAA1D8E690099261D41257815D9') AND T.PRODUCT_ID=HEXTORAW('01540EAB83EC6900E30EAD424661D792');

此處走索引了，結(jié)果倒是符合預(yù)期。

----------------------------------------------------
結(jié)果對(duì)比及結(jié)論：

所以經(jīng)此對(duì)比，我的結(jié)論是：
1、查詢條件字段與復(fù)合索引字段一致的，無論順序如何，ORACLE優(yōu)化器會(huì)自動(dòng)調(diào)整順序，結(jié)論是會(huì)走索引；
2、查詢條件字段與復(fù)合索引字段不一致，查詢條件字段包含復(fù)合索引前導(dǎo)列的，可以走索引；不包含索引前導(dǎo)列，則不走索引；
--------------------------
所以，由此結(jié)論可以看出，在設(shè)計(jì)查詢的時(shí)候，還是應(yīng)該要求開發(fā)在組織SQL的時(shí)候?qū)τ诘谝粋€(gè)查詢條件該用哪個(gè)字段還是需要綜合考慮系統(tǒng)所有查詢來進(jìn)行設(shè)計(jì)一下的。