深入淺出Mysql索引優化專題分享|面試怪圈

面試怪圈發表於2021-12-19
MySql索引優化面試

文章綱要

該文章結合18張手繪圖例，21個SQL經典案例、近10000字，將Mysql索引優化經驗予以總結，你可以根據綱要來決定是否繼續閱讀，完成這篇文章大概需要25-30分鐘，相信你的堅持是不負時光的：

  • 前言
  • 開篇小例子
    單索引效能最優？
    索引越多越好？
  • 常用術語
    主鍵索引（聚簇索引）
    輔助索引
    覆蓋索引
    最左匹配
    索引下推
  • 再談優化
    覆蓋索引，減少回表
    遵循最左匹配原則

    聯合索引，欄位順序
    字首索引
    索引失效
    大欄位影響檢索效能
    GROUP BY如何優化
    ORDER BY如何優化
    分頁效能優化
    ORDER BY再分頁BUG
    JOIN效能優化
  • 寫在最後

前言

mysql是我們最常用的資料庫，基本很多業務系統都在使用。可是往往在遇到效能問題的時候，總是束手無策。比如：

  • 明明知道有索引的概念，卻不知道這樣加索引是否能夠真正生效？
  • 有的時候，想繼續增加索引卻又擔心索引加的太多，那索引最多能加幾個，加的太多有沒有什麼影響呢？
  • 一個表的多個索引中經常出現一些重複的欄位，他們到底存在有沒有意義？還是冗餘的索引呢？

經過一週的梳理，我將工作中最常用的索引優化手段和方法梳理出來，足以解釋上述疑問。同時，相信你跟著我的思路來閱讀這篇文章，你對mysql索引的理解會有一個更高的層次提升，在工作中不再茫然。

今天這篇文章是根據我在京東內部分享的ppt整理而來，從很多很多角度來看待索引優化的問題，比如：索引為什麼失效、order by的效能提升與避雷、group by是否能夠提升效能、深分頁存在哪些問題及如何優化、join的時候如何選擇驅動表等。

除此之外呢，面試中也會提及一些常見的關於索引的概念，這篇文章也會通過一些例子來幫助你深入淺出索引中的奧祕，比如：索引下推、覆蓋索引和回表等。

同時，通過這篇文章的理解，你在使用其他資料庫，比如mongo或者類似的索引型別，也可以舉一反三。文中有大量的例子和SQL語句以及執行的原理。如果你肯花上一點時間來跟我一起理解。相信你在SQL優化領域會與眾不同！開始吧！

下文主要針對InnoDB儲存引擎的B+樹作為前提來闡述，不再敖述。

開篇小例子

為了能夠更好地理解後續章節的優化介紹，我先通過一個小例子，讓大家明白一個簡單的查詢語句的執行過程、邏輯及原理。

這裡所說的執行過程不是指：Mysql語法詞法解析器、優化器、執行器等巨集觀的維度，而是偏向索引樹的維度。

我們依舊採用大家最熟悉的學生表(student)來舉例吧，看下圖：

圖1：student表及索引說明

學生表，包含：id、number（學號）、name（姓名）、sex（性別）、age（年齡），並且id為主鍵，其他欄位分別有一個單獨索引。

類似這樣的索引設計，在我的工作中經常遇到，當然也隨著查詢邏輯的複雜性提升，這種單欄位索引也會變得越來越多。之所以出現這樣的情況，是對索引的理解和用法並不深入導致的，在茫然的時候選擇了：加單索引來解決效能問題的方法。

我猜大家可能會有這樣的兩個疑問：

  • 建立單欄位索引效能最優？如果不是，那該如何加索引呢?
  • 是不是索引越多越好？因為越多越容易命中？

單索引效能最優？

首先來看單索引效能是否最優？為了說明這個問題，我還是先準備一點資料幫助理解：

圖2：student表資料準備

student表有很多資料，1~7條資料如上圖所示，其中第一條黃色背景的資料，是如下SQL的命中結果：

SELECT * 
  FROM student 
 WHERE age = 7 
   AND sex = '男' ;

