MySQL索引使用細(xì)節(jié)
這里不介紹MySQL索引是什么，僅總結(jié)索引使用的一些注意事項
索引的代價
我們知道了B+樹的索引原理之后，也知道了表中的數(shù)據(jù)都是在聚簇索引上的，同時就是一顆B+樹。所有之后按其他列建立的索引都是二級索引也叫非聚簇索引。
奇跡和魔法可不是免費的
額外的索引當(dāng)然是有代價的，就像正常插入數(shù)據(jù)到聚簇索引中需要調(diào)整B+樹一樣，二級索引也有一模一樣的維護(hù)代價。那么毫無疑問多余的索引存在：
-
空間代價
B+樹的一個節(jié)點在內(nèi)存中體現(xiàn)為一個頁，一頁的大小是16KB，如果索引很多，內(nèi)存中就會有更多的索引頁。 -
時間代價
如果一個表頻繁的被修改，那么每次操作都要同步去修改
B+樹中的索引值，索引越多需要同時維護(hù)的B+樹就越多，這顯然是一筆不小的代價
所以索引的建立不是越多越好，索引的選擇和使用更像是不斷權(quán)衡比較選擇相對最優(yōu)的選擇。
索引的匹配規(guī)則
簡單說明索引是如何創(chuàng)建的：當(dāng)以多個值建立聯(lián)合索引時，會按從左到右的順序進(jìn)行排序，也就是假如建立的索引為(A, B, C)，
那么會先按列A進(jìn)行排序，直到A值完全一樣，再依照B進(jìn)行排序，C同理。
索引這種規(guī)則也就解釋了最左前綴原則，當(dāng)希望使用索引時，應(yīng)該包含索引從左開始的連續(xù)字段，因為查詢的順序也就是按建立索引的順序來的。
提供幾個樣例來說明：
- 字符串的模糊查詢是可以使用索引的（如果建立了的話），因為以
name排序的過程中，實際是字符串的比較排序，而字符串的比較是從左到右一個個字符比較大小的來，這和建立聯(lián)合索引的規(guī)則類似，比較好理解。
SELECT * FROM person_info WHERE name LIKE 'As%';
- 條件查詢中
and連接的條件順序不影響索引的使用，SQL語句執(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排序之后，對范圍內(nèi)的查詢相當(dāng)于分別查找兩個邊界值，再去節(jié)點之間的鏈表值。
不要忘記了B+樹的葉子節(jié)點是鏈表構(gòu)成的，有了邊界節(jié)點，在鏈表中遍歷就能找到期望數(shù)據(jù)了。
注意！
范圍查詢可以使用索引是有要求的，簡單總結(jié)成一句話就是，范圍查詢的左邊都是精確查詢，而范圍查詢的右邊無法使用索引。解釋一下：
聯(lián)合索引是從左向右依次排序的，也就是只有在當(dāng)前比較列之前的列完全相同的情況下，索引才是有效的。而左邊全是精確查詢就確保了 在范圍查詢前之前的列值是完全一樣的，就可以通過索引查找范圍，而找到一個范圍值之后剩下查詢的值就不能通過索引保證排序的（當(dāng)前在一個鏈表中假設(shè)按A進(jìn)行排序，而之后的B,C在單獨的一個A值下才是有序的，在這個列表中則表現(xiàn)為無序）。
關(guān)于關(guān)鍵字
-
Order By之后的順序遵循最左前綴原則，同時如果要使用索引，順序必須一致，order by就是一個按提供鍵 順序排序的過程。同時注意不要
ASC,DESC混合使用。這樣也會導(dǎo)致索引效率低下。 -
WHERE中最好只有建立了索引的列，如果出現(xiàn)了未建立索引的列，那么查詢只能先將符合條件的記錄提取出來之后再進(jìn)行排序。同樣的，
WHERE條件中對索引列不要使用表達(dá)式和函數(shù)，這會導(dǎo)致查詢將先對表達(dá)式進(jìn)行計算，構(gòu)建成新的列再排序。 Group by這點和Order By是完全類似的。
關(guān)于回表
回表同樣也是建立使用索引必須要權(quán)衡的代價之一?？紤]這樣一個查詢
SELECT * FROM person_info WHERE name > 'Asa' AND name < 'Barlow';
在范圍查找之后，要選擇出全部的數(shù)據(jù)，那么就必然需要拿著索引中找到的主鍵去回表。
- 訪問二級索引使用
順序I/O，訪問聚簇索引使用隨機(jī)I/O。
需要回表的記錄越多，使用二級索引的性能就越差。甚至可能因為回表過多，MySQL會將索引優(yōu)化成全表掃描。所以使用索引時盡可能指定需要的數(shù)據(jù)，而不要使用來表示，同時盡可能選擇索引中包含的列值*，這樣就不會進(jìn)行回表。
覆蓋索引是一種解決回表的方式，但代價是存儲了相當(dāng)?shù)娜哂鄶?shù)據(jù)。
挑選索引
只列出幾條大致的規(guī)則：
只為用于搜索，排序，和分組的列創(chuàng)建索引
-
考慮列的基數(shù)
列的基數(shù)指某一列中不重復(fù)數(shù)據(jù)的個數(shù)。如果一個列中重復(fù)的數(shù)據(jù)過多，基數(shù)過小（例如性別數(shù)據(jù)只有男，女，基數(shù)為2）那么建立索引的效果就特別差，因為在節(jié)點中查找時幾乎沒有區(qū)分?jǐn)?shù)據(jù)的功能 -
索引列的類型盡量小
這是考慮到建立，維護(hù)索引的代價。以整數(shù)類型為例，有
TINYINT、MEDIUMINT、INT、BIGINT這么幾種，它們占用的存儲空間依次遞增，我們這里所說的類型大小指的就是該類型表示的數(shù)據(jù)范圍的大小。能表示的整數(shù)范圍當(dāng)然也是依次遞增，如果我們想要對某個整數(shù)列建立索引的話，在表示的整數(shù)范圍允許的情況下，盡量讓索引列使用較小的類型，比如我們能使用INT就不要使用BIGINT，能使用MEDIUMINT就不要使用INT～數(shù)據(jù)類型越小，查詢時比較操作越快
數(shù)據(jù)類型越小，索引所占空間更小，一個頁面中可以存放更多的索引數(shù)據(jù)，變相減少了磁盤的
I/O次數(shù)
-
使用字符串值的前綴
這點是針對字符串可能過長的情況，理由和上面類似，為了減少查詢和維護(hù)時的代價，僅以字符串前幾位進(jìn)行比較建立索引。之后的值可以在一個小范圍內(nèi)遍歷查找，這樣犧牲了極小的性能省出了更多的空間和查找時間。
主鍵的選取
關(guān)于主鍵的選擇，一般MySQL會使用標(biāo)記為Unique的列構(gòu)建索引，如果沒有則會創(chuàng)建一個隱藏的自增列用于排序（在行格式，列結(jié)構(gòu)中提到過）。對于大多數(shù)情況來說，主鍵最好選擇一個非業(yè)務(wù)的列，因為自增的值是便于在頁中插入行數(shù)據(jù)的，如果不是順序的鍵很有可能數(shù)據(jù)插入過程中被分配在不同的頁，而插入新值后又需要不斷調(diào)整行數(shù)據(jù)的位置導(dǎo)致頁分裂之類的問題，影響效率。
