东莞网站建设-信科网络,网站建设经验与教训,网站建设丨找王科杰专业,.net 网站开发书籍in和exists的转换
1 结论
in()适合子查询结果集比外表查询结果集小的情况#xff08;子表查询结果集的记录数决定了数据库的交互次数#xff09;exists()适合子查询结果集比外表查询结果集大的情况#xff08;外表查询结果集的记录数决定了数据库的交互次数#xff09;当…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 i0;iA.length;i) {for(int j0;jB.length;j) {if(A[i].idB[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.idb.id)类似于以下过程:
for select * from A
for select 1 from B where B.id A.id 它的查询过程类似于以下过程
List resultSet[];
Array A(select * from A)for(int i0;iA.length;i) {if(exists(A[i].id) { //执行select 1 from B b where b.ida.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要快。
