编者按：
本文作者系Scott（中文名陈晓辉），ORACLE数据库专家，就职于甲骨文中国。个人主页：segmentfault.com/u/db_perf ，经其本人授权发布。
【免责声明】本号文章仅代表个人观点，与任何公司无关。
编辑|SQL和数据库技术(ID:SQLplusDB)
ORACLE：
代码语言：javascript复制SQL> create table tab2(c1 number, c2 number, c3 varchar2(10));
表が作成されました。
SQL> declare
a number;
begin
a := 1;
for i in 1 .. 500 loop
for j in 1 .. 1000 loop
insert into tab2 values(a,j,'a');
commit;
a := a 1;
end loop;
end loop;
end;
/
PL/SQLプロシージャが正常に完了しました。
SQL> create index ind2_2 on tab2(c2);
索引が作成されました。
SQL> insert into tab2 values(9999,null,'test');
1行が作成されました。
SQL> commit;
コミットが完了しました。
SQL> exec dbms_stats.gather_table_stats(ownname=>'TEST',tabname=>'TAB2',cascade=>TRUE);
PL/SQLプロシージャが正常に完了しました。
SQL> set lin 150 pages 9999
SQL> set autot traceonly exp
SQL> select count(*) from tab2 where c2 is null;
実行計画
----------------------------------------------------------
Plan hash value: 2781695375
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 310 (1)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 4 | | |
|* 2 | TABLE ACCESS FULL| TAB2 | 1 | 4 | 310 (1)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("C2" IS NULL)
SQL> select count(*) from tab2 where c2=10;
実行計画
----------------------------------------------------------
Plan hash value: 3563712581
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 4 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 4 | | |
|* 2 | INDEX RANGE SCAN| IND2_2 | 500 | 2000 | 4 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C2"=10)Mysql（Innodb）：
代码语言：javascript复制mysql> create table tab2(c1 int, c2 int, c3 varchar(10));
Query OK, 0 rows affected (0.01 sec)
mysql> delimiter //
mysql> create procedure my_procedure()
-> begin
-> DECLARE n int DEFAULT 1;
-> WHILE n < 1001 DO
-> insert into tab2(c1,c2,c3) value (n,n,'desc');
-> set n = n 1;
-> END WHILE;
-> end
-> //
Query OK, 0 rows affected (0.01 sec)
mysql> delimiter ;
mysql> call my_procedure;
Query OK, 1 row affected (0.84 sec)
mysql> create index ind2_2 on tab2(c2);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> insert into tab2 values(9999,null,'test');
Query OK, 1 row affected (0.00 sec)
mysql> explain select count(*) from tab2 where c2=10;
---- ------------- ------- ------------ ------ --------------- -------- --------- ------- ------ ---------- -------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
---- ------------- ------- ------------ ------ --------------- -------- --------- ------- ------ ---------- -------------
| 1 | SIMPLE | tab2 | NULL | ref | ind2_2 | ind2_2 | 5 | const | 1 | 100.00 | Using index |
---- ------------- ------- ------------ ------ --------------- -------- --------- ------- ------ ---------- -------------
1 row in set, 1 warning (0.00 sec)
mysql> explain select count(*) from tab2 where c2 is null;
---- ------------- ------- ------------ ------ --------------- -------- --------- ------- ------ ---------- --------------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
---- ------------- ------- ------------ ------ --------------- -------- --------- ------- ------ ---------- --------------------------
| 1 | SIMPLE | tab2 | NULL | ref | ind2_2 | ind2_2 | 5 | const | 1 | 100.00 | Using where; Using index |
---- ------------- ------- ------------ ------ --------------- -------- --------- ------- ------ ---------- --------------------------
1 row in set, 1 warning (0.00 sec)结论：
Oracle的B-tree索引不存储Null，所以“c2 is null”条件的检索不能从索引中受益。 Mysql的B tree索引也不直接不存储Null，但是“c2 is null”条件的检索能从索引中受益。
https://dev.mysql.com/doc/refman/8.0/en/is-null-optimization.html
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.
