MySQLスロークエリの根本原因を特定し、実装レベルで解決する方法

本記事では、MySQLのスロークエリログを活用した原因特定から、インデックス設計、クエリの最適化、実装レベルでの改善まで、実務で即座に適用できる具体的な解決手法を紹介します。スロークエリが発生しても、正しい診断と対応で90%以上のケースは改善可能です。

スロークエリが発生する4つの主要な原因

MySQLのパフォーマンス問題を解決するには、まず原因を正確に特定することが必須です。一般的に遭遇する主要な原因を整理しておきます。

1. インデックスの不足または不適切な設計

最も一般的な原因です。WHERE句やJOIN条件で使用されるカラムにインデックスがない、または複合インデックスの順序が最適でない場合、全テーブルスキャン（Full Table Scan）が発生し、パフォーマンスが劇的に低下します。

2. クエリの論理的な非効率性

複数回のクエリ実行やN+1問題、不要なJOIN、サブクエリの過多使用など、クエリそのものの構造に問題がある場合があります。

3. テーブルのスキャン範囲が大きすぎる

データベースサイズが大きくなると、フィルタリング条件が弱いクエリは膨大なレコードを読み込む必要があり、I/O負荷が増加します。

4. メモリ不足やバッファプールの枯渇

サーバーのメモリが不足していたり、innodb_buffer_poolサイズが適切でない場合、ディスクアクセスが頻発してスロークエリが増加します。

スロークエリログの有効化と解析手法

原因を特定するには、まずスロークエリログを有効化し、どのクエリが遅いのかを把握する必要があります。

スロークエリログの設定

MySQLのmy.cnfまたはmy.iniに以下の設定を追加し、スロークエリログを有効化してください：

[mysqld]
# スロークエリログの有効化
slow_query_log = 1
# ログファイルの保存先
slow_query_log_file = /var/log/mysql/slow-query.log
# スロークエリの閾値（秒）：2秒以上のクエリをログに記録
long_query_time = 2
# インデックスを使用しないクエリもログに記録
log_queries_not_using_indexes = 1
# ログに記録するクエリの最小行数
min_examined_row_limit = 1000

設定後、MySQLを再起動してください：

# macOS
brew services restart mysql

# Linux
sudo systemctl restart mysql

スロークエリログの分析

mysqldumpslowコマンドを使用して、ログを分析します：

# 実行時間が長い順にスロークエリを表示
mysqldumpslow -s t -t 10 /var/log/mysql/slow-query.log

# ロック時間が長い順に表示
mysqldumpslow -s l -t 10 /var/log/mysql/slow-query.log

# クエリの実行回数でソート
mysqldumpslow -s c -t 10 /var/log/mysql/slow-query.log

実装レベルでのスロークエリ解決方法

ステップ1: EXPLAIN で実行計画を確認

問題のクエリが見つかったら、EXPLAINで実行計画を確認します。特にtypeカラムがINDEX以上であることが目安です：

EXPLAIN FORMAT=JSON
SELECT u.id, u.name, o.order_date, o.total_amount
FROM users u
INNER JOIN orders o ON u.id = o.user_id
WHERE u.created_at >= '2024-01-01'
AND o.status = 'completed'
ORDER BY o.order_date DESC
LIMIT 100;

返される結果の重要なポイント：

• type: 「ALL」は全テーブルスキャン、「range」以上が目標。「const」「eq_ref」が最速
• rows: 読み込み予定行数。この値が大きいほど遅くなる傾向
• key: 使用されるインデックス。NULLなら全スキャン
• Extra: 「Using filesort」「Using temporary」は避けるべき警告

ステップ2: インデックスの追加・最適化

上記のクエリが非効率な場合、以下のインデックスを追加します：

# usersテーブルのcreated_atで範囲検索を高速化
ALTER TABLE users ADD INDEX idx_created_at (created_at);

