by Hayato Matsuura.

フィルタ述語の意図的な使用


多くの場合、フィルタ 述語は、複合インデックスの列の順番が正しくないことで、インデックスが 正常に使われていないことを表すものと見なされます。しかし、フィルタ述語はいい意味で使われることもあります。範囲検索のパフォーマンスを改善しては くれませんが、連続的にアクセスするデータをまとめる意味があるからです。

アクセス述語としては使われないwhere句の述語は、 このテクニックのちょうどいい例になります。

SELECT first_name, last_name, subsidiary_id, phone_number
  FROM employees
 WHERE subsidiary_id = ?
   AND UPPER(last_name) LIKE '%INA%'

ワイルドカードから始まるLIKEの条件では、インデックスツリーを使えないことを思い出してみましょう。 そうすると、LAST_NAMEUPPER(last_name)に インデックスがあっても、LAST_NAMEのインデックスは、スキャンされる インデックスの範囲を狭めてはくれないということが分かります。つまり、このような条件はインデックスにとってはよいものではないのです。

協力してください

この記事が気に入ったら、私の書いた本「SQLパフォーマンス詳解」や私によるトレーニングもきっと気にいるはず。

その一方、SUBSIDIARY_IDに対する条件は、 インデックスを使うのに適したものになっています。SUBSIDIARY_IDは 既にプライマリキーの最初の列になっていますので、追加でインデックスを作る必要はありません。

DB2
Explain Plan
------------------------------------------------------------
ID | Operation               |                   Rows | Cost
 1 | RETURN                  |                        |   40
 2 |  FETCH EMPLOYEES        |    33 of 333 (  9.91%) |   40
 3 |   RIDSCN                |   333 of 333 (100.00%) |   12
 4 |    SORT (UNQIUE)        |   333 of 333 (100.00%) |   12
 5 |     IXSCAN EMPLOYEES_PK | 333 of 10000 (  3.33%) |   12

Predicate Information
 2 - SARG (Q1.SUBSIDIARY_ID = ?)
     SARG ( UPPER(Q1.LAST_NAME) LIKE '%INA%')
 5 - START (Q1.SUBSIDIARY_ID = ?)
      STOP (Q1.SUBSIDIARY_ID = ?)
Oracle
--------------------------------------------------------------
|Id | Operation                   | Name       | Rows | Cost |
--------------------------------------------------------------
| 0 | SELECT STATEMENT            |            |   17 |  230 |
|*1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES  |   17 |  230 |
|*2 |   INDEX RANGE SCAN          | EMPLOYEE_PK|  333 |    2 |
--------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(UPPER("LAST_NAME") LIKE '%INA%')
   2 - access("SUBSIDIARY_ID"=TO_NUMBER(:A))

上の実行計画では、TABLE ACCESS BY INDEX ROWIDのコストが、 それに続くINDEX RANGE SCANのコストに比べて、100倍も大きく なっています。言い換えると、処理のほとんどはテーブルへのアクセスだという ことです。これはよくあるパターンで、それ自体に問題があるわけではありません。 しかし、クエリの実行時間のほとんどを占めているのは間違いありません。

アクセスされる行が1つのテーブルブロックに格納されている場合、データベースは全ての行を1回の読み込み処理で取得できるため、 テーブルアクセスは必ずしもボトルネックになりません。一方、行が複数のブロックに分散してしまっている場合、各行を取得するために データベースは多くの行を読み取らねばならないので、深刻なパフォーマンス問題になることがあります。つまりパフォーマンスは、アクセスされる 行の物理的な分散具合、すなわち行のクラスタ化の状況に左右されるということです。

注記

インデックスの並びとテーブルの並びの相関関係は、パフォーマンスを測る上での基準になります。いわゆる、クラスタ化係数 です。

クエリのパフォーマンスを改善するために、実際にインデックスの順序に合わせて行を並び替えることも可能です。しかし、 テーブルの行は一方向にしか並べられないので、この方法はほとんど使われません。テーブルが1つのインデックスにしか最適化されなくなって しまうからです。最適化のために1つのインデックスだけを選び出したとしても、多くのデータベースではそういったタスクのためには簡単なツールしか 用意していません。これはいわゆる行の並べ替えと呼ばれる方法ですが、やはりあまり現実的ではない方法です。

クラスタ化係数

インデックスクラスタ化係数は、インデックスの2つの連続したエントリが同じテーブルブロックにある可能性を表す、 間接的な指標です。オプティマイザは、TABLE ACCESS BY INDEX ROWID 処理のコスト値を計算する際に、これを考慮に入れます。

これが、インデックスの強力さの2つ目である、 データのクラスタ化です。データが自動的に理想的な順序で並べて保存されるように、 インデックスに列を追加することができます。それによってインデックスは、 データをクラスタ化する強力だけれどもシンプルなツールになるのです。

