津島博士のパフォーマンス講座　Indexページ ▶▶

 

皆さんこんにちは、今年も残暑が厳しいと思っていたら、急に涼しくなってきましたが体調はいかがでしょうか。
今回は、第6回の続きとして索引の設計方法などについて説明しようと思います。若手エンジニアと話す機会があり、索引の使い方や効果などをあまり知らないようだったので、具体的な例などを使用して説明しようと思います。このようなことは一概には言えませんが、多少の参考にはなると思いますので、参考にして下さい。

■ 1. 索引の決め方
まずは、索引（Bツリー索引）の決め方について説明しましょう。
索引はデータのアクセス性能を向上するための基本的な方法ですが、索引以外にも様々な方法（索引構成表、クラスタ化表、パーティション化、ビットマップ索引など）があります。それぞれには、メリットとデメリットがありますので、上手く使い分けないと効率の良いシステムにはなりません（例えば、大量の範囲スキャンをする場合には、パーティションは全表スキャンすることが可能ですが、索引はあまり効果がないなどです）。Bツリー索引以外については次回以降で説明しようと思います。
索引の設計を難しいと思われている方は多いのではないでしょうか。必要最小限の索引にするために（あまり効果のない索引は作らないために）、どのようなアクセスが最適かを考える必要があります（これが難しいため様々な索引の効果を実際にテストしているのが一般的です）。そして、作り過ぎないようにするには、SQLに優先順位を付けることも重要になります。このようなことが難しいと感じる要因だと思います。アドバイス機能（SQLチューニング・アドバイザ、SQLアクセス・アドバイザ）を使用すれば簡単に行えると思っているかもしれませんが、できれば基本を確りと身に付けて使用して欲しいですので、参考になることをまとめてみました。
ここでは、作成する索引を決めるための注意点（作成する列の候補、複数列索引、重複度の割合、参照整合性制約）について説明します。

（１）作成する列の候補
索引を作成する候補の列としては以下のようになりますので、このような列を求めることから始めます。
・頻繁にWHERE句の比較条件で使用されている選択率の低い列
比較条件の列に索引が作成されていると索引範囲スキャン（INDEX RANGE SCAN）が行われます（一意索引で等価条件の場合はINDEX UNIQUE SCANになります）。このアクセスする範囲は少ない方が性能が良いため選択率が低くなる列が効果的です（逆に選択率が高くなる列は、オプティマイザが選択しません）。カーディナリティの高い列は選択率が低くなりますが、範囲指定（<,>など）では選択率が高くなる場合があるので注意が必要です。
・頻繁に結合が行われている列
ネステッド・ループ結合では内部表（最初にアクセスしないテーブル）の結合列で索引を使用して結合しますので、結合列に索引がないと効率の良いアクセスにはなりません（この場合はハッシュ結合などが行われると思います）。結合する行数が少ない場合は、結合列に索引を作成してネステッド・ループ結合をする方が効率良いです。
・頻繁にソートされている列
索引はソートして格納されていますので、ORDER BYのようなソート処理にも効果があります（索引範囲スキャンや全索引スキャンするだけでソート結果になります）。ただし、NULLが存在する列のBツリー索引では効果がありません（使用されません）ので注意して下さい（Bツリー索引ではキー値にNULLは含まれないためです。ただし、複数列索引ではすべての列値がNULLの場合のみ含まれないようになります）。
・頻繁に更新されていない列
索引が作成されている列が更新されると索引も更新する必要がありますので、頻繁に更新されるとオーバーヘッドが大きくなり、断片化も発生し易くなるため効果が低下してしまいます。その断片化を解消するための再構築の頻度も多くなる可能性がありますので、作成しない方が良いです（作成しなくても良いようにデータベースを設計して下さい）。ただし、検索の性能が更新よりも優先度が高い場合もありますので、その場合のみ作成するとすれば良いでしょう。

