8.2.1.8 IS NULL Optimization NULL 优化:

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 NULLmysql>  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 对于任何索引部分