MySQL中，IS NULL和IS NOT NULL不會走索引？錯！
最近寫了一系列關(guān)于MySQL索引相關(guān)的文章，幫大家系統(tǒng)全面地把索引這塊的知識豐富串聯(lián)起來，需要回顧或?qū)W習(xí)這方面的知識的朋友可以看看前面的文章。
今天這篇文章給大家分析和示例一下，MySQL中，當查詢條件為IS NULL或 IS NOT NULL時，哪些情況會走索引，哪些情況下又不會走索引。最終結(jié)論可能與大家的直覺有所不同。
下面我們直接通過具體的實例來看看當查詢條件為IS NULL或 IS NOT NULL時，索引的使用情況。
實例一：少量數(shù)據(jù)，使用索引
這里采用的MySQL數(shù)據(jù)庫版本為8.0.18，后續(xù)實例均采用此版本。
創(chuàng)建一個test表，創(chuàng)建語句如下：
CREATE TABLEtest (
idINT PRIMARY KEY,
col1 INT,
col2 INT,
INDEX idx_col1 (col1)
);
-- 添加兩條數(shù)據(jù)
insertintotestvalues(1,null,1);
insertintotestvalues(2,null,2);其中在col1列上創(chuàng)建了索引。
實例演示
此時，我們來看IS NULL和 IS NOT NULL是否走索引。
mysql> explain SELECT * FROMtestWHERE col1 ISNULL \G
*************************** 1.row ***************************
id: 1
select_type: SIMPLE
table: test
partitions: NULL
type: ref
possible_keys: idx_col1
key: idx_col1
key_len: 5
ref: const
rows: 1
filtered: 100.00
Extra: Usingindex condition通過上面的EXPLAIN語句，我們可以看到，當查詢條件為IS NULL，且對應(yīng)查詢條件字段上有索引時，MySQL使用索引來處理IS NULL查詢條件。
再來看IS NOT NULL查詢條件：
mysql> explain SELECT * FROMtestWHERE col1 ISNOTNULL \G
*************************** 1.row ***************************
id: 1
select_type: SIMPLE
table: test
partitions: NULL
type: range
possible_keys: idx_col1
key: idx_col1
key_len: 5
ref: NULL
rows: 1
filtered: 100.00
Extra: Usingindex condition當查詢條件為IS NOT NULL時，同樣使用了索引。
在上面的示例中，我們可以看到無論是IS NULL或 IS NOT NULL都使用索引。
實例二：大量數(shù)據(jù)，少量NULL值
該實例依舊采用上述表結(jié)構(gòu)，初始化3萬數(shù)據(jù)，其中col1中的NULL值約占5%。
mysql> select count(*) from test;
+----------+
| count(*) |
+----------+
| 30000 |
+----------+在上述情況下，我們再來看看兩個查詢語句的索引使用情況。
IS NULL查詢條件：
mysql> explain SELECT * FROMtestWHERE col1 ISNULL \G
*************************** 1.row ***************************
id: 1
select_type: SIMPLE
table: test
partitions: NULL
type: ref
possible_keys: idx_col1
key: idx_col1
key_len: 5
ref: const
rows: 1551
filtered: 100.00
Extra: Usingindex condition可以看到，IS NULL查詢條件使用了索引。
IS NOT NULL查詢條件：
mysql> explain SELECT * FROMtestWHERE col1 ISNOTNULL \G
*************************** 1.row ***************************
id: 1
select_type: SIMPLE
table: test
partitions: NULL
type: ALL
possible_keys: idx_col1
key: NULL
key_len: NULL
ref: NULL
rows: 30570
filtered: 50.00
Extra: Usingwhere此時，IS NOT NULL查詢條件在執(zhí)行的過程中并沒有使用索引，而是采用了全表掃描。
這是因為，當表中的大部分數(shù)據(jù)都滿足 col1 IS NOT NULL 的條件（例如超過一半以上的記錄符合條件，本實例中為95%），MySQL 的查詢優(yōu)化器可能會認為使用索引的代價高于全表掃描的代價，從而選擇全表掃描。
實例三：大量數(shù)據(jù)，大量NULL值
該實例依舊采用上述表結(jié)構(gòu)，初始化3萬數(shù)據(jù)，其中col1中的NULL值約占95%。
在上述情況下，我們再來看看兩個查詢語句的索引使用情況。
IS NULL查詢條件：
mysql> explain SELECT * FROMtestWHERE col1 ISNULL \G
*************************** 1.row ***************************
id: 1
select_type: SIMPLE
table: test
partitions: NULL
type: ref
possible_keys: idx_col1
key: idx_col1
key_len: 5
ref: const
rows: 14957
filtered: 100.00
Extra: Usingindex condition可以看到，IS NULL查詢條件使用了索引。
IS NOT NULL查詢條件：
mysql> explain SELECT * FROMtestWHERE col1 ISNOTNULL \G
*************************** 1.row ***************************
id: 1
select_type: SIMPLE
table: test
partitions: NULL
type: range
possible_keys: idx_col1
key: idx_col1
key_len: 5
ref: NULL
rows: 1558
filtered: 100.00
Extra: Usingindex condition可以看到，IS NOT NULL查詢條件也使用了索引。
通過上面的三個實例，我們可以看到，無論是IS NULL或IS NOT NULL都是有可能使用索引的。這也證明了網(wǎng)絡(luò)上一概而論并不正確。
下面，我們就具體分析一下IS NULL和IS NOT NULL是否走索引的核心決定性因素。
索引是如何存儲NULL值的？
在分析MySQL是否使用索引的原因之前，我們先要了解一下針對NULL值，在索引中是如何存儲的。
在MySQL的InnoDB引擎中，聚簇索引一般是以主鍵作為存儲依據(jù)，主鍵列的值不能為NULL。所以，針對這種情況，不存在NULL值存儲的問題。
對于非聚簇索引中的NULL值，在大多數(shù)數(shù)據(jù)庫實現(xiàn)中，NULL值在索引結(jié)構(gòu)中有以下特點：
- B+樹的排序：NULL值通常被看作是最小值，因此索引存儲中，NULL會排在樹的最左邊。
- 鏈表形式存儲：B+樹葉子節(jié)點存儲數(shù)據(jù)的引用，而葉子節(jié)點之間是順序鏈接的，也就是說，包含NULL的記錄會集中在最左側(cè)，沿著鏈表可以順序讀取這些記錄。
- 查找NULL值：當查詢列值為NULL時，數(shù)據(jù)庫的索引機制可以通過掃描樹的最左側(cè)開始查找所有NULL值。
索引失效的原因
通過上面關(guān)于NULL值索引的存儲，我們可以看到，在MySQL的InnoDB數(shù)據(jù)引擎中，NULL也是“有序”的，也可以通過索引（從最左側(cè)開始）進行查找的。大多數(shù)關(guān)系型數(shù)據(jù)庫也都支持對 NULL 值進行索引。
而真正決定IS NULL或 IS NOT NULL是否走索引的前提是索引能夠顯著降低執(zhí)行成本。在MySQL中，查詢優(yōu)化器會根據(jù)執(zhí)行成本決定是否使用索引，而不是單純地因為 NULL 或 IS NULL 的條件導(dǎo)致索引失效。
例如：
- 如果通過索引查詢的結(jié)果集非常大（例如大多數(shù)記錄都為
NULL），那么索引命中的數(shù)據(jù)需要大量回表才能獲取完整的記錄，這種情況下，優(yōu)化器可能會選擇全表掃描，因為全表掃描的代價會更低。 - 如果通過索引可以顯著減少數(shù)據(jù)訪問和回表的次數(shù)（例如查詢結(jié)果集很小或者覆蓋索引被使用），MySQL一般會選擇使用索引。
因此，關(guān)于NULL值索引失效，有以下相關(guān)結(jié)論：
- NULL 值可以走索引，但是否使用索引取決于具體的執(zhí)行成本。
IS NULL和IS NOT NULL本身不會直接導(dǎo)致索引失效，優(yōu)化器會根據(jù)數(shù)據(jù)量和數(shù)據(jù)分布動態(tài)選擇索引或全表掃描。- 非聚簇索引通常需要回表，回表成本過高時，優(yōu)化器可能放棄索引。
- 查詢性能優(yōu)化需要結(jié)合具體的場景和數(shù)據(jù)分布分析，而不能簡單地說某些條件下索引會失效。
小結(jié)
通過上面的示例我們可以看到，在MySQL中，查詢條件使用IS NULL或 IS NOT NULL，理論上都會走索引。部分不走索引的情況也是因為優(yōu)化器判斷全表掃描的效率要高于使用索引，才導(dǎo)致放棄使用索引，而這與查詢條件為IS NULL或 IS NOT NULL本身沒有直接關(guān)系，只和執(zhí)行成本有關(guān)。
所以，針對IS NULL和 IS NOT NULL是否走索引，不能一概而論，還是要回歸到數(shù)據(jù)構(gòu)成本身，當然，原則上MySQL是會選擇走索引的。
