IS NULL优化
MySQL能够执行在相同的优化,它可以使用。例如,MySQL可以使用索引和范围来搜索 with 。col_name
IS NULL
col_name
=
constant_value
NULL
IS NULL
例子:
SELECT *FROM tbl_nameWHERE key_col IS NULL;SELECT *FROM tbl_nameWHERE key_col <=> NULL;SELECT *FROM tbl_nameWHERE key_col=const1 OR key_col=const2 OR key_col IS NULL;
如果WHERE
子句包含声明为的列的条件,则该表达式将被优化。在可能仍然会产生该列的情况下(例如,如果它来自a右侧的表)则不会进行此优化。col_name
IS NULL
NOT NULL
NULL
LEFT JOIN
MySQL还可以优化组合,这种形式在已解决的子查询中很常见。显示何时使用此优化。col_name= expr OR col_name IS NULL
EXPLAIN
ref_or_null
此优化可以处理IS NULL
任何关键部分。
假设在列和b
表上都有索引,则对查询进行一些优化的示例t2
:
SELECT *FROM t1WHERE t1. =expr OR t1. IS NULL;SELECT *FROM t1, t2WHERE t1. =t2. OR t2. IS NULL;SELECT *FROM t1, t2WHERE (t1. =t2. OR t2. IS NULL) AND t2.b=t1.b;SELECT *FROM t1, t2WHERE t1. =t2. AND (t2.b=t1.b OR t2.b IS NULL);SELECT *FROM t1, t2WHERE (t1. =t2. AND t2. IS NULL AND ...) OR (t1. =t2. AND t2. IS NULL AND ...);
ref_or_null
通过首先读取参考键,然后单独搜索具有NULL
键值的行来工作。
优化只能处理一个IS NULL
级别。在以下查询中,MySQL仅在表达式上使用键查找(t1.a=t2.a AND t2.a IS NULL)
,而不能在上使用键部分b
:
SELECT *FROM t1, t2WHERE (t1. =t2. AND t2. IS NULL) OR (t1.b=t2.b AND t2.b IS NULL);