8.2.1.8 IS NULL Optimization NULL 优化:
Oracle 对待null值:
SQL> create table t100(id int,name char(10));
表已创建。
begin
for i in 1 .. 1000
loop
insert into t100 values(i,'a'||i);
end loop;
commit;
end;
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SYS',
tabname => 'T100',
estimate_percent => 100,
method_opt => 'for all columns size repeat',
no_invalidate => FALSE,
degree => 8,
cascade => TRUE);
END;
/
SQL> explain plan for select * from t100 where id=NULL;
已解释。
SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------
Plan hash value: 3750333395
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 15 | 0 (0)| |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL| T100 | 1000 | 15000 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(NULL IS NOT NULL)
已选择14行。
Oracle 索引不存储NULL值
MySQL 可以执行相同的优化在col_name IS NULL ，它可以使用col_name=常值。
MySQL 可以使用indexes 和ranges 来搜索NULL 使用IS NULL
mysql> select * from t100 where id is NULL;
+----+------+-------+------+
| sn | id | quota | free |
+----+------+-------+------+
| 11 | NULL | xx | yy |
+----+------+-------+------+
1 row in set (0.00 sec)
mysql> explain select * from t100 where id is NULL;
+----+-------------+-------+------+---------------+-----------+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+-----------+---------+-------+------+-----------------------+
| 1 | SIMPLE | t100 | ref | t1oo_idx1 | t1oo_idx1 | 5 | const | 1 | Using index condition |
+----+-------------+-------+------+---------------+-----------+---------+-------+------+-----------------------+
1 row in set (0.00 sec)
Oracle 里索引不存储NULL值,Mysql里索引包含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;
如果一个WHERE 子句包含一个col_name IS NULL 条件 对于一个列定义为NOT NULL,
那个表达式是优化掉的,这个优化不会发生 当列可能产生NULL,比如,如果它来自LEFT JOIN 右边的表
MySQL 也可以优化 组合 col_name = expr OR col_name IS NULL,
是解决子查询的常见形式, EXPLAIN 显示ref_or_null 当优化器被使用
mysql> explain select * from t100 where id=7 or id is NULL;
+----+-------------+-------+-------------+---------------+-----------+---------+-------+------
+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra
|
+----+-------------+-------+-------------+---------------+-----------+---------+-------+------
+-----------------------+
| 1 | SIMPLE | t100 | ref_or_null | t1oo_idx1 | t1oo_idx1 | 5 | const | 2 | Using index condition
|
+----+-------------+-------+-------------+---------------+-----------+---------+-------+------
+-----------------------+
1 row in set (0.00 sec)
优化器可以处理 IS NULL 对于任何索引部分
