DevOpsツールとベストプラクティスの習得 - DevOpsナレッジハブ DevOpsツールとベストプラクティスの習得 - DevOpsナレッジハブ

MySQLスロークエリトラブルシューティング：ステップバイステップガイド

MySQLのスロークエリを見つけ、実行計画を読み、インデックスを修正し、変更が機能したことを証明するための実践的なワークフロー。

MySQLスロークエリトラブルシューティング：ステップバイステップガイド

MySQLのスロークエリトラブルシューティングは、1つの不快なルールから始まります。クエリテキストだけで推測してはいけません。見た目が悪いクエリでも、1日に1回しか実行されなければ無害かもしれません。シンプルに見えるクエリでも、1分間に何千回も実行され、多くの行をスキャンしたり、ロックの背後で待機したりして、データベースを台無しにする可能性があります。

有用なワークフローは、最も良い意味で退屈です。実際のスロークエリをキャプチャし、コストでグループ化し、実行計画を調査し、1つのことを変更し、再度測定します。これにより、ランダムなインデックスを追加したり、盲目的にグローバル設定を変更したり、アプリケーションが回避可能なクエリパターンを送信しているのにMySQLを非難したりすることを防げます。

私は通常、3つの質問から始めます。

  • どのクエリがユーザーに悪影響を与えているか？単に疑わしいだけではないか？
  • 時間は、行の読み取り、ソート、ロック待ち、アプリケーション待ちのどれに費やされているか？
  • EXPLAIN、タイミング、新しいスローログデータを使って修正を証明できるか？

スロークエリログから始める

MySQLのスロークエリログは、設定されたしきい値を超えたステートメントを記録します。MySQLマニュアルによると、ログはデフォルトで無効になっており、long_query_timeのデフォルトは10秒で、ステートメントは通常、少なくともその時間実行され、min_examined_row_limit行以上を検査してからログに記録されます。log_queries_not_using_indexesが有効になっている場合、MySQLは行ルックアップにインデックスを使用しないステートメントもログに記録できます。このオプションは診断中に役立ちますが、ビジーなシステムでは多くのノイズを生成する可能性があります。

実用的な初期設定は次のようになります。

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 1
log_output = FILE

多くの本番システムでは、1秒が妥当な最初のパスです。レイテンシに敏感なAPIの場合、一時的に0.5または0.2に下げることもあります。その場合は、計画とディスク容量の監視を行ってください。トラフィックの多いデータベースでは、しきい値を下げると、驚くほど多くのスローログデータが書き込まれる可能性があります。

MySQLセッションからアクティブな設定を確認できます。

SHOW VARIABLES LIKE 'slow_query_log';
SHOW VARIABLES LIKE 'slow_query_log_file';
SHOW VARIABLES LIKE 'long_query_time';
SHOW VARIABLES LIKE 'log_output';
SHOW VARIABLES LIKE 'min_examined_row_limit';
SHOW VARIABLES LIKE 'log_queries_not_using_indexes';

一時的な調査のために、設定ファイルを編集せずにログを有効にできます。

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;

SET GLOBALによる変更は、通常の設定プロセスを通じて永続化しない限り、再起動後も維持されないことに注意してください。MySQL 8では、SET PERSISTを使用するチームもありますが、私は次のオペレーターが確認できるように、意図した設定を構成管理にコミットすることを好みます。

log_queries_not_using_indexesをオンにする場合は、log_throttle_queries_not_using_indexesも設定して、1つのノイズの多いエンドポイントがログをあふれさせないようにすることを検討してください。MySQLは、インデックスなしのログが急速に増加する可能性があるため、特にこのスロットルをサポートしています。

個々のクエリを読む前にログをグループ化する

生のスローログは繰り返しが多いです。同じクエリが異なるIDで何百回も表示されることがあります。ファイルを上から下まで読むのは時間の無駄であり、まれで恐ろしいクエリが、一般的で高コストなクエリよりも重要に見えてしまいます。

まず、多くの環境でMySQLインストールに含まれているmysqldumpslowから始めます。

sudo mysqldumpslow -s t -t 10 /var/log/mysql/mysql-slow.log

これは、クエリ時間でソートされた上位10のパターンを要求します。正確なフラグはバージョンやプラットフォームによって異なるため、コマンドの動作が異なる場合はmysqldumpslow --helpを確認してください。便利なソートには、合計時間、平均時間、ロック時間、検査行数などがあります。

本番調査では、Percona Toolkitのpt-query-digestの方が、より豊富なグループ化とパーセンタイル形式の詳細を提供するため、多くの場合優れています。このツールは魔法ではなく、手動で計算する手間を省くだけです。重要なのは、影響度でランク付けすることです。夜間に1回8秒かかるクエリよりも、120ミリ秒かかるが毎秒600回実行されるクエリの方が緊急度が低い場合があります。

グループ化された出力を読むときは、パターンを探します。

  • 合計時間が高い：ユーザーから見えたり、リソースを大量に消費している可能性が高い。
  • カウントが高い：多くの場合、アプリケーションループまたはキャッシュミス。
  • 検査行数が多く、送信行数が少ない：通常、インデックスまたはフィルタリングの問題。
  • ロック時間が高い：トランザクション、書き込み競合、メタデータロック、またはDDLの問題の可能性。

