[TOC]
在上一篇文章中，我和你介紹了 InnoDB 索引的數(shù)據(jù)結(jié)構(gòu)模型，今天我們?cè)倮^續(xù)聊聊跟 MySQL 索引有關(guān)的概念。

在開(kāi)始這篇文章之前，我們先來(lái)看一下這個(gè)問(wèn)題：

在下面這個(gè)表 T 中，如果我執(zhí)行 select * from T where k between 3 and 5，需要執(zhí)行幾次樹(shù)的搜索操作，會(huì)掃描多少行？

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

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');

image.png

現(xiàn)在，我們一起來(lái)看看這條 SQL 查詢語(yǔ)句的執(zhí)行流程：

1. 在 k 索引樹(shù)上找到 k=3 的記錄，取得 ID = 300；
2. 再到 ID 索引樹(shù)查到 ID=300 對(duì)應(yīng)的 R3；
3. 在 k 索引樹(shù)取下一個(gè)值 k=5，取得 ID=500；
4. 再回到 ID 索引樹(shù)查到 ID=500 對(duì)應(yīng)的 R4；
5. 在 k 索引樹(shù)取下一個(gè)值 k=6，不滿足條件，循環(huán)結(jié)束。

在這個(gè)過(guò)程中，回到主鍵索引樹(shù)搜索的過(guò)程，我們稱為回表?？梢钥吹剑@個(gè)查詢過(guò)程讀了 k 索引樹(shù)的 3 條記錄（步驟 1、3 和 5），回表了兩次（步驟 2 和 4）。

在這個(gè)例子中，由于查詢結(jié)果所需要的數(shù)據(jù)只在主鍵索引上有，所以不得不回表。那么，有沒(méi)有可能經(jīng)過(guò)索引優(yōu)化，避免回表過(guò)程呢？

覆蓋索引

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

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

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

基于上面覆蓋索引的說(shuō)明，我們來(lái)討論一個(gè)問(wèn)題：在一個(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í)。也就是說(shuō)，如果有根據(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)求上用到覆蓋索引，不再需要回表查整行記錄，減少語(yǔ)句的執(zhí)行時(shí)間。

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

最左前綴原則

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

這里，我先和你說(shuō)結(jié)論吧。B+ 樹(shù)這種索引結(jié)構(gòu)，可以利用索引的“最左前綴”，來(lái)定位記錄。

為了直觀地說(shuō)明這個(gè)概念，我們用（name，age）這個(gè)聯(lián)合索引來(lái)分析。

image.png

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

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

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

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

基于上面對(duì)最左前綴索引的說(shuō)明，我們來(lái)討論一個(gè)問(wèn)題：在建立聯(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 上建立索引了。因此，第一原則是，如果通過(guò)調(diào)整順序，可以少維護(hù)一個(gè)索引，那么這個(gè)順序往往就是需要優(yōu)先考慮采用的。

索引下推

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

我們還是以市民表的聯(lián)合索引（name, age）為例。如果現(xiàn)在有一個(gè)需求：檢索出表中“名字第一個(gè)字是張，而且年齡是 10 歲的所有男孩”。那么，SQL 語(yǔ)句是這么寫的：

mysql> select * from tuser where name like '張%' and age=10 and ismale=1;

你已經(jīng)知道了前綴索引規(guī)則，所以這個(gè)語(yǔ)句在搜索索引樹(shù)的時(shí)候，只能用 “張”，找到第一個(gè)滿足條件的記錄 ID3。當(dāng)然，這還不錯(cuò)，總比全表掃描要好。

然后呢？

當(dāng)然是判斷其他條件是否滿足。在 MySQL 5.6 之前，只能從 ID3 開(kāi)始一個(gè)個(gè)回表。到主鍵索引上找出數(shù)據(jù)行，再對(duì)比字段值。

而 MySQL 5.6 引入的索引下推優(yōu)化（index condition pushdown)， 可以在索引遍歷過(guò)程中，對(duì)索引中包含的字段先做判斷，直接過(guò)濾掉不滿足條件的記錄，減少回表次數(shù)。

