😺

なぜインデックスが必要なのか

2025/08/08に公開

MySQL

idea

インデックスって何？

インデックスは本の索引のようなものです。
本で「MySQL」について調べる時

索引なし → 1ページ目から順番に全部読む（遅い）
索引あり → 索引で「MySQL: 45ページ」を見つけて直接ジャンプ（速い）

データベースも同じで、インデックスがあると欲しいデータに直接ジャンプできます。

どんな時にインデックスが使われる？

1. 特定のデータを探す時（WHERE句）

-- ユーザーをメールアドレスで探す
SELECT * FROM users WHERE email = 'user@example.com';

2. データを並び替える時（ORDER BY句）

-- 商品を価格順で並べる
SELECT * FROM products ORDER BY price;

3. 2つのテーブルを結合する時（JOIN）

-- ユーザーと注文を結合
SELECT * FROM users u JOIN orders o ON u.id = o.user_id;

4. 外部キー制約の参照先カラム（必須）

-- 外部キー制約を設定する場合、参照先カラムにインデックスが必須
CREATE TABLE departments (
    dept_id INT PRIMARY KEY,  -- 自動的にインデックスが作成される
    dept_name VARCHAR(100)
);

CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    emp_name VARCHAR(100),
    dept_id INT,
    -- この外部キー制約により、departments.dept_id にインデックスが必須
    FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
);

外部キー制約では参照先カラムにインデックスが必須な理由：

参照整合性チェックのために頻繁に参照先テーブルを検索する
インデックスがないと毎回テーブル全体をスキャンして非常に低速
INSERT/UPDATE時：参照先に該当値が存在するかチェック
DELETE/UPDATE時：参照元に依存するレコードがないかチェック

なぜインデックスで速くなる？

例：10万人のユーザーから「田中太郎」を探す

インデックスなしの場合

田中太郎を探しています...
1番目: 佐藤花子 ← 違う
2番目: 鈴木次郎 ← 違う
3番目: 高橋美咲 ← 違う
...
99,999番目: 田中太郎 ← 見つかった！

結果: 99,999回チェックが必要（最悪の場合）

インデックスありの場合

名前のインデックス（アルファベット順）:
あ行 → 1-5000番
か行 → 5001-15000番  
さ行 → 15001-25000番
た行 → 25001-35000番 ← 「田中」はここ
な行 → 35001-45000番
...

1. 「田中」は「た行」 → 25001-35000番を確認
2. その中で「田中太郎」を探す → 見つかった！

結果: 数回のチェックで発見

インデックスを作ってはいけない場合

1. 頻繁に更新するカラム

-- 悪い例
CREATE TABLE access_log (
    id INT PRIMARY KEY,
    user_id INT,
    access_count INT,  -- 毎回更新される
    
    -- これは避けるべき
    INDEX idx_access_count (access_count)
);

-- 問題: アクセスのたびにインデックスも更新が必要で遅くなる
UPDATE access_log SET access_count = access_count + 1 WHERE user_id = 123;

2. 値の種類が少ないカラム

-- 悪い例
CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    gender ENUM('男', '女'),  -- 2種類しかない
    
    -- 効果が薄い
    INDEX idx_gender (gender)
);

-- 問題: 「男」で検索しても半分のユーザーが該当してしまう
SELECT * FROM users WHERE gender = '男';

3. 小さなテーブル

-- 悪い例: 10行程度の設定テーブル
CREATE TABLE settings (
    id INT PRIMARY KEY,
    setting_name VARCHAR(50),
    setting_value VARCHAR(100),
    
    -- 無駄
    INDEX idx_setting_name (setting_name)
);

-- 10行なら全部チェックしても一瞬で終わる

実際にテーブルを作ってみよう

シンプルなユーザーテーブル

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,  -- 主キー（自動でインデックスが作られる）
    email VARCHAR(255) NOT NULL,
    name VARCHAR(100) NOT NULL,
    age INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    -- よく検索するカラムにインデックス
    UNIQUE INDEX idx_email (email),      -- メールアドレスで検索（重複なし）
    INDEX idx_name (name),               -- 名前で検索
    INDEX idx_age (age)                  -- 年齢で検索
);

商品テーブル

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    price INT NOT NULL,
    category VARCHAR(50) NOT NULL,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    -- 複合インデックス（よく一緒に使う条件）
    INDEX idx_category_active (category, is_active),  -- カテゴリ×有効な商品
    INDEX idx_price (price)                           -- 価格で並び替え
);

外部キー制約を含むテーブル設計例

-- カテゴリテーブル（親テーブル）
CREATE TABLE categories (
    id INT AUTO_INCREMENT PRIMARY KEY,  -- 自動的にインデックス作成
    category_code VARCHAR(10) UNIQUE,   -- UNIQUE制約で自動的にインデックス作成
    category_name VARCHAR(100) NOT NULL
);

