一、in 与 exists 的区别
1、exists、not exists 一般都是与子查询一起使用,In 可以与子查询一起使用,也可以直接in (a,b…)
2、exists 会针对子查询的表使用索引,not exists 会对主子查询都会使用索引。in 与子查询一起使用的时候,只能针对主查询使用索引,not in 则不会使用任何索引。
注意:一直以来认为 exists 比 in 效率高的说法是不准确的。
in 是把外表和内表作 hash 连接,而 exists 是对外表作 loop 循环,每次 loop 循环再对内表进行查询。
如果查询的两个表大小相当,那么用 in 和 exists 差别不大。
如果两个表中一个较小,一个是大表,则子查询表大的用exists,子查询表小的用in:
-- 例如:表A(小表),表B(大表)
-- 例子1
select * from A where cc in (select cc from B)
-- 效率低,用到了A表上cc列的索引;
select * from A where exists(select cc from B where cc=A.cc)
-- 效率高,用到了B表上cc列的索引。
-- 相反的例子2:
select * from B where cc in (select cc from A)
-- 效率高,用到了B表上cc列的索引;
select * from B where exists(select cc from A where cc=B.cc)
-- 效率低,用到了A表上cc列的索引。
not in 和 not exists 如果查询语句使用了 not in 那么内外表都进行全表扫描,没有用到索引;而 not extsts 的子查询依然能用到子表上的索引。
因为not in实质上等于!= and != …,因为 != 不会使用索引,故 not in 不会使用索引。
所以无论那个表大,用 not exists 都比 not in 要快。
3、exists 与 in 都可以实现一个目的,二者都可以用来过滤数据。
select count(1) from t1; --160W
select count(1) from t2; --90W
SELECT count(1)
FROM t1 a
WHERE EXISTS (SELECT accountid
FROM t2 b
WHERE a.keyid = b.keyid AND a.ideaid = b.ideaid);
--主大子小,不适合使用exist,因为exist只会利用子表t2的复合索引keyid+ideaid,而子表内容要小与主表,主表由于无法使用索引,查询效率低下.
select count(1) from t1 a where a