MySQL 优化 —— IS NULL 优化_mysql is null 优化

引言

本博客翻译自 MySQL 官网：IS NULL Optimization， MySQL版本 5.7。

MySQL 对 IS NULL 的优化

MySQL 可以对 IS NULL 执行和常量等值判断（列名 = 常量表达式，如name = 'Tom'）相同的优化。MySQL 可以利用索引和范围来搜索空值。

例如：

SELECT * FROM tbl_name WHERE key_col IS NULL;
 
SELECT * FROM tbl_name WHERE key_col <=> NULL;
 
SELECT * FROM tbl_name
  WHERE key_col=const1 OR key_col=const2 OR key_col IS NULL;

如果 WHERE 子句包含一个 IS NULL 条件，而这个列却被声明为 NOT NULL，那么IS NULL表达式就会被优化掉。当列值未声明为非空，那么就不会发生这种优化（例如， LEFT JOIN 右侧的表）。

MySQL 也会优化这样的条件组合：col_name = expr OR col_name IS NULL ，这是在已解析的子查询中较常见的形式。如果发生了这种优化，那么 EXPLAIN 执行计划会出现 ref_or_null 。

This optimization can handle one IS NULL for any key part. 对于任意的索引部分（任意的索引列），这种优化只会处理索引中的一个 IS NULL 的条件（博主：这句原文我思考了很久，不知道翻译的对不对）。

下面的查询示例都会被优化，假设有一张表 t2 中有一个索引覆盖了字段 a 和 b。

SELECT * FROM t1 WHERE t1.a=expr OR t1.a IS NULL;
 
SELECT * FROM t1, t2 WHERE t1.a=t2.a OR t2.a IS NULL;
 
SELECT * FROM t1, t2
  WHERE (t1.a=t2.a OR t2.a IS NULL) AND t2.b=t1.b;
 
SELECT * FROM t1, t2
  WHERE t1.a=t2.a AND (t2.b=t1.b OR t2.b IS NULL);
 
SELECT * FROM t1, t2
  WHERE (t1.a=t2.a AND t2.a IS NULL AND ...)
  OR (t1.a=t2.a AND t2.a IS NULL AND ...);

ref_or_null 的工作方式是，先用引用的索引列对表进行读取，然后另一个查询会搜索索引列值为 NULL 的记录。

优化只会处理一个索引列为 NULL 的情况。下面的查询中，MySQL 只会在表达式：(t1.a=t2.a AND t2.a IS NULL) 中用到索引列查询，即索引列 a 会发挥索引查找的作用，而索引列 b 不会用到。

SELECT * FROM t1, t2
  WHERE (t1.a=t2.a AND t2.a IS NULL)
OR (t1.b=t2.b AND t2.b IS NULL);

总结

MySQL 可以利用索引或范围对 IS NULL 表达式进行优化。如果MySQL 优化器发现有一个列被声明为了NOT NULL ，但在 WHERE 子句中却还要搜索该列为空的记录，那么这条表达式就会直接被去掉。

一种非常常见的表达式组合是：某列等于某个值或为空 (col_name = expr OR col_name IS NULL)。MySQL 同样会优化这条表达式，同时，EXPLAIN 会出现 ref_or_null 的字样。

ref_or_null 的工作方式是，先用索引列对表进行读取，然后另一个查询搜索索引列为 NULL 的记录。这是一个比较含混的优化描述，MySQL 并没有给出更加相信的优化细节，因此，我们仅仅知道MySQL 会优化某些情况的 IS NULL 条件表达式，一句话：先索引读表，再查询空值。

正确理解：This optimization can handle one IS NULL for any key part.  如果索引中的多个索引列都需要查询为空的记录，那么优化只会选择一个索引列，其主旨是 IS NULL 的优化是存在局限性的。