MySQL "IN"运算符在（大量？）值上的性能表现

Question

最近我一直在尝试使用Redis和MongoDB，似乎经常需要将一个id数组存储在MongoDB或Redis中。在这个问题中，我将专注于Redis，因为我正在询问MySQL的 IN 运算符。

我想知道在IN运算符中列出大量（300-3000）的 id 对性能的影响如何，它看起来像这样：

SELECT id, name, price
FROM products
WHERE id IN (1, 2, 3, 4, ...... 3000)

想象一下一个简单的产品和类别表，通常可以将它们JOIN在一起以获取某个类别下的产品。在上面的示例中，您可以看到在Redis中给定分类下的（category:4:product_ids）返回了id为4的类别中所有产品的id，并将它们放置在上面的SELECT查询中的IN运算符内。

这有多快？

这是一个“因情况而异”的情况吗？还是有一个具体的“这是（不）可接受的”、“快”的或“慢”的标准？我应该添加一个LIMIT 25，还是那没有帮助？

SELECT id, name, price
FROM products
WHERE id IN (1, 2, 3, 4, ...... 3000)
LIMIT 25

我应该将Redis返回的产品ID数组修剪为25个，然后只向查询添加25个ID，而不是将3000个ID添加到查询中并在查询内部使用LIMIT限制它为25吗？

SELECT id, name, price
FROM products
WHERE id IN (1, 2, 3, 4, ...... 25)

非常感谢任何建议或反馈！

- Michael van Rooijen

---

我不确定你具体在问什么？一个带有“id IN（1,2,3，...3000））”的查询比使用“id = value”的3000个查询更快。但是一个带有“category = 4”的连接将比上述两者都更快。 - Ronnis

虽然一个产品可以属于多个类别，因此不能使用“category = 4”这种方式。使用Redis，我会将所有属于特定类别的产品ID存储起来，然后在此基础上进行查询。我想真正的问题是，“id IN (1,2,3 ... 3000)”与“products_categories”表连接方式之间的性能差异。或者这就是你所说的内容？ - Michael van Rooijen

只需小心MySql中的那个bug。https://dev59.com/pXA75IYBdhLWcg3wOGLS - Itay Moav -Malimovka

当然，这种检索索引行的方法在效率上并没有任何问题；这只取决于数据库作者是否对其进行了测试和优化。就计算复杂度而言，我们最多会在“IN”子句上执行O(n log N)排序（如果使用像您展示的排序列表一样的排序，则可能是线性的，具体取决于算法），然后进行线性交集/查找。 - jberryman

Answer 1

一般而言，如果IN列表变得过大（通常在100或更小范围内），则更有效的方法是使用连接(join)，需要时创建临时表来保存这些数字。

如果数字为密集集合（没有间隔 - 如示例数据所示），则可以通过WHERE id BETWEEN 300 AND 3000来进一步优化。

然而，假设集合中存在间隔，则最好还是按照有效值列表处理（除非间隔数量相对较少，这种情况下可以使用：

WHERE id BETWEEN 300 AND 3000 AND id NOT BETWEEN 742 AND 836

或者说无论缺口是什么。

- Jonathan Leffler

---

可以举个“使用连接和创建临时表”的例子吗？ - Jake

如果数据集来自界面（多选元素），且所选数据存在间隙，并且这些间隙不是顺序的间隙（缺失：457、490、658，...），则 AND id NOT BETWEEN XXX AND XXX 将无法工作，最好使用与 @David Fells 所写的等价的 (x = 1 OR x = 2 OR x = 3 ... OR x = 99)。 - devasia2112

根据我的经验，在电子商务网站上展示大约50个不相关的产品ID的搜索结果时，我们最好使用“1. 50个单独的查询”而不是“2.一个带有许多值的查询”。目前我没有任何证明，除了查询#2总是会在我们的监控系统中显示为慢查询，而#1永远不会出现，即使执行次数达到了数百万...有人有同样的经验吗？（我们可以将其与更好的缓存或允许其他查询在查询之间交错相关联...） - Chaim Klar

@Chaim，当然单独的查询不慢。每个查询只需要获取一条记录。但是性能分析器并不知道这些相关联的查询需要被合并以便进行比较。 - Daniel

Answer 2

我一直在进行一些测试，正如David Fells在他的答案中所说，它已经非常优化了。作为参考，我创建了一个具有1,000,000个记录的InnoDB表，并对500,000个随机数字使用“IN”运算符进行选择，在我的MAC上只需2.5秒；仅选择偶数记录需要0.5秒。

我遇到的唯一问题是我不得不从my.cnf文件中增加max_allowed_packet参数。如果不这样做，就会生成一个神秘的“MYSQL has gone away”错误。

以下是我用于进行测试的PHP代码：

$NROWS =1000000;
$SELECTED = 50;
$NROWSINSERT =15000;

$dsn="mysql:host=localhost;port=8889;dbname=testschema";
$pdo = new PDO($dsn, "root", "root");
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$pdo->exec("drop table if exists `uniclau`.`testtable`");
$pdo->exec("CREATE  TABLE `testtable` (
        `id` INT NOT NULL ,
        `text` VARCHAR(45) NULL ,
        PRIMARY KEY (`id`) )");

$before = microtime(true);

$Values='';
$SelValues='(';
$c=0;
for ($i=0; $i<$NROWS; $i++) {
    $r = rand(0,99);
    if ($c>0) $Values .= ",";
    $Values .= "( $i , 'This is value $i and r= $r')";
    if ($r<$SELECTED) {
        if ($SelValues!="(") $SelValues .= ",";
        $SelValues .= $i;
    }
    $c++;

    if (($c==100)||(($i==$NROWS-1)&&($c>0))) {
        $pdo->exec("INSERT INTO `testtable` VALUES $Values");
        $Values = "";
        $c=0;
    }
}
$SelValues .=')';
echo "<br>";


