MySQLインデックスを正しく貼る：クエリ速度を10倍にする実装方法

MySQLのインデックスは適切に設定することで、クエリの実行速度を劇的に改善できます。この記事では、実務ですぐに活用できるインデックス設計の方法、貼り方のコツ、そしてよくあるミスを具体的なコード例で解説します。

インデックスが必要な理由と基本原理

データベースにテーブルが数百万行に達するとき、インデックスなしでのSELECT文は全行スキャン（フルテーブルスキャン）を行い、膨大な時間がかかります。インデックスは本の目次のような役割を果たし、データを高速に検索できる構造です。

MySQLの標準ストレージエンジンInnoDB（MySQL 5.7以降）では、B+木という構造でインデックスが管理されています。ここでは、実際の貼り方に焦点を当てます。

基本的なインデックスの貼り方

シングルカラムインデックスの作成

まず最も一般的な、単一カラムに対するインデックスを作成してみましょう。以下は実際の例です。

-- テーブル作成時にインデックスを定義
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    email VARCHAR(255) NOT NULL UNIQUE,
    username VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_username (username)  -- ここでインデックスを定義
);

-- 既存テーブルにインデックスを追加
ALTER TABLE users ADD INDEX idx_created_at (created_at);

-- CREATE INDEXを使う別の方法
CREATE INDEX idx_email ON users(email);

これらの3つの方法は本質的に同じ結果をもたらします。ただし、大規模テーブルに対してはALTER TABLEは実行時間が長くなる場合があるため、メンテナンス時間を確保してください。

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

WHERE句で複数のカラムを条件として使う場合、複合インデックスが有効です。ここで重要なのは「カラムの順序」です。左から右へ、より選別力の高い（ユニーク性が高い）カラムを配置するのが原則です。

-- 例：ユーザーのログテーブル
CREATE TABLE user_logs (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    action VARCHAR(50) NOT NULL,
    created_at TIMESTAMP,
    INDEX idx_user_action (user_id, action),  -- user_idが左に来る
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- よくあるクエリ：ユーザーIDとアクションで検索
SELECT * FROM user_logs 
WHERE user_id = 123 AND action = 'login' 
ORDER BY created_at DESC;

複合インデックスidx_user_action (user_id, action)は、以下のケースで有効に使われます：

• WHERE user_id = 123 AND action = 'login'（両方のカラムを使用）
• WHERE user_id = 123（左側のカラムのみ使用）

ただし、WHERE action = 'login'のように右側のカラムのみを使用する場合は、このインデックスは活用されません。

インデックスの性能確認と最適化

EXPLAINでクエリ計画を確認する

実際にインデックスが使われているかを確認するには、EXPLAINを使用します。

-- インデックスが効いているか確認
EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';

-- より詳細な情報を表示
EXPLAIN FORMAT=JSON SELECT * FROM users WHERE email = 'test@example.com';

EXPLAIN結果で見るべき重要なポイント：

• typeカラム：constやrefであれば良好。ALLはフルテーブルスキャン
• keyカラム：使用されているインデックス名。NULLならインデックスが活用されていない
• rowsカラム：スキャンされる推定行数。小さいほど効率的
• Extraカラム：Using indexがあればインデックスだけでクエリが完結

カバリングインデックスで完全一致クエリを高速化

SELECTで取得するカラムすべてをインデックスに含めると、テーブルへのアクセスが不要になります。これを「カバリングインデックス」と呼び、最高速のクエリが実現できます。

-- テーブル定義
CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(255),
    price DECIMAL(10, 2),
    category_id INT,
    stock INT
);

-- カバリングインデックス：category_idとpriceのみ取得するなら効果的
CREATE INDEX idx_category_price ON products(category_id, price, id);

-- このクエリはインデックスだけで完結（テーブルアクセスなし）
SELECT id, price FROM products 
WHERE category_id = 5 AND price > 100;

インデックス設計のハマりポイントと解決策

インデックスが使われないケース1：型の不一致

カラムの型と検索条件の値の型が異なる場合、MySQLは自動的に型変換を行い、インデックスが使われなくなります。

