Mysql在字段为允许null时，存在null值，会不会影响索引使用

Mysql在字段为允许null时，存在null值，会不会影响索引使用

三思_1234 

 

本文主要考察两个问题：

1. 当某一列有为null值的数据时，该列的索引是否还能生效
2. is null或者is not null是否能使用到索引

首先贴上几段官方文档：

You can add an index on a column that can have NULL values if you are using the MyISAM, InnoDB, or MEMORY storage engine. Otherwise, you must declare an indexed column NOT NULL, and you cannot insert NULL into the column.

大意是：如果你选择了MyISAM, InnoDB, or MEMORY 这几个引擎，那么你可以为允许null值的字段加上索引，如果不是这几个引擎，那么只能为那些not null的字段加上索引，这些字段也无法保存null值。

MySQL can perform the same optimization on col_name IS NULL that it can use for col_name = constant_value. For example, MySQL can use indexes and ranges to search for NULL with IS NULL.

大意是：mysql可以使用索引来查询NULL字段。

 

下面开始测试

建表：a、b、c字段均接受null值

添加索引：

ALTER TABLE `j_copy` ADD INDEX (a);

为问题1编写查询语句：

EXPLAIN select * from j_copy where a = 'a1';

解释执行过程：

问题1结论：当某一列有为null值的数据时，该列的索引依然生效

 

为问题2编写查询语句：

1.EXPLAIN select * from j_copy where a is null;

2.EXPLAIN select * from j_copy where a is not null;

解释执行过程：

1.

2.

情况1：

情况2：

问题2结论：

1.使用is null确实是走了索引，没有问题。

2.现象比较特殊，在这里is not null条件并没有走索引，但是修改成select a from j_copy的话，就可以走索引了。一开始不太理解这个现象，网上翻了很多文章，找到了一个比较靠谱的说法：

select a from j_copy直接读取索引上的数据后即可返回。而select * from j_copy如果走索引的话，则需要通过索引获取数据位置再去读取整行内容，在数据比较少的情况下，可能会更慢，所以mysql的优化器选择了直接全表扫描。

为了验证上面内容，我又测试了一下，为a、b、c三个字段添加联合索引，那么三个字段的数据都会保存在索引里面，再执行语句：

 

 

以上实验都是在字段接受null值的条件下做的实验。在字段not null的情况下，is null和is not null都不会走索引。