SQL性能：哪个更快？IN()还是JOIN？

Question

这是一个我从未得到明确答案的问题。在此示例中，我正在使用MySQL。

假设有一组相当大的值（比如说500个），如果使用IN()子句搜索表格，是否更快：

SELECT * FROM table WHERE field IN(values)

或者通过在内存中创建一个临时表，将其填充为所需的值，并将其与正在搜索的表进行连接：

CREATE TEMPORARY TABLE `temp_table` (`field` varchar(255) NOT NULL) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO temp_table VALUES (values)

SELECT * FROM table t1 JOIN temp_table t2 ON t1.field = t2.field

两种方法都会产生相同的结果集。

我进行了一些基本的基准测试，并发现在处理500个以上的值时，使用临时表比使用IN()子句更快。

有人能向我解释MySQL的内部工作原理以及对这个问题的正确回答是什么吗？

谢谢， Leo

- Leo

---

请发布您的基准测试结果以便进行验证。您的字段上是否有索引？ - Maxim Krizhanovsky

你应该意识到，答案在很大程度上取决于你的数据库结构。创建临时表、填充数据并加入表需要大量的 CPU 工作，而使用带有 IN 运算符的查询则不同。由于内存存储，IO 速度会更快，但 InnoDB 也可以将其工作数据集保存在内存中。如果你基于 InnoDB 表的主键进行 IN 搜索，它将轻松击败临时表。 - N.B.

如果您同时发布两个解释计划，那将会很有趣。 - Martin Smith

两个字段都被索引但不是主键。我的基准测试给出了粗略的结果，表明500个值是转折点。我想知道在使用IN()子句时发生了什么“幕后”。 - Leo

这些都是MyISAM表，我应该添加。 - Leo

Answer 1

从MySql在线文档中，IN():

IN (value,...)

如果所有的值都是常量，它们将根据expr的类型进行评估并排序。 然后使用二分搜索查找该项。这意味着，如果IN值列表完全由常量组成，则IN非常快。 否则，按照第11.2节“表达式计算中的类型转换”中描述的规则进行类型转换，但应用于所有参数。

考虑到我认为使用IN()与一组常量是有意义的，否则应该在另一个表上使用子查询。

当从另一个表检索项目时，您可以考虑使用EXISTS()代替JOIN，对于大型数据集，它会显著提高速度。

SELECT * 
FROM table t1 
WHERE EXISTS 
      (
        SELECT * 
        FROM temp_table t2 
        WHERE t1.field = t2.field
      )

- sll

---

我不理解二进制搜索的部分。为了使其工作，列必须经过排序，因此可能被索引了吧？那么为什么不直接从索引中查找呢？（除非他们指的是搜索使用二进制排序语义，而不是实际上的二进制搜索。） - Martin Smith

@Martin Smith：二分查找（根据文档仅用于常数）在常数集合已排序的情况下是有意义的，这意味着服务器会先进行排序，然后再执行二分查找。 - sll

啊，我明白了。我之前想反了。所以MySQL会先进行全表扫描，然后对每个值在常量列表中进行二分查找？如果该列被索引，并且in子句只有几个值，那么进行几次索引查找肯定更有效率吧？ - Martin Smith

@Martin Smith: 我不是这方面的专家，所以可能会有错，但据我理解，当查询从表中返回大量数据时，优化器会自动应用索引查找，但是它如何确保在搜索 IN(...) 列表中的值时优先选择索引查找呢？希望我的思路表达清楚了。 - sll

Answer 2

正确的答案取决于许多因素。

如果您的基准测试告诉您使用临时表更快，那么就应该采用这种方式。

请记得在更改硬件或大幅修改模式后再次进行基准测试。

- Grim...