學(xué)習(xí)筆記

InnoDB會(huì)把主鍵字段放到索引定義字段后面

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

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

SQL 查詢(xún)語(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，不滿(mǎn)足條件，循環(huán)結(jié)束。

在這個(gè)過(guò)程中，回到主鍵索引樹(shù)搜索的過(guò)程，我們稱(chēng)為回表。可以看到，這個(gè)查詢(xún)過(guò)程讀了 k 索引樹(shù)的 3 條記錄（步驟 1、3 和 5），回表了兩次（步驟 2 和 4）。

在這個(gè)例子中，由于查詢(xún)結(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ù)上了，因此可以直接提供查詢(xún)結(jié)果，不需要回表。也就是說(shuō)，在這個(gè)查詢(xún)里面，索引 k 已經(jīng)“覆蓋了”我們的查詢(xún)需求，我們稱(chēng)為覆蓋索引。

由于覆蓋索引可以減少樹(shù)的搜索次數(shù)，顯著提升查詢(xún)性能，所以使用覆蓋索引是一個(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)查詢(xún)市民信息的需求，我們只要在身份證號(hào)字段上建立索引就夠了。而再建立一個(gè)（身份證號(hào)、姓名）的聯(lián)合索引，是不是浪費(fèi)空間？

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

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

最左前綴原則
看到這里你一定有一個(gè)疑問(wèn)，如果為每一種查詢(xún)都設(shè)計(jì)一個(gè)索引，索引是不是太多了。如果我現(xiàn)在要按照市民的身份證號(hào)去查他的家庭地址呢？雖然這個(gè)查詢(xún)需求在業(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，然后向后遍歷，直到不滿(mǎn)足條件為止。

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

基于上面對(duì)最左前綴索引的說(shuō)明，討論一個(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)先考慮采用的。

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

那么，如果既有聯(lián)合查詢(xún)，又有基于 a、b 各自的查詢(xún)呢？查詢(xún)條件里面只有 b 的語(yǔ)句，是無(wú)法使用 (a,b) 這個(gè)聯(lián)合索引的，這時(shí)候你不得不維護(hù)另外一個(gè)索引，也就是說(shuō)你需要同時(shí)維護(hù) (a,b)、(b) 這兩個(gè)索引。

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

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

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

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

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

然后判斷其他條件是否滿(mǎn)足。

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

執(zhí)行流程

image.png

image.png

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