16.MySQL优化IS NULL

介绍

MySQL可以执行相同的优化col_name IS NULL和col_name = constant_value。例如，MySQL可以使用索引扫描和索引范围扫描查询NULL值， col_name IS NULL。

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;

假如一个列别定义为NOT NULL,而Where条件中这个列col_name IS NULL，则此表达式会被优化掉。假如列仍然可能产生NULL值的时候不会进行此优化，例如假如他是LEFT JOIN中右边的表。

MySQL还可以优化组合col_name = expr OR col_name IS NULL，解析子查询中的一种常见形式。

当这个优化被使用时EXPLAIN显示ref_or_null

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

下面是优化查询的一些示例，假设a和t2的列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键值的行。

优化器只能处理IS NULL级别。在下面这个查询中，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);