为什么某些类型的预准备查询在PHP中使用PDO而MySQL很慢？

php performance mysql pdo

2022-08-30 17:54:12

当使用带有 prepare（）/execute（）的 PDO 使用超过 10000 个键的查询时，与使用 mysqli 和预准备语句或不使用预准备语句的 PDO 执行相同查询相比，性能会降低约 10 倍。SELECT * FROM table WHERE Id IN ( .. )

更多奇怪的细节：

没有子句的更典型的 SELECT 语句即使有 100K+ 行也执行良好。例如，速度很快。WHERE Id IN( ..)SELECT * FROM table WHERE Id
在 prepare（）/execute（）完成后会发生性能下降 - 它完全在或中。在所有情况下，MySQL查询执行时间都很小 - 这不是MySQL优化的情况。PDOStatement::fetch()PDOStatement::fetchAll()
将 10K 查询拆分为 10 个具有 1K 键的查询是高性能的。
使用 mysql、带有预准备语句的 mysqli 或没有预准备语句的 PDO 是高性能的。
在下面的示例中，带/准备的 PDO 需要大约 6 秒，而其他 PDO 则需要大约 0.5 秒。
以非线性方式，您拥有的密钥越多，情况就越糟。尝试 100K 键。

示例代码：

// $imageIds is an array with 10K keys
$keyCount = count($imageIds);
$keys = implode(', ', array_fill(0, $keyCount, '?'));
$query = "SELECT * FROM images WHERE ImageID IN ({$keys})";
$stmt = $dbh->prepare($query);
$stmt->execute($imageIds);
// until now, it's been fast.  fetch() is the slow part
while ($row = $stmt->fetch()) {
    $rows[] = $row;
}

答案 1

确保您告诉PDO该值是整数而不是字符串;如果PDO将其作为字符串，则MySQL必须对值进行类型转换以进行比较。根据它是如何做到这一点的，它可能会导致MySQL避免使用索引，从而导致严重的减速。

我不完全确定这里的行为，但几年前我在Postgres上遇到了这个问题......

答案 2

示例代码中存在一些重大错误。所以更确切地说。

// $imageIds is an array with 10K keys
$keyCount = count($imageIds);
$keys = implode(', ', array_fill(0, $keyCount, '?'));
$query = "SELECT * FROM images WHERE ImageID IN ({$keys})";

到目前为止，上面的代码将提供类似这样的东西...

SELECT * FROM images WHERE ImageID IN (?, ?, ?, ?, ?, ?,...?, ?, ?, ?)

没有用于绑定的循环...应该有一个小循环，您可以在其中绑定传递给MySQL的所有参数。你从准备到执行。当正确的绑定主要是您想要的。

$stmt = $dbh->prepare($query);
$stmt->execute($imageIds);
// until now, it's been fast.  fetch() is the slow part
while ($row = $stmt->fetch()) {
    $rows[] = $row;
}

现在我有一个简单的逻辑问题关于这部分问题...

当使用带有 prepare（）/execute（）的 PDO 使用超过 10000 个键的查询时，与使用 mysqli 和预准备语句或不使用预准备语句的 PDO 执行相同查询相比，性能会降低约 10 倍。SELECT * FROM table WHERE Id IN ( .. )

如果重写相同的查询，这样您就不需要传递 10000 个键作为参数，那不是更好吗？

PDO并且在时间上没有重大差异。写得不好的查询确实如此。如果非常复杂的存储过程没有得到很好的优化，它们有时可能会变慢。MySQLi

检查另一个查询是否可以获取所需的结果。例如

创建一个名为test

create table `test` (
  `id` int(10) not null,
  `desc` varchar(255)
  ); 
insert into `test` (`id`,`desc`) values (1,'a'),(10,'a1'),(11,'a2'),(12,'a3'),(13,'a4'),(14,'a5'),(15,'a6'),(2,'ab'),(20,'ab1'),(21,'ab2'),(22,'ab3'),(23,'ab4'),(24,'ab5'),(25,'ab6');

运行这些简单的查询

select * from `test` where `id` rlike '^1$';
select * from `test` where `id` rlike '^1+';
select * from `test` where `id`=1;
select * from `test` where `id` rlike '^1.$';
select * from `test` where `id` rlike '.2$';
select * from `test` where `id` rlike '^2$';
select * from `test` where `id` rlike '.(2|3)'; // Slower
select * from `test` where `id` IN (12,13,22,23); // Faster
select * from `test` where `id` IN ('12,13,22,23'); // Wrong result
select * from `test` where `id` IN ('12','13','22','23'); // Slower

在此示例中，最后 4 个查询具有相同的结果。我认为大多数时候，如果你在SQLFiddle上检查它，你会得到与它们被赋予的标签相对应的查询时间。