MySQL中IS NULL,IS NOT NULL,!=,in大量id是否走索引的情况

不知道从什么时候开始，网上流传着这么一个说法：

MySQL的WHERE子句中包含 IS NULL、IS NOT NULL、!= 这些条件时便不能使用索引查询，只能使用全表扫描。

CREATE TABLE s1 (
    id INT NOT NULL AUTO_INCREMENT,
    key1 VARCHAR(100),
    key2 VARCHAR(100),
    key3 VARCHAR(100),
    key_part1 VARCHAR(100),
    key_part2 VARCHAR(100),
    key_part3 VARCHAR(100),
    common_field VARCHAR(100),
    PRIMARY KEY (id),
    KEY idx_key1 (key1),
    KEY idx_key2 (key2),
    KEY idx_key3 (key3),
    KEY idx_key_part(key_part1, key_part2, key_part3)
) Engine=InnoDB CHARSET=utf8;

这个表里有10000条记录：

mysql> SELECT COUNT(*) FROM s1;
+----------+
| COUNT(*) |
+----------+
|    10000 |
+----------+
1 row in set (0.00 sec)

上边几个查询语句的WHERE子句中用了IS NULL、IS NOT NULL、!=这些条件，但是从它们的执行计划中可以看出来，这些语句都采用了相应的二级索引执行查询，而不是使用所谓的全表扫描

键值为NULL的记录是怎么在B+树中存放的

新建一个称之为record_format_demo的表

CREATE TABLE record_format_demo (
     c1 VARCHAR(10),
     c2 VARCHAR(10) NOT NULL,
     c3 CHAR(10),
     c4 VARCHAR(10)
 ) CHARSET=ascii ROW_FORMAT=COMPACT;

聚簇索引和二级索引都对应着像上图一样的B+树（也就是说有多少个索引就有多少棵对应的B+树），不过：

• 对于聚簇索引索引来说，页面中的记录是按照主键值进行排序的；而对于二级索引来说，页面中的记录是按照给定的索引列的值进行排序的。
• 对于聚簇索引来说，B+树每一层节点（页面）都是按照页中记录的主键值大小进行排序的；而对于二级索引来说，B+树每一层节点（页面）都是按照页中记录的给定的索引列的值进行排序的。
• 对于聚簇索引来说，B+树叶子节点对应的页面中存储的是完整的用户记录（就是一条记录中包含我们定义的所有列值，还包含一些InnoDB自己添加的一些隐藏列）；而对于二级索引来说，B+树叶子节点对应的页面中存储的只是索引列的值 + 主键值。

对于二级索引来说，索引列的值可能为NULL。那对于索引列值为NULL的二级索引记录来说，它们被放在B+树的哪里呢？答案是：放在B+树的最左边(因为设计Innodb的人认为null是最小的值,就把它放在最左边了)。比方说我们有如下查询语句：

SELECT * FROM s1 WHERE key1 IS NULL;

那么到这里使不使用索引的依据已经有点苗头了.只要是要扫描的二级索引记录条数越多，那么需要执行的回表操作的次数也就越多，达到了某个比例时，使用二级索引执行查询的成本也就超过了全表扫描的成本（举一个极端的例子，比方说要扫描的全部的二级索引记录，那就要对每条记录执行一遍回表操作，自然不如直接扫描聚簇索引来的快）。

所以MySQL优化器在真正执行查询之前，对于每个可能使用到的索引来说，都会预先计算一下需要扫描的二级索引记录的数量，比方说对于下边这个查询：

SELECT * FROM s1 WHERE key1 IS NULL;

优化器会分析出此查询只需要查找key1值为NULL的记录，然后访问一下二级索引idx_key1，看一下值为NULL的记录有多少（如果符合条件的二级索引记录数量较少，那么统计结果是精确的，如果太多的话，会采用一定的手段计算一个模糊的值），这种在查询真正执行前优化器就率先访问索引来计算需要扫描的索引记录数量的方式称之为index dive。

当然，对于某些查询，比方说WHERE子句中有IN条件，并且IN条件中包含许多参数的话，比方说这样：

SELECT * FROM s1 WHERE key1 IN ('a', 'b', 'c', ... , 'zzzzzzz');

这样的话需要统计的key1值所在的区间就太多了，这样就不能采用index dive的方式去真正的访问二级索引idx_key1,而是需要采用之前在背地里产生的一些统计数据去估算匹配的二级索引记录有多少条.可能会统计出很多条记录,导致mysql任务扫描的行数太多,就不走索引了,直接全表扫描.

我这边理解in的话并不像 > 或者=可以范围或者精确查找索引,它是一个个的 =去找的,当in的数量太多的话,即使是in id,mysql也会觉得一个个找还不如直接全表扫描后再把值筛选出来快