MySQL 8 を EXPLAIN ANALYZE で最適化する：オプティマイザに「騙されない」ために – ITFROMZERO

Table of Contents

従来の EXPLAIN が単なる「空約束」に過ぎない理由

MySQL を使っているエンジニアなら、スロークエリが発生した際にまず EXPLAIN を実行するのが通例でしょう。クエリの先頭にこのキーワードを付けて MySQL がどのように実行するかを確認する作業はお馴染みです。しかし、従来の EXPLAIN はあくまで確率に基づいた「予測」に過ぎません。それはまるで、Google マップが「渋滞なし、所要時間10分」と表示しているのに、実際には直前に発生した事故で身動きが取れなくなるようなものです。

MySQL オプティマイザは統計情報（statistics）に基づいて、行数（rows）やコスト（cost）を推定します。問題は、このデータが頻繁に古くなったり、ハードディスク上の実際のデータ状況を正確に反映していなかったりすることです。筆者は以前、EXPLAIN では「50行のスキャン」と表示されているのに、実行に30秒もかかる不可解なケースに遭遇しました。原因は古いハードディスクからの実データ読み取り時の I/O ボトルネックでした。その時、MySQL が内部で実際に何をしているのかを正確に把握したいと切実に感じました。

そこで救世主となるのが、MySQL 8.0.18 から導入された EXPLAIN ANALYZE です。これは単なる推測ではなく、実際にクエリを実行し、その詳細なログを記録します。「天気予報を聞く」のと「実際に外に出てみる」のと同じくらい大きな違いがあるのです。

なぜ EXPLAIN ANALYZE が必須の「武器」なのか？

予測よりも実測値の方が信頼できる

最大の違いは、推定値（Estimated）のすぐ隣に実測値（Actual）が表示される点にあります。以下の情報を正確に把握できます：

Time to first row: 最初の1行を返すまでの時間（レスポンス重視の Web アプリでは極めて重要）。
Time to all rows: そのステップが完了するまでの総時間。
Actual rows: 実際にスキャンされた行数。
Loops: Join 実行時の実際のループ回数。

結果を見れば、どこが「ボトルネック」になっているかが一目瞭然です。インデックスが有効かどうかを議論する必要はありません。実際の数値が答えを教えてくれます。

注意点：実測には相応の負荷がかかる

EXPLAIN ANALYZE は実際に SQL を実行するため、INSERT、UPDATE、DELETE で使用するとデータに影響が及びます。また、1億件規模の大きなテーブルでは、通常のクエリと同じ時間がかかります。詳細なログを記録するため、むしろ通常より時間がかかることもあります。高負荷時の本番環境（Production DB）で直接試して、システムをフリーズさせるようなことは避けてください。同等のデータを持つステージング環境で分析するのが最善です。

クエリの「病気」を特定するためのデータの読み方

構文は非常にシンプルで、SELECT 文の前に EXPLAIN ANALYZE を追加するだけです：

EXPLAIN ANALYZE 
SELECT e.first_name, d.dept_name
FROM employees e
JOIN dept_emp de ON e.emp_no = de.emp_no
JOIN departments d ON de.dept_no = d.dept_no
WHERE e.last_name = 'Facello';

結果はツリー形式（Tree format）で返されます。実行順序を把握するために、内側から外側へ、下から上へと読むようにしましょう：

-> Inner hash join (de.dept_no = d.dept_no)  (cost=120.50 rows=150) (actual time=0.512..0.890 rows=180 loops=1)
    -> Table scan on d  (cost=1.10 rows=10) (actual time=0.011..0.025 rows=10 loops=1)
    -> Hash
        -> Inner hash join (e.emp_no = de.emp_no)  (cost=100.20 rows=140) (actual time=0.300..0.750 rows=150 loops=1)
            -> Index lookup on e using last_name (last_name='Facello')  (cost=20.10 rows=100) (actual time=0.100..0.250 rows=100 loops=1)
            -> Hash
                -> Table scan on de  (cost=50.50 rows=1000) (actual time=0.050..0.400 rows=1000 loops=1)

特に注目すべき3つの指標

actual time=0.100..0.250: 最初の1行に 0.100ms、そのステップの終了までに 0.250ms。この2つの数値の差が大きい場合、そのステップで膨大なデータを処理していることを意味します。
rows=180: この数値が estimated rows（推定行数）と大きく乖離している場合（例：推定10行に対して実際は1000行）、オプティマイザが古い統計情報に惑わされている可能性があります。
loops=1: Nested Loop Join において、この loops が数千に達している場合は、即座にインデックスを追加する必要がある箇所です。

実戦経験：ECサイトのシステムから学んだ教訓

最近、VIP 顧客の注文リストの読み込みに5秒以上かかる Web サイトを最適化しました。EXPLAIN では MySQL が customer_id のインデックスを使用し、50行だけをスキャンしていると表示されていました。理論上は非常に高速なはずですが、実際には遅いままでした。

EXPLAIN ANALYZE を実行したところ、真実が明らかになりました。Index lookup ステップに4.5秒もかかっていたのです。理由は、わずか50行であっても、その顧客のデータがディスクのパーティション全体に散らばっていたためでした。MySQL は実データを読み取るためにランダム I/O を繰り返していました。そこで、必要なカラムをすべて含む Covering Index を作成しました。その結果、MySQL はインデックスのみを読み取ればよくなり、ディスクを探索する必要がなくなりました。実行時間は5秒からわずか0.04秒に短縮されました。

教訓：オプティマイザの予測を信じるのではなく、実際に何が行われているかを確認しましょう。