mysql not null 索引吗_在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 只有完全返回索引字段时才会使用索引