閱讀該部分內(nèi)容時(shí)蛀恩，需要提前了解B+Tree樹基本知識(shí)點(diǎn)领迈，否則可能有些內(nèi)容你并不能很好的體會(huì)到算吩。對(duì)于下面幾點(diǎn)內(nèi)容如果不是很清楚拓萌，可以閱讀我之前寫的Mysql簡(jiǎn)敘一文中的內(nèi)容進(jìn)行了解岁钓。
- 每個(gè)索引都是一顆B+Tree，最下面的一層是葉子節(jié)點(diǎn)，其余都是內(nèi)節(jié)點(diǎn)屡限。而葉子節(jié)點(diǎn)中存儲(chǔ)的都是用戶相關(guān)數(shù)據(jù)品嚣，而內(nèi)節(jié)點(diǎn)存儲(chǔ)的是頁(yè)節(jié)點(diǎn)相關(guān)信息。
- InnoDB會(huì)自動(dòng)為主鍵（沒有會(huì)自動(dòng)添加）建立聚族索引钧大，聚族索引包含了所有的用戶數(shù)據(jù)翰撑。
- 二級(jí)索引中的用戶數(shù)據(jù)是通過索引列和主鍵組成的，利用二級(jí)索引找到對(duì)應(yīng)的記錄時(shí)拓型，如果需要獲取整條記錄信息需要通過二級(jí)索引中的主鍵通過回表的方式從聚族索引中獲取额嘿。
- B+Tree中每層節(jié)點(diǎn)都是按照索引列從小到大的順序排列的瘸恼。
- 查找數(shù)據(jù)時(shí)是從B+Tree的根節(jié)點(diǎn)開始往下找劣挫。
索引使用示例
對(duì)于后面的示例，我們定義如下表結(jié)構(gòu)：
CREATE TABLE `t_user` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '用戶ID',
`user_name` varchar(255) COLLATE utf8mb4_bin NOT NULL COMMENT '用戶姓名',
`age` int(11) NOT NULL COMMENT '年齡',
`phone_no` varchar(255) COLLATE utf8mb4_bin NOT NULL COMMENT '手機(jī)號(hào)',
PRIMARY KEY (`id`),
KEY `index_user_age` (`user_name`,`age`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
從表結(jié)構(gòu)中我們可以知道东帅，在這個(gè)表中我們存在兩個(gè)索引压固，一個(gè)是根據(jù)主鍵和用戶數(shù)據(jù)創(chuàng)建的聚族索引，另外一個(gè)則是user_name和age組成的二級(jí)索引靠闭，所以在這個(gè)表中存在兩顆B+Tree樹帐我。
全值匹配
SELECT * FROM t_user WHERE user_name = 'tom' AND age = 18;
我們建立的index_user_age索引兩個(gè)字段都在最后的查詢條件中，這個(gè)查詢的執(zhí)行過程如下：
- 首先通過index_user_age這個(gè)B+Tree樹能很快的找到對(duì)應(yīng)的記錄愧膀。
- 因?yàn)樾枰祷厮袛?shù)據(jù)拦键，而index_user_age該索引樹中只有user_name，age檩淋，id信息芬为，所以需要通過回表的方式從聚族索引找到對(duì)應(yīng)的整條用戶數(shù)據(jù)然后返回。
可能你一直有一個(gè)疑問就是我們的查詢語句中條件的順序是user_namge和age蟀悦，這個(gè)順序和索引中字段的順序一樣能用到索引媚朦。但是如果我的查詢語句中條件的順序是age和user_name時(shí)，還能用到索引嗎日戈？答案是能用到索引询张。在Mysql中存在查詢優(yōu)化引擎，它會(huì)根據(jù)你創(chuàng)建的索引里的字段順序去優(yōu)化你的SQL浙炼，所以對(duì)于這一點(diǎn)完全不用擔(dān)心份氧。
從上面的分析結(jié)果可以看出它們的結(jié)果是一樣的，所以你是不用擔(dān)心查詢條件中的字段順序的弯屈。
最左匹配原則
SELECT * FROM t_user WHERE age =18;
上面的SQL能用到索引嗎半火？如果你了解B+Tree索引知識(shí)，你就能知道是不行的季俩。因?yàn)樗环献钭笃ヅ湓瓌t钮糖，那為什么會(huì)這樣子呢？我們先看看index_user_age索引具體是什么樣子的。
通過上圖我想你很清楚索引的結(jié)構(gòu)了店归。最后一層就是我們的索引數(shù)據(jù)阎抒，它們的順序是按照user_name->age排列的，我們?cè)谑褂胕ndex_user_age索引時(shí)消痛，我們先只能先按照user_name按順序找到所有的對(duì)應(yīng)的索引數(shù)據(jù)且叁，然后再根據(jù)age去做進(jìn)一步的篩選，所以對(duì)于上面的SQL語句我們無法用到index_user_age索引秩伞。那下面這個(gè)語句可以用到index_user_age索引嗎逞带？
SELECT * FROM t_user WHERE user_name = 'tom';
相信你心中已經(jīng)有了答案。我們直接看這個(gè)語句的分析結(jié)果纱新，看看是不是和我們說的一致展氓。
從explain結(jié)果可以看出，第一條SQL是無法用到任何索引的脸爱，但是第二條可以用到index_user_age索引遇汞。
索引下推
我們修改index_user_age索引，在原先index_user_age的基礎(chǔ)上增加phone_no索引字段簿废，創(chuàng)建新的索引index_user_age_phone索引空入。修改后的表結(jié)構(gòu)如下：
CREATE TABLE `t_user` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '用戶ID',
`user_name` varchar(255) COLLATE utf8mb4_bin NOT NULL COMMENT '用戶姓名',
`age` int(11) NOT NULL COMMENT '年齡',
`phone_no` varchar(255) COLLATE utf8mb4_bin NOT NULL COMMENT '手機(jī)號(hào)',
`address` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `index_user_age_phone` (`user_name`,`age`,`phone_no`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
現(xiàn)在我有一個(gè)查詢?nèi)缦拢?/p>
SELECT * FROM t_user WHERE user_name = 'jack' AND phone_no = '11111111111';
我現(xiàn)在想問的是這個(gè)查詢中能用到索引嗎？phone_no這個(gè)能用到索引嗎族檬？根據(jù)前面的最左匹配原則可以知道這個(gè)查詢能用到索引index_user_age_phone歪赢，好像phone_no這個(gè)是沒法用到索引的。對(duì)于index_user_age_phone索引來說单料，它內(nèi)部索引列的字段順序是按照user_name->age->phone_no按順序排列的埋凯，對(duì)于我們的查詢條件，搜索時(shí)我們只能用到user_name這個(gè)列了看尼。但是實(shí)際上phone_no這個(gè)列也能用到递鹉，只不過不是用在搜索查詢中。
通過二級(jí)索引查詢記錄時(shí)藏斩，我們只能獲取到索引列上的值和主鍵躏结，如果我們的返回結(jié)果中需要返回該記錄的所有字段信息，我們就不得不通過回表的方式從聚族索引再次獲取該記錄的完整信息狰域。這個(gè)過程如下所示：
首先我們根據(jù)user_name找到所有滿足記錄數(shù)據(jù)媳拴，然后通過回表的方式從對(duì)應(yīng)的主鍵索引（聚族索引）中找到完整的用戶數(shù)據(jù)，然后再篩選phone_no這個(gè)條件兆览∏龋回表這個(gè)操作你可以理解為通過主鍵ID從聚族索引中獲取用戶數(shù)據(jù)，索引回表是有消耗的抬探，雖然通過主鍵查找記錄很快子巾，但是通過user_name匹配到的數(shù)據(jù)很多時(shí)帆赢，還是會(huì)影響查詢速度的。那有沒有方式可以減少回表的次數(shù)呢线梗？
估計(jì)聰明的你肯定想到了椰于，從圖中我們可以看到現(xiàn)在有三條記錄滿足 user_name=jack 這個(gè)條件，正常情況下我們需要回表三次仪搔。如果我在回表前就篩選phone_no這個(gè)查詢條件瘾婿，這樣是不是就能減少我們的回表次數(shù)呢？如果在回表前判斷phone_no這個(gè)條件烤咧，這時(shí)我們只有一條記錄滿足條件偏陪，回表次數(shù)也從3次變成1次了。
對(duì)于上面這種方式煮嫌，我們就叫做 索引下推 笛谦。這個(gè)特性是在Mysql5.6及以后才版本才提供，對(duì)于之前的Mysql版本是沒有這個(gè)優(yōu)化的立膛。
列前綴匹配
在開發(fā)中我們可能會(huì)聽別人說like查詢無法使用索引揪罕，但是實(shí)際真的如此嗎梯码？其實(shí)這個(gè)跟我們之前說的最左匹配原則很相似宝泵，例如有下面的這個(gè)查詢：
SELECT * FROM t_user WHERE user_name LIKE 't%';
這個(gè)查詢的意思是查找名字以t開頭的用戶，對(duì)于這個(gè)查詢是能使用到索引的轩娶。在這個(gè)查詢中會(huì)用到index_user_age_phone這個(gè)索引儿奶，因?yàn)樵诙?jí)索引樹中，索引的內(nèi)容是按照user_name鳄抒、age闯捎、phone_no這個(gè)順序排列的。而我們的查詢是查user_name以字母t開頭的许溅，所以在這個(gè)查詢中我們的索引能生效瓤鼻，這個(gè)方式我們通常稱作列前綴匹配。但是下面這個(gè)SQL索引是不生效的贤重。
SELECT * FROM t_user WHERE user_name LIKE '%t%';
這個(gè)SQL的意思是查找名字中包含字母t的用戶茬祷，所以這個(gè)時(shí)候索引不會(huì)生效。我們可以通過explain結(jié)果來驗(yàn)證我們的結(jié)論并蝗。
從上面的explain結(jié)果我們可以看出祭犯，對(duì)于like 't%' 即前綴匹配是可以用到索引的，而后面的 like '%t%' 是無法用到索引的滚停。
索引用于排序
索引不僅僅只是用作查詢條件沃粗，同時(shí)索引還能作用于排序。例如現(xiàn)在有下面這個(gè)排序：
SELECT * FROM t_user ORDER BY user_name,age,phone_no LIMIT 10;
例如上面這個(gè)SQL键畴，它是可以用到索引的最盅。因?yàn)閷?duì)于index_user_age_phone這個(gè)索引樹，它里面的數(shù)據(jù)是按照user_name、age和phone_no組合的值從小到大排列的涡贱，所以這里是能用到索引的挂签。可能你會(huì)問盼产，默認(rèn)情況下排序是ASC饵婆，跟索引里面一樣是從小到大，但如果換成DESC戏售，即從大到小的順序排列那還能使用索引嗎侨核？答案是可以，只要字段順序沒變灌灾，不管是ASC還是DESC它都能用到索引搓译。（注意：如果使用explain查看結(jié)果可能發(fā)現(xiàn)它沒有使用任何索引，這可能是因?yàn)槟惚碇械臄?shù)據(jù)量太少锋喜，Mysql掃全表比使用索引更快些己，因?yàn)槭褂枚?jí)索引需要回表）。
那是不是排序都能用上索引呢嘿般？答案是不是的段标。并不是所有的排序都能用上索引，對(duì)于下面的幾種情況是無法通過索引來排序的炉奴。
- ASC和DESC混用
SELECT * FROM t_user ORDER BY user_name ASC ,age DESC ,phone_no ASC LIMIT 10;
- where子句中出現(xiàn)非排序的索引列
SELECT * FROM t_user WHERE address = 'xxxx' ORDER BY user_name LIMIT 10;
- 排序列包含非統(tǒng)一個(gè)索引的列
SELECT * FROM t_user ORDER BY user_name,address;
- 排序列使用了函數(shù)
SELECT * FROM t_user ORDER BY UPPER(user_name)
索引用于分組
索引用于分組這個(gè)跟排序類似逼庞，基本上的原則也都一樣。
回表和索引覆蓋
前面說過瞻赶，有時(shí)候使用explain分析語句時(shí)發(fā)現(xiàn)并不是按照一些規(guī)則來的急前，至于為什么會(huì)如此這個(gè)就跟回表有關(guān)了叹螟。我們?cè)谑褂枚?jí)索引找到對(duì)應(yīng)數(shù)據(jù)后赶诊，如果我們要返回的列不在索引中赤屋，這個(gè)時(shí)候我們就需要進(jìn)行回表∈σ荩回表它需要通過ID再去聚族索引中找到原始數(shù)據(jù)司倚，并且這里面并不是一個(gè)順序I/O。如果對(duì)于大量的數(shù)據(jù)需要回表時(shí)字旭，Mysql往往有時(shí)候不會(huì)使用二級(jí)索引而是直接掃表对湃。因?yàn)榇罅康幕乇硭男视锌赡苓€沒有掃全表的效率高。
對(duì)于我們查詢而言遗淳，如果應(yīng)該盡量不使用號(hào)返回所有字段拍柒，而應(yīng)該只取我們需要的字段返回即可。同時(shí)我們還可以通過索引覆蓋*的方式來減少回表屈暗。例如我有一個(gè)查詢是通過user_name查詢用戶的年齡和手機(jī)號(hào)拆讯，我在創(chuàng)建索引時(shí)不僅僅只在user_name列上創(chuàng)建索引脂男，我可以創(chuàng)建一個(gè)由user_name、age和phone_no三個(gè)字段組成的索引种呐，而查詢的返回值只需要返回這三個(gè)字段即可宰翅。
建索引的一些建議
對(duì)于索引而言，并不是越多越好爽室。索引是可以提高我們的查詢效率汁讼，但是如果索引過多也會(huì)帶來一些負(fù)面影響。每個(gè)索引在InnoDB中都是一顆B+Tree阔墩，多一個(gè)索引就多一顆B+Tree嘿架，這會(huì)占用過多的磁盤空間。同時(shí)在更新和刪除時(shí)啸箫，需要同時(shí)更新和刪除索引中的數(shù)據(jù)耸彪，這樣會(huì)導(dǎo)致數(shù)據(jù)庫(kù)的寫入性能受影響。很多情況下面忘苛，有些索引是多余的蝉娜，我們應(yīng)該精簡(jiǎn)我們的索引。通常我們?cè)诮ㄋ饕龝r(shí)應(yīng)該有下面的幾個(gè)原則：
- 只為用戶搜索扎唾、排序或分組的列上建立索引召川。
對(duì)于這點(diǎn)我想很好理解，如果有些列我們根本用不上那就沒必要建索引稽屏，多建的索引并不能給我們帶來好處反而還浪費(fèi)了服務(wù)器的資源扮宠。 - 為基數(shù)大的列建立索引西乖。
什么是基數(shù)呢狐榔？例如性別這個(gè)列，正常情況下我們一般也就是存三個(gè)值获雕。例如：男薄腻、女、未知届案。即使我們的表里面存了100萬條數(shù)據(jù)庵楷，它的值還是在這三個(gè)里面，而這個(gè)列的基數(shù)就是3楣颠。為了加快搜索我們?cè)谛詣e這個(gè)列上面創(chuàng)建索引尽纽，這種方式并不能帶來查詢性能上的明顯改善，對(duì)于這種列我們就沒有必要在這個(gè)列上單獨(dú)創(chuàng)建索引了童漩。 - 索引列的類型盡量小弄贿。
例如對(duì)于整數(shù)類型在Mysql中就有好幾種，例如tinyint,mediumint,int,bigint矫膨。它們所占的空間依次遞增差凹，而能表示的數(shù)字大小也同樣依次遞增期奔。如果一個(gè)整數(shù)列的值能用int表示完整我們就不應(yīng)該用bigint，數(shù)據(jù)類型越小危尿，索引所占的空間也就越小呐萌，那么在一個(gè)業(yè)內(nèi)能存放的數(shù)據(jù)也就越多。這樣就能減少磁盤I/O的次數(shù)谊娇，減少磁盤I/O的次數(shù)也就意味著性能的提升肺孤。 - 刪除冗余和重復(fù)索引
什么樣的索引算是冗余和重復(fù)索引呢？例如我創(chuàng)建了一個(gè)以u(píng)ser_name列和age列組合而成的索引index_user_age济欢，然后又創(chuàng)建了一個(gè)以u(píng)ser_name列的索引index_user渠旁，這種情況下索引就重復(fù)了。對(duì)于這種情況船逮，我們可以刪掉index_user索引顾腊，因?yàn)閕ndex_user_age索引完全可以替代index_user索引的效果，所以我們沒必要多加一個(gè)索引浪費(fèi)服務(wù)器資源挖胃。
