MySQL临时表与WHERE IN子句

Question

问题如下。

我需要从表User中获取phoneNumber列为phoneNumberArray中的值的所有数据，其中*代表所有列。phoneNumberArray中大约有300到1000个电话号码。表User在phoneNumber列上有一个索引，并且可能包含数百万行数据。

是否更好创建一个临时表UserphoneNumbers并将所有电话号码插入其中并进行JOIN操作，还是使用简单的WHERE IN语句并传递1000个电话号码的数组？

- Nicolas Manzini

---

尝试两种方法并查看执行计划和统计数据，我猜使用临时表会更昂贵，因为它需要构建和填充表。 - dbajtr

请您展示一下查询语句、数据，或者两者都有也可以。 - Tim Biegeleisen

如果您的查询频率低且电话号码列表是静态的，那么创建一个临时表来存储电话号码将是最佳选择。但如果电话号码列表是动态的，使用WHERE IN会更好，可以减少表的创建和删除次数。 - User123456

我基本上会创建表格以便只查询一次，然后删除它。我只会在创建新账户时这样做。我会尝试两种方法并测试结果。 - Nicolas Manzini

"WHERE IN ( ... )" 意味着您需要通过许多数字之一查找“用户”。这似乎不太可能是使用情况。 - Rick James

@RickJames 将其想象成将一个电话号码数组映射到一个包含用户数据的数组。 - Nicolas Manzini

Answer 1

这是一个有趣的问题。有很多理由支持使用第二种方法——将值放入数组中。

使用临时表并不是一个不合理的选择，但它会给应用程序增加显著的复杂性。它会增加数据库的读写开销。应用程序必须创建表并加载它（数据库会负责删除它）。

如果您确实认为需要这个选项，请确保电话号码是表中的主键。这对于性能是最优的。

在MySQL中，特别是使用IN是一个合理的选择。MySQL对常量IN列表进行排序，并使用二进制搜索来匹配值。这对于全表扫描最有益。由于列表很大，全表扫描可能是最有效的方法。

个人而言，我会从IN列表开始。生成一个包含1,000个元素的IN列表的查询与包含3个元素的列表的代码复杂度相同。如果不能满足您的需求，请尝试使用临时表来测试性能。如果效果更好，则采用该方法。

- Gordon Linoff

Answer 2

我认为第一种方法更易读，更易于维护。

如果有一天你想从临时表切换到持久表，这将更容易。

关于性能，已经有一个关于它的主题：

我怀疑如果在WHERE子句中执行此操作，规划器会选择一条更有效的路线（即基于索引或预过滤的数据集）。

您可能可以通过在两个列上添加索引（不确定Postgres是否支持包含列和多列索引）来使连接运行得更快（如果不是更快）。

- Antoine Stas

---

感谢您的回答。关于这个主题有几篇帖子，但最终总是会涉及到引擎是否会进行优化。由于许多答案都是几年前的，所以很难知道引擎在2017年做了什么。 - Nicolas Manzini

你说得没错，但我处理的数据量非常大（数十亿），而两种方法在性能方面的差异并不显著。因此，我认为正确的方法是你喜欢的那种，从可读性的角度来看。 - Antoine Stas