MySQL优化(8):SELECT 之 IS NULL 优化

MySQL 对 col_name IS NULL 执行了优化。如果 WHERE 子句包含声明为 NOT NULL 的列的 col_name IS NULL 条件,则该表达式将被优化掉。

MySQL 可以对 col_name IS NULL 执行相同的优化,它可以用于 col_name = constant_value

例如,MySQL 可以使用索引和范围来搜索 NULLIS NULL

示例:

1
2
3
4
5
6
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 子句包含声明为 NOT NULL 的列的 col_name IS NULL 条件,则该表达式将被优化掉。 这种优化不会在列可能产生 NULL 的情况下发生(例如,如果它来自 LEFT JOIN 右侧的表)。

MySQL 还可以优化组合 col_name = expr OR col_name IS NULL,这是解析子查询中常见的一种形式。使用此优化时,EXPLAIN 显示 ref_or_null

这种优化可以为任何关键部分处理一个 IS NULL。

一些优化的查询示例,假设表 t2 的 a 和 b 列上有一个索引:

1
2
3
4
5
6
7
8
9
10
11
12
13
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 的工作原理是首先读取引用 Key,然后单独搜索具有 NULL Key 值的行。

优化只能处理一个 IS NULL 级别。 在以下查询中,MySQL 仅在表达式 (t1.a=t2.a AND t2.a IS NULL) 上使用 Key 查找,而不能在 b 上使用 Key 部分:

1
2
3
SELECT * FROM t1, t2
WHERE (t1.a=t2.a AND t2.a IS NULL)
OR (t1.b=t2.b AND t2.b IS NULL);
作者

光星

发布于

2021-07-18

更新于

2023-03-06

许可协议

评论