概要

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回のクエリで使用できるプリペアードステートメントの数には、限界があるらしい。

プリペアードステートメントのパフォーマンス測定

概要

検証方法

手順

検証環境

検証結果