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');
執(zhí)行 select * from T where k between 3 and 5确丢，需要執(zhí)行幾次樹的搜索操作，會掃描多少行吐限？
這條SQL查詢語句的執(zhí)行流程：
- 1.在k索引樹上找到k=3的記錄鲜侥，取得 ID = 300；
- 2.再到ID索引樹查到ID=300對應(yīng)的R3诸典；
- 3.在k索引樹取下一個值k=5剃毒，取得ID=500；
- 4.再回到ID索引樹查到ID=500對應(yīng)的R4搂赋；
- 5.在k索引樹取下一個值k=6，不滿足條件益缠，循環(huán)結(jié)束脑奠。
在這個過程中，回到主鍵索引樹搜索的過程幅慌，我們稱為回表宋欺。可以看到胰伍，這個查詢過程讀了k索引樹的3條記錄（步驟1齿诞、3和5），回表了兩次（步驟2和4）骂租。
在這個例子中祷杈，由于查詢結(jié)果所需要的數(shù)據(jù)只在主鍵索引上有，所以不得不回表渗饮。那么但汞，有沒有可能經(jīng)過索引優(yōu)化，避免回表過程呢互站？
覆蓋索引
如果執(zhí)行的語句是select ID from T where k between 3 and 5私蕾，這時只需要查ID的值，而ID的值已經(jīng)在k索引樹上了胡桃，因此可以直接提供查詢結(jié)果踩叭，不需要回表。也就是說，在這個查詢里面容贝，索引k已經(jīng)“覆蓋了”我們的查詢需求自脯，我們稱為覆蓋索引。
由于覆蓋索引可以減少樹的搜索次數(shù)嗤疯，顯著提升查詢性能冤今，所以使用覆蓋索引是一個常用的性能優(yōu)化手段。
需要注意的是茂缚，在引擎內(nèi)部使用覆蓋索引在索引k上其實讀了三個記錄戏罢，R3~R5（對應(yīng)的索引k上的記錄項），但是對于MySQL的Server層來說脚囊，它就是找引擎拿到了兩條記錄龟糕，因此MySQL認為掃描行數(shù)是2。
基于上面覆蓋索引的說明悔耘，我們來討論一個問題：在一個市民信息表上讲岁，是否有必要將身份證號和名字建立聯(lián)合索引？
假設(shè)這個市民表的定義是這樣的：
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
我們知道衬以，身份證號是市民的唯一標識缓艳。也就是說，如果有根據(jù)身份證號查詢市民信息的需求看峻，我們只要在身份證號字段上建立索引就夠了阶淘。而再建立一個（身份證號、姓名）的聯(lián)合索引互妓，是不是浪費空間溪窒？
如果現(xiàn)在有一個高頻請求，要根據(jù)市民的身份證號查詢他的姓名冯勉，這個聯(lián)合索引就有意義了澈蚌。它可以在這個高頻請求上用到覆蓋索引，不再需要回表查整行記錄灼狰，減少語句的執(zhí)行時間宛瞄。
當然，索引字段的維護總是有代價的交胚。因此坛悉，在建立冗余索引來支持覆蓋索引時就需要權(quán)衡考慮了。這正是業(yè)務(wù)DBA承绸，或者稱為業(yè)務(wù)數(shù)據(jù)架構(gòu)師的工作裸影。
最左前綴原則
看到這里你一定有一個疑問，如果為每一種查詢都設(shè)計一個索引军熏，索引是不是太多了轩猩。如果我現(xiàn)在要按照市民的身份證號去查他的家庭地址呢？雖然這個查詢需求在業(yè)務(wù)中出現(xiàn)的概率不高，但總不能讓它走全表掃描吧均践？反過來說晤锹，單獨為一個不頻繁的請求創(chuàng)建一個（身份證號，地址）的索引又感覺有點浪費彤委。應(yīng)該怎么做呢鞭铆？
B+樹這種索引結(jié)構(gòu)，可以利用索引的“最左前綴”焦影，來定位記錄车遂。
為了直觀地說明這個概念，我們用（name斯辰，age）這個聯(lián)合索引來分析舶担。
可以看到，索引項是按照索引定義里面出現(xiàn)的字段順序排序的彬呻。
當你的邏輯需求是查到所有名字是“張三”的人時衣陶，可以快速定位到ID4，然后向后遍歷得到所有需要的結(jié)果闸氮。
如果你要查的是所有名字第一個字是“張”的人剪况，你的SQL語句的條件是"where name like ‘張%’"。這時蒲跨，你也能夠用上這個索引译断，查找到第一個符合條件的記錄是ID3，然后向后遍歷财骨，直到不滿足條件為止。
可以看到藏姐，不只是索引的全部定義隆箩，只要滿足最左前綴，就可以利用索引來加速檢索羔杨。這個最左前綴可以是聯(lián)合索引的最左N個字段捌臊，也可以是字符串索引的最左M個字符。
基于上面對最左前綴索引的說明兜材，我們來討論一個問題：在建立聯(lián)合索引的時候理澎，如何安排索引內(nèi)的字段順序。
這里的評估標準是曙寡，索引的復用能力糠爬。因為可以支持最左前綴，所以當已經(jīng)有了(a,b)這個聯(lián)合索引后举庶，一般就不需要單獨在a上建立索引了执隧。因此努酸，第一原則是黍图，如果通過調(diào)整順序，可以少維護一個索引，那么這個順序往往就是需要優(yōu)先考慮采用的嚎于。
所以現(xiàn)在你知道了，這段開頭的問題里酷含，我們要為高頻請求創(chuàng)建(身份證號檬某，姓名）這個聯(lián)合索引，并用這個索引支持“根據(jù)身份證號查詢地址”的需求钓试。
那么装黑，如果既有聯(lián)合查詢，又有基于a亚侠、b各自的查詢呢曹体？查詢條件里面只有b的語句，是無法使用(a,b)這個聯(lián)合索引的硝烂，這時候你不得不維護另外一個索引箕别，也就是說你需要同時維護(a,b)、(b) 這兩個索引滞谢。
這時候串稀，我們要考慮的原則就是空間了。比如上面這個市民表的情況狮杨，name字段是比age字段大的 母截，那我就建議你創(chuàng)建一個（name,age)的聯(lián)合索引和一個(age)的單字段索引。
索引下推
以市民表的聯(lián)合索引（name, age）為例橄教。如果現(xiàn)在有一個需求：檢索出表中“名字第一個字是張清寇，而且年齡是10歲的所有男孩”。那么护蝶，SQL語句是這么寫的：
mysql> select * from tuser where name like '張%' and age=10 and ismale=1;
你已經(jīng)知道了前綴索引規(guī)則华烟，所以這個語句在搜索索引樹的時候，只能用 “張”持灰，找到第一個滿足條件的記錄ID3盔夜。當然，這還不錯堤魁，總比全表掃描要好喂链。
然后呢？
當然是判斷其他條件是否滿足妥泉。
在MySQL 5.6之前椭微，只能從ID3開始一個個回表。到主鍵索引上找出數(shù)據(jù)行盲链，再對比字段值赏表。
而MySQL 5.6 引入的索引下推優(yōu)化（index condition pushdown)检诗， 可以在索引遍歷過程中，對索引中包含的字段先做判斷瓢剿，直接過濾掉不滿足條件的記錄逢慌，減少回表次數(shù)。
-
無索引下推執(zhí)行流程圖
image.png -
索引下推執(zhí)行流程
image.png
這兩個圖里面间狂，每一個虛線箭頭表示回表一次攻泼。
第一個圖中，在(name,age)索引里面我特意去掉了age的值鉴象，這個過程InnoDB并不會去看age的值忙菠，只是按順序把“name第一個字是’張’”的記錄一條條取出來回表。因此纺弊，需要回表4次牛欢。
他們的區(qū)別是，InnoDB在(name,age)索引內(nèi)部就判斷了age是否等于10淆游，對于不等于10的記錄傍睹，直接判斷并跳過。在這個例子中犹菱，只需要對ID4拾稳、ID5這兩條記錄回表取數(shù)據(jù)判斷，就只需要回表2次腊脱。
——學自極客時間
