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

问题描述

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

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.

更多奇怪的细节:

• 没有WHERE Id IN( ..)子句的更典型的SELECT语句即使有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.

使用1K键将10K查询拆分为10个查询很有效.

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大约需要6秒，而其他示例大约需要0.5s.

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...

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