MySQL中In与Exists的区别
一、EXISTS和IN的介绍
1. exists
exists对外表用loop逐条查询,每次查询都会查看exists的条件语句,当exists里的条件语句能够返回记录行时(无论记录行是的多少,只要能返回),条件就为真,返回当前loop到的这条记录;反之,如果exists里的条件语句不能返回记录行,则当前loop到的这条记录被丢弃,exists的条件就像一个bool条件,当能返回结果集则为true,不能返回结果集则为false
例如:
select * from user where exists (select 1);
对user表的记录逐条取出,由于子条件中的select 1永远能返回记录行,那么user表的所有记录都将被加入结果集,所以与select * from user;是一样的。
例如:
select * from user where exists (select * from user where user_id = 0);
可以知道对user表进行loop时,检查条件语句(select * from user where user_id = 0),由于user_id永远不为0,所以条件语句永远返回空集,条件永远为false,那么user表的所有记录都将被丢弃。、
总结:
如果A表有n条记录,那么exists查询就是将这n条记录逐条取出,然后判断n遍exists条件。
2. in
in查询相当于多个or条件的叠加,这个比较好理解,比如下面的查询:
select * from user where user_id in (1, 2, 3);
等效于
select * from user where user_id = 1 or user_id = 2 or user_id = 3;
总结:
in查询就是先将子查询条件的记录全都查出来,假设结果集为B,共有m条记录,然后再将子查询条件的结果集分解成m个,再进行m次查询。
3. 使用上的区别
in查询的子条件返回结果必须只有一个字段,例如
select * from user where user_id in (select id from B);
不能是
select * from user where user_id in (select id, age from B);
而exists就没有这个限制。
二、EXISTS和IN的性能分析
为了便于分析,我把实际上的例子简化一下。
SELECT t1.id FROM tb_data t1
WHERE t1.task_id IN (SELECT t2.id FROM tb_task t2);
SELECT t1.id FROM tb_data t1
WHERE EXISTS (SELECT * FROM tb_task t2 WHERE t1.task_id = t2.id);
简化后:
SELECT * FROM A WHERE A.id IN (SELECT id FROM B);
SELECT * FROM A WHERE EXISTS (SELECT * from B WHERE B.id = A.id);
- in
假设B表的所有id为(1,2,3),查询1可以转换为:
SELECT * FROM A WHERE A.id = 1 OR A.id = 2 OR A.id = 3;
这里主要是用到了A的索引,B表如何对查询影响不大。
- exists
查询2可以转化以下伪代码:
for (i = 0; i < count(A); i++) {
a = get_record(A, i); #从A表逐条获取记录
if (B.id = a[id]) { #如果子条件成立
result[] = a;
}
}
return result;
这里主要用到了B表的索引,A表如何对查询的效率影响不大。
- 实际情况
SELECT t1.id FROM tb_data t1
WHERE t1.task_id IN (SELECT t2.id FROM tb_task t2);
它使用的索引情况如下:
使用了t1(A)表索引
SELECT t1.id FROM tb_data t1
WHERE EXISTS (SELECT * FROM tb_task t2 WHERE t1.task_id = t2.id);
它使用的索引情况如下:
使用了t2(B)表索引
三、总结
《高性能MySQL》书上说,MySQL会把in的查询语句改成exists再去执行(实际上我们在没有索引情况下,他们的执行过程确实是一致的)
在《MySQL技术内幕:SQL编程》这本书中说:确实有很多DBA认为EXISTS比IN的执行效率更高,可能是当时优化器还不是很稳定和足够优秀,但是目前绝大数的情况下,IN和EXISTS都具有相同的执行计划。
- IN查询在内部表和外部表上都可以使用到索引。
- Exists查询仅在内部表上可以使用到索引。
- 当子查询结果集很大,而外部表较小的时候,Exists的Block Nested Loop(Block
嵌套循环)的作用开始显现,并弥补外部表无法用到索引的缺陷,查询效率会优于IN。 - 当子查询结果集较小,而外部表很大的时候,Exists的Block嵌套循环优化效果不明显,IN
的外表索引优势占主要作用,此时IN的查询效率会优于Exists。 - 表的规模不是看内部表和外部表,而是外部表和子查询结果集。