Day17 後半のゴール
「“なんとなく複合インデックス”を卒業して、狙って効かせられるようになる」
前半で「どのカラムにインデックスを貼るか」の感覚をつかみました。
後半では一歩進めて、
複合インデックスをどう並べるか
インデックスが“効かなくなる”書き方
インデックスを貼りすぎることのデメリット
ここを押さえて、「理由を説明できるインデックス設計」を目指します。
複合インデックスとは何か
「“カラムの並び順”まで含めて設計するインデックス」
複合インデックスは、複数カラムをまとめて1本のインデックスにしたものです。
例えば、users テーブルに対して次のようなインデックスを貼るとします。
CREATE INDEX idx_users_status_created
ON users (status, created_at);
これは、「status → created_at の順に並んだインデックス」です。
ここで重要なのは、「順番も設計の一部」ということです。
このインデックスは、ざっくり言うと次のような検索に効きます。
status だけで絞る検索
status と created_at の両方で絞る検索
status で絞って created_at で並べる検索
逆に、created_at だけで絞る検索には、原則としてこのインデックスは効きません。
ここで出てくるのが「左端一致の法則」です。
左端一致の法則
「複合インデックスは“左から順に”しか使えない」
複合インデックスには、有名なルールがあります。
それが「左端一致の法則」です。
さっきの idx_users_status_created (status, created_at) を例にします。
このインデックスがフルに効くのは、左から順に条件がそろっているときです。
WHERE status = ‘active’
WHERE status = ‘active’ AND created_at >= ‘2025-01-01’
WHERE status = ‘active’ ORDER BY created_at DESC
こういうクエリでは、EXPLAIN すると key = idx_users_status_created になりやすく、
Extra に「Using filesort」が出ないことも多いです。
一方で、次のようなクエリではどうでしょうか。
SELECT *
FROM users
WHERE created_at >= '2025-01-01'
ORDER BY created_at DESC;
この場合、「status での条件」がありません。
左端の status が使われないため、idx_users_status_created は created_at のインデックスとしては基本的に使われません。
EXPLAIN すると、こうなりがちです。
table: users
type: ALL
key: NULL
rows: 100000
Extra: Using where; Using filesort
ここでのポイントは、「複合インデックスは左から順にしか使えない」ということです。
つまり、
インデックス (A, B, C) を貼ったら、A だけ、A+B、A+B+C には効く
B だけ、C だけ、B+C には基本的に効かない
というイメージを持っておくと、設計のミスが減ります。
複合インデックスの並び順をどう決めるか
「“よく絞る条件”を左、“よく並べる条件”をその次に置く」
では、複合インデックスのカラム順はどう決めるべきでしょうか。
ここで前半の話が効いてきます。
考え方の軸は二つです。
よく絞り込みに使うカラムを左側に置く
その次に、ORDER BY や GROUP BY によく使うカラムを置く
先ほどの例で言えば、
status でアクティブユーザーだけに絞ることが多い
その中で created_at の新しい順に並べることが多い
というクエリが主流なら、
(status, created_at)
の順でインデックスを貼るのは理にかなっています。
逆に、「期間で絞って、その中で status ごとに集計する」クエリが多いなら、
(created_at, status)
の方が有利な場合もあります。
大事なのは、
テーブルを見て順番を決めるのではなく、
実際のクエリを見て順番を決める
ということです。
インデックスが“効かなくなる”書き方
「関数をかける・前方一致でない LIKE は要注意」
インデックスを貼っていても、クエリの書き方次第で「効かなくなる」ことがあります。
ここは実務でよくハマるポイントなので、少し深掘りします。
関数をかけるとインデックスが使えないことが多い
例えば、created_at にインデックスがあるとします。
CREATE INDEX idx_users_created
ON users (created_at);
このとき、次のクエリはインデックスが効きやすいです。
SELECT *
FROM users
WHERE created_at >= '2025-01-01'
AND created_at < '2025-02-01';
一方、こう書いてしまうとどうでしょう。
SELECT *
FROM users
WHERE DATE(created_at) = '2025-01-01';
created_at に関数 DATE() をかけてしまうと、
MySQL は「インデックス上の値そのもの」ではなく「関数を適用した結果」で比較する必要が出てきます。
その結果、インデックスをうまく使えず、フルスキャンに近い動きになることが多いです。
EXPLAIN すると、type = range だったものが type = ALL になっていたりします。
対策としては、
関数をカラム側ではなく、リテラル側に寄せる
（例：created_at >= '2025-01-01' AND created_at < '2025-01-02' のように書く）
という書き方を意識します。
前方一致でない LIKE もインデックスが効きにくい
次に、文字列検索です。
name にインデックスがあるとします。
CREATE INDEX idx_users_name
ON users (name);
このとき、次のクエリはインデックスが効きやすいです。
SELECT *
FROM users
WHERE name LIKE '山田%';
先頭が固定されている「前方一致」なので、
インデックスの木構造を使って「山田」で始まる範囲を探せます。
一方、こう書くとどうでしょう。
SELECT *
FROM users
WHERE name LIKE '%太郎';
先頭がワイルドカードになっているため、
インデックスの「どこから見ればいいか」が分からず、
結局全件スキャンに近い動きになります。
EXPLAIN すると、type = ALL / key = NULL になりがちです。
Day17 の段階では、
カラムに関数をかける
LIKE の先頭に % を置く
といった書き方は、インデックスを殺しやすい、という感覚だけ持っておいてください。
インデックスのデメリット
「“貼れば貼るほど速くなる”わけではない」
ここまで「インデックスを貼ると速くなる」話ばかりしてきましたが、
当然デメリットもあります。
一番大きいのは、書き込みコストです。
INSERT のたびに、インデックスにもエントリを追加する
UPDATE でインデックス対象のカラムが変わると、インデックスも更新される
DELETE すると、インデックスからも削除される
つまり、インデックスが多いほど、
INSERT / UPDATE / DELETE が重くなる
という側面があります。
また、インデックス自体もディスク容量を食います。
巨大テーブルに無闇にインデックスを貼りまくると、
ストレージを圧迫し、キャッシュ効率も落ちます。
だからこそ、
「読むときの速さ」と「書くときの重さ」のバランスを取る
という視点が必要になります。
読み取りが圧倒的に多いシステム（ログ分析、レポート系）なら、
インデックスを厚めに貼る価値があります。
逆に、書き込みが非常に多いシステム（センサーの生データ、アクセスログの生書き込みなど）では、
インデックスを最小限に抑えることもあります。
実務での“インデックス設計の一歩目”
「クエリを集めて、EXPLAIN とセットで考える」
最後に、「実際にどうやって設計を始めるか」をまとめます。
いきなりテーブル定義だけを見て「ここにインデックス」と決めるのではなく、
まずは「よく使うクエリ」を集めます。
そのクエリに対して EXPLAIN を叩き、
どのテーブルがフルスキャンになっているか
どのカラムで絞り込んでいるのにインデックスが使われていないか
ORDER BY / GROUP BY で filesort や temporary が出ていないか
を確認します。
そのうえで、
WHERE / JOIN に頻出するカラムに単一インデックスを貼る
必要に応じて、よく使う組み合わせに複合インデックスを貼る
関数や前方以外の LIKE でインデックスを殺していないかを見直す
書き込み頻度とのバランスを考えて「貼りすぎていないか」をチェックする
という流れで、少しずつインデックス設計を固めていきます。
Day17 の時点で完璧を目指す必要はありません。
大事なのは、
「このインデックスは、このクエリを速くするために貼っている」
と説明できる状態になっていることです。
Day17 後半のまとめ
複合インデックスは「カラムの並び順まで含めて設計するインデックス」であり、(status, created_at) のように貼った場合は「左端一致の法則」に従って status だけ、status + created_at には効くが、created_at だけの条件には基本的に効かないため、実際のクエリを見て「よく絞る条件を左、よく並べる条件をその次」に置く、といった順番設計が重要になる。
また、カラムに関数をかける（DATE(created_at) など）や、先頭が % の LIKE（LIKE '%foo'）といった書き方は、せっかく貼ったインデックスをオプティマイザが使えなくする典型パターンであり、EXPLAIN すると type = ALL / key = NULL になりやすいので、「関数はできるだけリテラル側に寄せる」「文字列検索は前方一致を基本にする」といった書き方の工夫も含めてインデックス設計を考える必要がある。
さらに、インデックスは読み取りを速くする一方で、INSERT / UPDATE / DELETE のたびに更新コストがかかり、ディスク容量も消費するため、「よく使うクエリを集めて EXPLAIN し、WHERE・JOIN・ORDER BY・GROUP BY に頻出するカラムに絞って単一・複合インデックスを貼る」「書き込み頻度とのバランスを見て“貼りすぎない”」というスタンスで、「このインデックスはこのクエリを速くするために存在する」と説明できる状態を目指すことが、実務的なインデックス設計の核心になっていく。