インデックスを作るときに最低限知っておきたいこと
みなさんはDBテーブルにインデックスを作成するときに、作成すべきか迷うときはないでしょうか？
インデックスを作成する際に、最低限知っておきたいことを自分の中でまとめて見たので参考にしていただけると幸いです。
■インデックスを作るときの基準
◎データ量の多いテーブルに作る
データ量の多いテーブルにだけ作成する。
なぜなら、
データ量の少ないテーブルの場合フルスキャンの方が処理が速いためです。
つまり、インデックスを利用して取得する方が遅くなってしまいます。
MySQL のドキュメントにもこのように記載があります。
小さなテーブルまたは、レポートクエリーが行の大半またはすべてを処理する大きなテーブルに対するクエリーでは、インデックスはあまり重要ではありません。 クエリーで行の大半にアクセスする必要がある場合は、順次読み取る方が、インデックスを処理するより高速です。
MySQL のドキュメントには実際にどの程度のレコード件数があればデータ量の大きなテーブルと判断できるのか？、という基準に対する言及はされていませんでした。
◎カーディナリティの高い列に作る
カーディナリティが高い（値の種類が多い）列に作成する。
なぜなら、
カーディナリティが低い（値の種類が少ない）列にインデックスを作成したとしても、結局クエリが大量の行を返す可能性が高いためです。
つまり、結果的にフルテーブルスキャンと同じような動作になってしまいます。
具体例で考える
このような列を持つ persons テーブル があったとします。
| id | gender | country |
|---|---|---|
| '男性', '女性', '不明' の3種類 | '日本', 'アメリカ' など196種類 |
このgenderとcountryで比べてみます。
gender 列
- カーディナリティ: 低い
- インデックスの効果: 低い（gender を where 句などで指定しても大量の行を返す可能性が高いため)
country 列
- カーディナリティ: 高い
- インデックスの効果: 高い(より限定してデータを抽出できる可能性が高いため）
◎ SQL の条件指定でよく使われる列に作る
-
WHERE句 -
ORDER BY句 -
JOINの結合条件
これらの条件指定によく使われる列に作成する。
なぜなら、
条件として使用されない列にインデックスを貼っても、インデックススキャンは実行されないためです。
つまり、使われないインデックスを作成したところで意味がありません。（当たり前と言えば当たり前ですが。笑）
■インデックスを作成するときに注意したいこと
◎主キー制約やユニーク制約がある列にはインデックスを作らない
主キーやユニーク制約の存在する列には作る必要がありません。
なぜなら、
主キーやユニーク制約の存在する列には既にインデックスが作成されているからです。
つまり、既にインデックスが作成されているので新たに作成する必要がありません。
主キーやユニーク制約に暗黙的にインデックスが作成される理由
◎インデックスは更新性能を劣化させる
インデックスはデータ更新処理の性能を劣化させます。
なぜなら、
データの追加や更新、削除処理がされるとインデックス内に保持している値も更新しなければならないからです。
つまり、インデックスを作成すればするほど、当該テーブルに対する更新性能は落ちていくことになります。
インデックはテーブルとは独立した場所に保存される
◎カーディナリティの高さは複合列の組み合わせで考える
2つ以上の列に複合インデックスを作成する場合のカーディナリティの高さは複合列の組み合わせの数で考える必要があります。
具体例で考える
このような列を持つ persons テーブル があったとします。
| id | gender | country | birthday |
|---|---|---|---|
| '男性', '女性', '不明' の3種類 | '日本', 'アメリカ' など196種類 | '1992/11/24' など10000種類以上 |
gender, countrygender, birthday
の2パターンの組み合わせで比べてみます。
gender, country の組み合わせ
カーディナリティが低いと言える。
3✕196=588種類の異なるペアを持つため。
gender, birthday の組み合わせ
カーディナリティが高いと言える。
3×10000=30000種類以上の異なる値のペアを持つため。
◎カーディナリティが高くても特定の値にデータが集中している列には作らない
カーディナリティが高くても、特定の値にデータが集中している値にはインデックスは向いていません。
例えば、下記テーブルのlevel列のカーディナリティは100です。
しかし、保存されているデータの内
99%が100で、1~99の値が全体の1%しか存在しないというようなケースではlevel列にインデックスを作成することは向いていません。
| id | level |
|---|---|
| 1~100の100種類 |
というのも、
100を指定したSELECT文では、大量のデータ（99%の値）から検索を行わなければならない。
一方で残りの1~99の値を指定した場合はほぼピンポイントで検索にヒットするという動作になるからです。
※裏を返すと、この場合100を指定した検索が要件的に実行されない場合にはこの列にインデックスを作るメリットがある、ということ。
インデックスに関してSQLの書き方でも気をつけるべきことがあります。
こちらの記事もよろしければ見てみてください。
参考書籍
Discussion