一蝴罪、前言

最近小農(nóng)在找工作，因?yàn)榻衲暌咔榈奶厥庠虿角澹瑢?dǎo)致工作不是特別好找要门，所以一旦有面試電話，如果可以廓啊，都會(huì)去試一試欢搜，剛好接到一個(gè)面試邀請(qǐng)，感覺(jué)公司還不錯(cuò)谴轮，于是就確定了面試時(shí)間炒瘟，準(zhǔn)備了一下就去面試了。

第一輪面試是小組組長(zhǎng)面試第步，通過(guò)疮装。
第二輪是經(jīng)理面試也是通過(guò)了。
第三輪總監(jiān)面試粘都，前面都還有模有樣廓推，突然畫(huà)風(fēng)一轉(zhuǎn)，面試官說(shuō)：“問(wèn)你最后一個(gè)問(wèn)題”

面試官：10W條數(shù)據(jù)翩隧，我要從其中查出100條不連續(xù)的數(shù)據(jù)受啥，給你id，來(lái)查name和password進(jìn)行展示,如何才能高性能的去使用鸽心？

我：在id上建立聚簇索引滚局，然后用 in id 來(lái)縮小表搜索范圍，最后 使用條件查詢 小于最大id顽频，大于最小id藤肢，這樣可以讓sql速度能夠比較快的展示，雖然In的性能比較低
心里活動(dòng)：雕蟲(chóng)小技糯景，還最后一個(gè)問(wèn)題嘁圈，這樣的問(wèn)題再來(lái)一個(gè)吧

只見(jiàn)面試官緊鎖眉頭省骂，與我心里期待的表情有點(diǎn)不一樣啊，難道是哪個(gè)環(huán)節(jié)出了問(wèn)題最住？
面試官：這樣的性能不能達(dá)到最優(yōu)化的程度钞澳，而且如果我給你的最小id是1，最大id是100000呢涨缚？

你這就有點(diǎn)杠精了啊轧粟，那行吧，你是面試官你說(shuō)了算
我：既然id已經(jīng)給出來(lái)了脓魏，而且只查詢兩個(gè)字段兰吟，用聚簇索引那么查詢數(shù)據(jù)是很快的，用in id應(yīng)該是可以的茂翔。

面試官：好的混蔼，回去等通知吧
我。珊燎。惭嚣。。悔政。

二料按、后知

于是回去后，查詢資料卓箫，才知道原來(lái)面試官载矿，真正想考的是 “覆蓋索引”

什么是覆蓋索引：

當(dāng)sql語(yǔ)句的所求查詢字段（select列）和查詢條件字段（where子句）全都包含在一個(gè)索引中 （聯(lián)合索引），可以直接使用索引查詢而不需要回表烹卒。這就是覆蓋索引闷盔，通過(guò)使用覆蓋索引，可以減少搜索樹(shù)的次數(shù)旅急，這就是 覆蓋索引逢勾，在了解覆蓋索引之前，我們先來(lái)看看什么是索引藐吮。

三溺拱、什么是索引？

我們有一個(gè)主鍵列為id的表谣辞，表中有字段name迫摔，并且在name上有索引

表中 t_user 值分別為(1，張一)泥从、(2句占，張二)、(3躯嫉，張三)纱烘、(4杨拐，張四)、(5擂啥，張五)

表結(jié)構(gòu)如下：

mysql> create table t_user (
id bigint(20) not null auto_increment ,
name varchar(255) not null,
primary key (id),
index index_name (name) using btree)
engine=innodb
default character set=utf8 collate=utf8_general_ci

兩棵樹(shù)的示例示意圖如下：

在這里插入圖片描述

從圖中不難看出哄陶，根據(jù)葉子節(jié)點(diǎn)的內(nèi)容，索引類(lèi)型分為主鍵索引和二級(jí)索引（非主鍵索引）哺壶。

主鍵索引： 主鍵索引的葉子節(jié)點(diǎn)保存著主鍵即對(duì)應(yīng)行的全部數(shù)據(jù)屋吨。在InnoDB里，主鍵索引也被稱為聚簇索引（clustered index）变骡。

二級(jí)索引（非主鍵索引）： 二級(jí)索引樹(shù)中的葉子結(jié)點(diǎn)保存著索引值和主鍵值，當(dāng)使用二級(jí)索引進(jìn)行查詢時(shí)芭逝，需要進(jìn)行回表操作塌碌。在InnoDB里，非主鍵索引也被稱為二級(jí)索引（secondary index）

通過(guò)上面所講的旬盯，我們來(lái)看看如何通過(guò)sql語(yǔ)句來(lái)區(qū)分 主鍵索引和普通索引的查詢

• select * from t_user where id=1 即主鍵查詢方式台妆，則只需要搜索id這棵B+樹(shù)
• select * from t_user where name=張三 即普通索引查詢方式，則需要先搜索name索引樹(shù)胖翰，得到id的值為3接剩，再到id索引樹(shù)搜索一次。這個(gè)過(guò)程稱為回表

