MySQLのスロークエリのトラブルシューティング：ステップバイステップガイド
スローなデータベースクエリは、アプリケーションのパフォーマンス低下の最も一般的な原因の1つです。単一のクエリの実行に時間がかかりすぎると、貴重なサーバーリソース（CPU、I/O）が消費され、接続の飽和につながり、最終的にシステム全体の速度が低下します。これらのボトルネックを特定し、分析し、解決することは、健全で応答性の高いアプリケーションを維持するために不可欠です。
このガイドでは、MySQLのスロークエリのトラブルシューティングのための、包括的で実用的なステップバイステップのアプローチを提供します。最適なデータベースパフォーマンスを回復するために必要な、重要な構成手順、主要な診断ツール、および実証済みの最適化手法について説明します。
ステップ1：スロークエリログの有効化と設定
スロークエリのトラブルシューティングの基本は、スロークエリログ (Slow Query Log) です。MySQLは、このログを使用して、long_query_timeとして知られる、指定された実行時間しきい値を超えたクエリを記録します。
A. 構成変数
ロギングを有効にするには、通常、[mysqld]セクションのmy.cnf（Linux/Unix）またはmy.ini（Windows）構成ファイル内で、以下の変数を設定する必要があります。構成ファイルを変更する場合、通常、サーバーの再起動が必要です。
| 変数 | 説明 | 推奨値 |
|---|---|---|
slow_query_log |
ロギング機能を有効にします。 | 1 (オン) |
slow_query_log_file |
ログファイルへのパスを指定します。 | /var/log/mysql/mysql-slow.log |
long_query_time |
クエリが遅いと見なされるためのしきい値時間（秒単位）。 | 1 (1秒) またはそれ以下 (例: 0.5) |
log_queries_not_using_indexes |
実行時間に関係なく、インデックスを利用できなかったクエリをログに記録します。 | 1 (強く推奨) |
設定例（my.cnfの抜粋）
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 1
log_queries_not_using_indexes = 1
B. ステータスの確認と動的な設定
サーバーを再起動したくない場合は、現在のセッション（または、次の再起動まで持続するグローバル）に対して、動的にロギングを有効にすることができます。
-- 現在のステータスを確認
SHOW VARIABLES LIKE 'slow_query_log%';
SHOW VARIABLES LIKE 'long_query_time';
-- 再起動なしでグローバルに有効にする場合：
SET GLOBAL slow_query_log = 1;
SET GLOBAL long_query_time = 1;
ヒント: トラフィックの多いサーバーで
long_query_timeを低く設定しすぎると（例：0.1秒）、ディスク容量がすぐに満杯になる可能性があります。控えめな値（1秒）から開始し、主要なボトルネックを解決するにつれて徐々に値を下げてください。
ステップ2：スロークエリログの分析
ログがデータを収集し始めたら、次の課題は解釈です。スロークエリログは非常に大きく、反復的になる可能性があります。生のログファイルを手動で読むのは非効率的です。
A. mysqldumpslowの使用
標準のMySQLユーティリティであるmysqldumpslowは、ログエントリを集約および要約するために不可欠です。これは、同一のクエリ（IDや文字列などのパラメーターを無視）をグループ化し、カウント、実行時間、ロック時間、検査された行に関する統計を提供します。
一般的なmysqldumpslowコマンド
- 平均実行時間 (
t) でソートし、トップ10のクエリを表示:
bash
mysqldumpslow -s t -top 10 /path/to/mysql-slow.log
- 検査された行数 (
r) でソートし、類似のクエリを集約 (a):
bash
mysqldumpslow -s r -a /path/to/mysql-slow.log | less
- 合計ロック時間 (
l) でソート:
bash
mysqldumpslow -s l /path/to/mysql-slow.log
B. ボトルネックの特定
出力を見直す際は、以下の特性を示すクエリを優先してください。
- 高い合計時間: 頻繁に出現し、全体的な実行時間が長いクエリ（主要なボトルネック）。 (
tでソート) - 高いロック時間: テーブルまたは行ロックを待機するのに 상당한時間を費やしているクエリ。これは、トランザクションの問題または長時間実行される更新ステートメントを示していることがよくあります。
- 高い検査行数/送信行数: 10万行を検査しても10行しか返さないクエリは非常に非効率的であり、ほぼ間違いなくインデックスの欠落または不適切なインデックスを示しています。
エキスパートツール警告: 本番環境では、
mysqldumpslowよりも詳細なレポートと分析機能を提供する、Percona Toolkitのpt-query-digestなどの高度なツールの使用を検討してください。
ステップ3：EXPLAINによる詳細分析
問題のあるクエリが特定されたら、EXPLAINステートメントは、MySQLがそのクエリをどのように実行するかを理解するための最も強力なツールです。
使用方法
スロークエリの前にキーワードEXPLAINを付けるだけです。
EXPLAIN SELECT * FROM orders WHERE customer_id = 123 AND order_date > '2024-01-01';
主要なEXPLAINの出力列
EXPLAINの出力には、いくつかの重要なフィールドが含まれています。これらに細心の注意を払ってください。
1. type
これは結合タイプであり、テーブルがどのように結合されているか、または行がどのように取得されているかを示します。これは単一で最も重要な列です。
| タイプ | 効率 | 説明 |
|---|---|---|
system, const, eq_ref |
非常に優れている | 非常に高速な、一定時間でのルックアップ（主キー、一意インデックス）。 |
ref, range |
良好 | 非一意インデックスまたは範囲スキャンを使用したインデックス付きルックアップ（例：WHERE id > 10）。 |
index |
普通 | インデックス全体のスキャン。フルテーブルスキャンよりも高速ですが、大規模なデータセットでは非効率的です。 |
ALL |
劣悪 | フルテーブルスキャン。 クエリはテーブル内のすべての行を読み取る必要があります。これは、深刻なスロークエリの原因であると、ほぼ常に言えます。 |
2. rows
MySQLがクエリを実行するために検査する必要があると推定される行数です。少ない方が良いです。rowsがテーブルの合計行数に近い場合は、インデックスの欠落を探してください。
3. Extra
このフィールドは、内部操作に関する重要な情報を提供します。
Extra の値 |
意味 | 解決策 |
|---|---|---|
Using filesort |
MySQLがORDER BY句にインデックスを使用できなかったため、結果をメモリまたはディスク上でソートする必要がありました。 |
ソート列を含むインデックスを追加します。 |
Using temporary |
MySQLがクエリを処理するために一時テーブルを作成する必要がありました（多くの場合、GROUP BYまたはDISTINCTのため）。 |
クエリをリファクタリングするか、インデックスがグループ化列をカバーしていることを確認します。 |
Using index |
非常に優れている。クエリがインデックス構造（カバリングインデックス）のみを読み取ることで完全に満たされました。 | 最適なパフォーマンスです。 |
ステップ4：最適化手法
スロークエリの解決は、通常、インデックス作成、クエリの書き換え、および構成チューニングの3つの主要なカテゴリに分類されます。
B. インデックス作成戦略
インデックス作成は、type: ALLおよび高い「検査された行数」の問題を解決するための主要な方法です。
-
不足しているインデックスの特定:
WHERE句、JOIN条件、およびORDER BY句で頻繁に使用される列にインデックスを作成します。sql -- customer_idに関連するスロークエリの解決例 CREATE INDEX idx_customer_id ON orders (customer_id); -
複合インデックスの使用: クエリが複数の列でフィルタリングする場合（例：
WHERE country = 'US' AND city = 'New York'）、複合インデックスが必要になることがよくあります。sql -- 順序が重要です！最も制限的な列を最初に配置します。 CREATE INDEX idx_country_city ON address (country, city); -
カバリングインデックスの作成: カバリングインデックスには、クエリを満たすために必要なすべての列（フィルター列と選択された列の両方）が含まれます。これにより、MySQLはインデックス構造からのみデータを取得でき、結果として
Extra: Using indexとなります。sql -- クエリ: SELECT name, email FROM users WHERE active = 1; -- カバリングインデックス: CREATE INDEX idx_active_cover ON users (active, name, email);
B. クエリの書き換えとリファクタリング
インデックス作成が不十分な場合、クエリ自体に欠陥がある可能性があります。
SELECT *を避ける: 必要な列のみを選択します。これにより、ネットワークオーバーヘッドが減少し、カバリングインデックスの使用が可能になります。- 先頭のワイルドカードを最小限に抑える:
LIKE句の先頭でワイルドカードを使用する（WHERE name LIKE '%smith'）と、インデックスの使用が妨げられます。可能であれば、WHERE name LIKE 'smith%'を使用してください。 - インデックス付き列での計算を避ける:
WHERE句でインデックス付き列に関数を適用すると（WHERE YEAR(order_date) = 2024）、インデックスが使用できなくなります。代わりに、クエリの外部で範囲を計算します:WHERE order_date BETWEEN '2024-01-01' AND '2024-12-31'。 JOINの最適化:JOIN条件で使用される列がインデックス化されており、結合が最も効率的な順序で実行されていることを確認します（これはクエリオプティマイザによって自動的に行われることが多いですが、レビューする価値があります）。
C. サーバー構成のチェック（上級）
クエリが最適化されているのにまだ遅いという永続的な問題がある場合は、ハードウェアまたは構成の制限を検討してください。
innodb_buffer_pool_size: これはInnoDBにとって最も重要なメモリ設定です。データベースのワーキングセット（頻繁にアクセスされるテーブルとインデックス）を保持するのに十分な大きさであることを確認してください。一般的に、これは専用MySQLサーバーメモリの50〜80%である必要があります。- 接続プール: アプリケーションの接続プール設定が適切であることを確認し、接続の枯渇を防ぎます。これは、クエリのタイムアウトや体感的な遅延として現れる可能性があります。
まとめと次のステップ
スロークエリのトラブルシューティングは、測定、診断、検証を必要とする反復的なプロセスです。スロークエリログを体系的に有効にし、mysqldumpslowを使用してパフォーマンスのホットスポットを分析し、EXPLAINで実行計画を分解し、ターゲットを絞ったインデックス作成またはクエリの書き換えを実装することで、MySQL環境の健全性と応答性を大幅に改善できます。
解決のためのチェックリスト：
- ログ: スロークエリログはアクティブであり、関連するクエリをキャプチャしていますか？
- 特定: どのクエリが最もリソースを消費していますか（
mysqldumpslowを使用）？ - 診断: 実行計画（
EXPLAIN）はどうですか？type: ALLとUsing filesortを探してください。 - 解決: 必要なインデックスを実装するか、クエリの非効率的な部分を書き換えます。
- 検証: 最適化されたクエリを再度実行し、その実行時間を確認するか（または
EXPLAINを再実行）、ログを監視してクエリが二度と出現しないことを確認します。