Zenn
🧐

【MySQL】インデックスまとめ

2024/12/03に公開
MySQL
index
tech

背景

業務や個人で開発を行う際、インデックスについて悩むタイミングが度々あるため、備忘録を残そうと思いました。
本記事で扱うDBはMySQL(ver8.0)になります。

インデックスの基本

インデックスとは

MySQLのインデックスは、データベースの検索パフォーマンスを向上させるためのデータ構造です。インデックスを設定することで、データの検索やソートが効率化され、大量のデータを扱う際でも高速にクエリを実行できるようになります。

インデックスを利用することで、データベースは全件スキャン（Full Table Scan）を避け、インデックスを辿ることで効率的に目的のデータを見つけることができます。

インデックスのメリット

検索速度の向上

インデックスがある場合、検索処理が迅速になります。

ソートの最適化

ORDER BY や GROUP BY を伴うクエリの実行速度が向上します。

ユニーク性の保証

PRIMARY KEY や UNIQUE インデックスを使用することで、データの一意性を強制できます。

インデックスのデメリット

ただ、インデックスはバンバン貼っていけば良いわけではなく、下記のようなデメリットもあります。

書き込み速度の低下

INSERT、UPDATE、DELETE 操作時にインデックスも更新されるため、処理が遅くなることがあります。
よって、頻繁に更新されるテーブルにはあまり貼るべきではありませんね。

ストレージ消費

インデックスは追加のストレージを消費します。特に複数のインデックスを設定する場合、ストレージ使用量が増加します。

誤用によるパフォーマンスの低下

不適切なインデックス設定や過剰なインデックスは、逆にパフォーマンスを低下させる場合があります。
インデックスの数は最小限に抑えるべきです。

インデックスの種類

PRIMARY KEY

テーブル内の各行を一意に識別するインデックスで、主キー制約とともに使用されます。
自動的にユニークインデックスが作成されます。

UNIQUE

一意性が必要な列に設定します。同じ値を持つ複数の行が存在しないことを保証します。

INDEX（またはKEY）

通常のインデックスで、クエリの検索を高速化するために設定します。一意性の保証はありません。

インデックスの貼り方

インデックスの確認

SHOW INDEX FROM users;

インデックスの追加（テーブル変更）

ALTER TABLE users
ADD INDEX idx_name (name);  -- name列にインデックスを追加

インデックスの追加

CREATE INDEX idx_email
ON users (email);  -- email列にインデックスを追加

複合インデックスの追加

CREATE INDEX idx_email_created_at
ON users (email, created_at);  -- 複合インデックスを追加

ソート順を指定するインデックスの追加（未指定の場合はASCになる）

CREATE INDEX idx_name_created_at_desc
ON users (name ASC, created_at DESC);

インデックスの削除

DROP INDEX idx_name ON users;

インデックスの確認方法（EXPLAIN）

EXPLAINコマンドを使用することで、クエリがどのように実行されるか（実行計画）、インデックスが正しく使用されているかを確認できます。

EXPLAINの使い方

EXPLAIN をクエリの先頭に付けて実行します。
実行すると、クエリ実行に関する情報を含む結果が返されます。（下記は例です。）

EXPLAIN SELECT * FROM users WHERE name = 'Hoge' ORDER BY created_at DESC;
id select_type table type possible_keys key key_len rows extra
1 SIMPLE users ref idx_name idx_name_created_at 256 10 Using where; Using index

EXPLAINの出力項目

項目数が多いので、確認する際よく見る箇所に絞って説明します。

type

テーブルアクセスの種類を示します。効率の良い順に並べると以下のようになります。

  1. system: システムテーブルへのアクセス（最も効率的）。
  2. const: 一致するデータが1行だけの場合。
  3. eq_ref: 主キーまたはユニークインデックスを使った結合。
  4. ref: 非ユニークインデックスを使用。
  5. range: 範囲検索。
  6. index: インデックススキャン。
  7. ALL: フルテーブルスキャン（最も効率が悪い）。

