mysql的索引是日常開(kāi)發(fā)中用到比較多的概念恭垦，對(duì)于千萬(wàn)級(jí)的表來(lái)說(shuō)邻吭，能充分的利用索引，便能充分的提高查詢效率焕檬。之前都是用到什么就查什么姆坚，沒(méi)有一個(gè)總體匯總的體系。索引使用的經(jīng)驗(yàn)真的是和自己實(shí)際的使用關(guān)系很大实愚，以InnoDB為例簡(jiǎn)單的總結(jié)一下：
1.索引優(yōu)點(diǎn)
- 索引大大減小了服務(wù)器需要掃描的數(shù)據(jù)量兼呵。
- 索引可以幫助服務(wù)器避免排序和臨時(shí)表兔辅。
- 索引可以將隨機(jī)I/O變?yōu)轫樞騃/O。
2.主鍵查詢走索引
對(duì)于索引來(lái)說(shuō)击喂，最基本的就是主鍵索引了维苔，在建表的時(shí)候都會(huì)指定主鍵。主鍵也默認(rèn)的會(huì)建立索引懂昂，我們一般使用的索引都是Btree索引介时，大概結(jié)構(gòu)如下：
MyISAM和InnoDB索引結(jié)構(gòu)有很大差異，這里以InnoDB為例凌彬，InnoDB的葉節(jié)點(diǎn)存儲(chǔ)的是數(shù)據(jù)的行沸柔，而除了主鍵之外的列索引存儲(chǔ)的是主鍵key，也就是說(shuō)在查詢的時(shí)候需要二次查詢饿序，先通過(guò)列索引找到主鍵勉失，再通過(guò)主鍵索引找到row羹蚣。而MyISAM的主鍵索引和列索引一致原探，只不過(guò)主鍵不可以重復(fù)，但是列可以顽素。
上面的索引結(jié)構(gòu)圖告訴我們咽弦，在查詢時(shí)，能使用主鍵查詢盡量不要用列查詢胁出，因?yàn)闀?huì)帶來(lái)二次查詢型型。
2.單個(gè)索引還是組合索引
上面我們提到一個(gè)查詢過(guò)程，其實(shí)對(duì)于普通列的查詢會(huì)轉(zhuǎn)化為兩次查詢全蝶，那么我們應(yīng)該盡量能在列的查詢中過(guò)濾掉盡可能多的數(shù)據(jù)闹蒜。
單個(gè)列索引查詢也有個(gè)限制，如果查詢的列不是獨(dú)立的抑淫，那么Mysql不會(huì)使用索引绷落。獨(dú)立的列指查詢中的列不能使用表達(dá)式，也不是函數(shù)的參數(shù)始苇。
select uname from user where rootid + 1 = 10;
上面的sql是不會(huì)走索引的砌烁，因?yàn)樾枰獙?duì)所有的rootid列進(jìn)行計(jì)算，所以要全表掃描催式，如果改成下面的語(yǔ)句則可以避免
select uname from user where rootid = 9;
所以索引列是不可以做運(yùn)算的函喉。
在老版本的Mysql中是只能用一個(gè)索引進(jìn)行查詢的，在5.0之后的Mysql荣月，引入了“索引合并”的概念管呵。雖然能利用多個(gè)索引查詢，但是索引合并不是很合理的：
- 在做and操作的時(shí)候單個(gè)索引（組合）的性能是遠(yuǎn)好于多個(gè)獨(dú)立索引的哺窄。而且在and時(shí)捐下，通常只能利用多個(gè)索引中的一個(gè)進(jìn)行查詢顿天。
- 對(duì)于or操作，對(duì)于多個(gè)列索引的情況下蔑担，需要耗費(fèi)大量的CPU和內(nèi)存在數(shù)據(jù)的緩存牌废、合并和排序上。
- 優(yōu)化器不會(huì)把所有這些消耗計(jì)算到“查詢成本”中啤握，導(dǎo)致查詢成本被遠(yuǎn)遠(yuǎn)低估鸟缕。
所以說(shuō)，其實(shí)“索引合并”只是一個(gè)優(yōu)化方案排抬，多于多個(gè)索引的掃描后計(jì)算懂从，明顯性能上是要差于單索引的查找。
下面看一個(gè)單列查詢的例子：
這里我的mobile和uname字段都是索引字段蹲蒲，但是從執(zhí)行計(jì)劃中可以看到只是走了uname的索引番甩。也就是說(shuō)mobile的索引是沒(méi)有用到的，那么用什么方法讓查詢也利用到mobile索引呢届搁？
針對(duì)我們經(jīng)常查詢的多列場(chǎng)景缘薛，我們可以建組合索引，組合索引在可以盡可能多的運(yùn)用列的查詢規(guī)則卡睦。說(shuō)到組合索引那么必須說(shuō)一下最左前綴原則：
最左前綴原則指的的是在sql where 子句中一些條件或表達(dá)式中出現(xiàn)的列的順序要保持和多索引的一致或以多列索引順序出現(xiàn)宴胧，只要 出現(xiàn)非順序出現(xiàn)、斷層都無(wú)法利用到多列索引表锻。
假如：現(xiàn)在對(duì)于一個(gè)table建立了一個(gè)組合索引（uname恕齐，age，mobile）瞬逊，那么對(duì)于查詢時(shí)where語(yǔ)句查詢條件必須為（uname显歧，age，mobile）或（uname确镊，age）或（uname）士骤，此時(shí)才能利用組合索引。為什么呢骚腥？
因?yàn)槲覀冎續(xù)ysql為btree索引敦间，對(duì)于每一個(gè)點(diǎn)索引數(shù)據(jù)都會(huì)維護(hù)一個(gè)（uname，age束铭，mobile）的復(fù)合數(shù)據(jù)結(jié)構(gòu)廓块，我們?cè)赒uery的時(shí)候一定會(huì)以一個(gè)字段為第一個(gè)匹配，第一個(gè)字段匹配之后選取第二個(gè)字段進(jìn)行匹配依次類推契沫。那么對(duì)于（age带猴，mobile）、（age）懈万、（monile）這種情況沒(méi)有第一個(gè)匹配項(xiàng)自然不會(huì)走索引拴清，而（uname靶病，mobile）也不會(huì)走索引。
下面例子的索引是（uname,mobile,email）的組合索引：
當(dāng)使用（uname,mobile,email）時(shí)：
可以看出查詢?nèi)孔吡怂饕?br> 當(dāng)使用（uname,mobile）時(shí)：
此時(shí)也走了索引口予。
但是如果是（uname,email）時(shí)：
這時(shí)候只是走了uname的索引娄周。
如果為（mobile,email）時(shí)：
此時(shí)完全沒(méi)有走索引。
4.索引的區(qū)分度
索引的區(qū)分度沪停，主要是衡量索引值不相同的程度煤辨，區(qū)分度越大，越有利于索引的查詢木张。
設(shè)想一下众辨，對(duì)于sex列，列值只有male和female舷礼，那么也就是說(shuō)列中絕大多數(shù)值都是重復(fù)的鹃彻，那么用此索引進(jìn)行row的查找其實(shí)意義并不大。所以這樣的列建索引的意義并不大妻献。
另一種場(chǎng)景蛛株，對(duì)于列值比較長(zhǎng)的列，我們往往不能將整個(gè)列做索引旋奢，因?yàn)檫@樣會(huì)導(dǎo)致索引過(guò)大泳挥，降低索引效率然痊。我們需要取列值的前綴進(jìn)行索引至朗，那么索引前綴的大小選擇就需要計(jì)算區(qū)分度。
索引的區(qū)分度計(jì)算主要計(jì)算是通過(guò) 不重復(fù)的索引值/數(shù)據(jù)表的總記錄數(shù)剧浸。區(qū)分度越高锹引，索引查詢時(shí)會(huì)讓mysql在查詢時(shí)過(guò)濾掉更多的行。值越接近1唆香，證明區(qū)分度越高嫌变。
5.組合索引的順序和區(qū)分度：
上面說(shuō)到了區(qū)分度越高的越容易用來(lái)做索引，因?yàn)閰^(qū)分度高的列可以很容易的過(guò)濾掉很多的數(shù)據(jù)躬它。對(duì)于組合索引來(lái)說(shuō)腾啥，在考慮索引的順序的時(shí)候也是要考慮數(shù)據(jù)的分布，也就是區(qū)分度冯吓。對(duì)于多個(gè)列構(gòu)成的組合索引倘待，在查詢過(guò)濾的時(shí)候也是和列的位置有關(guān)的，這也是最左前綴規(guī)則說(shuō)的事情组贺，也就是說(shuō)如果在第一次能過(guò)濾掉大量的數(shù)據(jù)凸舵，那么后續(xù)的索引匹配就能減少很多消耗。所以在選擇索引順序的時(shí)候最好是要考慮到區(qū)分度的問(wèn)題失尖，將區(qū)分度比較高的列放在前面啊奄。
6.利用索引進(jìn)行排序
Mysql可以通過(guò)兩種方式達(dá)到排序的效果：
- 進(jìn)行排序計(jì)算
- 按照索引順序掃描
對(duì)于后者渐苏，掃描索引是很快的。但是如果索引不能覆蓋查詢所需的全部列菇夸，那么對(duì)于每一次查詢都會(huì)回表查詢一次行琼富。這基本都是隨機(jī)IO。這種情況下按照索引順序讀取數(shù)據(jù)反而會(huì)慢于全表掃描庄新。
只有當(dāng)索引的列順序和Order By子句的順序完全一致時(shí)公黑，并且所有的列的排序方向都一樣時(shí)，才能使用索引對(duì)子句進(jìn)行排序摄咆。
也就是說(shuō)索引的排序必須保證最左前綴規(guī)則凡蚜，當(dāng)然也有例外，就是在where子句中指定索引列為常量吭从，同時(shí)保證where中條件和order by中條件滿足最左前綴規(guī)則朝蜘。首先看一下滿足最左前綴規(guī)則的order by：
explain select uname from testuser order by uname,mobile;
上面的表的索引是（uname, mobile, addtime），可以看到涩金，查詢走了組合索引谱醇，同時(shí)查詢使用了覆蓋索引。
除了上述的方式步做，還可以這么寫：
explain select uname from testuser where uname = 'zhangsan' order by mobile,addtime;
可以 看出來(lái)Extra中沒(méi)有filesort,證明排序走了索引副渴。
當(dāng)排序條件順序不一致時(shí)：
explain select uname from testuser where uname = 'zhangsan' order by mobile asc,addtime DESC;
可以看到，Extra中出現(xiàn)了filesort全度，也就是說(shuō)排序沒(méi)有用到索引煮剧，所以排序順序不一致的情況下是不能利用索引進(jìn)行排序的。
上面都是針對(duì)組合索引的将鸵，單列索引對(duì)于索引排序意義不大勉盅，order by 只有在使用排序字段索引時(shí)才會(huì)有用。
針對(duì)排序這種場(chǎng)景顶掉，其實(shí)還可以在業(yè)務(wù)中實(shí)現(xiàn)排序草娜，這樣能大大的減輕數(shù)據(jù)庫(kù)的壓力，不至于因?yàn)橐粋€(gè)查詢而影響其他業(yè)務(wù)痒筒。
7.應(yīng)該注意的幾點(diǎn)
- 在使用索引查詢的時(shí)候宰闰，需要保證索引類型和查詢的數(shù)據(jù)類型一致，經(jīng)巢就福混用的是用int型查詢varchar類型的數(shù)據(jù)或反過(guò)來(lái)移袍，這樣會(huì)導(dǎo)致索引失效。
- range查詢要盡量放在后面萎战，因?yàn)樵趓ange后面的查詢不會(huì)走索引咐容，這一點(diǎn)在設(shè)計(jì)索引的室友要注意
- Like查詢不能前綴模糊匹配，也就是說(shuō)不可以like ‘%123’蚂维。因?yàn)閘ike的后綴模糊 like ‘123%’可以轉(zhuǎn)化為range查詢戳粒，但是前綴模糊不可以路狮。
- 索引不是越多越好，索引十分大時(shí)不僅會(huì)影響查詢效率蔚约，同時(shí)會(huì)為數(shù)據(jù)的插入造成很大的負(fù)擔(dān)奄妨。
- 對(duì)于重復(fù)索引需要?jiǎng)h除，規(guī)劃好索引是高效率的前提苹祟。
以上是最近學(xué)習(xí)mysql索引的筆記砸抛，如果有什么不正確的地方，懇請(qǐng)指出树枫，找錯(cuò)改錯(cuò)的過(guò)程很重要直焙。勿忘初心，方得始終~
