一、问题
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值。
三、总结
- mysql中btree索引含NULL，这点跟oracle不一样。
- mysql中用is null都会用到索引，不管返回的行数多少，我认为这是一个bug。
水平有限，如果有误，恳请大家指正！