MySQL-5.7-8.2.1.13 IS NULL Optimization

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 col_name = expr OR col_name IS NULL, a form that is common in resolved subqueries. EXPLAIN shows ref_or_null when this optimization is used.

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上使用关键部分: