【AlmaLinux 9】MySQL初心者講座 第7回：インデックスの極意とEXPLAINによるクエリ高速化

こんにちは！「LINUX工房」管理人の「リナックス先生」です。
「MySQL初心者講座」もいよいよ第7回。ゴールが見えてきましたね！前回はデータの集計や分析について学びましたが、皆さんの環境で扱うデータ量はどれくらいになりましたか？

開発中のテストデータが数百件程度であれば、どんなに非効率なSQLを書いても一瞬で結果が返ってきます。しかし、本番環境で数年運用され、レコードが数十万件を超えたとき、データベースの「真の実力」が試されます。インデックス（索引）を正しく理解していないエンジニアが作ったシステムは、サービス成長とともに必ず「重くて動かない」という致命的な問題を抱えることになるのです。

今日は、データベースを爆速化させる最強の武器、「インデックス」の仕組みと、それを使いこなすためのプロの診断技術を伝授します。

---

1. インデックス（索引）とは何か？辞書の仕組みに例えて理解する

インデックスとは、一言で言えば「検索用の地図」です。データベースのテーブルにある膨大なデータに対して、あらかじめ並び替えた情報を別の場所に持っておくことで、目的のデータを高速に見つけ出すための仕組みです。

1-1. フルテーブルスキャンの限界

インデックスがない状態での検索を、専門用語で「フルテーブルスキャン（Full Table Scan）」と呼びます。これは、テーブルの1行目から最後の行まで順番にチェックしていく作業です。100件なら一瞬ですが、100万件あれば100万回のチェックが必要です。これはコンピュータにとっても重い作業で、CPUを激しく消費し、ディスクI/O（データの読み込み）を発生させます。

1-2. 索引（Index）がある場合

厚さ数千ページの国語辞典を想像してください。巻頭や巻末にある「五十音順の索引」があれば、「りんご」という言葉を探すのに全ページをめくる必要はありません。「ら行」のページに飛び、そこから数ページ探すだけで完了します。これがインデックスの効果です。

---

2. MySQLの心臓部：B-tree構造が高速化を実現する仕組み

MySQL（特に標準のストレージエンジンであるInnoDB）で最も一般的に使われるインデックスの構造が「B-tree（Balanced Tree）」です。中級者以上のエンジニアとして、この構造をイメージできることは非常に重要です。

2-1. 木構造による絞り込み

B-treeは、データを「枝分かれした木」のような形で管理します。根（ルート）から始まり、中間ノードを経て、葉（リーフ）にある実際のデータ（またはポインタ）に辿り着きます。各ノードには「ここから左は〇〇以下、右は〇〇以上」といった情報が整理されています。

2-2. 計算量の劇的な減少（O(log n)）

100万件のデータがある場合、フルテーブルスキャンでは最悪100万回の比較が必要ですが、B-treeインデックスを使えば、わずか数十回程度のノード移動で目的のデータに到達できます。この「検索回数がデータ量の増加に対して非常に緩やかにしか増えない」という特性が、大規模システムを支える鍵となっています。

---

3. インデックスの作成と削除（CREATE INDEX / DROP INDEX）

それでは、AlmaLinux 9上のMySQLで実際にインデックスを作成してみましょう。基本的には、`WHERE`句や`JOIN`の結合条件によく使われるカラムに対してインデックスを貼ります。

3-1. インデックスの作成

例えば、`users`テーブルの`email`カラムで検索することが多い場合、以下のようにインデックスを作成します。

# 構文: CREATE INDEX インデックス名 ON テーブル名(カラム名);
CREATE INDEX idx_users_email ON users(email);

3-2. 現在のインデックスを確認する

テーブルにどのようなインデックスが貼られているかは、以下のコマンドで確認できます。第2回で解説した「主キー（PRIMARY KEY）」や「ユニーク制約（UNIQUE）」も、実は内部的にはインデックスとして作成されています。

SHOW INDEX FROM users;

3-3. インデックスの削除

インデックスは作りすぎると更新処理が遅くなる（後述）ため、不要になったものは削除します。

DROP INDEX idx_users_email ON users;

---

4. 魔法の診断コマンド「EXPLAIN」で実行計画を解読する

「インデックスを貼ったはずなのに、なぜか速くならない……」。そんなとき、プロのエンジニアが真っ先に実行するのが`EXPLAIN`コマンドです。これは、MySQLがそのSQLをどのように実行しようとしているか（実行計画）を教えてくれる、いわば「クエリのレントゲン写真」です。

4-1. EXPLAINの使い方

使い方は簡単です。実行したい`SELECT`文の先頭に`EXPLAIN`とつけるだけです。

EXPLAIN SELECT * FROM users WHERE email = 'kou@example.com';

4-2. ここだけは見ろ！重要なカラム

表示される表の中で、特に初心者が注目すべきは以下のカラムです。

• type: 結合のタイプ。ここが ALL になっていたら、それは「フルテーブルスキャン」が行われている警告サインです。const, ref, range ならインデックスが使われています。
• key: 実際に使われたインデックスの名前が表示されます。ここが NULL なら、インデックスは使われていません。
• rows: そのクエリを処理するためにMySQLが「読み取る必要がある」と予測した行数です。ここが巨大な数値なら、最適化の余地があります。
• Extra: 補足情報。Using filesort や Using temporary が出ている場合は、メモリやディスクを使って重い処理をしている証拠です。

