MySQL或者一般的SQL中，BETWEEN和IN有性能上的区别吗？

Question

我有一组连续的行，我想基于它们的主键（一个自动递增的整数）来获取它们。假设没有缺口，下面两种方式之间有任何性能差异吗：

SELECT * FROM `theTable` WHERE `id` IN (n, ... nk); 

并且：

SELECT * FROM `theTable` WHERE `id` BETWEEN n AND nk;

- pr1001

---

两者完全不同。一个检查范围，就像模拟信号一样，另一个检查状态，就像数字信号一样。因此，这不适用于性能比较。 - Sadat

从逻辑上讲，使用"between"应该表现更佳，因为它会对每个元素执行两次比较，而不是在"IN"情况下比较ID的数量（但这只是我的感觉...没有确凿的证据支持这一点）。 - Gabriele Petrioli

你应该考虑重新分配已接受的答案（如果可能的话）给LukasEnder。Andomar的回答是错误的，而LukasEnder解释了原因。 - Code Commander

Answer 1

BETWEEN（在这种情况下）应该优于IN，特别是当 n 增长且统计数据仍然准确时，请确保测量并检查执行计划！假设：

• m 是您的表的大小
• n 是您要查询的范围大小

索引可用（n 与 m 相比较小）

• 理论上，BETWEEN 可以使用主键索引进行单个 "范围扫描" (Oracle 术语)，然后遍历最多 n 个索引叶节点。复杂度将为 O(n + log m)

• IN 通常被实现为对主键索引进行一系列（循环）n 次 "范围扫描"。随着表的大小 m 增加，复杂度将始终为 O(n * log m) ... 这总是更差的（对于非常小的表 m 或非常小的范围 n，可以忽略不计）。

索引不可用（n 是 m 的显着部分）

无论如何，您都会获得全表扫描并计算每行的谓词：

• BETWEEN 需要计算两个谓词：一个是下限，另一个是上限。复杂度为 O(m)

• IN 需要计算最多 n 个谓词。复杂度为 O(m * n) ... 再次更糟，并且如果数据库可以将 IN 列表优化为哈希表而不是谓词列表，则可能为 O(m)。

- Lukas Eder

---

我期望在扫描范围时，使用范围扫描比使用唯一扫描更好。否则，为什么Oracle会实现范围扫描呢？ - Andomar

在MySQL中，与BETWEEN相比，IN操作可能会对性能造成严重影响。我曾经看到过包含几千个数字的IN操作导致查询停滞了数秒钟。而使用BETWEEN进行相同的查询只需要几毫秒的时间。因此，如果可以选择，最好总是使用BETWEEN。 - John

@John：永远不要说“总是”。如果你的IN列表只有1-2个元素，我有点怀疑你的说法是否正确。 - Lukas Eder

@LukasEder 当使用2个或更多元素时，BETWEEN将与IN一样快，因此使用它不会有任何损失。而当只使用1个元素时，您没有理由使用其中任何一个。 “始终”保持有效，无论是2个元素还是200万个元素，您要定位的元素越多，它就越有效率。没有任何理由使用IN来实现BETWEEN相同的功能，两种语法变体都有其目的。当使用IN替换BETWEEN时，您就剥夺了它的目的。 - John

Answer 2

a between b and c是一个宏，它会被扩展为b <= a and a <= c。

a in (b,c,d)是一个宏，它会被扩展为a=b or a=c or a=d。

假设您的n和nk都是整数，两个表达式最终应该意义相同。使用between变体应该更快，因为它只需要进行两次比较，而in变体需要进行nk - n次比较。

- Andomar

---

BETWEEN子句中较短的字符串解析速度更快。 - Erick Robertson

太好了，谢谢。我现在可以给你答案，但是SO说我需要等待7分钟。 - pr1001

@LukasEder是正确的。根据你的索引，IN可能会更快。了解最佳方法是在你特定情况下对两个选项进行基准测试。 - Code Commander

实际上 a in (b,c,d) 是一个宏，它展开为 a = any(b,c,d) （参见 SQL-92 标准）. - eci

Answer 3

我已经为这个问题做了研究。我的表格有1100万行。我执行了两个查询：

查询1：SELECT * FROM PLAYERS WHERE SCORE BETWEEN 10 TO 20 查询2：SELECT * FROM PLAYERS WHERE SCORE IN (10,11,...,20) 在执行时间上，这两个查询都像Andomar所说的那样被翻译。

在这两个查询中，查询1比查询2运行得更快。

要了解更多，请访问此链接： MySQL中BETWEEN VS IN()的性能比较 谢谢！

- Arpita

Answer 4

在许多数据库服务器中，IN()只是多个OR子句的同义词，因为它们在逻辑上等效。但在MySQL中，情况并非如此，它会对IN()列表中的值进行排序，并使用快速二分搜索来查看一个值是否在列表中。这种算法的时间复杂度是O(Log n)，而使用等效的OR子句系列的时间复杂度则为O(n)（即对于大型列表而言速度要慢得多）。

- Dhrubo Saha

---

我相信这是来自于《高性能MySQL：优化、备份与复制》的直接引用，不是吗？如果是的话，应该注明出处！ - Aaron Francis