索引B+樹
在MySQL中，索引是以B+樹的形式存在的优俘，它是B樹的變體京办，其定義基本與B樹相同，下圖就是B+樹的數(shù)據(jù)結(jié)構(gòu)帆焕，圖中非葉子節(jié)點惭婿，藍(lán)色部分代表索引，黃色部分代表指向下一個節(jié)點的指針叶雹，葉子節(jié)點則代表實際保存的數(shù)據(jù)财饥。
圖1? ?mysql索引結(jié)構(gòu)
B+樹與B樹主要存在以下區(qū)別：
非葉子節(jié)點的子樹指針與關(guān)鍵字個數(shù)相同
非葉子節(jié)點的子樹指針P[i]，指向關(guān)鍵字值[K[i], K[i+1]）的子樹
非葉子節(jié)點僅用來索引折晦，數(shù)據(jù)都保存在葉子節(jié)點中钥星。
所有葉子節(jié)點具有一個鏈指針指向下一個葉子節(jié)點
所有的中間節(jié)點元素都同時存在于子節(jié)點，在子節(jié)點元素中是最大（或最小）元素满着，上圖就是在子節(jié)點元素中最小谦炒，這個與我們具體定義的規(guī)則有關(guān)贯莺。
最左匹配原則
我們在前面了解了MySQL的索引結(jié)構(gòu)，下面我們就來分析如果是聯(lián)合索引编饺，在MySQL中是如何存儲的呢乖篷？
當(dāng)我們建立聯(lián)合索引時，聯(lián)合索引當(dāng)然還是一顆B+樹透且，比如我們建立一個聯(lián)合索引(a, b),那么它的索引結(jié)構(gòu)應(yīng)該是這樣的撕蔼。
a索引：1，1秽誊，2鲸沮，2，3锅论，3
b索引：1讼溺，2，1最易，4怒坯，1，2
通過觀察我們可以發(fā)現(xiàn)藻懒，在聯(lián)合索引中剔猿，對于a索引來說，索引是有序排列的嬉荆，對于b索引顯然是無序排列的归敬。同時我們還可以發(fā)現(xiàn)對于a值相等的情況下，b值也是有序的鄙早。
這種有序是相對的汪茧，a>1 and b=4;遇到這種范圍查詢，就不會再去走索引限番，這種情況下a值可以走索引舱污，而b值在這個范圍內(nèi)是無序的，所以最終也不會走索引弥虐。
那么我們就基本可以得出最左匹配原則的定義：最左優(yōu)先慌闭，以最左邊的為起點任何連續(xù)的索引都能匹配上。同時遇到范圍查詢(>躯舔、<、between省古、like)就會停止匹配粥庄。
實戰(zhàn)分析
首先我們來創(chuàng)建一個數(shù)據(jù)表tb_score，設(shè)置score和age字段組合成一個聯(lián)合索引豺妓，索引的名稱是“score_age_index”,在mysql中惜互，int類型占4個字節(jié)布讹，所以這個索引的長度是8個字節(jié)，這里計算索引的長度是為了判斷sql語句是否走了索引
1CREATETABLEtb_student?(
2`stu_id`intNOTNULLPRIMARYKEYAUTO_INCREMENTCOMMENT'主鍵id',
3`name`VARCHAR(100)NOTNULLCOMMENT'姓名',
4`score`intNOTNULLCOMMENT'成績',
5`age`intNOTNULLCOMMENT'年齡',
6INDEXscore_age_index?(`score`,`age`)
7)ENGINE=InnoDBDEFAULTCHARSET=utf8;
插入一些測試數(shù)據(jù)
1insertintotb_student(name,?score,?age)value('張三',40,21);
2insertintotb_student(name,?score,?age)value('王五',20,23);
3insertintotb_student(name,?score,?age)value('李四',90,26);
4insertintotb_student(name,?score,?age)value('趙六',60,19);
我們在分析查詢語句是否走索引可以用到mysql提供的一個命令explain训堆，如下圖我們做了一個查詢描验，根據(jù)分?jǐn)?shù)查詢學(xué)生的姓名，可以得出結(jié)論坑鱼，查詢走了我們定義的索引膘流，并沒有進(jìn)行全表掃描，下面我們就根據(jù)各種情況進(jìn)行分析鲁沥。
a.全值匹配
1mysql>explainselectnamefromtb_studentwhereage=20andscore=90;
根據(jù)結(jié)果可以得知呼股，key_len 為8 ，type為ref画恰，本次查詢用到了索引彭谁，雖然我們定義索引的順序是(score, age)，mysql可以進(jìn)行優(yōu)化允扇，自動幫我們改變順序缠局。
b.匹配左邊的列
上面這兩條sql語句，都是走索引的考润，因為他們都是從最左也就是score開始狭园，連續(xù)匹配的。
1mysql>explainselectnamefromtb_studentwhereage=20andscore=90;
2mysql>explainselectnamefromtb_studentwherescore=90;
而下面這條sql語句顯然是不會走索引的额划，因為它并沒有從最左連續(xù)匹配妙啃，這里走的是全表掃描，根據(jù)執(zhí)行結(jié)果我們也可以看出俊戳，type是ALL代表全表掃描揖赴，沒有使用到索引。
1mysql>explainselectnamefromtb_studentwhereage=19;
c.匹配列前綴
如果列是字符型的話它的比較規(guī)則是先比較字符串的第一個字符抑胎，第一個字符小的哪個字符串就比較小燥滑，如果兩個字符串第一個字符相同，那就再比較第二個字符阿逃，第二個字符比較小的那個字符串就比較小铭拧，依次類推，比較字符串恃锉。
如果score是字符類型搀菩，那么前綴匹配用的是索引，后綴和中綴只能全表掃描了破托。
1mysql>select*fromtb_studentwherealike'As%';?//前綴都是排好序的肪跋，走索引查詢
2mysql>select*fromtb_studentwherealike'%As'//全表查詢
3mysql>select*fromtb_studentwherealike'%As%'//全表查詢
d.匹配范圍值
可以對最左邊的列進(jìn)行范圍查詢，結(jié)果是一定會走索引的土砂。
1mysql>explainselectnamefromtb_studentwherescore?>60andscore?<90;
多個列同時進(jìn)行范圍查找時州既，只有對索引最左邊的那個列進(jìn)行范圍查找才用到B+樹索引谜洽，可以看到key_len為4，也就是只有score用到了索引吴叶，在90>score>60的情況下阐虚，age是無序的，不能用索引蚌卤，找到90>score>60的記錄后实束，只能根據(jù)條件?age>20 繼續(xù)逐條過濾.
1mysql>explainselectnamefromtb_studentwherescore?>60andscore?<90andage?>20;
e.精確匹配某一列并范圍匹配另一列
如果左邊的列是精確查找的，右邊的列可以進(jìn)行范圍查找造寝，如果score=90磕洪，age是有序的，并且我們可看到key_len是8诫龙，說明走的是聯(lián)合索引析显。
1mysql>explainselectnamefromtb_studentwherescore?=90andage?>20;
f.排序
???因為b+樹索引本身就是按照上述規(guī)則排序的，order by的子句后面的順序也必須按照索引列的順序給出签赃，就會走索引谷异。
1mysql>explainselectnamefromtb_studentorderbyscore,age;
這里和我們預(yù)想的結(jié)果?不太一致，經(jīng)過一番查證锦聊，如果數(shù)據(jù)庫中的數(shù)據(jù)量過小的時候歹嘹，mysql數(shù)據(jù)庫會自動為我們做優(yōu)化，它會認(rèn)為全表掃描要比索引更快孔庭，所以就采用全表掃描方式尺上。
如果我們顛倒順序去排序，那么肯定不會走索引圆到。
1mysql>explainselectnamefromtb_studentorderbyage,score;
如果最左邊列的值是定值怎抛，則對其他列順序排序是可以用到索引的。
1mysql>explainselectnamefromtb_studentwherescore?=60orderbyage;
EXPLAIN select * from student where a=1 and b=170 and c=1;-- ture
EXPLAIN select * from student where a=1 and b=170;-- ture
EXPLAIN select * from student where b=170 and c=1;-- false
EXPLAIN select * from student where a=1? and c=1;-- ture
EXPLAIN select * from student where b=170;-- false
EXPLAIN select * from student where a > 1 and b=170 and c=1;-- ture
EXPLAIN select * from student where a like "%1%" and b=170 and c=1; -- false
