今回はデータベースのインデックスに関して。
検索条件を早くする場合、何よりも速度が速くなるのはインデックスを張ることでしょう。
インデックスを張ることは単語を索引順に並べることですので、劇的に速度が向上します。
他にも
- アプリケーション(プログラム)での変更がなく、エンジニアが意識しなくてよい
- データ自体には影響を与えない
などのメリットもあります。
デメリットは「索引用の内部データを作成しなければいけないため、更新時に若干時間がかかる」ことかと思いますが、メリットに比べると小さいです。
データベースで使われているインデックスは正確にはB-treeインデックスといわれているものです。
Btreeインデックスに関してはの説明はこちらを参考に。
Btreeインデックスが優れている点としては下記のような点です。
- すべてのデータに対してだいたい同じ計算量でアクセスできること(長期間の運用では少しずつ崩れていく)
- データ量nに対し、フルスキャンの計算量はlognであること
- 長期間の運用による性能劣化が他のインデックスに比べて緩やか
- データの構造上、統合だけでなく、不等号やBetweenに関しても高速化が利く(逆に否定にはきかない)
- キーをソートしているため、集約関数、ORDER BY、集合関数に強い
具体的にはインデックスは下記のような場合にはると効果的と言われています。
- またデータ量nに対して計算量がlognなため、データ量が少ないもの(目安として1万以下のレコード)には効果がない
- カーディナリティの高いもの(データの種類が多いもの。例：顧客番号。逆にカーディナリティが低いものは性別。2種類しかないため。)
- SQLで検索条件として使われている
逆にインデックスの効果がないものとしては下記のようなケースです。
インデックス列に対して算術演算を行うとき
|
1 2 3 4 |
SELECT * FROM someTable WHERE col_1 * 1.1 > 100; --この col_1 >100/1.1が望ましいです。 |
インデックス列に対してSQL関数を使うとき
|
1 2 3 |
SELECT * FROM someTable WHERE STBSTR( col_1, 1 , 1) ='a'; |
インデックス列に対してIS NULLを使うとき
|
1 2 3 |
SELECT * FROM someTable WHERE col_1 is NULL; |
インデックス列に対して否定形を使うとき
|
1 2 3 |
SELECT * FROM someTable WHERE col_1 <> 100; |
インデックス列に対してORを用いているとき
|
1 2 3 |
SELECT * FROM someTable WHERE col_1 = 99 or col_1 =100 |
この場合INを使うほうがインデックスの効果はでます。
後方一致、中間一致のlike
|
1 2 3 |
SELECT * FROM someTable WHERE col_1 like '%test%' |
前方一致のみが有効です。
暗黙の型変換
|
1 2 3 4 |
SELECT * FROM someTable WHERE col_1 = '10' --実施に入っているデータが数字の場合 |
数値⇔文字や文字⇔日付を行った場合、エラーにはなりませんが、インデックスを使用することができません。
