mysql 索引优化 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 的优化是存在局限性的。

圣斗士Morty

发布了191 篇原创文章 · 获赞 280 · 访问量 52万+

他的留言板

关注

标签：WHERE,t2,t1,索引,MySQL,NULL,优化

来源： https://blog.csdn.net/u014745069/article/details/104074613