一、什么是回表查詢？

通俗的講就是，如果索引的列在 select 所需獲得的列中（因為在 mysql 中索引是根據(jù)索引列的值進(jìn)行排序的，所以索引節(jié)點中存在該列中的部分值）或者根據(jù)一次索引查詢就能獲得記錄就不需要回表，如果 select 所需獲得列中有大量的非索引列，索引就需要到表中找到相應(yīng)的列的信息，這就叫回表。

InnoDB聚集索引的葉子節(jié)點存儲行記錄，因此， InnoDB必須要有，且只有一個聚集索引：

（1）如果表定義了主鍵，則PK就是聚集索引；
（2）如果表沒有定義主鍵，則第一個非空唯一索引（not NULL unique）列是聚集索引；
（3）否則，InnoDB會創(chuàng)建一個隱藏的row-id作為聚集索引；

先創(chuàng)建一張表，sql 語句如下：

create table xttblog(
    id int primary key, 
    k int not null, 
    name varchar(16),
    index (k)
)engine = InnoDB;

然后，我們再執(zhí)行下面的 SQL 語句，插入幾條測試數(shù)據(jù)。

INSERT INTO xttblog(id, k, name) VALUES(1, 2, 'xttblog'),
    (2, 1, '業(yè)余草'),
    (3, 3, '業(yè)余草公眾號');

假設(shè)，現(xiàn)在我們要查詢出 id 為 2 的數(shù)據(jù)。那么執(zhí)行 select * from xttblog where ID = 2; 這條 SQL 語句就不需要回表。原因是根據(jù)主鍵的查詢方式，則只需要搜索 ID 這棵 B+ 樹。主鍵是唯一的，根據(jù)這個唯一的索引，MySQL 就能確定搜索的記錄。

但當(dāng)我們使用 k 這個索引來查詢 k = 2 的記錄時就要用到回表。select * from xttblog where k = 2; 原因是通過 k 這個普通索引查詢方式，則需要先搜索 k 索引樹，然后得到主鍵 ID 的值為 1，再到 ID 索引樹搜索一次。這個過程雖然用了索引，但實際上底層進(jìn)行了兩次索引查詢，這個過程就稱為回表。

也就是說，基于非主鍵索引的查詢需要多掃描一棵索引樹。因此，我們在應(yīng)用中應(yīng)該盡量使用主鍵查詢。

我這里表里的數(shù)據(jù)量比較少，如果數(shù)據(jù)量大的話，你能很明顯的看出兩次查詢所用的時間，很明顯使用主鍵查詢效率更高。

更多如下圖：

（1）先通過普通索引定位到主鍵值id=5；
（2）在通過聚集索引定位到行記錄；

這就是所謂的回表查詢，先定位主鍵值，再定位行記錄，它的性能較掃一遍索引樹更低。

小總結(jié)

使用聚集索引（主鍵或第一個唯一索引）就不會回表，普通索引就會回表。

二、什么是索引覆蓋？

只需要在一棵索引樹上就能獲取SQL所需的所有列數(shù)據(jù)，無需回表，速度更快。

explain的輸出結(jié)果Extra字段為Using index時，能夠觸發(fā)索引覆蓋。

三、如何實現(xiàn)索引覆蓋？

1、常見的方法是：將被查詢的字段，建立到聯(lián)合索引里去。

例子

create table user (
id int primary key,
name varchar(20),
sex varchar(5),
index(name)
)engine=innodb;

第一個sql：

select id,name from user where name='shenjian';

能夠命中name索引，索引葉子節(jié)點存儲了主鍵id，通過name的索引樹即可獲取id和name，無需回表，符合索引覆蓋，效率較高。

Extra：Using index。

第二個sql：

select id,name,sex from user where name='shenjian';

能夠命中name索引，索引葉子節(jié)點存儲了主鍵id，沒有儲存sex，sex字段必須回表查詢才能獲取到，不符合索引覆蓋，需要再次通過id值掃描聚集索引獲取sex字段，效率會降低。

Extra：Using index condition。

如果把(name)單列索引升級為聯(lián)合索引(name, sex)就不同了。

create table user1 (
id int primary key,
name varchar(20),
sex varchar(5),
index(name, sex)
)engine=innodb;

可以看到：