圖 3 和圖 4，是這兩個(gè)過(guò)程的執(zhí)行流程圖。

image.png

image.png

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

圖 3 中，在 (name,age) 索引里面我特意去掉了 age 的值，這個(gè)過(guò)程 InnoDB 并不會(huì)去看 age 的值，只是按順序把“name 第一個(gè)字是’張’”的記錄一條條取出來(lái)回表。因此，需要回表 4 次。

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

小結(jié)

今天這篇文章，我和你繼續(xù)討論了數(shù)據(jù)庫(kù)索引的概念，包括了覆蓋索引、前綴索引、索引下推。你可以看到，在滿足語(yǔ)句需求的情況下， 盡量少地訪問(wèn)資源是數(shù)據(jù)庫(kù)設(shè)計(jì)的重要原則之一。我們?cè)谑褂脭?shù)據(jù)庫(kù)的時(shí)候，尤其是在設(shè)計(jì)表結(jié)構(gòu)時(shí)，也要以減少資源消耗作為目標(biāo)。

接下來(lái)我給你留下一個(gè)問(wèn)題吧。

實(shí)際上主鍵索引也是可以使用多個(gè)字段的。DBA 小呂在入職新公司的時(shí)候，就發(fā)現(xiàn)自己接手維護(hù)的庫(kù)里面，有這么一個(gè)表，表結(jié)構(gòu)定義類似這樣的：

CREATE TABLE `geek` (
  `a` int(11) NOT NULL,
  `b` int(11) NOT NULL,
  `c` int(11) NOT NULL,
  `d` int(11) NOT NULL,
  PRIMARY KEY (`a`,`b`),
  KEY `c` (`c`),
  KEY `ca` (`c`,`a`),
  KEY `cb` (`c`,`b`)
) ENGINE=InnoDB;

公司的同事告訴他說(shuō)，由于歷史原因，這個(gè)表需要 a、b 做聯(lián)合主鍵，這個(gè)小呂理解了。

但是，學(xué)過(guò)本章內(nèi)容的小呂又納悶了，既然主鍵包含了 a、b 這兩個(gè)字段，那意味著單獨(dú)在字段 c 上創(chuàng)建一個(gè)索引，就已經(jīng)包含了三個(gè)字段了呀，為什么要?jiǎng)?chuàng)建“ca”“cb”這兩個(gè)索引？

同事告訴他，是因?yàn)樗麄兊臉I(yè)務(wù)里面有這樣的兩種語(yǔ)句：

select * from geek where c=N order by a limit 1;
select * from geek where c=N order by b limit 1;

我給你的問(wèn)題是，這位同事的解釋對(duì)嗎，為了這兩個(gè)查詢模式，這兩個(gè)索引是否都是必須的？為什么呢？

上期的問(wèn)題是，通過(guò)兩個(gè) alter 語(yǔ)句重建索引 k，以及通過(guò)兩個(gè) alter 語(yǔ)句重建主鍵索引是否合理。

有同學(xué)問(wèn)到為什么要重建索引。我們文章里面有提到，索引可能因?yàn)閯h除，或者頁(yè)分裂等原因，導(dǎo)致數(shù)據(jù)頁(yè)有空洞，重建索引的過(guò)程會(huì)創(chuàng)建一個(gè)新的索引，把數(shù)據(jù)按順序插入，這樣頁(yè)面的利用率最高，也就是索引更緊湊、更省空間。

重建索引 k 的做法是合理的，可以達(dá)到省空間的目的。但是，重建主鍵的過(guò)程不合理。不論是刪除主鍵還是創(chuàng)建主鍵，都會(huì)將整個(gè)表重建。所以連著執(zhí)行這兩個(gè)語(yǔ)句的話，第一個(gè)語(yǔ)句就白做了。這兩個(gè)語(yǔ)句，你可以用這個(gè)語(yǔ)句代替 ： alter table T engine=InnoDB

