13.2.2 インデクス構成列の検討
- 〈この項の構成〉
(1) 探索条件を満たすデータだけに絞り込む場合
(a) インデクス構成列の組み合わせ
探索条件を満たすデータだけに絞り込む場合は,探索条件に指定する列にインデクスを作成してください。AND演算子を使用して,複数の探索条件を満たすデータだけに絞り込む場合は,探索条件に指定する複数の列に対して,一つのインデクスを作成してください。すべての探索条件を一つのインデクスで絞り込む方が,効率が良いです。複数の探索条件を一つのインデクスで絞り込む例を次に示します。
- (例)
-
-
SQL文
SELECT * FROM TBL1 WHERE C1 = 10 AND C2 = 20 AND C3 = 30
-
インデクス定義
CREATE INDEX IDX1 ON TBL1(C1, C2, C3)
図13‒3 複数の探索条件を一つのインデクスで絞り込む例
-
[注意事項]
-
AND演算子を使用して,複数の探索条件を満たすデータだけに絞り込む場合に,探索条件のそれぞれの列に単一列インデクスを作成しても,どれか一つのインデクスしか使用しません※。インデクスに含まれない列の条件は,行データを参照して評価します。次に例を示します。
- (例)
-
-
SQL文
SELECT * FROM TBL1 WHERE C1 = 10 AND C2 = 20 AND C3 = 30
-
インデクス定義
CREATE INDEX IDX1 ON TBL1(C1)
CREATE INDEX IDX2 ON TBL1(C2)
CREATE INDEX IDX3 ON TBL1(C3)
図13‒4 探索条件のそれぞれの列に単一列インデクスを作成した場合
-
- 注※
-
SQL最適化オプションの指定内容によっては,インデクスを複数使用することがあります。ただし,一つの複数列インデクスを使用する方が効率が良いです。
(b) インデクス構成列の順序
複数列インデクスの場合,第1構成列は,必ず「=」条件を指定する列にします。また,「=」条件を指定する場合が多い列ほど先に指定します。これによって,インデクス内の検索範囲を小さくできるため,インデクス内の検索時間を短縮できます。
インデクス構成列順序による検索範囲の違いについて,次に例を示します。
- (例1)
-
「=」条件を指定した列が第1構成列である場合
-
SQL文
SELECT * FROM TBL1 WHERE C1 = 'B' AND C2 BETWEEN 10 AND 50
-
インデクス定義
CREATE INDEX IDX1 ON TBL1(C1,C2)
図13‒5 「=」条件を指定した列が第1構成列である場合
-
- (例2)
-
「=」条件を指定した列が第1構成列ではない場合
-
SQL文
SELECT * FROM TBL1 WHERE C1 = 'B' AND C2 BETWEEN 10 AND 50
-
インデクス定義
CREATE INDEX IDX2 ON TBL1(C2,C1)
図13‒6 「=」条件を指定した列が第1構成列ではない場合
-
「=」条件を指定した列については,インデクス構成列の順序を探索条件の指定順序と一致させる必要はありません。次に例を示します。
- (例)
-
-
SQL文
SELECT * FROM TBL1 WHERE C3 = 10 AND C1 = 20 AND C2 = 30
-
インデクス定義
CREATE INDEX IDX1 ON TBL1(C2, C3, C1)
-
- [説明]
-
インデクス構成列の順序が(C3, C1, C2)である必要はありません。
[注意事項]
-
「=」条件を指定した場合でも,フラグやステータス情報を設定する列のようにデータの重複度が高い列は,インデクス構成列には適しません。詳細は,「インデクス作成に適さない列」を参照してください。
(2) 表を結合する場合
(a) インデクス構成列の組み合わせ
表を結合する場合は,結合列にインデクスを作成してください。結合列にインデクスを作成することで,表の結合方法を効率の良いNESTED LOOPS JOINにできます。
-
結合列が複数ある場合は,すべての結合列を含んだ複数列インデクスを作成してください。すべての結合列がインデクス構成列に含まれていないと,NESTED LOOPS JOINを効率良く処理できません。詳細は,マニュアル「HiRDB Version 9 パフォーマンスガイド」の「効率の悪いNESTED LOOPS JOINの対策」を参照してください。
-
NESTED LOOPS JOINでは,内表の結合列に作成したインデクスを利用します。内表に探索条件が指定されている場合は,結合列に加えて,探索条件に指定した列をインデクス構成列に含めてください。
結合列と探索条件に指定した列を含んだインデクスの例を次に示します。
- (例)
-
-
SQL文
SELECT * FROM TBL1 INNER JOIN BY NEST TBL2
ON TBL1.C1 = TBL2.C1 AND TBL1.C2 = TBL2.C2
WHERE TBL1.C1=10 AND TBL2.C3 BETWEEN 100 AND 500
-
内表TBL2のインデクス定義
CREATE INDEX IDX2 ON TBL2(C1,C2,C3)
-
外表TBL1のインデクス定義
CREATE INDEX IDX1 ON TBL1(C1)
図13‒7 結合列と探索条件に指定した列を含んだインデクスの場合
-
探索条件に指定した列がインデクスに含まれていない場合,探索条件は行データを参照して評価することになります。次に例を示します。
- (例)
-
-
SQL文
SELECT * FROM TBL1 INNER JOIN BY NEST TBL2
ON TBL1.C1 = TBL2.C1 AND TBL1.C2 = TBL2.C2
WHERE TBL1.C1=10 AND TBL2.C3 BETWEEN 100 AND 500
-
内表TBL2のインデクス定義
CREATE INDEX IDX2 ON TBL2(C1,C2)
-
外表TBL1のインデクス定義
CREATE INDEX IDX1 ON TBL1(C1)
図13‒8 探索条件に指定した列がインデクスに含まれていない場合
-
(b) インデクス構成列の順序
インデクス構成列の順序は,結合条件及び探索条件の指定方法によって決定してください。詳細は「探索条件を満たすデータだけに絞り込む場合」の「インデクス構成列の順序」を参照してください。
(3) 探索条件を指定し,かつORDER BY,GROUP BYを指定している場合
(a) インデクス構成列の組み合わせ
探索条件に使用する列に加えて,ORDER BY,GROUP BYに指定する列をインデクス構成列に含めると,HiRDBが実行するソート処理を省略できることがあるため,効率良く処理ができます。ソート処理を省略できる条件については,マニュアル「HiRDB Version 9 コマンドリファレンス」の「アクセスパス表示ユティリティ(pdvwopt)」の「ORDER BY,GROUP BYを指定した検索に使用する表のインデクス定義」を参照してください。
(b) インデクス構成列の順序
探索条件として指定する列,グループ分け又はソートする列の順で構成する複数列インデクスを作成します。
次に例を示します。
- (例)
-
-
SQL文
SELECT * FROM TBL1 WHERE C3 = 10 AND C1 = 20
ORDER BY C4 DESC,C2 ASC
-
インデクス定義
CREATE INDEX IDX1 ON TBL1(C3 ASC, C1 ASC, C4 DESC, C2 ASC)
注
インデクス構成列ごとのASC,DESCの指定は,ORDER BYの指定と合わせてください。
-
(4) 一つの表に作成した複数のインデクスの構成列が,重複している場合
SQLごとにインデクスを検討した結果,一つの表に対して複数のインデクスを作成することがあります。しかし,インデクスの数が多いと性能に影響を与えることがあります。詳細は,「インデクスの数が性能に与える影響」を参照してください。
そこで,構成列が重複しているインデクスが複数ある場合は,一つのインデクスに統合できるか検討してください。インデクスを統合する場合,性能を優先したいSQLの条件に指定した列が,インデクスの第1構成列から連続する順序になるようにしてください。次に例を示します。
- (例)
-
-
SQL文1(性能を優先したいSQL)
SELECT * FROM TBL1 WHERE C3 = 10 AND C1 = 20
-
SQL文2
SELECT * FROM TBL1 WHERE C3 = 10 AND C2 = 30
-
インデクス定義
CREATE INDEX IDX1 ON TBL1(C3, C1, C2)
図13‒9 SQL文1を検索する場合 図13‒10 SQL文2を検索する場合
どちらのSQLの性能も優先したい場合は,それぞれのSQLに合わせたインデクスを作成してください。次に例を示します。
- (例)
-
-
SQL文1
SELECT * FROM TBL1 WHERE C3 = 10 AND C1 = 20
-
SQL文2
SELECT * FROM TBL1 WHERE C3 = 10 AND C2 = 30
-
インデクス定義
CREATE INDEX IDX1 ON TBL1(C3, C1)
CREATE INDEX IDX2 ON TBL1(C3, C2)
-
-
[注意事項]
-
インデクスの追加や,構成列を変更すると,SQLのアクセスパスが変わることがありますので,その表を操作するSQLのアクセスパスを再確認してください。