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 的内部工作原理以及这个问题的正确答案是什么吗？

谢谢， 狮子座

Answer 1

来自MySql在线文档， IN() ：

IN（值，...）

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

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

您可以考虑使用 EXISTS() 当从其他表检索项目时，使用 JOIN 代替 JOIN，对于大型数据集，它会明显更快

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

Answer 2

正确答案取决于很多因素。

您已经完成了工作 - 如果您的基准测试告诉您使用临时表更快，那么就应该这样做。

如果您更改硬件或显着更改架构，请记住再次进行基准测试。