FAQ

今天這個(gè) alter table T engine=InnoDB 讓我想到了我們線上的一個(gè)表, 記錄日志用的, 會(huì)定期刪除過(guò)早之前的數(shù)據(jù). 最后這個(gè)表實(shí)際內(nèi)容的大小才10G, 而他的索引卻有30G. 在阿里云控制面板上看,就是占了40G空間. 這可花的是真金白銀啊.
后來(lái)了解到是 InnoDB 這種引擎導(dǎo)致的,雖然刪除了表的部分記錄,但是它的索引還在, 并未釋放.
只能是重新建表才能重建索引.
如果當(dāng)時(shí)看到了這個(gè)專欄,把這個(gè)語(yǔ)句拿來(lái)用,就可以省下不少錢了.

老師， 因?yàn)檎牟荒軣o(wú)限細(xì)節(jié)和篇幅的緣故， 有些細(xì)節(jié)點(diǎn)沒(méi)有說(shuō)， 我也一直很困惑， 希望能幫忙解答下，辛苦了
1. 表的邏輯結(jié)構(gòu) ，表 —> 段 —> 段中存在數(shù)據(jù)段(leaf node segment) ，索引段( Non-leaf node segment）,請(qǐng)問(wèn)數(shù)據(jù)段就是主鍵索引的數(shù)據(jù)， 索引段就是二級(jí)索引的數(shù)據(jù)么
2. 建立的每個(gè)索引都有要維護(hù)一個(gè)數(shù)據(jù)段么 ？？ 那么新插入一行值 ， 豈不是每個(gè)索引段都會(huì)維護(hù)這個(gè)值
3. 索引的n階表示n個(gè)數(shù)據(jù)頁(yè)么。那是不是插入第一行數(shù)據(jù)樹(shù)高1 ，就是一個(gè)數(shù)據(jù)頁(yè)， 插入二三行，樹(shù)高是二，那就是兩個(gè)數(shù)據(jù)頁(yè)，而且B+樹(shù)只有l(wèi)eaf node存數(shù)據(jù)，所以父節(jié)點(diǎn)實(shí)際上有沒(méi)有數(shù)據(jù)，但是占一個(gè)頁(yè) ，好浪費(fèi) ， 是我理解有誤么
4. 樹(shù)高取決于數(shù)據(jù)頁(yè)的大小么 ， 這個(gè)不是很能理解 ，數(shù)據(jù)頁(yè)為16k 。 那么樹(shù)高不是一個(gè)定值了么，難道還和里面存數(shù)據(jù)的大小有關(guān)么
5. 查詢數(shù)據(jù)的時(shí)候，大致的流程細(xì)化來(lái)說(shuō) ,我這么理解對(duì)么 。 通過(guò)優(yōu)化器到表里的數(shù)據(jù)段／索引段取數(shù)據(jù) ，數(shù)據(jù)是按照段->區(qū)->頁(yè)維度去取 ， 取完后先放到數(shù)據(jù)緩沖池中，再通過(guò)二分法查詢?nèi)~結(jié)點(diǎn)的有序鏈表數(shù)組找到行數(shù)據(jù)返回給用戶 。 當(dāng)數(shù)據(jù)量大的時(shí)候，會(huì)存在不同的區(qū)，取范圍值的時(shí)候會(huì)到不同的區(qū)取頁(yè)的數(shù)據(jù)返回用戶。

1. 這樣理解也算對(duì)，不過(guò)要記得 主鍵也是索引的一種哈
2. 是的，所以說(shuō)索引越多，“維護(hù)成本”越大
3. 如果是幾百個(gè)兒子節(jié)點(diǎn)共用一個(gè)父節(jié)點(diǎn)，是不是就不會(huì)看上去那么浪費(fèi)啦
4. 樹(shù)高其實(shí)取決于葉子樹(shù)（數(shù)據(jù)行數(shù)）和“N叉樹(shù)”的N。 而N是由頁(yè)大小和索引大小決定的。
5. 基本是你說(shuō)的流程。不過(guò)不是“優(yōu)化器”去取的，是執(zhí)行器調(diào)用引擎，引擎內(nèi)部才管理了你說(shuō)的 段、頁(yè)這些數(shù)據(jù)

