世界快资讯:数据库允许空值(null),往往是悲剧的开始(1分钟系列)
很多小知识点,我以为自己懂了,实际没搞透。
数据库字段允许空值(null)的问题,你遇到过吗?
(资料图)
实验过程:
create table user (
id int,
name varchar(20),
index(id)
)engine=innodb;
【说明:id为索引,非唯一(non unique),允许空(null)】
insert into user values(1,"shenjian");
insert into user values(2,"zhangsan");
insert into user values(3,"lisi");
【问题一:以下select返回什么?】
select * from user where id!=1;
【插入一行,id会出现空值(null)】
insert into user(name) values("wangwu");
【问题二:再次select,会返回什么?】
select * from user where id!=1;
一起来看一下,这个小实验,涉及哪些知识点呢?
知识点1(热身):负向查询不能命中索引,会导致全表扫描。
explain select * from user where id!=1;
索引字段id上的不等于查询,如上图所示:
(1)type=ALL,全表扫描;
(2)rows=3,全表只有3行;
画外音:第一次select的结果。
知识点2(划重点):允许空值,不等于(!=)查询,可能导致不符合预期的结果。
insert into user(name) values("wangwu");
先构造一条id为NULL的数据,可以看到共有4条记录。
select * from user where id!=1;
再次执行不等于查询。
你猜结果集有几条记录(共4条,不等于排除1条)?
答错了!
结果集只有2条记录,空值记录并未出现在结果集里。
画外音:第二次select的结果,意不意外?
此时,如果想到得到符合预期的结果集,必须加上一个or条件。
select * from user where id!=1 or id is null;
画外音:恶心不恶心,这个大坑你踩过没有?
知识点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。
架构师之路-分享技术思路
相关推荐:《必须知道的RPC内核细节(收藏)》《谁家的加密密钥,写死在代码里?》《每秒10W次分词搜索,如何满足(收藏)》相关阅读
-
世界热推荐:今晚7:00直播丨下一个突破...
今晚19:00,Cocos视频号直播马上点击【预约】啦↓↓↓在运营了三年... -
NFT周刊|Magic Eden宣布支持Polygon网...
Block-986在NFT这样的市场,每周都会有相当多项目起起伏伏。在过去... -
环球今亮点!头条观察 | DeFi的兴衰与...
在比特币得到机构关注之后,许多财务专家预测世界将因为加密货币的... -
重新审视合作,体育Crypto的可靠关系才能双赢
Block-987即使在体育Crypto领域,人们的目光仍然集中在FTX上。随着... -
简讯:前端单元测试,更进一步
前端测试@2022如果从2014年Jest的第一个版本发布开始计算,前端开发... -
焦点热讯:刘强东这波操作秀
近日,刘强东发布京东全员信,信中提到:自2023年1月1日起,逐步为...