数据库允许空值(null)，往往是悲剧的开始（1分钟系列）

数据库字段允许空值，会遇到一些问题，此处包含的一些知识点，和大家聊一聊。

数据准备 ：

create table user (

id int,

name varchar(20),

index(id)

)engine=innodb;

insert into user values(1,'shenjian');

insert into user values(2,'zhangsan');

insert into user values(3,'lisi');

说明 ：

id为索引，非唯一 (non unique) ，允许空 (null) 。

知识点1（热身）： 负向查询不能命中索引，会导致全表扫描。

explain select * from user where id!=1;

索引字段id上的不等于查询，如上图所示：

（1）type=ALL，全表扫描；

（2）rows=3，全表只有3行；

知识点2（划重点）： 允许空值，不等于 (!=) 查询，可能导致不符合预期的结果。

insert into user(name) values('wangwu');

先构造一条id为NULL的数据，可以看到 共有4条记录 。

select * from user where id!=1 ;

再次执行不等于查询。

你猜结果集有几条记录（共4条，不等于排除1条）？

答 错了！

结果集只有2条记录 ，空值记录记录并未出现在结果集里。

select * from user where id!=1 or id is null;

如果想到得到符合预期的结果集，必须加上一个or条件。

画外音： 恶心不恶心，这个大坑你踩过没有？

知识点3（附加）： 某些or条件，又可能导致全表扫描，此时应该优化为union。

explain select * from user where id=1;

索引字段 id上的等值查询 ， 能 命中索引，如上图所示：

（1）type=ref，走非唯一索引；

（2）rows=1，预估扫描1行；

explain select * from user where id is null;

索引字段 id上的null查询 ，也 能 命中索引，如上图所示：

（1）type=ref，走非唯一索引；

（2）rows=1，预估扫描1行；

explain select * from user where id=1 or id is null;

如果放到一个 SQL 语句里 用or查询 ，则会 全表扫描 ，如上图所示：

（1）type=ALL，全表扫描；

（2）rows=4，全表只有4行；

explain select * from user where id=1 

union

select * from user where id is null;

此时应该 优化为union查询 ，又能够 命中索引 了，如上图所示：

（1）type=ref，走非唯一索引；

（2）rows=1，预估扫描1行；

画外音： 第三行临时表的ALL，是两次结果集的合并。

总结

（1） 负向比较 （例如： !=）会引发 全表扫描 ；

（2）如果允许空值， 不等于(!=)的查询，不会将空值行 (row) 包含进来 ，此时的结果集往往是不符合预期的，此时往往要加上一个or条件，把空值 (is null) 结果包含进来；

（3）or可能会导致全表扫描，此时可以优化为union查询；

（4）建表时加上 默认 (default) 值 ，这样能避免空值的坑；

（5） explain 工具是一个好东西；

希望大家有收获！

画外音：本文测试于 MySQL 5.6。

架构师之路-分享技术思路

相关推荐：

《 缓冲池(buffer pool)，这次彻底懂了！！！ 》

《 写缓冲(change buffer)，这次彻底懂了！！！ 》

《 两类非常隐蔽的全表扫描 | 1分钟系列 》

《 MyISAM与InnoDB的索引差异 | 1分钟系列 》