MySQL 中 exists 和 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] 
elect 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;

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

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