在資料庫日常優化中，索引是經常使用的手段之一。本文由db2資料庫專家劉自傳就圍繞索引從db2索引的掃描方式、db2在索引列上應用謂詞、在db2中如何設計索引、在db2中如何做索引的深度清理展開一些讨論、學習，更進一步探索索引的奧妙。
專家簡介
劉自傳
擁有10年it從業經驗，db2資料庫專家；具有多年的sql開發、c語言開發、db2運維及診斷優化經驗；獲ibmdb2 v10.1 for luw的初、中、進階的dba認證；擅長db2的sql優化、故障診斷及性能調優；db2china.net社群專家，并組織線上db2資料庫性能優化技術交流活動；具有多年醫療、金融行業、移動營運商（廣東、西藏）運維經驗。
1
db2索引的掃描方式
在db2，索引的掃描方式有多種形式，從db2執行計劃中可以看到有以下的形式：
1、ixscan–fetch (先索引掃描，再根據rid讀資料行)
2、index–only（索引掃描，隻索引讀即可滿足整個sql）
3、list prefetch（先索引掃描，再把rid排序，最後根據排序後rid讀資料行）
4、indexanding（也叫ixand，對兩或多個索引分别進行掃描，這些索引一般是單列索引，再把符合條件rid進行and運算，最後根據and運算後的rid讀取資料行）
5、indexoring（也叫ixor，對兩或多個索引分别進行掃描，這些索引一般是單列索引，再把符合條件rid進行or運算，最後根據or運算後的rid讀取資料行）
如下圖的執行計劃樹中，分别有list prefetch、ixand、ixor掃描方式：
2
db2在索引列上應用謂詞
db2索引管理器在對索引進行掃描時，在索引列上應用謂詞時涉及到索引列的start key/stop key，有以下3種情況：
（以上效率比較，是在這3種方式之間的比較，并不是與表掃描方式比較）
結合上圖，其中a類型是屬于range-delimiting謂詞；b、c類型是屬于index-sargable謂詞。
在分析執行計劃時，可以識别出索引掃描時使用的是哪種謂詞掃描方式，如下圖可以看出是使用a類型：range-delimiting謂詞方式：
3
在db2資料庫中如何設計索引？
（一）、索引設計大原則：
1.為最重要、最頻繁的查詢/業務優先建索引；
2.為表的主、外鍵建立索引（注意主、外鍵的資料類型及長度最好一緻，避免資料類型的轉換）;
3.索引的總數量、每個索引的字段數量要适中，一定不能太多，否則會增加io、記憶體、cpu的額外開銷，增加udi、編譯及準備、實用工具、備份及恢複的工作負載。（如：oltp建議在5個以内，olap在10個以内）;
4.可結合sql執行計劃、經驗、db2advis等來分析：sql是否使用了已有索引、是否需要建立新索引、是否基于現有索引重設計索引;
5.根據不同的情況選擇不同的索引類型：普通索引、唯一索引、集聚索引、包含索引；單鍵索引、組合索引；mdc索引等;
6.若業務有特殊性，可考慮讓索引支援雙向掃描;
7.根據表的uid工作負載，選擇适當pctfree/minpctused，避免索引頁的浪費、不合理的索引頁合并;
8.必要時可考慮建立完全索引掃描、包含索引來避免資料頁讀；
9.若評估啟用索引壓縮能有效節省索引大小，則啟用索引壓縮（注意不建議在cpu密集型環境中啟用）；
10.把索引表空間與資料表空間、大對象表空間分開放置在不同的實體磁盤，避免io争用；
11.對于分區表，可根據實際情況，選擇建立全局索引、分區索引，建議使用分區索引；
12.若時間、工作負載允許，可考慮在建索引時收集統計資訊、采樣收集統計資訊，加快收集統計資訊速度；
13.避免在小表上建議索引；
14.避免在大對象、長列（lb/lf）列上建立索引；
15.避免建立不使用的索引（建議考慮定期的索引清理，後面會提到索引的深度清理）；
16.特殊情況下，考慮建多個相關單列索引，讓db2優化器使用index-anding/index-oring索引操作。
（二）、避免建立備援索引
備援索引是指一個索引字段是另一個索引字段的前導部分，如存在兩個索引，分别是：（+a+b+c）、（+a+b），則索引（+a+b）是索引（+a+b+c）的備援索引。相比之下，db2優化器一般不會優先考慮備援索引。
知道了什麼是備援索引，那麼我們就可以有效地避免備援索引、識别備援索引。
要識别備援索引，通過查詢系統視圖：syscat.indexes.colnames/syscat.indexcoluse，檢查系統中是否已經存在了備援索引，根據索引的使用情況考慮做索引的清理，以節省額外的磁盤、io、記憶體、cpu的開銷及相關udi的維護成本。
（三）、複合索引的設計細節建議：
在對複合索引設計有一個three-star原則，就是選擇哪些字段、安排字段的順序：
1-start: where謂詞清單放最前面，當然where謂詞清單中有多個字段時，索引字段也有先後原則：等于謂詞及範圍定界謂詞的放在前面、過濾因子較小(能過濾較多的記錄)的放在較前；注意部分操作謂詞（如<>）是不能使用索引或索引對此操作謂詞不起優化作用。避免在索引列上使用函數。
2-start: order/group等字段置中間；
3-start: select列字段清單最後頁（這個可以做為可選項，若想建立完全索引掃描索引進選用）。
當然這此原則也要視業務及資料情況而定，可能平衡索引掃描與排序操作之間的成本。是以有時部分order/group等清單中的字段可能會優先于where謂詞部分字段放在中間，where的部分字段放在order/group字段的後面。
雖然啟用雙向掃描可以提高非按建索引時的順序來掃描，但最好是索引字段升序、降序盡量與業務sql要求的排序一緻。
4
在db2中如何做索引的深度清理？
随着業務的發展，在生産庫上會建立越來越多的索引，如何識别從未使用、不經常使用的索引呢，給我們的資料庫減減負？
1、db2 v9.5及以下版本：
使用db2pd -d dbname -tcb index[all]，在“tcb index stats”資訊塊中，請關注以下兩列：scans、ixonlyscns。
如果索引沒有出現在“tcb index stats”資訊塊中、或其中的scans值及ixonlyscns值相對同一表的其他索引的這兩個值來說，要小很多數量級，則一般可以認為很少使用的，可以考慮删除。
（注意：由于db2pd是直接讀取記憶體的，其資料是資料庫activate以來的積累，如果資料庫啟動的時間範圍覆寫了所有或大多業務周期是最好的，即該跑的sql基本跑過了，才更有多參考價值；資料activate了多久，可以從剛才的輸出的前面部分可以看到類似：database partition 0 -- database sample -- active -- up 0 days 00:14:20 -- date 2015-07-09-19.21.27.429000的情況，可以從“up”看看資料庫啟動了多久。）
2、db2 v9.7及以上版本：
檢視系統索引視圖：syscat.indexes.lastused，表示索引最後一次使用的日期；如果其值在對應表的業務周期（如月類型、年類型、周類型）還要前的，則證明索引在最近的業務周期内使用不上了。
對于syscat.indexes.lastused，還應考慮以下3點：
syscat.indexes.lastused='0001-01-01'，表示索引在建立以後重來沒有使用過；結合索引建立時間（syscat.indexes.create_time），如果索引建立時間比較久了,則很有可能是無用索引，否則就要結合業務周期來考慮了。
syscat.indexes.lastused<>'0001-01-01'，則還要結合上述db2pd，因為最近使用過并不代表是經常使用的。
考慮索引與表的聚集度，查詢系統視圖：syscat.indexes.clusterratio或對比syscat.indexes.fullkeycard與syscat.tables.card的值，如果比值很小，說明索引行與表資料行的聚集度比較差，差則表明在對表進行批量操作時很有可能對此索引發生較高的維護成本，特别是io維護成本上，是以請考慮清理此索引。
根據版本不同的，結合以上兩點來識别無用索引、不經常用的索引，但還有一個很重要的前提，索引的統計資訊是經常及時更新，否則可能會引起誤判進而誤殺索引。
另外，在清理無用、不經常用的索引時，請注意索引類型為p（主健）、u（唯一）一定要小心，當初建這些類型索引時可能是基于業務限制。
通過以上的讨論、學習，讓我們對索引有了更進一步的認識，為我們用好索引這把雙刃劍打下良好的基礎。
<b></b>
<b>本文來自雲栖社群合作夥伴"dbaplus"，原文釋出時間：2015-12-21</b>