Oracle 和 Mysql 的索引在Null字段上处理的异同

ORACLE：

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）：

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/ref...

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.

来源：https://www.modb.pro/db/142829