不知道從什么時(shí)候開始玩裙，網(wǎng)上流傳著這么一個(gè)說法：
MySQL的WHERE子句中包含 IS NULL兼贸、IS NOT NULL段直、!= 這些條件時(shí)便不能使用索引查詢，只能使用全表掃描溶诞。
這種說法愈演愈烈鸯檬，甚至被很多同學(xué)奉為真理。咱啥話也不說螺垢，舉個(gè)例子喧务。假如我們有個(gè)表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;
這個(gè)表里有10000條記錄：
mysql> SELECT COUNT(*) FROM s1;
+----------+
| COUNT(*) |
+----------+
| 10000 |
+----------+
1 row in set (0.00 sec)
下邊我們直接貼幾個(gè)圖：
上邊幾個(gè)查詢語句的WHERE子句中用了IS NULL枉圃、IS NOT NULL功茴、!=這些條件，但是從它們的執(zhí)行計(jì)劃中可以看出來孽亲，這些語句都采用了相應(yīng)的二級索引執(zhí)行查詢坎穿，而不是使用所謂的全表掃描，謠言不攻自破返劲。當(dāng)然玲昧，戳破這些謠言并不是本文的目的，本文來更細(xì)致的分析一下這些查詢到底是怎么執(zhí)行的篮绿。
NULL值是怎么在記錄中存儲的
在MySQL中孵延，每一條記錄都有它固定的格式，我們以InnoDB存儲引擎的Compact行格式為例亲配，來看一下NULL值是怎樣存儲的尘应。在Compact行格式下，一條記錄是由下邊這幾個(gè)部分構(gòu)成的：
為了故事的順利發(fā)展弃榨，我們新建一個(gè)稱之為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)計(jì)表中允許存儲
NULL的列有哪些娜饵。我們前邊說過，主鍵列官辈、被
NOT NULL修飾的列都是不可以存儲NULL值的箱舞，所以在統(tǒng)計(jì)的時(shí)候不會把這些列算進(jìn)去。比方說表record_format_demo的3個(gè)列c1拳亿、c3晴股、c4都是允許存儲NULL值的，而c2列是被NOT NULL修飾肺魁，不允許存儲NULL值电湘。 -
如果表中沒有允許存儲
NULL的列，則NULL值列表也不存在了，否則將每個(gè)允許存儲NULL的列對應(yīng)一個(gè)二進(jìn)制位寂呛，二進(jìn)制位按照列的順序逆序排列怎诫，二進(jìn)制位表示的意義如下：因?yàn)楸?code>record_format_demo有3個(gè)值允許為
NULL的列，所以這3個(gè)列和二進(jìn)制位的對應(yīng)關(guān)系就是這樣：image再一次強(qiáng)調(diào)贷痪，二進(jìn)制位按照列的順序逆序排列幻妓，所以第一個(gè)列
c1和最后一個(gè)二進(jìn)制位對應(yīng)。
二進(jìn)制位的值為
1時(shí)劫拢，代表該列的值為NULL肉津。二進(jìn)制位的值為
0時(shí)，代表該列的值不為NULL舱沧。
-
設(shè)計(jì)
InnoDB的大叔規(guī)定NULL值列表必須用整數(shù)個(gè)字節(jié)的位表示妹沙，如果使用的二進(jìn)制位個(gè)數(shù)不是整數(shù)個(gè)字節(jié)，則在字節(jié)的高位補(bǔ)0狗唉。表
record_format_demo只有3個(gè)值允許為NULL的列初烘，對應(yīng)3個(gè)二進(jìn)制位，不足一個(gè)字節(jié)分俯，所以在字節(jié)的高位補(bǔ)0肾筐，效果就是這樣：以此類推，如果一個(gè)表中有9個(gè)允許為
NULL缸剪，那這個(gè)記錄的NULL值列表部分就需要2個(gè)字節(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個(gè)列中c3和c4的值都為NULL唬渗，所以這3個(gè)列對應(yīng)的二進(jìn)制位的情況就是：
所以這記錄的NULL值列表用十六進(jìn)制表示就是：0x06。
鍵值為NULL的記錄是怎么在B+樹中存放的
對于InnoDB存儲引擎來說奋渔，記錄都是存儲在頁面中的（一個(gè)頁面默認(rèn)是16KB大心魇拧），這些頁面可以作為B+樹的節(jié)點(diǎn)而組成一個(gè)索引嫉鲸，類似這種樣子（只是用下邊的圖舉個(gè)B+樹的例子而已撑蒜，跟我們上邊列舉的表沒關(guān)系）：
聚簇索引和二級索引都對應(yīng)著像上圖一樣的B+樹（也就是說有多少個(gè)索引就有多少棵對應(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è)計(jì)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)的記錄的過程是靠一個(gè)所謂的頁目錄（Page Directory）做到的芬为，不過這不是本文的重點(diǎn)萄金，大家可以到小冊中翻看，都有詳細(xì)解釋媚朦。
使不使用索引的依據(jù)到底是什么氧敢？
那既然IS NULL、IS NOT NULL询张、!=這些條件都可能使用到索引孙乖，那到底什么時(shí)候索引，什么時(shí)候采用全表掃描呢瑞侮？
答案很簡單：成本的圆。當(dāng)然，關(guān)于如何定量的計(jì)算使用某個(gè)索引執(zhí)行查詢的成本比較復(fù)雜半火，我們在小冊中花了很大的篇幅來嘮叨了越妈。不過因?yàn)槠邢蓿覀冊谶@里只準(zhǔn)備定性的分析一下钮糖。對于使用二級索引進(jìn)行查詢來說梅掠，成本組成主要有兩個(gè)方面：
讀取二級索引記錄的成本
將二級索引記錄執(zhí)行回表操作酌住，也就是到聚簇索引中找到完整的用戶記錄的操作所付出的成本。
很顯然阎抒，要掃描的二級索引記錄條數(shù)越多酪我，那么需要執(zhí)行的回表操作的次數(shù)也就越多，達(dá)到了某個(gè)比例時(shí)且叁，使用二級索引執(zhí)行查詢的成本也就超過了全表掃描的成本（舉一個(gè)極端的例子都哭，比方說要掃描的全部的二級索引記錄，那就要對每條記錄執(zhí)行一遍回表操作逞带，自然不如直接掃描聚簇索引來的快）欺矫。
所以MySQL優(yōu)化器在真正執(zhí)行查詢之前，對于每個(gè)可能使用到的索引來說展氓，都會預(yù)先計(jì)算一下需要掃描的二級索引記錄的數(shù)量穆趴，比方說對于下邊這個(gè)查詢：
SELECT * FROM s1 WHERE key1 IS NULL;
優(yōu)化器會分析出此查詢只需要查找key1值為NULL的記錄，然后訪問一下二級索引idx_key1遇汞，看一下值為NULL的記錄有多少（如果符合條件的二級索引記錄數(shù)量較少未妹，那么統(tǒng)計(jì)結(jié)果是精確的，如果太多的話空入，會采用一定的手段計(jì)算一個(gè)模糊的值络它，當(dāng)然算法也比較麻煩，我們就不展開說了执庐，小冊里有說）酪耕，這種在查詢真正執(zhí)行前優(yōu)化器就率先訪問索引來計(jì)算需要掃描的索引記錄數(shù)量的方式稱之為index dive。當(dāng)然轨淌，對于某些查詢迂烁，比方說WHERE子句中有IN條件，并且IN條件中包含許多參數(shù)的話递鹉，比方說這樣：
SELECT * FROM s1 WHERE key1 IN ('a', 'b', 'c', ... , 'zzzzzzz');
這樣的話需要統(tǒng)計(jì)的key1值所在的區(qū)間就太多了盟步，這樣就不能采用index dive的方式去真正的訪問二級索引idx_key1，而是需要采用之前在背地里產(chǎn)生的一些統(tǒng)計(jì)數(shù)據(jù)去估算匹配的二級索引記錄有多少條（很顯然根據(jù)統(tǒng)計(jì)數(shù)據(jù)去估算記錄條數(shù)比index dive的方式精確性差了很多）躏结。
反正不論采用index dive還是依據(jù)統(tǒng)計(jì)數(shù)據(jù)估算却盘，最終要得到一個(gè)需要掃描的二級索引記錄條數(shù)，如果這個(gè)條數(shù)占整個(gè)記錄條數(shù)的比例特別大媳拴，那么就趨向于使用全表掃描執(zhí)行查詢黄橘，否則趨向于使用這個(gè)索引執(zhí)行查詢。
理解了這個(gè)也就好理解為什么在WHERE子句中出現(xiàn)IS NULL屈溉、IS NOT NULL塞关、!=這些條件仍然可以使用索引，本質(zhì)上都是優(yōu)化器去計(jì)算一下對應(yīng)的二級索引數(shù)量占所有記錄數(shù)量的比值而已子巾。
不信謠帆赢，不傳謠
大家可以看到小压，MySQL中決定使不使用某個(gè)索引執(zhí)行查詢的依據(jù)很簡單：就是成本夠不夠小。而不是是否在WHERE子句中用了IS NULL椰于、IS NOT NULL怠益、!=這些條件。大家以后也多多辟謠吧瘾婿，沒那么復(fù)雜蜻牢，只是一個(gè)成本而已瘾带。
歡迎希望文章對你有幫助芦劣，喜歡的可以關(guān)注作者給個(gè)贊哦
