1. 引言

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

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 TVALUES(100, 1, 'aa'),(200, 2, 'bb'),(300, 3, 'cc'),(500, 5, 'ee'),(600, 6, 'ff');

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

在 k 索引樹上找到 k=3 的記錄，取得 ID = 300；

再到 ID 索引樹查到 ID=300 對(duì)應(yīng)的 R3；

在 k 索引樹取下一個(gè)值 k=5，取得 ID=500；

再回到 ID 索引樹查到 ID=500 對(duì)應(yīng)的 R4；

在 k 索引樹取下一個(gè)值 k=6，不滿足條件，循環(huán)結(jié)束。

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

2. 覆蓋索引

??如果執(zhí)行的語(yǔ)句是 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 層來(lái)說，它就是找引擎拿到了兩條記錄，因此 MySQL 認(rèn)為掃描行數(shù)是 2。

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

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)求上用到覆蓋索引，不再需要回表查整行記錄，減少語(yǔ)句的執(zhí)行時(shí)間。

3. 最左前綴原則

??單獨(dú)為一個(gè)不頻繁的請(qǐng)求創(chuàng)建一個(gè)（身份證號(hào)，地址）的索引又感覺有點(diǎn)浪費(fèi)。應(yīng)該怎么做呢？
??這里，我先和你說結(jié)論吧。B+ 樹這種索引結(jié)構(gòu)，可以利用索引的“最左前綴”，來(lái)定位記錄。
??為了直觀地說明這個(gè)概念，我們用（name，age）這個(gè)聯(lián)合索引來(lái)分析。

索引項(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ì)最左前綴索引的說明，我們來(lái)討論一個(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)先考慮采用的。
??那么，如果既有聯(lián)合查詢，又有基于 a、b 各自的查詢呢？查詢條件里面只有 b 的語(yǔ)句，是無(wú)法使用 (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) 的單字段索引。

4. 索引下推

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

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

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


??圖 3 中，在 (name,age) 索引里面我特意去掉了 age 的值，這個(gè)過程 InnoDB 并不會(huì)去看age 的值，只是按順序把“name 第一個(gè)字是’張’”的記錄一條條取出來(lái)回表。因此，需要回表 4 次。
??圖 4 跟圖 3 的區(qū)別是，InnoDB 在 (name,age) 索引內(nèi)部就判斷了 age 是否等于 10，對(duì)于不等于 10 的記錄，直接判斷并跳過。在我們的這個(gè)例子中，只需要對(duì) ID4、ID5 這兩條記錄回表取數(shù)據(jù)判斷，就只需要回表 2 次。

總結(jié)

以上是生活随笔為你收集整理的05丨深入浅出索引（下）的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。