プリペアードステートメントを使用しない方が速い？

PHPのリファレンスには、プリペアードステートメントについて以下のように記載されています。

プリペアドステートメントを使用すると、この解析/コンパイル/最適化の繰り返しを避けることができます。端的に言うと、プリペアドステートメントは使用するリソースが少なくいため高速に動作するということです。

http://php.net/manual/ja/pdo.prepared-statements.php

そこで、バルクインサート文 (INSERT INTO test (num) VALUES (1), (2), ...) の実行にかかる時間を
プリペアードステートメントを使用した場合と使用しなかった場合で比較してみたところ、
意外な事に

1度に挿入する行数が1〜256行の辺りまでは双方とも同程度のパフォーマンスで、
256行を超えた頃から、プリペアードステートメントを使用しない方がパフォーマンスが上がる

という、公式のリファレンスの説明とは逆の結果になりました。

この結果について、何か知見をお持ちの方はいらっしゃいますでしょうか？

青 : プリペアードステートメントを使用しない (emulate prepare = ON)
赤 : プリペアードステートメントを使用する (emulate prepare = OFF)

なお、詳細な検証手順と結果は以下に記載しております。
http://qiita.com/kmotoki/items/7a0df427c8c2217ebbb4

こちらもご一読いただけると幸いです。
検証方法についてのご指摘なども、大歓迎です。

ちなみにstack overflow でも同様の質問があり、こちらでは
「パフォーマンスに差は出ない」
という結論になっていました。
http://stackoverflow.com/questions/14166798/mysql-pdo-prepared-faster-than-query-thats-what-this-simple-test-shows

行動規範の内容に同意します

回答4件

公式の記述のほうは、同じSQLを繰り返し使うならプリペアドステートメントが有利と言うものだと思います。
SQLはスクリプトが渡されるとオプティマイザがそれを解釈して最適な実行モジュールを作成します。
作成された実行モジュールはキャッシュに一定時間残ります。
従って、同じSQLを短期間に多数回使うなら(コンパイルも省けるし次々作成されるキャッシュがメモリを圧迫することも無いので)このキャッシュを利用したほうが有利です。
プリペアならデータ部分がマスクされているので、データが変わっても他が同じなら同じSQLとみなせキャッシュがあれば利用可能です。

検証のほうは1回のクエリで多くのデータを入れようとしている(?)ように見えます。

1回のクエリで挿入する行数を次第に増やしていく。

このやり方ではプリペアを指定したとしても毎回SQLが変わるので、実行モジュールのコンパイルも都度行われると思います。
そうするとキャッシュされた実行モジュールに値を渡す操作が(プリペアを使わない場合に比べて）余分に増えるので、それだけでもコストが高くなると思います。

こうではなく、1行づつインサートするSQLを65000回発行するなら、プリペアの方が早くなりそうに思います。

投稿2015/12/12 18:55

編集2015/12/12 18:57

hirohiro

総合スコア2068

KiyoshiMotoki

2015/12/12 19:42

hirohiro様、ご回答ありがとうございます。 > 検証のほうは1回のクエリで多くのデータを入れようとしている(?)ように見えます。説明が足りず、申し訳ございません。検証結果のグラフの見方ですが、例えばX軸が"2"の部分は　1. 一度に2行を挿入するインサート文（INSERT INTO test (num) VALUES (?), (?)）を続けざまに37768回実行し、かかった時間を計測する。　2. "1."を十回繰り返し、かかった時間の平均値を求める。　上の手順をプリペアードステートメントを使用した場合、しない場合それぞれについて実施した結果、双方とも約5秒だった。という意味となります。もちろん、　$dbh->prepare(); は、1回の試行に1度しか呼び出しておりません。 > 1行づつインサートするSQLを65000回発行するなら、プリペアの方が早くなりそうに思います。これを試行しなかったのは、片手落ちでしたね(^^; 後ほど1行ずつインサートした場合の結果もご報告させていただきます。

hirohiro

2015/12/13 05:35 編集

すみません。コードはちゃんと追いかけていませんでした。 1.　一度に挿入するデータが増えると極端に性能が落ちる点に関してこれは投稿コメントにも書いたように、実行モジュールの再利用回数が減って、多量のデータをマスクにマッチさせる手間が増えたからだと推測できます。(証明は難しいと思います) 2.　試行回数が多い段階で互角で、中盤にプリエアが有利これは、SQL文が単純なためでは無いかと推測できます。検証環境では512や1024時に以下のコストバランスが良かったのではないでしょうか？・オプテイマイザの評価時間やコンパイルコスト　->　1回のみ実行：プリペアが有利・プリペアにデータを挿入するコスト　->　プリペアでのみ発生：プリペアが不利それで、一度に挿入するデータが増えると処理付加が(グラフを見る限り)指数関数的に増えるのかも知れませんね。メモリや転送量などハードウェア的なものが関係しているのかも知れません。(これも検証コードでの証明は難しいと思います。) > 1行づつインサートするSQLを65000回発行するなら、プリペアの方が早くなりそうに思います。なので、これはやっても2つを37000回より悪い結果になりそうですね。すみません。　アプリケーションにおいて、何万行もインサートしたり、プリペアに何千データも挿入するSQLは稀だと思います。それよりも、条件を変えながらの複数検索や、同じような検索結果で角度を変えながらの分析グラフの表示などが多いのでは無いでしょうか？その場合プリペアの特性を理解してうまくプログラムすればコストカットもできそうに思います。ただprepareってコストカットのためというより、セキュリティ強化の手段としてのイメージが個人的には強いです。どうせ利用するので、使わないシーン自体がそもそも少ないような気がします。

KiyoshiMotoki

2015/12/13 11:30

hirohiro様取り急ぎ1行ずつインサートするSQLを65536回実行した結果をご報告させていただきます。プリペアードステートメントを... 　・使用しない = 約9.86秒　・使用する = 約9.39秒と、微妙ながら、プリペアードステートメントを使用した方が良いパフォーマンスが得られました。 ※質問欄の画像も、上の結果を追加したものに更新させていただきました。 > 1.　一度に挿入するデータが増えると極端に性能が落ちる点に関して > これは投稿コメントにも書いたように、実行モジュールの再利用回数が減って、多量のデータをマスクにマッチさせる手間が増えたからだと推測できます。これは、おっしゃる通りかもしれません。グラフの右側に行くほどクエリの再利用回数が減っているため、プリペアによるオーバーヘッドがパフォーマンスに大きく影響している可能性は考えられます。クエリの再利用回数を次第に増やしていき、パフォーマンスの推移を見ると何か分かるかもしれませんね。 > 検証環境では512や1024時に以下のコストバランスが良かったのではないでしょうか？プリペアードステートメントのコストバランスというよりは、MySQL自体、あるいは私の検証環境において最もパフォーマンスを発揮できるクエリと試行回数の組み合わせが、この辺りだったのかもしれません。プリペアードステートメントを使用する・しない双方とも、"512や1024時"に最も優れたパフォーマンスを発揮しておりますので。 > アプリケーションにおいて、何万行もインサートしたり、プリペアに何千データも挿入するSQLは稀だと思います。例えば、帳票データを扱うバッチ処理などではあり得るかな?と私は思います。 > それよりも、条件を変えながらの複数検索や、同じような検索結果で角度を変えながらの分析グラフの表示などが多いのでは無いでしょうか？ > その場合プリペアの特性を理解してうまくプログラムすればコストカットもできそうに思います。むしろ、私にとってはこちらの方が目から鱗でしたw 例えば、非常に頻繁に使用するクエリのプリペアードステートメントを（コネクションプーリングのようなイメージで）プールしておくことができれば、アプリケーションの性能を上げる事ができるかもしれませんね。引き続き検証を続けておりますので、進捗がありましたら、ご報告させていただきます。

takotakot

2015/12/15 13:02

> むしろ、私にとってはこちらの方が目から鱗でしたw ストアドプロシージャというものもあります。ご存じなければ調べてみると良いと思います。

行動規範の内容に同意します

気になる点を上げます。

prepareをクエリ発行の度に行っているようですが、
これは最初に一回だけで良いのでは？
内部動作は分かっていませんが、prepareの度にDBアクセスされて、
プリペアドステートメントの場合が不利な気がします。

マニュアルでも以下の方法で書かれています。

<?php
$stmt = $dbh->prepare("INSERT INTO REGISTRY (name, value) VALUES (:name, :value)");
$stmt->bindParam(':name', $name);
$stmt->bindParam(':value', $value);

// 行を挿入します
$name = 'one';
$value = 1;
$stmt->execute();

// パラメータを変更し、別の行を挿入します
$name = 'two';
$value = 2;
$stmt->execute();
?>

http://php.net/manual/ja/pdo.prepared-statements.php

また、理由が分からないのであれば、
mysqli_get_client_statsなどを使って、統計を取ってみては？

参考情報です。

KiyoshiMotokiさんの結果を、こちらで散布図にしてみたところ、
以下の様に、顕著にO(n)の傾向が見えます。
どうも、おかしいような気がします。

また、性能検証には向かない環境ですが、Windows 7上のOracle Virtualboxを
DBサーバとして、Windows 7のホストから負荷をかけてみました。
環境は以下の通りです。

APサーバ（ホスト）
OS : Windows 7
CPU : Intel(R) Core(TM) i7-4700MQ CPU @ 2.40GHz
Memory : 16GB
スクリプト : PHP 5.6.8
DB接続ドライバ : PDO
Oracle Virtual Box 5.0.8

DBサーバ(ゲスト)
OS : Ubuntu 14.04.3
Memory : 2GB
DB : MySQL 5.5.46

グラフ化した結果は以下の通りです。

顕著な差は出ませんでした。

mysqli_get_client_statsの情報(値が0の項目は省きました)


    [bytes_sent] => 11823835
    [bytes_received] => 5191393
    [packets_sent] => 131488
    [packets_received] => 131278
    [protocol_overhead_in] => 525112
    [protocol_overhead_out] => 525952
    [bytes_received_rset_header_packet] => 135
    [bytes_received_rset_row_packet] => 3418420
    [bytes_received_prepare_response_packet] => 1769418
    [packets_sent_command] => 65714
    [packets_received_rset_header] => 15
    [packets_received_rset_row] => 65654
    [packets_received_prepare_response] => 65534
    [non_result_set_queries] => 65654
    [ps_prepared_once_executed] => 1
    [rows_affected_normal] => 491520
    [rows_affected_ps] => 491520
    [command_buffer_too_small] => 196
    [connect_success] => 30
    [connect_failure] => 2
    [active_connections] => 18446744073709551584
    [explicit_close] => 30
    [explicit_free_result] => 15
    [explicit_stmt_close] => 15
    [com_quit] => 30
    [com_query] => 32887
    [com_stmt_prepare] => 15
    [com_stmt_execute] => 32767
    [com_stmt_close] => 15

(2015/12/15 21:02 追記)
バイナリログを出力する様になっていたりしませんか？

Bug #67676 prepared statement is very slow while binlog was enabled

という不具合があるようです。
試しに、こちらの環境でバイナリログを出力する様にしたところ、
以下の結果となり、KiyoshiMotokiさんの結果に少し近くなりました。

mysqli_get_client_statsの情報(実行前後の差分)

    [bytes_sent]  =>  47295406
    [bytes_received]  =>  20765932
    [packets_sent]  =>  525952
    [packets_received]  =>  525112
    [protocol_overhead_in]  =>  2100448
    [protocol_overhead_out]  =>  2103808
    [bytes_received_rset_header_packet]  =>  540
    [bytes_received_rset_row_packet]  =>  13673680
    [bytes_received_prepare_response_packet]  =>  7077672
    [packets_sent_command]  =>  262856
    [packets_received_rset_header]  =>  60
    [packets_received_rset_row]  =>  262616
    [packets_received_prepare_response]  =>  262136
    [non_result_set_queries]  =>  262616
    [ps_prepared_once_executed]  =>  4
    [rows_affected_normal]  =>  1966080
    [rows_affected_ps]  =>  1966080
    [command_buffer_too_small]  =>  784
    [connect_success]  =>  120
    [connect_failure]  =>  5
    [active_connections]  =>  18446744073709551491
    [explicit_close]  =>  120
    [explicit_free_result]  =>  60
    [explicit_stmt_close]  =>  60
    [com_quit]  =>  120
    [com_query]  =>  131548
    [com_stmt_prepare]  =>  60
    [com_stmt_execute]  =>  131068
    [com_stmt_close]  =>  60

投稿2015/12/13 00:50

編集2015/12/15 12:04

eripong

総合スコア1546

eripong

2015/12/13 00:55

すみません。 prepareを毎回行っているわけではありませんでしたね。失礼しました。統計は取ってみたらよいのではと思います。

KiyoshiMotoki

2015/12/13 11:28

eripong様 > 理由が分からないのであれば、 mysqli_get_client_statsなどを使って、統計を取ってみては？ご提案ありがとうございます。統計情報も確認してみたいと思います。引き続き検証を続けておりますので、進捗がありましたら、ご報告させていただきます。

行動規範の内容に同意します

hirohiro 様の「公式の記述のほうは、同じSQLを繰り返し使うならプリペアドステートメントが有利と言うものだと思います。」という回答が、ドキュメント本来の意図なのは、間違いないと思います。

時間がないので検証しておりませんが、グラフが左右対称に近いことも考えると
bind する数が過剰なため bindValue 自体に時間がかかっている
のではないかと思います。

bind された値はどう管理されるか…ということを考えると、MySQL 側では、

bind される値分のメモリを確保(prepare の時点で、ループ外で行われている可能性が高い)
bind 1回ごとに「格納すべき場所(メモリのどこか)を探し、そこに値を格納」
execute の時に、値を読み込んでいって解釈

していそうです。そのうち、2番目のところに時間がかかりそうですね。
O(n) ということなら、ここの探索部分が線形になっている…とかではないでしょうか。Oracle は考慮され、最適化されているのでしょう。

bind する値を「ループで最初の1つの値だけ変わる」や「最後の1つの値だけ変わる」場合についての計測を行えば、execute と bindValue のどちらにより時間がとられていそうか、なんとなくわかるかもしれません。(MySQL のコードを読むのが一番だとは思いますが)

bind する $i の値について考察すると理由が読めてくると思います。

PHP 側で、(100), (47), ... という「文字列を生成」した場合のパフォーマンスも見てみたら面白いかもしれませんね。

話は変わりますが、追試内容は「質問文に追記」した方が見易くて良いのではないかと思います。

投稿2015/12/15 07:38

takotakot

総合スコア1111

eripong

2015/12/15 12:18 編集

bindに時間がかかる可能性は、確かにありそうです。

行動規範の内容に同意します

ATTR_EMULATE_PREPARES=1はPDO側でpreparedステートメントをエミュレートするのでbindがたくさんあるとオーバーヘッドがえらい事になりそうですよね。
バルクで大量投入するより１件づつ大量インサートか、ある程度複雑な参照系で試せば、
ATTR_EMULATE_PREPARES=1の方が性能出そうな予感がします。

投稿2015/12/13 11:17

ooi

総合スコア43

KiyoshiMotoki

2015/12/13 11:39

ooi様、ご回答ありがとうございます。 > バルクで大量投入するより１件づつ大量インサートか、ある程度複雑な参照系で試せば、 ATTR_EMULATE_PREPARES=1の方が性能出そうな予感がします。ちょうど、追加検証の結果を質問欄に反映させていただいたところでしたが、 1件ずつインサートする場合は　ATTR_EMULATE_PREPARES=0 の方が微妙ながら、良いパフォーマンスを得られました。今のところ、クエリを再利用する回数が少なければ　ATTR_EMULATE_PREPARES=1 の方が有利で、回数を増やすに従って　ATTR_EMULATE_PREPARES=0 の方が性能を発揮していくのではないかな?と予想しております。引き続き検証を続けておりますので、進捗がありましたら、ご報告させていただきます。

ooi

2015/12/13 12:19

あ、なるほど、勘違いしてました。確かに右に行くほどmysql自体のプリペアードステートメントを使う方が遅くなるのは、再利用される回数が減るので分かりますね。左側で性能がほとんど変わらないのは何とも残念ですが、単純なSQLだとPDOでサニタイズした生SQLを送信するのと、MySQLがプリペアードステートメントを利用するのはほとんど変わらないレベルの処理なのかもしれないですね。　　 MySQL5.6とか5.7だとどうなんでしょう、という興味もあります。進捗楽しみにしております。

行動規範の内容に同意します

あなたの回答