MySQL索引使用細(xì)節(jié)
這里不介紹MySQL索引是什么，僅總結(jié)索引使用的一些注意事項(xiàng)
索引的代價(jià)
我們知道了B+樹(shù)的索引原理之后，也知道了表中的數(shù)據(jù)都是在聚簇索引上的神郊，同時(shí)就是一顆B+樹(shù)。所有之后按其他列建立的索引都是二級(jí)索引也叫非聚簇索引趾唱。
奇跡和魔法可不是免費(fèi)的
額外的索引當(dāng)然是有代價(jià)的涌乳，就像正常插入數(shù)據(jù)到聚簇索引中需要調(diào)整B+樹(shù)一樣，二級(jí)索引也有一模一樣的維護(hù)代價(jià)甜癞。那么毫無(wú)疑問(wèn)多余的索引存在：
-
空間代價(jià)
B+樹(shù)的一個(gè)節(jié)點(diǎn)在內(nèi)存中體現(xiàn)為一個(gè)頁(yè)夕晓，一頁(yè)的大小是16KB，如果索引很多悠咱，內(nèi)存中就會(huì)有更多的索引頁(yè)蒸辆。 -
時(shí)間代價(jià)
如果一個(gè)表頻繁的被修改，那么每次操作都要同步去修改
B+樹(shù)中的索引值析既，索引越多需要同時(shí)維護(hù)的B+樹(shù)就越多躬贡，這顯然是一筆不小的代價(jià)
所以索引的建立不是越多越好，索引的選擇和使用更像是不斷權(quán)衡比較選擇相對(duì)最優(yōu)的選擇眼坏。
索引的匹配規(guī)則
簡(jiǎn)單說(shuō)明索引是如何創(chuàng)建的：當(dāng)以多個(gè)值建立聯(lián)合索引時(shí)拂玻，會(huì)按從左到右的順序進(jìn)行排序，也就是假如建立的索引為(A, B, C)宰译，
那么會(huì)先按列A進(jìn)行排序檐蚜，直到A值完全一樣，再依照B進(jìn)行排序囤屹，C同理熬甚。
索引這種規(guī)則也就解釋了最左前綴原則逢渔，當(dāng)希望使用索引時(shí)肋坚，應(yīng)該包含索引從左開(kāi)始的連續(xù)字段，因?yàn)椴樵兊捻樞蛞簿褪前唇⑺饕捻樞騺?lái)的。
提供幾個(gè)樣例來(lái)說(shuō)明：
- 字符串的模糊查詢是可以使用索引的（如果建立了的話）智厌，因?yàn)橐?code>name排序的過(guò)程中诲泌，實(shí)際是字符串的比較排序，而字符串的比較是從左到右一個(gè)個(gè)字符比較大小的來(lái)铣鹏，這和建立聯(lián)合索引的規(guī)則類似敷扫，比較好理解。
SELECT * FROM person_info WHERE name LIKE 'As%';
- 條件查詢中
and連接的條件順序不影響索引的使用诚卸，SQL語(yǔ)句執(zhí)行前有查詢優(yōu)化器葵第。
SELECT * FROM person_info WHERE B = '1' and C = '2' and A = '0';
范圍值的匹配
如下的例子是可以使用索引的。
SELECT * FROM person_info WHERE name > 'Asa' AND name < 'Barlow';
按name排序之后合溺，對(duì)范圍內(nèi)的查詢相當(dāng)于分別查找兩個(gè)邊界值卒密，再去節(jié)點(diǎn)之間的鏈表值。
不要忘記了B+樹(shù)的葉子節(jié)點(diǎn)是鏈表構(gòu)成的棠赛，有了邊界節(jié)點(diǎn)哮奇，在鏈表中遍歷就能找到期望數(shù)據(jù)了。
注意睛约！
范圍查詢可以使用索引是有要求的鼎俘，簡(jiǎn)單總結(jié)成一句話就是，范圍查詢的左邊都是精確查詢辩涝，而范圍查詢的右邊無(wú)法使用索引贸伐。解釋一下：
聯(lián)合索引是從左向右依次排序的，也就是只有在當(dāng)前比較列之前的列完全相同的情況下怔揩，索引才是有效的棍丐。而左邊全是精確查詢就確保了 在范圍查詢前之前的列值是完全一樣的，就可以通過(guò)索引查找范圍沧踏，而找到一個(gè)范圍值之后剩下查詢的值就不能通過(guò)索引保證排序的（當(dāng)前在一個(gè)鏈表中假設(shè)按A進(jìn)行排序歌逢，而之后的B,C在單獨(dú)的一個(gè)A值下才是有序的，在這個(gè)列表中則表現(xiàn)為無(wú)序）翘狱。
關(guān)于關(guān)鍵字
-
Order By之后的順序遵循最左前綴原則秘案，同時(shí)如果要使用索引，順序必須一致潦匈，order by就是一個(gè)按提供鍵 順序排序的過(guò)程阱高。同時(shí)注意不要
ASC,DESC混合使用。這樣也會(huì)導(dǎo)致索引效率低下茬缩。 -
WHERE中最好只有建立了索引的列赤惊，如果出現(xiàn)了未建立索引的列，那么查詢只能先將符合條件的記錄提取出來(lái)之后再進(jìn)行排序凰锡。同樣的未舟，
WHERE條件中對(duì)索引列不要使用表達(dá)式和函數(shù)圈暗，這會(huì)導(dǎo)致查詢將先對(duì)表達(dá)式進(jìn)行計(jì)算，構(gòu)建成新的列再排序裕膀。 Group by這點(diǎn)和Order By是完全類似的员串。
關(guān)于回表
回表同樣也是建立使用索引必須要權(quán)衡的代價(jià)之一≈缈福考慮這樣一個(gè)查詢
SELECT * FROM person_info WHERE name > 'Asa' AND name < 'Barlow';
在范圍查找之后寸齐，要選擇出全部的數(shù)據(jù)，那么就必然需要拿著索引中找到的主鍵去回表抄谐。
- 訪問(wèn)二級(jí)索引使用
順序I/O渺鹦，訪問(wèn)聚簇索引使用隨機(jī)I/O。
需要回表的記錄越多蛹含，使用二級(jí)索引的性能就越差海铆。甚至可能因?yàn)榛乇磉^(guò)多，MySQL會(huì)將索引優(yōu)化成全表掃描挣惰。所以使用索引時(shí)盡可能指定需要的數(shù)據(jù)卧斟，而不要使用來(lái)表示，同時(shí)盡可能選擇索引中包含的列值*憎茂，這樣就不會(huì)進(jìn)行回表珍语。
覆蓋索引是一種解決回表的方式，但代價(jià)是存儲(chǔ)了相當(dāng)?shù)娜哂鄶?shù)據(jù)竖幔。
挑選索引
只列出幾條大致的規(guī)則：
只為用于搜索板乙，排序，和分組的列創(chuàng)建索引
-
考慮列的基數(shù)
列的基數(shù)指某一列中不重復(fù)數(shù)據(jù)的個(gè)數(shù)拳氢。如果一個(gè)列中重復(fù)的數(shù)據(jù)過(guò)多募逞，基數(shù)過(guò)小（例如性別數(shù)據(jù)只有男馋评，女放接，基數(shù)為2）那么建立索引的效果就特別差，因?yàn)樵诠?jié)點(diǎn)中查找時(shí)幾乎沒(méi)有區(qū)分?jǐn)?shù)據(jù)的功能 -
索引列的類型盡量小
這是考慮到建立留特，維護(hù)索引的代價(jià)纠脾。以整數(shù)類型為例，有
TINYINT蜕青、MEDIUMINT苟蹈、INT、BIGINT這么幾種右核，它們占用的存儲(chǔ)空間依次遞增慧脱，我們這里所說(shuō)的類型大小指的就是該類型表示的數(shù)據(jù)范圍的大小。能表示的整數(shù)范圍當(dāng)然也是依次遞增贺喝，如果我們想要對(duì)某個(gè)整數(shù)列建立索引的話菱鸥，在表示的整數(shù)范圍允許的情況下宗兼，盡量讓索引列使用較小的類型，比如我們能使用INT就不要使用BIGINT采缚，能使用MEDIUMINT就不要使用INT～數(shù)據(jù)類型越小针炉，查詢時(shí)比較操作越快
數(shù)據(jù)類型越小挠他，索引所占空間更小扳抽，一個(gè)頁(yè)面中可以存放更多的索引數(shù)據(jù)，變相減少了磁盤的
I/O次數(shù)
-
使用字符串值的前綴
這點(diǎn)是針對(duì)字符串可能過(guò)長(zhǎng)的情況殖侵，理由和上面類似贸呢，為了減少查詢和維護(hù)時(shí)的代價(jià)，僅以字符串前幾位進(jìn)行比較建立索引拢军。之后的值可以在一個(gè)小范圍內(nèi)遍歷查找楞陷，這樣犧牲了極小的性能省出了更多的空間和查找時(shí)間。
主鍵的選取
關(guān)于主鍵的選擇茉唉，一般MySQL會(huì)使用標(biāo)記為Unique的列構(gòu)建索引固蛾，如果沒(méi)有則會(huì)創(chuàng)建一個(gè)隱藏的自增列用于排序（在行格式，列結(jié)構(gòu)中提到過(guò)）度陆。對(duì)于大多數(shù)情況來(lái)說(shuō)艾凯，主鍵最好選擇一個(gè)非業(yè)務(wù)的列，因?yàn)樽栽龅闹凳潜阌谠?code>頁(yè)中插入行數(shù)據(jù)的懂傀，如果不是順序的鍵很有可能數(shù)據(jù)插入過(guò)程中被分配在不同的頁(yè)趾诗，而插入新值后又需要不斷調(diào)整行數(shù)據(jù)的位置導(dǎo)致頁(yè)分裂之類的問(wèn)題，影響效率蹬蚁。