（２）複数列索引
列の候補ができたら、次に複数列索引を作成するかどうかです。このときに、どの列で作成するかは悩むところですが、索引はできるだけ一意性が高いように作成した方が効率が良いので、1つの列で一意なのであれば単独で良いです（他の列を付けても無意味ですしキー長が長いと索引の効率が悪くなります）。そうでないものはできるだけ一意性になるような複数の列で作成することを検討します。このときの列の順番が問題ですが、第6回で説明したように、選択率の低い順と使用頻度の高い順（使用頻度が高くても、あまりにも索引の効果がない列は対象外にして下さい）に先頭から配置した方が効率が良いですが、クラスタ化係数の検討も忘れないようにして下さい。
・クラスタ化係数
クラスタ化係数とは、オプティマイザ統計の索引統計情報の一つです。テーブルのデータ格納順が索引とどれくらい異なるかを表す値で、低い値ほど同じ割合が高いことを意味します（この値が低いと、オプティマイザがこの索引を使用する優先順位を上げます）。このような列に索引を作成して、索引アクセスをすると範囲スキャンの物理I/Oを削減することができます（重複値が多いような場合は同じ値は隣接して格納されますので、同じブロックに格納される割合が高くなるからです）。そのため、テーブルにデータを格納するのが、ある列の値順になるような場合には（列でソートなどして格納するような場合には）、この列を先頭にした（クラスタ化係数の低い）索引の方がアクセス効率が良くなります。ただし、これは挿入や削除をして行くと効果が薄れていきます（この値が高くなっていきます）ので注意して下さい。このようなデータの格納を自動的に行うのがクラスタ化表や索引構成表です（これは次回以降で説明します）。

（３）重複度の割合
カーディナリティが低い場合は、Bツリー索引はあまり効果がないと言われていますが（ビットマップ索引の方が効果的と言われています）、重複度の割合が極端に異なると索引の効果がある場合があるので検討してみて下さい（例えば、以下のように値が1～5までは重複度が高いが、6～20までは一意性が高い場合などです）。このような場合は、重複度の低い値が条件に指定されたときは索引アクセス、重複度の高い値が条件に指定したときは全表スキャンが性能が良くなりますが、オプティマイザが効果的に選択してくれます（これはコストベース・オプティマイザの良さが出る良い例です）。ただし、バインド変数を使用している場合には、第7回で説明したOracle Database 11gからの「優れたカーソル共有」機能を使用しないと、このように動作しませんので注意して下さい。

（４）参照整合性制約
参照整合性制約は、親表と子表の関係をOracleデータベースが保障してくれる機能です（子表の外部キー値は親表の主キーに存在することを保障します）。これを使用すると、主キーには一意性を保証するために一意索引が作成されますが、外部キーには索引が作成されません。ただし、子表の外部キー列に索引が存在しないと、以下の図のように親表の主キーを更新することで子表を共有ロック（子表を処理する間）してしまいますので、他のトランザクションから更新できなくなります。そのため、大量に更新を行うようなシステムでは、子表の外部キーの索引を作成するか、または参照整合性制約を使用しないことを検討して下さい。
これは、親表をDELETEなどすると、それに対応する子表のデータを処理する必要がありますが、索引が存在しないと全表スキャンになるため、その間はアクセスさせないように共有ロックする必要があるからです。ON DELETE句（子表の外部キーの同じ値をNULLにするか、または行を削除する）が指定されていない場合でも、参照整合性制約の違反チェックをするために共有ロックが行われます（同じキー値が子表の外部キーに存在する場合は、主キーの削除などをできないようにする必要があるからです）。

img_tsushima_110119_01.jpg

■ 2. 組合せの効果

索引に効果がある処理が単独であれば、それに対応する索引を作成すれば良いのですが、索引は多く作成し過ぎるとオーバーヘッドになるため、少ない数の索引で最大限の効果を出すことがベストです。そのため、効果がある処理を組み合わせて索引を作ることも検討すると更に効率的です。ただし、単純に組み合わせても効果がない場合がありますので、ここでは索引の効果がある以下の処理の組合せの効果について説明します。知っている方には簡単かもしれませんが復習として確認してみて下さい。
・比較条件
・ORDER BY句
・GROUP BY句（DISTINCT句）
・結合
それでは、それぞれについて説明します。

（１）ORDER BY句と比較条件
ORDER BY句と比較条件が一緒の場合の索引の効果について考えてみましょう。次のようなWHERE句とORDER BY句があるSQL文は（列c1の選択率が1%未満で索引アクセスが効率良いとした場合）、どのような索引を作成すると効率良いか考えてみましょう。代表的な索引の候補①~③を使用して、どの索引が効率が良いか説明します。