前述のクエリにこのコンセプトを適用すると、スキャンされるインデックスの範囲は狭めなくても、where句の全ての列を含むインデックスを作る必要があります。

CREATE INDEX empsubupnam ON employees
       (subsidiary_id, UPPER(last_name))

SUBSIDIARY_IDは、インデックスの最初の列なので、 アクセス述語として使われます。UPPER(last_name)は、 フィルタ述語としてのLIKEを カバーします。大文字表現にインデックスを作ることによって、実行時の CPU使用を少し抑えることができますが、単にLAST_NAMEに 対するインデックスでも同じように動作します。これについては次の節で説明します。

DB2
Explain Plan
--------------------------------------------------------
ID | Operation             |                 Rows | Cost
 1 | RETURN                |                      |   15
 2 |  FETCH EMPLOYEES      |               0 of 0 |   15
 3 |   IXSCAN EMPSUBUPNAM2 | 0 of 10000 (   .00%) |   15

Predicate Information
 3 - START (Q1.SUBSIDIARY_ID = ?)
      STOP (Q1.SUBSIDIARY_ID = ?)
      SARG (Q1.LAST_NAME LIKE '%INA%')

希望通りの実行計画を得るには、インデックスとwhere句からUPPERを消す必要が ありました。DB2 LUW 10.5では、関数インデックスはまだ新しい機能なので、 オプティマイザが完全には動いてくれないようです。また、DB2では 大文字小文字を区別しない動作をさせるのには、然るべき照合順序を使った方がよいようです。

Oracle
--------------------------------------------------------------
|Id | Operation                   | Name       | Rows | Cost |
--------------------------------------------------------------
| 0 | SELECT STATEMENT            |            |   17 |   20 |
| 1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES  |   17 |   20 |
|*2 |   INDEX RANGE SCAN          | EMPSUBUPNAM|   17 |    3 |
--------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("SUBSIDIARY_ID"=TO_NUMBER(:A))
       filter(UPPER("LAST_NAME") LIKE '%INA%')

新しい実行計画でも、前とほとんど同じ処理を行っている様子が分かります。 しかし、コスト値は劇的に下がっています。述語情報を見ると、LIKEフィルタが既にINDEX RANGE SCANに適用されています。つまり、LIKEフィルタの条件を満たさない行は、即座に捨てられているのです。 テーブルアクセスにはフィルタ述語が使われていません。従って、where句を満たさない行はロードされていないということです。

2つの実行計画の違いは、「Rows」列に明確に表れています。オプティマイザの見積もりによると、クエリは最終的に17レコードに一致すると されています。最初の実行計画のインデックススキャンは、333行を返していました。データベースは、この333行をテーブルから読み出して、それを 17行まで減らすLIKEフィルタを適用していた訳です。2番目の 実行計画では、最初のインデックスアクセスではこのような行を読み出していないので、データベースはTABLE ACCESS BY INDEX ROWIDを17回だけ実行すればよいようになっています。

もう1つ、INDEX RANGE SCAN処理のコスト値が2から3に 増えているのにも気付けるとよいでしょう。これは、列を追加したことでインデックスが大きくなったためです。パフォーマンス向上の観点から言うと、 これは許容できる範囲と言えます。

警告

フィルタ述語に使うだけの目的では、新しいインデックスを 作らないようにすべきです。既にあるインデックスを拡張し、メンテナンスコストを 抑えましょう。いくつかのデータベースでは、プライマリキーのインデックスに プライマリキーでない列を加えることも出来てしまいます。

次のアニメーションは、2つの実行計画の違いを表したものです。

図意図的なフィルタ述語

この一目瞭然の例は、where句の各列にインデックスを 作った時の一般常識を確認したに過ぎないかもしれません。しかしこの「常識」は、 どんな条件がアクセス述語に使われるかを決定し、パフォーマンスに多大な影響を及ぼす、列の順序を無視しています。列の順序の決め方は、成り行き任せにして しまうべきではありません。

列の数が増えるほど、インデックスのサイズは増えていき、テキスト列を追加した際は特に増加します。対数的スケーラビリティにより、影響はかなり限定されますが、 インデックスが大きいと、パフォーマンスもよくなりません。where句にある全ての列をインデックスに追加すれば いいという意味ではなく、実行ステップの初期段階でのデータ量を減らすために、フィルタ述語を意図的に使っていくべきです。

この説明が気に入れば、きっとこの本も 気に入るはず。

著者について

Markus Winandの写真

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

彼の本

カバー『SQLパフォーマンス詳解』

核心をわかりやすく 解説。

Markusから購入します
(送料無料+PDF)

Amazonで購入
(印刷版のみ)

“Use The Index, Luke!” by Markus Winand is licensed under a Creative Commons Attribution-Noncommercial-No Derivative Works 3.0 Unported License.
法律上の通知 | 接触 | 無保証 | 商標 | Privacy | CC-BY-NC-ND 3.0 license