那這條SQL語句是如何執行的呢？前面我們給這個表加了4個非主鍵索引，既然我們用了兩個查詢條件，因此，為了提升檢索效能，mysql的優化器會選擇其中的一個索引樹去查詢。這裡我們做一個假設，假設優化器選擇idx_age這個索引,當然選擇idx_sex這個索引，跟我們下面闡述的原理是一樣的。

我手繪了idx_age和主鍵索引的簡圖，輔助理解：

圖3：主鍵索引簡圖

圖4：idx_age索引簡圖

  • 主鍵索引
    主鍵索引的葉子節點17是主鍵id，它下方的R1R7是我對行記錄（也就是全欄位內容）的簡寫。其中，紅色的背景R1就是查詢命中的結果。
  • idx_age索引
    idx_age為非聚簇索引，索引的葉子節點為年齡+主鍵Id，順便提一下，不知道你有沒有考慮為什麼該索引的葉子節點不直接掛的是行記錄呢？ 我想原因有二：1.主鍵已經有行記錄，再次儲存佔用額外的空間，如果二級索引更多，儲存冗餘就更大 2.mysql的儲存以page為儲存單元，如果單索引鍵空間佔用更多，一個page能容納的鍵更少，導致樹更深，檢索需要更多的IO訪問。

回過頭說下，上述語句的執行過程吧：

  • 1.在age索引樹查詢age=7的記錄，取得ID=1;
  • 2.繼續到主鍵索引樹查詢ID=1對應的資料R1，判斷R1.sex等於’男’，返回到結果集中;
  • 3.在age索引樹查詢下一個age=7的記錄，取得ID=2;
  • 4.繼續到主鍵索引樹查詢ID=2對應的資料R2，判斷R2.sex不等於’男’，丟棄;
  • 5.繼續第3步和第4步，發現ID=3的記錄也不滿足條件
  • 6.當在age索引樹上找到age=10的記錄時，不滿足條件，迴圈結束。

你會發現，整個查詢執行的過程中，查詢主鍵索引樹3次，查詢主鍵索引的目的有兩個：一是為了查詢sex是否滿足條件，二是為了返回需要的全欄位。

單索引執行是這樣，那我們繼續看一下，如果我們基於SQL語句的兩個查詢欄位age、sex建立聯合索引，執行過程是怎樣的呢？是否會提升效能呢？

同樣，我手繪了一張聯合索引的簡圖如下：

圖5：idx_age_sex聯合索引簡圖

聯合索引中(5,'男')代表一個索引鍵 ，5是年齡,'男'是性別。同樣葉子節點的綠色陰影部分為Id值。

聯合索引的執行情況是這樣的：

  • 1.在聯合索引樹上查詢age=7並且sex=‘男’的記錄ID=1;
  • 2.繼續到主鍵索引樹查詢ID=1對應的資料R1, 返回到結果集中。

你會發現這次執行僅僅為了返回需要的全欄位,才執行了一次主鍵索引樹的查詢，比單欄位索引少了2次。少的這兩次主鍵索引樹查詢你知道意味著什麼嗎？ 減少了至少兩次的IO訪問（因為mysql為了提升效能會將部分頁快取，暫時忽略這種情況）。我們都知道IO、CPU、記憶體是mysql效能優化的幾大主要影響因素和考慮點。

因此，我們可以得出一個結論：使用聯合索引能提升索引命中率，減少回表篩選帶來的IO損耗。相反單索引需要更多的回表次數。

索引越多越好？

可能有人有這樣的索引誤區，索引越多越容易命中？是不是這樣的呢？首先看下增加索引會帶來什麼影響呢？

  • 1.索引需要佔用儲存空間
    索引是一種效能優化的資料結構，本身也是一種採用空間換時間的思路來提升查詢效能。因此，增加索引的數量一定會導致對應的增加儲存空間。

  • 2.索引更新需要更多維護成本
    我們一直在討論索引的查詢，可我們不能忽略的一點是，當Insert、Update、Delete等操作也帶來索引的更新和維護，因此，索引的數量也會帶來更多的維護成本，你說呢？

因此，綜合所有該表的SQL的查詢條件，合理規劃索引的個數，避免冗餘索引的出現，有助於降低維護成本。比如下面的索引，可以將左圖兩個索引優化為右圖一個索引：

圖6：冗餘索引去除