也就是說(shuō)萨咳，基于二級(jí)索引（非主鍵索引）的查詢需要多掃描一棵索引樹(shù)懊缺。因此，我們?cè)趹?yīng)用中應(yīng)該盡量使用主鍵查詢培他。

看到這里如果你看懂了上面的介紹鹃两，那么這里你會(huì)有一個(gè)疑問(wèn)，我直接用in id不就好了嗎舀凛，建立id主鍵索引俊扳，就可以不用回表了，速度不也就提升了嗎猛遍？

如果是 5.5 之前的版本確實(shí)不會(huì)走索引的馋记，在 5.5 之后的版本，MySQL 做了優(yōu)化懊烤。MySQL 在 2010 年發(fā)布 5.5 版本中梯醒，優(yōu)化器對(duì) in 操作符可以自動(dòng)完成優(yōu)化，針對(duì)建立了索引的列可以使用索引腌紧，沒(méi)有索引的列還是會(huì)走全表掃描冤馏，也就是我們所說(shuō)的回表。

那么寄啼，有沒(méi)有可能經(jīng)過(guò)索引優(yōu)化逮光，避免回表過(guò)程呢代箭？答應(yīng)是有的

四、覆蓋索引

sql語(yǔ)句如下涕刚，其中id自增嗡综，name為索引：

mysql> create table t_user (
id bigint(20) not null auto_increment ,
name varchar(255) not null,
password varchar(255) ,

                primary key (id),
                engine=innodb
                default character set=utf8 collate=utf8_general_ci

比如有這么兩句sql

語(yǔ)句A： select id from user_table where name= '張三'
語(yǔ)句B： select password from user_table where name= '張三'

語(yǔ)句A： 因?yàn)? name索引樹(shù) 的葉子結(jié)點(diǎn)上保存有 name和id的值 ，所以通過(guò) name索引樹(shù) 查找到id后杜漠，因此可以直接提供查詢結(jié)果极景，不需要回表，也就是說(shuō)驾茴，在這個(gè)查詢里面盼樟，索引name 已經(jīng) “覆蓋了” 我們的查詢需求，我們稱為 覆蓋索引

語(yǔ)句B： name索引樹(shù) 上 找到 name='張三' 對(duì)應(yīng)的主鍵id, 通過(guò)回表在主鍵索引樹(shù)上找到滿足條件的數(shù)據(jù)

因此我們可以得知锈至，當(dāng)sql語(yǔ)句的所求查詢字段（select列）和查詢條件字段（where子句）全都包含在一個(gè)索引中（聯(lián)合索引）晨缴，可以直接使用索引查詢而不需要回表。這就是覆蓋索引峡捡。

例如上面的語(yǔ)句B是一個(gè)高頻查詢的語(yǔ)句击碗，我們可以建立(name,password)的聯(lián)合索引，這樣们拙，查詢的時(shí)候就不需要再去回表操作了稍途，可以提高查詢效率。

所以關(guān)于上面的面試題我們就可以得出砚婆，使用聯(lián)合索引就可以很好的回答面試官的問(wèn)題（id,name,password）這樣的聯(lián)合索引就可以調(diào)用到覆蓋索引械拍，可以減少樹(shù)的搜索次數(shù)，不再需要回表查整行記錄装盯，顯著提升查詢性能殊者，所以使用覆蓋索引是一個(gè)常用的性能優(yōu)化手段。

說(shuō)到了聯(lián)合索引我們就不得不說(shuō)聯(lián)合索引中最重要的匹配原則验夯，最左匹配原則了

五猖吴、最左匹配原則

最左前綴匹配原則，是非常重要的原則挥转，mysql會(huì)從左向右進(jìn)行匹配海蔽。

例如我們定義了(name,password)兩個(gè)聯(lián)合索引字段，我們 使用 where name = '張三' and password = '2'索引可以生效的绑谣，當(dāng)我們是顛倒了他們的順序 使用where password = '1' and name = '王五'党窜，索引同樣也是可以生效的，在mysql查詢優(yōu)化器會(huì)判斷糾正這條sql語(yǔ)句該以什么樣的順序執(zhí)行效率最高借宵，最后才生成真正的執(zhí)行計(jì)劃幌衣，我們能盡量的利用到索引時(shí)的查詢順序效率最高，所以mysql查詢優(yōu)化器會(huì)最終以這種順序（where name = '張三' and password = '2'）進(jìn)行查詢執(zhí)行，就類(lèi)似 我們的 order by name,password這樣一種排序規(guī)則豁护，先對(duì)張三的用戶進(jìn)行查詢排序哼凯，在對(duì)password進(jìn)行處理

在這里插入圖片描述

