mysql中索引的null值问题_mysql index null

在mysql中，对含有null值的索引列，以及is null、is not null的查询条件是否会走索引这点，网上有很多说法，为此我自己单独做了个实验：

首先创建两张表，一张表所有字段默认为null，另一张表所有字段默认为not null。

CREATE TABLE a_achievement  ( 
	s_id   	int(11) NULL,
	Math   	decimal(4,1) NULL,
	English	decimal(4,1) NULL,
	Chinese	float NULL 
	);


CREATE TABLE a_achievement_t  ( 
	s_id   	int(11) not NULL,
	Math   	decimal(4,1)not NULL,
	English	decimal(4,1)not NULL,
	Chinese	float not NULL 
	);
1
2
3
4
5
6
7
8
9
10
11
12
13
14

然后向表中插入数据：

insert into a_achievement(s_id,Math,English,Chinese)
values(1,100,95,88),(2,98,92,100),(3,99,100,80),(4,100,95,99),(5,90,96,100);

insert into a_achievement_t(s_id,Math,English,Chinese)
values(1,100,95,88),(2,98,92,100),(3,99,100,80),(4,100,95,99),(5,90,96,100);
1
2
3
4
5

接下来创建索引：

create index s_id_index on a_achievement(s_id);
create index s_id_index on a_achievement_t(s_id);
1
2

然后执行查询：

explain 
select * from a_achievement where s_id is not null 
1
2

这里可以看到表中字段默认为null时使用is not null是不可以走索引的，接下来看下另外一张表：

explain 
select * from a_achievement_t where s_id is not null 
1
2

另外一张表是没有走索引的，也就是说默认为not null的字段在使用is not null的时候是不走索引的，接下来看下 is null:

explain 
select * from a_achievement where s_id is null 
1
2

上图中可以看到，默认为null的字段在使用is null的时候是走索引的，看下另外一张表：

explain 
select * from a_achievement_t where s_id is null 
1
2

默认为not null的字段在使用is null的条件的时候还是没有走索引。

接下来还有另外一种情况，查询时将select * 换成select s_id

explain 
select s_id  from a_achievement where s_id is null 
1
2

explain 
select s_id  from a_achievement_t where s_id is null 
1
2

explain 
select s_id  from a_achievement where s_id is not null 
1
2

explain 
select s_id  from a_achievement_t where s_id is not null 
1
2

这次经过以上四个实验可以看出，当索引列作为查询列时，只有 select s_id from a_achievement_t where s_id is null ;并未走索引，其他三种情况是因为可以直接从索引中通过where条件获取到所要查询的列，所以才走了索引，而未走索引的这种情况，想要知道是什么情况就要了解一下sql的执行过程，是要先执行where，后执行select ，因为s_id本就定义为not null的情况，所以在执行where的时候，就获取不到数据，就不会走索引了。

通过以上实验可以得出结论：
1、当索引字段不可以为null 时,只有使用is not null 返回的结果集中只包含索引字段时，才使用索引
2、当索引字段可以为空时，使用 is null 不影响覆盖索引，但是使用 is not null 只有完全返回索引字段时才会使用索引