从开发者角度谈Mysql(3):EXISTS、IN与JOIN的用法区别

2018-02-27 11:44:43来源:https://www.jianshu.com/p/708f2cbf4434作者:匠心零度人点击

分享



匠心零度 转载请注明原创出处,谢谢!



说在前面

上篇文章从开发者角度谈Mysql(2):建议列设置为NOT NULL建议大家列设置为 NOT NULL,唯一约束键一定要遵守此规范,业务场景在建表的时候也需要进行完备性考虑的,很尴尬发文章的前一天,公司就因为业务字段默认为 NULL 导致一个事故发生(由于涉及一些公司具体业务,不方便在此分享,望理解)。


知识点

EXISTS、IN 与 JOIN,都可以用来实现形如“查询A表中在(或不在)B 表中的记录”的查询逻辑。


在查询的两个表大小相当的情况下,3种查询方式的执行时间通常是:


EXISTS <= IN <= JOIN
NOT EXISTS <= NOT IN <= LEFT JOIN

只有当表中字段允许 NULL 时,NOT IN 的方式最慢:


NOT EXISTS <= LEFT JOIN <= NOT IN

但是如果两个表中一个较小,一个较大,则子查询表大的用 EXISTS,子查询表小的用 IN,因为 IN 是把外表和内表作 hash 连接,而 EXISTS 是对外表作 loop 循环,每次 loop 循环再对内表进行查询。而无论那个表大,用 NOT EXISTS 都比 NOT IN 要快。这是因为如果查询语句使用了 NOT IN 那么内外表都进行全表扫描,没有用到索引;而 NOT EXISTS 的子查询依然能用到表上的索引。


实践

随着业务的发展,数据量越来越大,我们会发现 MySQL 的 EXISTS 与 INNER JION 和 NOT EXISTS 与 LEFT JOIN 性能差别惊人。


例如我们一般在做数据插入时,想插入不重复的数据,或者盘点数据在一个表,另一个表否有存在相同的数据会用 NOT EXISTS 和 EXISTS :


insert into t1(a1) 
select b1 from t2 where not exists(select 1 from t1 where t1.id = t2.r_id);

如果t1的数据量很大时,性能会非常慢。经过实践,用以下方法能提高很多。


insert into t1(a1)
select b1 from t2
left join (select distinct t1.id from t1 ) t1 on t1.id = t2.r_id
where t1.id is null;


select * from t1 where exists(select 1 from t2 where t1.id = t2.r_id);

替换为:


select t1.* from t1
inner join (select distinct r_id from t2) t2 on t1.id = t2.r_id;

结论

在 MySQL 中,EXISTS 在命中率高的情况下查询速度较快,像这种需要判断的表的数据量较大,而条件表的数据量较少时不宜使用。INNER JOIN 相对较稳定一些,不会随命中率的变化而影响性能


如果读完觉得有收获的话,欢迎点赞、关注、加公众号【匠心零度】,查阅更多精彩历史!!!












最新文章

123

最新摄影

闪念基因

微信扫一扫

第七城市微信公众平台