# ordersテーブルの複合インデックス
# user_idでのJOIN、statusでフィルタ、order_dateでソート
ALTER TABLE orders 
ADD INDEX idx_user_status_date (user_id, status, order_date);

# 複合インデックスの列順は重要：
# 1. WHERE句の等号条件（status）
# 2. WHERE句の範囲条件（無い場合はスキップ）
# 3. ORDER BY句（order_date）

ステップ3: クエリ構造の改善

以下は実務で頻出するアンチパターンと改善案です：

アンチパターン1: N+1問題

// 悪い例：ユーザーごとにクエリが実行される
$users = $pdo->query("SELECT id, name FROM users LIMIT 100");
foreach ($users as $user) {
    $orders = $pdo->query("SELECT * FROM orders WHERE user_id = {$user['id']}");
}
// 結果：101回のクエリが実行される（1 + 100）

改善案：JOINまたはINを使用して1クエリで完結させます：

// 良い例：1クエリで完結
SELECT u.id, u.name, o.order_date, o.total_amount
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
LIMIT 100;

アンチパターン2: サブクエリの過多使用

// 悪い例
SELECT name FROM users 
WHERE id IN (
    SELECT user_id FROM orders 
    WHERE order_date >= '2024-01-01'
    AND total_amount > (
        SELECT AVG(total_amount) FROM orders
    )
);
// 問題：複数のサブクエリが実行され、最適化が困難

改善案：JOINとWITH句を使用：

// 良い例
WITH avg_order AS (
    SELECT AVG(total_amount) AS avg_amount FROM orders
)
SELECT DISTINCT u.id, u.name
FROM users u
INNER JOIN orders o ON u.id = o.user_id
CROSS JOIN avg_order
WHERE o.order_date >= '2024-01-01'
AND o.total_amount > avg_order.avg_amount;

ステップ4: インデックスの利用可能性を確認

インデックスを作成しても、クエリ内での計算や暗黙の型変換によってインデックスが使用されないことがあります：

// インデックスが使用されない例1：カラムの計算
SELECT * FROM orders 
WHERE YEAR(order_date) = 2024;  // ❌ インデックス未使用

// 改善：計算を避ける
SELECT * FROM orders 
WHERE order_date >= '2024-01-01' 
AND order_date < '2025-01-01';   // ✅ インデックス使用

// インデックスが使用されない例2：型の不一致
SELECT * FROM users 
WHERE id = '123';  // ❌ 文字列と数値の比較

// 改善：型を統一
SELECT * FROM users 
WHERE id = 123;    // ✅ インデックス使用

サーバーレベルでの最適化

innodb_buffer_poolサイズの調整

データベースサーバーに搭載されているRAM容量の60～80%をinnodb_buffer_poolに割り当てることが推奨されています：

[mysqld]
# 例：16GBのRAM搭載サーバーの場合
innodb_buffer_pool_size = 12G

# innodb_buffer_poolのインスタンス数（複数化で並行アクセス性能向上）
innodb_buffer_pool_instances = 8

クエリキャッシュの検討

MySQL 8.0以降ではクエリキャッシュが廃止されているため、RedisやMemcachedなどの外部キャッシュを検討してください。同一クエリが頻繁に実行される場合、キャッシュレイヤーは非常に効果的です。

よくある質問

まとめ

• スロークエリログを有効化し、mysqldumpslowで問題クエリを特定することが第一歩
• EXPLAINコマンドで実行計画を確認し、インデックスが使用されているか検証
• 複合インデックスの列順（等号条件 → 範囲条件 → ORDER BY）を正しく設計することで、90%のスロークエリは改善可能
• N+1問題やサブクエリの過多など、クエリ構造そのものの見直しも重要
• innodb_buffer_poolサイズをサーバーリソースに応じて適切に調整
• キャッシュレイヤー（Redis等）の導入で、アプリケーションレベルでの改善も検討

MySQL 公式ドキュメント - クエリ最適化を定期的に参照し、最新のベストプラクティスを把握することをお勧めします。