MySQL 的索引中不能使用 IS NULL、IS NOT NULL、!= 吗？

网上流传着这样一句话：

当MySQL WHERE子句IS NULL、IS NOT NULL、!=这些条件时，不能使用索引查询，只能使用全表扫描。

这个说法越来越流行，甚至很多学生都信以为真。啥也不说，直接举个例子。假设我们有一个表 s1，其结构如下：

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;
复制代码

此表中有 10,000 条记录：

mysql> SELECT COUNT(*) FROM s1;
+----------+
| COUNT(*) |
+----------+
|    10000 |
+----------+
1 row in set (0.00 sec)
复制代码

让我们在下面放一些查询图片： ❙ry WHERE 子句 是IS NULL、 IS NOT NULL，但是从他们的执行中可以看出，！计划，这些语句都使用适当的二级索引来执行查询，而不是使用所谓的全表扫描。谣言是自欺欺人的。当然，本文无意驳斥这些谣言。本文更详细地分析了这些查询的执行。

NULL值如何存储在记录中

MySQL中的所有记录都有固定的格式。我们以InnoDB存储引擎Compact的行格式为例，看看d的值是否为NULL。在Compact行格式中，记录由以下部分组成：

以此类推，如果一个表有 9 个元素为 NULL，那么 NULL 值列表​​ 需要这条记录的 2 部分。来表示字节。

假设我们现在向表中插入一条记录 record_format_demo:

INSERT INTO record_format_demo(c1, c2, c3, c4)
    VALUES('eeee', 'fff', NULL, NULL);
复制代码

这条记录是 c1c1 , c4 数值​​这三列中的c3和c4都是null，所以这三列对应的二进制位为：NULL值列表 表示为十六进制：0x06。

带NULL键值的记录如何存储在B+树中

以InnoDB存储引擎为例，记录是以页的形式存储的（默认页大小为16KB），这些页B+树的节点组成一个这样的索引（下图只是B+树的例子，与上表无关）：

。当记录A的id为521后，沿着记录的单向链表，沿着每条记录的next_record属性，直到某个key❀❀列❙

SELECT * FROM s1 WHERE key1 IS NULL;
复制代码

SELECT * FROM s1 WHERE key1 IN ('a', 'b', 'c', ... , 'zzzzzzz');
复制代码