引言

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

索引設計原則

我們在數(shù)據(jù)庫表設計好之后，先不要著急馬上就進行表的索引設計，因為這個時候其實你也并不清楚未來在這個表上可能存在的查詢條件到底是什么。所以我們需要先根據(jù)實際的產品需求來進行業(yè)務代碼開發(fā)，在這個過程中我們必然會涉及到數(shù)據(jù)庫持久化操作，也就是我們常說的CRUD。等我們把對應的Mapper接口以及SQL寫好后，也就基本確定了哪些字段是條件字段、哪些字段是排序字段以及哪些字段是分組字段。這些字段確認好之后，我們就可以著手進行數(shù)據(jù)庫表的索引設計了。關于如何設計索引，這里給大家梳理了7條非常實用的索引設計原則，相信大家在實際的項目中都可以用得上。

原則一：根據(jù)SQL語句中的where條件、order by條件以及group by條件對應的字段進行索引設計。

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

原則二：在基數(shù)比較大的字段上建立索引，同時需要將基數(shù)更高的字段放在最左邊。

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

假設有這樣一張員工表中包含了性別字段i_gender，它的值只有0：男性，1：女性這兩個值。我們都知道Mysql的索引結構是通過B+樹實現(xiàn)的，而B+樹背后的核心本質思想實際就是二分查找。而二分查找就需要待排序的數(shù)據(jù)基數(shù)大，也就是區(qū)分度高。而字段中只有0、1這樣的就屬于基數(shù)比較小，無法發(fā)揮索引樹檢索的效率，Mysql認為這種索引樹還不如全表查詢來的痛快。

另外還需要特別注意點是，對于區(qū)分度高的字段我們應該把它放在聯(lián)合索引的左側，因為這樣可以更快得過濾掉更多的無效數(shù)據(jù)，從而提升索引的使用效率。還是拿員工信息來舉例子，員工表中的畢業(yè)院校的字段的區(qū)分度就比民族字段區(qū)分度要高的多，索引我們在設計聯(lián)合索引的時候就需要將畢業(yè)院校的字段仿造民族的左側，這樣可以更快的過濾掉無效數(shù)據(jù)。

原則三：如果SQL中出現(xiàn)JOIN操作，那么JOIN的字段必須建立索引，同時字段的類型、字符集都需要保持一致。

數(shù)據(jù)庫JOIN是常見的數(shù)據(jù)記錄遍歷的SQL操作，假設平臺有一張用戶表以及訂單表，這個時候如果想要獲取用戶的訂單信息，那么就可以使用JOIN操作來完成操作。不過在使用JOIN的過程中如果參與JOIN的表過多的話，對應的結果可能是一個笛卡爾積，對于Mysql的優(yōu)化器來說實在是很難選擇出來哪個才是最好的執(zhí)行計劃，就好比找對象一樣，如果只有一個可以選擇也沒什么好糾結的，如果有10個可以選擇，那就很頭大了，不知道選擇哪個好，因此我們要避免出現(xiàn)過多數(shù)量表的JOIN。另外很重要的一點就是在進行JOIN的字段上一定要建立索引，否則全表掃描。同時JOIN字段的類型、字符集等都要保持一致，避免在JOIN過程中可能導致的隱式的類型轉換造成不走索引的后果。

原則四：如果SQL中出現(xiàn)JOIN操作，那么JOIN的字段必須建立索引，同時字段的類型、字符集都需要保持一致。

數(shù)據(jù)庫JOIN是常見的數(shù)據(jù)記錄遍歷的SQL操作，假設平臺有一張用戶表以及訂單表，這個時候如果想要獲取用戶的訂單信息，那么就可以使用JOIN操作來完成操作。不過在使用JOIN的過程中如果參與JOIN的表過多的話，對應的結果可能是一個笛卡爾積，對于Mysql的優(yōu)化器來說實在是很難選擇出來哪個才是最好的執(zhí)行計劃，就好比找對象一樣，如果只有一個可以選擇也沒什么好糾結的，如果有10個可以選擇，那就很頭大了，不知道選擇哪個好，因此我們要避免出現(xiàn)過多數(shù)量表的JOIN。另外很重要的一點就是在進行JOIN的字段上一定要建立索引，否則全表掃描。還有很重要的一點，用于JOIN的字段的類型、字符集等都需要保持一致，否則可能存在隱式的類型轉換導致走不了索引。

原則五：盡量在字段類型值比較小的字段上建立索引。

索引本身也是占用磁盤空間的，因此如果可以在字段類型比較小的字段上面建立索引，相應的索引占用空間就會更少，對應其數(shù)據(jù)檢索的效率就會更高。但是這并非絕對的，如果存在區(qū)分度更高的字段但是字段類型比較大，那么我們還是會在區(qū)分度高的字段上面建立索引，但是我們可以采取一些折中的辦法，比如我們可以取字段的前10個字符作為索引，這樣我們們既可以在區(qū)分度高的字段建立索引，但是又至于太占用磁盤空間。

原則六：索引不是建地越多越好

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

原則七：使用字符串前綴創(chuàng)建索引

有些字段類型的長度比較長，因此字段的區(qū)分區(qū)相對來說也是比較大的，因此這些字段比較適合建索引。但是也是因為字段長度的原因，所建立的索引占用磁盤空間就會相對較大。實際上只要字段區(qū)分度足夠高，沒有必要對全字段建立索引，我們可以截取字段指定數(shù)量的字符作為檢索條件的索引，具體需要截取多少字符那需要根據(jù)截取的字符串是否可以保持比較大區(qū)分度來進行決定。

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

總結

本文主要總結了在進行索引設計的時候需要考慮的幾點設計原則，其實索引設計的根本無非就是兩點，一個是希望通過兩三個聯(lián)合索引來覆蓋數(shù)據(jù)檢索的各個場景，避免因為檢索的時候沒有索引導致的數(shù)據(jù)檢索效率低的問題，再者就是希望在實際的SQL運行過程中盡量避免索引失效情況的發(fā)生，避免建了索引但是實際上并不起作用。把握了這兩個準則之后，相信大家在設計索引的時候可以游刃有余。