MYSQL中“IN”操作的性能表现

Question

我正在两步中运行一个MYSQL查询。首先，我使用一个查询获取一个id列表，然后使用第二个查询检索这些id的数据，类似于SELECT * FROM data WHERE id in (id1, id2 ...)。我知道这听起来有点hacky，但由于查询非常复杂; 第一个涉及大量几何和三角函数，第二个涉及许多不同的连接，所以我以这种方式完成了它们。我相信它们可以写成单个查询，但我的MYSQL水平还不够。

这种方法可行，但感觉并不正确；此外，我担心它无法扩展。目前，我正在测试一个包含10,000条记录的数据库，其中“IN”子句中有400个ID（即IN (id1, id2 ... id400)），性能良好。但是如果有100万条记录呢？

这种类型的查询的性能瓶颈在哪里（速度，内存等）？有没有重构这种类型查询的想法，使其更加出色（例如，是否值得研究存储过程）？

- Roy

---

为什么不提供你的查询更多细节？ - RichardOD

我想我并不是在询问任何特定的查询；而只是原则上使用“IN”与大量参数列表是否是一个好主意。 - Roy

Answer 1

当记录数达到一定数量时，SELECT中的 IN 子句比常量列表更快。

请参见我博客中的这篇文章进行性能比较：

• 在MySQL中传递参数：使用IN列表还是临时表

如果查询中 IN 子句中使用的列已经建立索引，就像这样：

SELECT  *
FROM    table1
WHERE   unindexed_column IN
        (
        SELECT  indexed_column
        FROM    table2
        )

如果table1列id和table2列id都被索引，那么如果您使用WHERE table1.id IN (SELECT table2.id FROM table2)这个查询，它会被优化为EXISTS（每条来自table1的记录仅使用一次）。

不幸的是，MySQL无法执行HASH SEMI JOIN或MERGE SEMI JOIN，尽管它们更有效率（特别是如果两个列都被索引）。

- Quassnoi

---

这确实也帮助了我。好文章。 - Mark Rendle

Answer 2

为什么你要先提取ID呢？你应该直接连接这些表。如果你需要用到这些ID，你可以先将它们插入到一个临时表中，然后再使用这个表进行连接。

- Eric Hogue

---

是的，你可能是对的。我首先进行提取，因为提取查询非常复杂（有很多数学计算、一些子查询等），而我的小脑袋无法同时处理连接操作...只是想知道是否应该将这个重构放在我的待办事项列表的顶部！ - Roy

那么你应该将它们放入临时表中。这比获取它们并构建in子句更简单。正如Quassnoi所说，这样做会更快。 - Eric Hogue