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


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

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

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

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

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

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

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


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

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

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

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

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

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


図 5.3を見れば、 セカンダリインデックスとテーブルデータの間にクラスタインデックスの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のデフォルト動作は、セカンダリインデックスを使う際に、 パフォーマンス上の問題の原因になることがあります。

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

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