by Hayato Matsuura.

インデックスのみのスキャン: テーブルアクセスを 避ける


インデックスのみのスキャンは、あらゆるチューニング法の中でも最強の方法の1つと言えるでしょう。where句の評価のために テーブルにアクセスする必要がないだけでなく、インデックス自体にお目当ての列があるなら、テーブルに全くアクセスしなくてもよいのです。

クエリ全体をカバーするためには、SQL文に出てくる全ての 列をインデックスに含めなくてはなりません。次の例のように、select句に含まれる列も例外ではありません。

CREATE INDEX sales_sub_eur
    ON sales
     ( subsidiary_id, eur_value )
SELECT SUM(eur_value)
  FROM sales
 WHERE subsidiary_id = ?

もちろん、インデックスを作る際は、他の句よりもwhere句が優先されます。 SUBSIDIARY_ID列は、アクセス述語として 使えるように、インデックスの最初にあります。

協力してください

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

実行計画では、インデックススキャンが行われているとあり、その後にテーブルアクセス(TABLE ACCESS BY INDEX ROWID)は行われていません。

DB2
Explain Plan
---------------------------------------------------------------
ID | Operation              |                       Rows | Cost
 1 | RETURN                 |                            |   21
 2 |  GRPBY (COMPLETE)      |       1 of 34804 (   .00%) |   21
 3 |   IXSCAN SALES_SUB_EUR | 34804 of 1009326 (  3.45%) |   19

Predicate Information
 3 - START (Q1.SUBSIDIARY_ID = ?)
      STOP (Q1.SUBSIDIARY_ID = ?)
Oracle
----------------------------------------------------------
| Id  | Operation         | Name          |  Rows | Cost |
----------------------------------------------------------
|   0 | SELECT STATEMENT  |               |     1 |  104 |
|   1 |  SORT AGGREGATE   |               |     1 |      |
|*  2 |   INDEX RANGE SCAN| SALES_SUB_EUR | 40388 |  104 |
----------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("SUBSIDIARY_ID"=TO_NUMBER(:A))

インデックスがクエリ全体をカバーしていることから、 カバリングインデックスとも呼ばれます。

注記

インデックスのはたらきによって、テーブルアクセスしなくても良かった場合のことを、 カバリングインデックスと言います。

これは、インデックスの機能であるかのような響きがあるので、誤解されやすい用語です。インデックスのみのスキャン、という言い方の 方が、実行計画の操作であることを明白に示しています。

データベースがインデックスに保存された値を使えるように、 インデックスはEUR_VALUE列を持っています。クエリの結果を返すための全ての情報をインデックスが持っているので、 テーブルにアクセスする必要はありません。

インデックスのみのスキャンは、桁違いにパフォーマンスを改善してくれます。実行計画の行数見積もりを見てみましょう。 オプティマイザは、4万行をまとめる必要があるとしています。つまり、各行が別々のブロックに保存されていたのだとすると、 インデックスのみのスキャンによって、4万回のテーブル読み込みをしなくて済んだということです。このインデックスがそこそこのクラスタ化係数を持っている、 つまり少数のテーブルブロックにデータがクラスタ化されているとすると、この利点はかなり小さくなります。

クラスタ化係数に加え、選択する行数によって、インデックスのみのスキャンの パフォーマンス改善効果は限定されてきます。もし1行しか選択しないとすると、1回分のテーブルアクセスしか削減できません。ツリーの走査でも数ブロックを読み込む必要が あることを考えると、節約できたテーブルアクセスの回数は、無視できる程度かもしれません。

重要

インデックスのみのスキャンの優位性は、 アクセスされる行数と、クラスタ化係数によって決まります。

インデックスのみのスキャンは、アグレッシブなインデックス戦略です。不要なメモリを確保したり、update文実行時のメンテナンス コストが増えたりするので、見込みだけでインデックスのみのスキャンを狙ってインデックスをデザインしてはなりません。詳しくは、第8章, 「データの変更を 参照してください。実際には、まずはwhere句などを優先し、select句を考えずにインデックスを作って、 必要があればそこから拡張していくべきでしょう。

インデックスのみのスキャンは、あまり嬉しくないことであなたを驚かせる可能性もあります。例えば、クエリを最近の売上だけに限定したものに 変えてみましょう。

SELECT SUM(eur_value)
  FROM sales
 WHERE subsidiary_id = ? AND sale_date > ?

実行計画を見ない内は、選択する行数が減った分、クエリの実行は 速くなると考えるでしょう。しかし、where句が インデックスにない列を参照しようとするので、その列を取得するためデータベースはテーブルへアクセスしなくてはならなくなりました。

