MySQL“IN”运算符在（大量？）值上的性能

Question

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

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

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

想象一下像 products 这样简单的东西您通常可以将它们连接在一起以获取特定类别中的产品。在上面的示例中，您可以看到，在 Redis 中的给定类别 ( category:4:product_ids ) 下，我返回了 id 为 4 的类别中的所有产品 id，并将它们放在上面的 中IN 运算符内的 SELECT 查询。

这有多高效？

这是“视情况而定”的情况吗？或者是否有一个具体的“这是（不）可接受的”或“快”或“慢”，或者我应该添加一个LIMIT 25，还是没有帮助？

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

或者我应该修剪 Redis 返回的产品 id 数组，将其限制为 25，并且仅向查询添加 25 个 id，而不是 3000，然后从查询内部将其限制为 25？

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

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

Answer 1

一般来说，如果 IN 列表变得太大（对于某些定义不明确的“太大”值，通常在 100 或更小范围内），使用连接会变得更有效，如果需要的话创建一​​个临时表来保存数字。

如果数字是密集的集合（没有间隙 - 示例数据表明），那么您可以使用 WHERE id BETWEEN 300 AND 3000 做得更好。

但是，假设集合中存在间隙，此时最好使用有效值列表（除非间隙数量相对较少，在这种情况下您可以使用：

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

或者无论间隙是什么。

Answer 2

我一直在做一些测试，正如 David Fells 在他的回答中所说，它得到了很好的优化。作为参考，我创建了一个包含 1,000,000 个寄存器的 InnoDB 表，并使用“IN”运算符对 500,000 个随机数进行选择，在我的 MAC 上只需要 2.5 秒；仅选择偶数寄存器需要 0.5 秒。

我遇到的唯一问题是我必须增加 my.cnf 文件中的 max_allowed_pa​​cket 参数。如果没有，就会生成一个神秘的“MYSQL 已消失”错误。

这是我用来进行测试的 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

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);

Answer 4

实际上，在大量记录列表上使用带有大量参数集的 IN 会很慢。

在我最近解决的案例中，我有两个 where 子句，一个有 2,50 个参数，另一个有 3,500 个参数，查询包含 4000 万条记录的表。

我使用标准 WHERE IN 进行查询花了 5 分钟。通过对 IN 语句使用子查询（将参数放入其自己的索引表中），我将查询时间缩短到了两秒。

根据我的经验，曾在 MySQL 和 Oracle 工作过。

Answer 5

IN 很好，而且优化得很好。确保在索引字段上使用它就可以了。

它在功能上等同于：

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

就数据库引擎而言。

编辑：请注意这个答案是在 2011 年写的，并查看这个答案的评论，讨论最新的 MySQL 功能。

Answer 6

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

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