引言

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

索引設(shè)計(jì)原則

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

原則一：根據(jù)SQL語(yǔ)句中的where條件、order by條件以及group by條件對(duì)應(yīng)的字段進(jìn)行索引設(shè)計(jì)。

當(dāng)我們的SQL語(yǔ)句中出現(xiàn)where條件、order by條件以及group by條件的時(shí)候，也就是表示我們需要通過(guò)SQL語(yǔ)句來(lái)進(jìn)行數(shù)據(jù)過(guò)濾(where條件)、根據(jù)哪些字段進(jìn)行排序(order by條件)以及根據(jù)哪些字段進(jìn)行分組聚合(group by條件)。因此我們的設(shè)計(jì)的索引需要盡可能的覆蓋這些字段，為的就是在數(shù)據(jù)查詢(xún)的時(shí)候通過(guò)這些字段用上索引。假設(shè)我們有這樣一張表clothes可以用來(lái)查詢(xún)衣服，那么在設(shè)計(jì)索引的時(shí)候就需要根據(jù)實(shí)際的查詢(xún)需求在對(duì)應(yīng)的字段建立索引。那么對(duì)于衣服這張表來(lái)說(shuō)一般會(huì)在c_brand(品牌)、c_type(類(lèi)型)以及c_size(尺碼)等這些字段建立索引，因?yàn)樗麄兪亲畛Ｓ玫暮Y選條件，另外可以考慮在價(jià)格字段上進(jìn)行排序，這也是非常常見(jiàn)的過(guò)濾條件。

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

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

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

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

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

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

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

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

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

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

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

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

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

有些字段類(lèi)型的長(zhǎng)度比較長(zhǎng)，因此字段的區(qū)分區(qū)相對(duì)來(lái)說(shuō)也是比較大的，因此這些字段比較適合建索引。但是也是因?yàn)樽侄伍L(zhǎng)度的原因，所建立的索引占用磁盤(pán)空間就會(huì)相對(duì)較大。實(shí)際上只要字段區(qū)分度足夠高，沒(méi)有必要對(duì)全字段建立索引，我們可以截取字段指定數(shù)量的字符作為檢索條件的索引，具體需要截取多少字符那需要根據(jù)截取的字符串是否可以保持比較大區(qū)分度來(lái)進(jìn)行決定。

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

總結(jié)

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