スマートなロジック


SQLデータベースの主な機能のひとつに、アドホックなクエリ、 つまり、新しい種類のクエリをいつでも実行可能であることが 挙げられるでしょう。これは、クエリ オプティマイザ(クエリプランナ)が、文を受け取る度に 解析し、妥当な実行計画を直ちに生成してくれるからに他なりません。 実行時に最適化が行われることのオーバーヘッドは、バインドパラメータによって 小さく抑えることができます。

つまりそれは、データベースは動的SQLに最適化されているという ことです。必要ならこの機能を使っていくべきでしょう。

初心者からエキスパートまで役に立つ内容です。
特に駆け出しのエンジニアは持っておくといい

しかし、「動的SQLは 遅い」という都市伝説のせいか、静的SQLを好んで、動的SQLを避ける傾向も 多いと言わざるを得ません。しかしこれは、DB2やOracle、 SQL Serverと言った、実行計画の共有キャッシュを使ったデータベースに おいては、利益以上に害をもたらす習慣です。

デモのため、EMPLOYEESテーブルを 検索するアプリケーションを考えてみて下さい。アプリケーションは、 子会社IDや従業員ID、姓(大文字小文字は問わない)の様々な 組み合わせで検索できるとしましょう。「スマートな」仕組みで、 全ての場合を1つのクエリでカバーすることも可能です。

SELECT first_name, last_name, subsidiary_id, employee_id
  FROM employees
 WHERE ( subsidiary_id    = :sub_id OR :sub_id IS NULL )
   AND ( employee_id      = :emp_id OR :emp_id IS NULL )
   AND ( UPPER(last_name) = :name   OR :name   IS NULL )

このクエリは、読みやすさのために名前付き バインド変数を使っています。取り得る全てのフィルタ表現は、 静的に文に埋め込まれています。フィルタが必要ない時は、検索語の代わりに NULLを指定すればよいだけです。ORの 論理で条件を無効にできます。

これは、完全に正しいSQL文です。NULLの使い方も、 SQLの3つの値の論理演算に一致した定義に 沿っています。にもかかわらずこの文は、全くもって最悪の パフォーマンスを招くアンチパターンです。

データベースは、それぞれのフィルタが実行時にキャンセルされる 可能性があるため、実行計画を最適化できません。そのためデータベースは、 全てのフィルタが使えないという最悪のケースに備えなくてはならないの です。

----------------------------------------------------
| Id | Operation         | Name      | Rows | Cost |
----------------------------------------------------
|  0 | SELECT STATEMENT  |           |    2 |  478 |
|* 1 |  TABLE ACCESS FULL| EMPLOYEES |    2 |  478 |
----------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter((:NAME   IS NULL OR UPPER("LAST_NAME")=:NAME) 
       AND (:EMP_ID IS NULL OR "EMPLOYEE_ID"=:EMP_ID) 
       AND (:SUB_ID IS NULL OR "SUBSIDIARY_ID"=:SUB_ID))

その結果、各列にインデックスがあったとしても、 データベースはフルテーブルスキャンを実行してしまいます。

データベースは、「スマートな」仕組みをうまく解決できなかった わけではありません。バインドパラメータを使ったため、一般的な 実行計画を作り、それをキャッシュして、それ以降異なる値が 与えられた時にも使いまわせるようにしたのです。バインドパラメータを使わず実際の 値をSQL文に埋め込んだ場合、オプティマイザはフィルタに対して 正しいインデックスを選択します。

SELECT first_name, last_name, subsidiary_id, employee_id
  FROM employees
 WHERE( subsidiary_id    = NULL     OR NULL IS NULL )
   AND( employee_id      = NULL     OR NULL IS NULL )
   AND( UPPER(last_name) = 'WINAND' OR 'WINAND' IS NULL )
---------------------------------------------------------------
|Id | Operation                   | Name        | Rows | Cost |
---------------------------------------------------------------
| 0 | SELECT STATEMENT            |             |    1 |    2 |
| 1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES   |    1 |    2 |
|*2 |   INDEX RANGE SCAN          | EMP_UP_NAME |    1 |    1 |
---------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
  2 - access(UPPER("LAST_NAME")='WINAND')

これは解決策とは言えません。そういう条件にすればデータベースは うまくできる、と証明したに過ぎません。

警告

リテラル値を使うと、アプリケーションがSQL インジェクション攻撃に対して脆弱になると共に、 最適化のオーバーヘッドが増えてパフォーマンス問題を引き起こす 原因になります。

動的なクエリに対する一番の解は、動的SQLです。KISSの 原則に従い、データベースには今何が必要かだけを伝え、それ以外は 伝える必要はないのです。

SELECT first_name, last_name, subsidiary_id, employee_id
  FROM employees
 WHERE UPPER(last_name) = :name

クエリではバインドパラメータを使っていることに注意しましょう。

Tweet this tip

ヒント

動的なwhere句が 必要な時は、動的SQLを使いましょう。

動的SQLを使う時も、バインドパラメータを使いましょう。 そうでないと、「動的SQLは 遅い」という都市伝説がまた蘇ってきてしまいます。

