Day19 後半
「CREATE INDEX を“ちゃんと使う”ための具体的なイメージ」
前半で、インデックスは
「よく検索に使う列に付ける“索引”」
というところまでは掴めました。
後半ではいよいよ、
CREATE INDEX の書き方
複数列インデックスのイメージ
インデックスのデメリット（なぜ“張りすぎ注意”なのか）
どのインデックスを作るかを決める考え方
を、具体例ベースで固めていきます。
「とりあえず全部にインデックス」は危険なので、
ここで“効くインデックス”の感覚を作っておきましょう。
CREATE INDEX の基本形
「名前を付けて、どのテーブルのどの列に索引を張るか宣言する」
インデックスを作る基本構文はこうです。
CREATE INDEX インデックス名
ON テーブル名 (列名);
例えば、users テーブルの email にインデックスを張りたい場合はこうです。
CREATE INDEX idx_users_email
ON users (email);
ここで大事なのは二つです。
一つ目は、インデックスにも「名前」があること。idx_users_email のように、
「idx_テーブル名_列名」みたいなルールで付けることが多いです。
二つ目は、「どのテーブルの、どの列に対してか」を明示すること。ON users (email) の部分がそれです。
この一行を実行した瞬間、
データベース内部に「email 用の索引構造」が作られます。
以降、WHERE email = ... の検索などで、
このインデックスが自動的に使われる可能性が出てきます。
複数列インデックス（複合インデックス）のイメージ
「user_id だけでなく、user_id＋created_at で探したいとき」
単一列だけでなく、
複数列をまとめてインデックスにすることもできます。
例えば、ログイン履歴 login_logs を考えます。
id | user_id | created_at
---+---------+-------------------
1 | 1 | 2025-05-01 10:00
2 | 1 | 2025-05-02 09:00
3 | 2 | 2025-05-01 11:00
...
よく書くクエリが、
「あるユーザーのログイン履歴を、日付順で取りたい」
だとします。
SELECT *
FROM login_logs
WHERE user_id = 1
ORDER BY created_at DESC;
この場合、user_id 単体のインデックスでもそこそこ効きますが、(user_id, created_at) の複合インデックスを作ると、
さらに効率が良くなることがあります。
CREATE INDEX idx_login_logs_user_created
ON login_logs (user_id, created_at);
このインデックスは、
まず user_id で絞り込み
その中で created_at の順序も意識した構造
になっているイメージです。
ただし、複合インデックスには重要なルールがあります。
先頭の列（ここでは user_id）を使わない検索には効きにくい、ということです。
例えば、
SELECT *
FROM login_logs
WHERE created_at >= '2025-05-01';
のように、created_at だけで絞るクエリには、(user_id, created_at) インデックスはあまり役に立ちません。
「複合インデックスは、左から順に使われる」
という感覚を持っておくと、設計ミスを減らせます。
インデックスのデメリット
「速くなる代わりに、“書き込み”と“容量”のコストを払う」
インデックスは魔法ではなく、
ちゃんとコストもあります。
一番大きいのは二つです。
一つ目は、「INSERT / UPDATE / DELETE が重くなる」こと。
行を追加・変更・削除するたびに、
テーブル本体だけでなく、
関連するインデックスも更新しなければならないからです。
インデックスが多ければ多いほど、
この「更新コスト」が積み上がります。
二つ目は、「ディスク容量を食う」こと。
インデックスはテーブルとは別のデータ構造なので、
その分のストレージが必要です。
小さなアプリなら気になりませんが、
大規模になると無視できない差になります。
だからこそ、
「よく使う検索のための、必要最低限のインデックスだけを張る」
という考え方が重要になります。
「とりあえず全部にインデックス」はなぜ危険か
「読むのは速いけど、書くたびに息切れするデータベースになる」
初心者がやりがちなパターンとして、
「検索が遅いのが怖いから、全部の列にインデックスを張っておこう」
という発想があります。
これは短期的には「なんとなく速くなった気がする」かもしれませんが、
長期的にはかなり危険です。
理由はシンプルで、
INSERT / UPDATE / DELETE のたびに、
大量のインデックスを更新し続けることになるからです。
結果として、
新規登録が遅い
バッチ処理が遅い
テーブルのサイズが無駄に膨らむ
といった問題が出てきます。
インデックスは、
「よく使う検索のための“専用の近道”」
なので、
使わない近道を大量に作るのは無駄
むしろ道が増えすぎて管理が大変になる
というイメージを持っておくと、
「必要なところだけに絞ろう」という感覚が自然に出てきます。
どのインデックスを作るかを決める考え方
「よく書くクエリから逆算する」
インデックス設計の基本は、
「テーブルから考える」のではなく
「よく書くクエリから逆算する」
ことです。
例えば、アプリでよく使うクエリが次のようなものだとします。
ユーザーを email で探す
ユーザーの注文を user_id で探す
ログイン履歴を user_id ＋ created_at で絞る
この場合、候補になるのは、
users(email)
orders(user_id)
login_logs(user_id, created_at)
といったインデックスです。
逆に、ほとんど検索に使わない列
例えば「プロフィールの自己紹介文」などに
インデックスを張っても、ほぼ意味がありません。
「どんなクエリが多いか」
「どんな WHERE / JOIN 条件が多いか」
を意識して、それに合わせてインデックスを設計する。
これが、実務でのインデックス設計の基本姿勢です。
セキュリティの視点から見るインデックス設計
「速くすること」と「見せていい範囲を分けて考える」
インデックス自体は、
「速く探すための構造」であって、
アクセス制御の仕組みではありません。
でも、設計を間違えると、
本来あまり触らせたくない列に対しても、
高速な検索ができる状態を作ってしまう
ということが起きます。
例えば、
パスワードハッシュ
秘密情報に近いフラグ
などにインデックスを張ること自体は技術的には可能ですが、
そもそもそういった列に対して
頻繁な検索が必要な設計になっている時点で、
「権限設計やデータ設計を見直した方がいい」サインかもしれません。
大事なのは、
インデックスを張る前に、
「この列をキーに高速検索できる状態を、本当に許していいのか？」
という視点を一度挟むことです。
小さな練習イメージ
頭の中で、次のようなアプリを想像してみてください。
ユーザーは email でログインする。
マイページでは、自分の注文履歴が日付順で表示される。
管理画面では、特定ユーザーのログイン履歴をよく調べる。
このとき、
どのテーブルの、どの列にインデックスを張ると気持ちよく動きそうか
逆に、どの列にはインデックスを張る必要がなさそうか
を考えてみると、
「クエリから逆算するインデックス設計」の感覚が少しずつ掴めてきます。
Day19 後半のまとめ
CREATE INDEX は「インデックスに名前を付けて、どのテーブルのどの列に索引を張るか宣言する」文。
複数列インデックスは、「user_id＋created_at」のような組み合わせ検索を速くできるが、左側の列から順にしか効かないという性質がある。
インデックスは検索を速くする代わりに、INSERT / UPDATE / DELETE を重くし、ディスク容量も消費するため、「張りすぎ」は逆効果になる。
インデックス設計は「テーブルから」ではなく、「よく書くクエリ（WHERE / JOIN / ORDER BY）」から逆算して考えるのが基本。
性能チューニングとしてインデックスを使いつつ、「その列を高速に検索できる状態を本当に許していいのか」というセキュリティ視点も忘れないことが大事。
ここまで来たあなたは、
「正しい SQL を書く」だけでなく、
「その SQL を現実的な速度で動かすための仕組み」まで意識できる段階に入っています。
この先、EXPLAIN などで実行計画を見るようになると、インデックスとの付き合い方がさらに立体的になっていきます。