MySQL中IS NULL、IS NOT NULL、!=不能用索引？胡扯！

小孩子4919發表於2019-08-22
MySqlNull索引

標籤： 公眾號文章

不知道從什麼時候開始，網上流傳著這麼一個說法：

MySQL的WHERE子句中包含 IS NULL、IS NOT NULL、!= 這些條件時便不能使用索引查詢，只能使用全表掃描。

這種說法愈演愈烈，甚至被很多同學奉為真理。我們啥話也不說，舉個例子。假如我們有個表s1，結構如下：

CREATE TABLE s1 (
    id INT NOT NULL AUTO_INCREMENT,
    key1 VARCHAR(100),
    key2 VARCHAR(100),
    key3 VARCHAR(100),
    key_part1 VARCHAR(100),
    key_part2 VARCHAR(100),
    key_part3 VARCHAR(100),
    common_field VARCHAR(100),
    PRIMARY KEY (id),
    KEY idx_key1 (key1),
    KEY idx_key2 (key2),
    KEY idx_key3 (key3),
    KEY idx_key_part(key_part1, key_part2, key_part3)
) Engine=InnoDB CHARSET=utf8;
複製程式碼

這個表裡有10000條記錄：

mysql> SELECT COUNT(*) FROM s1;
+----------+
| COUNT(*) |
+----------+
|    10000 |
+----------+
1 row in set (0.00 sec)
複製程式碼

下邊我們直接貼幾個圖：

image_1dfqmch3p1f881eqmvb29gk1tom6e.png-40.7kB

image_1dfqmbf5616fb1g0b1trv13elsst61.png-40.7kB

image_1dfqmarklhku131o18rs15281min5k.png-40.2kB

上邊幾個查詢語句的WHERE子句中用了IS NULL、IS NOT NULL、!=這些條件，但是從它們的執行計劃中可以看出來，這些語句都採用了相應的二級索引執行查詢，而不是使用所謂的全表掃描，謠言不攻自破。當然，戳破這些謠言並不是本文的目的，本文來更細緻的分析一下這些查詢到底是怎麼執行的。

NULL值是怎麼在記錄中儲存的

在MySQL中，每一條記錄都有它固定的格式，我們以InnoDB儲存引擎的Compact行格式為例，來看一下NULL值是怎樣儲存的。在Compact行格式下，一條記錄是由下邊這幾個部分構成的：

image_1dfqmp377ebqgqf15e1tuv1qri6r.png-72.8kB

為了故事的順利發展，我們新建一個稱之為record_format_demo的表：

CREATE TABLE record_format_demo (
     c1 VARCHAR(10),
     c2 VARCHAR(10) NOT NULL,
     c3 CHAR(10),
     c4 VARCHAR(10)
 ) CHARSET=ascii ROW_FORMAT=COMPACT;
複製程式碼

因為我們的重點是NULL值是如何儲存在記錄中的，所以重點嘮叨一下行格式的NULL值列表部分，其他的部分可以到小冊中檢視。儲存NULL值的過程如下：

  1. 首先統計表中允許儲存NULL的列有哪些。

    我們前邊說過，主鍵列、被NOT NULL修飾的列都是不可以儲存NULL值的，所以在統計的時候不會把這些列算進去。比方說表record_format_demo的3個列c1、c3、c4都是允許儲存NULL值的，而c2列是被NOT NULL修飾，不允許儲存NULL值。

  2. 如果表中沒有允許儲存NULL的列，則NULL值列表也不存在了，否則將每個允許儲存NULL的列對應一個二進位制位，二進位制位按照列的順序逆序排列，二進位制位表示的意義如下：

    • 二進位制位的值為1時，代表該列的值為NULL。
    • 二進位制位的值為0時，代表該列的值不為NULL。

    因為表record_format_demo有3個值允許為NULL的列，所以這3個列和二進位制位的對應關係就是這樣：

    image_1dfqn3dt810cpog1l4710q637q78.png-19.3kB

    再一次強調，二進位制位按照列的順序逆序排列，所以第一個列c1和最後一個二進位制位對應。

  3. 設計InnoDB的大叔規定NULL值列表必須用整數個位元組的位表示，如果使用的二進位制位個數不是整數個位元組，則在位元組的高位補0。

    表record_format_demo只有3個值允許為NULL的列，對應3個二進位制位，不足一個位元組，所以在位元組的高位補0，效果就是這樣：

    image_1dfqn48071s0i104314m31isi1ks97l.png-37.7kB

    以此類推，如果一個表中有9個允許為NULL，那這個記錄的NULL值列表部分就需要2個位元組來表示了。

