在開始這篇文章之前，我們先來看一下這個(gè)問題：
在下面這個(gè)表 T 中掰茶，如果我執(zhí)行 select * from T where k between 3 and 5囊咏，需要執(zhí)行幾次樹的搜索操作洽胶，會(huì)掃描多少行？

下面是這個(gè)表的初始化語句佳镜。

mysql> 
create table T (
ID int primary key,
k int NOT NULL DEFAULT 0, 
s varchar(16) NOT NULL DEFAULT '',
index k(k))engine=InnoDB;

insert into T 
values(100,1, 'aa'),(200,2,'bb'),(300,3,'cc'),(500,5,'ee'),(600,6,'ff'),(700,7,'gg');

現(xiàn)在僚稿，我們一起來看看這條SQL查詢語句的執(zhí)行流程：
1.在k索引樹上找到k=3的記錄，取得ID=300蟀伸；
2.再到ID索引樹查到ID=300對(duì)應(yīng)的R3蚀同；
3.在k索引樹取下一個(gè)值k=5，取得D=500啊掏；
4.再回到ID索引樹查到ID=500對(duì)應(yīng)的R4蠢络；
5.在k索引樹取下一個(gè)值k=6，不滿足條件脖律，循環(huán)結(jié)束谢肾。

在這個(gè)過程中，回到主鍵索引樹搜索的過程小泉，我們稱為回表芦疏。可以看到微姊，這個(gè)查詢過程讀了k索引樹的3條記錄（步驟1酸茴、3和5），回表了兩次（步驟2和4）兢交。
在這個(gè)例子中薪捍，由于查詢結(jié)果所需要的數(shù)據(jù)只在主鍵索引上有，所以不得不回表配喳。那么酪穿，有沒有可能經(jīng)過索引優(yōu)化，避免回表過程呢晴裹？

覆蓋索引

如果執(zhí)行的語句是 select ID from T where k between 3 and 5被济，這時(shí)只需要查 ID 的值，而 ID 的值已經(jīng)在 k 索引樹上了涧团，因此可以直接提供查詢結(jié)果只磷，不需要回表经磅。也就是說，在這個(gè)查詢里面钮追，索引 k 已經(jīng)“覆蓋了”我們的查詢需求预厌，我們稱為覆蓋索引。

由于覆蓋索引可以減少樹的搜索次數(shù)元媚，顯著提升查詢性能轧叽，所以使用覆蓋索引是一個(gè)常用的性能優(yōu)化手段。

需要注意的是惠毁，在引擎內(nèi)部使用覆蓋索引在索引 k 上其實(shí)讀了三個(gè)記錄犹芹，R3~R5（對(duì)應(yīng)的索引 k 上的記錄項(xiàng)），但是對(duì)于 MySQL 的Server 層說鞠绰，它就是找引擎拿到了兩條記錄來腰埂，因此 MySQL 認(rèn)為掃描行數(shù)是 2。

基于上面覆蓋索引的說明蜈膨，我們來討論一個(gè)問題：在一個(gè)市民信息表上屿笼，是否有必要將身份證號(hào)和名字建立聯(lián)合索引？
假設(shè)這個(gè)市民表的定義是這樣的：

CREATE TABLE `tuser` (
  `id` int(11) NOT NULL,
  `id_card` varchar(32) DEFAULT NULL,
  `name` varchar(32) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `ismale` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `id_card` (`id_card`),
  KEY `name_age` (`name`,`age`)
) ENGINE=InnoDB

我們知道翁巍，身份證號(hào)是市民的唯一標(biāo)識(shí)驴一。也就是說，如果有根據(jù)身份證號(hào)查詢市民信息的需求灶壶，我們只要在身份證號(hào)字段上建立索引就夠了肝断。而再建立一個(gè)（身份證號(hào)、姓名）的聯(lián)合索引驰凛，是不是浪費(fèi)空間胸懈？

如果現(xiàn)在有一個(gè)高頻請(qǐng)求，要根據(jù)市民的身份證號(hào)查詢他的姓名恰响，這個(gè)聯(lián)合索引就有意義了趣钱。它可以在這個(gè)高頻請(qǐng)求上用到覆蓋索引，不再需要回表查整行記錄胚宦，減少語句的執(zhí)行時(shí)間首有。

當(dāng)然，索引字段的維護(hù)總是有代價(jià)的枢劝。因此井联，在建立冗余索引來支持覆蓋索引時(shí)就需要權(quán)衡考慮了。這正是業(yè)務(wù) DBA您旁，或者稱為業(yè)務(wù)數(shù)據(jù)架構(gòu)師的工作烙常。

最左前綴原則

最左前綴原則指的是在使用復(fù)合索引時(shí)，索引的最左邊的連續(xù)幾個(gè)列會(huì)被用于查詢過濾條件的匹配被冒。查詢條件必須從索引的最左邊開始军掂，并且不能跳過中間的列。只有當(dāng)查詢中的過濾條件與索引的最左前綴完全匹配時(shí)昨悼，索引才能被充分利用蝗锥。

如果為每一種查詢都設(shè)計(jì)一個(gè)索引，索引是不是太多了率触。如果我現(xiàn)在要按照市民的身份證號(hào)去查他的家庭地址呢终议？雖然這個(gè)查詢需求在業(yè)務(wù)中出現(xiàn)的概率不高，但總不能讓它走全表掃描吧葱蝗？反過來說穴张，單獨(dú)為一個(gè)不頻繁的請(qǐng)求創(chuàng)建一個(gè)（身份證號(hào)，地址）的索引又感覺有點(diǎn)浪費(fèi)两曼。應(yīng)該怎么做呢皂甘？

