建立索引的原則 - 以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索引

一種是主鍵索引，主鍵索引即聚集索引（Cluster Index），它不僅有主鍵，而且有主鍵所屬的全部數據，所以在Innodb中，主鍵索引即數據；

一種是列值為Key，主鍵位置為Value即(列值, 主鍵位置) 的非主鍵索引（Secondary Index） 

🎜🎜🎜🎜資料全部掛在主鍵葉子節點下。所以如果不能保證主鍵的插入順序，那麼就會發生大量的主鍵節點分裂，產生大量的I/O操作。另外Innodb規定單一索引欄位的長度不得超過768字節，否則截斷超出長度不放入索引。 Innodb的非主鍵索引全部都指向主鍵索引，查找非主鍵索引無法獲得整行數據，需要透過葉子節點的指針查到其主鍵索引的位置才能獲得整行數據，所以主鍵索引必須設計得盡可能小，否則非主鍵索引將會非常的大。 🎜🎜四、建立索引的原則🎜

        下面我們來看看建立一個好的索引需要遵循的原則，並結合特定的例子來做說明；
1. 最左前綴匹配原則，非常重要的原則，mysql會一直向右匹配直到遇到範圍查詢( >、 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_m transaction_currency欄位放到索引中，因為這兩個欄位的區分度太差；
根據建立索引的原則2，語句1是可以走到這個索引的；
根據建立索引的原則1，語句2也是可以走到這個索引的；
索引不是越多越好，建立過多的索引會增加資料庫記憶體或磁碟的消耗，並且會影響到得插入、刪除等操作的效能，索引在建立索引時要遵循索引建立的原則，通盤考慮；

 以上就是建立索引的原則- 以innodb為例的內容，更多相關內容請關注PHP中文網（www.php.cn）！