Sql中in和exists详解

in和exists的转换

1 结论

1. in()适合子查询结果集比外表查询结果集小的情况（子表查询结果集的记录数决定了数据库的交互次数）
2. exists()适合子查询结果集比外表查询结果集大的情况（外表查询结果集的记录数决定了数据库的交互次数）
3. 当外表查询结果集与子查询结果集数据一样大时，in与exists效率差不多,可任选一个使用
4. 小表驱动大表（更准确的说是查询结果集小的驱动查询结果集大的）
5. IN查询在内部表和外部表上都可以使用到索引。
6. Exists查询仅在内部表上可以使用到索引。
7. 表的规模不是看内部表和外部表记录数的，而是外部表和子查询结果集中记录数的大小

2 in和exists的区别

2.1 in的性能分析

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

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

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

以上查询使用了in语句,in()只执行一次,它查出B表中的所有id字段并缓存到内存中之后,检查A表的id是否与B表中的id相等,如果相等则将A表的记录加入结果集中,直到遍历完A表的所有记录.
它的查询过程类似于以下过程

List resultSet=[];
Array A=(select * from A);
Array B=(select id from B);

for(int i=0;i<A.length;i++) {
   for(int j=0;j<B.length;j++) {
      if(A[i].id==B[j].id) {
         resultSet.add(A[i]);
         break;
      }
   }
}
return resultSet;
1
2
3
4
5
6
7
8
9
10
11
12
13

分析：

1. 当前的in子查询是B表驱动A表
2. mysql先将B表的数据一次性查出来存放于内存中，B表的记录数决定了数据库的交互次数
3. 遍历B表的数据，再去查A表（每次遍历都是一次连接交互，这里会耗资源）
4. 假设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)
1
2

类似于以下过程:

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

它的查询过程类似于以下过程

List resultSet=[];
Array A=(select * from A)

for(int i=0;i<A.length;i++) {
   if(exists(A[i].id) {    //执行select 1 from B b where b.id=a.id是否有记录返回
       resultSet.add(A[i]);
   }
}
return resultSet;
1
2
3
4
5
6
7
8
9

分析：

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

2.3 实例

1. 建表sql

#–1.学生表 
#-Student(s_id,s_name,s_birth,s_sex) –学生编号,学生姓名, 出生年月,学生性别
CREATE TABLE `Student` (
    `s_id` VARCHAR(20),
    s_name VARCHAR(20) NOT NULL DEFAULT '',
    s_brith VARCHAR(20) NOT NULL DEFAULT '',
    s_sex VARCHAR(10) NOT NULL DEFAULT '',
    PRIMARY KEY(s_id)
);

#–2.成绩表 
#Score(s_id,c_id,s_score) –学生编号,课程编号,分数
Create table Score(
    s_id VARCHAR(20),
    c_id VARCHAR(20) not null default '',
    s_score INT(3),
    primary key(`s_id`,`c_id`)
);

#-3.插入学生表数据
insert into Student values('01' , '赵雷' , '1990-01-01' , '男');
insert into Student values('02' , '钱电' , '1990-12-21' , '男');
insert into Student values('03' , '孙风' , '1990-05-20' , '男');
insert into Student values('04' , '李云' , '1990-08-06' , '男');
insert into Student values('05' , '周梅' , '1991-12-01' , '女');
insert into Student values('06' , '吴兰' , '1992-03-01' , '女');
insert into Student values('07' , '郑竹' , '1989-07-01' , '女');
insert into Student values('08' , '王菊' , '1990-01-20' , '女');

#-4.成绩表数据
insert into Score values('01' , '01' , 80);
insert into Score values('01' , '02' , 90);
insert into Score values('01' , '03' , 99);
insert into Score values('02' , '01' , 70);
insert into Score values('02' , '02' , 60);
insert into Score values('02' , '03' , 80);
insert into Score values('03' , '01' , 80);
insert into Score values('03' , '02' , 80);
insert into Score values('03' , '03' , 80);
insert into Score values('04' , '01' , 50);
insert into Score values('04' , '02' , 30);
insert into Score values('04' , '03' , 20);
insert into Score values('05' , '01' , 76);
insert into Score values('05' , '02' , 87);
insert into Score values('06' , '01' , 31);
insert into Score values('06' , '03' , 34);
insert into Score values('07' , '02' , 89);
insert into Score values('07' , '03' , 98);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48

数据展示：

2. in方法

SELECT
	a.* 
FROM
	Student a 
WHERE
	a.s_id IN (SELECT b.s_id FROM Score b WHERE b.c_id = '01')
1
2
3
4
5
6

3. exists方法

SELECT
	a.* 
FROM
	Student a 
WHERE
	EXISTS(SELECT * FROM Score b WHERE a.s_id = b.s_id AND b.c_id = '01')
1
2
3
4
5
6

4. 结果

3 not in 和not exists

如果查询语句使用了not in，那么内外表都进行全表扫描，没有用到索引；但not extsts 的子查询依然能用到表上的索引。所以无论哪个表大，用not exists都比not in要快。