MySQLインデックス最適化の完全ガイド

1. インデックスを最適化する理由

MySQLデータベースにおいて、インデックスはクエリパフォーマンス向上の鍵となります。統計によると、適切なインデックス設計により、クエリ速度が10倍から100倍向上することがあります。ただし、インデックスは二面性があり、正しく使用すればパフォーマンスが大幅に向上しますが、誤用すると書き込み速度が低下したり、ストレージが無駄になったりするリスクがあります。

2. インデックス最適化の主要戦略

1) 適切なインデックスタイプを選択する

-- インデックス作成例
CREATE INDEX idx_user_age ON users(age);          -- B-Treeインデックス（デフォルト）
CREATE FULLTEXT INDEX idx_content ON articles(content);  -- フルテキストインデックス
ALTER TABLE orders ADD SPATIAL INDEX idx_geo(location);  -- スペーシャルインデックス

最適化提案：

  • 80%のシーンでB-Treeインデックスを使用
  • テキスト検索にはフルテキストインデックス（FULLTEXT）を使用
  • 地理データにはスペーシャルインデックス（SPATIAL）を使用

2) 不要なインデックスを避ける

一般的な誤解：すべてのフィールドにインデックスを作成する

-- 冗長なインデックス例（最適化が必要）
CREATE INDEX idx_name ON users(name);
CREATE INDEX idx_name_email ON users(name, email);  -- nameを含む複合インデックスは冗長

解決策：

  • 定期的にSHOW INDEX FROM table_nameを使用してインデックスを確認
  • sys.schema_redundant_indexesビューを使用（MySQL 8.0+）

3) 最左プレフィックス原則の実践

-- 複合インデックス例
CREATE INDEX idx_composite ON employees(department_id, salary, hire_date);

-- 効果的なクエリ：
SELECT * FROM employees 
WHERE department_id = 5 AND salary > 10000;

-- 無効なクエリ：
SELECT * FROM employees 
WHERE salary > 10000;  -- インデックスの最初の列を使用していない

重要なポイント：

  • 複合インデックスの列順序＝クエリ条件順＋ソート要求
  • 区別度の高い列を左側に配置する

4) インデックス選択の黄金法則

優先すべきフィールド：

  • WHERE句で頻繁に使用されるフィールド
  • JOIN接続フィールド
  • ORDER BY/GROUP BYフィールド
  • 高区別度フィールド（例：user_id）

回避すべき点：

  • 長いテキストフィールドにはインデックスを作成しない（プレフィックスインデックスを使用可能）
  • 低区別度フィールドには慎重にインデックスを作成（例：性別フィールド）
-- プレフィックスインデックス例
CREATE INDEX idx_name_prefix ON customers(name(10));  -- 最初の10文字を使用

5) カバリングインデックスの最適化

原理：インデックスにクエリで必要なすべてのフィールドが含まれている

-- テーブル復帰が必要
SELECT * FROM products WHERE category = '電子製品';

-- カバリングインデックス最適化
CREATE INDEX idx_cover ON products(category, price, stock);
SELECT category, price FROM products WHERE category = '電子製品';

6) インデックス条件プッシュダウン（ICP）

MySQL 5.6+新機能：ストレージエンジン層でデータをフィルタリング

-- ICP有効化（デフォルトで有効）
SET optimizer_switch = 'index_condition_pushdown=on';

7) ソート最適化のコツ

-- filesortが必要なクエリ
SELECT * FROM orders ORDER BY create_time DESC LIMIT 100;

-- 最適化方法：
ALTER TABLE orders ADD INDEX idx_time_status (create_time, status);
SELECT * FROM orders 
WHERE create_time > '2023-01-01' 
ORDER BY create_time DESC LIMIT 100;

3. インデックス使用時の注意点

  1. 更新コスト：インデックスはINSERT/UPDATE/DELETEのオーバーヘッドを増加させる
  2. ストレージコスト：各インデックスはテーブルサイズの約10-30%を占める
  3. 統計情報：定期的にANALYZE TABLE table_nameを実行する
  4. インデックス失効シーン：
  • インデックス列に対して演算を行う：WHERE YEAR(create_time) = 2023
  • 先頭ワイルドカードを使用する：WHERE name LIKE '%張'
  • 暗黙の型変換：WHERE phone = 13800138000（phoneが文字列型の場合）

4. パフォーマンス検証ツール

  1. EXPLAIN実行計画：
EXPLAIN SELECT * FROM users WHERE age BETWEEN 20 AND 30;

特に注目すべき点：

  • type（アクセスタイプ）：少なくともrangeレベルに達していること
  • key（実際に使用されているインデックス）
  • Extra：Using index（カバリングインデックス）
  1. スローログ解析：
# my.cnf設定
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1

タグ: MySQL Indexing Query_Optimization

7月31日 04:16 投稿

ホットタグ

©2026 異端開発室