假設我們現在向record_format_demo表中插入一條記錄：

INSERT INTO record_format_demo(c1, c2, c3, c4)
    VALUES('eeee', 'fff', NULL, NULL);
複製程式碼

這條記錄的c1、c3、c4這3個列中c3和c4的值都為NULL，所以這3個列對應的二進位制位的情況就是：

image_1dfqng28g7df1l68r4737p3a882.png-38.6kB

所以這記錄的NULL值列表用十六進位制表示就是：0x06。

鍵值為NULL的記錄是怎麼在B+樹中存放的

對於InnoDB儲存引擎來說，記錄都是儲存在頁面中的（一個頁面預設是16KB大小），這些頁面可以作為B+樹的節點而組成一個索引，類似這種樣子（只是用下邊的圖舉個B+樹的例子而已，跟我們上邊列舉的表沒關係）：

image_1dfqnp86e76v16h31l7qk21v458f.png-296kB

聚簇索引和二級索引都對應著像上圖一樣的B+樹（也就是說有多少個索引就有多少棵對應的B+樹），不過：

  • 對於聚簇索引索引來說，頁面中的記錄是按照主鍵值進行排序的；而對於二級索引來說，頁面中的記錄是按照給定的索引列的值進行排序的。

  • 對於聚簇索引來說，B+樹每一層節點（頁面）都是按照頁中記錄的主鍵值大小進行排序的；而對於二級索引來說，B+樹每一層節點（頁面）都是按照頁中記錄的給定的索引列的值進行排序的。

  • 對於聚簇索引來說，B+樹葉子節點對應的頁面中儲存的是完整的使用者記錄（就是一條記錄中包含我們定義的所有列值，還包含一些InnoDB自己新增的一些隱藏列）；而對於二級索引來說，B+樹葉子節點對應的頁面中儲存的只是索引列的值 + 主鍵值。

按規定，一條記錄的主鍵值不允許儲存NULL值，所以下邊語句中的WHERE子句結果肯定為FALSE：

SELECT * FROM tbl_name WHERE primary_key IS NULL;
複製程式碼

像這樣的語句優化器自己就能判定出WHERE子句必定為NULL，所以壓根兒不會去執行它，不信我們看（Extra資訊提示WHERE子句壓根兒不成立）：

image_1dfqofhth2941mtorq72f1nqf8s.png-35.5kB

對於二級索引來說，索引列的值可能為NULL。那對於索引列值為NULL的二級索引記錄來說，它們被放在B+樹的哪裡呢？答案是：放在B+樹的最左邊。比方說我們有如下查詢語句：

SELECT * FROM s1 WHERE key1 IS NULL;
複製程式碼

那它的查詢示意圖就如下所示：

image_1dfqqjqnahm6176uta91j7j1q8ram.png-52.9kB

從圖中可以看出，對於s1表的二級索引idx_key1來說，值為NULL的二級索引記錄都被放在了B+樹的最左邊，這是因為設計InnoDB的大叔有這樣的規定：

We define the SQL null to be the smallest possible value of a field.

也就是說他們把SQL中的NULL值認為是列中最小的值。

在通過二級索引idx_key1對應的B+樹快速定位到葉子節點中符合條件的最左邊的那條記錄後，也就是本例中id值為521的那條記錄之後，就可以順著每條記錄都有的next_record屬性沿著由記錄組成的單向連結串列去獲取記錄了，直到某條記錄的key1列不為NULL。

