MySQL can perform the same optimization on col_name IS NULL that it can use for col_name = constant_value. MySQL可以对col_name = constant_value使用的col_name为NULL执行相同的优化。For example, MySQL can use indexes and ranges to search for NULL with IS NULL.例如，MySQL可以使用索引和范围使用IS NULL来搜索NULL。

Examples:示例：

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;

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的情况下（例如，如果它来自LEFT JOIN右侧的表），则不会发生这种优化。

MySQL can also optimize the combination col_name = expr OR col_name IS NULL, a form that is common in resolved subqueries. MySQL还可以优化col_name = expr OR col_name IS NULL的组合，这在已解析的子查询中很常见。EXPLAIN shows ref_or_null when this optimization is used.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列上有索引，则一些优化查询示例如下：

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 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. 优化只能处理一个IS NULL的级别。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:在下面的查询中，MySQL只在表达式上使用键查找(t1.a=t2.a AND t2.a IS NULL)，不能在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);