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


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

アクセス述語としては使われない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のインデックスは、スキャンされる インデックスの範囲を狭めてはくれないということが分かります。つまり、このような 条件はインデックスにとってはよいものではないのです。

このウェブサイトにぴったりのカップは僕たちのショップにあります。
#見た目もいい感じだし、ここでの僕の仕事を支えてくれています

その一方、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つの実行計画の違いを 表したものです。

図5.1 意図的なフィルタ述語


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

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

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

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