在MySQL中is null, is not null对索引的影

本次测试使用的数据库版本为5.7.26

image.png

准备测试的两张表数据如下：

 表a

CREATE TABLE `a_user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) NOT NULL,
`age` int(11) DEFAULT NULL,
`sex` varchar(20) DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;



INSERT INTO `a_user` (`id`, `name`, `age`, `sex`) VALUES ('1', 'tom', '20', '男');
INSERT INTO `a_user` (`id`, `name`, `age`, `sex`) VALUES ('2', 'bob', '40', '男');
INSERT INTO `a_user` (`id`, `name`, `age`, `sex`) VALUES ('3', 'lucy', '30', '女');


alter table a_user add index index_name(name(20));

 表b

CREATE TABLE `b_user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`sex` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;



INSERT INTO `b_user` (`id`, `name`, `age`, `sex`) VALUES ('1', 'tom', '20', '男');
INSERT INTO `b_user` (`id`, `name`, `age`, `sex`) VALUES ('2', 'bob', '40', '男');
INSERT INTO `b_user` (`id`, `name`, `age`, `sex`) VALUES ('3', 'lucy', '30', '女');


alter table b_user add index index_name(name(20));

一、索引字段不为null
使用select *查询
select * from a_user where name is null;

image.png
select * from a_user where name is not null; image.png
结果
select * from a_user where name is not null; 未使用索引；
select * from a_user where name is null; 未使用索引；

查询索引字段
select name from a_user where name is null;

image.png

select name from a_user where name is not null;

image.png

结果
select name from a_user where name is null; 未使用索引；
select name from a_user where name is not null; 使用索引；

多字段查询（索引字段+非索引字段）
select name,sex from a_user where name is null;

image.png

select name,sex from a_user where name is not null;

image.png

结果
select name,sex from a_user where name is null;未使用索引；
select name,sex from a_user where name is not null;未使用索引；

二、索引字段为null 时
使用select *查询
select * from b_user where name is null;

image.png

select * from b_user where name is not null;

image.png

结果：
select * from b_user where name is null;使用索引
select * from b_user where name is not null;未使用索引

查询索引字段
select name from b_user where name is null;

image.png

select name from b_user where name is not null;

image.png

结果：
select name from b_user where name is null;使用索引
select name from b_user where name is not null;使用索引

多字段查询（索引字段+非索引字段）
select name,sex from b_user where name is null;

image.png

select name,sex from b_user where name is not null;

image.png

结果：
select name,sex from b_user where name is null;使用索引
select name,sex from b_user where name is not null;未使用索引

总结以上测试：
1、当索引字段不为null 时,只有使用is not null 返回的结果集中只包含索引字段时，才使用索引；
2、当索引字段为null时候，使用 is null 不影响覆盖索引，但是使用 is not null 只有完全返回索引字段时才会使用索引