ALLが表示された場合効率が悪い状態のため、インデックスの追加を検討する必要がある、という感じになります。

possible_keys

クエリに利用可能なインデックスの候補を示します。
ここがNULLの場合、クエリに適したインデックスがないということになります。

key

実際に使用されたインデックスを示します。
インデックスが使用されていない場合はNULLが表示されます。

rows

クエリでスキャンされる行数の推定値です。
この値が大きい場合、クエリのパフォーマンスに悪い影響を与える可能性があります。

extra

実行に関する追加情報を提供します。
下記のうち、Using indexが表示されればOK、temporaryやfilesortが表示されたら改善する必要あり、といった感じです。

  • Using index: インデックスだけでデータを取得（カバリングインデックス）。
  • Using where: WHERE条件でフィルタリングが行われた。
  • Using temporary: 一時テーブルが使用された（最適化が必要）。
  • Using filesort: ファイルベースのソートが発生（インデックスでソート可能なら改善の余地あり）。

私の場合、基本的にはtypeを見てALLではないことを確認し、ALLならrowsとextraを見て改善策を考える流れで確認しています。
インデックス貼ったのにpossible_keysに表示されない…ってこともよくありますね😅

インデックスが効かないパターン

先頭列を使用しない複合インデックス

複合インデックスの先頭列が条件に含まれない場合、インデックスが無効化されます。

インデックスが (name, created_at) の順で定義されている場合：

SELECT * FROM users WHERE created_at > '2024-01-01';

そのため、複合インデックスでは定義する順番を意識する必要があります。

ワイルドカードの先頭使用（部分一致・後方一致検索）

LIKE検索でワイルドカード（%）を文字列の先頭に置くと、インデックスが無効化されます。

SELECT * FROM users WHERE name LIKE '%Hoge';

基本的に検索は前方一致、またはフルテキストで検索するのが吉です。

計算や関数の使用

クエリ内でインデックス列に計算や関数を使用すると、インデックスが無効化されます。

SELECT * FROM users WHERE YEAR(created_at) = 2023;

インデックスは列の元の値に基づいて構築されるため、関数や計算を使用するとインデックスを活用できなくなります。

ORDER BYの順序とインデックスが合致しない

下記のように、nameは昇順、created_atは降順でインデックスを設定した場合。

CREATE INDEX idx_example ON users (name, created_at);

インデックス作成時にソート順を指定しなかった場合、暗黙的に昇順で設定されます。
よって、下記と同等になります。

CREATE INDEX idx_example ON users (name ASC, created_at ASC);

よってorder byで下記のように指定（ソート順が一致しない）した場合、インデックスは効きません。

SELECT * 
FROM users 
WHERE name = 'Hoge' 
ORDER BY created_at DESC;

降順でソートしたい場合には、ソート順をDESCで指定した専用のインデックスを設ける必要があります。

CREATE INDEX idx_example ON users (name ASC, created_at DESC);

インデックスの設計について

これは私見ですが、インデックスはDB設計の段階で設定すべきかなと思っています。（当たり前かもしれませんが…。）

DB設計時に検索に使われるカラムを設定すると思います。その際、データ量や検索方法を鑑みて一緒にインデックスを貼り、100万件くらいテストデータを投入してEXPLAINで実行計画を確認…というところまでがDB設計なのかなと思っています。

過去に参画した案件では、パフォーマンスが良くなかったりslow queryが出たクエリに対し、後付けでインデックスを設定していました。
ただ、このやり方だとどうしても場当たり的になってしまい、insert時の負荷なども考えるとなかなか最適なインデックスを設定するのは難しいなと感じる場面が多かったです。

よって、最近はDB設計の段階でインデックスを設計・設定するようになりました。以降、リリース後にパフォーマンスの問題が出るケースも少なくなり、やはりインデックスを考慮した上でDB設計すべきなんだなーというのが所感です。

Discussion