パラメータ化クエリ


この節では、大抵の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
---------------------------------------------------------------
|Id | Operation                   | Name        | Rows | Cost |
---------------------------------------------------------------
| 0 | SELECT STATEMENT            |             |   99 |   70 |
| 1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES   |   99 |   70 |
|*2 |   INDEX RANGE SCAN          | EMPLOYEE_PK |   99 |    2 |
---------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("SUBSIDIARY_ID"=20)

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

SELECT first_name, last_name
  FROM employees
 WHERE subsidiary_id = 30
----------------------------------------------------
| Id | Operation         | Name      | Rows | Cost |
----------------------------------------------------
|  0 | SELECT STATEMENT  |           | 1000 |  478 |
|* 1 |  TABLE ACCESS FULL| EMPLOYEES | 1000 |  478 |
----------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("SUBSIDIARY_ID"=30)

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

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

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

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

ヒント

列に対するヒストグラムは、値が均一に分散していない時、最も 有効にはたらきます。

列の値が均一に分布しているなら、その列が持つ一意な値の数を テーブルの行数で割ればいいだけです。バインドパラメータを使った時も 同じことです。

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

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

Tweet this tip

ヒント

バインドパラメータを使わないのは、プログラムを 毎回コンパイルして実行するようなものです。

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

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

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

Tweet this tip

ヒント

実際には、与えられた値が実行計画に影響を 及ぼすケースは、それほど多いわけではありません。確信が持てない 場合は、SQLインジェクションを防止するために、バインドパラメータを 使いましょう。

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

C#

バインドパラメータなしの場合

int subsidiary_id;
SqlCommand cmd = new SqlCommand(
                   "select first_name, last_name" 
                 + "  from employees"
                 + " where subsidiary_id = " + subsidiary_id
                 , connection);

バインドパラメータありの場合

int subsidiary_id;
SqlCommand cmd =
       new SqlCommand(
                      "select first_name, last_name" 
                    + "  from employees"
                    + " where subsidiary_id = @subsidiary_id
                    , connection);
cmd.Parameters.AddWithValue("@subsidiary_id", subsidiary_id);

SqlParameterCollection クラスのドキュメントも参照しましょう。

Java

バインドパラメータなしの場合

int subsidiary_id;
Statement command = connection.createStatement(
                    "select first_name, last_name" 
                  + "  from employees"
                  + " where subsidiary_id = " + subsidiary_id
                  );

バインドパラメータありの場合

int subsidiary_id;
PreparedStatement command = connection.prepareStatement(
                    "select first_name, last_name" 
                  + "  from employees"
                  + " where subsidiary_id = ?"
                  );
command.setInt(1, subsidiary_id);

PreparedStatement クラスのドキュメントも参照しましょう。

Perl

バインドパラメータなしの場合

my $subsidiary_id;
my $sth = $dbh->prepare(
                  "select first_name, last_name" 
                . "  from employees"
                . " where subsidiary_id = $subsidiary_id"
                );
$sth->execute();

バインドパラメータありの場合

my $subsidiary_id;
my $sth = $dbh->prepare(
                  "select first_name, last_name" 
                . "  from employees"
                . " where subsidiary_id = ?"
                );
$sth->execute($subsidiary_id);

Programming the Perl DBIを参照しましょう。

PHP

バインドパラメータなしでMySQLを使用する場合

$mysqli->query("select first_name, last_name" 
             . "  from employees"
             . " where subsidiary_id = " . $subsidiary_id);

バインドパラメータありの場合

if ($stmt = $mysqli->prepare("select first_name, last_name" 
                           . "  from employees"
                           . " where subsidiary_id = ?")) 
{
   $stmt->bind_param("i", $subsidiary_id);
   $stmt->execute();
} else {
  /* handle SQL error */
}

mysqli_stmt::bind_param クラスのドキュメントおよびPDOの ドキュメントに含まれる、「プリペアドステートメントおよび ストアドプロシージャ」も参照しましょう。

Ruby

バインドパラメータなしの場合

dbh.execute("select first_name, last_name" 
          + "  from employees"
          + " where subsidiary_id = #{subsidiary_id}");

バインドパラメータありの場合

dbh.prepare("select first_name, last_name" 
          + "  from employees"
          + " where subsidiary_id = ?");
dbh.execute(subsidiary_id);

Ruby DBI チュートリアルの「クオート、プレースホルダー、パラメータ束縛」も 参照しましょう。

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

注記

バインドパラメータは、SQL文の構造を変えることはできません。

つまり、テーブル名や列名にバインドパラメータを使用する ことはできません。以下のようなバインドパラメータは使えないと いうことです。

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

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

実行時にSQL文の構造も変える必要がある場合には、 動的SQLを使いましょう。

カーソル共有と強制パラメータ化

オプティマイザやSQLクエリが複雑になっていくと、実行計画の キャッシュの重要性が増してきます。SQL ServerとOracleには、 SQL文中のリテラル値を、自動的にバインドパラメータに置き換える 機能があります。CURSOR_SHARING (Oracle)あるいは 強制パラメータ化 (SQL Server)といいます。

どちらの機能も、バインドパラメータを全く使っていない アプリケーションを避けるためにあります。これらの機能を有効にする ことで、開発者が意図的にリテラル値を使ってしまうのを防ぐことが できます。

Photo of Markus Winand
Markus Winand氏は、開発者がSQLパフォーマンスを改善するお手伝いをしています。 彼は、SQL Performance Explainedの 著者でもあり、出張トレーニングhttp://winand.at/での リモート講義も 行っています。