技术栈

主页 > 其他 >

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

技术栈 - 中国领先的IT技术门户

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

说在前面

上篇文章从开发者角度谈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 相对较稳定一些,不会随命中率的变化而影响性能


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

责任编辑:admin  二维码分享:
本文标签: t1EXISTSidt2select查询