至於為什麼可以這樣優化，通過閱讀下文，你會得到答案。

常用術語

圍繞著索引有很多術語，也許你經常聽到或者在面試中被問到，但是卻沒有理解是什麼意思。但是為了提升自身逼格和自我的虛榮心滿足，那麼，我們一起回顧下吧：

  • 主鍵索引

以下是student表的主鍵索引，它也叫做一級索引。有的時候也有人稱它為聚簇索引，聚簇索引是因葉子節點的id和行資料聚簇在一起而得名，如下圖：

圖7：主鍵索引

  • 輔助索引

而與主鍵索引的對應的就是輔助索引，它也叫做二級索引。由於葉子節點上無行資料，只有一個id，因此它是非聚簇索引。

圖8：輔助索引簡圖

  • 覆蓋索引

覆蓋索引並不是一種索引型別，而是一種索引查詢的形式和行為。覆蓋索引往往應用於聯合索引。下圖就是一個由age、sex組成的聯合索引：

圖9：聯合索引簡圖

當查詢條件運用了索引，並且SELECT的欄位也覆蓋在該索引樹上，也就是一顆索引樹既滿足了檢索也滿足了結果，無需為了拿到需要的SELECT欄位而去回表的一種方式。

細心的你會發現id在SELECT的欄位中，也是可以走覆蓋索引的。
以下SQL就是運用了覆蓋索引的例子：

SELECT sex,age,id FROM student WHERE age=7;
  • 最左匹配

建立聯合索引的時候是否要考慮欄位的順序？比如idx_A_B和idx_B_A是一樣的嗎？答案是否！
idx_A_B可以滿足以下兩個SQL走索引：

SELECT * FROM　t WHERE A=1 AND B=1;
SELECT * FROM　t WHERE A=1;

而idx_B_A可以滿足這兩個SQL走索引：

SELECT * FROM　t WHERE A=1 AND B=1;
SELECT * FROM　t WHERE B=1;

順序不同導致的效果也截然不同。查詢條件只能根據索引由左到右的順序來匹配索引，而不可以跨索引欄位。

提示：WHERE A=1 AND B=1 或者WHERE B=1 AND A=1是沒有任何區別的。優化器已經幫助我們做好了優化。

  • 索引下推

我們來看下在下圖這個聯合索引前提下，根據文章開頭給出的資料樣例，看看這個SQL在不同的MYSQL版本中如何執行的呢？

圖10：聯合索引idx_name_age
SELECT name
 FROM student
WHERE name like ‘小%’                   
  AND age=7

5.6之前的版本

  • 1.根據name從聯合索引查詢到7條name以“小”開頭的記錄的ID；
  • 2.根據ID回表到主鍵索引查詢全欄位，篩選age=7的記錄，返回。

圖11：5.6之前服務層與引擎層流程圖

因此，回表7次。

5.6及之後的版本

  • 1.根據name從聯合索引查詢到7條name以“小”開頭的記錄，由於索引上儲存了age欄位，因此在該索引上就可以過濾出age=7的記錄，查詢到符合條件的3條記錄的ID。
  • 2.根據ID回表到主鍵索引查詢全欄位，返回結果集。

圖12：5.6及以後服務層與引擎層流程圖

因此，回表3次。

以上描述的就是索引下推，你可能會有疑問，為什麼叫下推？不是左推、右推、上推呢?其實，這個概念是相對MYSQL的層次劃分的，將MYSQL的服務層下推到儲存引擎層來過濾。索引下推少了5和8這兩步，這兩步也就是服務端參與的，將age=7在儲存引擎層完成了過濾。

再談優化

覆蓋索引，減少回表

最常用的查詢操作就是Select * 操作，如果在二級索引進行條件篩選，但為了獲取
全部欄位，需要回表操作，前面提過，回表越多，效能較差。因此，按需select欄位，讓where後的條件欄位和select欄位覆蓋索引減少回表次數，是非常重要的優化手段。

遵循最左匹配原則

前面講到最左匹配原則，涉及兩點：

假設student表上有這樣的索引：idx_name_age。

  • 欄位從左向右匹配，如下：
## 能使用索引的name部分
SELECT * FROM student WHERE name = '小一';