select id,name ... where name='shenjian';
select id,name,sex ... where name='shenjian';
單列索升級為聯(lián)合索引(name, sex)后，索引葉子節(jié)點存儲了主鍵id，name，sex，都能夠命中索引覆蓋，無需回表。

畫外音，Extra：Using index。

四、哪些場景可以利用索引覆蓋來優(yōu)化SQL？

場景1：全表count查詢優(yōu)化

原表為：
user(PK id, name, sex)；

直接：
select count(name) from user;
不能利用索引覆蓋。

添加索引：
alter table user add key(name);
就能夠利用索引覆蓋提效。

場景2：列查詢回表優(yōu)化

這個例子不再贅述，將單列索引(name)升級為聯(lián)合索引(name, sex)，即可避免回表。

場景3：分頁查詢

將單列索引(name)升級為聯(lián)合索引(name, sex)，也可以避免回表。

五、如何創(chuàng)建有效的索引

如果需要索引很長的字符串，此時需要考慮前綴索引

• 前綴索引即選擇所需字符串的一部分前綴作為索引，這時候，需要引入一個概念叫做索引選擇性，索引選擇性是指不重復(fù)的索引值與數(shù)據(jù)表的記錄總數(shù)的比值，可以看出索引選擇性越高則查詢效率越高，當(dāng)索引選擇性為1時，效率是最高的，但是在這種場景下，很明顯索引選擇性為1的話我們會付出比較高的代價，索引會很大，這時候我們就需要選擇字符串的一部分前綴作為索引，通常情況下一列的前綴作為索引選擇性也是很高的

如何選擇前綴

• 計算該列完整列的選擇性，使得前綴選擇性接近于完整列的選擇性

使用多列索引

• 盡量不要為多列上創(chuàng)建單列索引，因為這樣的情況下最多只能使用一星索引，這樣的話，不如去創(chuàng)建一個全覆蓋索引，在多列上創(chuàng)建單列索引大部分情況下并不能提高 MySQL 的查詢性能，MySQL 5.0 中引入了合并索引，在一定程度上可以表內(nèi)多個單列索引來定位指定的結(jié)果，但是 5.0 以前的版本，如果 where 中的多個條件是基于多個單列索引，那么 MySQL 是無法使用這些索引的，這種情況下，還不如使用 union

選擇合適的索引列順序

• 經(jīng)驗是將選擇性最高的列放到索引最前列，可以在查詢的時候過濾出更少的結(jié)果集
• 但這樣并不總是最好的，如果考慮到 group by 或者 order by 等情況，再比如考慮到一些特別場景下的 guest 賬號等數(shù)據(jù)情況，上面的經(jīng)驗法則可能就不是最適用的

覆蓋索引

• 所謂覆蓋索引就是指索引中包含了查詢中的所有字段，這種情況下就不需要再進(jìn)行回表查詢了
• MySQL 中只能使用 B-Tree 索引做覆蓋索引，因為哈希索引等都不存儲索引的列的值，覆蓋索引對于 MyISAM 和 InnoDB 都非常有效，可以減少系統(tǒng)調(diào)用和數(shù)據(jù)拷貝等時間
• Tips：減少?select *?操作

使用索引掃描來做排序

• MySQL 生成有序的結(jié)果有兩種方法：通過排序操作，或者按照索引順序掃描；使用排序操作需要占用大量的 CPU 和內(nèi)存資源，而使用?index?性能是很好的，所以，當(dāng)我們查詢有序結(jié)果時，盡量使用索引順序掃描來生成有序結(jié)果集

怎樣保證使用索引順序掃描：

• 索引列順序和 ORDER BY 順序一致
• 所有列的排序方向一致
• 如果關(guān)聯(lián)多表，那么只有當(dāng) ORDER BY 子句引用的字段全部為第一張表時，才能使用索引做排序，限制依然是需要滿足索引的最左前綴要求

壓縮索引

• MyISAM 中使用了前綴壓縮技術(shù)，會減少索引的大小，可以在內(nèi)存中存儲更多的索引，這部分優(yōu)化默認(rèn)也是只針對字符串的，但是可以自定義對整數(shù)做壓縮
• 這個優(yōu)化在一定情況下性能比較好，但是對于某些情況可能會導(dǎo)致更慢，因為前綴壓縮決定了每個關(guān)鍵字都必須依賴于前面的值，所以無法使用二分查找等，只能順序掃描，所以如果查找的是逆序那么性能可能不佳

