mysql not null 索引吗_MySQL中 IS NULL、IS NOT NULL、!= 能用上索引吗？

看面试题的时候，总能看到MySQL在什么状况下用不上索引，以下：mysql

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

不耽误你们时间，告诉你们结论：

MySQL中决定使不使用某个索引执行查询的依据就是成本够不够小，若是null值不少，仍是会用到索引的。

本身作了个验证：

一个大概3万数据的表，若是只有10多个记录是null值，is null走索引，not null和!=没走索引，若是大部分都是null值，只有部分几条数据有值，is null，not null和!=都走索引。算法

如下是搬过来网上的验证，让你们看看，结构以下：sql

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值是怎么在记录中存储的

在MySQL中，每一条记录都有它固定的格式，咱们以InnoDB存储引擎的Compact行格式为例，来看一下NULL值是怎样存储的。在Compact行格式下，一条记录是由下边这几个部分构成的：

code

新建一个称之为record_format_demo的表：orm

CREATE TABLE record_format_demo (

c1 VARCHAR(10),

c2 VARCHAR(10) NOT NULL,

c3 CHAR(10),

c4 VARCHAR(10)

) CHARSET=ascii ROW_FORMAT=COMPACT;

由于咱们的重点是NULL值是如何存储在记录中的，因此重点唠叨一下行格式的NULL值列表部分。存储NULL值的过程以下：

1.首先统计表中容许存储NULL的列有哪些。blog

咱们前边说过，主键列、被NOT NULL修饰的列都是不能够存储NULL值的，因此在统计的时候不会把这些列算进去。比方说表record_format_demo的3个列c1、c3、c4都是容许存储NULL值的，而c2列是被NOT NULL修饰，不容许存储NULL值。排序

2.若是表中没有容许存储NULL的列，则NULL值列表也不存在了，不然将每一个容许存储NULL的列对应一个二进制位，二进制位按照列的顺序逆序排列，二进制位表示的意义以下：

由于表record_format_demo有3个值容许为NULL的列，因此这3个列和二进制位的对应关系就是这样：

再一次强调，二进制位按照列的顺序逆序排列，因此第一个列c1和最后一个二进制位对应。

二进制位的值为1时，表明该列的值为NULL。

二进制位的值为0时，表明该列的值不为NULL。

3.设计InnoDB的大叔规定NULL值列表必须用整数个字节的位表示，若是使用的二进制位个数不是整数个字节，则在字节的高位补0。

表record_format_demo只有3个值容许为NULL的列，对应3个二进制位，不足一个字节，因此在字节的高位补0，效果就是这样：

以此类推，若是一个表中有9个容许为NULL，那这个记录的NULL值列表部分就须要2个字节来表示了。

假设咱们如今向record_format_demo表中插入一条记录：

INSERT INTO record_format_demo(c1, c2, c3, c4)

VALUES('eeee', 'fff', NULL, NULL);

这条记录的c1、c3、c4这3个列中c3和c4的值都为NULL，因此这3个列对应的二进制位的状况就是：

因此这记录的NULL值列表用十六进制表示就是：0x06。

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

对于InnoDB存储引擎来讲，记录都是存储在页面中的(一个页面默认是16KB大小)，这些页面能够做为B+树的节点而组成一个索引，相似这种样子(只是用下边的图举个B+树的例子而已，跟咱们上边列举的表不要紧)：

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

对于聚簇索引索引来讲，页面中的记录是按照主键值进行排序的；而对于二级索引来讲，页面中的记录是按照给定的索引列的值进行排序的。

对于聚簇索引来讲，B+树每一层节点(页面)都是按照页中记录的主键值大小进行排序的；而对于二级索引来讲，B+树每一层节点(页面)都是按照页中记录的给定的索引列的值进行排序的。

对于聚簇索引来讲，B+树叶子节点对应的页面中存储的是完整的用户记录(就是一条记录中包含咱们定义的全部列值，还包含一些InnoDB本身添加的一些隐藏列)；而对于二级索引来讲，B+树叶子节点对应的页面中存储的只是索引列的值 + 主键值。

按规定，一条记录的主键值不容许存储NULL值，因此下边语句中的WHERE子句结果确定为FALSE：

SELECT * FROM tbl_name WHERE primary_key IS NULL;

像这样的语句优化器本身就能断定出WHERE子句一定为NULL，因此压根儿不会去执行它，不信咱们看(Extra信息提示WHERE子句压根儿不成立)：

对于二级索引来讲，索引列的值可能为NULL。那对于索引列值为NULL的二级索引记录来讲，它们被放在B+树的哪里呢？答案是：放在B+树的最左边。比方说咱们有以下查询语句：

SELECT * FROM s1 WHERE key1 IS NULL;

那它的查询示意图就以下所示：

从图中能够看出，对于s1表的二级索引idx_key1来讲，值为NULL的二级索引记录都被放在了B+树的最左边，这是由于设计InnoDB的大叔有这样的规定：

We define the SQL null to be the smallest possible value of a field.

也就是说他们把SQL中的NULL值认为是列中最小的值。

在经过二级索引idx_key1对应的B+树快速定位到叶子节点中符合条件的最左边的那条记录后，也就是本例中id值为521的那条记录以后，就能够顺着每条记录都有的next_record属性沿着由记录组成的单向链表去获取记录了，直到某条记录的key1列不为NULL。

小贴士： 经过B+树快速定位到叶子节点的记录的过程是靠一个所谓的页目录(Page Directory)作到的，不过这不是本文的重点，你们能够到小册中翻看，都有详细解释。

使不使用索引的依据究竟是什么？

那既然IS NULL、IS NOT 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，而是须要采用以前在背地里产生的一些统计数据去估算匹配的二级索引记录有多少条(很显然根据统计数据去估算记录条数比index dive的方式精确性差了不少)。

反正不论采用index dive仍是依据统计数据估算，最终要获得一个须要扫描的二级索引记录条数，若是这个条数占整个记录条数的比例特别大，那么就趋向于使用全表扫描执行查询，不然趋向于使用这个索引执行查询。

理解了这个也就好理解为何在WHERE子句中出现IS NULL、IS NOT NULL、!=这些条件仍然可使用索引，本质上都是优化器去计算一下对应的二级索引数量占全部记录数量的比值而已。

结论

你们能够看到，MySQL中决定使不使用某个索引执行查询的依据很简单：就是成本够不够小。而不是是否在WHERE子句中用了IS NULL、IS NOT NULL、!=这些条件。