by Hayato Matsuura.

索引構成表とクラスタ化インデックス


インデックスのみのスキャンは、 インデックスに保存された冗長なデータだけを使ってSQL文を実行することです。ヒープテーブル上にある元のデータは 必要ありません。この考え方を次のレベルに持ち込んで、全ての列をインデックスに入れることを考えると、なぜヒープテーブルが必要なのかという 疑問に行きつきます。

いくつかのデータベースにおいては、一次的なテーブルの保存先として インデックスを使用できます。Oracleではこのコンセプトを索引構成表 (IOT)と呼び、その他のデータベースではクラスタ化インデックスと言います。 この節では、どちらの単語も、必要に応じてテーブルあるいはインデックスの特徴を強調するものとして使います。

索引構成表は、ヒープテーブルを持たないBツリーインデックスのことです。これには、次の2つの利点があります。(1) ヒープ構造の分の容量が節約できます。 (2) クラスタ化インデックスへの全てのアクセスが、自動的にインデックスのみのスキャンになります。どちらも 頼もしい利点ではありますが、実際には実現の難しい問題です。

協力してください

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

索引構成表の不利な点は、同じテーブルに別のインデックスを作ろうとした時に明らかになります。通常のインデックスと同じように、 いわゆるセカンダリインデックスは、クラスタ化インデックスに保存された元のテーブルデータを 参照します。そこでは、データはヒープテーブル内に静的に保存されていますが、インデックスの順序を変えるためにいつでも動かせるようになっています。 そのため、索引構成表のセカンダリインデックスには、行の物理的な位置を 保存することができないのです。データベースは、代わりに論理キーを使います。

次の図は、2012年5月23日の全ての売上を検索するため、インデックスの走査を行う場合の処理を表しています。比較のために、 まずヒープテーブルを使う際の動きの図を見てみましょう。実行計画は、 (1) INDEX RANGE SCAN、(2) TABLE ACCESS BY INDEX ROWIDの2つのステップからなっています。

図インデックスを使用したヒープテーブルへのアクセス

テーブルアクセスがボトルネックになる可能性は高いかもしれませんが、 ROWIDがテーブル行への直接のポインタになっているので、 1行につき1回の読み込みで済んでいます。インデックスが位置を持っているので、データベースはすぐにヒープテーブルから該当の行をロードできるのです。 しかしその動きは、索引構成表でセカンダリインデックスを使おうとすると 一変します。セカンダリインデックスは物理的なポインタ(ROWID)を 持たず、クラスタインデックスのキーの値だけを保持します。これをクラスタリングキーと言います。多くの場合は、 これが索引構成表のプライマリキーになります。

セカンダリインデックスがROWIDを持たない理由

セカンダリインデックスにとっては、テーブルの行への直接のポインタが あった方が好ましいと言えます。しかしそれは、行が保存してあるストレージ上の 場所が変化しない場合にのみ可能です。残念ながら、行がインデックスの一部であり、 順番通りに並べる必要がある場合には、不可能になります。インデックスの順番を正しく保つためには、行を逐一動かす必要があるからです。例えば、 insert句は、新しいエントリ用に空き容量を確保するため、 リーフノードを分割することがあります。これにより、いくつかのエントリは違う場所の新しいデータブロックに移動されることになります。

一方でヒープテーブルを使う場合、行を順番に並べておく必要はありません。 データベースは、空いている場所がありさえすればどこにでも新しいエントリを保存します。 一度書き込まれたら、ヒープテーブル内ではそのデータは移動しません。

セカンダリインデックスにアクセスしてもROWIDは得られませんが、 クラスタインデックスを検索する際の論理キーになります。クラスタインデックスの 検索は、1度のアクセスでは不十分で、ツリーの走査が必要になってしまいます。 つまり、セカンダリインデックスを使ってテーブルにアクセスすると、2つのインデックスを 検索することになります。まずセカンダリインデックスで検索し(INDEX RANGE SCAN)、 続いてセカンダリインデックスで見つけた各行をクラスタインデックスで検索します(INDEX UNIQUE SCAN)。

図索引構成表のセカンダリインデックス

を見れば、 セカンダリインデックスとテーブルデータの間にクラスタインデックスのBツリーがあることが分かります。

セカンダリインデックスを使って索引構成表にアクセスするのは 非常に非効率ですが、ヒープテーブルを使う場合と同じ方法で、テーブルへのアクセスを抑えることができます。インデックスのみのスキャンを使えば いいのです。この場合は、「セカンダリインデックスのみのスキャン」とでも言えば良いかもしれません。アクセス回数だけでなくINDEX UNIQUE SCANも抑えられるので、インデックスのみのスキャンのパフォーマンス上の優位性はより大きくなります。

重要

