17.6.2 インデクス定義について
まず,会話型SQL実行ユティリティ(HiRDB SQL Executer)でインデクスの定義情報を調べます。
-
HiRDB SQL Executerがある場合
インデクスの一覧を取得したい場合には,HiRDB SQL ExecuterのINDEXESコマンドを使用します。
インデクス名からインデクス構成列名を取得したい場合には,INDEXCLMコマンドを使用します。
なお,GUI版HiRDB SQL Executerの場合は,[ディクショナリビュー]画面でも確認できます。[ディクショナリビュー]画面の参照方法については,マニュアル「HiRDB UAP開発ガイド」を参照してください。
詳細については,HiRDB SQL Executerのヘルプを参照してください。
-
HiRDB SQL Executerがない場合
HiRDB SQL Executerがない場合は,ディクショナリ表を検索して情報を取得します。ディクショナリ表の検索例を次に示します。なお,ディクショナリ表については,マニュアル「HiRDB UAP開発ガイド」を参照してください。
-
定義されているインデクスの一覧を取得したい場合は,ディクショナリ表のSQL_INDEXESを検索します。
SELECT INDEX_ID , INDEX_NAME , TABLE_NAME , UNIQUE_TYPE , VALUE(ARRAY_TYPE,' ') , COLUMN_COUNT FROM MASTER.SQL_INDEXES WHERE TABLE_SCHEMA LIKE USER ORDER BY 1
-
インデクス名からインデクス構成列名などを取得したい場合は,ディクショナリ表のSQL_INDEX_COLINFとSQL_COLUMNSを結合検索します。
SELECT Y.INDEX_ORDER , X.COLUMN_ID , X.COLUMN_NAME , VALUE(X.MAX_ELM,1) , Y.ASC_DESC FROM MASTER.SQL_COLUMNS X , MASTER.SQL_INDEX_COLINF Y WHERE X.TABLE_SCHEMA = Y.TABLE_SCHEMA AND X.TABLE_NAME = Y.TABLE_NAME AND X.COLUMN_NAME = Y.COLUMN_NAME AND Y.INDEX_NAME LIKE 'IDX1' AND Y.TABLE_SCHEMA LIKE USER ORDER BY 1
- 〈この項の構成〉
(1) インデクスの定義
(a) 表示の確認
-
Scan Typeの情報を確認してください。TABLE SCAN,又はAND PLURAL INDEXES SCANとなる場合で,性能が余り良くないときにはインデクスを定義している列をすべて含む,複数列インデクスを定義することを検討してください。
-
Index Nameの情報を確認して,意図したインデクスが使用されているかどうかを確認してください。
-
サーチ条件,キー条件から,インデクスが有効に絞り込まれているかどうか確認してください。インデクスの検索する範囲が有効に絞り込まれていない例を次に示します。
-
サーチ条件がない(サーチ条件にSearchCnd:NONE(FULL SCAN)が表示される)
-
サーチ条件の第1構成列の絞り込み範囲が,MIN〜MAXまでである(サーチ条件の絞り込み範囲の後ろに(FULL SCAN)が表示される)
-
インデクスのサーチする範囲が広い
-
(b) ベターユース
ANDを使用して,複数の述語を指定した検索をすることが多い表には,それぞれの述語の列を構成列とする複数列インデクスを定義します。この場合,=述語で組み合わせて指定することが多い列を,値の重複の少ないものから順にインデクス構成列として指定します。
(c) 理由
インデクスのサーチ範囲が狭くなるため,サーチ時間が短縮されます。
(d) 例
SELECT * FROM T1 WHERE C1=10 AND C2=30 AND C3 BETWEEN 10 AND 20 SELECT * FROM T1 WHERE C1=10 AND C2=30 AND C3 <= 15 SELECT * FROM T1 WHERE C1=20 AND C2=40 AND C4 = 60 ↓
T1(C1,C2,C3),T1(C1,C2,C4)のインデクスを定義します。
(e) 注意事項
-
インデクスの構成列数を増やし過ぎると,インデクス自体を検索する時間が長くなります。
-
列を更新すると,インデクスのメンテナンスが発生します。したがって,更新が多い列は,できるだけインデクス構成列に含めないようにしてください。
-
インデクスを定義する数はできるだけ少なくしてください。
(2) 結合検索に使用する表のインデクス定義
(a) 表示の確認
Join Typeがソートマージジョインで,大量データのソートがされていて性能が良くない場合,外表の探索条件が十分に絞り込めるのであれば,内表の結合列にインデクスを作成してネストループジョインにすると,効率的に検索できる可能性があります。
(b) ベターユース
制限条件で絞り込んだ表との結合検索に使用する表には,結合列を第1構成列とするインデクスを定義します。
結合条件を複数(n個)指定することが多い場合には,結合列が第1構成列から第n構成列となるインデクスを定義します。この場合,値の重複の少ない列を先に指定してください。さらに,結合条件以外の探索条件のある場合には,結合列の後ろ(第n+1構成列以降)に指定してインデクスを定義します。
(c) 理由
制限条件の指定された表を外表,結合列にインデクスの定義された表を内表としてネストループジョインをするため,結合にインデクスを使用でき,入出力回数を削減できます。
(d) 例
SELECT * FROM T1,T2 WHERE T1.C3=20 AND T1.C1=T2.C1 AND T1.C2=T2.C2 AND T2.C3>10 内表の結合列はT2(C1,C2) ↓
T2(C1,C2,C3)のインデクスを定義します。
(e) 注意事項
-
結合条件で外表に制限条件がない場合,通常はネストループジョインになりません。
-
内表,外表の両側に制限条件を指定している場合,ネストループジョインにならないことがあります。
-
ネストループジョインを優先的,又は強制的にしたい場合には,SQL最適化オプションの「ネストループジョイン強制」,又は「ネストループジョイン優先」を指定してください。詳細については,マニュアル「HiRDB UAP開発ガイド」を参照してください。
(3) 外結合時に使用するインデクス
(a) 表示の確認
-
外結合(LEFT OUTER JOIN)時に,意図したインデクスが使用されているかどうか確認してください。
(b) ベターユース
次のようなインデクスは,外結合時には使用されません。
-
FROM句中のON探索条件に指定した,外表の列に対するインデクス
-
WHERE句に指定した,内表の列に対するインデクス
インデクスを使用して検索するためには,外表の条件はWHERE句に,内表の条件はON探索条件に指定できるかどうか検討してください。
- 注 外結合時の外表,内表の区分を次に示します。
-
外表 LEFT OUTER JOIN 内表 ON …
WHERE …
(c) 理由
外表の列は,FROM句中のON探索条件の真偽に関係なく,すべての行が取り出されます。そのため,FROM句中のON探索条件に指定した外表の列に対する制限条件は,インデクスを使用した絞り込みをしません。
内表の列は,FROM句中のON探索条件を評価した後に,NULL値を補います。NULL値を補った後にWHERE句の条件を評価するため,WHERE句に指定した内表の列に対する制限条件は,インデクスを使用した絞り込みをしません。
(d) 例
SELECT * FROM T1 LEFT JOIN T2 ON T1.C1=T2.C1 and T1.C2='a' and T2.C2='b' WHERE T1.C3='c' and T2.C4='d' ↓
下線のある列は,インデクスを使用して評価します。
(4) ORDER BY,GROUP BYを指定した検索に使用する表のインデクス定義
(a) 表示の確認
1表の検索でORDER BY処理のためにソートをしている場合,インデクスが昇順,又は降順に作成されていることを利用して,ORDER BY列がソートされて出てくるようにインデクス定義を変更することで,ソート処理をなくすことができます。
(b) ベターユース
=述語(「列指定=値指定」だけ),又はIS NULL述語(「列指定 IS NULL」)の探索条件で絞り込んだ後,ORDER BY,又はGROUP BYを指定した1表の検索をする場合,次の二つの条件を共に満たすインデクスを定義してください。
-
=述語,又はIS NULL述語を指定した列指定が,第1構成列から第n構成列まで連続して指定されている。
-
GROUP BY,又はORDER BYで指定する列が,その順番通りに第n+1構成列以降連続して指定されている。
(c) 理由
インデクスを利用してアクセスするデータを絞り込み,更にソート処理を省略できるため,CPU時間,入出力時間を削減できます。
(d) 例
SELECT C2, C3 FROM T1 WHERE C1=10 AND C2= ? AND C3>10 ORDER BY C4 DESC,C5 ASC ↓
T1(C1 ASC,C2 ASC,C4 DESC,C5 ASC),又はT1(C1 ASC,C2 ASC,C4 DESC,C5 ASC,C3 ASC)のインデクスを定義します。
(e) 注意事項
次の条件のどれかを満たす場合,ソート処理をなくすことはできなくなります。
-
HiRDB/シングルサーバ,HiRDB/パラレルサーバ共通の制限
-
DISTINCTを指定している
-
探索条件によって,インデクスを使用しない検索を選択している
-
T1が分割されていて,サーバ内分割された非分割キーインデクス(Index Nameのインデクス属性が"d"のインデクス)は,ソート処理を省略できません。ただし,すべての分割キーに「列=値指定」を指定している場合を除きます。
-
-
HiRDB/シングルサーバの制限
T1が分割していて,CREATE TABLEで指定した分割キーがインデクス構成列の先頭から同じ順で含まれている場合(Index Nameのインデクス属性が"d"のインデクス),ソート処理を省略できません。ただし,すべての分割キーに「列=値指定」を指定している場合を除きます。
-
HiRDB/パラレルサーバの制限
-
INSERT文−SELECT文を実行するSQLである
-
副問合せ中である
-
集合演算を指定している
-
HAVING句を指定している
-
FOR UPDATE,FOR READ ONLYを指定している
-
T1が分割されていて,CREATE TABLEで指定した分割キーがインデクス構成列の先頭から同じ順で含まれていて(Index Nameのインデクス属性が"d"のインデクス),かつ同一バックエンドサーバのRDエリアを複数割り当てている場合には,ソート処理を省略できません。ただし,すべての分割キーに「列=値指定」を指定している場合を除きます。
-
(5) 集合関数MIN,MAXを指定した検索をする表のインデクス定義
(a) 表示の確認
GROUP BY指定がなく,選択式に集合関数のMIN(列),MAX(列)を指定した検索の性能が悪い場合には,集合関数中の列指定にインデクスを定義することで,インデクスの最小値,又は最大値だけを参照するだけで集合関数の値を求められるようになります。この場合,Group by Mode はIMPLICIT MIN-MAX INDEXとなります。
(b) ベターユース
次の二つのベターユースがあります。
-
探索条件を指定しない場合
集合関数のMIN,又はMAXのどちらか少なくとも一方を指定した検索に使用する表には,MIN,又はMAXを指定する列を第1構成列とするインデクスを定義してください。
-
=述語(又はIS NULL述語)の探索条件を指定する場合
=述語(又はIS NULL述語)の探索条件(n個)で絞り込んだ後に,集合関数のMIN,MAXを指定した検索に使用する表には,次の条件をすべて満たすインデクスを定義してください。
-
=述語(又はIS NULL述語)で指定した列指定が,第1構成列から第n構成列まで連続して指定する。
-
MIN,MAXに指定する列を第n+1構成列に指定する。
-
ほかの探索条件の列は,第n+2構成列以降に指定する。
-
(c) 理由
集合関数MIN,又はMAXに対しては,インデクスの最小値,又は最大値を参照して結果を得るので,入出力回数,CPU時間を削減できます。
(d) 例
-
探索条件を指定しない場合
SELECT MIN(C1), MAX(C1) FROM T1 ↓
T1(C1)のインデクスを定義します。
-
=述語(又はIS NULL述語)の探索条件を指定する場合
SELECT MIN(C1), MAX(C1) FROM T1 WHERE C2=10 AND C3=20 AND C4<30 ↓
T1(C2,C3,C1,C4)のインデクスを定義します。
(e) 注意事項
次の条件のどれかを満たす場合,IMPLICIT MIN-MAX INDEXにはなりません。
-
HiRDB/シングルサーバ,HiRDB/パラレルサーバ共通の制限
-
MIN,MAX以外の集合関数を指定している。
-
MIN,MAXが複数ある場合に,集合関数の引数の列が異なる。
-
GROUP BY句がある。
-
探索条件にシステム定義スカラ関数,関数呼出し,又はIS_USER_CONTAINED_IN_HDS_GROUPのどれかを含む。
-
探索条件にインデクス構成列以外の列を含む。
-
FLAT指定を,集合関数MAX又はMINの引数に指定している場合,探索条件に繰返し列を含む。
-
(b)のベターユースに該当するインデクスがない。
-
T1が分割されていて,サーバ内分割された非分割キーインデクス(Index Nameのインデクス属性が"d"のインデクス)を使用して検索しています。ただし,すべての分割キーに「列=値指定」を指定している場合を除きます。
-
-
HiRDB/シングルサーバの制限
-
T1が分割されていて,CREATE TABLEで指定した分割キーがインデクス構成列の先頭から同じ順で含まれている場合(Index Nameのインデクス属性が"d"のインデクス)はソート処理を省略できません。ただし,すべての分割キーに「列=値指定」を指定している場合を除きます。
-
T1が分割されていて,CREATE TABLEで指定した分割キーがインデクス構成列の先頭から同じ順で含まれるインデクス(Index Nameのインデクス属性が"d"のインデクス)を使用して検索しています。ただし,すべての分割キーに「列=値指定」を指定している場合を除きます。
-
-
HiRDB/パラレルサーバの制限
-
集合演算を指定している。
-
INSERT文−SELECT文を実行するSQLである。
-
HAVING句がある。
-
FOR READ ONLYを指定している。
-
T1が分割されていて,CREATE TABLEで指定した分割キーがインデクス構成列の先頭から同じ順で含まれるインデクス(Index Nameのインデクス属性が"d"のインデクス)を使用して検索していて,かつ同一バックエンドサーバのRDエリアを複数割り当てています。ただし,すべての分割キーに「列=値指定」を指定している場合を除きます。
-
(6) NULL値を除外キーとするインデクスの定義
(a) 表示の確認
次の項目が該当しないか確認してください。
-
NULL値の重複度が高いインデクス構成列に対して,サーチ条件種別がIS NULLとなる検索をしていて性能が悪い。
-
NULL値の挿入,NULL値への更新,NULL値から別の値への更新,NULL値の行の削除をしていて性能が悪い。
(b) ベターユース
NULL値を除外キーとするインデクスを定義します。
(c) 理由
NULL値を除外キーとするインデクスを定義すると,NULL値を除いてインデクスを作成します。そのため,探索条件にIS NULLを指定した場合には,インデクスを使用しないでTABLE SCANとなります。ただし,IS NULL以外の述語(=,betweenなど)を指定した場合にはインデクスを使用します。
NULL値の件数が多く,インデクスを使用してデータページをランダムにアクセスしている場合には,NULL値を除外キーとするインデクスを定義してTABLE SCANにした方が高速に検索できるようになります。
また,NULL値をインデクスキー値から除外することで,インデクスメンテナンスのオーバヘッドとログ量を削減できます。
(d) 例
SELECT * FROM T1 WHERE C1 IS NULL ↓
NULL値を除外キーとするT1(C1)のインデクスを定義します。
SELECT * FROM T1 WHERE C1 IS NULL(→ インデクスを使用しません) SELECT * FROM T1 WHERE C1 ='a'(→ インデクスを使用します)
(7) その他
-
検索方法がTABLE SCANとなっている表で,排他待ちが多発している場合は,探索条件を指定する列にインデクスを定義すると,アクセスするデータを絞り込めるので排他待ちが少なくなる可能性があります。
-
インデクスを使用した検索時に排他待ちが多発している場合,探索条件を満たした行,又はキー値に対してだけ排他を掛ける無排他条件判定を指定できるかどうか検討してください。無排他条件判定については,マニュアル「HiRDB UAP開発ガイド」を参照してください。
-
検索方法がAND PLURAL INDEXES SCANとなっている場合に,複数のユーザが同時に同一表を更新すると,デッドロックになることがあります。この場合,システム定義のpd_work_table_optionオペランドを変更することを検討してください。pd_work_table_optionオペランドについては,マニュアル「HiRDB システム定義」を参照してください。
-
アクセスパス表示ユティリティの結果から,使用されていないインデクスがある場合は,削除することをお勧めします。不要なインデクスがあると,更新処理で余分な負荷が掛かったり,余分にデータベース容量が必要になったりします。