2022 老生常談 深入理解用好MySQL索引

来源:https://www.cnblogs.com/lihanlin/archive/2022/04/28/16202723.html
-Advertisement-
Play Games

文章首發於公眾號：BiggerBoy 有讀者說面試被問到怎麼用SQL模擬資料庫死鎖？ 這位讀者表示對Java中的死鎖還是略知一二的，但是突然用SQL寫死鎖的案例之前還真沒遇到過，這個問題沒答上來。所以今天就帶大家一起來看下怎麼用SQL讓資料庫中產生死鎖。 什麼是死鎖 說到死鎖，還是先來複習下什麼是死 ...


尊重原創版權: https://www.gewuweb.com/hot/11908.html

圖解｜用好MySQL索引，你需要知道的一些事情

尊重原創版權: https://www.gewuweb.com/sitemap.html

一篇文章來聊一聊如何用好MySQL索引。

圖解｜用好MySQL索引，你需要知道的一些事情

為了更好地進行解釋，我創建了一個存儲引擎為InnoDB的表user_innodb，並批量初始化了500W+條數據。包含主鍵id、姓名欄位（name）、性別欄位（gender，用0，1表示不同性別）、手機號欄位（phone），併為name和phone欄位創建了聯合索引。

CREATE TABLE user_innodb ( id int NOT NULL AUTO_INCREMENT, name
varchar(255) DEFAULT NULL, gender tinyint(1) DEFAULT NULL, phone
varchar(11) DEFAULT NULL, PRIMARY KEY (id)， INDEX IDX_NAME_PHONE (name,
phone)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

1. 索引的代價

索引可以非常有效地提升查詢效率，既然這麼好，我給每個欄位都創建一個索引行不行？我勸你不要衝動。

圖解｜用好MySQL索引，你需要知道的一些事情

任何事情都有兩面，索引也不例外。過度使用索引，我們在空間和時間上都會付出相應的代價。

1.1 空間上的代價

索引就是一棵B+數，每創建一個索引都需要創建一棵B+樹，每一棵B+樹的節點都是一個數據頁，每一個數據頁預設會占用16KB的磁碟空間，每一棵B+樹又會包含許許多多的數據頁。所以，大量創建索引，你的磁碟空間會被迅速消耗。

1.2 時間上的代價

空間上的代價你可以使用“鈔能力”來解決，但時間上的代價我們可能就束手無策了。

鏈表的維護

我以主鍵索引為例舉個例子，主鍵索引的B+樹的每一個節點內的記錄都是按照主鍵值由小到大的順序，採用單向鏈表的方式進行連接的。如下圖所示：

圖解｜用好MySQL索引，你需要知道的一些事情

如果我現在要刪除主鍵id為1的記錄，會破壞3個數據頁內的記錄排序，需要對這3個數據頁內的記錄進行重排列，插入和修改操作也是同理。

註：這裡給大家提一嘴，其實刪除操作並不會立即進行數據頁內記錄的重排列，而是會給被刪除的記錄打上一個刪除的標識，等到合適的時候，再把記錄從鏈表中移除，但是總歸需要涉及到排序的維護，勢必要消耗性能。

假如這張表有12個欄位，我們為這張表的12個欄位都設置了索引，我們刪除1條記錄，需要涉及到12棵B+樹的N個數據頁內記錄的排序維護。

更糟糕的是，你增刪改記錄的時候，還可能會觸發數據頁的回收和分裂。還是以上圖為例，假如我刪除了id為13的記錄，那麼數據頁124就沒有存在的必要了，會被InnoDB存儲引擎回收；我插入一條id為12的記錄，如果數據頁32的空間不足以存儲該記錄，InnoDB又需要進行頁面分裂。我們不需要知道頁面回收和頁面分裂的細節，但是能夠想象到這個操作會有多複雜。

如果每個欄位都創建索引，所有這些索引的維護操作帶來的性能損耗，你能想象了吧。

查詢計劃

執行查詢語句之前，MySQL查詢優化器會基於cost成本對一條查詢語句進行優化，並生成一個執行計劃。如果創建的索引太多，優化器會計算每個索引的搜索成本，導致在分析過程中耗時太多，最終影響查詢語句的執行效率。

2. 回表的代價

2.1 什麼是回表

我再啰嗦一遍什麼是回表，我們可以通過二級索引找到B+樹中的葉子結點，但是二級索引的葉子節點的內容並不全，只有索引列的值和主鍵值。我們需要拿著主鍵值再去聚簇索引（主鍵索引）的葉子節點中去拿到完整的用戶記錄，這個過程叫做回表。

圖解｜用好MySQL索引，你需要知道的一些事情

上圖中我以name二級索引為例，並且只畫出了二級索引的葉子節點和聚簇索引的葉子節點，省略了兩棵B+樹的非葉子節點。

從二級索引的葉子節點延伸出的3條線表示的就是回表操作。

2.2 回表的代價

我們根據name欄位查找二級索引的葉子節點的代價還是比較小的，原因有二：

  1. 葉子節點所在的頁通過雙向鏈表進行關聯，遍歷的速度比較快；
  2. MySQL會儘量讓同一個索引的葉子節點的數據頁在磁碟空間中相鄰，儘力避免隨機IO。

但是二級索引葉子節點中的主鍵id的排布就沒有任何規律了，畢竟name索引是對name欄位進行排序的。進行回表的時候，極有可能出現主鍵id所在的記錄在聚簇索引葉子節點中反覆橫跳的情況（正如上圖中回表的3條線表示的那樣），也就是隨機IO。如果目標數據頁恰好在記憶體中的話效果倒也不會太差，但如果不在記憶體中，還要從磁碟中載入一個數據頁的內容（16KB）到記憶體中，這個速度可就太慢了。

是不是說完了回表的代價之後，我會給出一種更高效的搜索方式？不是，回表已經是一種比較高效的搜索方式了，我們需要做的就是儘量地減少回表操作帶來的損耗，總結起來就是兩點：

  1. 能不回表就不回；
  2. 必須回表就減少回表的次數。

接下來先給大家介紹兩個與回表相關的重要概念，這兩個概念涉及到的方法也是索引使用原則的一部分，因為比較重要，在這裡我把這兩個概念先解釋給大家聽。

3. 索引覆蓋、索引下推

3.1 索引覆蓋

想一下，如果非聚簇索引的葉子節點上有你想要的所有數據，是不是就不需要回表了呢？比如我為name和phone欄位創建了一個聯合索引，如下圖：

圖解｜用好MySQL索引，你需要知道的一些事情

如果我們恰好只想搜索name、phone以及主鍵欄位，

SELECT id, name, phone FROM user_innodb WHERE name = "蟬沐風";

可以直接從葉子節點獲取所有數據，根本不需要回表操作。

我們把索引中已經包含了所有需要讀取的列數據的查詢方式稱為 覆蓋索引 （或 索引覆蓋 ）。

3.2 索引下推

3.2.1 概念

還是拿name和phone的聯合索引為例，我們要查詢所有name為「蟬沐風」，並且手機尾號為6606的記錄，查詢SQL如下：

SELECT * FROM user_innodb WHERE name = "蟬沐風" AND phone LIKE "%6606";

由於聯合索引的葉子節點的記錄是先按照name欄位排序，name欄位相同的情況下再按照phone欄位排序，因此把%加在phone欄位前面的時候，是無法利用索引的順序性來進行快速比較的，也就是說這條查詢語句中只有name欄位可以使用索引進行快速比較和過濾。正常情況下查詢過程是這個樣子的：

  1. InnoDB使用聯合索引查出所有name為蟬沐風的二級索引數據，得到3個主鍵值：3485，78921，423476；

  2. 拿到主鍵索引進行回表，到聚簇索引中拿到這三條完整的用戶記錄；

  3. InnoDB把這3條完整的用戶記錄返回給MySQL的Server層，在Server層過濾出尾號為6606的用戶。

如下麵兩幅圖所示，第一幅圖表示InnoDB通過3次回表拿到3條完整的用戶記錄，交給Server層；第二幅圖表示Server層經過phone LIKE
"%6606"條件的過濾之後找到符合搜索條件的記錄，返給客戶端。

圖解｜用好MySQL索引，你需要知道的一些事情

圖解｜用好MySQL索引，你需要知道的一些事情

值得我們關註的是，索引的使用是在存儲引擎中進行的，而數據記錄的比較是在Server層中進行的。現在我們把上述搜索考慮地極端一點，假如數據表中10萬條記錄都符合name='蟬沐風'的條件，而只有1條符合phone
LIKE
"%6606"條件，這就意味著，InnoDB需要將99999條無效的記錄傳輸給Server層讓其自己篩選，更嚴重的是，這99999條數據都是通過回表搜索出來的啊！關於回表的代價你已經知道了。

現在引入 索引下推 。準確來說，應該叫做 索引條件下推 （Index Condition Pushdown， ICP
），就是過濾的動作由下層的存儲引擎層通過使用索引來完成，而不需要上推到Server層進行處理。ICP是在MySQL5.6之後完善的功能。

再回顧一下，我們第一步已經通過name =
"蟬沐風"在聯合索引的葉子節點中找到了符合條件的3條記錄，而且phone欄位也恰好在聯合索引的葉子節點的記錄中。這個時候可以直接在聯合索引的葉子節點中進行遍歷，篩選出尾號為6606的記錄，找到主鍵值為78921的記錄，最後只需要進行1次回表操作即可找到符合全部條件的1條記錄，返回給Server層。

很明顯，使用ICP的方式能有效減少回表的次數。

另外，ICP是預設開啟的，對於二級索引，只要能把條件甩給下麵的存儲引擎，存儲引擎就會進行過濾，不需要我們干預。

3.2.2 演示

查看一下當前ICP的狀態：

SHOW VARIABLES LIKE 'optimizer_switch';

圖解｜用好MySQL索引，你需要知道的一些事情

執行以下SQL語句，並用EXPLAIN查看一下執行計劃，此時的執行計劃是Using index condition

EXPLAIN SELECT * FROM user_innodb WHERE name = "蟬沐風" AND phone LIKE "%6606";

圖解｜用好MySQL索引，你需要知道的一些事情

然後關閉ICP

SET optimizer_switch="index_condition_pushdown=off";

再查看一下ICP的狀態

圖解｜用好MySQL索引，你需要知道的一些事情

再次執行查詢語句，並用EXPLAIN查看一下執行計劃，此時的執行計劃是Using where

EXPLAIN SELECT * FROM user_innodb WHERE name = "蟬沐風" AND phone LIKE "%6606";

圖解｜用好MySQL索引，你需要知道的一些事情

註：即使滿足索引下推的使用條件，查詢優化器也未必會使用索引下推，因為可能存在更高效的方式。

由於之前我給name欄位創建了索引，導致一直沒有使用索引下推，EXPLAIN語句顯示使用了name索引，而不是name和phone的聯合索引；刪除name索引之後，才獲得上述截圖的效果。大家做實驗的時候需要註意。

到目前為止大家應該清楚了索引和回錶帶來的性能問題，講這些自然不是為了恐嚇大家讓大家遠離索引，相反，我們要以正確的方式積極擁抱索引，最大限度降低其帶來的負面影響，放大其優勢。如何用好索引，從兩個方面考慮：

  1. 高效發揮已經創建的索引的作用（避免索引失效）
  2. 為合適的列創建合適的索引（索引創建原則）

4. 什麼時候索引會失效？

4.1 違反最左首碼原則

拿我們文章開始創建的聯合索引為例，該聯合索引的B+樹數據頁內的記錄首先按照name欄位進行排序，name欄位相同的情況下，再按照phone欄位進行排序。

所以，如果我們直接使用phone欄位進行搜索，無法利用索引的順序性。

EXPLAIN SELECT * FROM user_innodb WHERE phone = "13203398311";

圖解｜用好MySQL索引，你需要知道的一些事情

EXPLAIN可以查看搜索語句的執行計劃，其中，possible_keys列表示在當前查詢中，可能用到的索引有哪一些；key列表示實際用到的索引有哪一些。

但是一旦加上name的搜索條件，就會使用到聯合索引，而且不需要在意name在WHERE子句中的位置，因為查詢優化器會幫我們優化。

EXPLAIN SELECT * FROM user_innodb WHERE phone = "13203398311" AND name =
'蟬沐風';

圖解｜用好MySQL索引，你需要知道的一些事情

4.2 使用反向查詢（!=, <>,NOT LIKE）

MySQL在使用反向查詢（!=, <>, NOT LIKE）的時候無法使用索引，會導致全表掃描，覆蓋索引除外。

EXPLAIN SELECT * FROM user_innodb WHERE name != '蟬沐風';

圖解｜用好MySQL索引，你需要知道的一些事情

4.3 LIKE以通配符開頭

當使用name LIKE '%沐風'或者name LIKE
'%沐%'這兩種方式都會使索引失效，因為聯合索引的B+樹數據頁內的記錄首先按照name欄位進行排序，這兩種搜索方式不在意name欄位的開頭是什麼，自然就無法使用索引，只能通過全表掃描的方式進行查詢。

EXPLAIN SELECT * FROM user_innodb WHERE name LIKE '%沐風';

圖解｜用好MySQL索引，你需要知道的一些事情

但是使用通配符結尾就沒有問題

EXPLAIN SELECT * FROM user_innodb WHERE name LIKE '蟬沐%';

圖解｜用好MySQL索引，你需要知道的一些事情

4.4 對索引列做任何操作

如果不是單純使用索引列，而是對索引列做了其他操作，例如數值計算、使用函數、（手動或自動）類型轉換等操作，會導致索引失效。

4.4.1 使用函數

EXPLAIN SELECT * FROM user_innodb WHERE LEFT(name,3) = '蟬沐風';

圖解｜用好MySQL索引，你需要知道的一些事情

MySQL8.0新增了函數索引的功能，我們可以給函數作用之後的結果創建索引，使用以下語句

ALTER TABLE user_innodb ADD KEY IDX_NAME_LEFT ((left(name,3)));

再次執行EXPLAIN語句，此時索引生效

圖解｜用好MySQL索引，你需要知道的一些事情

4.4.2 使用表達式

EXPLAIN SELECT * FROM user_innodb WHERE id + 1 = 1100000;

圖解｜用好MySQL索引，你需要知道的一些事情

換一種方式，單獨使用id，就能高效使用索引：

EXPLAIN SELECT * FROM user_innodb WHERE id = 1100000 - 1;

圖解｜用好MySQL索引，你需要知道的一些事情

4.4.3 使用類型轉換

例1

user_innodb中的phone欄位為varchar類型，實驗之前我們先給phone欄位創建個索引

ALTER TABLE user_innodb ADD INDEX IDX_PHONE (phone);

隨便搜索一個存在的手機號，看一下索引是否成功

EXPLAIN SELECT * FROM user_innodb WHERE phone = '13203398311';

圖解｜用好MySQL索引，你需要知道的一些事情

可以看到能使用到索引，現在我們稍微修改一下，把phone = '13203398311'修改為phone =
13203398311，這意味著我們將字元串的搜索條件改成了整形的搜索條件，再看一下還會不會使用到索引：

EXPLAIN SELECT * FROM user_innodb WHERE phone = 13203398311;

圖解｜用好MySQL索引，你需要知道的一些事情

顯示索引失效。

例2

我們再看一個例子，主鍵id類型是bigint，但是在搜索條件中我估計使用字元串類型：

EXPLAIN SELECT * FROM user_innodb WHERE id = '1099999';

圖解｜用好MySQL索引，你需要知道的一些事情

總結

稍微總結一下這個問題，當索引欄位類型為字元串時，使用數字類型進行搜索不會用到索引；而索引欄位類型為數字類型時，使用字元串類型進行搜索會使用到索引。

要搞明白這個問題，我們需要知道MySQL的數據類型轉換規則是什麼。簡單地說就是MySQL會自動將數字轉化為字元串，還是將字元串轉化為數字。

一個簡單的方法是，通過SELECT '10' > 9的結果來確定MySQL的類型轉換規則：

  • 結果為1，說明MySQL會自動將字元串類型轉化為數字，相當於執行了SELECT 10 > 9;
  • 結果為0，說明MySQL會自動將數字轉化為字元串，相當於執行了SELECT '10' > '9'。

mysql> SELECT '10' > 9;+----------+| '10' > 9 |+----------+| 1 |+----------+1
row in set (0.00 sec)

上面的執行結果為1，說明MySQL遇到類型轉換時，會自動將字元串轉換為數字類型，因此對於例1:

EXPLAIN SELECT * FROM user_innodb WHERE phone = 13203398311;

就相當於

EXPLAIN SELECT * FROM user_innodb WHERE CAST(phone AS signed int) =
13203398311;

也就是對索引欄位使用了函數，按照前文的介紹，對索引使用函數是不會使用到索引的。

對於例2:

EXPLAIN SELECT * FROM user_innodb WHERE id = '1099999';

就相當於

EXPLAIN SELECT * FROM user_innodb WHERE id = CAST('1099999' AS unsigned int);

沒有在索引欄位添加任何操作，因此能夠使用到索引。

4.5 OR連接

使用OR連接的查詢語句，如果OR之前的條件列是索引列，但是OR之後的條件列不是索引列，則不會使用索引。舉例：

EXPLAIN SELECT * FROM user_innodb WHERE id = 1099999 OR gender = 0;

圖解｜用好MySQL索引，你需要知道的一些事情

上面總結了一些索引失效的場景，這些經驗的總結往往對SQL的優化很有益處，但同時需要註意的是這些經驗並非金科玉律。

比如使用<>查詢時，在某些時候是可以用到索引的：

EXPLAIN SELECT * FROM user_innodb WHERE id <> 1099999;

圖解｜用好MySQL索引，你需要知道的一些事情

最終是否使用索引，完全取決於MySQL的優化器，而優化器的判定依據就是cost開銷（Cost Base
Optimizer），優化器並非基於具體的規則，也不是基於語義，就是單純地執行開銷小的方案罷了。所以在·EXPLAIN·的結果中你會看到possible_keys一列，優化器會把這裡邊的索引都試一遍（是不是又加深了對不能隨便創建索引的認識呢？），然後選一個開銷最小的，如果都不太行，那就直接全表掃描好了。

而cost開銷，和資料庫版本、數據量等都有關係，因此如果想更精準地提升索引功能性，擁抱EXPLAIN吧！

5. 索引創建（使用）原則

之前講過的 索引覆蓋 和 索引下推 都可以作為索引創建的原則，就是在創建索引的時候，儘量發揮 索引覆蓋 和 索引下推
的優勢。

儘量避免上述提及到的索引可能失效的情況的出現，同樣是索引的使用原則。

除此之外，再給大家介紹一些。

5.1 不為離散度高的列創建索引

先來看一下列的離散度公式：COUNT(DISTINCT(column_name)) /
COUNT(*)，列的不重覆值的個數與所有數據行的比例。簡而言之，如果列的重覆值越多，列的離散度越低。重覆值越少，離散度就越高。

舉個例子，gender（性別）列只有0、1兩個值，列的離散度非常低，假如我們為該列創建索引，我們會在二級索引中搜索到大量的重覆數據，然後進行大量回表操作。大量回表哈？你懂了吧。

不要為重覆值多的列創建索引

5.2 只為用於搜索、排序或分組的列創建索引

我們只為出現在WHERE子句中的列或者出現在ORDER BY和GROUP BY子句中的列創建索引即可。僅出現在查詢列表中的列不需要創建索引。

5.3 用好聯合索引

用2條SQL語句來說明這個問題：

1. SELECT * FROM user_innodb WHERE name = '蟬沐風' AND phone = '13203398311';2.
SELECT * FROM user_innodb WHERE name = '蟬沐風';

語句1和語句2都能夠使用索引，這帶給我們的一個索引設計原則就是：

不要為聯合索引的第一個索引列單獨創建索引

因為聯合索引本身就是先按照name列進行排序，因此聯合索引對name的搜索是有效的，不需要單獨為name再創建索引了。也正因為此

建立聯合索引的時候，一定要把最常用的列放在最左邊

5.4 對過長的欄位，建立首碼索引

如果一個字元串格式的列占用的空間比較大（就是說允許存儲比較長的字元串數據），為該列創建索引，就意味著該列的數據會被完整地記錄在每個數據頁的每條記錄中，會占用相當大的存儲空間。

對此，我們可以為該列的前幾個字元創建索引，也就是在二級索引的記錄中只會保留字元串的前幾個字元。比如我們可以為phone列創建索引，索引只保留手機號的前3位：

ALTER TABLE user_innodb ADD INDEX IDX_PHONE_3 (phone(3));

然後執行下麵的SQL語句：

EXPLAIN SELECT * FROM user_innodb WHERE phone = '1320';

圖解｜用好MySQL索引，你需要知道的一些事情

由於在IDX_PHONE_3索引中只保留了手機號的前3位數字，所以我們只能定位到以132開頭的二級索引記錄，然後在遍歷所有的這些二級索引記錄時再判斷它們是否滿足第4位數為0的條件。

當列中存儲的字元串包含的字元較多時，為該欄位建立首碼索引可以有效節省磁碟空間

5.5 頻繁更新的值，不要作為主鍵或索引

因為可能涉及到數據頁分裂的情況，會影響性能。

5.6 隨機無序的值，不建議作為索引，例如身份證、UUID

不建議作為索引，例如身份證、UUID

尊重原創版權: https://www.gewuweb.com/sitemap.html

尊重原創版權: https://www.gewuweb.com/hot/13713.html

圖解｜用好MySQL索引，你需要知道的一些事情

一篇文章來聊一聊如何用好MySQL索引。

圖解｜用好MySQL索引，你需要知道的一些事情

為了更好地進行解釋，我創建了一個存儲引擎為InnoDB的表user_innodb，並批量初始化了500W+條數據。包含主鍵id、姓名欄位（name）、性別欄位（gender，用0，1表示不同性別）、手機號欄位（phone），併為name和phone欄位創建了聯合索引。

CREATE TABLE user_innodb ( id int NOT NULL AUTO_INCREMENT, name
varchar(255) DEFAULT NULL, gender tinyint(1) DEFAULT NULL, phone
varchar(11) DEFAULT NULL, PRIMARY KEY (id)， INDEX IDX_NAME_PHONE (name,
phone)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

1. 索引的代價

索引可以非常有效地提升查詢效率，既然這麼好，我給每個欄位都創建一個索引行不行？我勸你不要衝動。

圖解｜用好MySQL索引，你需要知道的一些事情

任何事情都有兩面，索引也不例外。過度使用索引，我們在空間和時間上都會付出相應的代價。

1.1 空間上的代價

索引就是一棵B+數，每創建一個索引都需要創建一棵B+樹，每一棵B+樹的節點都是一個數據頁，每一個數據頁預設會占用16KB的磁碟空間，每一棵B+樹又會包含許許多多的數據頁。所以，大量創建索引，你的磁碟空間會被迅速消耗。

1.2 時間上的代價

空間上的代價你可以使用“鈔能力”來解決，但時間上的代價我們可能就束手無策了。

鏈表的維護

我以主鍵索引為例舉個例子，主鍵索引的B+樹的每一個節點內的記錄都是按照主鍵值由小到大的順序，採用單向鏈表的方式進行連接的。如下圖所示：

圖解｜用好MySQL索引，你需要知道的一些事情

如果我現在要刪除主鍵id為1的記錄，會破壞3個數據頁內的記錄排序，需要對這3個數據頁內的記錄進行重排列，插入和修改操作也是同理。

註：這裡給大家提一嘴，其實刪除操作並不會立即進行數據頁內記錄的重排列，而是會給被刪除的記錄打上一個刪除的標識，等到合適的時候，再把記錄從鏈表中移除，但是總歸需要涉及到排序的維護，勢必要消耗性能。

假如這張表有12個欄位，我們為這張表的12個欄位都設置了索引，我們刪除1條記錄，需要涉及到12棵B+樹的N個數據頁內記錄的排序維護。

更糟糕的是，你增刪改記錄的時候，還可能會觸發數據頁的回收和分裂。還是以上圖為例，假如我刪除了id為13的記錄，那麼數據頁124就沒有存在的必要了，會被InnoDB存儲引擎回收；我插入一條id為12的記錄，如果數據頁32的空間不足以存儲該記錄，InnoDB又需要進行頁面分裂。我們不需要知道頁面回收和頁面分裂的細節，但是能夠想象到這個操作會有多複雜。

如果每個欄位都創建索引，所有這些索引的維護操作帶來的性能損耗，你能想象了吧。

查詢計劃

執行查詢語句之前，MySQL查詢優化器會基於cost成本對一條查詢語句進行優化，並生成一個執行計劃。如果創建的索引太多，優化器會計算每個索引的搜索成本，導致在分析過程中耗時太多，最終影響查詢語句的執行效率。

2. 回表的代價

2.1 什麼是回表

我再啰嗦一遍什麼是回表，我們可以通過二級索引找到B+樹中的葉子結點，但是二級索引的葉子節點的內容並不全，只有索引列的值和主鍵值。我們需要拿著主鍵值再去聚簇索引（主鍵索引）的葉子節點中去拿到完整的用戶記錄，這個過程叫做回表。

圖解｜用好MySQL索引，你需要知道的一些事情

上圖中我以name二級索引為例，並且只畫出了二級索引的葉子節點和聚簇索引的葉子節點，省略了兩棵B+樹的非葉子節點。

從二級索引的葉子節點延伸出的3條線表示的就是回表操作。

2.2 回表的代價

我們根據name欄位查找二級索引的葉子節點的代價還是比較小的，原因有二：

  1. 葉子節點所在的頁通過雙向鏈表進行關聯，遍歷的速度比較快；
  2. MySQL會儘量讓同一個索引的葉子節點的數據頁在磁碟空間中相鄰，儘力避免隨機IO。

但是二級索引葉子節點中的主鍵id的排布就沒有任何規律了，畢竟name索引是對name欄位進行排序的。進行回表的時候，極有可能出現主鍵id所在的記錄在聚簇索引葉子節點中反覆橫跳的情況（正如上圖中回表的3條線表示的那樣），也就是隨機IO。如果目標數據頁恰好在記憶體中的話效果倒也不會太差，但如果不在記憶體中，還要從磁碟中載入一個數據頁的內容（16KB）到記憶體中，這個速度可就太慢了。

是不是說完了回表的代價之後，我會給出一種更高效的搜索方式？不是，回表已經是一種比較高效的搜索方式了，我們需要做的就是儘量地減少回表操作帶來的損耗，總結起來就是兩點：

  1. 能不回表就不回；
  2. 必須回表就減少回表的次數。

接下來先給大家介紹兩個與回表相關的重要概念，這兩個概念涉及到的方法也是索引使用原則的一部分，因為比較重要，在這裡我把這兩個概念先解釋給大家聽。

3. 索引覆蓋、索引下推

3.1 索引覆蓋

想一下，如果非聚簇索引的葉子節點上有你想要的所有數據，是不是就不需要回表了呢？比如我為name和phone欄位創建了一個聯合索引，如下圖：

圖解｜用好MySQL索引，你需要知道的一些事情

如果我們恰好只想搜索name、phone以及主鍵欄位，

SELECT id, name, phone FROM user_innodb WHERE name = "蟬沐風";

可以直接從葉子節點獲取所有數據，根本不需要回表操作。

我們把索引中已經包含了所有需要讀取的列數據的查詢方式稱為 覆蓋索引 （或 索引覆蓋 ）。

3.2 索引下推

3.2.1 概念

還是拿name和phone的聯合索引為例，我們要查詢所有name為「蟬沐風」，並且手機尾號為6606的記錄，查詢SQL如下：

SELECT * FROM user_innodb WHERE name = "蟬沐風" AND phone LIKE "%6606";

由於聯合索引的葉子節點的記錄是先按照name欄位排序，name欄位相同的情況下再按照phone欄位排序，因此把%加在phone欄位前面的時候，是無法利用索引的順序性來進行快速比較的，也就是說這條查詢語句中只有name欄位可以使用索引進行快速比較和過濾。正常情況下查詢過程是這個樣子的：

  1. InnoDB使用聯合索引查出所有name為蟬沐風的二級索引數據，得到3個主鍵值：3485，78921，423476；

  2. 拿到主鍵索引進行回表，到聚簇索引中拿到這三條完整的用戶記錄；

  3. InnoDB把這3條完整的用戶記錄返回給MySQL的Server層，在Server層過濾出尾號為6606的用戶。

如下麵兩幅圖所示，第一幅圖表示InnoDB通過3次回表拿到3條完整的用戶記錄，交給Server層；第二幅圖表示Server層經過phone LIKE
"%6606"條件的過濾之後找到符合搜索條件的記錄，返給客戶端。

圖解｜用好MySQL索引，你需要知道的一些事情

圖解｜用好MySQL索引，你需要知道的一些事情

值得我們關註的是，索引的使用是在存儲引擎中進行的，而數據記錄的比較是在Server層中進行的。現在我們把上述搜索考慮地極端一點，假如數據表中10萬條記錄都符合name='蟬沐風'的條件，而只有1條符合phone
LIKE
"%6606"條件，這就意味著，InnoDB需要將99999條無效的記錄傳輸給Server層讓其自己篩選，更嚴重的是，這99999條數據都是通過回表搜索出來的啊！關於回表的代價你已經知道了。

現在引入 索引下推 。準確來說，應該叫做 索引條件下推 （Index Condition Pushdown， ICP
），就是過濾的動作由下層的存儲引擎層通過使用索引來完成，而不需要上推到Server層進行處理。ICP是在MySQL5.6之後完善的功能。

再回顧一下，我們第一步已經通過name =
"蟬沐風"在聯合索引的葉子節點中找到了符合條件的3條記錄，而且phone欄位也恰好在聯合索引的葉子節點的記錄中。這個時候可以直接在聯合索引的葉子節點中進行遍歷，篩選出尾號為6606的記錄，找到主鍵值為78921的記錄，最後只需要進行1次回表操作即可找到符合全部條件的1條記錄，返回給Server層。

很明顯，使用ICP的方式能有效減少回表的次數。

另外，ICP是預設開啟的，對於二級索引，只要能把條件甩給下麵的存儲引擎，存儲引擎就會進行過濾，不需要我們干預。

3.2.2 演示

查看一下當前ICP的狀態：

SHOW VARIABLES LIKE 'optimizer_switch';

圖解｜用好MySQL索引，你需要知道的一些事情

執行以下SQL語句，並用EXPLAIN查看一下執行計劃，此時的執行計劃是Using index condition

EXPLAIN SELECT * FROM user_innodb WHERE name = "蟬沐風" AND phone LIKE "%6606";

圖解｜用好MySQL索引，你需要知道的一些事情

然後關閉ICP

SET optimizer_switch="index_condition_pushdown=off";

再查看一下ICP的狀態

圖解｜用好MySQL索引，你需要知道的一些事情

再次執行查詢語句，並用EXPLAIN查看一下執行計劃，此時的執行計劃是Using where

EXPLAIN SELECT * FROM user_innodb WHERE name = "蟬沐風" AND phone LIKE "%6606";

圖解｜用好MySQL索引，你需要知道的一些事情

註：即使滿足索引下推的使用條件，查詢優化器也未必會使用索引下推，因為可能存在更高效的方式。

由於之前我給name欄位創建了索引，導致一直沒有使用索引下推，EXPLAIN語句顯示使用了name索引，而不是name和phone的聯合索引；刪除name索引之後，才獲得上述截圖的效果。大家做實驗的時候需要註意。

到目前為止大家應該清楚了索引和回錶帶來的性能問題，講這些自然不是為了恐嚇大家讓大家遠離索引，相反，我們要以正確的方式積極擁抱索引，最大限度降低其帶來的負面影響，放大其優勢。如何用好索引，從兩個方面考慮：

  1. 高效發揮已經創建的索引的作用（避免索引失效）
  2. 為合適的列創建合適的索引（索引創建原則）

4. 什麼時候索引會失效？

4.1 違反最左首碼原則

拿我們文章開始創建的聯合索引為例，該聯合索引的B+樹數據頁內的記錄首先按照name欄位進行排序，name欄位相同的情況下，再按照phone欄位進行排序。

所以，如果我們直接使用phone欄位進行搜索，無法利用索引的順序性。

EXPLAIN SELECT * FROM user_innodb WHERE phone = "13203398311";

圖解｜用好MySQL索引，你需要知道的一些事情

EXPLAIN可以查看搜索語句的執行計劃，其中，possible_keys列表示在當前查詢中，可能用到的索引有哪一些；key列表示實際用到的索引有哪一些。

但是一旦加上name的搜索條件，就會使用到聯合索引，而且不需要在意name在WHERE子句中的位置，因為查詢優化器會幫我們優化。

EXPLAIN SELECT * FROM user_innodb WHERE phone = "13203398311" AND name =
'蟬沐風';

圖解｜用好MySQL索引，你需要知道的一些事情

4.2 使用反向查詢（!=, <>,NOT LIKE）

MySQL在使用反向查詢（!=, <>, NOT LIKE）的時候無法使用索引，會導致全表掃描，覆蓋索引除外。

EXPLAIN SELECT * FROM user_innodb WHERE name != '蟬沐風';

圖解｜用好MySQL索引，你需要知道的一些事情

4.3 LIKE以通配符開頭

當使用name LIKE '%沐風'或者name LIKE
'%沐%'這兩種方式都會使索引失效，因為聯合索引的B+樹數據頁內的記錄首先按照name欄位進行排序，這兩種搜索方式不在意name欄位的開頭是什麼，自然就無法使用索引，只能通過全表掃描的方式進行查詢。

EXPLAIN SELECT * FROM user_innodb WHERE name LIKE '%沐風';

圖解｜用好MySQL索引，你需要知道的一些事情

但是使用通配符結尾就沒有問題

EXPLAIN SELECT * FROM user_innodb WHERE name LIKE '蟬沐%';

圖解｜用好MySQL索引，你需要知道的一些事情

4.4 對索引列做任何操作

如果不是單純使用索引列，而是對索引列做了其他操作，例如數值計算、使用函數、（手動或自動）類型轉換等操作，會導致索引失效。

4.4.1 使用函數

EXPLAIN SELECT * FROM user_innodb WHERE LEFT(name,3) = '蟬沐風';

圖解｜用好MySQL索引，你需要知道的一些事情

MySQL8.0新增了函數索引的功能，我們可以給函數作用之後的結果創建索引，使用以下語句

ALTER TABLE user_innodb ADD KEY IDX_NAME_LEFT ((left(name,3)));

再次執行EXPLAIN語句，此時索引生效

圖解｜用好MySQL索引，你需要知道的一些事情

4.4.2 使用表達式

EXPLAIN SELECT * FROM user_innodb WHERE id + 1 = 1100000;

圖解｜用好MySQL索引，你需要知道的一些事情

換一種方式，單獨使用id，就能高效使用索引：

EXPLAIN SELECT * FROM user_innodb WHERE id = 1100000 - 1;

圖解｜用好MySQL索引，你需要知道的一些事情

4.4.3 使用類型轉換

例1

user_innodb中的phone欄位為varchar類型，實驗之前我們先給phone欄位創建個索引

ALTER TABLE user_innodb ADD INDEX IDX_PHONE (phone);

隨便搜索一個存在的手機號，看一下索引是否成功

EXPLAIN SELECT * FROM user_innodb WHERE phone = '13203398311';

圖解｜用好MySQL索引，你需要知道的一些事情

可以看到能使用到索引，現在我們稍微修改一下，把phone = '13203398311'修改為phone =
13203398311，這意味著我們將字元串的搜索條件改成了整形的搜索條件，再看一下還會不會使用到索引：

EXPLAIN SELECT * FROM user_innodb WHERE phone = 13203398311;

圖解｜用好MySQL索引，你需要知道的一些事情

顯示索引失效。

例2

我們再看一個例子，主鍵id類型是bigint，但是在搜索條件中我估計使用字元串類型：

EXPLAIN SELECT * FROM user_innodb WHERE id = '1099999';

圖解｜用好MySQL索引，你需要知道的一些事情

總結

稍微總結一下這個問題，當索引欄位類型為字元串時，使用數字類型進行搜索不會用到索引；而索引欄位類型為數字類型時，使用字元串類型進行搜索會使用到索引。

要搞明白這個問題，我們需要知道MySQL的數據類型轉換規則是什麼。簡單地說就是MySQL會自動將數字轉化為字元串，還是將字元串轉化為數字。

一個簡單的方法是，通過SELECT '10' > 9的結果來確定MySQL的類型轉換規則：

  • 結果為1，說明MySQL會自動將字元串類型轉化為數字，相當於執行了SELECT 10 > 9;
  • 結果為0，說明MySQL會自動將數字轉化為字元串，相當於執行了SELECT '10' > '9'。

mysql> SELECT '10' > 9;+----------+| '10' > 9 |+----------+| 1 |+----------+1
row in set (0.00 sec)

上面的執行結果為1，說明MySQL遇到類型轉換時，會自動將字元串轉換為數字類型，因此對於例1:

EXPLAIN SELECT * FROM user_innodb WHERE phone = 13203398311;

就相當於

EXPLAIN SELECT * FROM user_innodb WHERE CAST(phone AS signed int) =
13203398311;

也就是對索引欄位使用了函數，按照前文的介紹，對索引使用函數是不會使用到索引的。

對於例2:

EXPLAIN SELECT * FROM user_innodb WHERE id = '1099999';

就相當於

EXPLAIN SELECT * FROM user_innodb WHERE id = CAST('1099999' AS unsigned int);

沒有在索引欄位添加任何操作，因此能夠使用到索引。

4.5 OR連接

使用OR連接的查詢語句，如果OR之前的條件列是索引列，但是OR之後的條件列不是索引列，則不會使用索引。舉例：

EXPLAIN SELECT * FROM user_innodb WHERE id = 1099999 OR gender = 0;

圖解｜用好MySQL索引，你需要知道的一些事情

上面總結了一些索引失效的場景，這些經驗的總結往往對SQL的優化很有益處，但同時需要註意的是這些經驗並非金科玉律。

比如使用<>查詢時，在某些時候是可以用到索引的：

EXPLAIN SELECT * FROM user_innodb WHERE id <> 1099999;

圖解｜用好MySQL索引，你需要知道的一些事情

最終是否使用索引，完全取決於MySQL的優化器，而優化器的判定依據就是cost開銷（Cost Base
Optimizer），優化器並非基於具體的規則，也不是基於語義，就是單純地執行開銷小的方案罷了。所以在·EXPLAIN·的結果中你會看到possible_keys一列，優化器會把這裡邊的索引都試一遍（是不是又加深了對不能隨便創建索引的認識呢？），然後選一個開銷最小的，如果都不太行，那就直接全表掃描好了。

而cost開銷，和資料庫版本、數據量等都有關係，因此如果想更精準地提升索引功能性，擁抱EXPLAIN吧！

5. 索引創建（使用）原則

之前講過的 索引覆蓋 和 索引下推 都可以作為索引創建的原則，就是在創建索引的時候，儘量發揮 索引覆蓋 和 索引下推
的優勢。

儘量避免上述提及到的索引可能失效的情況的出現，同樣是索引的使用原則。

除此之外，再給大家介紹一些。

5.1 不為離散度高的列創建索引

先來看一下列的離散度公式：COUNT(DISTINCT(column_name)) /
COUNT(*)，列的不重覆值的個數與所有數據行的比例。簡而言之，如果列的重覆值越多，列的離散度越低。重覆值越少，離散度就越高。

舉個例子，gender（性別）列只有0、1兩個值，列的離散度非常低，假如我們為該列創建索引，我們會在二級索引中搜索到大量的重覆數據，然後進行大量回表操作。大量回表哈？你懂了吧。

不要為重覆值多的列創建索引

5.2 只為用於搜索、排序或分組的列創建索引

我們只為出現在WHERE子句中的列或者出現在ORDER BY和GROUP BY子句中的列創建索引即可。僅出現在查詢列表中的列不需要創建索引。

5.3 用好聯合索引

用2條SQL語句來說明這個問題：

1. SELECT * FROM user_innodb WHERE name = '蟬沐風' AND phone = '13203398311';2.
SELECT * FROM user_innodb WHERE name = '蟬沐風';

語句1和語句2都能夠使用索引，這帶給我們的一個索引設計原則就是：

不要為聯合索引的第一個索引列單獨創建索引

因為聯合索引本身就是先按照name列進行排序，因此聯合索引對name的搜索是有效的，不需要單獨為name再創建索引了。也正因為此

建立聯合索引的時候，一定要把最常用的列放在最左邊

5.4 對過長的欄位，建立首碼索引

如果一個字元串格式的列占用的空間比較大（就是說允許存儲比較長的字元串數據），為該列創建索引，就意味著該列的數據會被完整地記錄在每個數據頁的每條記錄中，會占用相當大的存儲空間。

對此，我們可以為該列的前幾個字元創建索引，也就是在二級索引的記錄中只會保留字元串的前幾個字元。比如我們可以為phone列創建索引，索引只保留手機號的前3位：

ALTER TABLE user_innodb ADD INDEX IDX_PHONE_3 (phone(3));

然後執行下麵的SQL語句：

EXPLAIN SELECT * FROM user_innodb WHERE phone = '1320';

圖解｜用好MySQL索引，你需要知道的一些事情

由於在IDX_PHONE_3索引中只保留了手機號的前3位數字，所以我們只能定位到以132開頭的二級索引記錄，然後在遍歷所有的這些二級索引記錄時再判斷它們是否滿足第4位數為0的條件。

當列中存儲的字元串包含的字元較多時，為該欄位建立首碼索引可以有效節省磁碟空間

5.5 頻繁更新的值，不要作為主鍵或索引

因為可能涉及到數據頁分裂的情況，會影響性能。

5.6 隨機無序的值，不建議作為索引，例如身份證、UUID

不建議作為索引，例如身份證、UUID


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • Dapper+FastExcel SQL Server數據導出Excel
    由於經常需要進行報表導出的操作，但有時候數據量比較大，趁手的工具不是收費就是學習使用也比較花費時間成本，所以找了些庫進行簡單的整合，能夠滿足需求，百萬條數據幾分鐘即可導出，效率也能滿足要求，所以將就著用 數據讀取處理 public class DBConnectFactory { public co ...
  • c# 一些警告的處理方法
    在使用.Net 6開發程式時，發現多了很多新的警告類型。這裡總結一下處理方法。 CS8618 在退出構造函數時，不可為 null 的 屬性“Name”必須包含非 null 值 經常遇到的有CS8618警告：如果定義屬性可能為空時，在編譯時會報這個警告，比如下麵的代碼： public class Pl ...
  • 通俗易懂的ArcGis開發快速入門
    前言 本文主要介紹ArcGis的ArcEngine開發，學習時，我們需要放下心裡障礙，那就是Gis開發只是普通的軟體開發，並不需要專業的GIS知識，就是非常普通的，調用相關的C++開發的COM組件。 開發環境：VS2017。 ArcEngine版本：10.1。 基礎學習 正式使用ArcGis之前，需 ...
  • stm32F103RCT6使用FFT運算分析波形詳解（非常新手）
    本文詳細的講解了FFT的實現，簡直是手把手了。我也是慢慢學過來的，知道網上的教程對於初學者不是很友好，所以決定自己寫一份博客來記錄下來我的經驗 ...
  • 04-CubeMx+Keil+Proteus模擬STM32 - GPIO（三）
    本文例子參考《STM32單片機開發實例——基於Proteus虛擬模擬與HAL/LL庫》 源代碼：https://github.com/LanLinnet/STM33F103R6 項目要求 實現流水燈效果。 硬體設計 在第一節的基礎上，在Proteus中添加電路如下圖所示，其中我們添加了一個排阻RX8 ...
  • 基於 Xcode 搭建 OpenCV 開發環境
    如何基於Xcode搭建OpenCV開發環境 我的開發平臺是MacBook Pro (13-inch, M1, 2020)，版本11.4，碩士課題是關於電腦視覺的，平時主要用Clion/PyCharm基於opencv-python/C++進行開發。近期閑來沒事體驗一下蘋果官方的開發工具，由於遇到了一 ...
  • 2022深讀《嵌入式Linux記憶體使用與性能優化》筆記
    Set介面 介紹 無序（添加和取出的順序不一致），沒有索引 不允許重覆，所以最多包含一個null JDK API中Set介面實現類有 Set介面常用方法 和List介面一樣，Set介面也是Collection的子介面，因此，常用方法和Collection介面一樣 特點 不能存放重覆的元素 set介面 ...
  • Redis設計與實現2.2：主從複製
    主從複製 SLAVEOF 新舊複製功能 舊版複製功能 舊版複製功能的實現為 同步 和 命令傳播： 當剛連上Master時，要做一次全同步： sequenceDiagram participant Slave participant Master Slave->>Master: SYNC Master ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類