DB2
Explain Plan
-------------------------------------------------------------------
ID | Operation                 |                       Rows |  Cost
 1 | RETURN                    |                            | 13547
 2 |  GRPBY (COMPLETE)         |        1 of 1223 (   .08%) | 13547
 3 |   FETCH SALES             |    1223 of 34804 (  3.51%) | 13547
 4 |    RIDSCN                 |   34804 of 34804 (100.00%) |    32
 5 |     SORT (UNQIUE)         |   34804 of 34804 (100.00%) |    32
 6 |      IXSCAN SALES_SUB_EUR | 34804 of 1009326 (  3.45%) |    19

Predicate Information
 3 - SARG (? < Q1.SALE_DATE)
     SARG (Q1.SUBSIDIARY_ID = ?)
 6 - START (Q1.SUBSIDIARY_ID = ?)
      STOP (Q1.SUBSIDIARY_ID = ?)
Oracle
--------------------------------------------------------------
|Id | Operation                    | Name      | Rows  |Cost |
--------------------------------------------------------------
| 0 | SELECT STATEMENT             |           |     1 | 371 |
| 1 |  SORT AGGREGATE              |           |     1 |     |
|*2 |   TABLE ACCESS BY INDEX ROWID| SALES     |  2019 | 371 |
|*3 |    INDEX RANGE SCAN          | SALES_DATE| 10541 |  30 |
--------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("SUBSIDIARY_ID"=TO_NUMBER(:A))
   3 - access("SALE_DATE">:B)

クエリが選択する行数は減ったのに、テーブルアクセスによって レスポンスタイムが増えてしまいます。関係があるのは、クエリが返す行数ではなく、行を探し出すためにデータベースが何行にアクセスするか なのです。

警告

where句の条件を増やすと、 「不合理な」パフォーマンスの変化を引き起こすことがあります。クエリを変更する前に、実行計画を確認しましょう。

インデックスのみのスキャンにインデックスが使えなくなってしまうと、オプティマイザは次点の実行計画を選択します。つまり、オプティマイザは 全く違う実行計画を選ぶか、上の例のように違うインデックスを使った似たような実行計画を選びます。今回は、前の章で作ったSALE_DATEの インデックスを使っています。

オプティマイザの観点だと、このインデックスはSALES_SUB_EURと 比べると2つの優位点があります。オプティマイザは、SALE_DATEに 対するフィルタは、SUBSIDIARY_IDに対するフィルタよりも 選択性が高いと見ているのです。前の実行計画2つの「Rows」列からもそれが分かります(約1万行と4万行)。このクエリはバインドパラメータを使っているので、 これらの見積もりは、あくまで予想です。例えば、SALE_DATEの 条件に、初めて売上があった日が指定されたら、テーブル全体が選択されてしまいます。

SALES_DATEのインデックスの2つ目の優位点は、 クラスタ化係数がよいことです。SALESテーブルは時系列に大きく なっていくものなので、これは正しい考えと言えます。行が削除されない限り、新しい行は常にテーブルの最後尾に追加されていきます。 テーブルもインデックスもおおむね時系列にソートされているので、テーブルの並び方とインデックスの並び方は一致します。これはすなわち、 クラスタ化係数がよいということになります。

クラスタ化係数のよいインデックスということは、行を取りだす時に 少数のブロックしか読まなくてよいようにデータベースがデータを保存しているはずなので、選択する行は近くに保存されています。このようなインデックスを 使うと、インデックスのみのスキャンでなくても十分にクエリは高速になります。 この場合、他のインデックスから不要な列を削除するべきでしょう。

注記

自動的にクラスタ化係数がよくなるようなインデックスがあるので、 インデックスのみのスキャンのパフォーマンス上の優位性は小さくなります。

今回の例では、良い方の偶然もあります。SALE_DATEに 対する新しいフィルタは、インデックスのみのスキャンができないようにしただけでなく、新しいアクセスパスがあることも示してくれました。 オプティマイザは、この変更によるパフォーマンスの影響を小さくしてはくれましたが、別の句に列を追加することで、インデックスのみのスキャンを しないようにすることも可能です。一方、select句に 列を追加してしまうと、インデックスのみのスキャンが出来なくなったことによる影響を限定するアクセスパスは登場しなかったでしょう。

ヒント

インデックスのみのスキャンであることを明記しておきましょう。

インデックスのみのスキャンを使っていることを忘れないように コメントを書いておき、誰でも分かるようにしておきましょう。

関数インデックスも、 インデックスのみのスキャンに関連して、不愉快な不意打ちを仕掛けてくる 可能性があります。LAST_NAME列を選択する時には、 UPPER(last_name)に対するインデックスはインデックスのみのスキャンを使えません。前の節で、 LIKEフィルタをサポートするようにLAST_NAME列にも インデックスを作っておくべきだったかもしれません。そうすれば、 LAST_NAMEを選択する時もインデックスのみのスキャンが使えたでしょう。

ヒント

いつも最適な情報がインデックスに入るように、なるべくオリジナルの データにインデックスを作成するように常に心がけましょう。

アクセス述語として使えない表現に関数インデックスを作るのは やめましょう。

