建立索引的原則-以innodb為例

一、寫在前面

        隨著開發、測試任務進入尾聲，大家都在整理一些專案釋出前的一些準備工作，其中一個重要的工作就是為之前寫的一些sql語句建立索引，這高併發、高訪問量的環境下是非常有必要的，建立一個好的索引能夠極大地提高sql語句的查詢效率，那麼問題來了，到底什麼是索引，怎樣才能建立一個好的索引呢？本文以mysql Innodb儲存引擎為例，結合實際的專案來看一下，如何建立一個好的而索引。

二、索引定義

        MySQL官方對索引的定義為：索引(Index)是幫助MySQL高效獲取資料的資料結構。提取句子主幹，就可以得到索引的本質：索引是資料結構。
        我們知道，資料庫查詢是資料庫的最主要功能之一，例如下面的SQL語句：SELECT * FROM test_table WHERE id = 99 ；可以從表test_table中獲得id為99的資料記錄。
        我們都希望查詢資料的速度能儘可能的快，因此資料庫系統的設計者會從查詢演算法的角度進行優化。最基本的查詢演算法當然是順序查詢(linear search)，遍歷test_table然後逐行匹配id的值是否是99，這種複雜度為O(n)的演算法在資料量很大時顯然是糟糕的，好在電腦科學的發展提供了很多更優秀的查詢演算法，例如二分查詢(binary search)、二叉樹查詢(binary tree search)等。如果稍微分析一下會發現，每種查詢演算法都只能應用於特定的資料結構之上，例如二分查詢要求被檢索資料有序，而二叉樹查詢只能應用於二叉查詢樹上，但是資料本身的組織結構不可能完全滿足各種資料結構(例如，理論上不可能同時將兩列都按順序進行組織)，所以，在資料之外，資料庫系統還維護著滿足特定查詢演算法的資料結構，這些資料結構以某種方式引用(指向)資料，這樣就可以在這些資料結構上實現高階查詢演算法。這種資料結構，就是索引。
        舉上面的例子主要是為了簡單說明地說明索引的作用，包括mysql Innodb在內的大部分資料庫系統及檔案系統並沒有選擇二叉樹結構作為索引，而是採用了B-Tree或其變種B+Tree作為索引結構，這種索引結構可以最大限度地減少查詢過程中磁碟I/O的存取次數，關於什麼是B-Tree或B+Tree以及選擇它們做資料庫索引結構的原因，大家可以自行去學習。下面我們首先介紹下mysql Innodb引擎的兩種B+Tree索引。

三、Mysql Innodb B+Tree索引

1. 一種是主鍵索引，主鍵索引即聚集索引（Cluster Index），它不僅有主鍵，而且有主鍵所屬的全部資料，所以在Innodb中，主鍵索引即資料；
2. 一種是列值為Key，主鍵位置為Value即 (列值, 主鍵位置) 的非主鍵索引（Secondary Index）           Innodb屬於索引組織表，所有的資料全部掛在主鍵葉子節點下。所以如果不能保證主鍵的插入順序，那麼會發生大量的主鍵節點分裂，產生大量的I/O操作。另外Innodb規定單個索引欄位的長度不得超過768位元組，否則截斷超出長度不放入索引。         Innodb的非主鍵索引全部都指向主鍵索引，查詢非主鍵索引無法獲得整行資料，需要通過葉子節點的指標查到其主鍵索引的位置才能獲得整行資料，所以主鍵索引必須設計得儘可能小，否則非主鍵索引將會非常的大。

四、建立索引的原則

        下面我們看一下建立一個好的索引需要遵循的原則，並結合具體的例子來做說明；
1. 最左字首匹配原則，非常重要的原則，mysql會一直向右匹配直到遇到範圍查詢(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)順序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引則都可以用到，a,b,d的順序可以任意調整。
2. =和in可以亂序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意順序，mysql的查詢優化器會幫你優化成索引可以識別的形式。
3. 儘量選擇區分度高的列作為索引,區分度的公式是count(distinct col)/count(*)，表示欄位不重複的比例，比例越大我們掃描的記錄數越少，唯一鍵的區分度是1，而一些狀態、性別欄位可能在大資料面前區分度就是0，那可能有人會問，這個比例有什麼經驗值嗎？使用場景不同，這個值也很難確定，一般需要join的欄位我們都要求是0.1以上，即平均1條掃描10條記錄
4. 索引列不能參與計算，保持列“乾淨”，比如from_unixtime(create_time) = ‘2015-08-14’就不能使用到索引，原因很簡單，b+樹中存的都是資料表中的欄位值，但進行檢索時，需要把所有元素都應用函式才能比較，顯然成本太大。所以語句應該寫成create_time = unix_timestamp(‘2015-08-14’)。
5. 儘量的擴充套件索引，不要新建索引。比如表中已經有a的索引，現在要加(a,b)的索引，那麼只需要修改原來的索引即可。
6. 在order by或者group by子句中，如果想通過索引來進行排序，所建索引列的順序必須與order by或者group by子句的順序一致，並且所有列的排序方向（倒序或者正序）都一樣；如果查詢關聯多張表，則只有order by子句引用的欄位全部來自第一張表時，才能利用索引來排序；order by或者group by語句與查詢型語句的限制是一樣的:需要滿足索引的最左字首原則；否則mysql就要執行排序操作，無法利用索引來排序；（有一種情況order by或者group by子句可以不滿足最左字首原則，就是其前導為常量的時候，如果where或者join對這些列指定了常量，就可以彌補索引的不足）。

五、舉例

        語句1：

        語句2：

        對於這兩條語句，如果單獨進行考慮的話，大家可能會建立兩個索引；
針對語句1建立(status,netting_batch_no,debtor_agent_member_id)；
針對語句2建立(netting_batch_no,debtor_agent_member_id,transaction_currency)；
如果綜合考慮來看的話，其實一個索引就夠了，即(netting_batch_no,debtor_agent_member_id)，這裡沒必要將status或者transaction_currency欄位放到索引中，因為這兩個欄位的區分度太差；
根據建立索引的原則2，語句1是可以走到這個索引的；
根據建立索引的原則1，語句2也是可以走到這個索引的；
索引不是越多越好，建立過多的索引會增加資料庫記憶體或者磁碟的消耗，並且會影響到得插入、刪除等操作的效能，索引在建立索引時要遵循索引建立的原則，通盤考慮；