部分インデックス: 特定の行に対してのみインデックスを作成する


Applies to
DB2No
MySQLNo
OracleNo
PostgreSQLYes
SQL ServerYes

ここまでは、に対してインデックスを作成する ことだけを取り上げてきました。どのにインデックスを 作成するかも、部分インデックス(PostgreSQL) あるいはフィルター選択されたインデックス(SQL Server) という機能で実現できます。

注意

Oracleでは、部分インデックスに対してユニークなアプローチを取って います。これについては、この節の内容を踏まえて、 次節で詳しく説明します。

部分インデックスは、以下の例にあるステータスコードのような定数を 使った、比較的よく使われるwhere句で便利です。

SELECT message
  FROM messages
 WHERE processed = 'N'
   AND receiver  = ?

このようなクエリは、キューのシステムで非常によく使われます。 クエリは、特定の受信者に向けた未処理のメッセージを全て取得します。 既に処理されたメッセージはほとんど必要ないと言っていいでしょう。もし 必要だとしても、プライマリキーのような別の基準でアクセスされることに なるはずです。

このようなクエリを最適化するには、2カラムに対するインデックスを 使います。このクエリだけを考えるなら、範囲条件は指定していないので、 インデックスの列の順番を気にする必要はありません。

CREATE INDEX messages_todo
          ON messages (receiver, processed)

このインデックスは目的を果たすのには十分なのですが、滅多に 検索されない行、つまり処理済みのメッセージの行を大量に含んでいます。 巨大なディスク容量を使っていたとしても、対数的スケーラビリティによって、 インデックスはクエリの実行を非常に高速にすることができます。

部分インデックスを使うと、インデックスが未処理のメッセージのみを 含むよう制限することができます。この定義の文法は意外にも簡単で、 where句を使うだけです。

CREATE INDEX messages_todo
          ON messages (receiver)
       WHERE processed = 'N'

このインデックスは、where句の条件を満たす行だけ からなります。今回の例では、インデックス上はPROCESSEDは 常に'N'になるので、クエリのwhere句から PROCESSED列の指定を外してしまうことすらできてしまいます。 これは2つの次元でインデックスのサイズを減らせたことを示しています。 より少ない行しか含まないという垂直方向と、列を減らしたという水平方向 です。

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

これで、インデックスは非常に小さくなります。キューのシステムの 場合、テーブルサイズが大きくなっていったとしても、インデックスのサイズは あまり変わらないということになります。インデックスは未処理のメッセージ しか含まないからです。

部分インデックスのwhere句は、任意の複雑さで 指定できます。関数に対しては、インデックスの定義と同様に、確定的な関数 しか使えないという、基本的な制限があります。SQL Serverでは、より制限が 厳しく、インデックス 述語内では関数もOR演算子も使用できません.

データベースは、クエリにwhere句が あれば常に部分インデックスを利用できます。

考えてみよう

以下のクエリに対する、考え得る最小のインデックスの特性は なんでしょうか?

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