MySQL版本5.7
建表
create table staffs(
id int primary key auto_increment,
name varchar(24) not null default '' comment '姓名',
age int not null default 0 comment '年龄',
pos varchar(20) not null default '' comment '职位',
add_time timestamp not null default current_timestamp comment '入职时间'
)charset utf8 comment '员工记录表';
insert into staffs(name,age,pos) values('z3',22,'manager');
insert into staffs(name,age,pos) values('July',23,'dev');
insert into staffs(name,age,pos) values('2000',23,'dev');
alter table staffs add index idx_staffs_nameAgePos(name,age,pos);
查看staffs表数据
mysql> select * from staffs;
+----+------+-----+---------+---------------------+
| id | name | age | pos | add_time |
+----+------+-----+---------+---------------------+
| 1 | z3 | 22 | manager | 2020-02-15 00:11:33 |
| 2 | July | 23 | dev | 2020-02-15 00:11:33 |
| 3 | 2000 | 23 | dev | 2020-02-15 00:11:33 |
+----+------+-----+---------+---------------------+
现在进行sql分析
explain select * from staffs where name is null;
explain select * from staffs where name is not null;
explain select name from staffs where name is not null;
发现is null
并没有走索引,而且sqlExtra内容是Impossible WHERE。不难理解,mysql为什么会这么说。因为staffs表定义列都不允许为null,所以它认为这是Impossible WHERE。
第二条语句is not null
没有走索引,第三条语句is not null使用到了覆盖索引。
那我们修改name列定义,设置default null。
alter table staffs modify name varchar(24) default null comment '姓名';
更改id=2的数据name=null
mysql> update staffs set name=null where id=2;
再次查看staffs表数据
mysql> select * from staffs;
+----+------+-----+---------+---------------------+
| id | name | age | pos | add_time |
+----+------+-----+---------+---------------------+
| 1 | z3 | 22 | manager | 2020-02-15 00:11:33 |
| 2 | NULL | 23 | dev | 2020-02-15 00:11:33 |
| 3 | 2000 | 23 | dev | 2020-02-15 00:11:33 |
+----+------+-----+---------+---------------------+
再次分析explain select * from staffs where name is null;
发现此时查询走索引了。
再看下explain select * from staffs where name is not null;
发现is not null
也走了索引。
结论:为指定字段设置了非空(not null),在使用is null
或is not null
时是不走索引的。而列定义允许为空,查询中也能使用到索引的。