SQL> SELECT c1,c2,c3 FROM tab01 WHERE c1 = 10 ORDER BY c2;
①	CREATE INDEX ix_tab01_1 ON tab01 (c1,c2);
②	CREATE INDEX ix_tab01_2 ON tab01 (c2,c1);
③	CREATE INDEX ix_tab01_3 ON tab01 (c1,c2,c3);

これは、①（または③）が効率が良いです。どうしてか分からない方のために、以下の実行計画を見ながら説明します。①と②の違いは、INDEX RANGE SCAN（索引範囲スキャン）かINDEX FULL SCAN（全索引スキャン）かですので、①のようにWHERE句の比較条件に指定している列を最初に指定すると索引範囲スキャンになり、アクセスするブロックが少なくなり効率良いことになります。③はアクセスするすべての列で索引を作成しているので、テーブルにアクセスする必要がなくなります。選択リストに指定している列が少なければ、これが最適なアクセスになりますので、このように作成して下さい。③は高速全索引スキャンになる場合もありますが、このSQL文では行われません（高速全索引スキャンは索引の順番に読込みませんので、SORT ORDER BY を行う必要があります。②の全索引スキャンは、すべてのリーフ・ブロックを順番に読込みますのでSORT ORDER BYは必要ありません）。

①の索引
実行計画
————————————————–
| Id  | Operation                   | Name       |
————————————————–
|   0 | SELECT STATEMENT            |            |
|   1 |  TABLE ACCESS BY INDEX ROWID| TAB01      |
|*  2 |   INDEX RANGE SCAN          | IX_TAB01_1 |
②の索引
実行計画
————————————————–
| Id  | Operation                   | Name       |
————————————————–
|   0 | SELECT STATEMENT            |            |
|   1 |  TABLE ACCESS BY INDEX ROWID| TAB01      |
|*  2 |   INDEX FULL SCAN           | IX_TAB01_2 |
③の索引
実行計画
————————————————–
| Id  | Operation         | Name       |
————————————————–
|   0 | SELECT STATEMENT  |            |
|*  1 |  INDEX RANGE SCAN | IX_TAB01_3 |
 
 

（２）GROUP BY句と比較条件
次に、GROUP BY句と比較条件がある場合について考えてみましょう。これも「（１）ORDER BY句と比較条件」と同じ索引の候補①~③を使用して、どの索引が効率が良いか説明します。

SQL> SELECT c2,COUNT(c1),COUNT(c3) FROM tab01 WHERE c1 = 10 GROUP BY c2;

① CREATE INDEX ix_tab01_1 ON tab01 (c1,c2);
② CREATE INDEX ix_tab01_2 ON tab01 (c2,c1);
③ CREATE INDEX ix_tab01_3 ON tab01 (c1,c2,c3);

以下の実行計画を見ると、ORDER BY句と同じような実行計画になりますが、GROUP BY句で索引を使用するとSORT GROUP BY NOSORTになり、ORDER BY句のように処理が無くなる訳ではないことが分かります（同じ値で集計をする必要があるからです）。GROUP BY処理がすべて無くなる訳ではありませんが、索引によりソート処理が必要なくなるのでORDER BY句と同じ効果になります。索引アクセスについても同様に①（または③）が効率良いことになります。

①の索引
実行計画
————————————————–
| Id  | Operation                    | Name       |
————————————————–
|   0 | SELECT STATEMENT             |            |
|   1 |  SORT GROUP BY NOSORT        |            |
|   2 |   TABLE ACCESS BY INDEX ROWID| TAB01      |
|*  3 |    INDEX RANGE SCAN          | IX_TAB01_1 |
②の索引
実行計画
————————————————–
| Id  | Operation                    | Name       |
————————————————–
|   0 | SELECT STATEMENT             |            |
|   1 |  SORT GROUP BY NOSORT        |            |
|   2 |   TABLE ACCESS BY INDEX ROWID| TAB01      |
|*  3 |    INDEX FULL SCAN           | IX_TAB01_2 |
③の索引
実行計画
————————————————–
| Id  | Operation                    | Name       |
————————————————–
|   0 | SELECT STATEMENT             |            |
|   1 |  SORT GROUP BY NOSORT        |            |
|*  2 |   INDEX RANGE SCAN           | IX_TAB01_3 |
 
 

