MySQLのインデックスについて

はじめに

MySQLのインデックスについて、案件の実装でインデックスを貼る際に内部で何が起きているかや基本的な考え方が曖昧なままだったので、

インデックスとは何か、インデックスを貼ることによるメリットとデメリット

などについて調べてまとめてみました。

インデックスとは何か

まず、「db インデックス」などで検索してみると、

インデックスとは、

「データベースの特定のカラム（列）に作成する「索引（さくいん）」のようなもので、データ検索やソートの速度を劇的に向上させる仕組み」

「特定のカラムでデータを素早く探すための、並び替え済みの目次」

と書かれています。

インデックスを貼ることによって、MySQLはテーブルとは別に検索用のデータ構造を保持し、

これによって、テーブル全体を順番に確認する必要がなくなり、検索速度を上げることができます。

インデックスがない時とある時の検索

インデックスがない場合の検索時の挙動は

1.テーブルの最初の行を確認する

2.where句の条件に合うか評価する

3.次の行へ

4.最後の行まで上記を繰り返す

となります。このような検索方法はフルテーブルスキャンと呼ばれ、100万行のテーブルであれば100万行全てを確認する必要があり行数に比例して検索時間が増加します。

一方で、インデックスがある場合は、

1.B+ツリーという二分探索木をベースにしたデータ構造を用いて、100万行のデータでも数回~十数回程度の比較によって該当するキーを特定する。

2.インデックス構造から該当行のIDを取得する

3.IDで元テーブルから該当するデータを取得する

となります。

上記の違いによって、特定カラムにインデックスを貼ることによって検索を高速化することができます。

インデックスを貼る際の注意点

インデックスは貼れば貼るほど良いというものでもありません。

まず、インデックスを貼ることによって、

INSERT/UPDATE/DELETEが遅くなります

これは更新の際に、インデックス構造も更新する必要があるためです。

次に、

ストレージ容量が増える

ことです。インデックス構造を保存するためです。

これらは記載した理由から、インデックスが多いほど顕著になります。

なので、インデックスは何をどこに貼るかを論理的に判断して適切に貼る必要があります。

さいごに

インデックスについて改めて基本的な部分を整理してみましたが、
実務では、テーブルに対してインデックスをどのように貼るべきかを事前に正解として判断することは簡単ではなく、
実際のクエリやデータ量を見ながら調整していく必要があると感じました。

テーブルの想定されるデータ件数や使用頻度、
インデックスが効かないSQLの書き方になっていないか、
複合インデックスの評価順がクエリと一致しているかなど、
複数の要素を総合的に考える必要があります。

そのため、まずは
「実際に使われているクエリを確認すること」
「EXPLAINで実行計画を確認すること」
を意識しながら、インデックスがどのように使われているかを継続的に確認していくことが重要だと感じました。

今後も実務の中で検証を重ねながら、
「貼る理由を説明できるインデックス設計」を目指して学習を続けていきたいと思います。