|
数据库字段允许空值，会遇到一些问题，此处包含的一些知识点，和大家聊一聊。 数据准备： 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工具是一个好东西； 希望大家有收获！ 画外音：本文测试于MySQL5.6。 |
|
|
