迎面走來了你的面試官，身穿格子衫，挺著啤酒肚，髮際線嚴重後移的中年男子。 手拿泡著枸杞的保溫杯，胳膊夾著MacBook，MacBook上還貼著公司標語：“加班使我快樂”。 面試官： 看你簡歷上用過MySQL，問你幾個簡單的問題吧。什麼是聚簇索引和非聚簇索引？ 這個問題難不住我啊。來之前我看一下一燈M ...
迎面走來了你的面試官，身穿格子衫，挺著啤酒肚，髮際線嚴重後移的中年男子。
手拿泡著枸杞的保溫杯，胳膊夾著MacBook，MacBook上還貼著公司標語：“加班使我快樂”。
面試官： 看你簡歷上用過MySQL，問你幾個簡單的問題吧。什麼是聚簇索引和非聚簇索引？
這個問題難不住我啊。來之前我看一下一燈MySQL八股文。
我： 舉個例子：有這麼一張用戶表
CREATE TABLE `user` (
`id` int COMMENT '主鍵ID',
`name` varchar(10) COMMENT '姓名',
`age` int COMMENT '年齡',
PRIMARY KEY (`id`)
) ENGINE=InnoDB CHARSET=utf8 COMMENT='用戶表';
用戶表中存儲了這些數據：
| id | nane | age |
|---|---|---|
| 1 | 一燈 | 18 |
| 2 | 張三 | 22 |
| 3 | 李四 | 21 |
| 4 | 王二 | 19 |
| 5 | 麻子 | 20 |
那麼在索引中，這些數據是怎麼存儲的呢？
MySQL的InnoDB引擎中索引使用的B+樹結構。
別問為什麼根節點存儲了（1,4）兩個元素，左子節點又存儲了（1,2,3）三個元素，下麵帶有三個葉子節點，葉子節點之間又用有序鏈表相連？
問就是B+樹的特性，不瞭解的可以翻一下上期的文章。
如上圖所示，葉子節點中存儲了全部元素的索引，就是聚簇索引。
一般主鍵索引就是聚簇索引，如果表中沒有主鍵，MySQL也會預設建立一個隱藏主鍵做主鍵索引。
什麼是非聚簇索引？
假設我們在age（年齡）欄位上建一個普通索引，age欄位上面的索引存儲結構就是下麵這樣：
葉子節點中只存儲了當前索引欄位和主鍵ID，這樣的存儲結構就是非聚簇索引。
面試官： 那什麼是聯合索引呢？
我： 有多個欄位組成的索引就是聯合索引。
面試官： 【暈】建聯合索引有什麼好處？它跟在單個欄位上建索引有什麼區別？
我： 假設有這麼一條查詢語句。
select * from user where age = 18 and name = '張三';
如果我們在age和name欄位上分別建兩個索引，這個查詢語句只會用到其中一個索引。
但是我們在age和name欄位建一個聯合索引（age，name），它的存儲結構就變成這樣了。
如果只在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，當然無法用到索引。
面試官： 什麼是覆蓋索引和回表查詢？
我： 這個就更簡單了，上面已經提到這個知識點了。
當我們在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 = '張三';
如果沒有索引下推，會先匹配出 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。
文章持續更新，可以微信搜一搜「 一燈架構 」第一時間閱讀更多技術乾貨。
