はじめに：正直なところ、バックエンド開発に携わってきたここ数年、 リリース後にたった1つの遅いSQLクエリが原因でデータベースがダウンしてしまう事故を数多く目にしてきました。ある時、午前2時に呼び出され、本番環境のMySQLのCPU使用率が90%まで急上昇していました。調べてみると、ある集計SQLが30秒も実行されており、接続プールが満杯になり、サービス全体が崩壊寸前でした。その出来事をきっかけに、私は遅いクエリの対策の重要性を真に認識するようになりました。この記事では、私が経験した失敗や蓄積したノウハウをまとめました。皆さんが無駄な回り道をせずに済む一助となれば幸いです。

目次

• • 一、なぜスロークエリはパフォーマンスの敵なのか？
  • • [1.1 実際に起きた本番環境での事故](#1.1 実際に起きた本番環境での事故)
    • [1.2 スロークエリの危険性](#1.2 スロークエリの危険性)
    • [1.3 日常生活での例え：渋滞](#1.3 日常生活での例え：渋滞)
  • 2. スロークエリログの有効化
  • • [2.1 MySQLの設定](#2.1 MySQLの設定)
    • [2.2 スロークエリログの確認](#2.2 スロークエリログの確認)
    • [2.3 スロークエリログ分析の要点](#2.3 スロークエリログ分析の要点)
  • 三、EXPLAINの各フィールドを1行ずつ解説
  • • [3.1 EXPLAINの基本用法](#3.1 EXPLAINの基本用法)
    • [3.2 id列：実行順序](#3.2 id列：実行順序)
    • [3.3 select_type列：クエリタイプ](#3.3 select_type列：クエリタイプ)
    • [3.4 table列：アクセスされるテーブル](#3.4 table列：アクセスされるテーブル)
    • [3.5 type列：アクセスタイプ（重要！）](#3.5 type列：アクセスタイプ（重要！）)
    • [3.6 possible_keys列：使用可能なインデックス](#3.6 possible_keys列：使用可能なインデックス)
    • [3.7 key列：実際に使用されたインデックス](#3.7 key列：実際に使用されたインデックス)
    • [3.8 key_len列：インデックスの使用長](#3.8 key_len列：インデックスの使用長)
    • [3.9 ref列：インデックスのマッチ条件](#3.9 ref列：インデックスのマッチ条件)
    • [3.10 rows列：推定スキャン行数](#3.10 rows列：推定スキャン行数)
    • [3.11 Extra列：追加情報（重要！）](#3.11 Extra列：追加情報（重要！）)
  • 4. よくある遅いクエリのシナリオと最適化
  • • [4.1 シナリオ1：全テーブルスキャン（type=ALL）](#4.1 シナリオ1：全テーブルスキャン（type=ALL）)
    • [4.2 シナリオ2：インデックスの無効化](#4.2 シナリオ2：インデックスの無効化)
    • [4.3 シナリオ3：Using filesort（ORDER BYにインデックスなし） ](#4.3 シナリオ3：Using filesort（ORDER BYにインデックスなし）)
    • [4.4 シナリオ4：Using temporary（GROUP BY/DISTINCTにインデックスなし）](#4.4 シナリオ4：Using temporary（GROUP BY/DISTINCTにインデックスなし）)
    • [4.5 シナリオ5： 大規模テーブルのJOIN効率が低い](#4.5 シナリオ5：大規模テーブルのJOIN効率が低い)
  • 五、スロークエリ管理体制
  • • [5.1 事前：SQLレビュー](#5.1 事前：SQLレビュー)
    • [5.2 実行中：リアルタイム監視](#5.2 実行中：リアルタイム監視)
    • [5.3 事後：定期分析](#5.3 事後：定期分析)
    • [5.4 管理プロセス](#5.4 管理プロセス)
  • 六、落とし穴ガイド
  • 七、Q&A
  • • [Q1：EXPLAINとEXPLAIN ANALYZEの違いは何ですか？](#Q1：EXPLAINとEXPLAIN ANALYZEの違いは何ですか？)
    • Q2：インデックスを追加したのに、クエリが依然として遅いのはなぜですか？
    • Q3：スロークエリログに記録されたデータが多すぎる場合、どのようにフィルタリングすればよいですか？
  • 8. 面接で頻出するポイントまとめ
  • • ポイント1：EXPLAINのtype列にはどのような値がありますか？パフォーマンス順はどのようになっていますか？
    • [ポイント2：Extra列の「Using filesort」と「Using temporary」は何を意味しますか？](#出題ポイント2：Extra列の「Using filesort」と「Using temporary」は何を意味しますか？)
    • 出題ポイント3：オーバーレイインデックスとは何ですか？
    • 出題ポイント4：インデックスが機能しなくなる一般的なシナリオにはどのようなものがありますか？
    • 出題ポイント5：遅いSQLをどのように分析しますか？
  • 九、模擬面接官の質問と参考回答
  • • シナリオ問題1：本番環境のCPU使用率が急上昇しています。これがスロークエリが原因かどうかをどのように判断しますか？
    • [シナリオ問題2：ページングクエリ `LIMIT 1000000, 10` の実行が非常に遅い場合、どのように最適化しますか？](# シナリオ問題2：ページングクエリ LIMIT 1000000, 10 の実行が遅い場合、どのように最適化しますか？)
    • [シナリオ問題3：テーブルに複合インデックス (a, b, c) があります。以下のSQLではインデックスを利用できますか？](# シナリオ問題3：テーブルに複合インデックス (a, b, c) がありますが、以下のSQLでインデックスは利用できますか？）
    • シナリオ問題4：インデックスを追加したにもかかわらず、EXPLAINの結果でインデックスが使用されていないと表示されます。考えられる原因は何でしょうか？
    • シナリオ問題5：遅いクエリの管理策をどのように設計すればよいでしょうか？
  • 10. ディスカッショントピック
  • 十一、参考資料

一、なぜ遅延クエリはパフォーマンスの敵なのか？

1.1 実際に起きた本番環境でのインシデント

昨年の「ダブルイレブン」直前、当社のシステムの注文照会インターフェースが突然タイムアウトを起こしました。

監視アラートが鳴りやまなくなり、MySQLのCPU使用率は90%まで急上昇し、QPSは通常の2000から200へと急落しました。

緊急調査の結果、運用担当者が以下の統計用SQLを実行していたことが判明しました：

SELECT COUNT(*) FROM order WHERE create_time > 『2024-01-01』 AND status = 1;

このSQLは30秒にわたり実行され、800万行のデータをスキャンしました。

コネクションプールが満杯となり、他の正常なリクエストがすべて待機列に並んでしまい、システム全体が崩壊寸前となった。

1.2 スロークエリの危険性

1.3 生活における例え：交通渋滞

スロークエリは、都市の幹線道路での深刻な交通渋滞のようなものです。

1本の幹線道路が渋滞すると、都市全体の交通に影響が及びます。

救急車や消防車が通れなくなり、その結果は想像に難くありません。

データベースも同様で、1つのスローSQLがシステム全体を機能不全に陥らせる可能性があります。

二、スロークエリログの有効化

2.1 MySQLの設定

MySQLの設定ファイル（通常は my.cnf または my.ini）を見つけ、以下の設定を追加または変更します：

[mysqld]
# スロークエリログを有効にする
slow_query_log = 1

# スロークエリログファイルのパス
slow_query_log_file = /var/log/mysql/slow.log

# 1秒を超えるクエリをスロークエリとして記録
long_query_time = 1

# インデックスを使用していないクエリを記録（有効化を推奨）
log_queries_not_using_indexes = 1

変更後、MySQLを再起動します：

sudo systemctl restart mysql

2.2 スロークエリログの確認

方法1：ログファイルを直接確認する

# 最新のスロークエリを確認する
tail -f /var/log/mysql/slow.log

方法2：mysqldumpslow ツールを使用する

# 実行時間順に並べ替え、上位10件を表示
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

# 実行回数順に並べ替えて表示
mysqldumpslow -s c -t 10 /var/log/mysql/slow.log

方法3：pt-query-digest ツールを使用する（推奨）

# Percona Toolkit のインストール
sudo apt-get install percona-toolkit

# スロークエリログを分析し、詳細なレポートを生成
pt-query-digest /var/log/mysql/slow.log > slow_query_report.txt

pt-query-digest 出力内容：

• Rank：クエリ実行時間の割合に基づく順位
• Query ID：クエリのフィンガープリント
• Response time：総応答時間と1回あたりの平均時間
• Calls：実行回数
• R/Call：1回の呼び出しあたりの平均時間
• V/M：応答時間の分散の平均（値が大きいほど不安定）

2.3 低速クエリログ分析の要点

三、EXPLAINフィールドの行ごとの解説

3.1 EXPLAINの基本用法

EXPLAIN SELECT * FROM user WHERE id = 100;

出力は概ね以下のようになります：

+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+---------- ---+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| 1 | SIMPLE | user | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
+----+---------- ---+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+

以下、各フィールドごとに解説します。

3.2 id列：実行順序

idが同じ場合は、上から順に実行されます。idが異なる場合は、idが大きいものから先に実行されます。

-- idが同じ場合
EXPLAIN SELECT * FROM user u, order o WHERE u.id = o.user_id;
-- idがすべて1の場合、userテーブルが先に実行され、次にorderテーブルが実行される

-- idが異なる場合（サブクエリ）
EXPLAIN SELECT * FROM user WHERE id = (SELECT user_id FROM order WHERE id = 100);
-- サブクエリのid=2が先に実行され、外側のid=1が後に実行される

3.3 select_type列：クエリタイプ

3.4 table列：アクセスされたテーブル

現在の行がどのテーブルにアクセスしているかを表示します。

派生テーブルの場合は、<derivedN>と表示され、NはサブクエリのIDです。

3.5 type列：アクセス種別（重要！）

typeはSQLのパフォーマンスを判断する核心的な指標であり、パフォーマンスの高い順から低い順は以下の通りです：

system > const > eq_ref > ref > range > index > ALL

落とし穴への注意 ：typeに index または ALL が表示された場合は注意が必要です。indexと表示されているのを見て、「インデックスが使われているから問題ない」と誤解する人を多く見かけますが、実際にはindexはインデックスツリー全体をスキャンするものであり、全テーブルスキャンと大差ありません！

3.6 possible_keys列：使用される可能性のあるインデックス

MySQLが使用される可能性があると判断したインデックスを表示します。

注意：あくまで「可能性」であり、実際に使用されるとは限りません。

3.7 key列：実際に使用されたインデックス

MySQLが実際に選択したインデックスが表示されます。

NULLの場合は、インデックスが使用されていないことを示します。

3.8 key_len列：インデックスの使用長さ

このフィールドは非常に重要です！複合インデックスで実際に何個のフィールドが使用されたかを判断できます。

例：

-- 複合インデックス idx_name_age (name, age)
EXPLAIN SELECT * FROM user WHERE name = 『張三』 AND age = 20;
-- key_len = 82 + 4 = 86。これは、2つのフィールドが両方とも使用されたことを示している

EXPLAIN SELECT * FROM user WHERE name = 『張三』;
-- key_len = 82。nameフィールドのみが使用されていることを示す

3.9 ref列：インデックスのマッチング条件

インデックスのどの列が使用されたかを示す。一般的な値：

• const：定数一致
• データベース名.テーブル名.カラム名：テーブルのカラム一致

3.10 rows列：推定スキャン行数

MySQLがスキャンする必要があると推定する行数。この数値は小さいほど良い。

落とし穴への注意：「rows」は推定値であり、実際の値ではない！統計情報に基づいて計算されるため、統計情報が古くなっている場合、この値には大きな誤差が生じる可能性がある。私もこの落とし穴に陥ったことがある。EXPLAINではrows=100と表示されていたが、実際に実行すると100万行がスキャンされた。

3.11 Extra列：追加情報（重要！）

例解説：

-- オーバーレイインデックス、パフォーマンス良好
EXPLAIN SELECT id, name FROM user WHERE name = 『張三』;
-- Extra: Using index

-- ファイルソート、パフォーマンス不良
EXPLAIN SELECT * FROM user ORDER BY age;
-- Extra: Using filesort

-- 一時テーブルを使用
EXPLAIN SELECT status, COUNT(*) FROM user GROUP BY status;
-- Extra: Using temporary; Using filesort

4. よくある低速クエリのシナリオと最適化

4.1 シナリオ1：全テーブルスキャン（type=ALL）

問題となるSQL：

-- userテーブルのphoneカラムにインデックスがない
EXPLAIN SELECT * FROM user WHERE phone = 『13800138000』;

EXPLAINの結果：

type: ALL
rows: 1000000
Extra: Using where

最適化案：インデックスの追加

-- インデックスの追加
ALTER TABLE user ADD INDEX idx_phone (phone);

-- 再度EXPLAINを実行
type: ref
rows: 1
key: idx_phone

4.2 シナリオ2：インデックスの無効化

2.1 暗黙的な型変換

-- phoneはvarchar型だが、数値が渡されている
EXPLAIN SELECT * FROM user WHERE phone = 13800138000;
-- type: ALL、インデックスが無効化！

-- 正しい記述
EXPLAIN SELECT * FROM user WHERE phone = 『13800138000』;
-- type: ref、インデックスが有効

2.2 インデックスフィールドに対する関数操作

-- エラー：create_timeに対する関数操作
EXPLAIN SELECT * FROM user WHERE YEAR(create_time) = 2024;
-- type: ALL

-- 正しい方法：範囲クエリに書き換える
EXPLAIN SELECT * FROM user WHERE create_time BETWEEN 『2024-01-01』 AND 『2024-12-31』;
-- type: range

2.3 like 『%xxx』 プレフィックス部分一致

-- プレフィックス部分一致のため、インデックスが無効
EXPLAIN SELECT * FROM user WHERE name LIKE 『%張三%』;
-- type: ALL

-- 末尾部分一致、インデックスが有効
EXPLAIN SELECT * FROM user WHERE name LIKE 『张三%』;
-- type: range

4.3 シナリオ3：Using filesort（ORDER BYにインデックスなし）

問題となるSQL：

-- create_timeでソートするが、create_timeにはインデックスがない
EXPLAIN SELECT * FROM order WHERE user_id = 100 ORDER BY create_time DESC LIMIT 10;
-- Extra: Using where; Using filesort

最適化案：複合インデックスを追加

-- 複合インデックスを追加（列の順序に注意！）
ALTER TABLE `order` ADD INDEX idx_user_time (user_id, create_time);

-- 再度EXPLAINを実行
-- Extra: Using index condition
-- インデックスが直接ソート済みであるため、追加のソートは不要

落とし穴への注意：複合インデックスのフィールド順序は重要です！等値検索のフィールドを前に、範囲検索／ソート用のフィールドを後ろに配置してください。インデックスのフィールド順序を逆に書いてしまい、結果としてインデックスが半分しか活用されていないケースを数多く見てきました。

4.4 シナリオ4：Using temporary（GROUP BY/DISTINCTにインデックスなし）

問題のSQL：

EXPLAIN SELECT status, COUNT(*) FROM user GROUP BY status;
-- Extra: Using temporary; Using filesort

最適化案：

-- 案1：GROUP BY フィールドにインデックスを追加
ALTER TABLE user ADD INDEX idx_status (status);

-- 案2：サブクエリに書き換える（データ量が多い場合）
SELECT status, cnt FROM (
    SELECT status, COUNT(*) AS cnt FROM user WHERE id > 0 GROUP BY status
) t;

4.5 シナリオ5：大規模テーブルのJOIN効率が低い

問題のSQL：

-- userテーブルは1,000万行、orderテーブルは5,000万行
EXPLAIN SELECT * FROM user u 
JOIN `order` o ON u.id = o.user_id
 
WHERE u.status = 1;

最適化案：小さいテーブルで大きいテーブルを駆動

-- 駆動テーブルが小さい結果セットであることを確保
EXPLAIN SELECT * FROM 
(SELECT * FROM user WHERE status = 1) u 
JOIN `order` o ON u.id = o.user_id;

-- あるいはSTRAIGHT_JOINを使用して結合順序を強制する
EXPLAIN SELECT * FROM user u 
STRAIGHT_JOIN `order` o ON u.id = o.user_id 
WHERE u.status = 1;

同時に、JOINフィールドにインデックスが設定されていることを確認する：

ALTER TABLE `order` ADD INDEX idx_user_id (user_id);

5. 遅延クエリ管理体制

5.1 事前：SQLレビュー

コードレビューの際には、必ずSQLを確認する必要があります：

• 新規に追加されたSQLはインデックスを利用しているか？
• フルテーブルスキャンのリスクはないか？
• 大規模なテーブルに対して全量操作を行っていないか？
• SELECT *を使用していないか？

レビューチェックリスト：

5.2 実行時：リアルタイム監視

Prometheus + Grafana によるクエリ遅延監視ダッシュボードの構築：

# Prometheus の設定
- job_name: 『mysql』
  static_configs:
    - targets: [『localhost:9104』]

主要な監視指標：

5.3 事後対応：定期的な分析

毎週の遅延クエリTOP10分析レポートテンプレート：

1. クエリSQL
2. 実行回数 / 平均実行時間
3. スキャン行数 / 返された行数
4. EXPLAIN分析
5. 最適化の提案
6. 最適化後の予想パフォーマンス向上率

5.4 ガバナンスプロセス

遅延クエリの発見 → EXPLAIN分析 → 原因の特定 → SQLの最適化／インデックスの追加 → 効果の検証 → 継続的な監視
     ↑ |
     └──────────────── 定期点検を行い、閉ループを形成 ────────────────────────────┘

六、落とし穴ガイド

落とし穴1：EXPLAINのrowsは推定値であり、実際の値ではない

私もこの落とし穴にはまりました。開発環境のデータ量は少なく、EXPLAINではrows=100と表示されていたため、自信を持って本番環境にデプロイしました。しかし、本番環境では800万件のデータがあり、100万行がスキャンされてしまいました。必ず本番環境のレプリカで検証してください！
落とし穴2：開発環境と本番環境のEXPLAIN結果に大きな差異がある

開発環境ではデータが数百件しかないため、オプティマイザがフルテーブルスキャンを選択する可能性があります。本番環境ではデータが数百万件あり、同じSQLでもインデックス経由で処理される場合があります。必ずデータ量が近い環境で検証してください。
落とし穴3：インデックスは正しく設定されているのに、実行計画でインデックスが使用されない

インデックスが明らかに存在しているにもかかわらず、EXPLAINでkey=NULLと表示されることがあります。これは統計情報が古くなっている可能性が高いため、以下のコマンドを実行してください：

ANALYZE TABLE user;

統計情報を更新すると、オプティマイザは正しいインデックスを選択するようになります。
落とし穴4：オプティマイザの選択ミス

ごく稀に、オプティマイザが誤った実行計画を選択することがあります。FORCE INDEX を使用してインデックスを強制的に使用させることができます：

SELECT * FROM user FORCE INDEX (idx_phone) WHERE phone = 『13800138000』;

ただし、これはあくまで緊急時の手段であり、日常的に使用することは推奨されません。

7. 質問と回答

Q1：EXPLAINとEXPLAIN ANALYZEの違いは何ですか？

A：

EXPLAINは、実行計画（推定値）のみを表示し、実際にSQLを実行することはありません。

EXPLAIN ANALYZE （MySQL 8.0.18以降）はSQLを実際に実行し、実際の実行時間とスキャンされた行数を表示します。

-- 実行計画のみを確認
EXPLAIN SELECT * FROM user WHERE id = 1;

-- 実際に実行して分析（注意：SQLが実際に実行されます！）
EXPLAIN ANALYZE SELECT * FROM user WHERE id = 1;

本番環境では EXPLAIN ANALYZE の使用には注意が必要です。特に UPDATE/DELETE の場合！

Q2：インデックスを追加したのに、クエリの処理が遅いのはなぜですか？

A：

考えられる原因：

1. インデックスが使用されていない：EXPLAINのkey列がNULLになっていないか確認してください
2. テーブルへのアクセス回数が多すぎる： SELECT * により大量のテーブルスキャンが発生している。オーバーレイインデックスの導入を検討
3. データ量が多すぎる：インデックス経由であってもスキャン行数が多すぎる。テーブル分割やデータベース分割を検討
4. インデックスの選択性が低い：例えば性別フィールド（男性／女性のみ）の場合、インデックスの効果が非常に低い
5. サーバーの負荷が高い： ディスクI/Oが飽和し、CPU使用率が急上昇している場合、どんなに優れたインデックスでも意味がありません

Q3：スロークエリログに記録されるデータが多すぎる場合、どのようにフィルタリングすればよいですか？

A：

設定でフィルタリングできます：

# 10秒を超えるスロークエリのみを記録する
long_query_time = 10

# 管理ステートメント（ALTER TABLEなど）を記録しない
log_slow_admin_statements = 0

# スレーブサーバーの遅延クエリを記録しない
log_slow_slave_statements = 0

または、pt-query-digestのフィルタリングパラメータを使用する：

# クエリ時間が5秒を超えるもののみを分析
pt-query-digest --filter 『$event->{Query_time} > 5』 /var/log/mysql/slow.log

8. 面接で頻出するポイントまとめ

ポイント1：EXPLAINのtype列にはどのような値がありますか？パフォーマンス順はどのようになりますか？

答え：

system > const > eq_ref > ref > range > index > ALL

• system/const：主キーまたは一意インデックス。パフォーマンスが最も良い
• eq_ref：JOIN時の主キーによる結合
• ref：通常インデックスによる等値検索
• range：インデックスの範囲スキャン
• index：インデックスの全スキャン
• ALL：全テーブルスキャン。パフォーマンスが最も低い

出題ポイント2：Extra列の「Using filesort」と「Using temporary」は何を意味しますか？

解答：

• Using filesort：MySQLがインデックスを利用してソートを完了できず、追加のソート操作が必要となる。通常、ORDER BY 句のフィールドにインデックスがないか、最左プレフィックス条件を満たしていないことが原因である。
• Using temporary：中間結果を保存するために一時テーブルを作成する必要がある。GROUP BY、DISTINCT、UNIONなどの操作でよく見られる。

どちらもパフォーマンス上の警告であり、最適化が必要である。

出題ポイント3：カバレッジインデックスとは何か？

答え：

カバーインデックスとは、クエリの対象となるすべてのフィールドがインデックスに含まれており、テーブルへのアクセス（テーブル参照）を必要としないものを指します。

-- インデックス：idx_name_age (name, age)
SELECT name, age FROM user WHERE name = 『張三』;
-- インデックスツリーを参照するだけで全データを取得でき、Extraには「Using index」と表示されます

利点：テーブルへのアクセスによるI/Oを削減し、クエリのパフォーマンスを大幅に向上させます。

出題ポイント4：インデックスが無効になる一般的なシナリオにはどのようなものがありますか？

答え：

1. インデックスフィールドに対する関数操作（YEAR(create_time)）
2. 暗黙的な型変換（文字列フィールドに数値を指定）
3. LIKEによるプレフィックス検索（『%張三%』）
4. 最左プレフィックス原則に違反している場合
5. インデックスフィールドが計算に利用される場合（id + 1 = 100）
6. OR条件の一部フィールドにインデックスがない場合
7. フルテーブルスキャンがインデックスより高速な場合（データ量が極めて少ない場合）

出題ポイント5：遅いSQLをどのように分析するか？

解答：

1. EXPLAINで実行計画を分析し、type、key、rows、Extraを確認
2. インデックスが使用されているか確認し、使用されていない場合は原因を分析
3. スキャン行数、rowsが過大ではないか
4. Extraを確認し、filesortやtemporary

がないか確認する

5. 遅延クエリログを確認し、実行時間と頻度を把握する
6. 最適化前後の比較を行い、EXPLAINで効果を検証する

WHERE a = 1 AND b = 2 AND c = 3; -- 可能、すべてにインデックスが適用される
WHERE a = 1 AND b = 2; -- 可能、aとbにインデックスが適用される
WHERE a = 1 AND c = 3; -- 可能、aのみにインデックスが適用される（cでプレフィックスが途切れる）
WHERE b = 2 AND c = 3; -- 不可、最左プレフィックスが途切れている
WHERE a = 1 AND b > 2 AND c = 3; -- 可、a, bが利用される （bは範囲条件のため、cは使用されない）