今までのインデックスについての自分の理解が、「インデックスを作ると検索スピードが上がる」くらいの理解でした。これではいけないと思い、ようやく真面目に勉強したのでそれをまとめるついでにクイズ形式にしてみました。
インデックスとは
データベースにおけるインデックスは、データの検索速度を向上させるための機能です。一般的に、テーブル内のデータの特定の列に対して作成され、その列の値に基づいてデータにアクセスする際の効率を高めます。
よくある例でインデックスは本でいう索引に当たります。見たい項目を索引で探し、何ページかを確認してそのページを開けば一枚一枚ページをめくって探すより効率的に目当てのページに辿り着けますよね。
それをデータベースでも同じことをしているというイメージです。
第一問
以下 SQL 文でインデックスが使用される検索はいくつあるでしょうか？
1 /* 後方一致 */
SELECT * FROM companies WHERE name LIKE '%visionary';
2 /* 完全一致 */
SELECT * FROM companies WHERE name = 'visionary';
3 /* 前方一致 */
SELECT * FROM companies WHERE name LIKE 'visionary%';
4 /* 否定 */
SELECT * FROM companies WHERE name <> 'visionary';
解答・解説
答えは完全一致(2) と 前方一致(3) の 2 つです！
インデックスが使用できる検索方法は以下 3 つです。
- 完全一致
- 前方一致
- 範囲検索
インデックスが使用できない検索方法は以下 4 つあります。
- 否定検索
- 後方一致
- 中間一致
- インデックスを張ったカラムに対しての演算
本の索引をイメージしてみてください。
後方一致について、最後の文字が y で終わるものを探す場合は索引から探すことはできず結局全てから探さないと分からないですよね。
否定検索についても、〜ではないものに関しては全てから探さないと分からないですね。
第二問
一般的にはどのカラムに対するインデックスが一番効果が高いでしょうか？
- 性別
- 名前
- 都道府県
解答・解説
答えは 2 の名前 です！
これはカラム内に存在する異なる値の種類が関係してきます。
異なる値の種類をカーディナリティと呼びます。
よりインデックスの効果を得ることができるのはカーディナリティが高いカラムになります！
| カラム名 | データの種類数 |
|---|---|
| 性別 | 2 |
| 名前 | かなりの数 |
| 都道府県 | 47 |
効果がある順に並べると、名前 > 都道府県 > 性別 となります。
そのカラムで検索するとどのくらい絞れるかということです！
なのでインデックスを作成する際はカーディナリティを意識してちゃんと効果がありそうなのかを考えて作成してみてください！
第三問
インデックスは検索が速くなる代わりに新規作成・更新削除が遅くなるでしょうか？
解答・解説
答えは 遅く なります！
最初にインデックスは本でいう索引に当たると言いましたが、本の中身が一部変更されたら索引も変わりますよね。DB も一緒でレコードを新規作成・更新削除をした場合インデックスも更新しないといけません。
インデックスを作成すると更新系の処理にも影響を与えるのでとりあえずという感覚でインデックスを作成するのはやめましょう。
第四問
一つのテーブルに二つのインデックスが作成されて検索をした場合、二つのインデックスが同時に使用されるでしょうか？それとも一つしかインデックスは使用されないでしょうか？
解答・解説
答えは 一つのインデックスしか使用されません！
そもそもインデックスは同時に二つ使うことができません。
ではどちらのインデックスが使用されるのでしょうか？
それはカーディナリティが高いカラムの方をよしなに使用してくれます。よしなにやって欲しくない場合は使用するインデックスをあらかじめ指定しておくことも出来ます。
第五問
以下のような二つの複合インデックスがあり、都道府県 = '東京都' AND name = 'visionary'のような検索条件だった場合、どちらのインデックス方が早いでしょうか？
- 名前 - 都道府県インデックス
- 都道府県 - 名前インデックス
解答・解説
答えは 1 の名前 - 都道府県インデックス です！
複合インデックスの場合インデックスの順番がとても重要です。
名前 - 都道府県インデックスの場合は最初に名前で絞られます。
都道府県 - 名前インデックスの場合は最初に都道府県で絞られます。
先ほどの問題でも出てきましたが、名前と都道府県では名前の方がカーディナリティが高いです。なので先に名前で絞り込んだ方が検索結果が少なくなるので名前 - 都道府県インデックスの方が早いということになります。