NULL在oracle和mysql索引上的区别

一、问题

oracle的btree索引不存储NULL值，所以用is null或is not null都不会用到索引范围扫描，但是在mysql中也是这样吗？

二、实验

先看看NULL在oracle（11g）中的情况
准备测试数据

SQL> create table t1 as select * from dba_objects;
SQL> update t1 set object_id = null where object_id > 17840;
SQL> update t1 set data_object_id = null where data_object_id > 60;
SQL> commit;
SQL> create index idx1_id on t1(object_id);
SQL> create index idx2_data on t1(data_object_id);

搜集统计信息

SQL> begin
dbms_stats.gather_table_stats(ownname => 'SCOTT',
tabname => 'T1',
estimate_percent => 100,
cascade => true,  
method_opt => 'for all indexed columns size auto', 
no_invalidate => false,
degree => 4);
end;
/

查看数据分布

SQL> select count(*) "总行数", 
  2         count(distinct object_id) "object_id非空不同值", 
  3         count(decode(object_id,null,1,null)) "object_id空值总数",
  4         count(distinct data_object_id) "data_object_id非空不同值",
  5         count(decode(data_object_id,null,1,null)) "data_object_id空值总数"
  6    from t1;

    总行数 object_id非空不同值 object_id空值总数 data_object_id非空不同值 data_object_id空值总数
---------- ------------------- ----------------- ------------------------ ----------------------
     13582               13578                 4                       47                  13510

执行sql，并查看执行计划
第1条sql：is null返回行数少

SQL> select * from t1 where object_id is null;  

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |    50 (100)|          |
|*  1 |  TABLE ACCESS FULL| T1   |     4 |   352 |    50   (0)| 00:00:01 |
--------------------------------------------------------------------------

第2条sql：is not null返回行数多

SQL> select * from t1 where object_id is not null;  

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |    50 (100)|          |
|*  1 |  TABLE ACCESS FULL| T1   | 13578 |  1166K|    50   (0)| 00:00:01 |
--------------------------------------------------------------------------

第3条sql：is null返回行数多

SQL> select * from t1 where data_object_id is null;  

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |    50 (100)|          |
|*  1 |  TABLE ACCESS FULL| T1   | 13510 |  1161K|    50   (0)| 00:00:01 |
--------------------------------------------------------------------------

第4条sql：is not null返回行数少

SQL> select * from t1 where data_object_id is not null;  

-----------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |       |       |     7 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1        |    72 |  6336 |     7   (0)| 00:00:01 |
|*  2 |   INDEX FULL SCAN           | IDX2_DATA |    72 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

可以看到第1条和第3条sql不会用到索引，这是由于oracle的btree索引并不存储NULL，所以用is null作为条件在索引中找不到任何结果，只能全表扫。
第2条sql也没有用到索引，因为返回的行数多。第4条sql用到了索引，但用的是索引全扫描，原理其实还是由于索引不存储NULL，is not null正好跟索引特性相同。

接下来我们看看在mysql（8.0）中又会是什么情形，通过工具把上面的表导入到mysql中
更新t1表的统计信息

analyze table t1;

查看执行计划
第5条sql：is null返回行数少

(scott@localhost)[hello]> explain select * from t1 where object_id is null; 
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key     | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | t1    | NULL       | ref  | IDX1_ID       | IDX1_ID | 5       | const |    4 |   100.00 | Using index condition |
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-----------------------+

第6条sql：is not null返回行数多

(scott@localhost)[hello]> explain select * from t1 where object_id is not null; 
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | IDX1_ID       | NULL | NULL    | NULL | 13541 |    50.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+

第7条sql：is null返回行数多

(scott@localhost)[hello]> explain select * from t1 where data_object_id is null;
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key       | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | t1    | NULL       | ref  | IDX2_DATA     | IDX2_DATA | 5       | const | 6770 |   100.00 | Using index condition |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-----------------------+

第8条sql：is not null返回行数少

(scott@localhost)[hello]> explain select * from t1 where data_object_id is not null;
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key       | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | t1    | NULL       | range | IDX2_DATA     | IDX2_DATA | 5       | NULL |   72 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+

可以看到在mysql中，is not null会根据返回的行数来决定用不用索引，返回行数多不用索引，返回行数少用索引，这一点跟oracle相同。但is null都会用到索引，不管你返回的行数是多少，这点的确是出乎我的意料。既然is null用到索引，那么难道是mysql的btree索引中包含NULL值？

查看索引的信息

(scott@localhost)[hello]> select * from mysql.innodb_index_stats where database_name='hello' and table_name='t1' and index_name in ('IDX1_ID', 'IDX2_DATA');

从索引的统计信息中可以看到，mysql认为t1表的OBJECT_ID，DATA_OBJECT_ID的不同值分别是13579，48。而前面我们知道object_id非空不同值和data_object_id非空不同值分别为13578和47。两者都相差1，那也就是说索引的确是含NULL值。

三、总结

1. mysql中btree索引含NULL，这点跟oracle不一样。
2. mysql中用is null都会用到索引，不管返回的行数多少，我认为这是一个bug。
   水平有限，如果有误，恳请大家指正！