踩過(guò)坑：有人問(wèn)我聯(lián)合索引的技巧，回答的不是很好
總結(jié)：
1、覆蓋索引：如果查詢條件使用的是普通索引（或是聯(lián)合索引的最左原則字段），查詢結(jié)果是聯(lián)合索引的字段或是主鍵，不用回表操作，直接返回結(jié)果，減少IO磁盤讀寫讀取正行數(shù)據(jù)
2、最左前綴：聯(lián)合索引的最左 N 個(gè)字段，也可以是字符串索引的最左 M 個(gè)字符
3、聯(lián)合索引：根據(jù)創(chuàng)建聯(lián)合索引的順序，以最左原則進(jìn)行where檢索，比如（age，name）以age=1 或 age= 1 and name=‘張三’可以使用索引，單以name=‘張三’ 不會(huì)使用索引，考慮到存儲(chǔ)空間的問(wèn)題，還請(qǐng)根據(jù)業(yè)務(wù)需求，將查找頻繁的數(shù)據(jù)進(jìn)行靠左創(chuàng)建索引。
4、索引下推：like 'hello%’and age >10 檢索，MySQL5.6版本之前，會(huì)對(duì)匹配的數(shù)據(jù)進(jìn)行回表查詢。5.6版本后，會(huì)先過(guò)濾掉age<10的數(shù)據(jù)，再進(jìn)行回表查詢，減少回表率，提升檢索速度

先回答老師的問(wèn)題:
如果c列上重復(fù)率很低的情況下,兩個(gè)索引都可以不用建。因?yàn)槿绻^(guò)濾只剩下幾條數(shù)據(jù),排序也不影響
如果C列重復(fù)度比較高,就需要建立(c,b)的聯(lián)合索引了,來(lái)消除排序了。因?yàn)樵跀?shù)據(jù)量大的情況下,排序是一個(gè)非常耗時(shí)的操作,
很有可能還需要磁盤臨時(shí)表來(lái)做排序。而且如果沒(méi)有(c,b)聯(lián)合索引,limit 1僅僅表示返回給客戶端一條數(shù)據(jù),沒(méi)有起到限制掃描行數(shù)的作用
ca列上的索引,由于滿足最左前綴,不用加。因?yàn)閏是固定值,那么a列就是有序的.那么這里limit 1就很好限制了只用精準(zhǔn)掃描一條數(shù)據(jù).
所以有時(shí)候如果在where條件建立索引的效率差的情況下,在order by limit這一列建索引也是很好的方案,排好序,在回表,只要過(guò)濾出滿足條件的limit行,就能及時(shí)停止掃描
老師我有幾個(gè)問(wèn)題:
1.using where的時(shí)候,需要回表,然后把數(shù)據(jù)傳輸給server層,server層來(lái)過(guò)濾數(shù)據(jù)。那么這些數(shù)據(jù)是存在server層的哪個(gè)地方呢？
2.limit起到限制掃描行數(shù)作用并且有using where的時(shí)候,limit這個(gè)操作時(shí)在存儲(chǔ)引擎層做的還是在server層做的？
3.ICP是不是做得不太好,感覺(jué)很多地方?jīng)]有用到索引下推,都會(huì)顯示using index condition

回答得很好。
1. 沒(méi)有存，就是一個(gè)臨時(shí)內(nèi)存，讀出來(lái)馬上判斷，然后掃描下一行可以復(fù)用
2. Server層。 接上面的邏輯，讀完以后順便判斷一下夠不夠limit 的數(shù)了，夠就結(jié)束循環(huán)
3. 嗯，你很細(xì)心，其實(shí)它表示的是“可以下推”，實(shí)際上是“可以，但沒(méi)有”??