-- テーブル定義：user_idはINT
CREATE TABLE orders (
    id INT PRIMARY KEY,
    user_id INT,
    created_at TIMESTAMP,
    INDEX idx_user_id (user_id)
);

-- ❌ インデックスが使われない：文字列と比較
EXPLAIN SELECT * FROM orders WHERE user_id = '123';
-- 結果：user_idに自動変換されるが、インデックスが無視される可能性

-- ✅ インデックスが使われる：型を合わせる
EXPLAIN SELECT * FROM orders WHERE user_id = 123;

インデックスが使われないケース2：関数を使用する

WHERE句でカラムに関数を適用すると、インデックスが使われません。

-- ❌ インデックスが使われない：関数を使用
SELECT * FROM users WHERE YEAR(created_at) = 2024;

-- ✅ インデックスが使われる：範囲指定
SELECT * FROM users 
WHERE created_at >= '2024-01-01' 
  AND created_at < '2025-01-01';

インデックスが使われないケース3：ワイルドカード検索の開始位置

LIKE検索で開始位置にワイルドカードがあると、インデックスが効きません。

-- ❌ インデックスが使われない
SELECT * FROM products WHERE name LIKE '%phone%';

-- ✅ インデックスが使われる：開始位置が固定
SELECT * FROM products WHERE name LIKE 'phone%';

本番環境での実装ステップ

段階的なインデックス追加と性能監視

本番環境では、一度に大量のインデックスを追加するのは避けてください。段階的に追加し、各段階で性能を監視します。

-- ステップ1：スロークエリログを有効化して遅いクエリを発見
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2;  -- 2秒以上のクエリを記録

-- ステップ2：Performance Schemaで詳細な統計情報を確認
SELECT OBJECT_SCHEMA, OBJECT_NAME, COUNT_READ, COUNT_WRITE 
FROM performance_schema.table_io_waits_summary_by_index_usage 
WHERE OBJECT_SCHEMA != 'mysql' 
ORDER BY COUNT_READ DESC;

-- ステップ3：必要なインデックスのみを作成
ALTER TABLE target_table ADD INDEX idx_new (column_name);

-- ステップ4：インデックスサイズを確認
SELECT 
    TABLE_NAME, 
    INDEX_NAME, 
    ROUND(STAT_VALUE * @@innodb_page_size / 1024 / 1024, 2) AS size_mb
FROM mysql.innodb_index_stats 
WHERE STAT_NAME = 'size' 
  AND TABLE_NAME = 'target_table';

インデックスを使うべき場面と使うべきでない場面

インデックスを貼るべき場面

• WHERE句で頻繁に検索されるカラム
• JOINの条件に使用されるカラム
• ORDER BY句で使用されるカラム
• ユニーク性が高いカラム（user_id、emailなど）

インデックスを貼るべきでない場面

• カーディナリティが非常に低いカラム（booleanカラムなど。値が2種類だけ）
• 頻繁にUPDATEやINSERTが行われるカラム（インデックス更新のオーバーヘッド）
• 容量が極めて大きいテーブルへの小規模な読み取り専用カラム

類似ツールとの比較

MySQLのインデックス以外に、クエリ最適化の方法として「キャッシュ層（Redis、Memcached）」や「検索エンジン（Elasticsearch）」があります。インデックスは構造化データの高速検索に特化しており、まずはインデックスで改善を試みるべきです。キャッシュは一時的なデータ、Elasticsearchは全文検索に適しており、用途が異なります。

よくある質問

まとめ

• インデックスはWHERE句、JOIN条件、ORDER BYで使われるカラムに優先的に貼る
• 複合インデックスはカラムの順序が重要。選別力が高い（ユニーク性が高い）カラムを左に配置する
• EXPLAINコマンドで実際にインデックスが使われているか必ず確認する
• 関数の使用、ワイルドカード検索の開始位置、型の不一致はインデックスが効かない典型的なケース
• 本番環境では段階的にインデックスを追加し、スロークエリログで性能改善を監視する
• インデックスの過剰追加はINSERT・UPDATE・DELETE性能の低下をもたらすため、必要最小限に留める

MySQLの公式インデックス最適化ドキュ