mysql - 在这种情况下，为什么 mysql "where not exist "的性能比 "not in"好得多？ "where not exists"的机制是什么？

Question

最近，一个查询操作的结果着实引起了我的注意。这里是:我的第一个查询是:

select * from tableA as a
where a.keyword NOT IN
(
select keyword from tableB
union
select keyword from tableC
union
select keyword from tableD
)

我的第二个查询是:

select * from tableA as a
where NOT EXISTS
(select NULL from
  (
  select keyword from tableB
  union
  select keyword from tableC
  union
  select keyword from tableD
  ) as t
where a.keyword=t.keyword
)

很明显这两个查询是等价的。但是第一个大约需要 10 秒才能完成，第二个只需要大约 1 秒。我真的很想知道是什么机制导致了如此显着的性能差异。

Answer 1

它们在返回相同结果方面是相同的，但技术上优化器可能会选择不同的路径来执行查询。它因引擎而异，根据统计数据和类似的东西，但特别是对于 mysql，它通常非常简单并且遵循您在查询中编写的内容。

所以第一个可能会从 tableB、tableC、tableD 中获取所有内容，从 keyword 构建哈希然后执行外部查询并检查子查询中是否存在keyword。

第二个可能会从 tableA 中获取每一行，然后检查特定的 keyword 是否存在于 tableB 中，tableC, tableD.

然而，当您从一个版本升级到另一个版本并且优化器变得更智能或显着改变其中一个表中的行数等时，它可能会发生变化。