使用索引的注意事項及常見場景、案例

      索引的原理與作用，各種書籍和網絡上的介紹可以說是鋪天蓋地，基本上主流資料庫系統的也都是一緻的。選擇索引字段的原則，比如外鍵字段、資料類型較小的字段、經常用于查詢或排序的字段、表關聯的字段等等，在此不做贅述。本人在工作中見到過很多人建立的索引，回想自己以前也會有理論知識空洞的體會，總感覺理論知識無法與具體的工作問題相比對。在此僅以工作學習中積累的一點經驗和問題場景整理以飨讀者。先把常見的注意事項整理如下：

索引應該建在選擇性高的字段上（鍵值唯一的記錄數/總記錄條數），選擇性越高索引的效果越好、價值越大，唯一索引的選擇性最高； 組合索引中字段的順序，選擇性越高的字段排在最前面； where條件中包含兩個選擇性高的字段時，可以考慮分别建立索引，引擎會同時使用兩個索引（在OR條件下，應該說必須分開建索引）； 不要重複建立彼此有包含關系的索引，如index1(a,b,c) 、index2(a,b)、index3(a)； 組合索引的字段不要過多，如果超過4個字段，一般需要考慮拆分成多個單列索引或更為簡單的組合索引；

      最後需要提醒的是，不要濫用索引。因為過多的索引不僅僅會增加實體存儲的開銷，對于插入、删除、更新操作也會增加處理上的開銷，而且會增加優化器在選擇索引時的計算代價。

是以太多的索引與不充分、不正确的索引對性能都是毫無益處的。一言以蔽之，索引的建立必須慎重，對每個索引的必要性都應該經過仔細分析，要有建立的依據。

舉下面一個場景的例子，建立這樣的索引是有效的嗎？

再比如，該表最常使用的SQL場景有以下兩種類型，應該如何建立索引？

      以執行計劃和邏輯IO的統計資料顯示，兩個場景的測試結果都是後者索引有明顯的效果，大家有興趣可以自己測試驗證一下。當然，生産環境遠比這些要複雜，各表的資料量及資料分布情況也會影響引擎的執行方式，引擎對索引選擇與要求也會不一樣，此處僅以簡單語句做示例進行說明。

組合索引查詢的各種場景：

組合索引 Index (A, B, C)

下面條件可以用上該組合索引查詢：

A>5

A=5 AND B>6

A=5 AND B=6 AND C=7

A=5 AND B=6 AND C IN (2, 3)

下面條件将不能用上組合索引查詢：

B>5                                           ——查詢條件不包含組合索引首列字段

B=6 AND C=7                            ——理由同上

下面條件将能用上部分組合索引查詢：

A>5 AND B=2                            ——當範圍查詢使用第一列，查詢條件僅僅能使用第一列

A=5 AND B>6 AND C=2             ——範圍查詢使用第二列，查詢條件僅僅能使用前二列

A=5 AND B IN (2, 3) AND C=2   ——理由同上

組合索引排序的各種場景：

組合索引 Index(A, B)

下面條件可以用上組合索引排序：

ORDER BY A                   ——首列排序

A=5 ORDER BY B            ——第一列過濾後第二列排序

ORDER BY A DESC, B DESC      ——注意，此時兩列以相同順序排序

A>5 ORDER BY A            ——資料檢索和排序都在第一列

下面條件不能用上組合索引排序：

ORDER BY B                   ——排序在索引的第二列

A>5 ORDER BY B            ——範圍查詢在第一列，排序在第二列

A IN(1,2) ORDER BY B    ——理由同上

ORDER BY A ASC, B DESC       ——注意，此時兩列以不同順序排序

索引合并的簡單說明：

資料庫能同時使用多個索引

SELECT * FROM TB WHERE A=5 AND B=6

能分别使用索引(A) 和 (B)；

對于這個語句來說，建立組合索引(A,B) 更好；

最終是采用組合索引，還是兩個單列索引？主要取決于應用系統中是否存在這類語句：SELECT * FROM TB WHERE B=6

SELECT * FROM TB WHERE A=5 OR B=6

組合索引(A, B)不能用于此查詢（目前的資料庫也很智能，部分OR條件也能夠使用組合索引，但效果不是很穩定）；

很明顯，分别建立索引(A) 和 (B)會更好；

删除無效的備援索引

TB表有兩個索引(A, B) 和 (A)，對應兩種SQL語句：SELECT * FROM TB WHERE A=5 AND B=6 和 SELECT * FROM TB WHERE A=5

執行時，并不是WHERE A=5 就用 (A)； WHERE A=5 AND B=6  就用 (A, B)；

其查詢優化器會使用其中一個以前常用索引，要麼都用(A, B)， 要麼都用 (A)。

是以應該删除索引(A)，它已經被(A, B)包含了，沒有任何存在的必要。

附1，查詢指定表的資料量與索引定義情況：

附2，借助性能視圖，查詢資料表的SQL通路方式

附3，索引重建示例

補充：

      Where條件中Or的兩組條件如果分别落在兩個資料表上，即使對應的字段都已建立索引，引擎也是無法使用索引的。如下SQL，此語句實際上僅傳回一條資料，但對于TRFKZL和TRHBZL來說，Oracle、SqlServer都是進行全表掃描。

按照建議更改SQL寫法，走索引查找，響應時間在1秒以内。當然，從原始語句的篩選條件也能夠感覺到怪怪的，根本上來講應該是個設計問題。