-- 商品テーブル（子テーブル）
CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    price INT NOT NULL,
    category_id INT NOT NULL,  -- 外部キーカラム
    
    -- 外部キー制約：categories.id にはPRIMARY KEYでインデックス必須
    FOREIGN KEY (category_id) REFERENCES categories(id),
    
    INDEX idx_category_id (category_id)  -- 外部キーにもインデックス（推奨）
);

-- カテゴリコードでも参照したい場合
CREATE TABLE special_products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    category_code VARCHAR(10) NOT NULL,
    
    -- categories.category_code にはUNIQUE制約でインデックス必須
    FOREIGN KEY (category_code) REFERENCES categories(category_code)
);

複合インデックスって何？

複数のカラムを組み合わせたインデックスです。

良い例

-- よくあるクエリ
SELECT * FROM products WHERE category = 'スマホ' AND is_active = TRUE;

-- このクエリ用のインデックス
INDEX idx_category_active (category, is_active)

順序が大事

-- 良い順序: よく使う条件から順番に
INDEX idx_category_active (category, is_active)

-- この順序だと以下のクエリで効果的
SELECT * FROM products WHERE category = 'スマホ';                    -- ◯
SELECT * FROM products WHERE category = 'スマホ' AND is_active = 1;  -- ◯

-- 悪い順序
INDEX idx_active_category (is_active, category)

-- この順序だとこのクエリで効果が薄い
SELECT * FROM products WHERE category = 'スマホ';  -- △（効果が薄い）

インデックスが使われているか確認する

-- 実行計画を確認
EXPLAIN SELECT * FROM users WHERE email = 'user@example.com';

結果の見方

+----+-------+------+------+---------------+-----------+
| id | table | type | rows | key           | Extra     |
+----+-------+------+------+---------------+-----------+
|  1 | users | ref  |    1 | idx_email     |           |
+----+-------+------+------+---------------+-----------+

typeがrefやconst → インデックス使用（良い）
typeがALL → 全行スキャン（悪い）
keyに表示されるのが使用されたインデックス名

実践的な例：ブログシステム

-- ユーザーテーブル
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,  -- インデックス自動作成
    email VARCHAR(255) UNIQUE NOT NULL, -- UNIQUE制約でインデックス自動作成
    username VARCHAR(50) UNIQUE NOT NULL
);

-- 記事テーブル
CREATE TABLE articles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    content TEXT,
    author_id INT NOT NULL,
    status ENUM('draft', 'published') DEFAULT 'draft',
    published_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    -- 外部キー制約：users.id には PRIMARY KEY でインデックス必須
    FOREIGN KEY (author_id) REFERENCES users(id),
    
    -- よくあるクエリに基づいたインデックス
    INDEX idx_author_status (author_id, status),     -- 作者の公開記事一覧
    INDEX idx_published_date (status, published_at), -- 公開記事を日付順
    INDEX idx_title (title(50))                      -- タイトル検索（前方一致）
);

-- コメントテーブル
CREATE TABLE comments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    article_id INT NOT NULL,
    user_id INT NOT NULL,
    comment TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    -- 外部キー制約：参照先のインデックスは必須（既に存在）
    FOREIGN KEY (article_id) REFERENCES articles(id),
    FOREIGN KEY (user_id) REFERENCES users(id),
    
    -- 記事のコメント一覧表示用
    INDEX idx_article_created (article_id, created_at)
);

よく使うクエリとインデックス

-- 1. 特定作者の公開記事一覧
SELECT * FROM articles 
WHERE author_id = 123 AND status = 'published' 
ORDER BY published_at DESC;
-- → idx_author_status インデックスが活用される

-- 2. 最新の公開記事一覧
SELECT * FROM articles 
WHERE status = 'published' 
ORDER BY published_at DESC 
LIMIT 10;
-- → idx_published_date インデックスが活用される

-- 3. 記事のコメント一覧
SELECT * FROM comments 
WHERE article_id = 456 
ORDER BY created_at;
-- → idx_article_created インデックスが活用される

まとめ

インデックス設計の基本ルール

よく検索するカラムにインデックスを作る
複数の条件で検索する場合は複合インデックスを使う
外部キー制約の参照先カラムには必ずインデックスが必要（必須要件）
頻繁に更新するカラムや値の種類が少ないカラムは避ける
小さなテーブルには不要
作ったら実行計画で確認する

外部キー制約のインデックスについて特に注意

参照先カラム：必ずインデックスが必要（PRIMARY KEY、UNIQUE制約、または明示的にINDEXを作成）
参照元カラム：パフォーマンス向上のため推奨（必須ではないが、JOINが頻繁なら作成すべき）

簡単チェックリスト

WHERE句でよく使うカラムにインデックスはある？
ORDER BY句でよく使うカラムにインデックスはある？
JOINで使う外部キーにインデックスはある？
外部キー制約の参照先カラムにインデックスはある？（必須）
不要なインデックスは作っていない？
実行計画でちゃんとインデックスが使われている？

最初は基本的なインデックスから始めて、実際の使用状況を見ながら少しずつ最適化していくのがおすすめです！