減少重復(fù)、冗余以及未使用的索引

• MySQL 的唯一限制和主鍵限制都是通過索引實現(xiàn)的，所以不需要在同一列上增加主鍵、唯一限制再創(chuàng)建索引，這樣是重復(fù)索引
• 再舉個例子，如果已經(jīng)創(chuàng)建了索引（A，B），那么再創(chuàng)建索引（A）的話，就屬于重復(fù)索引，因為 MySQL 索引是最左前綴，所以索引（A，B）本身就可以使用索引（A），但是創(chuàng)建索引（B）的話不屬于重復(fù)索引
• 盡量減少新增索引，而應(yīng)該擴(kuò)展已有的索引，因為新增索引可能會導(dǎo)致 INSERT、UPDATE、DELETE 等操作更慢
• 可以考慮刪除沒有使用到的索引，定位未使用的索引，有兩個辦法，在 Percona Server 或者 MariaDB 中打開 userstates 服務(wù)器變量，然后等服務(wù)器運行一段時間后，通過查詢 INFORMATION_SCHEMA.INDEX_STATISTICS 就可以查詢到每個索引的使用頻率

索引和鎖

• InnoDB 支持行鎖和表鎖，默認(rèn)使用行鎖，而 MyISAM 使用的是表鎖，所以使用索引可以讓查詢鎖定更少的行，這樣也會提升查詢的性能，如果查詢中鎖定了1000行，但實際只是用了100行，那么在 5.1 之前都需要提交事務(wù)之后才能釋放這些鎖，5.1 之后可以在服務(wù)器端過濾掉行之后就釋放鎖，不過依然會導(dǎo)致一些鎖沖突

減少索引和數(shù)據(jù)碎片

• 首先我們需要了解一下為什么會產(chǎn)生碎片，比如 InnoDB 刪除數(shù)據(jù)時，這一段空間就會被留空，如果一段時間內(nèi)大量刪除數(shù)據(jù)，就會導(dǎo)致留空的空間比實際的存儲空間還要大，這時候如果進(jìn)行新的插入操作時，MySQL 會嘗試重新使用這部分空間，但是依然無法徹底占用，這樣就會產(chǎn)生碎片
• 產(chǎn)生碎片帶來的后果當(dāng)然是，降低查詢性能，因為這種情況會導(dǎo)致隨機(jī)磁盤訪問
• 可以通過 OPTIMIZE TABLE 或者重新導(dǎo)入數(shù)據(jù)表來整理數(shù)據(jù)

三、什么是索引下推

假設(shè)有這么個需求，查詢表中“名字第一個字是張，性別男，年齡為10歲的所有記錄”。那么，查詢語句是這么寫的：

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

根據(jù)前面說的“最左前綴原則”，該語句在搜索索引樹的時候，只能匹配到名字第一個字是‘張’的記錄（即記錄ID3），接下來是怎么處理的呢？當(dāng)然就是從ID3開始，逐個回表，到主鍵索引上找出相應(yīng)的記錄，再比對age和ismale這兩個字段的值是否符合。

但是！MySQL 5.6引入了索引下推優(yōu)化，可以在索引遍歷過程中，對索引中包含的字段先做判斷，過濾掉不符合條件的記錄，減少回表字?jǐn)?shù)。
下面圖1、圖2分別展示這兩種情況。

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

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

總結(jié)

如果沒有索引下推優(yōu)化（或稱ICP優(yōu)化），當(dāng)進(jìn)行索引查詢時，首先根據(jù)索引來查找記錄，然后再根據(jù)where條件來過濾記錄；在支持ICP優(yōu)化后，MySQL會在取出索引的同時，判斷是否可以進(jìn)行where條件過濾再進(jìn)行索引查詢，也就是說提前執(zhí)行where的部分過濾操作，在某些場景下，可以大大減少回表次數(shù)，從而提升整體性能。文章來源地址http://www.zghlxwxcb.cn/news/detail-730973.html

到了這里，關(guān)于在Mysql中，什么是回表，什么是覆蓋索引，索引下推？的文章就介紹完了。如果您還想了解更多內(nèi)容，請在右上角搜索TOY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！