MySQL中IS NULL、IS NOT NULL、!=不能用索引？胡扯！
不知道從什么時候開始，網(wǎng)上流傳著這么一個說法：
MySQL的WHERE子句中包含 IS NULL、IS NOT NULL、!= 這些條件時便不能使用索引查詢，只能使用全表掃描。
這種說法愈演愈烈，甚至被很多同學奉為真理。咱啥話也不說，舉個例子。假如我們有個表s1，結(jié)構(gòu)如下：
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)
下邊我們直接貼幾個圖：
上邊幾個查詢語句的WHERE子句中用了IS NULL、IS NOT NULL、!=這些條件，但是從它們的執(zhí)行計劃中可以看出來，這些語句都采用了相應的二級索引執(zhí)行查詢，而不是使用所謂的全表掃描，謠言不攻自破。當然，戳破這些謠言并不是本文的目的，本文來更細致的分析一下這些查詢到底是怎么執(zhí)行的。
NULL值是怎么在記錄中存儲的
在MySQL中，每一條記錄都有它固定的格式，我們以InnoDB存儲引擎的Compact行格式為例，來看一下NULL值是怎樣存儲的。在Compact行格式下，一條記錄是由下邊這幾個部分構(gòu)成的：
為了故事的順利發(fā)展，我們新建一個稱之為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值的過程如下：
首先統(tǒng)計表中允許存儲
NULL的列有哪些。我們前邊說過，主鍵列、被
NOT NULL修飾的列都是不可以存儲NULL值的，所以在統(tǒng)計的時候不會把這些列算進去。比方說表record_format_demo的3個列c1、c3、c4都是允許存儲NULL值的，而c2列是被NOT NULL修飾，不允許存儲NULL值。如果表中沒有允許存儲
NULL的列，則NULL值列表也不存在了，否則將每個允許存儲NULL的列對應一個二進制位，二進制位按照列的順序逆序排列，二進制位表示的意義如下：因為表
record_format_demo有3個值允許為NULL的列，所以這3個列和二進制位的對應關(guān)系就是這樣：
再一次強調(diào)，二進制位按照列的順序逆序排列，所以第一個列
c1和最后一個二進制位對應。二進制位的值為
1時，代表該列的值為NULL。二進制位的值為
0時，代表該列的值不為NULL。設計
InnoDB的大叔規(guī)定NULL值列表必須用整數(shù)個字節(jié)的位表示，如果使用的二進制位個數(shù)不是整數(shù)個字節(jié)，則在字節(jié)的高位補0。表
record_format_demo只有3個值允許為NULL的列，對應3個二進制位，不足一個字節(jié)，所以在字節(jié)的高位補0，效果就是這樣：
以此類推，如果一個表中有9個允許為
NULL，那這個記錄的NULL值列表部分就需要2個字節(jié)來表示了。
假設我們現(xiàn)在向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個列對應的二進制位的情況就是：
所以這記錄的NULL值列表用十六進制表示就是：0x06。
鍵值為NULL的記錄是怎么在B+樹中存放的
對于InnoDB存儲引擎來說，記錄都是存儲在頁面中的（一個頁面默認是16KB大小），這些頁面可以作為B+樹的節(jié)點而組成一個索引，類似這種樣子（只是用下邊的圖舉個B+樹的例子而已，跟我們上邊列舉的表沒關(guān)系）：
聚簇索引和二級索引都對應著像上圖一樣的B+樹（也就是說有多少個索引就有多少棵對應的B+樹），不過：
對于聚簇索引索引來說，頁面中的記錄是按照主鍵值進行排序的；而對于二級索引來說，頁面中的記錄是按照給定的索引列的值進行排序的。
對于聚簇索引來說，B+樹每一層節(jié)點（頁面）都是按照頁中記錄的主鍵值大小進行排序的；而對于二級索引來說，B+樹每一層節(jié)點（頁面）都是按照頁中記錄的給定的索引列的值進行排序的。
對于聚簇索引來說，B+樹葉子節(jié)點對應的頁面中存儲的是完整的用戶記錄（就是一條記錄中包含我們定義的所有列值，還包含一些InnoDB自己添加的一些隱藏列）；而對于二級索引來說，B+樹葉子節(jié)點對應的頁面中存儲的只是
索引列的值 + 主鍵值。
按規(guī)定，一條記錄的主鍵值不允許存儲NULL值，所以下邊語句中的WHERE子句結(jié)果肯定為FALSE：
SELECT * FROM tbl_name WHERE primary_key IS NULL;
像這樣的語句優(yōu)化器自己就能判定出WHERE子句必定為NULL，所以壓根兒不會去執(zhí)行它，不信我們看（Extra信息提示W(wǎng)HERE子句壓根兒不成立）：
對于二級索引來說，索引列的值可能為NULL。那對于索引列值為NULL的二級索引記錄來說，它們被放在B+樹的哪里呢？答案是：放在B+樹的最左邊。比方說我們有如下查詢語句：
SELECT * FROM s1 WHERE key1 IS NULL;
那它的查詢示意圖就如下所示：
從圖中可以看出，對于s1表的二級索引idx_key1來說，值為NULL的二級索引記錄都被放在了B+樹的最左邊，這是因為設計InnoDB的大叔有這樣的規(guī)定：
We define the SQL null to be the smallest possible value of a field.
也就是說他們把SQL中的NULL值認為是列中最小的值。
在通過二級索引idx_key1對應的B+樹快速定位到葉子節(jié)點中符合條件的最左邊的那條記錄后，也就是本例中id值為521的那條記錄之后，就可以順著每條記錄都有的next_record屬性沿著由記錄組成的單向鏈表去獲取記錄了，直到某條記錄的key1列不為NULL。
小貼士： 通過B+樹快速定位到葉子節(jié)點的記錄的過程是靠一個所謂的頁目錄（Page Directory）做到的，不過這不是本文的重點，大家可以到小冊中翻看，都有詳細解釋。
使不使用索引的依據(jù)到底是什么？
那既然IS NULL、IS NOT NULL、!=這些條件都可能使用到索引，那到底什么時候索引，什么時候采用全表掃描呢？
答案很簡單：成本。當然，關(guān)于如何定量的計算使用某個索引執(zhí)行查詢的成本比較復雜，我們在小冊中花了很大的篇幅來嘮叨了。不過因為篇幅有限，我們在這里只準備定性的分析一下。對于使用二級索引進行查詢來說，成本組成主要有兩個方面：
讀取二級索引記錄的成本
將二級索引記錄執(zhí)行回表操作，也就是到聚簇索引中找到完整的用戶記錄的操作所付出的成本。
很顯然，要掃描的二級索引記錄條數(shù)越多，那么需要執(zhí)行的回表操作的次數(shù)也就越多，達到了某個比例時，使用二級索引執(zhí)行查詢的成本也就超過了全表掃描的成本（舉一個極端的例子，比方說要掃描的全部的二級索引記錄，那就要對每條記錄執(zhí)行一遍回表操作，自然不如直接掃描聚簇索引來的快）。
所以MySQL優(yōu)化器在真正執(zhí)行查詢之前，對于每個可能使用到的索引來說，都會預先計算一下需要掃描的二級索引記錄的數(shù)量，比方說對于下邊這個查詢：
SELECT * FROM s1 WHERE key1 IS NULL;
優(yōu)化器會分析出此查詢只需要查找key1值為NULL的記錄，然后訪問一下二級索引idx_key1，看一下值為NULL的記錄有多少（如果符合條件的二級索引記錄數(shù)量較少，那么統(tǒng)計結(jié)果是精確的，如果太多的話，會采用一定的手段計算一個模糊的值，當然算法也比較麻煩，我們就不展開說了，小冊里有說），這種在查詢真正執(zhí)行前優(yōu)化器就率先訪問索引來計算需要掃描的索引記錄數(shù)量的方式稱之為index dive。當然，對于某些查詢，比方說WHERE子句中有IN條件，并且IN條件中包含許多參數(shù)的話，比方說這樣：
SELECT * FROM s1 WHERE key1 IN ('a', 'b', 'c', ... , 'zzzzzzz');
這樣的話需要統(tǒng)計的key1值所在的區(qū)間就太多了，這樣就不能采用index dive的方式去真正的訪問二級索引idx_key1，而是需要采用之前在背地里產(chǎn)生的一些統(tǒng)計數(shù)據(jù)去估算匹配的二級索引記錄有多少條（很顯然根據(jù)統(tǒng)計數(shù)據(jù)去估算記錄條數(shù)比index dive的方式精確性差了很多）。
反正不論采用index dive還是依據(jù)統(tǒng)計數(shù)據(jù)估算，最終要得到一個需要掃描的二級索引記錄條數(shù)，如果這個條數(shù)占整個記錄條數(shù)的比例特別大，那么就趨向于使用全表掃描執(zhí)行查詢，否則趨向于使用這個索引執(zhí)行查詢。
理解了這個也就好理解為什么在WHERE子句中出現(xiàn)IS NULL、IS NOT NULL、!=這些條件仍然可以使用索引，本質(zhì)上都是優(yōu)化器去計算一下對應的二級索引數(shù)量占所有記錄數(shù)量的比值而已。
不信謠，不傳謠
大家可以看到，MySQL中決定使不使用某個索引執(zhí)行查詢的依據(jù)很簡單：就是成本夠不夠小。而不是是否在WHERE子句中用了IS NULL、IS NOT NULL、!=這些條件。大家以后也多多辟謠吧，沒那么復雜，只是一個成本而已。
推薦閱讀：
完全整理 | 365篇高質(zhì)技術(shù)文章目錄整理
專注服務器后臺技術(shù)棧知識總結(jié)分享
歡迎關(guān)注交流共同進步