Mysql 中 exists 和 in 的区别[java教程]

Mysql 中 exists 和 in 的区别

Mysqlexistsin 的区别[java教程]

最近刷到面试题:Mysqlexists 和 in 的区别,先说下答案。

下面将主查询的表称为外表;子查询的表称为内表。exists 与 in 的区别如下:

  • 子查询使用 exists,会先进行主查询,将查询到的每行数据循环带入子查询校验是否存在,过滤出整体的返回数据;子查询使用 in,会先进行子查询获取结果集,然后主查询匹配子查询的结果集,返回数据
  • 外表内表相对大小情况不一样时,查询效率不一样:两表大小相当,in 和 exists 差别不大;内表大,用 exists 效率较高;内表小,用 in 效率较高。
  • 不管外表与内表的大小,not exists 的效率一般要高于 not in,跟子查询的索引访问类型有关。

建表、造数据,验证一下以上答案。

# 建表 student1
drop table if exists student1;
create table student1(
sid int primary key auto_increment,
sname varchar(40)
);

# 建存储过程给表 student1,插入1000条数据
drop procedure if exists addStudent1;
create procedure addStudent1()
BEGIN
	declare idx int;
	set idx = 1;
	while idx <= 1000 DO
	insert into student1 values(null, concat('student-', idx));
	set idx = idx + 1;
  end while;
end;

call addStudent1();

select * from student1;

# 建表 student2
drop table if exists student2;
create table student2(
sid int primary key auto_increment,
sname varchar(40)
);

# 建存储过程给表 student2,插入100000条数据
drop procedure if exists addStudent2;
create procedure addStudent2()
BEGIN
	declare idx int;
	set idx = 1;
	while idx <= 100000 DO
	insert into student2 values(null, concat('student-', idx));
	set idx = idx + 1;
  end while;
end;

call addStudent2();

select * from student2;

in 与 exists 的查询 SQL

select count(1) from student1 where sname in (select sname from student2);
select count(1) from student1 where exists (select sname from student2 where student2.sname = student1.sname);
select count(1) from student2 where sname in (select sname from student1);
select count(1) from student2 where exists (select sname from student1 where student2.sname = student1.sname);

执行时间:

[SQL] select count(1) from student1 where sname in (select sname from student2);
受影响的行: 0
时间: 0.092s

[SQL] 
select count(1) from student1 where exists (select sname from student2 where student2.sname = student1.sname);
受影响的行: 0
时间: 0.076s

[SQL] 
select count(1) from student2 where sname in (select sname from student1);
受影响的行: 0
时间: 14.820s

[SQL] 
select count(1) from student2 where exists (select sname from student1 where student2.sname = student1.sname);
受影响的行: 0
时间: 15.144s

结论:student2 大表在内适用 exists,所以第 2 条 SQL 比第 1 条快;student1 小表在内适用 in,所以第 3 条 SQL 比第 4 条快。

not in 与 not exists 的查询 SQL

select count(1) from student1 where sname not in (select sname from student2);
select count(1) from student1 where not exists (select sname from student2 where student2.sname = student1.sname);
select count(1) from student2 where sname not in (select sname from student1);
select count(1) from student2 where not exists (select sname from student1 where student2.sname = student1.sname);

执行时间:

[SQL] 
select count(1) from student1 where sname not in (select sname from student2);
受影响的行: 0
时间: 0.079s

[SQL] 
select count(1) from student1 where not exists (select sname from student2 where student2.sname = student1.sname);
受影响的行: 0
时间: 0.075s

[SQL] select count(1) from student2 where sname not in (select sname from student1);
受影响的行: 0
时间: 15.797s

[SQL] 
select count(1) from student2 where not exists (select sname from student1 where student2.sname = student1.sname);
受影响的行: 0
时间: 15.160s

结论:not exists 性能高于 not in

给 student1、student2 sname 字段,加上索引,上述结论仍然成立。

create index idx_1 on student1(sname);
create index idx_2 on student2(sname);

执行时间:

[SQL] select count(1) from student1 where sname in (select sname from student2);
受影响的行: 0
时间: 0.022s

[SQL] 
select count(1) from student1 where exists (select sname from student2 where student2.sname = student1.sname);
受影响的行: 0
时间: 0.014s

[SQL] 
select count(1) from student2 where sname in (select sname from student1);
受影响的行: 0
时间: 0.379s

[SQL] 
select count(1) from student2 where exists (select sname from student1 where student2.sname = student1.sname);
受影响的行: 0
时间: 0.373s

[SQL] 
select count(1) from student1 where sname not in (select sname from student2);
受影响的行: 0
时间: 0.006s

[SQL] 
select count(1) from student1 where not exists (select sname from student2 where student2.sname = student1.sname);
受影响的行: 0
时间: 0.006s

[SQL] 
select count(1) from student2 where sname not in (select sname from student1);
受影响的行: 0
时间: 0.455s

[SQL] 
select count(1) from student2 where not exists (select sname from student1 where student2.sname = student1.sname);
受影响的行: 0
时间: 0.418s

再细看一下,not in 与 not exists 查询索引使用情况

not in,子查询使用了 index_subquery 访问类型

EXPLAIN EXTENDED select count(1) from student2 where sname not in (select sname from student1);
SHOW WARNINGS;

Mysql 中 exists 和 in 的区别[java教程]-盾给网络

not exists,子查询使用了 ref 访问类型

EXPLAIN EXTENDED select count(1) from student2 where not exists (select sname from student1 where student2.sname = student1.sname);
SHOW WARNINGS;

Mysql 中 exists 和 in 的区别[java教程]-盾给网络

 

人已赞赏
java

@Transactional 注解哪些情况下会失效?[java教程]

2020-7-31 11:06:46

wordpress

【Wordpress插件】weDocs - 文档插件

2019-10-11 9:31:01

⚠️
盾给网上的部份代码及教程来源于互联网,仅供网友学习交流,未经作者或上传书面授权,请勿作他用。
若您的权利被侵害,请联系站长 QQ: 84087680 或 点击右侧 私信:盾给网络管理员 反馈,我们将尽快处理。
0 条回复 A文章作者 M管理员
    暂无讨论,说说你的看法吧
个人中心
购物车
优惠劵
今日签到
有新私信 私信列表
搜索