## 無法使用索引
SELECT * FROM student WHERE age = 7;
  • 字元從左向右匹配，如下：
## 能使用索引的name部分
SELECT * FROM student WHERE name like '小%';

## 無法使用索引
SELECT * FROM student WHERE name like '%小';

因此，我建議大家在設計索引的時候一定要考慮該原則，保證索引設計的合理性。不僅僅該原則涉及到索引的設計，同時也涉及到功能的設計。比如，某表資料量較大，產品建議左右模糊匹配，出於效能考慮，可以建議產品的設計改為僅使用右模糊匹配。

聯合索引，欄位順序

往往建立聯合索引，不管是idx_A_B或者idx_B_A都能滿足設計要求，那麼聯合索引欄位的順序，怎樣設計才是最合理的？才能夠更長遠呢？這裡我給出兩點參考建議：

  • 考慮索引的複用能力

複用能力很好理解，比如查詢有這幾種情況：A=1 AND B=1 AND C=1、A=1 AND B=1、A=1或者A>=1 再或者A=1 AND B>=1等等，這些情況下，索引idx_A_B_C肯定是最合適，相反，idx_B_A_C或者idx_A_C_B等的複用能力要差一些。不妨，你使用上面講到的最左原則來思考下吧。

  • 考慮空間的佔用情況

為了說明這點，先看下面這些SQL：

SELECT * FROM student WHERE name ='小一' and age = 7;
SELECT * FROM student WHERE name ='小一';
SELECT * FROM student WHERE age = 7;

滿足這些SQL可以有以下兩種設計，你可以先思考下，你會選擇哪一種呢？

圖13：索引1和索引2設計

你會發現索引1和索引2都可以滿足三個SQL，從複用能力上來說是等同的，那該如何抉擇呢？答案是可以從儲存空間佔用上考慮。

idx_name_age和idx_age_name欄位相同，空間佔用沒有太大差別，而idx_age和idx_name一個為int型別，一個為varchar(10)型別，int佔用4位元組，而varchar(10)佔用32位元組，相差了8倍，相信選擇哪一個索引，你已經有了答案。

一個非空的varchar欄位，在UTF8編碼下的長度計算公式為：3*len+2。

字首索引

聯合索引欄位順序，我們提及了儲存空間的考慮。不知道你沒有發現，在郵箱欄位上建立索引相同的字尾佔用了重複的空間。比如@xixihaha.com,那是否考慮僅使用字首呢？

圖14：郵箱字首索引

就像上圖這樣去設計索引，可以節省空間。但是要重點考慮選擇字首的大小，比如這個選擇5、6還是7合適呢？你可以根據字首的區分度來考慮。比如我們選擇了email(1)這樣的字首，那麼區分度只有26個英文字母，顯然達不到索引設計預期的效果。

下面是新增字首索引的語法，你可以參考：

mysql> ALTER TABLE student 
         ADD INDEX idx_email_6(email(6));

索引失效

索引往往會在某些情況下不按照我們預期的執行方式執行，導致失效。我列舉了幾種常見的失效情況,SQL直白明瞭，請看以下示例：

  • 索引欄位函式操作
SELECT * FROM student WHERE left(name,1) = '小';
  • 索引欄位隱式型別強轉
    ps:學號為varchar型別。
SELECT * FROM student WHERE number = 2021007;
  • 索引欄位運算子操作
SELECT * FROM student WHERE age+1 = 7;
  • 負向查詢
    包含：!=、<>、not in、not like、!>、!<等。
SELECT * FROM student WHERE age != 7;
  • 隱字元字元編碼
    a表採用uft8編碼，b表採用utf8mb4編碼，當使用字串欄位進行join操作。
SELECT * FROM t1 a join t2 b on a.name = b.name;

大欄位影響檢索效能

在設計表時，我們會使用TEXT或者BLOB等型別來儲存大文字或者二進位制，而這些大欄位對查詢效能的影響是比較大的。那是為什麼呢？

回表查詢，需要將整行資料讀取，由於大欄位佔用空間較大，帶來大量IO操作，影響資料讀取效能。

