一篇文章講清楚MySQL的聚簇/聯合/覆蓋索引、回表、索引下推

一燈架構發表於2022-06-25
迎面走來了你的面試官,身穿格子衫,挺著啤酒肚,髮際線嚴重後移的中年男子。
手拿泡著枸杞的保溫杯,胳膊夾著MacBook,MacBook上還貼著公司標語:“加班使我快樂”。

程式設計師髮型2.jpg

面試官: 看你簡歷上用過MySQL,問你幾個簡單的問題吧。什麼是聚簇索引和非聚簇索引?

這個問題難不住我啊。來之前我看一下一燈MySQL八股文。

我: 舉個例子:有這麼一張使用者表

CREATE TABLE `user` (
  `id` int COMMENT '主鍵ID',
  `name` varchar(10) COMMENT '姓名',
  `age` int COMMENT '年齡',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB CHARSET=utf8 COMMENT='使用者表';

使用者表中儲存了這些資料:

idnaneage
1一燈18
2張三22
3李四21
4王二19
5麻子20

那麼在索引中,這些資料是怎麼儲存的呢?

MySQL的InnoDB引擎中索引使用的B+樹結構。

image-20220504221759177.png

別問為什麼根節點儲存了(1,4)兩個元素,左子節點又儲存了(1,2,3)三個元素,下面帶有三個葉子節點,葉子節點之間又用有序連結串列相連?

問就是B+樹的特性,不瞭解的可以翻一下上期的文章。

如上圖所示,葉子節點中儲存了全部元素的索引,就是聚簇索引
一般主鍵索引就是聚簇索引,如果表中沒有主鍵,MySQL也會預設建立一個隱藏主鍵做主鍵索引。

什麼是非聚簇索引?

假設我們在age(年齡)欄位上建一個普通索引,age欄位上面的索引儲存結構就是下面這樣:

image-20220504221847348.png

葉子節點中只儲存了當前索引欄位和主鍵ID,這樣的儲存結構就是非聚簇索引。

面試官: 那什麼是聯合索引呢?

我: 有多個欄位組成的索引就是聯合索引。

面試官: 【暈】建聯合索引有什麼好處?它跟在單個欄位上建索引有什麼區別?

我: 假設有這麼一條查詢語句。

select * from user where age = 18 and name = '張三';

如果我們在age和name欄位上分別建兩個索引,這個查詢語句只會用到其中一個索引。

但是我們在age和name欄位建一個聯合索引(age,name),它的儲存結構就變成這樣了。

image-20220504220728526.png

如果只在age上面建索引,會先查詢age上面非聚簇索引,有三條age=18的記錄,主鍵ID分別是1、4、5,然後再用這三個ID去查詢主鍵ID的聚簇索引。

如果在age和name上面建聯合索引,會先查詢age和name上面的非聚簇索引,匹配到一條記錄,主鍵ID是1,然後再用這個ID去查詢主鍵ID的聚簇索引。

由此可以得出,聯合索引的優點:大大減少掃描行數。

面試官: 你再說一下什麼是最左匹配原則?

我: 最左匹配原則是指在建立聯合索引的時候,遵循最左優先,以最左邊的為起點任何連續的索引都能匹配上。

當我們在(age,name)上建立聯合索引的時候,where條件中只有age可以用到索引,同時有age和name也可以用到索引。但是隻有name的時候是無法用到索引的。

為什麼會出現這種情況呢?

看上面的圖,就理解了,(age,name)的聯合索引,是先按照age排序,age相等的行再按照name排序。如果where條件只有一個name,當然無法用到索引。

面試官: 什麼是覆蓋索引和回表查詢?

我: 這個就更簡單了,上面已經提到這個知識點了。

image-20220504221929024.png

當我們在age上建索引的時候,查詢SQL是這樣的時候:

select id from user where age = 18;

就會用到覆蓋索引,因為ID欄位我們使用age索引的時候已經查出來,不需要再二次回表查詢了。

但是當查詢SQL是這樣的時候:

select * from user where age = 18;

想要查詢所有欄位,就需要二次回表查詢。因為我們第一次用age索引的時候只查出來了主鍵ID,還需要再用主鍵ID回表查詢出所有欄位。

面試官: 再問一個,你知道什麼是索引下推嗎?

這麼冷門的問題,你都問的出來,真的要面試造火箭啊!

我: 索引下推(Index Condition Pushdown)是MySQL5.6引入的一個優化索引的特性。

舉例:

在(age,name)上面建聯合索引,並且查詢SQL是這樣的時候:

select * from user where age = 18 and name = '張三';

image-20220504224330386.png

如果沒有索引下推,會先匹配出 age = 18 的三條記錄,再用ID回表查詢,篩選出 name = '張三' 的記錄。

如果使用索引下推,會先匹配出 age = 18 的三條記錄,再篩選出 name = '張三' 的一條記錄,最後再用ID回表查詢。

由此得出,索引下推的優點:減少了回表的掃描行數。

面試官: 小夥子,八股文背的挺溜啊。我給你出個實戰題,看你有沒有準備。下面這個查詢SQL該怎麼建聯合索引?

select a from table where b = 1 and c = 2;
故意刁難我?你以為實戰題就不能背八股文了嗎?

我: 剛才在講聯合索引的時候已經說了這個知識點了,where條件有b和c的等值查詢,聯合索引就建成(b,c),由於select後面有a,我們就建立 (b,c,a) 的聯合索引,並且可以用到覆蓋索引,查詢速度更快。

面試官: 小夥子,有點東西。一會兒就給你發offer,明天就來上班,薪資double。

文章持續更新,可以微信搜一搜「 一燈架構 」第一時間閱讀更多技術乾貨。

相關文章