RDBのインデックスについて勉強したのでメモ。
インデックスとは
インデックス(索引)とはテーブルに格納されているデータを高速に取り出す為の仕組みを意味する。インデックスを適切に使用することによってSQL文の応答時間が劇的に改善される可能性がある。
一口にインデックスと言っても、いくつかの種類があり、DBMSによっても使用できる種類に差がある。しかし、実は頻繁に利用するインデックスは1種類だけなので、それだけ覚えれば十分である。そのインデックスは、B-treeインデックスである。B-treeは非常にポピュラーであるため、通常、DBMSにおいてデフォルトで使用される。
B-treeインデックスはどの列に作れば良いか
B-treeインデックスは以下の指針に沿って作成する。
- 大規模なテーブルに対して作成する。
- カーディナリティの高い列に作成する。
- SQL文でWHERE句の選択条件、または結合条件に使用されている列に作成する。
大規模なテーブルに対して作成する。
データ量が少ないテーブルではインデックスを使うよりもフルスキャンを使った方が検索性能が良かったり、インデックスを作ってもフルスキャンを使うのと検索性能がほとんど変わらない場合が多い。では、「データ量が少ないテーブル」とはどの程度が閾値なのか。これは、マシンスペックなど環境によって左右されるので、固定的な値は存在しないが、目安としてはレコード数が1万件を超えた場合は、インデックスを作成する意味はあると考えて良い。
カーディナリティの高い列に作成する。
カーディナリティとは、カラムの値の種類の絶対数をあらわす。例えば、人間の性別なら「男・女」の2種類なので、カーディナリティは2となる。もう一つ例を挙げると、1年間の日付なら「1～365」なのでカーディナリティは365である。
B-treeインデックスを作るときは、カーディナリティの高い列を選ぶことが基本である。この場合の目安は、特定のカラム値を指定した時に、全体の5%程度に絞り込めるだけのカーディナリティがあることである。例えば、365日のうちの1日を指定するSELECT文を考えるとすれば、0.3%に絞り込めるため、B-treeインデックスを作る意味はある、と判断できる。
SQL文でWHERE句の選択条件、または結合条件に使用されている列に作成する。
これは当たり前な話だが、SQLの選択条件や結合条件に使用されない列にいくらインデックスを作っても無意味である。
ただし、インデックスが使用されるためには、SQLの記述方法として注意点があるので、代表的なものを挙げる。
- インデックス列に演算を行っている
- 索引列に対してSQL関数を適用している
- IS NULL述語を使っている
- 非定形を用いている
- ORを用いている
- 後方一致、または中間一致のLIKE述語を用いている
- 暗黙の型変換を行っている
上記の各パターンについて解説する。下記ではcol_1カラムに対してインデックスを作成している。
1. インデックス列に演算を行っている
インデックスを作成したカラムはSQLにおいて「裸」でいるのが原則である。カラムに対して演算を行うとインデックスを利用することができない。
インデックスが利用されない例
SELECT * FROM SomeTable WHERE col_1 * 1.1 > 100;
インデックスが利用される例
SELECT * FROM SomeTable WHERE col_1 > 100/1.1
2. 索引列に対してSQL関数を適用している
理由は1.と同じである。
インデックスが利用されない例
SELECT * FROM SomeTable WHERE SUBSTR(col_1, 1, 1) = 'a';
3. IS NULL述語を使っている
B-treeインデックスは一般的にNULLについてはデータの値とは見なさず、保持していない。したがって、IS NULL、またはIS NOT NULL述語に対しては有効ではない。
インデックスが利用されない例
SELECT * FROM SomeTable WHERE col_1 IS NULL;
4. 非定形を用いている
非定型はインデックスを利用できない。理由は、検索したとしても検索範囲が広過ぎて役に立たないからである。
インデックスが利用されない例
SELECT * FROM SomeTable WHERE col_1 <> 100;
5. ORを用いている
ORを用いた場合はインデックスを利用できない。これはINで書き換えることで回避することができる。
インデックスが利用されない例
SELECT * FROM SomeTable WHERE col_1 = 99 OR col_1 = 100;
インデックスが利用される例
SELECT * FROM SomeTable WHERE col_1 IN (99, 100);
6. 後方一致、または中間一致のLIKE述語を用いている
LIKE述語を使用するときは、前方一致検索の場合のみ索引が使用される。
インデックスが利用されない例
SELECT * FROM SomeTable WHERE col_1 LIKE '%a';
インデックスが利用されない例
SELECT * FROM SomeTable WHERE col_1 LIKE '%a%';
インデックスが利用される例
SELECT * FROM SomeTable WHERE col_1 LIKE 'a%';
7. 暗黙の型変換を行っている
データ型の異なるカラム値をSQLにおいて選択条件または結合条件として利用する場合、数値型⇔文字列型、文字列型⇔日付型のように、型変換を行って型を統一する必要がある。
列とデータ型の異なる値を条件に指定した場合、DBMSは内部的に暗黙の型変換を行う。しかし、その場合はインデックスが使用されなくなる。これを回避するためには明示的に条件に使用する値のデータ型をカラムのデータ型に合わせてやる必要がある。
インデックスが利用されない例
SELECT * FROM SomeTable WHERE col_1 = 10;
インデックスが利用されない例
SELECT * FROM SomeTable WHERE col_1 = '10';
インデックスが利用される例
SELECT * FROM SomeTable WHERE col_1 = CAST(10, AS CHAR(2));
主キーおよび一意制約の列には作成不要
DBMSは主キー制約や一意制約を作成する際、内部的にはB-treeインデックスを作成している。B-treeインデックスがデータをソートして保持するため、重複値チェックにこれを利用している。そのため、主キーや一意制約が存在するカラムに、二重にインデックスを作成する必要はない。
B-treeインデックスは検索性能を劣化させる
インデックスはテーブルのデータが更新されていくと、長期的には構造が崩れて性能が劣化していく。そのため、運用において定期的なメンテナンスを行う、具体的にはインデックスの再構築を行うことが、性能を維持するためには望ましい方策である。