この節で挙げた問題は、広範囲にわたるものです。実行計画の 共有キャッシュを使う全てのデータベースには、この問題をうまく処理する 機能がありますが、その多くは新しい問題やバグを抱えています。

DB2

DB2は、実行計画の共有キャッシュを使っており、この節で 取り上げた問題にさらされています。

DB2では、REOPTヒントを使った再最適化の アプローチを指定することができます。デフォルトでは、 上で挙げた問題に悩まされることになってしまう、一般的な実行計画を 生成するNONEに設定されています。 REOPT(ALWAYS)に設定すると、実行時に最適な計画を 生成できるよう、実際のバインド変数を事前に読み取ります。これに よって、その文に対する実行計画のキャッシュを効果的に無効にする ことができます。

最後のオプションがREOPT(ONCE)で、これは 最初の実行時だけに実際のバインドパラメータの値を読み取ります。 この方法の問題は、 振る舞いが確定的でなく、最初の実行時の値がその後の全ての実行に 影響してしまうことです。データベースが再起動される度に 実行計画が変わってしまう可能性がありますし、さらに予想しにくい ことに、キャッシュされた実行計画が有効期限切れになり、 次に文が実行された時にオプティマイザが違う値で実行計画を作って しまうこともあり得ます。

MySQL

MySQLは、実行計画のキャッシュをしないので、この問題には 影響されません。2009年の機能 リクエストで実行計画のキャッシュのインパクトについて議論が ありました。しかし、実行計画のキャッシュに よる効果が見合わないほど、MySQLのオプティマイザはシンプルに できているということのようです。

Oracle

Oracleは、共有の実行計画キャッシュ(“SQL領域”) )を使うので、この節に出てきた問題にさらされて います。

Oracleでは、いわゆるバインド ピークという機能がリリース9iで 登場しました。バインドピークは、実行計画を準備する時、 最初の実行時の実際のバインド値をオプティマイザが使うようにする ものです。この方法の問題は、 振る舞いが確定的でなく、最初の実行時の値がその後の全ての実行に 影響してしまうことです。データベースが再起動される度に 実行計画が変わってしまう可能性がありますし、さらに予想しにくい ことに、キャッシュされた実行計画が有効期限切れになり、 次に文が実行された時にオプティマイザが違う値で実行計画を作って しまうこともあり得ます。

リリース11gでは、さらにこれを 改善するため、 Adaptive Cursor Sharingという機能が登場しました。これにより、 同じSQL文に対して複数の実行計画をキャッシュできるようになりました。 この機能により、オプティマイザはバインドパラメータを読み取るのに加え、 選択性の見積もりを実行計画と一緒に保存することができます。 キャッシュにアクセスした時、そのキャッシュと一緒に保存された 選択性の範囲内にバインド値が収まっていない場合は、キャッシュは 再利用されません。この時、オプティマイザは新しい実行計画を 立案し、既にキャッシュされた実行計画と比較します。既に同じ SQL文に対する実行計画がある場合、現在のバインド値に対する選択性の 見積もりをカバーする新しい実行計画と置き換えます。そうでない場合、 その実行計画は、選択性の見積もり値と一緒に、クエリに対する 新しいパターンの実行計画としてキャッシュされます。

PostgreSQL

PostgreSQLのクエリ計画のキャッシュは、カーソルが 開かれた文にのみ適用されます。つまり、 PreparedStatementを開く必要があります。 この節で取り上げた問題は、ステートメントハンドルを再利用 する場合にのみ発生します。なお、PostgreSQLのJDBCドライバは、 5回目の実行以降でのみキャッシュを有効にすることに注意 しましょう。実際のバインド値で 計画を立てるも参照してください。

SQL Server

SQL Serverでは、パラメータ スニッフィングという機能を使います。これは、SQL文のパースの際、 最初の実行時の実際のバインド値をオプティマイザが使えるように するものです。 この方法の問題は、 振る舞いが確定的でなく、最初の実行時の値がその後の全ての実行に 影響してしまうことです。データベースが再起動される度に 実行計画が変わってしまう可能性がありますし、さらに予想しにくい ことに、キャッシュされた実行計画が有効期限切れになり、 次に文が実行された時にオプティマイザが違う値で実行計画を作って しまうこともあり得ます。

SQL Server 2005では、パラメータスニッフィングと リコンパイルをより詳細に制御できるクエリヒントが追加されました。 クエリ ヒントRECOMPILEは、指定した文に対して 実行計画のキャッシュをしないようにするものです。OPTIMIZE FORは、最適化にのみ使う 実際のパラメータ値を指定します。最後に、USE PLANヒント では全体で使用する実行計画を指定します。

OPTION(RECOMPILE)ヒントの元々の実装にはバグがあり、 全てのバインド変数を考慮してくれません。SQL Server 2008での 新しい実装では別のバグがあるため、問題の複雑さにさらに拍車を かけています。Erland Sommarskog氏が、全てのSQL Serverのリリースに対して、 この関連情報をまとめてくれています。

ヒューリスティックな手法によって、「スマートなロジック」の問題は ある程度までは解決できますが、そういった手法は、列のヒストグラムや LIKE式にバインドパラメータを当てはめて解決しようという 試みです。

最適な実行計画を得られる最も信頼できる方法は、SQL文において 不要なフィルタを使わないことです。

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