MySQL“in”运算符在(大?)数值上的性能

内容来源于 Stack Overflow,并遵循CC BY-SA 3.0许可协议进行翻译与使用

  • 回答 (2)
  • 关注 (0)
  • 查看 (77)

最近,我一直在尝试Redis和MongoDB,似乎经常会有这样的情况,可以存储一系列身份证无论是在MongoDB还是Redis。我会坚持这个问题,因为我问的是MySQL接线员。

我想知道列出大量(300-3000)的身份证在IN操作符中,它看起来如下所示:

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

想象一件简单的事情产品类别表,通常可以将这些表连接在一起以获得产品从某物范畴...。在上面的示例中,可以看到在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和LIMIT-从查询中将其转换为25?

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

任何建议/反馈都是非常感谢的!

提问于
用户回答回答于

一般来说,如果IN列表太大(对于一些定义不清的值“过大”(通常在100或更小的范围内)),则使用联接会变得更有效,如果需要的话创建一个临时表来保存数字。

如果数字是一个密集的集合(样本数据表明没有间隙),那么可以使用更好的方法。WHERE id BETWEEN 300 AND 3000...。但是,假设集合中存在空白,在这一点上,最好还是采用有效值列表(除非空白相对较少,在这种情况下,可以使用:WHERE id BETWEEN 300 AND 3000 AND id NOT BETWEEN 742 AND 836或者不管是什么缺口。

用户回答回答于

我一直在做一些测试,它是很好的优化。作为参考,我创建了一个带有1000000寄存器的InnoDB表,并使用带有500000个随机数的“IN”操作符进行选择,在我的MAC中只需要2,5s。(仅选择偶数寄存器需要0,5s)。

我唯一的问题是我不得不增加_允许_来自my.cnf文件的数据包参数。如果没有,就会产生一个神秘的“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

扫码关注云+社区