MySQL "IN“运算符在(大？)值的数量
我最近一直在试验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和categories表这样简单的东西，您通常可以将这两个表连接在一起，以从某个类别获得products。在上面的示例中，您可以看到在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-ing为25？
SELECT id, name, price
FROM products
WHERE id IN (1, 2, 3, 4, ...... 25)任何建议/反馈都是非常感谢的！
回答 6
Stack Overflow用户
发布于 2011-05-07 12:42:29
一般来说，如果IN列表变得太大(对于一些定义不明确的“太大”的值，通常在100或更小的范围内)，使用连接会变得更有效，如果需要的话，可以创建一个临时表来保存数字。
如果数字是一个密集的集合(没有间隙-示例数据表明了这一点)，那么您可以使用WHERE id BETWEEN 300 AND 3000做得更好。
但是，假设集合中存在间隙，此时使用有效值列表可能更好(除非间隙的数量相对较少，在这种情况下，您可以使用：
WHERE id BETWEEN 300 AND 3000 AND id NOT BETWEEN 742 AND 836或者不管差距是什么。
Stack Overflow用户
发布于 2013-03-23 22:56:34
我已经做了一些测试，as David Fells says in his answer，它已经被很好地优化了。作为参考，我创建了一个包含1,000,000个寄存器的InnoDB表，并使用"IN“运算符对500,000个随机数进行选择，在我的MAC上只需要2.5秒；只选择even寄存器需要0.5秒。
我遇到的唯一问题是，我必须从my.cnf文件中增加max_allowed_packet参数。如果不是，则会生成一个神秘的“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.465420007706sStack Overflow用户
发布于 2016-02-10 15:23:11
您可以创建一个临时表，其中可以放置任意数量的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);https://stackoverflow.com/questions/4514697
复制相似问题