Posted inデータベース

実行計画の読み方：PostgresとMySQLにおける「スロークエリ」の謎を解明する

Database tutorial - IT technology blog
Database tutorial - IT technology blog

クエリが遅い理由（そしてRAM増設では解決しない理由）

以前、わずか452行を取得するのに18.4秒もかかるクエリを午後中ずっと眺めていたことがあります。開発者としての最初の本能は「サーバーのRAMを増やす必要がある」というお決まりの反応でした。当然、メモリを2倍にしても何も変わりませんでした。データベースは依然として低速なままで、私はお手上げ状態でした。データベースを、SQLを入れればデータが出てくる魔法の箱のように扱いがちですが、パフォーマンスが低下したときは、エンジンがリクエストをどのように処理しているかを正確に把握する必要があります。

そこで登場するのが実行計画です。これはデータにとってのGPSナビゲーションのようなものだと考えてください。この指示が読めなければ、どのインデックスを追加すべきか、どの結合を書き直すべきかを推測するしかありません。長年PostgreSQLとMySQLのデバッグを行ってきた経験から、スロークエリの90%はEXPLAIN出力に含まれるいくつかの特定の指標を理解することで解決できることが分かりました。

実行計画とは一体何か？

コマンドを実行する前に、舞台裏で何が起こっているかを知っておくと役立ちます。クエリを送信すると、オプティマイザがテーブル、行数（統計情報）、利用可能なインデックスを評価します。そして、さまざまなパスの「コスト」を計算します。テーブル全体をスキャンすべきか？インデックスを使うべきか？テーブルAをテーブルBに先に結合すべきか、それともその逆か？

実行計画は最終的な決定事項です。これは操作のツリー構造であり、各ノードは「Index Scan（インデックススキャン）」や「Hash Join（ハッシュ結合）」などの特定のステップを表します。データはツリーの葉から根へと流れます。この流れを理解することが、効率の低下を見つける鍵となります。

PostgreSQL: EXPLAIN ANALYZEで実態を把握する

PostgreSQLにおいて、基本的なEXPLAINコマンドはオプティマイザが何が起こると考えているかを示します。私はこれ単体で使うことはほとんどありません。代わりにEXPLAIN ANALYZEを信頼しています。これは実際にクエリを実行し、現実世界でのタイミングを提供してくれます。

-- パフォーマンスデバッグに私がよく使うコマンド
EXPLAIN (ANALYZE, BUFFERS, VERBOSE) 
SELECT * FROM orders o 
JOIN users u ON o.user_id = u.id 
WHERE u.email = '[email protected]';

出力を確認する際は、以下の3つの指標を優先してください：

  • Actual Time（実時間）： ミリ秒単位の文字通りの測定値です。「Actual Time」が「Cost（コスト）」の見積もりよりも大幅に高い場合、テーブルの統計情報が古くなっている可能性があります。
  • Buffers（バッファ）： 共有バッファキャッシュから読み取られたデータ量とディスクから読み取られたデータ量を示します。高い「Read」数値は、ディスクへの負荷が高すぎることを示しています。
  • Seq Scan vs. Index Scan（シーケンシャルスキャン対インデックススキャン）： 100万行のテーブルに対する「Seq Scan」（シーケンシャルスキャン）は、データベースがディスク上のすべてのブロックを読み取っていることを意味します。これは通常、インデックス作成の最初のターゲットとなります。

MySQL: オプティマイザのロジックを可視化する

MySQL 8.0では、EXPLAIN ANALYZEによって大幅な改善が行われました。古い表形式よりもはるかに読みやすいツリー形式の出力が提供されます。実行中のどこに時間が費やされているかを正確に示してくれます。

-- モダンなMySQL 8.0でのアプローチ
EXPLAIN ANALYZE 
SELECT u.name, count(o.id) 
FROM users u 
LEFT JOIN orders o ON u.id = o.user_id 
GROUP BY u.id;