前述のようなクエリをまとめるのは、インデックスのみのスキャンを使う候補になり得ます。これらのクエリは、たくさんの行を選択するけれど 対象の列は少ないので、インデックスのみのスキャンをサポートするスリムなインデックスを作れます。多くの列を対象にすればするほど、 インデックスのみのスキャンをサポートするために、たくさんの列をインデックスに 追加しなくてはなりません。開発者としては、本当に必要な列だけを選択するようにしましょう。

ヒント

select *するのはやめて、必要な列だけを指定しましょう。

多くの行にインデックスを作るとたくさんの容量が必要なのとはまた別に、使用しているデータベースの制限に届いてしまう可能性もあります。 多くのデータベースでは、インデックスあたりの列数やインデックスエントリの 総量に厳格な制限があります。つまり、好きなだけの列をインデックスに追加したり、 どんな長さの列もインデックスに追加したり出来るわけではないということです。特に重要な制限事項を次に列挙します。次の節では、テーブル全体をカバーする インデックスについて取り上げます。

DB2

DB2 LUWでは、インデックスに 含められるのは64列までで、ページサイズの25%までの長さの列に制限されています。

DB2では、インデックスでない列にユニークインデックスを 追加できるINCLUDEをサポートしています。これにより、一意性の文脈を変えずに インデックスのみのスキャンに使えるように、ユニークインデックスに列を追加できます。

MySQL

InnoDBのMySQLでは、(全列の)キー長合計は3072バイトに制限されています。さらに、各列の長さはinnodb_large_prefixが 有効になっていないか、行フォーマットにDYNAMICあるいは COMPRESSEDが使用されていない場合は、767バイトに 制限されています。これは、MySQL 5.6以降ではデフォルトに なっています。MyISAMのインデックスは、16列までで、 かつキーの最大長が1000バイトに制限されています。

MySQLは、「プレフィックスインデックス」 (「部分インデックス」と呼ばれることもある)というユニークな機能があります。 これにより、列の最初の数文字にだけインデックスを作ることができます。一方で、第2章で説明したような部分インデックスはありません。最大長(上述の通り、 767、1000、3072のいずれか)を超えるインデックスを作ろうとすると、SQL モードあるいは行フォーマット の設定によっては、MySQLはその列を自動的に短くします。このような時には、create index文は 「Specified key was too long; max key length is ... bytes」という 警告を出しつつも成功してしまいます。つまり、インデックスは列の完全なコピーではなくなってしまい、この列を選択する際もインデックス のみのスキャンに使えなくなってしまいます(関数インデックスの場合と同様です)。

「Specified key was too long; max key length is … bytes.」のようなエラーが出るようなら、明示的にキーの長さを指定してインデックスを作ることも可能です。これは、 LAST_NAME列の最初の10文字にのみインデックスを作る例です。

CREATE INDEX .. ON employees (last_name(10))
Oracle

インデックスの最大キー長は、ブロックサイズとインデックスのストレージパラメータに依存します(ブロックサイズの 75%から多少のオーバーヘッドを引いた値)。Bツリーインデックスでは32列に制限されています。

デフォルト構成(8kブロック)のOracle 11gでは、インデックスの最大キー長は6398バイトです。この制限を超えると、 「ORA-01450: maximum key length (6398) exceeded.」のエラーが 発生します。

PostgreSQL

PostgreSQLでは、インデックスのみのスキャンをリリース 9.2からサポートしています。

Bツリーインデックスのキー長は、2713バイト(ハードコードされており、 およそBLCKSZ/3)に制限されています。エラー時のメッセージは「index row size ... exceeds btree maximum, 2713」で、制限を超えるinsertあるいはupdateが実行された時のみ発生します。Bツリーインデックスは、32 列まで含むことができます。

SQL Server

SQL Server 2016からは32列のキー長をサポートしていますが、全体の長さは1700バイトに制限されています (900バイトはクラスタインデックス用)。 0 しかし、SQL Serverには インデックスのみのスキャンをサポートするためだけに、任意の長い列を追加することができる機能があります。これにより、SQL Serverは キー列か非キー列かを区別します。

キー列とは、ここまで扱ってきたインデックスの列と同じ意味です。 非キー列は、インデックスリーフノードに保存されているだけの列のことです。非キー列はアクセス述語(seek predicates)としては使えませんが、任意の長さにできます。

非キー列は、create indexコマンドの includeキーワードで定義できます。

 CREATE INDEX empsubupnam
     ON employees (subsidiary_id, last_name)
INCLUDE(phone_number, first_name)

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

ヒント

どの列も選択しないクエリは通常、インデックスのみのスキャンになります。

分かりやすい例を思い浮かべられますか?

著者について

Markus Winandの写真

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

彼の本

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

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

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

Amazonで購入
(印刷版のみ)

注記

0

SQL Server 2016より前のバージョンでは、16列かつ900バイトの制限があります。

“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