MySQL can perform the same optimization on col_name
IS NULL that it can use for col_name
=
constant_value
. For example, MySQL can use indexes and ranges to search for NULL
with IS NULL.
MySQL可以对col_name IS NULL执行与col_name = constant_value相同的优化。例如,MySQL可以使用索引和范围来搜索IS NULL。
If a WHERE
clause includes a col_name
IS NULL condition for a column that is declared as NOT NULL
, that expression is optimized away.
如果WHERE子句为声明为NOT NULL的列包含col_name IS NULL条件,则该表达式将被优化掉。
This optimization does not occur in cases when the column might produce NULL
anyway (for example, if it comes from a table on the right side of a LEFT JOIN
).
当列无论如何都可能产生NULL(例如,如果它来自左JOIN右侧的表)时,不会发生这种优化。
MySQL can also optimize the combination
, a form that is common in resolved subqueries. EXPLAIN shows ref_or_null when this optimization is used.col_name
= expr
OR col_name
IS NULL
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。
Some examples of queries that are optimized, assuming that there is an index on columns a
and b
of table t2
:
下面是一些优化后的查询示例,假设表t2的a列和b列上有一个索引:
ref_or_null works by first doing a read on the reference key, and then a separate search for rows with a NULL
key value.
ref_or_null首先读取引用键,然后单独搜索具有NULL键值的行。
The optimization can handle only one IS NULL level. In the following query, MySQL uses key lookups only on the expression (t1.a=t2.a AND t2.a IS NULL)
and is not able to use the key part on b
:
优化只能处理一个IS NULL级别。在下面的查询中,MySQL只对表达式(t1.a=t2)使用键查找。和t2。a IS NULL)并且不能在b上使用关键部分: