|
|
51CTO旗下网站
|
|
移动端
创建专栏

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

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

作者:58沈剑|2019-07-05 07:50

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

数据准备:

  1. create table user ( 
  2. id int, 
  3. name varchar(20), 
  4. index(id) 
  5. )engine=innodb
  6.   
  7. insert into user values(1,'shenjian'); 
  8. insert into user values(2,'zhangsan'); 
  9. insert into user values(3,'lisi'); 

说明:id为索引,非唯一(non unique),允许空(null)。

知识点1(热身):负向查询不能命中索引,会导致全表扫描。

  1. explain select * from user where id!=1; 

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

  • type=ALL,全表扫描;
  • rows=3,全表只有3行;

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

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

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

  1. select * from user where id!=1; 

再次执行不等于查询。

你猜结果集有几条记录(共4条,不等于排除1条)?

答错了!

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

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

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

画外音:恶心不恶心,这个大坑你踩过没有?

知识点3(附加):某些or条件,又可能导致全表扫描,此时应该优化为union。

  1. explain select * from user where id=1

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

  • type=ref,走非唯一索引;
  • rows=1,预估扫描1行;

  1. explain select * from user where id is null; 

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

  • type=ref,走非唯一索引;
  • rows=1,预估扫描1行;

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

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

  • type=ALL,全表扫描;
  • rows=4,全表只有4行;

  1. explain select * from user where id=1  
  2. union 
  3. select * from user where id is null; 

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

  • type=ref,走非唯一索引;
  • rows=1,预估扫描1行;

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

总结

  • 负向比较(例如:!=)会引发全表扫描;
  • 如果允许空值,不等于(!=)的查询,不会将空值行(row)包含进来,此时的结果集往往是不符合预期的,此时往往要加上一个or条件,把空值(is null)结果包含进来;
  • or可能会导致全表扫描,此时可以优化为union查询;
  • 建表时加上默认(default)值,这样能避免空值的坑;
  • explain工具是一个好东西;

希望大家有收获!

画外音:本文测试于MySQL5.6。

【本文为51CTO专栏作者“58沈剑”原创稿件,转载请联系原作者】

戳这里,看该作者更多好文

【编辑推荐】

  1. 干货 | MySQL数据库安全之审计
  2. 墨菲定律视角下的数据库入侵防御
  3. PB级数据实时查询,滴滴Elasticsearch多集群架构实践
  4. 一次诡异的数据库“死锁”,问题究竟在哪里?
  5. 心里没点B树,怎能吃透数据库索引底层原理?
【责任编辑:赵宁宁 TEL:(010)68476606】

点赞 0
分享:
大家都在看
猜你喜欢