パラメータ化クエリ

この節では、大抵のSQLテキストでは扱われないトピックである、 パラメータ化クエリとバインド パラメータを取り上げます。

動的パラメータ、あるいはバインド変数とも呼ばれる、バインドパラメータとは、データベースにデータを渡すための方法です。値を SQL文に直接埋め込む代わりに、?や:name、 @nameといったプレースホルダを使い、別のAPIを呼び出して実際の値を渡します。

1度限りのSQL文に、直接値を書き込んで実行するのには何の問題もありませんが、プログラム内でのバインドパラメータには2つの利点が あります。

セキュリティ

バインドパラメータは、SQL インジェクションを防ぐ最適な方法です。

パフォーマンス

SQL ServerやOracleのように、実行計画をキャッシュするデータ ベースでは、複数回実行される同じ文に対して同じ実行計画を再利用することができます。これにより、実行計画を作成し直すコストを 節約できますが、SQL文は完全に同じでなければ なりません。SQL文に違う値を入れてしまうと、データベースはそれを異なる文だと判断してしまい、実行計画を再作成します。

バインドパラメータを使う場合は、SQL文には実際の値を書かず、 代わりにプレースホルダを記入します。この方法だと、値が違っても文自体に変更はありません。

必然的に、以下の例のように実際の値によって得られるデータの量が異なる 場合などは例外があります。以下は、99行が得られる場合の例です。

SELECT first_name, last_name
  FROM employees
 WHERE subsidiary_id = 20

上の例のように、少人数の子会社の場合はインデックスをたどることで高速になりますが、大人数の子会社の場合、インデックスを使うよりも TABLE ACCESS FULLの方が高速になります。以下は、1000行が得られる場合の例です。

SELECT first_name, last_name
  FROM employees
 WHERE subsidiary_id = 30

この例では、SUBSIDIARY_IDのヒストグラムが役に立って くれます。オプティマイザが、SQL文にある子会社のIDがどの程度の頻度で現れるかを判断するために、ヒストグラムを使います。その結果、双方のクエリで 異なる行数の見積もりが得られたのです。

これに続くコスト計算では、その違いにより異なるコスト値を算出しています。 オプティマイザは、最終的にコスト値が最も低い実行計画を採用します。少人数の子会社の場合は、インデックスを使う方ということになります。

TABLE ACCESS BY INDEX ROWIDのコストは、行数の見積もりに よって非常に敏感に変わります。10倍の行を得る時には、コスト値はその倍数で増えます。インデックスを使用する場合の全体のコストは、フルテーブル スキャンよりも大きくなります。そのため、大人数の子会社に対しては、オプティマイザは別の実行計画を選択するのです。

バインドパラメータを使う際は、与えられた値がどの程度の 頻度で出現するか明確な情報は持っていません。全て同じように分布していて、同じ行数の見積もり、同じコスト値が得られると推測するだけです。そのため、 いつも同じ実行計画を使うことになります。

オプティマイザとコンパイラを比較した時、バインド変数は、プログラムの 変数のようなものです。一方で、値をSQL文に直接書き込んだ場合は、定数にあたります。データベースは、コンパイラが定数式をコンパイル中に評価する ように、データベースはSQL文中の値を最適化の際に使います。簡単に言うと、 コンパイラは変数のランタイム値が分からないのと同じように、オプティマイザはバインドパラメータの中身は分かりません。

そう考えると、バインドパラメータを使わないとオプティマイザは常に最適な実行計画を選択できるようになると言っている一方で、バインド パラメータを使うとパフォーマンスが向上するというのは、矛盾しています。しかし、これは程度問題です。あらゆる実行計画のパターンを生成しては 評価するのは、最終的には同じ結果を得るわけですから、割に合わない大変なことです。

それぞれのクエリに特化した実行計画を立てるべきか、一般的な実行計画を 立てるべきかを決めるのは、データベースにとってはジレンマです。常に最適な実行計画を得るために、取り得る全てのパターンについて評価すべきか、 最適とまでは言えない実行計画を使ってしまうリスクを許容してでも、最適化のオーバーヘッドを抑え、可能な限りキャッシュした実行計画を使うか。 この板ばさみは、完全な最適化をしてみないと、それが違う実行結果を出すことになるかどうか、データベースは分からないことが原因です。 データベースベンダは、ヒューリスティックな方法でこのジレンマを解決しようとしていますが、まだ限定的な効果しか得られていません。

開発者としては、このジレンマを解決するよう、バインド パラメータを使えばよいのです。つまり、間違いなく 実行計画に影響を与えるものを除いて、バインドパラメータを使うべきです。

「実行予定」と「完了済」のように不規則に分布したステータスは、ここではよい例です。「完了済」のエントリ数は、「実行予定」の数と比べると けた違いに多いでしょう。このような場合、インデックスを使うのは、「実行予定」のエントリを検索する時だけ意味があります。パーティショニングはもう一つの例です。テーブルを分割して、インデックスが複数のストレージ領域に渡って作成された 状態になったとしましょう。与えられた値によって、どのパーティションをスキャンするかが変わります。LIKEを使ったクエリの パフォーマンスは、次節で見るように バインドパラメータに影響される可能性があります。

以下は、各言語でのバインドパラメータの使い方のコードスニペットです。

疑問符(?)は、SQL標準で唯一定義されている、 プレースホルダの文字です。疑問符は位置パラメータ、つまり、左から右に向って番号が付けられます。値を特定の疑問符に割り当てたい場合、 その番号を指定します。しかしこれは、プレースホルダを追加したり削除したりする際に番号付けを変えなければならないので、かなり 現実的でない方法です。多くのデータベースでは、この問題を解決するため、 アットマーク(@name)を使ったり、コロン(:name)を 使ったりと、パラメータに名前をつけられるよう独自の拡張をしています。

String sql = prepare("SELECT * FROM ? WHERE ?");

sql.execute('employees', 'employee_id = 1');