はじめに
先日、社内でインデックスについて話題になり、そのとき知ったことや調べたことを自分なりにまとめてみました。
間違っている部分などありましたらご指摘いただければと思います。
インデックスってなに？
データベースにおけるインデックス（索引）とは、対象のカラム値を適切な順番に並び替えたデータのことです。インデックスを使用することで、特定のカラム値をすばやく見つけることができるようになります。
例えば次のようなテーブルから Yuffie Kisaragi を見つけようとしたとき、通常であれば name カラムを上から順番に見ていく必要があります。
| id | name | age |
|---|---|---|
| 1 | Cloud Strife | 21 |
| 2 | Barret Wallace | 35 |
| 3 | Tifa Lockhart | 20 |
| 4 | Aerith Gainsborough | 22 |
| 5 | Red XIII | 48 |
| 6 | Yuffie Kisaragi | 16 |
| 7 | Cait Sith | 4 |
| 8 | Vincent Valentine | 27 |
| 9 | Cid Highwind | 32 |
このくらいのレコード数であれば簡単に検索することができますが、これが数十万、数百万となると検索に時間がかかってしまいます。
ここで、name 列にインデックスを貼るとどうなるでしょうか。
| id | name |
|---|---|
| 4 | Aerith Gainsborough |
| 2 | Barret Wallace |
| 7 | Cait Sith |
| 9 | Cid Highwind |
| 1 | Cloud Strife |
| 5 | Red XIII |
| 3 | Tifa Lockhart |
| 6 | Yuffie Kisaragi |
| 8 | Vincent Valentine |
name 列を a-z 順に並び替えた別のデータが作られました。
このようにデータが順番に並ぶことで、B木と呼ばれるデータ構造を利用したアルゴリズムが使えるようになり、高速な検索が可能になります。
ここで Yuffie Kisaragi を検索すると、B木アルゴリズムを使ってデータの位置をインデックスが教えてくれます。位置がわかればテーブルを見に行き、すばやくレコードを取得することができます。
ただし、name と age という２つの条件で検索を行いたい場合、両方のカラムにインデックスを貼らないと効果を発揮しません。複数の条件で検索する場合の速度を上げたい場合は、それらをまとめた複合インデックスを作る必要があります。
すべてのカラムにインデックスを貼ればいいのでは？
インデックスのおかげで検索が速くなるとはいえ、すべての列にインデックスを貼ればいいというものでもありません。
インデックスのデメリット
インデックスを貼った場合、対象となるカラムを並び替えた別のデータが作成されるため、レコードが追加されたらインデックスにも新しいデータを書き込む必要があります。
そのため、すべてのカラムにインデックスを貼ると検索は速くなりますが、挿入、更新、削除はインデックスのデータも書き換える必要があるため速度が落ちます。また、単純にデータ量が増えるため容量が圧迫されることにもなります。
インデックを貼る場合は、頻繁に検索される条件のカラムに絞る方が効果的です。
レコード数が少ないと効果が薄い
当然かもしれませんが、レコード数が少ない場合はインデックスのありなしで速度に大きな違いは現れません。逆に上記のようなデメリットもあるため、レコード数の少ないテーブルにはインデックスを貼る必要はないでしょう。
カーディナリティが低いと効果が薄い
カーディナリティが低いカラムへのインデックスは効果が薄いとされています。
カーディナリティとは、個人的には「選択肢の多さ」という意味合いで捉えています。例えば、性別のカラムがあった場合、そこに入る値は「男性」か「女性」の二択となり、選択肢は少なくカーディナリティが低いと言えます（性別＝生物学的な性別とします）。
例えば、従業員1万人が在籍する会社の従業員テーブルに「性別」カラムがあり、そこにインデックスを貼ったとします。
性別が「男性」の「山田太郎」さんを検索する場合、まずインデックスが貼られた「性別」カラムを検索します。この会社には男性が 6000 人在籍しており、インデックスを貼ったことで素早く 6000 人を絞り込むことができました。しかし、名前にインデックスが貼られていないため、残りの 6000 レコードは上から順番に検索する必要があり、結果的に時間がかかってしまいます。。
このようにカーディナリティの低いカラムはインデックスの効果が低いため、インデックスを貼るときはカーディナリティについても考慮する必要があります。
また複合インデックスを貼る場合はカーディナリティが高い順に検索がかかるように作るとパフォーマンスが向上します。上記の例でいえば「性別」→「名前」ではなく「名前」→「性別」の順に絞り込むようなインデックスがベターです（そもそも性別にインデックスは必要ないと思いますが）。
まとめ
インデックスとは対象のカラム値を適切な順番に並び替えたデータのこと。
テーブルにインデックスを貼ることで、B木アルゴリズムを使った高速な検索が可能となる。
インデックスは闇雲に貼っても効果がなく、以下のようなテーブル、カラムを対象とすると効果的。
- 頻繁に検索されるテーブル
- レコード数が多いテーブル
- カーディナリティが高いカラム