一、什么是回表查詢耿导？

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

InnoDB聚集索引的葉子節(jié)點(diǎn)存儲(chǔ)行記錄胎撇，因此， InnoDB必須要有殖氏，且只有一個(gè)聚集索引：

（1）如果表定義了主鍵晚树，則PK就是聚集索引；
（2）如果表沒(méi)有定義主鍵雅采，則第一個(gè)非空唯一索引（not NULL unique）列是聚集索引爵憎；
（3）否則慨亲，InnoDB會(huì)創(chuàng)建一個(gè)隱藏的row-id作為聚集索引；

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

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

然后刑棵，我們?cè)賵?zhí)行下面的 SQL 語(yǔ)句，插入幾條測(cè)試數(shù)據(jù)愚铡。

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

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

但當(dāng)我們使用 k 這個(gè)索引來(lái)查詢 k = 2 的記錄時(shí)就要用到回表淮野。select * from xttblog where k = 2; 原因是通過(guò) k 這個(gè)普通索引查詢方式捧书，則需要先搜索 k 索引樹(shù)，然后得到主鍵 ID 的值為 1骤星，再到 ID 索引樹(shù)搜索一次经瓷。這個(gè)過(guò)程雖然用了索引，但實(shí)際上底層進(jìn)行了兩次索引查詢洞难，這個(gè)過(guò)程就稱為回表了嚎。

也就是說(shuō)，基于非主鍵索引的查詢需要多掃描一棵索引樹(shù)廊营。因此歪泳，我們?cè)趹?yīng)用中應(yīng)該盡量使用主鍵查詢。

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

更多如下圖：

image.png

（1）先通過(guò)普通索引定位到主鍵值id=5伶氢；
（2）在通過(guò)聚集索引定位到行記錄；
這就是所謂的回表查詢瘪吏，先定位主鍵值癣防，再定位行記錄，它的性能較掃一遍索引樹(shù)更低掌眠。

小總結(jié)

使用聚集索引（主鍵或第一個(gè)唯一索引）就不會(huì)回表蕾盯，普通索引就會(huì)回表。

二蓝丙、什么是索引覆蓋级遭？

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

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

三丢郊、如何實(shí)現(xiàn)索引覆蓋盔沫？

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

例子

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

第一個(gè)sql：
select id,name from user where name='shenjian';

image.png

Extra：Using index撑蚌。

第二個(gè)sql：
select id,name,sex from user where name='shenjian';

image.png

能夠命中name索引上遥，索引葉子節(jié)點(diǎn)存儲(chǔ)了主鍵id，沒(méi)有儲(chǔ)存sex争涌，sex字段必須回表查詢才能獲取到粉楚，不符合索引覆蓋，需要再次通過(guò)id值掃描聚集索引獲取sex字段亮垫，效率會(huì)降低模软。

Extra：Using index condition。

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

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

image.png

可以看到：

select id,name ... where name='shenjian';
select id,name,sex ... where name='shenjian';
單列索升級(jí)為聯(lián)合索引(name, sex)后燃异，索引葉子節(jié)點(diǎn)存儲(chǔ)了主鍵id，name继蜡，sex回俐，都能夠命中索引覆蓋，無(wú)需回表稀并。

畫(huà)外音仅颇，Extra：Using index。

四碘举、哪些場(chǎng)景可以利用索引覆蓋來(lái)優(yōu)化SQL忘瓦？

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

image

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

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

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

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

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

場(chǎng)景3：分頁(yè)查詢

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

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

如果需要索引很長(zhǎng)的字符串逼泣，此時(shí)需要考慮前綴索引

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

如何選擇前綴

• 計(jì)算該列完整列的選擇性慷蠕，使得前綴選擇性接近于完整列的選擇性

使用多列索引

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

選擇合適的索引列順序

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

覆蓋索引

• 所謂覆蓋索引就是指索引中包含了查詢中的所有字段，這種情況下就不需要再進(jìn)行回表查詢了
• MySQL 中只能使用 B-Tree 索引做覆蓋索引闹击，因?yàn)楣Ｋ饕榷疾淮鎯?chǔ)索引的列的值镶蹋，覆蓋索引對(duì)于 MyISAM 和 InnoDB 都非常有效，可以減少系統(tǒng)調(diào)用和數(shù)據(jù)拷貝等時(shí)間
• Tips：減少 select * 操作

使用索引掃描來(lái)做排序

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

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

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

壓縮索引

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

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

• MySQL 的唯一限制和主鍵限制都是通過(guò)索引實(shí)現(xiàn)的绪商，所以不需要在同一列上增加主鍵苛谷、唯一限制再創(chuàng)建索引辅鲸，這樣是重復(fù)索引
• 再舉個(gè)例子格郁，如果已經(jīng)創(chuàng)建了索引（A，B）独悴，那么再創(chuàng)建索引（A）的話例书，就屬于重復(fù)索引，因?yàn)?MySQL 索引是最左前綴刻炒，所以索引（A决采，B）本身就可以使用索引（A），但是創(chuàng)建索引（B）的話不屬于重復(fù)索引
• 盡量減少新增索引坟奥，而應(yīng)該擴(kuò)展已有的索引树瞭，因?yàn)樾略鏊饕赡軙?huì)導(dǎo)致 INSERT、UPDATE爱谁、DELETE 等操作更慢
• 可以考慮刪除沒(méi)有使用到的索引晒喷，定位未使用的索引，有兩個(gè)辦法访敌，在 Percona Server 或者 MariaDB 中打開(kāi) userstates 服務(wù)器變量凉敲，然后等服務(wù)器運(yùn)行一段時(shí)間后，通過(guò)查詢 INFORMATION_SCHEMA.INDEX_STATISTICS 就可以查詢到每個(gè)索引的使用頻率

索引和鎖

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

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

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

三诸狭、什么是索引下推

假設(shè)有這么個(gè)需求灯萍，查詢表中“名字第一個(gè)字是張拓售，性別男剑令，年齡為10歲的所有記錄”块饺。那么谨究，查詢語(yǔ)句是這么寫(xiě)的：

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

根據(jù)前面說(shuō)的“最左前綴原則”莹汤，該語(yǔ)句在搜索索引樹(shù)的時(shí)候拴袭，只能匹配到名字第一個(gè)字是‘張’的記錄（即記錄ID3）读第，接下來(lái)是怎么處理的呢？當(dāng)然就是從ID3開(kāi)始拥刻，逐個(gè)回表怜瞒，到主鍵索引上找出相應(yīng)的記錄，再比對(duì)age和ismale這兩個(gè)字段的值是否符合般哼。

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

圖1

圖2

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

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

總結(jié)

如果沒(méi)有索引下推優(yōu)化（或稱ICP優(yōu)化）成黄，當(dāng)進(jìn)行索引查詢時(shí)，首先根據(jù)索引來(lái)查找記錄逻杖，然后再根據(jù)where條件來(lái)過(guò)濾記錄奋岁；在支持ICP優(yōu)化后，MySQL會(huì)在取出索引的同時(shí)荸百，判斷是否可以進(jìn)行where條件過(guò)濾再進(jìn)行索引查詢闻伶，也就是說(shuō)提前執(zhí)行where的部分過(guò)濾操作，在某些場(chǎng)景下够话，可以大大減少回表次數(shù)蓝翰，從而提升整體性能。

?