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

已标记关键词 清除标记
©️2020 CSDN 皮肤主题: 编程工作室 设计师:CSDN官方博客 返回首页