比如我們要查詢姓張的用戶，我們的條件查詢可以為 "where name like ‘張%’"楚里，但是不能是 where name like '%張%'或者是 where name like '%張'断部，因?yàn)樗饕梢杂糜诓樵儣l件字段為索引字段，根據(jù)字段值必須是最左若干個(gè)字符進(jìn)行的模糊查詢班缎，也就是需要是 '張%' 這樣的添加才可以使用蝴光。

索引的復(fù)用能力。因?yàn)榭梢灾С肿钭笄熬Y达址，所以當(dāng)已經(jīng)有了(name,password)這個(gè)聯(lián)合索引后蔑祟，一般就不需要單獨(dú)在name上建立索引了。因此沉唠，第一原則是疆虚，如果通過(guò)調(diào)整順序，可以少維護(hù)一個(gè)索引右冻，那么這個(gè)順序往往就是需要優(yōu)先考慮采用的装蓬。

如果既有聯(lián)合查詢著拭，又有基于name,password各自的查詢呢纱扭？查詢條件里面只有password的語(yǔ)句，是無(wú)法使用(name,password)這個(gè)聯(lián)合索引的儡遮，這時(shí)候你需要同時(shí)維護(hù)(name,password)乳蛾、(password) 這兩個(gè)索引。

創(chuàng)建索引時(shí)鄙币，我們也要考慮空間代價(jià)肃叶，使用較少的空間來(lái)創(chuàng)建索引
假設(shè)我們現(xiàn)在不需要通過(guò)name查詢password了，需要通過(guò)name查詢age或通過(guò)age查詢name

• 1.（name,age）聯(lián)合索引+age單字段索引
• 2.（age,name）聯(lián)合索引+name單字段索引

name字段是比age字段大的十嘿，所以因惭，選擇第一種，索引占用空間較小的一個(gè)

六绩衷、索引下推

上面我們說(shuō)到滿足最左前綴原則的時(shí)候蹦魔，最左前綴可以用于在索引中定位記錄。那么如果那些不符合最左前綴的部分咳燕，會(huì)怎么樣呢勿决？

如果現(xiàn)在有一個(gè)需求：檢索出表中“名字第一個(gè)字是張，而且沒(méi)有刪除的信息(is_del = 1)招盲。SQL語(yǔ)句如下：

mysql> select * from t_user where name like '張%' and is_del=1

在MySQL 5.6之前低缩，只能從匹配的位置一個(gè)個(gè)回表。到主鍵索引上找出數(shù)據(jù)行曹货，再對(duì)比字段值

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

根據(jù)（username,is_del）聯(lián)合索引查詢所有滿足名稱以“張”開(kāi)頭的索引娜遵，然后回表查詢出相應(yīng)的全行數(shù)據(jù)，然后再篩選出未刪除的用戶數(shù)據(jù)壤短。過(guò)程如下圖：

每一個(gè)虛線箭頭表示回表一次
圖一（無(wú)索引下推執(zhí)行流程）

圖一

每一個(gè)虛線箭頭表示回表一次
圖二（索引下推執(zhí)行流程）

在這里插入圖片描述

圖1跟圖2的區(qū)別是设拟，InnoDB在(name,is_del)索引內(nèi)部就判斷了數(shù)據(jù)是否邏輯刪除，對(duì)于邏輯刪除的記錄久脯，直接判斷并跳過(guò)纳胧。在我們的這個(gè)例子中，只需要對(duì)ID1帘撰、ID4這兩條記錄回表取數(shù)據(jù)判斷跑慕，就只需要回表2次

mysql默認(rèn)啟用索引下推，我們也可以通過(guò)修改系統(tǒng)變量optimizer_switch的index_condition_pushdown標(biāo)志來(lái)控制SET optimizer_switch = 'index_condition_pushdown=off';

我們也需要注意：

• innodb引擎的表摧找，索引下推只能用于二級(jí)索引核行，因?yàn)閕nnodb的主鍵索引樹(shù)葉子結(jié)點(diǎn)上保存的是全行數(shù)據(jù)，所以這個(gè)時(shí)候索引下推并不會(huì)起到減少查詢?nèi)袛?shù)據(jù)的效果
• 索引下推一般可用于所求查詢字段（select列）不是/不全是聯(lián)合索引的字段蹬耘，查詢條件為多條件查詢且查詢條件子句（where/order by）字段全是聯(lián)合索引

六芝雪、小結(jié)

今天的內(nèi)容就到這里了，我們?cè)谏厦婷枋隽藬?shù)據(jù)庫(kù)索引的概念综苔，包括了覆蓋索引惩系、聯(lián)合索引、索引下推如筛，那么下次如果有面試官問(wèn)你剛開(kāi)始的問(wèn)題堡牡，相信大家可以好好的回（dui）答（ta）一下面試官了，在sql優(yōu)化中杨刨，減少回表次數(shù)晤柄，或者直接使用覆蓋索引是比較重要的，盡量少地訪問(wèn)資源也是數(shù)據(jù)庫(kù)設(shè)計(jì)的重要原則之一妖胀，謝謝大家芥颈，加油~