概要
PHPのリファレンスには、プリペアードステートメントについて以下のように記載されている。
同じクエリを異なったパラメータで何度も実行すると、アプリケーションの 動作は目に見えて遅くなるでしょう。 プリペアドステートメントを使用すると、この 解析/コンパイル/最適化 の繰り返しを避けることができます。 端的に言うと、プリペアドステートメントは使用するリソースが少なくいため 高速に動作するということです。
そこで、プリペアードステートメントを使用した場合と使用しなかった場合のパフォーマンスを比較してみる事にした。
検証方法
手順
検証用テーブルに、ランダムな値を持つレコードを65536行(2^16行)挿入する。
クエリはバルクインサート
(INSERT INTO test (num) VALUES (1), (2), ...)
を使用し、1回のクエリで挿入する行数を
- 2行
- 4行
- 8行
- ...
と、次第に増やしていく。
以上の操作をプリペアードステートメントを使用する場合と使用しない場合の双方で10回ずつ試行し、かかった時間の平均値を比較する。
なお、クエリを実行するスクリプトとDBは別々のサーバに配置されており、
両サーバはローカルネットワークで接続されている。
検証環境
- サーバ（AP, DBともに同スペック）
- OS : CentOS 5.8
- CPU : Intel(R) Xeon(R) CPU X5675 @ 3.07GHz
- Memory : 1026080 kB
- DB : MySQL 5.5
- スクリプト : PHP 5.3
- DB接続ドライバ : PDO
検証用テーブル
CREATE TABLE `test` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`num` int(10) unsigned NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
PHPのソースコード
<?php
error_reporting(E_ALL);
define ('BASE', 2);
define ('MAX_EXP', 16);
define ('NUMBER_OF_RECORD', pow(BASE, MAX_EXP));
define ('TEST_COUNT', 10);
for ($exp = 1; $exp <= MAX_EXP; $exp++) {
$chunkSize = pow(BASE, $exp);
$emulatePrepares = test($chunkSize, true);
$usePrepares = test($chunkSize, false);
print "$chunkSize\t$emulatePrepares\t$usePrepares\n";
}
function test($chunkSize, $emulatePrepares) {
$dbh = createDBHandler($emulatePrepares);
for ($i = 0; $i < TEST_COUNT; $i++) {
$numbers = createRandomNumbers(NUMBER_OF_RECORD);
truncate($dbh);
$result[] = insertCollectively($dbh, $numbers, $chunkSize);
}
return array_sum($result) / TEST_COUNT;
}
/**
* 配列$numbersを、$chunkSize行ずつインサートする。
*
* $chunkSizeは、$numbersを割り切る数でなければならない。
*/
function insertCollectively(PDO $dbh, array $numbers, $chunkSize) {
$placeHolders = array_pad(array(), $chunkSize, '(?)');
$valuesClause = implode(', ', $placeHolders);
$stmt = $dbh->prepare('INSERT INTO test (num) VALUES ' . $valuesClause);
$before = microtime(true);
$dbh->beginTransaction();
foreach (array_chunk($numbers, $chunkSize) as $chunkedNumbers) {
$i = 1;
foreach ($chunkedNumbers as $number) {
$stmt->bindValue($i++, $number, PDO::PARAM_INT);
}
$stmt->execute();
}
$dbh->commit();
return microtime(true) - $before;
}
function truncate($dbh) {
$dbh->exec('TRUNCATE TABLE test');
}
function createDBHandler($emulatePrepares) {
$dbh = new PDO('mysql:XXXXX', 'XXXXX', 'XXXXX');
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$dbh->setAttribute(PDO::ATTR_AUTOCOMMIT, false);
$dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES, $emulatePrepares);
return $dbh;
}
function createRandomNumbers($count) {
$numbers = array();
for ($i = 0; $i < $count; $i++) {
$numbers[] = mt_rand();
}
return $numbers;
}
?>
検証結果
| bulk count | emulate prepare | use prepare |
|---|---|---|
| 2 | 5.053875351 | 5.017175078 |
| 4 | 2.835401201 | 2.725035954 |
| 8 | 1.637799835 | 1.598171449 |
| 16 | 1.020874143 | 0.973363829 |
| 32 | 0.769477415 | 0.734067106 |
| 64 | 0.645589924 | 0.63260026 |
| 128 | 0.596083951 | 0.568830323 |
| 256 | 0.556294942 | 0.562757993 |
| 512 | 0.52461729 | 0.583038902 |
| 1024 | 0.532318234 | 0.696268725 |
| 2048 | 0.543481255 | 0.841279483 |
| 4096 | 0.560284638 | 1.188867807 |
| 8192 | 0.547622371 | 1.861706996 |
| 16384 | 0.539314747 | 3.190056157 |
| 32768 | 0.535651708 | 5.886143303 |
意外な事に、
- 1度に挿入する行数が2〜256行の辺りまでは双方とも同程度のパフォーマンスで、
- 256行を超えた頃から、プリペアードステートメントを使用しない方がパフォーマンスが上がる
という、公式のリファレンスの説明とは逆の結果になった。
理由は分からない。
ちなみに、1度に65536行を挿入しようとすると、以下のようなエラーが発生した。
Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[HY000]: General error: 1390 Prepared statement contains too many placeholders' in /tmp/test.php:36
Stack trace:
#0 /tmp/test.php(36): PDO->prepare('INSERT INTO tes...')
#1 /tmp/test.php(22): insertCollectively(Object(PDO), Array, 65536)
#2 /tmp/test.php(12): test(65536, false)
#3 {main}
thrown in /tmp/test.php on line 36
1回のクエリで使用できるプリペアードステートメントの数には、限界があるらしい。