9.5 インデックスのチューニング
インデックスも，データベースアプリケーションの性能に大きな影響を与える要因の1つである。リレーショナルエンジンで最も効率的なクエリの実行計画を選択できるようにインデックスを設定し，そのインデックスをSQLステートメントのWHERE句に指定すれば，SQLステートメントは希望するデータに直接アクセスできるようになる。そのため，テーブルスキャンと比較してデータの検索性能が大幅に改善される。たとえば，正しくインデックスを選択するだけで，十数時間かかっていた処理が数分ですむこともある。
インデックス設計の検証およびチューニングを実施するまえに，インデックスの設計を再度検証し，十分に理解してもらいたい。
9.5.1 インデックス設計の検証
インデックス設計をチューニングするには，あらかじめテーブルにインデックスを定義しておく必要がある。もしテーブルにインデックスが定義されていなければ，最適となるようなインデックスを設定しなければならない。すでにインデックスが定義されているならば，定義されているインデックスがクラスタ化インデックスであるか非クラスタ化インデックスであるかを調べ，それが利用条件に適した選択であるか否かを検証する。
たとえば，クラスタ化インデックスを設定すると，テーブルの各行はクラスタリングキーのソート順に基づいて並べ替えられるため，行の移動が発生する。このため，挿入や更新処理が頻繁に発生する列にクラスタ化インデックスを設定することは適していない。仮に，キー値が2と5のデータ行が存在するところにキー値が3のデータ行を挿入する場合を考えてみよう。この場合，クラスタリングキーの値に従って順番に並ぶように，データ行が物理的に並べ替えられる。挿入しなければならないページに空き領域がなければ，さらにページ分割が発生する。更新の場合にも，更新後のデータ行が更新まえのデータ行より大きくなれば，空き領域を作成するために同じようにデータ行の再配置が必要となる。これらの処理のオーバーヘッドによって，性能は低下してしまう。
これに対して非クラスタ化インデックスの場合は，キー値の重複が多い列の範囲検索に設定するには向いていない。非クラスタ化インデックスのデータ行は，クラスタリングキーの値に基づいて物理的に並べられるわけではなく，単純に書き込まれた順に格納されている。したがって，同じキー値を持つデータ行や範囲内にあるキー値を持つデータ行が複数のページに分散して格納されていることもある。このような場合に範囲検索を実行すると，データページに対してランダムアクセスすることになり，性能が低下するおそれがある。もし，この場合にクラスタ化インデックスが設定されていたのであれば，データ行は物理的に同じページに格納されているはずなので，データページへのアクセスは最小限ですむ。たとえ，別のページに存在していたとしても，連続するページに格納されているので，結果的にシーケンシャルアクセスとなり，ランダムアクセスよりは効率的に検索することができる。
テーブルにどのようなインデックスが定義されているかを確認するには，sp_helpindexシステムストアドプロシージャを使用する。Fig.9-1に，pubs.authorsテーブルに対してsp_helpindexシステムストアドプロシージャを実行した結果を示す。
Fig.9-1 sp_helpindexの出力例
さらに，リレーショナルエンジンが最も効率的な実行計画を作成するための条件を備えているか否か，問題のクエリに指定されているインデックスは有用であるか否かについても，分析しなければならない。インデックスの有用性を判定するには，インデックス値の分布を知り，重要度に基づいてクエリを並べ替える必要がある。そして，最も重要なクエリあるいは多数のユーザーによって頻繁に利用される列に，インデックスを設定するのである。
また，FILL FACTOR値の設定についても検証しておきたい。1つのページにできるだけ多数のデータあるいはインデックスを格納するほうが，アクセスするページ数が減少し，バッファキャッシュに保存されている可能性が高くなり，性能的には好ましい。しかし，データの挿入が発生すれば，ページ分割が発生するおそれがあり，性能が低下することもあり得る。したがって，アプリケーション特性も考慮したうえで決定しなければならない。
設定されているインデックスが効果的に利用される可能性があるか否かを判断するためには，次のような点について検証する必要がある。
○インデックスが設定された列にNULL値が許可されていないか
NULL値が許されている列をインデックスとして定義しても，比較条件を満たさないので，その行は選択されない。ANSI規格は，NULL値を等号，不等号，大小などで比較できないものと定めている。
○一意の主キーにインデックスが定義されているか
○結合条件の列にインデックスが定義されているか○条件としてWHERE句に指定する機会が多い列にインデックスが定義されているか
○ORDER BY句あるいはGROUP BY句に指定する列にインデックスが定義されているか
○複合インデックスが適切に定義されているか
複合インデックスの場合，探索引数に複合インデックスの先頭にあるインデックス列を指定しなければ，インデックスは使用されない。できれば，先頭のインデックスに唯一無二の値を持つような列を指定するようにしたほうがよい。たとえば，次のようにする。
Create index idx1 on employee(division,
dev, emptype)
select * from employee where division
= 'accounting' ○
select * from employee where emptype
= 'temporary' ×○更新・挿入・削除などが中心となるテーブルにインデックスを多数定義していないか
オンライントランザクション処理などでは，インデックスは，できれば5箇所以下，多くても8箇所以下にできないかを検討する。テーブルに8箇所の非クラスタ化インデックスがあれば，最小9回の書き込みが実施されることになる。これに対して，DELETEステートメントを実行したあとでINSERTステートメントを実行する「not-in-place更新」の場合は，17回の書き込みが実施される可能性がある。
○検索処理中心のテーブルにインデックスが定義されているか
意思決定支援システムなど，挿入・更新・削除などが少ない検索中心のテーブルであれば，検索時に使用される列にインデックスを定義しておくほうがディスクI/O数が減少し，性能上は好ましい。
○インデックスの列長が長くないか
インデックス列長が短ければ，1ページに多くのインデックス列を格納することができる。それによって，インデックスページ数を少なくでき，読み込み回数を減らすことができる。また，キャッシュに保持されている可能性も高くなる。
○性別や都道府県名など，値のタイプが少ない列にクラスタ化インデックスを定義しているか
テーブル中の10〜15％以上の行が同じ値を持つ列にクラスタ化インデックスを設定すれば，データ行の並べ替えを最小限にとどめることができる。クラスタ化インデックスは，頻繁に並べ替えずにすむ列に設定することが望ましい。クラスタ化インデックスを設定するときには，(A)範囲検索の対象となる列，(B)多数の重複値を含む列，(C)ORDER BY句で頻繁に参照される列，(D)結合で参照される主キー以外の列，(E)結果セットをソートするために頻繁に利用される列，を選択するとよいだろう。行をソートするときには，一般的にtempdbデータベースの作業テーブルとのあいだでコピーが行われる。この処理はI/Oオーバーヘッドを増加させるうえ，tempdbデータベースにおけるI/Oとロックが競合してしまう危険性を伴う。逆に一本調子に挿入される順次キー列にクラスタ化インデックスを生成するのは避けたほうがよい。
○データ量が少ないテーブルにインデックスを定義していないか
1エクステント以下の小さなテーブルにインデックスを定義し，インデックスによるアクセスとテーブルスキャンによるアクセスとを比較すると，インデックスの場合はインデックスページとデータページとを読み込む必要がある。それに対してテーブルスキャンは，データページだけを読み込めばよい。
アクセスされるデータがテーブルの全データ量の10〜20％以下であれば，非クラスタ化インデックスは効率的といってよい。非クラスタ化インデックスは，(A)結果として1行のみを返す検索あるいはランダムな検索，(B)結合，(C)数行の範囲検索，のキーとなる列に設定するとよい。
SQL Server 7.0の場合は，上記の検証事項を1つ1つデータベース管理者がチェックしなくても，次項以降で説明するインデックスチューニングウィザードやクエリアナライザを利用すれば，手軽に検証することができる。
 |
Chapter 9 7/46 |  |