これは次のようなDISTINCT句を使用しているSQL文の場合も同様にSORT UNIQUE NOSORTになります。

SQL> SELECT DISTINCT c1,c2 ,c3 FROM tab01 WHERE c1 = 10;
実行計画
—————————————————
| Id  | Operation                    | Name       |
—————————————————
|   0 | SELECT STATEMENT             |            |
|   1 |  SORT UNIQUE NOSORT          |            |
|   2 |   TABLE ACCESS BY INDEX ROWID| TAB01      |
|*  3 |    INDEX RANGE SCAN          | IX_TAB01_1 |

 

（３）結合と比較条件
次に、結合と比較条件が存在するSQL文の索引について考えてみましょう。ORDEREDヒントは、結合の順番を分かり易くするためです（FROM句の順番で結合するように指定しています）。内部表（この場合は”tab02”です）の代表的な索引の候補①~③を使用して、どの索引が効率が良いか説明します。

SQL> SELECT /*+ ORDERED */ A.c1,A.c2,B.c3 FROM tab01 A,tab02 B WHERE A.c1 = B.c1 AND B.c2 = 10 ;

①	CREATE INDEX ix_tab02_1 ON tab02 (c1,c2);
②	CREATE INDEX ix_tab02_2 ON tab02 (c2,c1);
③	CREATE INDEX ix_tab02_3 ON tab02 (c1,c2,c3);

これは結合方法がハッシュ結合かネステッド・ループ結合かで異なってきますが（ソート・マージ結合は「（４）結合とORDER BY句」で説明します）、ここではネステッド・ループ結合の場合を説明します（ハッシュ結合は、外部表のハッシュテーブルを作成して結合するため、結合列の索引は使用しません）。実行計画は以下になります（分かり易くするために実行計画にPredicate Information（述語の評価順序）を載せています。WHERE句が複数指定されているときに、どのような順番で評価されたかを確認することができます）。①と②で変化はありませんので、どちらでも問題ないことが分かります（これは複数列索引の順番の決め方に従えば良いと思います）。③はテーブル”tab02”にアクセスするすべての列で索引を作成しているので（テーブルにアクセスする必要がなくなるため）、他の場合と同じようにアクセスする列が少なければこれがベストになります。

①の索引
実行計画
————————————————–
| Id  | Operation                    | Name       |
————————————————–
|   0 | SELECT STATEMENT             |            |
|   1 |  NESTED LOOPS                |            |
|   2 |   NESTED LOOPS               |            |
|   3 |    TABLE ACCESS FULL         | TAB01      |
|*  4 |    INDEX RANGE SCAN          | IX_TAB02_1 |
|   5 |   TABLE ACCESS BY INDEX ROWID| TAB02      |
————————————————–
Predicate Information (identified by operation id):
————————————————–
   4 – access(“A”.”C1″=”B”.”C1″ AND “B”.”C2″=10)
②の索引
実行計画
————————————————–
| Id  | Operation                    | Name       |
————————————————–
|   0 | SELECT STATEMENT             |            |
|   1 |  NESTED LOOPS                |            |
|   2 |   NESTED LOOPS               |            |
|   3 |    TABLE ACCESS FULL         | TAB01      |
|*  4 |    INDEX RANGE SCAN          | IX_TAB02_2 |
|   5 |   TABLE ACCESS BY INDEX ROWID| TAB02      |
————————————————–
Predicate Information (identified by operation id):
————————————————–
   4 – access(“B”.”C2″=10 AND “A”.”C1″=”B”.”C1″)
③の索引
実行計画
————————————————–
| Id  | Operation                    | Name       |
————————————————–
|   0 | SELECT STATEMENT             |            |
|   1 |  NESTED LOOPS                |            |
|   2 |   TABLE ACCESS FULL          | TAB01      |
|*  3 |   INDEX RANGE SCAN           | IX_TAB02_3 |
 
 

参考のためにハッシュ結合の実行計画も載せておきます。結合列”c1”には索引が必要ないため以下の索引（ix_tab02_4）がベストになります。つまり、結合方法を意識して索引を作成する必要あるということです。

