六條幹貨幫你的MySQL索引起飛

導讀	本文主要總結了在進行索引設計的時候需要考慮的幾點設計原則，其實索引設計的根本無非就是兩點，一個是希望透過兩三個聯合索引來覆蓋資料檢索的各個場景，避免因為檢索的時候沒有索引導致的資料檢索效率低的問題，再者就是希望在實際的SQL執行過程中儘量避免索引失效情況的發生，避免建了索引但是實際上並不起作用。

相信大家都知道索引可以加快資料的查詢速度，但是有時候如果索引設計不當，也可能造成索引失效而進行全表資料掃描，從而最終導致系統效能下降。因此我們在索引設計階段就需要充分考慮各種可能情況，儘量避免由於索引設計缺陷導致的後期出現資料查詢效能問題。本文總結了7個實用Mysql索引設計原則，相信在大家進行索引設計的時候可以進行參考。

我們在資料庫表設計好之後，先不要著急馬上就進行表的索引設計，因為這個時候其實你也並不清楚未來在這個表上可能存在的查詢條件到底是什麼。所以我們需要先根據實際的產品需求來進行業務程式碼開發，在這個過程中我們必然會涉及到資料庫持久化操作，也就是我們常說的CRUD。等我們把對應的Mapper介面以及SQL寫好後，也就基本確定了哪些欄位是條件欄位、哪些欄位是排序欄位以及哪些欄位是分組欄位。這些欄位確認好之後，我們就可以著手進行資料庫表的索引設計了。關於如何設計索引，這裡給大家梳理了7條非常實用的索引設計原則，相信大家在實際的專案中都可以用得上。

當我們的SQL語句中出現where條件、order by條件以及group by條件的時候，也就是表示我們需要透過SQL語句來進行資料過濾(where條件)、根據哪些欄位進行排序(order by條件)以及根據哪些欄位進行分組聚合(group by條件)。因此我們的設計的索引需要儘可能的覆蓋這些欄位，為的就是在資料查詢的時候透過這些欄位用上索引。假設我們有這樣一張表clothes可以用來查詢衣服，那麼在設計索引的時候就需要根據實際的查詢需求在對應的欄位建立索引。那麼對於衣服這張表來說一般會在c_brand(品牌)、c_type(型別)以及c_size(尺碼)等這些欄位建立索引，因為他們是最常用的篩選條件，另外可以考慮在價格欄位上進行排序，這也是非常常見的過濾條件。

什麼叫基數比較大的欄位呢?實際就是值比較多的欄位，或者說就是欄位值的區分度比較高，我們可以用一個簡單的公式來評判某個欄位的區分度，區分度等於count(distinct 具體的列) / count(*)，表示欄位不重複的比例。也就是說欄位中包含的變化資料比較多的話是比較適合建索引的，因為這樣才能發揮索引B+樹的潛力。為什麼這麼說呢?

假設有這樣一張員工表中包含了性別欄位i_gender，它的值只有0：男性，1：女性這兩個值。我們都知道Mysql的索引結構是透過B+樹實現的，而B+樹背後的核心本質思想實際就是二分查詢。而二分查詢就需要待排序的資料基數大，也就是區分度高。而欄位中只有0、1這樣的就屬於基數比較小，無法發揮索引樹檢索的效率，Mysql認為這種索引樹還不如全表查詢來的痛快。

另外還需要特別注意點是，對於區分度高的欄位我們應該把它放在聯合索引的左側，因為這樣可以更快得過濾掉更多的無效資料，從而提升索引的使用效率。還是拿員工資訊來舉例子，員工表中的畢業院校的欄位的區分度就比民族欄位區分度要高的多，索引我們在設計聯合索引的時候就需要將畢業院校的欄位仿造民族的左側，這樣可以更快的過濾掉無效資料。