小貼士： 通過B+樹快速定位到葉子節點的記錄的過程是靠一個所謂的頁目錄（Page Directory）做到的，不過這不是本文的重點，大家可以到小冊中翻看，都有詳細解釋。

使不使用索引的依據到底是什麼？

那既然IS NULL、IS NOT NULL、!=這些條件都可能使用到索引，那到底什麼時候索引，什麼時候採用全表掃描呢？

答案很簡單：成本。當然，關於如何定量的計算使用某個索引執行查詢的成本比較複雜，我們在小冊中花了很大的篇幅來嘮叨了。不過因為篇幅有限，我們在這裡只准備定性的分析一下。對於使用二級索引進行查詢來說，成本組成主要有兩個方面：

  • 讀取二級索引記錄的成本

  • 將二級索引記錄執行回表操作，也就是到聚簇索引中找到完整的使用者記錄的操作所付出的成本。

很顯然，要掃描的二級索引記錄條數越多，那麼需要執行的回表操作的次數也就越多，達到了某個比例時，使用二級索引執行查詢的成本也就超過了全表掃描的成本（舉一個極端的例子，比方說要掃描的全部的二級索引記錄，那就要對每條記錄執行一遍回表操作，自然不如直接掃描聚簇索引來的快）。

所以MySQL優化器在真正執行查詢之前，對於每個可能使用到的索引來說，都會預先計算一下需要掃描的二級索引記錄的數量，比方說對於下邊這個查詢：

SELECT * FROM s1 WHERE key1 IS NULL;
複製程式碼

優化器會分析出此查詢只需要查詢key1值為NULL的記錄，然後訪問一下二級索引idx_key1，看一下值為NULL的記錄有多少（如果符合條件的二級索引記錄數量較少，那麼統計結果是精確的，如果太多的話，會採用一定的手段計算一個模糊的值，當然演算法也比較麻煩，我們就不展開說了，小冊裡有說），這種在查詢真正執行前優化器就率先訪問索引來計算需要掃描的索引記錄數量的方式稱之為index dive。當然，對於某些查詢，比方說WHERE子句中有IN條件，並且IN條件中包含許多引數的話，比方說這樣：

SELECT * FROM s1 WHERE key1 IN ('a', 'b', 'c', ... , 'zzzzzzz');
複製程式碼

這樣的話需要統計的key1值所在的區間就太多了，這樣就不能採用index dive的方式去真正的訪問二級索引idx_key1，而是需要採用之前在背地裡產生的一些統計資料去估算匹配的二級索引記錄有多少條（很顯然根據統計資料去估算記錄條數比index dive的方式精確性差了很多）。

反正不論採用index dive還是依據統計資料估算，最終要得到一個需要掃描的二級索引記錄條數，如果這個條數佔整個記錄條數的比例特別大，那麼就趨向於使用全表掃描執行查詢，否則趨向於使用這個索引執行查詢。

理解了這個也就好理解為什麼在WHERE子句中出現IS NULL、IS NOT NULL、!=這些條件仍然可以使用索引，本質上都是優化器去計算一下對應的二級索引數量佔所有記錄數量的比值而已。

不信謠，不傳謠

大家可以看到，MySQL中決定使不使用某個索引執行查詢的依據很簡單：就是成本夠不夠小。而不是是否在WHERE子句中用了IS NULL、IS NOT NULL、!=這些條件。大家以後也多多闢謠吧，沒那麼複雜，只是一個成本而已。

題外話

寫文章挺累的，有時候你覺得閱讀挺流暢的，那其實是背後無數次修改的結果。如果你覺得不錯請幫忙轉發一下，萬分感謝～ 這裡是我的公眾號「我們都是小青蛙」，裡邊有更多技術乾貨，時不時扯一下犢子，歡迎關注：

MySQL中IS NULL、IS NOT NULL、!=不能用索引？胡扯！

相關文章