SQL> CREATE INDEX ix_tab02_4 ON tab02 (c2);
SQL> SELECT /*+ ORDERED */ A.c1,A.c2,A.c3 FROM tab01 A,tab02 B WHERE A.c1 = B.c1 AND B.c2 = 10 ;
実行計画
—————————————————
| Id  | Operation                    | Name       |
—————————————————
|   0 | SELECT STATEMENT             |            |
|*  1 |  HASH JOIN                   |            |
|   2 |   TABLE ACCESS FULL          | TAB01      |
|   3 |   TABLE ACCESS BY INDEX ROWID| TAB02      |
|*  4 |    INDEX RANGE SCAN          | IX_TAB02_4 |
—————————————————
Predicate Information (identified by operation id):
—————————————————
   1 – access(“A”.”C1″=”B”.”C1″)
   4 – access(“B”.”C2″=10)

 

（４）結合とORDER BY句
次に、結合とORDER BYについて考えてみましょう。これも代表的な索引の候補①~③を使用して、どのようになるか説明します。

SQL> SELECT /*+ ORDERED */ A.c1,A.c2,B.c3 FROM tab01 A,tab02 B WHERE A.c1 = B.c1 ORDER BY B.c2;

①	CREATE INDEX ix_tab02_1 ON tab02 (c1,c2);
②	CREATE INDEX ix_tab02_2 ON tab02 (c2,c1);
③	CREATE INDEX ix_tab02_3 ON tab02 (c1,c2,c3);

これもネステッド・ループ結合の実行計画を見ながら説明しましょう（③は「（３）結合と比較条件」と同様にテーブルにアクセスしないだけなので省略します）。索引を使用してネステッド・ループ結合を行う場合は、SORT ORDER BYを削除することはできないのが分かります。つまり、結合とORDER BY句を複合列索引にしても効果がないということです。②は複数列索引の先頭が結合列でないので全表スキャンになります。

 
①の索引
実行計画
—————————————————
| Id  | Operation                     | Name       |
—————————————————
|   0 | SELECT STATEMENT              |            |
|   1 |  SORT ORDER BY                |            |
|   2 |   NESTED LOOPS                |            |
|   3 |    NESTED LOOPS               |            |
|   4 |     TABLE ACCESS FULL         | TAB01      |
|*  5 |     INDEX RANGE SCAN          | IX_TAB02_1 |
|   6 |    TABLE ACCESS BY INDEX ROWID| TAB02      |
②の索引
実行計画
—————————————————
| Id  | Operation                | Name       |
—————————————————
|   0 | SELECT STATEMENT         |            |
|   1 |  SORT ORDER BY           |            |
|   2 |   NESTED LOOPS           |            |
|   3 |    TABLE ACCESS FULL     | TAB01      |
|*  4 |    TABLE ACCESS FULL     | TAB02      |
—————————————————
 

次のようなSQL文（USE_MERGEヒントはソートマージ結合を行わせるためのヒントです）でソート・マージ結合（結合列をソートして結合する）を行うと索引をソートに使用しますが、これは結合列とORDER BY句の列が一緒になるような場合だけになりますので、使用は限定されると思います。この例はINDEX FULL SCAN（全索引スキャン）なのであまり効果がありませんが、列c1の比較条件が追加されてINDEX RANGE SCANになるともっと効果があると思います。

SQL> SELECT /*+ USE_MERGE(A B) */ A.c1,A.c2,A.c3 FROM tab01 A,tab02 B WHERE A.c1 = B.c1 ORDER BY B.c1;
実行計画
—————————————————
| Id  | Operation                    | Name       |
—————————————————
|   0 | SELECT STATEMENT             |            |
|   1 |  MERGE JOIN                  |            |
|   2 |   INDEX FULL SCAN            | IX_TAB02_1 |
|*  3 |   SORT JOIN                  |            |
|   4 |    TABLE ACCESS FULL         | TAB01      |
—————————————————
Predicate Information (identified by operation id):
—————————————————
3 – access(“A”.”C1″=”B”.”C1″)
      filter(“A”.”C1″=”B”.”C1″)

 

■ 3. おわりに

今回は複数列索引（Bツリー索引）の設計について説明しました。また機会があれば他のことについても説明したいと思います。次回はBツリー索引以外について説明しますのでよろしくお願いします。質問をお待ちしています。 それでは、次回まで、ごきげんよう。
 

ページトップへ戻る▲ 

 

津島博士のパフォーマンス講座　Indexページ ▶▶