最近寫了一系列關(guān)于MySQL索引相關(guān)的文章，幫大家系統(tǒng)全面地把索引這塊的知識(shí)豐富串聯(lián)起來(lái)，需要回顧或?qū)W習(xí)這方面的知識(shí)的朋友可以看看前面的文章。

今天這篇文章給大家分析和示例一下，MySQL中，當(dāng)查詢條件為IS NULL或 IS NOT NULL時(shí)，哪些情況會(huì)走索引，哪些情況下又不會(huì)走索引。最終結(jié)論可能與大家的直覺(jué)有所不同。

下面我們直接通過(guò)具體的實(shí)例來(lái)看看當(dāng)查詢條件為IS NULL或 IS NOT NULL時(shí)，索引的使用情況。

實(shí)例一：少量數(shù)據(jù)，使用索引

這里采用的MySQL數(shù)據(jù)庫(kù)版本為8.0.18，后續(xù)實(shí)例均采用此版本。

創(chuàng)建一個(gè)test表，創(chuàng)建語(yǔ)句如下：

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)建了索引。

實(shí)例演示

此時(shí)，我們來(lái)看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

通過(guò)上面的EXPLAIN語(yǔ)句，我們可以看到，當(dāng)查詢條件為IS NULL，且對(duì)應(yīng)查詢條件字段上有索引時(shí)，MySQL使用索引來(lái)處理IS NULL查詢條件。

再來(lái)看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

當(dāng)查詢條件為IS NOT NULL時(shí)，同樣使用了索引。

在上面的示例中，我們可以看到無(wú)論是IS NULL或 IS NOT NULL都使用索引。

實(shí)例二：大量數(shù)據(jù)，少量NULL值

該實(shí)例依舊采用上述表結(jié)構(gòu)，初始化3萬(wàn)數(shù)據(jù)，其中col1中的NULL值約占5%。

mysql> select count(*) from test;
+----------+
| count(*) |
+----------+
|    30000 |
+----------+

在上述情況下，我們?cè)賮?lái)看看兩個(gè)查詢語(yǔ)句的索引使用情況。

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

此時(shí)，IS NOT NULL查詢條件在執(zhí)行的過(guò)程中并沒(méi)有使用索引，而是采用了全表掃描。

這是因?yàn)?，?dāng)表中的大部分?jǐn)?shù)據(jù)都滿足 col1 IS NOT NULL 的條件（例如超過(guò)一半以上的記錄符合條件，本實(shí)例中為95%），MySQL 的查詢優(yōu)化器可能會(huì)認(rèn)為使用索引的代價(jià)高于全表掃描的代價(jià)，從而選擇全表掃描。

實(shí)例三：大量數(shù)據(jù)，大量NULL值

該實(shí)例依舊采用上述表結(jié)構(gòu)，初始化3萬(wàn)數(shù)據(jù)，其中col1中的NULL值約占95%。

在上述情況下，我們?cè)賮?lái)看看兩個(gè)查詢語(yǔ)句的索引使用情況。

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查詢條件也使用了索引。

通過(guò)上面的三個(gè)實(shí)例，我們可以看到，無(wú)論是IS NULL或IS NOT NULL都是有可能使用索引的。這也證明了網(wǎng)絡(luò)上一概而論并不正確。

下面，我們就具體分析一下IS NULL和IS NOT NULL是否走索引的核心決定性因素。

索引是如何存儲(chǔ)NULL值的？

在分析MySQL是否使用索引的原因之前，我們先要了解一下針對(duì)NULL值，在索引中是如何存儲(chǔ)的。

在MySQL的InnoDB引擎中，聚簇索引一般是以主鍵作為存儲(chǔ)依據(jù)，主鍵列的值不能為NULL。所以，針對(duì)這種情況，不存在NULL值存儲(chǔ)的問(wèn)題。

對(duì)于非聚簇索引中的NULL值，在大多數(shù)數(shù)據(jù)庫(kù)實(shí)現(xiàn)中，NULL值在索引結(jié)構(gòu)中有以下特點(diǎn)：

• B+樹的排序：NULL值通常被看作是最小值，因此索引存儲(chǔ)中，NULL會(huì)排在樹的最左邊。
• 鏈表形式存儲(chǔ)：B+樹葉子節(jié)點(diǎn)存儲(chǔ)數(shù)據(jù)的引用，而葉子節(jié)點(diǎn)之間是順序鏈接的，也就是說(shuō)，包含NULL的記錄會(huì)集中在最左側(cè)，沿著鏈表可以順序讀取這些記錄。
• 查找NULL值：當(dāng)查詢列值為NULL時(shí)，數(shù)據(jù)庫(kù)的索引機(jī)制可以通過(guò)掃描樹的最左側(cè)開始查找所有NULL值。

索引失效的原因

通過(guò)上面關(guān)于NULL值索引的存儲(chǔ)，我們可以看到，在MySQL的InnoDB數(shù)據(jù)引擎中，NULL也是“有序”的，也可以通過(guò)索引（從最左側(cè)開始）進(jìn)行查找的。大多數(shù)關(guān)系型數(shù)據(jù)庫(kù)也都支持對(duì) NULL 值進(jìn)行索引。

而真正決定IS NULL或 IS NOT NULL是否走索引的前提是索引能夠顯著降低執(zhí)行成本。在MySQL中，查詢優(yōu)化器會(huì)根據(jù)執(zhí)行成本決定是否使用索引，而不是單純地因?yàn)?nbsp;NULL 或 IS NULL 的條件導(dǎo)致索引失效。

例如：

• 如果通過(guò)索引查詢的結(jié)果集非常大（例如大多數(shù)記錄都為 NULL），那么索引命中的數(shù)據(jù)需要大量回表才能獲取完整的記錄，這種情況下，優(yōu)化器可能會(huì)選擇全表掃描，因?yàn)槿頀呙璧拇鷥r(jià)會(huì)更低。
• 如果通過(guò)索引可以顯著減少數(shù)據(jù)訪問(wèn)和回表的次數(shù)（例如查詢結(jié)果集很小或者覆蓋索引被使用），MySQL一般會(huì)選擇使用索引。

因此，關(guān)于NULL值索引失效，有以下相關(guān)結(jié)論：

• NULL 值可以走索引，但是否使用索引取決于具體的執(zhí)行成本。
• IS NULL 和 IS NOT NULL 本身不會(huì)直接導(dǎo)致索引失效，優(yōu)化器會(huì)根據(jù)數(shù)據(jù)量和數(shù)據(jù)分布動(dòng)態(tài)選擇索引或全表掃描。
• 非聚簇索引通常需要回表，回表成本過(guò)高時(shí)，優(yōu)化器可能放棄索引。
• 查詢性能優(yōu)化需要結(jié)合具體的場(chǎng)景和數(shù)據(jù)分布分析，而不能簡(jiǎn)單地說(shuō)某些條件下索引會(huì)失效。

小結(jié)

通過(guò)上面的示例我們可以看到，在MySQL中，查詢條件使用IS NULL或 IS NOT NULL，理論上都會(huì)走索引。部分不走索引的情況也是因?yàn)閮?yōu)化器判斷全表掃描的效率要高于使用索引，才導(dǎo)致放棄使用索引，而這與查詢條件為IS NULL或 IS NOT NULL本身沒(méi)有直接關(guān)系，只和執(zhí)行成本有關(guān)。

所以，針對(duì)IS NULL和 IS NOT NULL是否走索引，不能一概而論，還是要回歸到數(shù)據(jù)構(gòu)成本身，當(dāng)然，原則上MySQL是會(huì)選擇走索引的。