這里，我先和你說結(jié)論吧悼凑。B+ 樹這種索引結(jié)構(gòu)偿枕，可以利用索引的“最左前綴”，來定位記錄户辫。

可以看到墓塌，索引項(xiàng)是按照索引定義里面出現(xiàn)的字段順序排序的。

當(dāng)你的邏輯需求是查到所有名字是“張三”的人時(shí)奥额，可以快速定位到ID4苫幢，然后向后遍歷得到所有需要的結(jié)果。

如果你要查的是所有名字第一個(gè)字是“張”的人披坏，你的SQL語句的條件是"where name like‘張%'"态坦。這時(shí)，你也能夠用上這個(gè)索引棒拂，查找到第一個(gè)符合條件的記錄是ID3伞梯，然后向后遍歷，直到不滿足條件為止帚屉。

可以看到谜诫，不只是索引的全部定義，只要滿足最左前綴攻旦，就可以利用索引來加速檢索喻旷。這個(gè)最左前綴可以是聯(lián)合索引的最左N個(gè)字段，也可以是字符串索引的最左M個(gè)字符牢屋。

基于上面對(duì)最左前綴索引的說明且预，我們來討論一個(gè)問題：在建立聯(lián)合索引的時(shí)候槽袄，如何安排索引內(nèi)的字段順序。

這里我們的評(píng)估標(biāo)準(zhǔn)是锋谐，索引的復(fù)用能力遍尺。因?yàn)榭梢灾С肿钭笄熬Y，所以當(dāng)已經(jīng)有了（a涮拗，b）這個(gè)聯(lián)合索引后乾戏，一般就不需要單獨(dú)在a上建立索引了。因此三热，第一原則是鼓择，如果通過調(diào)整順序，可以少維護(hù)一個(gè)索引就漾，那么這個(gè)順序往往就是需要優(yōu)先考慮采用的呐能。

所以現(xiàn)在你知道了，這段開頭的問題里抑堡，我們要為高頻請(qǐng)求創(chuàng)建（身份證號(hào)催跪，姓名）這個(gè)聯(lián)合索引，并用這個(gè)索引支持“根據(jù)身份證號(hào)查詢地址”的需求夷野。

那么懊蒸，如果既有聯(lián)合查詢，又有基于a悯搔、b各自的查詢呢骑丸？查詢條件里面只有b的語句是無法使用（a，b）這個(gè)聯(lián)合索引的妒貌，這時(shí)候你不得不維護(hù)另外一個(gè)索引通危，也就是說你需要同時(shí)維護(hù)（a，b）灌曙、（b）這兩個(gè)索引菊碟。

這時(shí)候，我們要考慮的原則就是空間了在刺。比如上面這個(gè)市民表的情況逆害，name 字段是比 age 字段大的 ，那我就建議你創(chuàng)建一個(gè)（name,age) 的聯(lián)合索引和一個(gè) (age) 的單字段索引蚣驼。

索引下推

上一段我們說到滿足最左前綴原則的時(shí)候魄幕，最左前綴可以用于在索引中定位記錄。這時(shí)颖杏，你可能要問纯陨，那些不符合最左前綴的部分，會(huì)怎么樣呢？

我們還是以市民表的聯(lián)合索引（name翼抠，age）為例咙轩。如果現(xiàn)在有一個(gè)需求：檢索出表中“名字第一個(gè)字是張，而且年齡是10歲的所有男孩”阴颖。那么臭墨，SQL語句是這么寫的：
mysql> select * from tuser where name like '張%' and age=10 and ismale=1;

你已經(jīng)知道了前綴索引規(guī)則，所以這個(gè)語句在搜索索引樹的時(shí)候膘盖，只能用 “張”，找到第一個(gè)滿足條件的記錄 ID3尤误。當(dāng)然侠畔，這還不錯(cuò)，總比全表掃描要好损晤。(mysql 會(huì)一直向右匹配直到遇到范圍查詢（>软棺、<、between尤勋、like）就停止匹配喘落。范圍列可以用到索引，但是范圍列后面的列無法用到索引最冰。即瘦棋，索引最多用于一個(gè)范圍列，因此如果查詢條件中有兩個(gè)范圍列則無法全用到索引)

然后呢暖哨？當(dāng)然是判斷其他條件是否滿足赌朋。

在MySQL5.6之前，只能從ID3開始一個(gè)個(gè)回表篇裁。到主鍵索引上找出數(shù)據(jù)行沛慢，再對(duì)比字段值。
而MySQL5.6引入的索引下推優(yōu)化（index condition pushdown）达布，可以在索引遍歷過程中团甲，對(duì)索引中包含的字段先做判斷，直接過濾掉不滿足條件的記錄黍聂，減少回表次數(shù)躺苦。
圖3和圖4，是這兩個(gè)過程的執(zhí)行流程圖产还。

無索引下推執(zhí)行流程

索引下推執(zhí)行流程

在圖3和4這兩個(gè)圖里面圾另，每一個(gè)虛線箭頭表示回表一次。

圖3中雕沉，在（name集乔，age）索引里面我特意去掉了age的值，這個(gè)過程InnoDB并不會(huì)去看age的值，只是按順序把“name第一個(gè)字是’張”的記錄一條條取出來回表扰路。因此尤溜，需要回表4次。

圖4跟圖3的區(qū)別是汗唱，InnoDB在（name宫莱，age）索引內(nèi)部就判斷了age是否等于10，對(duì)于不等于10的記錄哩罪，直接判斷并跳過授霸。在我們的這個(gè)例子中，只需要對(duì)ID4际插、ID5這兩條記錄回表取數(shù)據(jù)判斷碘耳，就只需要回表2次。