MySQL を PDO で使うときは ATTR_EMULATE_PREPARES を設定しよう
「DSAS for Social を支える技術」 というネタでadvent calendar に挑戦します。 methane です。
PDOで MySQL を使うときは、みなさん $stmt = $con->prepare("..."); して $stmt->execute($values); とかしてプリペアドステートメントを利用されていると思います。 実は、このプリペアドステートメント、パフォーマンス的にはあまり良くありません。1つのクエリを実行するために、プレースホルダ付きのクエリを投げた後に、それに値をバインドして実行するコマンドを投げるので、1回のクエリを実行するのに2往復の通信が必要になるのです。
プリペアドステートメントにはパフォーマンスの利点（同じクエリを何度も発行するときにDBサーバーがクエリの解析を繰り返さないでもすむ）というものと、SQLインジェクション対策になる（正しくSQLを実行できる）という利点がありますが、特にPHPではWebアクセスの度にプリペアし直すのでパフォーマンスの利点は殆ど無くて、基本的には正しくSQLを実行するために使ってることが多いのではないでしょうか？
変数を含むSQLを正しく実行するのに、別にプリペアドステートメントを使う必要はありません。変数の値を、正しくエスケープ処理して、SQLに埋め込めば良いのです。そして、 PDO::ATTR_EMULATED_PREPARE を利用すると、PDOのプリペアドステートメントを普通に使うだけで、PDO内部で正しくエスケープされたSQLを構築してMySQLに投げてくれるので、クエリ実行時に発行するコマンドが1つですむようになり、DBサーバーのCPU,ネットワーク帯域、パケット数を全て削減できます。この設定をするには、PDOのオブジェクトに対して次の1行を実行するだけです。php-5.2以降、デフォルトので有効です。以下の設定は不要です
$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, 1);
ちょうどこの設定が漏れていて、S-in後にこの設定を反映させた事があったので、その時の Ganglia のグラフを紹介しておきます。S-in直後で無駄なクエリをたくさん実行している状態だったので、理論上最大値である 1/2 のパケット数削減ができています。（全体的に右肩上がりなのは、どんどんアクセスが増えている時間帯だからです）
DBサーバーのネットワークのパケット数
DBサーバーのCPU使用率
注意しないといけない点として、この設定を使うと一部挙動が変わる可能性があります。たとえば、 "...LIMIT ?" というクエリに ->execute(array(..., 3)); とすると、 "...LIMIT '3'" (シングルクォートに注目) に展開されてしまってSQLが不正になってしまうので、型を指定して bindParam() を利用するなり、整数型と判っている部分だけ通常の ($limit が正の整数として "...LIMIT $limit" のような) 文字列処理をする必要があります。
MySQLを骨までしゃぶるためには必須の設定なので、 PHP+MySQLユーザーはぜひ活用してください。
追記
最近のバージョンの pdo_mysql はデフォルトで ATTR_EMULATE_PREPARES が有効なようです。DSAS for Social でも新しいバージョンのphpを使用しているはずなのですが、サーバーサイドで本当のprepared statementが実行されていたので driver_options に無効にする指定をしてしまっていたなどの問題があったようです。
また、この pdo 内部でprepared statement のエミュレートを行う挙動は、文字エンコーディングの設定によってはとエスケープの処理が正しく行われなくなりSQLインジェクションが可能になることがあります。 KLabのソーシャルゲーム案件では、内部では文字列を完全に UTF-8 化しており、MySQLのデフォルトエンコーディングや接続時の設定も utf8 にしているので問題ありませんが、特にSJISを利用されている方はご注意下さい。 (参考: ぼくがPDOを採用しなかったわけ(Shift_JISによるSQLインジェクション))