既然無法迴避使用大欄位，我有兩點建議送給你吧：

  • 覆蓋索引
    使用覆蓋索引，避免回表對大欄位的讀取，從而避免帶來過多的IO操作。

  • 表超過10萬行，將大欄位單獨放置一張表
    我們在分表策略中，有一種縱向拆分，就是針對此種場景的一種分表設計思路。

GROUP BY如何優化

不知道你有沒有使用關鍵字EXPLAIN去檢視GROUP BY操作的執行計劃，你會發現在EXTRA欄位中出現類似filesort的關鍵字。這是因為預設情況下，MySQL對所有GROUP BY col1，col2….的欄位進行排序，類似在查詢中指定 ORDER BY col1，col2…一樣。因此，GROUP BY是預設排序的。

因此，我們可以讓GROUP BY後的欄位利用索引排序，或者你的業務場景不需要排序的情況下，可以使用以下語句禁用預設排序：

SELECT age,count(*) 
  FROM student 
GROUP BY age 
ORDER BY NULL;

ORDER BY如何優化

ORDER BY是最常用的場景，因為很多業務都需要排序，比如取排行TOP5，根據年齡排序，按照建立時間排序等。我們知道索引樹是有序的。如果ORDER BY能夠使用索引樹有序的先天特性，從而避免二次排序帶來的時間和空間的複雜度。明顯是提升排序效能的重要手段。

因此，MySQL 可以使用一個索引來滿足ORDER BY子句，而不需要額外的排序。但需要遵守以下三個原則：

  • WHERE 條件和 ORDER BY 使用相同的索引。
  • ORDER BY 欄位的順序和索引順序一致。
  • ORDER BY 的欄位都是升序或者都是降序。

以下這個排序語句，很好的結合了idx_age_name索引使用的三個原則：

SELECT * 
  FROM student 
WHERE age = 7
ORDER BY age ASC,name ASC;

分頁效能優化

深分頁的時候，MYSQL查詢幾秒鐘的情況，你遇到過嗎？不知道MYSQL在分頁時處於何種考慮，LIMIT n,m,這個操作跳過n條資料需要進行回表，導致我們下面這個SQL需要回表10萬次。

SELECT * FROM student where age = 10 LIMIT 100000,10

辦法總是有的，可換種思路避免這10萬次回表，來看SQL的優化吧：

SELECT *
  FROM student s1
INNER JOIN(
  SELECT id FROM student where age = 10 LIMIT 100000,10
) s2 on s1.id = s2.id ;

ORDER BY再分頁BUG

工作中，有人被這個BUG坑過嗎？ORDER BY後分頁，相鄰兩頁存在重複資料。無數次檢查SQL和程式碼邏輯無誤，BUG始終無法定位。 這是由於ORDER BY後的欄位存在重複值的情況，比如age欄位存在重複的值，導致分頁時，順序被打亂。

SELECT * 
  FROM student  
 ORDER BY age ASC 
 LIMIT 1,15;

因此，解決該問題的方法很簡單，基於age的排序後增加一個能確定唯一值的排序欄位，比如我採用id欄位再次排序：

SELECT * 
  FROM student  
 ORDER BY age ASC,ID ASC 
 LIMIT 1,15;

JOIN效能優化

JOIN也是多表關聯的常用的關鍵字，有LEFT JOIN、RIGHT JOIN、JOIN等。在瞭解JOIN效能優化前，需要明確：驅動表和被驅動表。

  • LEFT JOIN
    左表是驅動表，右表是被驅動表

  • RIGHT JOIN
    右表時驅動表，左表是被驅動表

  • INNER JOIN
    MYSQL會選擇資料量比較小的表作為驅動表，大表作為被驅動表

你會發現INNER JOIN的時候，MYSQL選擇小表為驅動表，為什麼呢？在弄清楚原因之前，我們瞭解JOIN的三種演算法，我們用這個SQL來觀察三種演算法的執行過程：

SELECT t1.*,t2.* 
  FROM table1 t1 
  LEFT JOIN table2 t2 on t1.a=t2.a;

假設：table1有100行資料，table2有1000行資料。

  • Index Nested-Loop Join（索引巢狀查詢連線）

既然是索引巢狀查詢連線，那肯定是依賴索引，我們假設這兩個表都有索引：idx_a。執行過程是這樣的：

1.從表t1中讀入一行資料 R1；

