by Hayato Matsuura.

部分インデックス


ここまでは、に対してインデックスを作成する ことだけを取り上げてきました。どのにインデックスを 作成するかも、部分インデックス(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つの次元でインデックスのサイズを減らせたことを示しています。より少ない行しか含まないという垂直方向と、列を減らしたという水平方向 です。

協力してください

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

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

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

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

考えてみよう

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

SELECT message
  FROM messages
 WHERE processed = 'N'

著者について

Markus Winandの写真

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

彼の本

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

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

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

Amazonで購入
(印刷版のみ)

Do not use offset for pagination

Learn why

Visit my sibling!A lot changed since SQL-92!

Use The Index, Luke のカップは

ステッカー、コースター、本、コーヒーマグ。 学習に必要なものすべて。

今すぐ購入

Connect with Markus Winand

Markus Winand on LinkedInMarkus Winand on XINGMarkus Winand on Twitter
“Use The Index, Luke!” by Markus Winand is licensed under a Creative Commons Attribution-Noncommercial-No Derivative Works 3.0 Unported License.
法律上の通知 | お問い合わせ | 無保証 | 商標 | プライバシーとGDPR | CC-BY-NC-ND 3.0 license