セカンダリインデックスを使って索引構成表にアクセスするのは、非常に非効率です。

次の例から、データベースの持つ冗長性が見えてきます。セカンダリインデックスは、 各インデックスのエントリに対するクラスタリングキーを保存していることを忘れないでおきましょう。これにより、索引構成表にアクセスせずに、 セカンダリインデックスからクラスタリングキーを参照できます。

SELECT sale_id
  FROM sales_iot
 WHERE sale_date = ?
-------------------------------------------------
| Id | Operation        | Name           | Cost |
-------------------------------------------------
|  0 | SELECT STATEMENT |                |    4 |
|* 1 |  INDEX RANGE SCAN| SALES_IOT_DATE |    4 |
-------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("SALE_DATE"=:DT)

テーブルSALES_IOTは、SALE_IDを クラスタリングキーに持つ索引構成表です。インデックスSALE_IOT_DATESALE_DATE列にだけ作られていますが、クラスタリングキーである SALE_IDのコピーも持っているため、セカンダリインデックスだけでクエリの結果を返すことができます。

他の列を選択しようとすると、各行についてクラスタリングインデックスのINDEX UNIQUE SCANを実行する必要があります。

SELECT eur_value
  FROM sales_iot
 WHERE sale_date = ?
---------------------------------------------------
| Id  | Operation         | Name           | Cost |
---------------------------------------------------
|   0 | SELECT STATEMENT  |                |   13 |
|*  1 |  INDEX UNIQUE SCAN| SALES_IOT_PK   |   13 |
|*  2 |   INDEX RANGE SCAN| SALES_IOT_DATE |    4 |
---------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("SALE_DATE"=:DT)
   2 - access("SALE_DATE"=:DT)

索引構成表とクラスタ化インデックスは、パッと見よりも便利なものではないことが 分かったのではないでしょうか。セカンダリインデックスを使うと、クラスタ化インデックスのパフォーマンス上の優位点は、あっさりと崩れ去ってしまいます。 クラスタリングキーは、大抵の場合ROWIDよりも長いので、 ヒープテーブルにあった時よりもセカンダリインデックスは長くなってしまい、ヒープテーブルが減って空いた領域を使い尽くしてしまいます。 通常、索引構成表とクラスタ化インデックスが強みを発揮するのは、セカンダリインデックスを使わない時に限られます。ヒープテーブルには、 簡単に参照でき、固定されたマスタコピーを提供してくれるという利点があります。

重要

1つのインデックスしか持たないテーブルは、クラスタ化インデックスあるいは索引構成表にぴったりです。

複数のインデックスがあるテーブルは、ヒープテーブルの方が向いているでしょう。 テーブルアクセスを減らすために、インデックスのみのスキャンを使うこともできます。 これにより、他のインデックスの速度を落とさずに、クラスタ化インデックスのselectのパフォーマンスを得ることができます。

データベースの索引構成表とクラスタ化インデックスのサポート状況は、非常に一貫性がない状況になっています。以下の一覧は、特に重要な仕様です。

DB2

DB2では索引構成表は使えませんが、「クラスタ化インデックス」という単語は 別の意味で使われています。この単語が表す機能では、ヒープテーブルを使用しますが、新しくinsertする行を、インデックス上でなるべく近い 行と同じブロックに保存します。

MySQL

MyISAMエンジンではヒープテーブルのみを使い、InnoDBエンジンは常にクラスタ化インデックスを使います。 つまり、クラスタ化インデックスに関する直接的な選択肢はありません。

Oracle

Oracleでは、デフォルトでヒープテーブルを使います。索引構成表は、ORGANIZATION INDEX句を使って 作ることができます。

CREATE TABLE (
   id    NUMBER NOT NULL PRIMARY KEY,
   [...]
) ORGANIZATION INDEX

Oracleでは、プライマリキーが常にクラスタリングキーになります。

PostgreSQL

PostgreSQLでは、ヒープテーブルのみを使います。

しかし、CLUSTER 句を使うと、ヒープテーブルの中身をインデックスに合わせて並べ替えられます

SQL Server

デフォルトでは、SQL Serverはプライマリキーをクラスタリングキーとして、 クラスタ化インデックス(索引構成表)を使います。一方で、一意でない 列も含め、任意の列をクラスタリングキーとして使うこともできます。

ヒープテーブルを使うには、プライマリキーの定義時に NONCLUSTERED句を使う必要があります。

CREATE TABLE (
   id    NUMBER NOT NULL,
   [...]
   CONSTRAINT pk PRIMARY KEY NONCLUSTERED (id)
)

クラスタ化インデックスを削除することで、テーブルがヒープテーブルを使うようになります。

SQL Serverのデフォルト動作は、セカンダリインデックスを使う際に、 パフォーマンス上の問題の原因になることがあります。

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

著者について

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