資料庫JOIN是常見的資料記錄遍歷的SQL操作，假設平臺有一張使用者表以及訂單表，這個時候如果想要獲取使用者的訂單資訊，那麼就可以使用JOIN操作來完成操作。不過在使用JOIN的過程中如果參與JOIN的表過多的話，對應的結果可能是一個笛卡爾積，對於Mysql的最佳化器來說實在是很難選擇出來哪個才是最好的執行計劃，就好比找物件一樣，如果只有一個可以選擇也沒什麼好糾結的，如果有10個可以選擇，那就很頭大了，不知道選擇哪個好，因此我們要避免出現過多數量表的JOIN。另外很重要的一點就是在進行JOIN的欄位上一定要建立索引，否則全表掃描。同時JOIN欄位的型別、字符集等都要保持一致，避免在JOIN過程中可能導致的隱式的型別轉換造成不走索引的後果。

資料庫JOIN是常見的資料記錄遍歷的SQL操作，假設平臺有一張使用者表以及訂單表，這個時候如果想要獲取使用者的訂單資訊，那麼就可以使用JOIN操作來完成操作。不過在使用JOIN的過程中如果參與JOIN的表過多的話，對應的結果可能是一個笛卡爾積，對於Mysql的最佳化器來說實在是很難選擇出來哪個才是最好的執行計劃，就好比找物件一樣，如果只有一個可以選擇也沒什麼好糾結的，如果有10個可以選擇，那就很頭大了，不知道選擇哪個好，因此我們要避免出現過多數量表的JOIN。另外很重要的一點就是在進行JOIN的欄位上一定要建立索引，否則全表掃描。還有很重要的一點，用於JOIN的欄位的型別、字符集等都需要保持一致，否則可能存在隱式的型別轉換導致走不了索引。

索引本身也是佔用磁碟空間的，因此如果可以在欄位型別比較小的欄位上面建立索引，相應的索引佔用空間就會更少，對應其資料檢索的效率就會更高。但是這並非絕對的，如果存在區分度更高的欄位但是欄位型別比較大，那麼我們還是會在區分度高的欄位上面建立索引，但是我們可以採取一些折中的辦法，比如我們可以取欄位的前10個字元作為索引，這樣我們們既可以在區分度高的欄位建立索引，但是又至於太佔用磁碟空間。

有的同學在設計索引的時候恨不得把所有的欄位都加上索引，總是覺得索引越多肯定效能越好，實際上真實場景下並非如此。我們都知道索引就像是一本書的目錄，就像樹的目錄會佔用書中的紙張一樣，索引也是需要佔用磁碟空間進行儲存的，因此過多的索引會浪費資源。另外索引過多反而會降低效能，因為在進行資料插入的過程中，如果索引建立的過多就會導致更新多棵索引樹，在這個過程中，如果資料的插入並不是按照順序插入那麼還會導致資料頁分裂的問題。因此我們儘量透過兩道三個聯合索引來覆蓋全部的查詢場景。

有些欄位型別的長度比較長，因此欄位的區分割槽相對來說也是比較大的，因此這些欄位比較適合建索引。但是也是因為欄位長度的原因，所建立的索引佔用磁碟空間就會相對較大。實際上只要欄位區分度足夠高，沒有必要對全欄位建立索引，我們可以擷取欄位指定數量的字元作為檢索條件的索引，具體需要擷取多少字元那需要根據擷取的字串是否可以保持比較大區分度來進行決定。

SELECT COUNT(DISTINCT LEFT(order_code, 10)) / COUNT(*) FROM order

本文主要總結了在進行索引設計的時候需要考慮的幾點設計原則，其實索引設計的根本無非就是兩點，一個是希望透過兩三個聯合索引來覆蓋資料檢索的各個場景，避免因為檢索的時候沒有索引導致的資料檢索效率低的問題，再者就是希望在實際的SQL執行過程中儘量避免索引失效情況的發生，避免建了索引但是實際上並不起作用。把握了這兩個準則之後，相信大家在設計索引的時候可以遊刃有餘。

原文來自：