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

数据准备:

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工具是一个好东西;

  

  

出处:https://mp.weixin.qq.com/s?__biz=MjM5ODYxMDA5OQ==&mid=2651962495&idx=1&sn=74e9e0dc9d03a872fd5bce5769f6c22a&chksm=bd2d09a38a5a80b50da3b67c03da8417426cbb201427557959fa91e9094a848a14e0db214370&scene=21#wechat_redirect  

最新文章

  1. Android中使用dimen定义尺寸(转)
  2. [转载]CRect::DeflateRect
  3. hover事件优化(延时操作)
  4. paip.网页右键复制菜单限制解除解决方案
  5. hp_jetdirect 9100漏洞检测
  6. 自动化测试LoadRunner
  7. QPainter类学习
  8. 《OpenCL异构计算》新版中译本派送中!
  9. 电脑知识--Windows一片
  10. ORM查询语言OQL
  11. JBOSS安装配置详细教程
  12. 如何面试 PHP 工程师?
  13. First Python script
  14. IDEA 编译时 报 “常量字符串过长” 解决办法
  15. wcf_first
  16. [HNOI2015]菜肴制作(拓扑排序)
  17. Spring 学习——Spring注解——Autowiring(自动装配)
  18. 最好用的jquery列表拖动排列(由项目提取)
  19. A - 摆仙果
  20. 教你如何打开android4.3和4.4中隐藏的AppOps

热门文章

  1. 015:URLs分层模块化
  2. js 页面 保持状态 的方法
  3. XML to HTML
  4. UVa 11212 Editing a Book (IDA* && 状态空间搜索)
  5. JS数据容量单位转换(kb,mb,gb,tb)
  6. Codeforces Round #345 (Div 2)
  7. windows10 下 gcc/g++ 的安装
  8. Selenium 三种等待方式详解
  9. 【每日一包0007】array-range
  10. 把一个树莓派SD卡系统和文件迁移到空SD卡中