複合インデックスの順番の設定について - SQL Server

複合インデックスの順番の設定について紹介します。

概要

非クラスタ化インデックスでは、複数の列をインデックスに設定することができます。この時にインデックスに設定する列の順番を指定できますが、どのように設定したら良いかのTipsを紹介します。

順番

最初の項目ほど足きりが多くできる列を指定します。また、検索に最もよく利用される列を指定します。

検索によく利用される列から指定する

複合インデックスの場合、最初の項目の列に指定した列がクエリで使われていない場合にはインデックスは使われない動作になります。
下記のテーブルを例にします。

Productsテーブル

商品グループ	商品カテゴリ	商品名	価格
ダイニング	椅子	ゆったりチェア	48600
リビング	椅子	ふかふかソファ	125400
ダイニング	テーブル	しんぷるテーブル	89600

複合インデックスを作成し列の順番を 「商品グループ」「商品カテゴリ」「商品名」の順番にします。この時下記のSQLクエリではインデックスは使われません。複合インデックスの最初の列である「商品グループ」がクエリの条件式に含まれていないためです。

select * From Products where 商品名='しんぷるテーブル'

一方、下記のSQLはインデックスが利用されます。「商品グループ」の列名は複合インデックスの最初の項目の列に設定されているためです。

select * From Products where 商品グループ='ダイニング'

また、下記のSQLを実行した場合、商品グループの条件を満たすレコードの検索にはインデックスが使われますが、商品名と一致するレコードの検索にはインデックスが使われません。これは、インデックスの2番目の項目「商品カテゴリ」がクエリに含まれていないため、「商品名」の検索にはインデックスが使われない動作になります。

select * From Products where 商品グループ='ダイニング' and 商品名='しんぷるテーブル'

上記の挙動から、検索クエリでなるべく多く使われる列名を複合インデックスの最初の項目に設定するのが良いです。

足きりが多くできる列から指定する

足きりが多くできる列を最初に指定します。下記のテーブルを例に考えます。

Productsテーブル

商品グループ	商品カテゴリ	商品名	価格
ダイニング	椅子	ゆったりチェア	48600
リビング	椅子	ふかふかソファ	125400
ダイニング	テーブル	しんぷるテーブル	89600
リビング	その他	もふもふクッション	4200
ベッドルーム	ベッド	あっさりベッド	78000
スタディ	テーブル	ちいさめデスク	56400
スタディ	椅子	のびのびチェア	22400
リビング	テーブル	ちいさな丸テーブル	36500

下記のクエリが実行される場合、条件を満たさないレコードを一番多く落とせるものを最初のインデックスに設定します。上記のテーブルで下記のクエリを実行する場合であれば、「商品名」であればレコード1つに、「商品グループ」であればレコードを2つに、「商品カテゴリ」であればレコード3つに絞り込まれます。より多く絞り込めるのは「商品名」の列になりますので、「商品名」を複合インデックスの最初に指定すると良いです。

select * From Products where 商品名='ちいさめデスク' and 商品グループ='スタディ' and 商品カテゴリ='椅子'

「商品カテゴリ」「商品グループ」「商品名」の順番で複合インデックスを作成し、下記のSQLを実行すると、インデックスを用いて椅子のカテゴリのレコードを抽出します。上記のテーブル例では3つのレコードが取り出されます。その後テーブルのデータ領域にアクセスし、価格をチェックし 100000以上のレコードを取り出します。
一方「商品名」「商品グループ」「商品カテゴリ」の順番で複合インデックスを作成した場合は、最初のインデックス項目である「商品カテゴリ」がクエリにないためインデックスは利用されません。インデックスを使わずにテーブルのデータ領域にアクセスし「商品カテゴリ」と「価格」の条件が一致するレコードを抽出します。

select * From Products where 商品カテゴリ='椅子' and 価格 > 100000

レコード数が少ない場合は、違いはほとんど出ませんが、レコード数が増えてくると、

• A:インデックスにより多くのレコード(n件)が条件に一致し、一致したレコードのテーブルのデータを読み込み条件に一致するか確認する
• B:テーブル全体のレコードを読み込み条件に一致するか確認する

AとBの動作を比較したとき、nが非常に大きい場合、 AよりBの処理速度が速くなる場合があります。これは、テーブルの全件走査はブロックリードができるためインデックスで条件にマッチしたレコードを逐次確認するよりも動作が速くなるためです。
一方でnが小さい場合は、インデックスを利用したAの処理速度のほうが速くなります。

上記の動作を回避するためにも、複合インデックスの最初の項目の列でなるべく多くのレコードを足切りできるように設定します。