古いバージョンを使用している場合は、「type」列が非常に重要です。以下の特定の値を探してください：

  • system/const: 素晴らしい。クエリは瞬時に1行を見つけました。
  • eq_ref/ref: 良い。エンジンはインデックスを使用して効率的に行を見つけています。
  • ALL: フルテーブルスキャン。これは、図書館のすべてのページを調べて特定の引用を1つ探すのと同等の行為です。ほぼ間違いなくボトルネックになります。

一般的なボトルネックと警告サイン

実行計画を確認する際は、トラブルの兆候となるパターンを探してください。よくある問題は、2つの大きなテーブルに対する**Nested Loop（入れ子状のループ）**です。テーブルAとテーブルBにそれぞれ10万行あると想像してください。ネストされたループは100億回の比較を実行しようとするかもしれません。通常、ここでは**Hash Join（ハッシュ結合）**の方が高速です。しかし、統計情報が古いためにオプティマイザが片方のテーブルを小さいと誤認した場合、低速なループを選択してしまうことがあります。

もう一つの警告サインは、MySQLにおける**「Using temporary」**や**「Using filesort」**です。これは、データベースが`ORDER BY`や`GROUP BY`句のソートにインデックスを使用できない場合に発生します。データをメモリやディスクに展開して手動でソートせざるを得なくなります。大規模なデータセットの場合、これにより50ミリ秒のクエリが5秒に変わってしまうことがあります。

データインポートのワークフローに関するヒント

パフォーマンスの問題は、データ移行時によく発生します。テストやインポートのためにCSVファイルをJSONに変換する必要があるときは、toolcraft.app/ja/tools/data/csv-to-jsonを使用しています。これは完全にブラウザ内で動作するため、データのプライバシーが保たれます。本番環境にデプロイする前に、実行計画が期待通りにスケールするかを検証するためのモックデータを素早く準備するのに最適な方法です。

実践：スロークエリの修正

例えば、製品検索に2.5秒かかっているとします。EXPLAINを実行すると、次のような断片が表示されました：

-- PostgreSQL出力の断片
Filter: (price > 150.00)
Rows Removed by Filter: 842,000
Sequential Scan on products  (cost=0.00..28000.00 rows=1200 width=64)

「Rows Removed by Filter（フィルタによって削除された行）」が決定的証拠です。PostgreSQLは、必要な1,200行を見つけるためだけに842,000行を読み取る必要がありました。これは、(category_id, price)に対する複合インデックスの絶好の候補です。インデックスを追加すると、計画はIndex Scan（インデックススキャン）に切り替わり、実行時間は10ミリ秒未満にまで短縮されるでしょう。

パフォーマンス向上のための4つのルール

  1. 2回実行する： 1回目はディスクから読み取り、2回目はバッファキャッシュから読み取ります。常に「ウォーム（キャッシュが効いた状態）」のパフォーマンスを基準にしてください。
  2. 統計情報を更新する： PostgreSQLでは、大量の更新後にANALYZE;を実行します。MySQLではANALYZE TABLE;を使用します。オプティマイザは、持っているメタデータの鮮度以上の賢さは発揮できません。
  3. SELECT * の使用をやめる： 不要な列を取得すると、「Index Only Scan（インデックスオンリースキャン）」が妨げられる可能性があります。要求されたすべてのデータがインデックスに含まれていれば、データベースはメインテーブルのヒープに触れる必要さえありません。
  4. 型の不一致に注意する： 列がVARCHARなのに、WHERE id = 123（整数）のようにクエリを投げると、データベースはインデックスを無視して、すべての行に対して変換関数を実行してしまうことがあります。

結論

実行計画をマスターしたことで、私のコードの書き方は変わりました。クエリが速くなることを「期待」するのではなく、「証明」できるようになったのです。EXPLAINをローカル開発の標準的な習慣にしてください。大規模なテーブルに対して複数の結合やフィルタがあるクエリは、すべてクイックチェックに値します。用語を覚えるには少し練習が必要ですが、パフォーマンスの向上はすぐに現れます。ユーザー、そしてサーバーのCPUもその違いに気づくはずです。

Share:
Tags: