Prepared statementはステートメントを一度定義して、それを何回も違う引数で実行するものです。
これはセキュリティを増しなおかつ効率のよい方法で、アドホックなクエリのストリングに置き換わるものです。

SELECT * FROM Country WHERE code = ?

「?」はいわゆる場所とりです。上のクエリを実行するときはこの場所に値がいります。
「?」は必須ではありません。

Prepared statementはSQL のロジックとデータを分離することでセキュリティを増加します。
ロジックとデータを分離することで、SQLインジェクション攻撃を回避することができます。
通常のクエリを扱っている場合、ユーザから受け取ったデータを処理するには注意が必要です。
これはシングル・クオート、ダブル・クオート、バックスラッシュなどの文字をエスケープする関数を
使用することに関係します。こういったことはpreparedstatementを使用する際には不必要です。
データを分離することでMySQLは自動的にこういった文字を考慮しており特別な関数を使用して
こういう文字をエスケープする必要がありません。

prepared statementでの性能向上はいくつかの異なった機能によります。
まず最初に、クエリを一度しかパースしなくてよいことです。
最初にステートメントの用意をした際、MySQLはステートメントをパースしてシンタクスをチックして、
クエリの実行の用意をします。同じクエリを何回も実行するのであれば、そのオーバヘッドは2度目からありません。
あらかじめ、パースしてあることで例えばなんどもINSERTステートメントを使うような場合、スピードが増加します。

2つ目の性能向上は新しいバイナリーのプロトコルによります。
今までのプロトコルはネットワークを介して転送する前に、全てをストリングに変換していました。
クライアントはデータをストリングに変換し(大抵の場合もとのデータより大きい)、ネットワーク
(か他の方法で)サーバに転送します。サーバはストリングをもとの正しいデータタイプに変換します。
バイナリー・プロトコルであればこのオーバーヘッドがありません。全てのタイプはそのままの形で(バイナリー形式)
で転送されます。そのためCPUの使用も削減され、ネットワークの使用も押さえることができます。

Prepared statementは上の全ての場合に有益です。しかし、アプリケーションの全てに適用すべきでもないし、
できません。最初にこれは DML (INSERT, REPLACE, UPDATE, とDELETE), CREATE TABLE, とSELECT
クエリに限られています。将来の版では他のクエリもサポートされ、prepared statementのクエリのAPIは
もっと一般的なものになります。

時として、prepared statementは普通のクエリよりも遅いときがあります。
理由はサーバーまで2往復するため、一度だけ実行されるだけのクエリであれば実行の速度が落ちます。
そのような場合はprepared statementで得られるセキュリティが性能を落としてまでも必要か考慮する
必要があります。

以上抜粋

mysql> PREPARE s1 FROM 'SELECT 1';
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> PREPARE s2 FROM 'SELECT 2';
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> execute s1;
+—+
| 1 |
+—+
| 1 |
+—+
1 row in set (0.00 sec)

mysql> execute s2;
+—+
| 2 |
+—+
| 2 |
+—+
1 row in set (0.00 sec)

※以下のキャプチャーでは、最後のPREPAREステートメントにsyntaxエラーが含まれており
s1として設定したPREPAREステートメントが無効になってしまっている状態。
prepared_statement

mysql> PREPARE s1 FROM 'SELECT 1';
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> PREPARE s2 FROM 'SELECT 2';
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> PREPARE s1 FROM 'SELECT (1+2)';
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> execute s1;
+——-+
| (1+2) |
+——-+
| 3 |
+——-+
1 row in set (0.00 sec)

mysql> execute s2;
+—+
| 2 |
+—+
| 2 |
+—+
1 row in set (0.00 sec)

mysql>

※以下のキャプチャーでは、最初のPREPAREステートメントが同じ名前で定義されているので
上書きされてs1というステートメントが最後に実行されたステートメントになっている。

prepared_statements

以下のように別名で定義すれば上書きされないので問題無い。
mysql> PREPARE P10 FROM 'SELECT (10+10)';
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> execute P10;
+———+
| (10+10) |
+———+
| 20 |
+———+
1 row in set (0.00 sec)

mysql>

PREPAREDステートメントを利用すれば、ネットワークトラフィックを減らしたり、
同じクエリーを何回も実行するなどの時間を削減する事ができます。

PREPAREDステートメントは、セッション固有のものになります。
他のユーザーのセッションや自分の他のセッションで同じ名前のステートメントを作成しても影響を受けません。
prepared_statement_session

———————————————————————
PREPARED ステートメントを利用した運用 (例:その1)
———————————————————————
ここでは、大陸と人口に変数を入れられるようにしてます。

① PREPARE STATEMENTの作成

PREPARE PS_COUNTRY_POP FROM 'SELECT Name, Population
FROM Country
WHERE Continent = ? AND Population > ?';

② 変数の値をSETしてます。 /* SELECT @C :=’Asia’ ,@P := 1000000000; でもOK */
SET @c = 'Asia', @p = 1000000000;

※SELECTで変数を設定した場合(例)
alternative

③ PREPAREステートメントの実行
EXECUTE PS_COUNTRY_POP USING @c, @p;

prepared_statement_exec

———————————————————————
PREPARED ステートメントを利用した運用 (例:その2)
———————————————————————
ここでは、大陸を変数にして国と人口を表示するためのPREPAREステートメントを利用。

① PREPARE STATEMENTの作成

PREPARE PS_Population FROM 'SELECT Name, Population
FROM Country WHERE Continent = ?';

② 変数の値をSETしてます。 /* SELECT @C :=’South America’ ; でもOK */
SET @c = 'South America';

③ PREPAREステートメントの実行 
EXECUTE PS_Population USING @c;

prepare_statement

※ 追加メモ:セッションを切って再接続してもPREPAREステートメントは利用出来ません。
execute_prepare_statement_session

※明示的にPREPAREステートメントをDEALLOCATEする方法。(セッションを切ればなくなりますが….. リソース開放の為)
① deallocate prepare PS_Population;
② drop prepare PS_Population;
drop_prepare_statement

「おまけ」
SELECT,INSERT,UPDATE,REPLACE,DELETE,SET,DO,
Many SHOW statements,CREATE TABLE statements
など殆どのDDL、DMLが設定可能

mysql> PREPARE s1 FROM 'SHOW FULL PROCESSLIST';
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> execute s1;

show_full_process

—————————————
MYSQL プリペアド・ステートメント
6.9. MySQL クエリキャッシュ

MySQL サーバ には Query Cache 機能があります。 クエリキャッシュの使用時、このキャッシュには、
SELECT クエリのテキストと、クライアントに送られたその結果が格納されます。
後でまったく同じクエリを受け取ると、サーバはそのクエリの解析と実行をもう一度繰り返す代わりに、
クエリキャッシュから結果を取り出します。
注意:クエリキャッシュから古いデータが返されることはありません。データが変更されると、
クエリキャッシュの関連するエントリがすべてフラッシュされます。

    その他利用にあたり参考になりそうなサイト

mysql の server side prepared statement って速いの?
MySQL で prepared statement を使うと query cache が効かないCommentsAdd Star
PHPのPDO::mysql。prepared statementはquery cacheを使っている!

Comments are closed.

Post Navigation