不知道從什么時候開始，網(wǎng)上流傳著這么一個說法：
MySQL的WHERE子句中包含 IS NULL、IS NOT NULL、!= 這些條件時便不能使用索引查詢，只能使用全表掃描。
這種說法愈演愈烈，甚至被很多同學(xué)奉為真理。 咱啥話也不說，舉個例子。 假如我們有個表 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í)行計劃中可以看出來，這些語句都采用了相應(yīng)的二級索引執(zhí)行查詢，而不是使用所謂的全表掃描，謠言不攻自破。 當(dāng)然，戳破這些謠言并不是本文的目的，本文來更細(xì)致的分析一下這些查詢到底是怎么執(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;
因?yàn)槲覀兊闹攸c(diǎn)是 NULL 值是如何存儲在記錄中的，所以重點(diǎn)嘮叨一下行格式的 NULL值列表 部分，其他的部分可以到小冊中查看。 存儲 NULL 值的過程如下：
首先統(tǒng)計表中允許存儲 NULL 的列有哪些。
我們前邊說過，主鍵列、被 NOT NULL 修飾的列都是不可以存儲 NULL 值的，所以在統(tǒng)計的時候不會把這些列算進(jìn)去。 比方說表 record_format_demo 的3個列 c1 、 c3 、 c4 都是允許存儲 NULL 值的，而 c2 列是被 NOT NULL 修飾，不允許存儲 NULL 值。
如果表中沒有允許存儲 NULL 的列，則 NULL值列表 也不存在了，否則將每個允許存儲 NULL 的列對應(yīng)一個二進(jìn)制位，二進(jìn)制位按照列的順序逆序排列，二進(jìn)制位表示的意義如下：
因?yàn)楸?/span>record_format_demo 有3個值允許為 NULL 的列，所以這3個列和二進(jìn)制位的對應(yīng)關(guān)系就是這樣：
再一次強(qiáng)調(diào)，二進(jìn)制位按照列的順序逆序排列，所以第一個列 c1 和最后一個二進(jìn)制位對應(yīng)。
設(shè)計 InnoDB 的大叔規(guī)定 NULL值列表 必須用整數(shù)個字節(jié)的位表示，如果使用的二進(jìn)制位個數(shù)不是整數(shù)個字節(jié)，則在字節(jié)的高位補(bǔ)0。
表 record_format_demo 只有3個值允許為 NULL 的列，對應(yīng)3個二進(jìn)制位，不足一個字節(jié)，所以在字節(jié)的高位補(bǔ)0，效果就是這樣：
以此類推，如果一個表中有9個允許為 NULL ，那這個記錄的 NULL值列表 部分就需要2個字節(jié)來表示了。
假設(shè)我們現(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個列對應(yīng)的二進(jìn)制位的情況就是：
所以這記錄的 NULL值列表 用十六進(jìn)制表示就是： 0x06 。
鍵值為NULL的記錄是怎么在B+樹中存放的 對于InnoDB存儲引擎來說，記錄都是存儲在頁面中的（一個頁面默認(rèn)是16KB大?。?，這些頁面可以作為 B+ 樹的節(jié)點(diǎn)而組成一個索引，類似這種樣子（只是用下邊的圖舉個B+樹的例子而已，跟我們上邊列舉的表沒關(guān)系）：
聚簇索引和二級索引都對應(yīng)著像上圖一樣的 B+ 樹（也就是說有多少個索引就有多少棵對應(yīng)的 B+ 樹），不過：
對于聚簇索引索引來說，頁面中的記錄是按照主鍵值進(jìn)行排序的； 而對于二級索引來說，頁面中的記錄是按照給定的索引列的值進(jìn)行排序的。
對于聚簇索引來說，B+樹每一層節(jié)點(diǎn)（頁面）都是按照頁中記錄的主鍵值大小進(jìn)行排序的； 而對于二級索引來說，B+樹每一層節(jié)點(diǎn)（頁面）都是按照頁中記錄的給定的索引列的值進(jìn)行排序的。
對于聚簇索引來說，B+樹葉子節(jié)點(diǎn)對應(yīng)的頁面中存儲的是完整的用戶記錄（就是一條記錄中包含我們定義的所有列值，還包含一些InnoDB自己添加的一些隱藏列）； 而對于二級索引來說，B+樹葉子節(jié)點(diǎn)對應(yīng)的頁面中存儲的只是 索引列的值 + 主鍵值 。
按規(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+ 樹的最左邊，這是因?yàn)樵O(shè)計 InnoDB 的大叔有這樣的規(guī)定：
We define the SQL null to be the smallest possible value of a field.
也就是說他們把SQL中的 NULL 值認(rèn)為是列中最小的值。
在通過二級索引 idx_key1 對應(yīng)的 B+ 樹快速定位到葉子節(jié)點(diǎn)中符合條件的最左邊的那條記錄后，也就是本例中 id 值為 521 的那條記錄之后，就可以順著每條記錄都有的 next_record 屬性沿著由記錄組成的單向鏈表去獲取記錄了，直到某條記錄的 key1 列不為NULL。
小貼士： 通過B+樹快速定位到葉子節(jié)點(diǎn)的記錄的過程是靠一個所謂的頁目錄（Page Directory）做到的，不過這不是本文的重點(diǎn)，大家可以到小冊中翻看，都有詳細(xì)解釋。
使不使用索引的依據(jù)到底是什么？ 那既然 IS NULL 、 IS NOT NULL 、 != 這些條件都可能使用到索引，那到底什么時候索引，什么時候采用全表掃描呢？
答案很簡單： 成本。 當(dāng)然，關(guān)于如何定量的計算使用某個索引執(zhí)行查詢的成本比較復(fù)雜，我們在小冊中花了很大的篇幅來嘮叨了。 不過因?yàn)槠邢?，我們在這里只準(zhǔn)備定性的分析一下。 對于使用二級索引進(jìn)行查詢來說，成本組成主要有兩個方面：
很顯然，要掃描的二級索引記錄條數(shù)越多，那么需要執(zhí)行的回表操作的次數(shù)也就越多 ，達(dá)到了某個比例時，使用二級索引執(zhí)行查詢的成本也就超過了全表掃描的成本（舉一個極端的例子，比方說要掃描的全部的二級索引記錄，那就要對每條記錄執(zhí)行一遍回表操作，自然不如直接掃描聚簇索引來的快）。
所以MySQL優(yōu)化器在真正執(zhí)行查詢之前，對于每個可能使用到的索引來說，都會預(yù)先計算一下需要掃描的二級索引記錄的數(shù)量，比方說對于下邊這個查詢：
SELECT * FROM s1 WHERE key1 IS NULL;
優(yōu)化器會分析出此查詢只需要查找 key1 值為 NULL 的記錄，然后訪問一下二級索引 idx_key1 ，看一下值為 NULL 的記錄有多少（如果符合條件的二級索引記錄數(shù)量較少，那么統(tǒng)計結(jié)果是精確的，如果太多的話，會采用一定的手段計算一個模糊的值，當(dāng)然算法也比較麻煩，我們就不展開說了，小冊里有說），這種在查詢真正執(zhí)行前優(yōu)化器就率先訪問索引來計算需要掃描的索引記錄數(shù)量的方式稱之為 index dive 。 當(dāng)然，對于某些查詢，比方說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)化器去計算一下對應(yīng)的二級索引數(shù)量占所有記錄數(shù)量的比值而已。
不信謠，不傳謠 大家可以看到，MySQL中決定使不使用某個索引執(zhí)行查詢的依據(jù)很簡單： 就是成本夠不夠小。 而不是是否在WHERE子句中用了 IS NULL 、 IS NOT NULL 、 != 這些條件。 大家以后也多多辟謠吧，沒那么復(fù)雜，只是一個成本而已。
注意： 公眾號文章寫的匆忙，盡量保證沒有錯誤。 如有幫助，點(diǎn)個好看～
