MySQL exists 与 in 的性能及效率

Posted on

注意：下面结果针对5.7版本，无论大表在前还是小表在前，in 的查询效率都要远远高于exists。但在8.0版本中，得益于版本的改善，in 的查询效率 与 exists 几乎没有明显差别。

MySQL 8 里面的优化可参考：Optimizing Subqueries with Materialization

MySQL 中的 in 语句是把外表和内表做 hash 连接，而 exists 语句是对外表做 loop 循环，每次 loop 循环再对内表进行查询。一直大家都认为 exists 比 in 语句的效率要高，这种说法其实是不准确的。需要分不同情况讨论。

exists 对外表用 loop 逐条查询，每次查询都会查看 exists 的条件语句，当 exists 里的条件语句能够返回记录行时（无论记录行有多少，只要能返回），条件就为真，返回当前 loop 到的这条记录，反之如果 exists 里的条件语句不能返回记录行，则当前 loop 到的这条记录被丢弃，exists 的条件就像一个 bool 条件，当能返回结果集则为 true，不能返回结果集则为 false。

实例一：exists 子句永远为 true

1
select * from user where exists (select 1);

对 user 表的记录逐条取出，由于子条件中的 select 1 永远能返回记录行，那么 user 表的所有记录都将被加入结果集，所以与 select * from user; 是一样的。

实例二：exists 子句永远为 false

这里假设 user 表的 userId 都是大于 0 的

1
select * from user where exists (select * from user where userId = 0);

可以知道对 user 表进行 loop 时，检查条件语句 select * from user where userId = 0，由于 userId 永远不为 0，所以条件语句永远返回空集，条件永远为 false，那么 user 表的所有记录都将被丢弃。

not exists 与 exists 相反，也就是当子查询有结果返回时，loop 到的记录将被丢弃，否则将 loop 到的记录加入结果集。

exists 的执行过程

总的来说，如果 A 表有 n 条记录，那么 exists 查询就是将这 n 条记录逐条取出，然后判断 n 遍 exists 条件。

in 的实际效果

in 查询相当于多个 or 条件的叠加，这个比较好理解，比如下面的查询：

1
select * from user where userId in (1, 2, 3);

等效于：

1
select * from user where userId = 1 or userId = 2 or userId = 3;

not in 与 in 相反，如下：

1
select * from user where userId not in (1, 2, 3);

等效于：

1
select * from user where userId != 1 and userId != 2 and userId != 3;

in 的执行过程

总的来说，in 查询就是先将子查询结果集拿出来，假设结果集为 B，共有 m 条记录，然后在将子查询条件的结果集分解成 m 个，再进行 m 次查询。

值得一提的是，in 查询的子条件返回结果必须只有一个字段，例如：

1
select * from user where userId in (select id from B);

而不能是：

1
select * from user where userId in (select id, age from B);

而 exists 就没有这个限制。

exists 和 in 的性能

考虑如下SQL语句

  1. select * from A where exists (select * from B where B.id = A.id);

  2. select * from A where A.id in (select id from B);

查询 1 可以转化为以下伪代码，便于理解：

1
2
3
4
5
6
7
8
9
10
$result = [];

for ($i = 0; $i < count(A); $i++) {
　　$a = get_record(A, $i);  // 从A表逐条获取记录

　　if (B.id = $a[id])  // 如果子条件成立
　　　　$result[] = $a;
}

return $result;

查询 1 主要是用到了 B 表的索引，A 表如何对查询的效率的影响应该不大。

假设B表的所有id为1,2,3,查询2可以转换为：

1
select * from A where A.id = 1 or A.id = 2 or A.id = 3;

这个就好理解了，这里主要是用到了 A 的索引，B 表如何对查询影响不大。

not exists 和 not in

  1. select * from A where not exists (select * from B where B.id = A.id);

  2. select * from A where A.id not in (select id from B);

查询 1 还是和上面一样，用了 B 的索引。

而对于查询 2，可以转化为如下语句：

1
select * from A where A.id != 1 and A.id != 2 and A.id != 3;

可以知道 not in 是个范围查询，这种 != 的范围查询无法使用任何索引，等于说 A 表的每条记录，都要在 B 表里遍历一次，查看 B 表里是否存在这条记录。

故 not exists 比 not in 效率高。

mysql 中的 in 语句是把外表和内表作 hash 连接，而 exists 语句是对外表作 loop 循环，每次 loop 循环再对内表进行查询。一直大家都认为 exists 比 in 语句的效率要高，这种说法其实是不准确的。这个是要区分环境的。

如果查询的两个表大小相当，那么用 in 和 exists 差别不大

如果两个表中一个较小，一个是大表，则子查询表大的用 exists，子查询表小的用 in：

例如，表 A（小表），表 B（大表）

  1. select * from A where cc in (select cc from B) 效率低，用到了 A 表上 cc 列的索引；

select * from A where exists(select cc from B where cc=A.cc) 效率高，用到了 B 表上 cc 列的索引。

相反的

  1. select * from B where cc in (select cc from A) 效率高，用到了 B 表上 cc 列的索引；

select * from B where exists(select cc from A where cc=B.cc) 效率低，用到了 A 表上 cc 列的索引。

not in 和 not exists 如果查询语句使用了 not in 那么内外表都进行全表扫描，没有用到索引；而 not exists 的子查询依然能用到表上的索引。

所以无论那个表大，用 not exists 都比 not in 要快。