$after = microtime(true);
echo "Insert execution time =" . ($after-$before) . "s<br>";

$before = microtime(true);  
$sql = "SELECT count(*) FROM `testtable` WHERE id IN $SelValues";
$result = $pdo->prepare($sql);  
$after = microtime(true);
echo "Prepare execution time =" . ($after-$before) . "s<br>";

$before = microtime(true);

$result->execute();
$c = $result->fetchColumn();

$after = microtime(true);
echo "Random selection = $c Time execution time =" . ($after-$before) . "s<br>";



$before = microtime(true);

$sql = "SELECT count(*) FROM `testtable` WHERE id %2 = 1";
$result = $pdo->prepare($sql);
$result->execute();
$c = $result->fetchColumn();

$after = microtime(true);
echo "Pairs = $c Exdcution time=" . ($after-$before) . "s<br>";

结果如下：

Insert execution time =35.2927210331s
Prepare execution time =0.0161771774292s
Random selection = 499102 Time execution time =2.40285992622s
Pairs = 500000 Exdcution time=0.465420007706s

- jbaylina

---

为了他人的利益，我将补充说明，在我的2013年底款MacBook Pro上通过VirtualBox（CentOS）运行时，与问题相关的第三行输出是：随机选择 = 500744时间执行时间= 53.458173036575秒。根据您的应用程序，53秒可能是可以容忍的。对于我的用途来说，实际上并不是很好。此外，请注意，偶数测试对于手头的问题并不相关，因为它使用模运算符（％）与等于运算符（=）而不是IN（）。 - rinogo

这是相关的，因为它是一种比较使用IN运算符的查询与不使用此功能的类似查询的方法。你得到更高时间的原因可能是下载时间，因为你的机器正在交换或在另一个虚拟机中工作。 - jbaylina

Answer 3

您可以创建一个临时表，可以将任意数量的ID放入其中并运行嵌套查询 例如:

CREATE [TEMPORARY] TABLE tmp_IDs (`ID` INT NOT NULL,PRIMARY KEY (`ID`));

并选择：

SELECT id, name, price
FROM products
WHERE id IN (SELECT ID FROM tmp_IDs);

- Vladimir Jotov

---

最好加入您的临时表而不是使用子查询。 - scharette

@loopkin，您能否解释一下如何使用连接和子查询来完成此操作？ - Jeff Solomon

@jeffSolomon 请选取产品表中的id、名称和价格，与临时ID表通过id进行连接。 - scharette

这个答案！正是我在寻找的，非常非常快速适用于长注册表。 - developer_hatch

非常感谢，伙计。它运行得非常快。 - mrHalfer

Answer 4

在大量记录的情况下，使用大参数集合的IN将会非常缓慢。

最近我解决了一个问题，其中有两个where子句，一个有2.5万个参数，另一个有3.5万个参数，查询了一张有4000万条记录的表格。

使用标准的WHERE IN语句，我的查询花费了5分钟。但是，如果使用子查询来替代IN语句（将参数放入自己的索引表中），查询时间可以缩短到2秒。

在我的经验中，这适用于MySQL和Oracle。

- yoyodunno

---

明白了，您的意思是将"WHERE ID IN(1,2,3)"改为"WHERE ID IN(SELECT id FROM xxx)"，用子查询代替IN语句（将参数放入单独的索引表中）。 - Istiyak Tailor

同意istiyak的观点，因为你的陈述不够清晰。 - Manish Gupta

@ManishGupta 对不起没说清楚，但是我认为你理解对了——将所有值放入一个索引表中，并将该SELECT语句作为子查询添加到IN语句中。很难记得，因为这是几年前的事情了。 - yoyodunno

Answer 5

IN语句非常好用，而且已经进行了优化。请确保在索引字段上使用它，并且您就可以放心使用了。

它的功能等同于：

(x = 1 OR x = 2 OR x = 3 ... OR x = 99)

就DB引擎而言。

编辑：请注意，此答案是在2011年编写的，请查看评论以了解最新的MySQL功能。

- David Fells

---

不是真的。我使用IN子句从数据库中获取5k条记录。IN子句包含PK列表，因此相关列已经建立索引并保证唯一性。 EXPLAIN显示，实际上进行了全表扫描，而不是使用“先进先出队列”样式的PK查找。 - Antoniossss

在MySQL中，我不相信它们是“功能等效的”。IN使用优化以获得更好的性能。 - Joshua Pinter

Josh，答案是来自2011年的 - 我确定事情自那时以来已经发生了变化，但是早些时候，IN被彻底转换为一系列OR语句。 - David Fells

这个答案是不正确的。根据《高性能MySQL》所述，在MySQL中，它会对IN()列表中的值进行排序，并使用快速二分查找来确定值是否在列表中。这个过程的时间复杂度是O(log n)，与等效的OR语句相比，OR语句的时间复杂度是O(n)，即在列表很大时会慢得多。 - Bert

Bert - 是的。这个答案已经过时了。请随意建议修改。 - David Fells

Answer 6

当你为IN运算符提供很多值时，它首先必须对其进行排序以去除重复项。至少我怀疑是这样。因此，提供太多值并不好，因为排序需要N log N时间。

我的经验证明，将值集分成较小的子集并组合应用程序中所有查询的结果可以获得最佳性能。我承认我在不同的数据库（Pervasive）上积累了经验，但相同的情况可能适用于所有引擎。我的每个集合的值计数为500-1000。更多或更少会显着变慢。

- Jarekczek

---

我知道这已经过去7年了，但是这个答案的问题在于它只是基于一个有根据的猜测的评论。 - Giacomo1968