索引を使うとなぜパフォーマンスが良くなるのか

１．次のようなSQL文による問い合わせが実行されると……
　　SELECT ename,job,deptno
　　FROM employee
　　WHERE ename='SCOTT';　←条件で行を選択

２．索引がなければ、表の行データを含むすべてのブロックがメモリに読み込まれ、該当の行が探索される（図3-3）

３．索引があって使用される場合、索引を使って行が存在するブロックを特定し、該当ブロックのみがメモリに読み込まれる（図3-4）

索引を使うと常に速い？

索引がある場合とない場合の処理の違いを確認しました。
索引を使用すると常に最も速い検索ができるわけではないことがわかったと思います。
違いを、以下にもう少し解説します。

2.と3.を比較して、アクセスするブロック数が少ない方が、パフォーマンスはよさそうであることがわかるでしょう。
実際には、発生する物理Ⅰ/0数、メモリ上でアクセスするブロック数が少ないほどパフォーマンスはよいということができます。

各種DBでは、SQL文をチェックした後、最適なアクセスパスを選択するため、オプティマイザと呼ばれる機能を使って複数のアクセスパスを比較し、最適な解を導き出します。
DB設計者は、このアクセスパスの選択肢を適切なものにできるよう、適切な種類のインデックスや表を作成する必要があります。
その指針として、次のような点が挙げられます。

• １，SQL文のWHERE条件句で使用される列で、対象となる行を絞り込むために使用される列に索引を作成する
• ２，1.において、WHERE条件旬で対象となる行の絞り込みが有効な列（一意性が高い、カーディナリティが高いという）に
  索引を作成する
• ３，一意性を高くするために、列を組み合わせた複合列に索引を作る

一方、小さい表をアクセスする場合は索引を使用しない場合がほとんどです。
Oracleでは、パフォーマンスをよくするために、「表全体を読み込む」というアクセスパスが選択された場合、複数のブロックをまとめて1回のIOで読み込む「マルチブロック読み込み」を行います。
ディスクから読み込みを行う場合、1回のIOで複数のブロックをまとめて読み込むことができます。
初期化パラメータ（db_file_multiblock_read_count）で設定します。
デフォルトは8ブロックです。

• 索引を使用したときは1ブロック単位の読み込み
• 全表検索を選択したときはマルチブロック読み込み

索引を使用しても、アクセスするブロック数（索引+表）が多ければ、かえって全表検索の方が効率がよい場合もあります。
したがって、下記のようなケースはB*tree索引は有効ではないということができます。

• 小さい表を検索する場合
• 一意性が高くないため、たくさんの行がヒットする（アクセスするブロック数が増える）場合

索引作成の目的

索引を作成する目的としては、これまでに紹介した検索処理の高速化を含め、次の2つを挙げることができます。

• １．検索処理の高速化
• ２．データの一意性を保証データの一意性を保証

データの一意性を保証するために、Oracleデータベースは、列または列の組み合わせ（複数列）に対して、Primary Key制約またはUnique制約をつけると、自動的に一意索引を作成します。
そして、DML文（INSERTまたはUPDATE）が発行されると、索引を使用して、「挿入または更新後の列借」と「既存の索引のソートされた列値」が比較され、同じ値がないかどうかを高速に確認できるようになっています。
列値がソートされていないと、値を比較する際に、最初から最後まで検索する必要があり、検索に時間がかかることになります。

B*treeインデックスの特徴

B*treeインデックスの特徴から、どのように使用すべきかをまとめます。

• where句の条件式で頻繁に使用される列または列の組み合わせに作成する
• 一意性が高い列または列の組み合わせに有効
  等価条件が最も有効ということができるが、範囲検索でも指定した値の一意性が高ければ効果的
• 小さい表では使用されない
• not検索、null値権索、or条件を多用するような検索では使用されない
• sort対象の列、結合列に作成すると有効
• 更新によって、索引の構造が崩れてしまうと効率が悪くなる

一意性を高くするために

1列のみでは一意性が高くない場合でも、複数列を組み合わせると一意性が高くなる場合、複数列の組み合わせで索引を作成することを検討します。

一般的に、条件検索の値を指定した場合に、該当する検索対象の行が表全体の行の2％〜4％未満になる場合、その列に索引を作成するのは有効だといわれます。
一方、5％以上15％未満になる場合は、テストしてみます。
特に、SELECT句で問い合わされている列が索引の一部に組み込まれていて、値を取得するのに表へのアクセスが必要ない場合、パフォーマンスは非常に良くなります。
同じ表の複数列に索引を別々に作成していた場合、1つの複合索引にできないかを検討してみましょう。

ただし、複合索引を作成する場合、索引に指定する列の順序がパフォーマンスに影響を与えるので、その点は十分考慮してください。

• 列で定義されたデータ型！＝指定された値のデータ型

< 前へ　｜　3.3 論理設計の手順（2/4）　｜ 次へ >