IN和EXISTS的区别和使用

2022-10-10 11:18:27

一、结论

in()适合子表比主表数据小的情况
exists()适合子表比主表数据大的情况
当主表数据与子表数据一样大时,in与exists效率差不多,可任选一个使用

二、区别

2.1 in的性能分析

select * from A
where id in(select id from B)

我们知道上诉sql会先执行括号内的子查询,再执行主查询,因此相当于以下过程：

for select id from B
for select * from A where A.id = B.id

分析：
0、当前的in子查询是B表驱动A表
1、mysql先将B表的数据一把查出来至于内存中
2、遍历B表的数据，再去查A表（每次遍历都是一次连接交互，这里会耗资源）
3、假设B有100000条记录，A有10条记录，会交互100000次数据库；再假设B有10条记录，A有100000记录，只会发生10次交互。

结论:in()适合B表比A表数据小的情况

2.2 Exists的性能分析

select a.* from A a
where exists(select 1 from B b where a.id=b.id)

类似于以下过程

for  select * from A
for  select 1 from B where B.id = A.id

分析：
0、当前exists查询是A表驱动B表
1、与in不同，exists将A的纪录数查询到内存，因此A表的记录数决定了数据库的交互次数
2、假设A有10000条记录，B有10条记录，数据库交互次数为10000；假设A有10条，B有10000条，数据库交互次数为10。

2.3 小结

我们来看下面两个循环：
for (int i = 0; i<10000; i++){
for(int j = 0; j<10; j++){
····

for (int i = 0; i<10; i++){
for(int j = 0; j<10000; j++){
····
在java中，我们都知道上述的两个循环的时间复杂度都是一样的；但在数据库中则是有区别的，首先第一层循环，数据库只做一次交互一把将数据查出到缓存中，而第二层循环的数据库交互次数决定于第一层循环数据量的大小。对于数据库而言，交互次数越多越耗费资源，一次交互涉及了“连接-查找-断开”这些操作，是相当耗费资源的。
使用in时，B表驱动A
使用exists时，A表驱动B
所以我们写sql时应当遵循“小表驱动大表“的原则

三、sql使用示例

3.1 in的使用

select a_Id,a1,a2,a3 from A where id in (?,?);
select a_Id,a1,a2,a3 from A where id in (select b_id from B where b1 = ?);

3.2 Exsits的使用

select a_id,a1,a2,a3 from A where Exists (select 1 from B where b1 = ? and b_id = a_id);

四、工作中应用场景

表A（id1，id2，id3，a1，a2，a3···），主键（id1，id2，id3）
表B（id3，b1，b2，b3···），主键（id3）
业务：根据给出的id1条件，去查询仅被id1引用的表B记录.
Exists:

select * from B b
	where 
		exists ( select 1 from A  where id3 = b.id3 and id1 = ?)
		and not exists (select 1 from A where id3 != b.id3  and id1 = ?)

In:

select * from B 
	where 
		id3 in ( select id3 from A  where id1 = ?)
		and not in (select id3 from A where i id1 = ?)

五、小谈

1、in的限制

在oracle中使用IN，其括号内列表允许最大的表达式数为1000，超出1000会报错。
如下sql:

select * from A where id in ('1'，'1'，'1'，'1'，'1'，····，'1','1');

当括号内的值超出1000会报错，但在mysql上执行却是可以的。
处于好奇，本人又做了如下sql测试：

select * from A where id in (select id from A);

这条sql执行是成功的，即便括号内的语句查出的值超出1000，在oracle和mysql都是能执行成功的。

因此，oracle报的表达式列表不能超过1000，并非是容量不能超过1000.

2、count(*)、count(id)、count(1)选择

使用select 1 from table的结果是临时得到1列（列的值为1），其行数为表的记录数（行数），如果配合exists 语句则可以快速查询结果是否存在，而结果的具体数据不涉及到，在应用中，效率比select * 快。
扩展： select 1 from table;与select anycol(目的表集合中的任意一行） from table;与select * from table 从作用上来说是没有差别的，都是查看是否有记录，一般是作条件查询用的。select 1 from 中的1是一常量（可以为任意数值），查到的所有行的值都是它，但从效率上来说，1>anycol>*，因为不用查字典表。
也由此，我们可以知道，平常时我们统计行数时，使用count(常量)，效率更高，如下：

select count(id) from table;
select count(*) from table;
select count(1) from table;

count(1)的效率最高，因为它不关注具体的数据。

那我们开发中就要选择count(1)吗？不一定
可以参考阿里规范：
https://developer.aliyun.com/article/756450

一、 结论