問題描述
當使用 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.
更多奇怪的細節(jié):
更典型的 SELECT 語句沒有
WHERE Id IN( ..)子句，即使有 100K+ 行也能很好地執(zhí)行.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 Idfor example is fast.
性能下降發(fā)生在 prepare()/execute() 完成之后 - 它完全在 PDOStatement::fetch() 或 PDOStatement::fetchAll() 中>.在所有情況下，MySQL 查詢執(zhí)行時間都很短 - 這不是 MySQL 優(yōu)化的情況.
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 該值是整數(shù)而不是字符串；如果 PDO 把它作為一個字符串，那么 MySQL 將不得不對這些值進行類型轉(zhuǎn)換以進行比較.根據(jù)它的處理方式，它可能會導(dǎo)致 MySQL 避免使用索引，從而導(dǎo)致嚴重的減速.
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...
這篇關(guān)于為什么在 PHP 和 MySQL 中使用 PDO 的某些類型的準備查詢很慢?的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！