Rows_examinedが高いことが常に悪いとは限りません。レポートクエリやバッチジョブは、意図的にスキャンすることがあります。問題は、スキャンがジョブに一致しているかどうか、そして適切なタイミングで発生しているかどうかです。

1つのクエリを安全に再現する

1つのクエリパターンを選択し、パラメータ付きの実際のサンプルを取得します。スローログがリテラルを正規化した場合は、アプリケーションログ、APMトレース、または生のスローログエントリで元のクエリを見つけます。

手動で実行する前に、影響範囲を確認します。レプリカでの遅いSELECTは通常安全です。本番環境での遅いUPDATEは、軽々しく再実行できるものではありません。書き込みクエリの場合は、最初に計画とトランザクションパターンを検査するか、実際のデータを持つステージングコピーでテストします。

各クエリの便利なメモは次のようになります。

エンドポイント: GET /customers/123/orders
クエリパターン: 顧客とステータスによる注文、新しい順
観測値: 平均1.8秒、検査行数420k、送信行数20
テーブルサイズ: 1200万行
期待結果サイズ: 注文の1ページ
疑い: customer_id、status、created_atの複合インデックスがない

このメモにより、作業がランダムなSQLスニペットではなく、実際のユーザーパスに結び付けられます。

EXPLAINを使用し、オペレーターのように読む

スロークエリに対してEXPLAINを実行します。

EXPLAIN
SELECT id, customer_id, status, created_at, total
FROM orders
WHERE customer_id = 123
  AND status = 'paid'
ORDER BY created_at DESC
LIMIT 20;

MySQL 8の場合、EXPLAIN ANALYZEはクエリを実行し、実際のタイミング情報を表示できます。高コストなクエリでは注意して使用してください。実際にステートメントを実行するためです。制御された環境での単純なSELECTクエリには非常に役立ちます。

最初に確認する列は、type、possible_keys、key、rows、filtered、Extraです。

typeはアクセスパターンを示します。const、eq_ref、ref、rangeは通常良い兆候です。indexはMySQLがインデックスをスキャンしていることを意味し、それでも作業が多すぎる可能性があります。ALLはフルテーブルスキャンを意味します。小さなテーブルではフルスキャンが自動的に間違っているわけではありませんが、数百万行のホットテーブルでは疑わしいです。

keyはMySQLが選択したインデックスを示します。possible_keysに有望なインデックスがリストされているのにkeyが異なる場合、オプティマイザーは他のインデックスの方がコストが低いと判断している可能性があります。これは、選択性の低さ、統計情報の古さ、またはフィルターとソートの両方に一致しないインデックスが原因で発生する可能性があります。

rowsは推定値であり、保証ではありません。推定値が大きく外れている場合は、適切なメンテナンスウィンドウ中にANALYZE TABLEを実行するか、データ分布に偏りがないか確認します。

Extraはしばしば問題を示します。Using filesortはMySQLが別個のソートステップを必要とすることを意味します。必ずしもディスクソートを意味するわけではありませんが、結果セットが大きい場合は確認する価値があります。Using temporaryは、グループ化、DISTINCTクエリ、または複雑なソートでよく表示されます。Using indexは、クエリがテーブル行を読み取らずにインデックスから満たされているため、良い場合があります。

クエリ全体の形状を考慮してインデックスを修正する

最も一般的なスロークエリの修正は、「WHERE句の列にインデックスを追加する」ことではありません。より良いルールは、クエリがフィルタリング、結合、ソート、および行を制限する方法に一致するインデックスを構築することです。

上記の注文クエリの場合、customer_idの単一列インデックスは役立つかもしれませんが、MySQLはその顧客の多くの行をソートする必要があるかもしれません。複合インデックスの方が多くの場合有用です。

CREATE INDEX idx_orders_customer_status_created
ON orders (customer_id, status, created_at);

クエリが新しい順にソートする場合、MySQLは多くの場合インデックスを逆順にスキャンできます。MySQL 8では、より大きなパターンに適合する場合に降順インデックスを定義することもできます。

CREATE INDEX idx_orders_customer_status_created_desc
ON orders (customer_id, status, created_at DESC);

列の順序は重要です。等価フィルターを最初に配置し、クエリに一致する場合は範囲またはソート列をその後に配置します。たとえば、WHERE customer_id = ? AND status = ? ORDER BY created_at DESC LIMIT 20の場合、customer_id, status, created_atは通常、created_at, customer_id, statusよりも有用です。

役立つと思われるすべてのインデックスを追加しないでください。インデックスは読み取りを高速化しますが、書き込みを遅くし、ストレージを消費します。テーブルが大量の挿入または更新を受ける場合、新しい複合インデックスには実際のコストがかかります。まず既存のインデックスを確認します。

SHOW INDEX FROM orders;

正しい答えは、2つの弱いインデックスを1つのより良い複合インデックスに置き換えることであり、3つすべてを維持することではありません。

