DBのインデックスと複合インデックス

Q:インデックスとは?

データの並び順序のこと
初期状態では主キーに対してインデックスが割り当てられている。

Q:インデックスをつける意味は?

データ件数が多いテーブルの検索を早くすることができる。

例えば下記の社員テーブルに対し、社員コードにインデックスを付けた場合

社員テーブル(インデックス:無し)

社員コード	所属コード	社員名
1004	101	テスト太郎A
1002	102	テスト太郎B
1003	101	テスト太郎C
1001	101	テスト太郎D
1005	102	テスト太郎E

社員テーブル(インデックス：社員コード)

社員コード	所属コード	社員名
1001	101	テスト太郎D
1002	102	テスト太郎B
1003	101	テスト太郎C
1004	101	テスト太郎A
1005	102	テスト太郎E

この並び順をDBが保持する。
※「並び順」を保持するのであり、この並び替えを行ったテーブルを保持するわけでない

そして、下記のようなSQLを実行したとき、

select * from 社員テーブル where 社員コード = 1003

・「社員テーブル(インデックス:無し)」のとき
データの並び順が決まっていないため、全件検索し該当レコードを探す
・「社員テーブル(インデックス：社員コード)」のとき
社員コード列が昇順に並んでいるので、社員コード列に対して二分探索のようなアルゴリズムでの検索を行い該当レコードを探す

インデックスは検索を効率よく行う仕組みのため、テーブルの件数が多いほどインデックスの有無による速度差は大きくなる。
逆に、テーブル件数が少ない場合はそこまで差は出ない。

Q:じゃあ複合インデックスって?

複数の並び替え条件でインデックスを作ること。

例えば、所属コードと社員コードでインデックスを作った場合

社員テーブル(インデックス：所属コード,社員コード)

社員コード	所属コード	社員名
1001	101	テスト太郎D
1003	101	テスト太郎C
1004	101	テスト太郎A
1002	102	テスト太郎B
1005	102	テスト太郎E

このような並び順を保持する。

注意点として、複合インデックスは２つ目以降のインデックスのみで使用できない
上記のテーブルは所属で絞り込んだならば社員コードはソートされているが、社員コード単体で見た場合はソートされていない。そのため、社員コードのみを検索条件としたときは全件検索が実行される。

例えば下記のようなSQLを実行したとき、

select * from 社員テーブル where 社員コード = 1003 
/*インデックス：所属コード,社員コード*/

データの並び順が社員コード順になっていないので、全件検索し該当レコードを探す動きになる

Q:主キーインデックスと普通のインデックスの違いは？

インデックスという視点では違いはない。主キーはDBが自動でインデックスをつけ削除不可である。したがって何もインデックスを作成しなくても主キーのインデックスは必ず生成される。

※主キー順は非常に重要である。
上記の社員テーブルにおいて社員コードのみで絞り込む要件しかない場合、
１．社員テーブル(主キー順：所属コード,社員コード)
２．社員テーブル(主キー順：社員コード,所属コード)

『１．』の主キーでは主キーインデックスが所属コード、社員コードの順となるため新しく社員コードのみのインデックスを作成する必要がある。
しかし『２．』の主キーなら主キーインデックスが社員順となるため新しくインデックスを作成する必要はない。
見た目の区分に合わせて主キーを作りたくなるが、実際のデータがどのように検索されるかで主キーを作らないと無駄なインデックスが増えてしまう。

Q:インデックスは複数作れるの?

できる。このときは内部的に複数の並び順を持っている。
例えば上記の
・社員テーブル(インデックス：社員コード)
・社員テーブル(インデックス：所属コード,社員コード)
をそれぞれ保持することもできる

Q:ならインデックスは使いそうなものを片っ端から作った方がいいのか？

むやみにインデックスを作ると下記の問題が起こる。そのためインデックスは必要最小限であることが望ましい。
・検索時に間違った(想定とは違う)インデックスが使用される確率が上がる
インデックスはオプティマイザが判定して使用する。
多数のインデックスがあると想定とは別のインデックスを使い結果的に速度低下につながることがある。
・登録時に低速になる。
データ登録を行えば当然並び順に影響を与えるためインデックスの再作成が行われる。大量のインデックスがあればすべてのインデックスを再作成するため登録に時間がかかるようになる。
特に大量のデータを一括登録するテーブルの場合はインデックスを張るのは慎重にすること。

Q：特定のインデックスを強制的に使わせることはできないの？

SQLのヒント句を用いれば可能。ただし基本的にオプティマイザが最適なインデックスを使う。
下記のような時以外は使わないのが基本となる。
・テーブルのレコード件数の増減が激しく、統計情報から正しいインデックスを推察できない。
　例：登録レコード数が多く、１日の終わりにバッチ処理でテーブルの中身をクリアする場合など
・SQLの構造がおかしく正しいインデックスを使用できないが、不具合のリスク等を考えSQLの修正は行いたくない。
・統計情報の更新を行いたいが、DBを触ることが許されずSQLだけで何とかしなければならない。

追記：インデックスを正しく使うにはテーブルの統計情報の定期的なリフレッシュが必要

インデックスが使われるかどうかはDBのオプティマイザが判断しており、
さらにオプティマイザがインデックスを使用するかどうかの判断材料としてテーブルの統計情報を使用する。

• オプティマイザ：SQLから実際のDBデータ取得処理を生成する機能。プログラムならコンパイルが近い。
• 統計情報：テーブルにどんなデータがどれぐらい入っているかの情報のこと

この統計情報は一定のタイミングで更新されるが、統計情報の自動更新を切っていたり(更新はそれなりに重いため)、インデックス追加直後などは更新されていない。
よって統計情報を更新しないと実際のデータの状態と統計情報の状態にずれが発生し、その結果SQL実行時に正しいインデックスが使用されないことがある。