2.從資料行R1中，取出a欄位到表t2裡去查詢；

3.根據idx_a索引取出表t2中滿足條件的行，跟R1組成一行，作為結果集的一部分；

4.重複執行步驟1到3，直到表t1的末尾迴圈結束。

你會發現,總掃描行數為：200次，包括遍歷t1表的100次和巢狀查詢idx_a索引的100次，因此，掃描次數受驅動表t1影響

圖15：索引巢狀查詢連線
  • Simple Nested-Loop Join（簡單巢狀查詢連線）

依然是這個SQL，如果沒有idx_a這個索引，執行過程是什麼樣的呢？

  1. 從表t1中讀入一行資料 R1；

  2. 從資料行R1中，取出a欄位到表t2裡去查詢；

  3. 全表掃描取出表t2中滿足條件的行，跟R組成一行，
    作為結果集的一部分

  4. 重複執行步驟1到3，直到表t1的末尾迴圈結束。

失去了索引，形成了笛卡爾掃描，掃描次數為100100次，100次的t1表的掃描和與t2表全表掃描比對，因此，這個效能太差了，MYSQL並未採用此種演算法。

圖16：簡單巢狀查詢連線
  • Block Nested-Loop Join（分塊巢狀查詢連線）

分塊巢狀查詢連結是針對簡單巢狀查詢的解決方案，採用Join Buffer快取的方式，提升效能。執行過程是這樣的：

1.把表t1的資料讀入執行緒記憶體join_buffer中

2.掃描表t2，把表t2中的每一行取出來，跟join_buffer中的資料做對比，滿足join條件的，作為結果集的一部分返回。

因此，儘量比對次數是10萬次，但表掃描次數為1100次，是table1和table2的資料總行數。

圖17：採用JoinerBuffer巢狀查詢連線

然後，似乎我們遺漏了一個概念分塊，這裡並未提及它。你試想下table1資料量比較大，會把所有資料裝載到Joiner Buffer中嗎？當然會採用分而治之的方法。這種方法就是分塊。

按照分塊的方式，我們重新看下，假設table1的50條資料裝滿Joiner Buffer,再次看下執行流程吧：

圖18：採用JoinerBuffer分塊巢狀查詢連線

table1將分為兩次裝載到Join Buffer與table2比對，你會發現掃描次數是這樣計算：table1的行數+塊數×table2的行數=100+2×1000=2100。而塊數是掃描次數的一個重要影響係數，而這個係數是由table1的行數決定，也就是說驅動表的行數決定。

經過三種演算法的比對，你是否發現，掃描次數由驅動表的大小決定，這也就是為什麼InnerJoiner會選擇小表作為驅動表的原因。

那麼，最後我們總結下優化Join的手段有：

  • 將小表作為驅動表
    無論是否使用索引，小表作為驅動表都能夠減少掃描次數。

  • 調整join_buffer_size大小
    MYSQL該引數的預設值大小為512k,調整該引數的大小，可以減少分塊巢狀查詢的塊數，能夠成倍的減少掃描次數。

  • 關聯時使用索引
    關聯時使用索引避免掃描和笛卡爾判斷，是提升join效能的絕對殺手鐗！

寫在最後

SQL調優雖然說起來理論比較多也相對來說好理解，當問題來的時候，還可能束手無策。你可以在SQL語句上增加force index或者ignore index來強制或者忽略某個索引，來驗證是不是MYSQL優化器給出了錯誤的優化。

當然，可以通過explainSQL語句來觀察語句的執行過程、索引的使用情況等，幫助你綜合分析。explain是優化的非常重要的技巧，不妨你百度找篇文章來仔細研究一下。

好了，今天這篇文章就分享到這裡啦。有什麼疑問可以關注我，留言加我好友。

作者介紹

  • keaizhuzhu，公眾號面試怪圈小編，網站面試怪圈站長，曾就職於阿里巴巴本地生活，目前就職於京東做後端開發。
  • 編寫過《Java面試怪圈內卷手冊》面試祕籍，全網閱讀量過萬次。
  • 官網：http://www.msgqer.com。旨在分享前端、後端、大資料、各種中介軟體技術的面試資料，總訪問量數萬次。點選【閱讀原文】可直達。

相關文章