インデックスの使用を妨げるクエリを書き換える

一部のスロークエリは、インデックス値を関数やMySQLが効率的に使用できないパターンの背後に隠しているために低速になります。

このバージョンは一般的で厄介です。

SELECT *
FROM orders
WHERE YEAR(created_at) = 2026;

created_atにインデックスが設定されている場合、YEAR()でラップすると通常の範囲ルックアップが妨げられる可能性があります。代わりに述語を範囲として記述します。

SELECT id, customer_id, status, created_at, total
FROM orders
WHERE created_at >= '2026-01-01'
  AND created_at <  '2027-01-01';

同じ考え方が先頭ワイルドカード検索にも当てはまります。

WHERE email LIKE '%@example.com'

通常のBツリーインデックスは、文字列の途中にジャンプできません。サフィックス検索が重要な場合は、生成列、別の正規化されたフィールド、またはそのユースケース向けに設計された検索システムが必要になる場合があります。

また、SELECT *にも注意してください。開発中は無害に見えますが、より小さな射影でカバリングインデックスを使用できる場合に、MySQLにテーブル行の読み取りを強制する可能性があります。また、不要なデータをネットワーク経由で送信します。

クエリプランが正常に見える場合でもロックを確認する

クエリは適切なプランを持っていても、待機が原因で低速になることがあります。スローログのLock_timeはその方向を示すことができますが、すべての種類の待機を説明するわけではありません。ユーザーがランダムな一時停止を報告した場合は、アクティブセッションを確認します。

SHOW PROCESSLIST;

MySQL 8では、サーバーの設定方法に応じて、パフォーマンススキーマとsysスキーマビューがより詳細な情報を提供できます。簡単な確認として、インデックスを変更する前に、長時間実行トランザクションとブロックされたステートメントを確認することがよくあります。

実際の例：UPDATE orders SET status = ? WHERE id = ?クエリは高速であるべきです。プライマリキールックアップでスローログに表示される場合、問題は、無関係な作業を行っている間にトランザクションが行をロックしたままにしていることかもしれません。修正は別のインデックスではありません。修正はトランザクションを短縮し、遅い外部呼び出しをトランザクションの外に移動することです。

メタデータロックも同様の罠を作り出す可能性があります。ALTER TABLEを実行しているマイグレーションが古いトランザクションを待機し、新しいクエリが保留中のDDLの背後に積み重なる可能性があります。スロークエリログは症状を示しますが、根本原因はデプロイメントの動作です。

クエリ作業の後にサーバー設定を調整する

設定は重要ですが、最初の答えとして使いすぎるのは簡単です。クエリが10行を返すために500万行をスキャンする場合、メモリを増やしても、悪いプランの痛みが軽減されるだけかもしれません。

InnoDB主体のシステムでは、innodb_buffer_pool_sizeが最初に確認する設定です。専用のMySQLサーバーでは、多くの場合メモリの大部分に設定されますが、適切な値はホスト上で他に何が実行されているか、データセットサイズ、ワークロードによって異なります。ブログ投稿から盲目的にパーセンテージをコピーしないでください。

また、データベースがディスクを待機しているかどうかも確認します。ワーキングセットがメモリに収まらない場合、またはストレージが飽和状態の場合、適切にインデックスが設定されたクエリでも停止する可能性があります。クエリレビューとホストメトリクス（CPU、ディスクレイテンシ、IOPS、メモリプレッシャー、接続数）を組み合わせて確認します。

コネクションプールは、スロークエリをさらに悪化させる可能性があります。1つのエンドポイントが多数のスローステートメントを発行すると、プールが満たされ、無関係なリクエストが接続を待機し、アプリ全体が壊れたように感じられます。その場合でも、クエリの修正が主な仕事ですが、プールの制限とタイムアウトによって、システムがどの程度優雅に障害に対処するかが決まります。

修正を証明する

インデックスを追加するかクエリを書き換えた後、再度EXPLAINを実行します。推定行数が減り、より適切なキーが選択され、高コストな追加ステップが少なくなっていることを確認します。次に、実際のパラメータを使用して実際のクエリをテストします。

1回の高速実行で止まらないでください。ウォームキャッシュは問題を隠す可能性があります。一般的なケース、大規模なケース、厄介なケースを試します。

  • 多くの注文を持つ顧客。
  • 一致する注文がない顧客。
  • ビジー期間にまたがる日付範囲。
  • ほとんどの行に一致するステータス値。

次に、デプロイ後にスローログを監視します。最良の結果は、「クエリがステージングでより良く見えた」ということではありません。最良の結果は、クエリパターンが上位の原因から消え、CPUまたはI/Oプレッシャーが低下し、ユーザーパスが高速化することです。

MySQLスロークエリトラブルシューティングは、主に規律ある証拠収集です。適切なしきい値でログを有効にし、高コストなパターンをグループ化し、計画を調査し、クエリ形状を修正し、新しいデータで検証します。この習慣は、過小修正と過剰修正の両方を防ぎます。これは、データベースがすでにプレッシャー下にある場合にまさに必要なことです。