外链网站分类重庆网站建设技术外包
in和exists的转换
1 结论
- in()适合子查询结果集比外表查询结果集小的情况（子表查询结果集的记录数决定了数据库的交互次数）
- exists()适合子查询结果集比外表查询结果集大的情况（外表查询结果集的记录数决定了数据库的交互次数）
- 当外表查询结果集与子查询结果集数据一样大时，in与exists效率差不多,可任选一个使用
- 小表驱动大表（更准确的说是查询结果集小的驱动查询结果集大的）
- IN查询在内部表和外部表上都可以使用到索引。
- Exists查询仅在内部表上可以使用到索引。
- 表的规模不是看内部表和外部表记录数的，而是外部表和子查询结果集中记录数的大小
2 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
以上查询使用了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;
分析：
- 当前的in子查询是B表驱动A表
- mysql先将B表的数据一次性查出来存放于内存中，B表的记录数决定了数据库的交互次数
- 遍历B表的数据，再去查A表（每次遍历都是一次连接交互，这里会耗资源）
- 假设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
它的查询过程类似于以下过程
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;
分析：
- 当前exists查询是A表驱动B表
- 与in不同，exists将A的纪录查询到内存，因此A表的记录数决定了数据库的交互次数
- 假设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);
数据展示：
2. in方法
SELECTa.*
FROMStudent a
WHEREa.s_id IN (SELECT b.s_id FROM Score b WHERE b.c_id = '01')
3. exists方法
SELECTa.*
FROMStudent a
WHEREEXISTS(SELECT * FROM Score b WHERE a.s_id = b.s_id AND b.c_id = '01')
4. 结果
3 not in 和not exists
如果查询语句使用了not in，那么内外表都进行全表扫描，没有用到索引；但not extsts 的子查询依然能用到表上的索引。所以无论哪个表大，用not exists都比not in要快。