[Image showing MySQL EXPLAIN output with highlighted type and key columns]

---

5. インデックスを貼っても速くならない？「カーディナリティ」の重要性

何でもかんでもインデックスを貼ればいいというわけではありません。インデックスの効果が出ない典型的なケースが、「カーディナリティ（値の種類の多さ）」が低いカラムにインデックスを貼ることです。

5-1. カーディナリティとは？

例えば、「性別（男性・女性・その他）」のカラムにインデックスを貼ったとします。100万人のデータがあっても、値は3種類しかありません。この場合、インデックスを使ってもデータを十分に絞り込めないため、MySQLは「インデックスを使うより、最初から全部見たほうが早い」と判断し、インデックスを無視してしまいます。

5-2. インデックスのトレードオフ（代償）

インデックスには以下のデメリットがあることを忘れないでください。

• 書き込み（INSERT/UPDATE/DELETE）が遅くなる: データを更新するたびに、裏側で「索引の地図」も書き換える必要があるため、手間が増えます。
• ディスク容量を消費する: 巨大なテーブルに複数のインデックスを貼ると、インデックスファイルのサイズがデータ本体を超えることもあります。

結論として、「検索によく使い、かつ値のバリエーションが豊富なカラム」に絞ってインデックスを貼るのがプロの鉄則です。

---

6. 複合インデックス（マルチカラムインデックス）の設計ルール

実務では、`WHERE age = 25 AND prefecture = ‘Tokyo’` のように、複数のカラムを組み合わせて検索することが多いですよね。この場合、それぞれのカラムに個別にインデックスを貼るよりも、「複合インデックス（複数のカラムをセットにした索引）」を1つ作るほうが圧倒的に効率的です。

6-1. 複合インデックスの作成

CREATE INDEX idx_age_prefecture ON users(age, prefecture);

6-2. 「最左プレフィックス」のルール

ここが重要です。`(age, prefecture)` という順番でインデックスを作った場合、以下のようになります。

• WHERE age = 25 → インデックスが使われる（OK）
• WHERE age = 25 AND prefecture = 'Tokyo' → インデックスが使われる（最強）
• WHERE prefecture = 'Tokyo' → インデックスが使われない！（NG）

インデックスの左側のカラムから順に指定しないと、MySQLは地図を使いこなせません。この順番の設計にはセンスが問われます。

---

7. AIを活用したクエリ最適化とインデックス推奨の受け方

複雑なクエリのチューニングは、ベテランエンジニアでも頭を悩ませる作業です。しかし、現代のエンジニアにはAIという相棒がいます。AI（GeminiやChatGPT）に、テーブル構造と`EXPLAIN`の結果を渡すだけで、最適なインデックスの案を提示させることができます。

7-1. AIへのパフォーマンスチューニング依頼プロンプト

「あなたはMySQLのパフォーマンスチューニングのスペシャリストです。
以下のクエリが非常に遅く、改善したいと考えています。

【テーブル構造】
CREATE TABLE orders (
    id INT PRIMARY KEY,
    user_id INT,
    product_id INT,
    order_date DATETIME,
    amount DECIMAL(10,2)
);

【遅いクエリ】
SELECT * FROM orders WHERE user_id = 12345 AND order_date >= '2026-01-01' ORDER BY order_date DESC;

【EXPLAINの結果】
type: ALL, rows: 1000000, Extra: Using filesort

この結果を分析し、
1. なぜ遅いのかの原因解説
2. 作成すべき最適なインデックス（複合インデックスを含む）の提案
3. なぜその順番でカラムを並べるべきかの理由
をステップバイステップで教えてください。」

AIは即座に「`user_id`で絞り込み、かつ`order_date`で範囲検索とソートを行っているため、`(user_id, order_date)`の複合インデックスが最適です」といった論理的な回答を返してくれます。これを利用しない手はありません。

---

まとめ：パフォーマンスを制する者がデータベースを制す

第7回の講座、お疲れ様でした！

今日は、データベースの「速度」という、実務において最もクリティカルな課題に挑みました。インデックスの仕組み、B-treeの恩恵、`EXPLAIN`による診断、そして設計上のトレードオフ。これらを理解したあなたは、もう「ただデータを保存できる人」ではなく、「大規模なトラフィックに耐えうるシステムを設計できるエンジニア」への階段を登っています。

インデックスは「貼れば速くなる魔法」ではなく、「計画的に配置する戦略」です。常に`EXPLAIN`を確認する癖をつけ、AIを壁打ち相手にしながら、世界一スマートなデータベースを構築していきましょう。

次回はいよいよ最終回。第8回「バックアップと復旧：mysqldumpによるデータ保護と自動化」です。どんなに速いデータベースを作っても、データが消えてしまえば終わりです。エンジニアの最後の砦である「データ保護」の極意を伝授します。最後まで走り抜けましょう！