mysql is null 可以走索引么

搞Oracle时，我们知道对于单列索引，当索引列条件is null时是不能走索引的，即使is null的值非常少，哪怕就1个，也不能走索引，因为Oracle的b+树是不保存空值的记录。
如下Oracle的测试，26w的表is null只有1行也是全表扫描的。

HR@orcl>select count(*) from test1 t;

  COUNT(*)
----------
    263163
HR@orcl>select count(*) from test1 t where t.id is null;

  COUNT(*)
----------
         1

Elapsed: 00:00:00.02

Execution Plan
----------------------------------------------------------
Plan hash value: 3896847026

----------------------------------------------------------------------------
| Id  | Operation          | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |     1 |     5 |  1090   (1)| 00:00:14 |
|   1 |  SORT AGGREGATE    |       |     1 |     5 |            |          |
|*  2 |   TABLE ACCESS FULL| TEST1 |     1 |     5 |  1090   (1)| 00:00:14 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("T"."ID" IS NULL)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       3946  consistent gets
          0  physical reads
          0  redo size
        526  bytes sent via SQL*Net to client
        519  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

HR@orcl>select count(*) from test1 t where t.id=2;

  COUNT(*)
----------
         1

Elapsed: 00:00:00.03

Execution Plan
----------------------------------------------------------
Plan hash value: 4245524744

----------------------------------------------------------------------------------
| Id  | Operation         | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |              |     1 |     5 |     1   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |              |     1 |     5 |            |          |
|*  2 |   INDEX RANGE SCAN| IND_ID_TEST1 |     1 |     5 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("T"."ID"=2)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
        526  bytes sent via SQL*Net to client
        519  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

那么对于Mysql来说，也是一样的么
先来测试看下结果：

-- 创建测试表
CREATE TABLE `t` 
( `id` int(11) NOT NULL, 
`a` int(11) DEFAULT NULL, 
`b` int(11) DEFAULT NULL, 
PRIMARY KEY (`id`), 
KEY `a` (`a`), 
KEY `b` (`b`)) 
ENGINE=InnoDB;

-- 插入数据存储过程（插入1w行）

drop procedure if exists idata;
delimiter ;;
create  procedure idata()
begin
  declare i int;
  set i=1;
  while(i<=10000) do
    insert into t values(i, i, i);
    set i=i+1;
  end while;
end;;
delimiter ;
call idata();
-- 修改10行记录的a值为空
update t set a = null where id<=10;
看下执行计划：
[root@mysql.sock][xhy]>>select count(*) from t where a is null;
+----------+
| count(*) |
+----------+
|       10 |
+----------+
1 row in set (0.00 sec)

[root@mysql.sock][xhy]>>explain select * from t where a is null;
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | t     | NULL       | ref  | a             | a    | 5       | const |   10 |   100.00 | Using index condition |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
可以看到key值为a，说明使用到了 a列的索引。
那么 is not null 能使用到索引么
[root@mysql.sock][xhy]>>explain select * from t where a is not null;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t     | NULL       | ALL  | a             | NULL | NULL    | NULL | 9980 |   100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
看这里是没有，我们把a值更新下，如下：
update t set a=null where id>10;
update t set a=id where id<=10;

[root@mysql.sock][xhy]>>explain select * from t where a is not null;
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | t     | NULL       | range | a             | a    | 5       | NULL |   10 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
再看使用 is not null也使用到索引了。

从以上测试可以得出MySQL里 is null 和 is not null 是都可以走索引的。
那么为啥 is null也可以走索引呢，因为 MySQL的二级索引也是会记录空值的，空值放在B+树的最左侧。
所以说 MySQL 的is null ,is not null 会不会走索引跟这个列是不是 null值无关，走不走还是由值的过滤性决定的，简单说值少就走，多了就不走，那么多少算多多少算少，一般20%可以作为个临界点。