为什么在 PHP 和 MySQL 中使用 PDO 的某些类型的准备查询很慢? [英] Why are certain types of prepared queries using PDO in PHP with MySQL slow?

问题描述

当使用 SELECT * FROM table WHERE Id IN ( .. ) 查询超过 10000 个键时，使用 PDO 和 prepare()/execute()，性能下降约 10 倍使用带有准备好的语句的 mysqli 或不使用准备好的语句的 PDO 进行查询.

When using SELECT * FROM table WHERE Id IN ( .. ) queries with more than 10000 keys using PDO with prepare()/execute(), the performance degrades ~10X more than doing the same query using mysqli with prepared statements or PDO without using prepared statements.

更多奇怪的细节:

• 更典型的 SELECT 语句没有 WHERE Id IN( ..) 子句，即使有 100K+ 行也能很好地执行.SELECT * FROM table WHERE Id 例如很快.

• More typical SELECT statements that don't have the WHERE Id IN( ..) clause perform fine even with 100K+ rows. SELECT * FROM table WHERE Id for example is fast.

性能下降发生在 prepare()/execute() 完成之后 - 它完全在 PDOStatement::fetch() 或 PDOStatement::fetchAll() 中>.在所有情况下，MySQL 查询执行时间都很短 - 这不是 MySQL 优化的情况.

The performance degradation occurs after prepare()/execute() is complete - it's entirely in PDOStatement::fetch() or PDOStatement::fetchAll(). The MySQL query execution time is tiny in all cases - this isn't a case of a MySQL optimization.

将 10K 查询拆分为 10 个具有 1K 键的查询是高效的.

Splitting the 10K query into 10 queries with 1K keys is performant.

使用 mysql、带有准备语句的 mysqli 或不带准备语句的 PDO 是高性能的.

Using mysql, mysqli with prepared statements, or PDO without prepared statements is performant.

PDO w/prepared 在下面的例子中需要约 6 秒，而其他需要约 0.5 秒.

PDO w/prepared takes ~6 seconds on the example below, while the others take ~0.5s.

您拥有的密钥越多，非线性就会变得更糟.尝试 10 万个密钥.

It gets worse in a non-linear fashion the more keys you have. Try 100K keys.

示例代码:

// $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;
}

推荐答案

确保您告诉 PDO 该值是整数而不是字符串；如果 PDO 把它作为一个字符串，那么 MySQL 将不得不对这些值进行类型转换以进行比较.根据它的处理方式，它可能会导致 MySQL 避免使用索引，从而导致严重的减速.

Make sure you're telling PDO that the value is an integer not a string; if PDO puts it as a string, then MySQL will have to typecast the values for comparison. Depending on how it goes about this, it could cause major slowdowns by causing MySQL to avoid using an index.

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

I'm not completely sure about the behaviour here, but I have had this problem with Postgres a few years back...

这篇关于为什么在 PHP 和 MySQL 中使用 PDO 的某些类型的准备查询很慢?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！