インデックス使用基準(Index Usage Criteria)

インデックス使用基準(Index Usage Criteria)
どの適切なインデックスを作成すべきかを効率的に決定するには、これらのインデックスが実際にSQLサーバで使用されたかどうかを決定する必要があります.インデックスが有効に使用できない場合、データを変更すると、スペースが浪費され、不要な負担が増加します.
少なくともインデックスの最初の列が有効な検索パラメータ(search argument SANG)またはjoin句に含まれていない場合、SQL Serverはインデックスを使用してより効果的なブックマーク検索(bookmark lookup)を行わないことを覚えておいてください.
複合インデックスを作成するには、カラムの順序を選択するときに覚えておいてください.storeテーブルの次のインデックスを考えてみましょう.
Create index nc1_stores on stores (city, state, zip)
次の各クエリでは、インデックスの最初のカラムcityが含まれているため、インデックスが使用されます.これはSARSです.
 

 select stor_name from stores 
   where city = 'Frederick'
     and state = 'MD'
     and zip = '21702'

  

select stor_name from stores
   where city = 'Frederick'
     and state = 'MD'

 

 select stor_name from stores
   where city = 'Frederick'
     and zip = '21702'

ただし、次のクエリは、city列がSARGであることを指定していないため、インデックスを使用してブックマークを検索しません.

select stor_name from stores 
   where state = 'MD'
     and zip = '21702'

select stor_name from stores
   where zip = '21702'

参照
コメント:
前述した最後の2つのクエリについて、実行計画(execution plan)情報を表示すると、クエリが実際にnc 1_を使用していることがわかります.storeインデックスは、結果セット(resultset)を取得します.さらによく見ると、クエリはインデックス検索(index seek)ではなくインデックススキャン(index scan)を使用しているインデックスを最も効果的に使用していないことがわかります.
クエリー・アクセス・メソッドの詳細については、35章「Understanding Query Optimization」を参照してください.この章では、インデックス検索について説明します.
インデックス検索(Index seek)では、SQL Serverがインデックスツリーに沿ってルートレベル(root level)から指定した行が検索されるまでインデックスキー値マッチング検索を行い、インデックスキー値に格納されたブックマーク値(bookmark value)を使用してデータページからマッチングしたデータ行を直接取得します(このブックマーク値は行識別子(RID)でも構いません).、または集約インデックスのキー値).
インデックス・スキャン(Index scan)、SQL Serverは、インデックス・ツリー内のすべてのリーフ・レベル(leaf level)のローを検索して、一致する検索を行います.一致する行が見つかった場合、ブックマークを使用してデータ行を取得します.
両方ともインデックスが使用されていますが、I/Oコストの観点からインデックス検索よりもインデックススキャンの方がコストが高くなりますが、テーブルスキャン(Table scan)よりもやや小さくなります.ただし、本章ではインデックス検索の目的を学習しますので、インデックスの使用についてお話しするときはインデックス検索を指します.
可能な列のブックマーククエリーを取得するには、テーブル内のすべての列にインデックスを作成することで、どのタイプのクエリーでもインデックスを使用できます.このポリシーは、ad hoc queries(任意のクエリー)をサポートする読み取り専用DSS(意思決定支援システム)環境で適切である可能性があります.
しかし、この章のIndex selectionセクションでご覧のように、多くのインデックスが使用されないため、問題もあります.
カラムにインデックスが作成されたからといって、オプティマイザはカラムのインデックスを常に使用します.たとえば、カラムの選択性が足りない場合(not selective enough)、カラムのインデックスは使用しません.また、大きなテーブル(large table)上にインデックスを作成しすぎると、データベース内の多くのスペースを占有し、バックアップの必要時間が増加します.前述したように、1つのOLT P(オンラインオンラインオンライン処理)システムでは、インデックスが多すぎると、データの挿入、修正、削除操作に多くの追加負担をもたらし、性能上の不利な影響を及ぼします.
参照
推奨:(テーブルあたり4～5インデックス)
私がよく犯した設計エラーの1つは、OLTP環境でインデックスを定義しすぎたことです.多くの場合、冗長なインデックスやオプティマイザがクエリーを処理する際にまったく考慮していないインデックスがあります.その結果、これらのインデックスは空間の浪費とデータの修正時の不要な負担を増加させます.
この点では、あるお客様が1つのテーブルに8つのインデックスを作成し、4つのインデックスが同じカラムにあり、そのカラムのキー値が一意(unique key)であり、インデックス内のカラムが最初のインデックスカラムである場合があります.テーブルに対するクエリーおよび変更操作は、where句に含まれます.結果は4つのインデックスのうち1つのみが使用されました.
この章が終わると、これらのインデックスがすべて必要ではない理由を理解し、インデックスの作成を避けるべきカラムを再認識し、決定することができます.
自己総括:
1、インデックスはデータベースのクエリー速度を高める重要で有効な手段と方法である.
2、インデックスの作成と維持には代価が必要であるため、インデックスが多ければ多いほど良いわけではありません.
3、インデックスを作成しても、クエリー・オプティマイザは必ずしもインデックスを使用する必要はありません.クエリー・パラメータ(SARGs)が有効ではないか、インデックスを使用するためのコストがテーブル・スキャン(Table Scan)よりも大きい可能性があります.