遅いMySQLクエリの原因はこれ！実行計画の見方・活用法

---

遅いMySQLクエリの原因はこれ！実行計画の見方・活用法

データベースを使ったアプリケーションを開発・運用していると、避けて通れない課題の一つが「遅いクエリ」です。ユーザー体験の低下、サーバーリソースの圧迫、運用コストの増加など、遅いクエリが引き起こす問題は多岐にわたります。しかし、どこに原因があるのか、どうすれば改善できるのか、迷ってしまうことも少なくありません。

この記事では、MySQLでクエリが遅くなる様々な原因を解説し、その中でも特に強力な原因特定ツールである「実行計画（EXPLAIN）」に焦点を当てます。EXPLAINの出力結果をどのように読み解き、パフォーマンス改善にどう繋げるのか、具体的なステップと豊富な例を交えて、徹底的に解説していきます。

この記事を読むことで得られること:

• なぜMySQLクエリは遅くなるのか、その主要な原因を理解できる。
• 実行計画（EXPLAIN）の基本的な使い方と、その重要性を理解できる。
• EXPLAIN出力の各カラム（id, select_type, table, type, key, rows, Extraなど）が何を意味するのか、詳細に理解できる。
• 特に重要な type と Extra の値を読み解き、クエリの効率性を評価できるようになる。
• 実行計画からパフォーマンスの問題点（フルスキャン、一時テーブルの使用、ファイルソートなど）を見つけ出せるようになる。
• 具体的な遅いクエリの実行計画の例を見て、その改善策を学ぶことができる。
• EXPLAIN以外の遅延原因特定ツール（スロークエリログ、Performance Schemaなど）についても知ることができる。
• 実行計画分析に基づいた具体的なパフォーマンスチューニング手法（インデックス最適化、クエリ書き換えなど）を習得できる。
• データベースパフォーマンス改善のための継続的な取り組み方を知ることができる。

序章：遅いクエリはなぜ発生する？その影響とEXPLAINの重要性

アプリケーションのレスポンスが遅い、ウェブサイトの表示に時間がかかる、バッチ処理がなかなか終わらない――これらの問題の多くは、データベースへの問い合わせ、すなわちSQLクエリの遅延が根本原因となっている場合があります。

なぜクエリは遅くなるのでしょうか？ それは、データ量が増加したり、アプリケーションの利用者が増えたり、あるいは単にクエリの書き方が非効率だったり、データベース設計に問題があったりするなど、様々な要因が複雑に絡み合うためです。

遅いクエリが引き起こす影響は深刻です。

• ユーザー体験の低下: ユーザーは待つことを嫌います。ページの読み込みが遅い、操作に時間がかかるといった状況は、ユーザーの離脱に直結します。
• リソースコストの増加: 遅いクエリはCPU、メモリ、ディスクI/Oといったデータベースサーバーのリソースを長時間占有します。これにより、他のクエリの実行も妨げられ、システム全体の処理能力が低下します。結果として、同じ処理能力を維持するためにより高性能なサーバーが必要となり、コストが増大します。
• 開発・運用の非効率化: 遅いクエリの調査や改善には、開発者や運用エンジニアの貴重な時間が費やされます。また、デッドロックなどの深刻な問題を引き起こす可能性もあり、障害対応の負荷も増大します。
• ビジネス機会の損失: Eコマースサイトでの購入離脱、ゲームでのプレイ体験悪化、レポート作成の遅延など、ビジネス上の直接的な損失に繋がることも少なくありません。

これらの問題を解決するためには、まず「何が原因で遅くなっているのか」を正確に特定する必要があります。原因特定のために、MySQLが提供する最も基本的で強力なツールが実行計画（EXPLAIN）です。

EXPLAINは、MySQLがどのようにSQLクエリを実行するかを「計画」した結果を表示します。この計画には、どのテーブルに、どの順番でアクセスし、どのインデックスを使用し、どのようにデータを結合・フィルタリング・ソートするかといった詳細な情報が含まれています。例えるなら、EXPLAINは「クエリの交通整理計画書」のようなものです。この計画書を読み解くことで、MySQLが「なぜ」そのようにクエリを実行しようとしているのか、そしてそこに「非効率な点はないか」を知ることができるのです。

次の章では、クエリが遅くなる一般的な原因をさらに詳しく見ていきましょう。

第1章：MySQLクエリ遅延の様々な原因

実行計画の話に入る前に、そもそもクエリが遅くなる原因にはどのようなものがあるのか、全体像を把握しておきましょう。これらの原因の多くは、後述するEXPLAINの結果に現れてきます。

1. インデックスがない、あるいは適切でない:

   • 最も一般的で、かつ最も影響が大きい原因です。巨大な電話帳で、人名を五十音順のインデックスなしに探すようなものです。WHERE句やJOIN条件、ORDER BY句などで指定したカラムに適切なインデックスが存在しない場合、MySQLはテーブル全体を読み込む「フルスキャン」を行う可能性が高くなります。
   • インデックスがあっても、クエリの内容（例: WHERE句での関数使用、LIKE ‘%…’、データ型の不一致など）によってはインデックスが利用されないことがあります。
   • 複合インデックスの場合、インデックスの定義順序（最左一致の原則）を考慮しないと、インデックスの一部しか使えない、あるいは全く使えないということもあります。

2. JOIN条件の非効率性:

   • 複数のテーブルを結合（JOIN）する際に、JOIN条件に使われているカラムにインデックスがない、あるいはデータ型が異なると、MySQLは非効率なJOIN方法（Nested-Loop Joinで片方をフルスキャンするなど）を選択することがあります。
   • JOINするテーブルの順番もパフォーマンスに影響します。

3. WHERE句の非効率な条件:

   • WHERE句でカラムに対して関数を使用する（例: WHERE DATE(created_at) = '...'）。
   • LIKE '%...' のように前方一致でない部分一致検索を行う。
   • カラムのデータ型と検索値のデータ型が異なる（暗黙の型変換が発生し、インデックスが使えないことがある）。
   • NOT IN や <> (不等号) 条件が多用されている場合（インデックスが使えない場合がある）。
   • OR条件（場合によってはインデックスが使えない、あるいは複数インデックスを組み合わせるが非効率な場合がある）。

4. 大量のデータの取得:

   • SELECT * で必要のないカラムまで取得する。特にLOB（Large Object）やTEXT/BLOB型を含む場合、ネットワークやメモリの負荷が増大します。
   • LIMIT句を使わずに大量の行を取得する。
   • カーディナリティが低いカラムでWHERE句を指定し、多くの行がヒットする場合（インデックスがあっても多くの行を読み込む必要がある）。

5. ソート（ORDER BY）や集計（GROUP BY）の非効率性:

   • ORDER BY句やGROUP BY句に使われているカラムに適切なインデックスがない場合、MySQLは一時的な領域（メモリまたはディスク）を使ってデータのソートや集計を行います（「ファイルソート」「一時テーブル」）。これはデータ量が多い場合に大きなオーバーヘッドとなります。
   • SELECT、WHERE、ORDER BY、GROUP BYなどで使用されるカラムをカバーする複合インデックスがない場合。

6. サブクエリの非効率性:

   • 古いバージョンのMySQLでは、サブクエリの最適化があまり強力ではありませんでした。特にDEPENDENT SUBQUERY（外部クエリの各行に対してサブクエリが実行される）は遅くなりがちです。MySQL 5.6以降、サブクエリの最適化は改善されていますが、依然として非効率なパターンは存在します。

7. データベース設計の問題:

   • 非正規化の度合いが不適切（過度に正規化されすぎてJOINが増える、あるいは非正規化しすぎて冗長性が高まり更新が遅くなる）。
   • データ型の選択ミス（必要以上に大きな型、FLOAT/DOUBLEでの厳密比較など）。
   • 巨大なテーブル（数億行以上のテーブルは、適切にパーティショニングしないと管理・検索が困難になる）。

8. サーバーリソース不足:

   • CPU使用率が継続的に高い。
   • メモリ不足によるスワッピング発生、バッファプールの不足。
   • ディスクI/Oのボトルネック（ディスクの読み書きが追いつかない）。
   • ネットワーク帯域の不足。

9. ロック競合:

   • 複数のトランザクションが同じデータやリソースにアクセスしようとした際に、ロックの待ち時間が発生する。UPDATE/DELETE文、トランザクション分離レベルの設定、長時間実行されるクエリなどが原因となります。

10. MySQLの設定:

    • innodb_buffer_pool_size が小さすぎる（データやインデックスがメモリにキャッシュされず、頻繁にディスクアクセスが発生する）。
    • sort_buffer_size, tmp_table_size など、ソートや一時テーブル関連の設定が小さい。
    • クエリキャッシュ（MySQL 5.7まで）の設定が不適切。
    • その他の各種バッファサイズやタイムアウト設定。

これらの原因のうち、インデックス、JOIN、WHERE句、データ量、ソート/集計、サブクエリなど、クエリ自体の実行方法に関する問題を特定するのに最も有効なツールがEXPLAINです。リソース不足やロック競合は、EXPLAINだけでは直接特定できませんが、EXPLAINでクエリ自体に問題がないと判断できた場合に、次に疑うべき点として重要です。

第2章：性能分析の主役「実行計画（EXPLAIN）」の基本

それでは、EXPLAINコマンドの使い方と基本的な概念を見ていきましょう。

2.1 EXPLAINコマンドの実行方法（基本形、FORMAT=JSON）

EXPLAINコマンドは非常にシンプルです。分析したいSELECT、INSERT、REPLACE、UPDATE、DELETE文の前にEXPLAINというキーワードを付けるだけです。（通常、最も使用頻度が高く、遅延の原因となりやすいのはSELECT文です。）

基本的な使い方:

sql
EXPLAIN SELECT column1, column2 FROM table1 JOIN table2 ON table1.id = table2.table1_id WHERE table1.column3 > 100 ORDER BY table2.column4;

このコマンドを実行すると、クエリの実行計画がテーブル形式で表示されます。

EXPLAIN FORMAT=JSON:

MySQL 5.6以降では、より詳細な情報をJSON形式で出力する EXPLAIN FORMAT=JSON が利用できます。

sql
EXPLAIN FORMAT=JSON SELECT column1, column2 FROM table1 JOIN table2 ON table1.id = table2.table1_id WHERE table1.column3 > 100 ORDER BY table2.column4;

JSON形式の出力は、ネストされた構造になっており、標準のテーブル形式よりも多くの情報（例: コスト推定値、各JOINステップで検査される行数など）が含まれています。人間の目で直接読み解くのは少し大変ですが、ツールと組み合わせることで非常に強力な情報源となります。多くのグラフィカルなMySQLクライアント（MySQL Workbenchなど）は、このJSON出力を整形して分かりやすく表示する機能を持っています。

2.2 EXPLAIN ANALYZE (MySQL 8.0+) の紹介

MySQL 8.0.18以降では、EXPLAIN ANALYZE というさらに強力なオプションが追加されました。

sql
EXPLAIN ANALYZE SELECT column1, column2 FROM table1 JOIN table2 ON table1.id = table2.table1_id WHERE table1.column3 > 100 ORDER BY table2.column4;

標準の EXPLAIN は、あくまでMySQLオプティマイザが「予測した」実行計画と推定値（rowsなど）を表示します。一方、EXPLAIN ANALYZE は、実際にクエリを実行し、各ノード（テーブルアクセス、JOIN、ソートなど）でどれくらいの時間がかかり、どれくらいの行が処理されたかを計測して表示します。

EXPLAIN ANALYZE の出力は、標準のEXPLAINの各行に実際の統計情報が付加されたような形式になります。例えば、-> Table scan on table1 (actual time=0.123..4.567 rows=1000 loops=1) のように、実際の実行時間（最初の行を返すまでの時間..最後の行を返すまでの時間）、実際に処理された行数、ループ回数などが表示されます。

これにより、オプティマイザの予測が現実とどれだけ乖離しているか、クエリの実行時間の中でどの部分が最も時間を消費しているのかを正確に知ることができます。

注意点: EXPLAIN ANALYZE は実際にクエリを実行するため、データに対する影響（INSERT/UPDATE/DELETEの場合）や、本番環境でのリソース消費には十分注意が必要です。通常は、開発環境やステージング環境、あるいは本番環境でも負荷の低い時間帯や、読み取りクエリに対して限定的に使用することをお勧めします。

この記事の後半では、主に標準の EXPLAIN 出力（テーブル形式）の見方を中心に解説しますが、EXPLAIN ANALYZE が提供する「実際の値」の重要性も覚えておいてください。

第3章：EXPLAIN出力カラム徹底解説

EXPLAINを実行すると、通常以下のようなカラムを持つテーブルが出力されます。（MySQLのバージョンやクエリによって、表示されるカラムは若干異なる場合があります。）

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	users	NULL	ALL	PRIMARY,idx_email	NULL	NULL	NULL	1000000	100.00	Using where
1	SIMPLE	orders	NULL	ref	idx_user_id	idx_user_id	4	dbname.users.id	10	100.00	Using index

この各カラムがクエリの実行計画の重要なピースとなります。一つずつ詳細に見ていきましょう。

3.1 id – クエリの実行順序

id カラムは、クエリ内の各SELECT文（またはUNION、派生テーブルなど）に割り当てられるユニークな識別子です。

• 同じidを持つ行は、同じ実行ステージに属し、MySQLはそれらをまとめて処理します（通常はJOIN）。
• idが異なる場合は、基本的にはidの大きい順に実行されます。ただし、派生テーブル（DERIVED）やサブクエリ（SUBQUERY）など、一部のselect_typeでは、その結果が外部クエリに使われるため、必ずしもidの大きい順とは限りません。JOINに関しては、通常はidが同じ行が上から順に処理されます。
• ネストされたクエリ構造（サブクエリやUNION）では、異なるidが割り当てられます。

3.2 select_type – SELECTの種類と複雑さ

select_type カラムは、その行がクエリのどの部分に属しているか、そしてそのSELECTがどのような種類であるかを示します。クエリの構造（サブクエリ、UNIONなど）を理解するのに役立ちます。

代表的な値を以下に示します。

• SIMPLE: 最も基本的なSELECT。UNIONやサブクエリを含まない、単純なクエリ全体、またはUNIONやサブクエリを含んでいても、それらが最適化されてSIMPLEになる場合。
• PRIMARY: UNIONを含むクエリ全体の最初のSELECT、またはUNION ALLを含むクエリ全体の最初のSELECT。
• UNION: UNIONの2番目以降のSELECT。
• DEPENDENT UNION: 外部クエリに依存するUNIONの2番目以降のSELECT。これは通常効率が悪いサインです。
• UNION RESULT: UNIONの結果を格納する一時テーブル。
• SUBQUERY: FROM句以外の場所にある単純なサブクエリ。
• DEPENDENT SUBQUERY: FROM句以外の場所にある、外部クエリに依存するサブクエリ。外部クエリの各行に対してサブクエリが実行されるため、データ量が多いと非常に遅くなります。これも非効率なサインです。
• DERIVED: FROM句にあるサブクエリの結果。MySQLはこの結果を一時テーブルに格納してから外部クエリを評価します。派生テーブルとも呼ばれます。
• MATERIALIZED: FROM句にあるサブクエリがマテリアライズ（一時テーブルとして具体化）されたもの。（MySQL 5.6以降のサブクエリ最適化）
• UNCACHEABLE SUBQUERY: 外部クエリに依存せず、かつWHERE句などにあるサブクエリだが、結果をキャッシュできないもの（例: NOW()のような非決定的な関数を使用している場合）。

特に DEPENDENT SUBQUERY はパフォーマンス問題の温床となりやすいため、この select_type が出現した場合は要注意です。可能であればJOINへの書き換えなどを検討しましょう。

3.3 table – どのテーブルを見ているか

table カラムは、EXPLAINのその行がどのテーブル（または一時テーブル、派生テーブルなど）に関するものかを示します。

• テーブル名 (users, ordersなど)。
• 派生テーブル (<derived2> のように、id=2 の派生テーブルの結果を参照している)。
• UNIONの結果 (<union1,4> のように、id=1 と id=4 のUNIONの結果を参照している)。
• 一時テーブル (<tmp> のように、一時テーブルを参照している)。

3.4 partitions – 使用されるパーティション (パーティショニングを使用している場合)

テーブルがパーティショニングされている場合、このカラムはクエリでアクセスされるパーティションを示します。NULL でない場合は、パーティションプルーニング（不要なパーティションをスキャン対象から外す最適化）が有効になっていることを意味します。NULL の場合は、全てのパーティションをスキャンしている可能性があります。

3.5 type – テーブルへのアクセス方法の「質」

type カラムは、MySQLがテーブルからどのように行を取得するか（アクセス方法）を示します。これはEXPLAIN出力の中で最も重要と言っても過言ではないカラムです。テーブルへのアクセス効率を端的に示します。

type の値は、一般的に以下の順で効率が良いとされています。（左に行くほど良い）

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

それぞれの値について詳しく見ていきましょう。

• system: テーブルに1行しかない（システムテーブルのような）特殊なケース。const の一種と考えられます。
• const: クエリがテーブルの最大1行に一致することがわかっている場合に使用されます。PRIMARY KEYやUNIQUEインデックスを使った = 検索で、検索対象の行が1行に特定できる場合に発生します。非常に高速です。例: SELECT * FROM users WHERE id = 1; (idがPKの場合)
• eq_ref: JOINにおいて、前のテーブルからの1行に対して、現在のテーブルから1行がPRIMARY KEYまたはUNIQUE NOT NULLインデックスを使って読み込まれる場合に発生します。JOINの中で最も効率の良いタイプです。例: SELECT u.name, o.order_id FROM users u JOIN orders o ON u.id = o.user_id; (u.idがPK、o.user_idにUNIQUE NOT NULLインデックスがある場合)
• ref: JOINにおいて、またはWHERE句で、インデックスのプレフィックスまたはインデックス全体を使って値が一致する行を検索する場合に発生します。eq_ref と似ていますが、一致する行が複数になる可能性があります。PRIMARY KEYやUNIQUEインデックスでないインデックス、あるいはUNIQUEインデックスでもNOT NULLでないカラムでの = 検索でよく見られます。例: SELECT * FROM orders WHERE user_id = 10; (user_idに非UNIQUEインデックスがある場合)
• fulltext: FULLTEXTインデックスを使用した全文検索の場合。
• ref_or_null: ref と似ていますが、加えてNULL値を含む行も検索する場合に使用されます。例: SELECT * FROM users WHERE parent_id = 10 OR parent_id IS NULL; (parent_idにインデックスがある場合)
• index_merge: 複数のインデックスを組み合わせて行を検索する場合。AND条件やOR条件などで発生することがあります。例えば、WHERE columnA = 10 OR columnB = 20 で columnA と columnB にそれぞれインデックスがある場合、両方のインデックスを使って結果を取得し、マージすることがあります。常に ref や range より効率が良いとは限りません。
• unique_subquery: IN 句のサブクエリにおいて、サブクエリがPRIMARY KEYまたはUNIQUEインデックスを使用して効率的に検索できる場合。WHERE primary_key_col IN (SELECT unique_col FROM another_table WHERE ...)
• index_subquery: IN 句のサブクエリにおいて、サブクエリが非UNIQUEインデックスを使用して検索できる場合。
• range: インデックスを使って、ある範囲の行を検索する場合。<, >, <=, >=, BETWEEN, IN, IS NULL, <=> (NULL安全比較) などで発生します。ref よりは効率が落ちますが、index や ALL よりははるかに効率的です。インデックスが使える範囲検索です。例: SELECT * FROM products WHERE price BETWEEN 100 AND 500; (priceにインデックスがある場合)
• index: インデックス全体をスキャンする場合（インデックスフルスキャン）。これはインデックスにクエリに必要な全ての情報（SELECTリスト、WHERE句、ORDER BY句など）が含まれている場合（カバーリングインデックス）や、WHERE句がないがORDER BY句のためにインデックス順序が必要な場合などに発生します。テーブル全体をスキャンする ALL よりはディスクI/Oが少ないですが、インデックスの行数が多いとやはり遅くなります。
• ALL: テーブル全体をスキャンする場合（テーブルフルスキャン）。最も効率の悪いアクセス方法です。 テーブルの全行を読み込んでから、WHERE句でフィルタリングやJOINを行います。巨大なテーブルで type=ALL が発生している場合、そのクエリはほぼ確実に遅いです。この type を見つけたら、まずインデックスが適切に使用されているかを確認すべきです。

パフォーマンスチューニングの観点から:

• 理想的なのは system, const, eq_ref, ref あたりです。これらのタイプは、インデックスを使って効率的に特定の行にアクセスできています。
• range は、範囲検索が必要な場合に許容できるレベルです。
• index は、データ量やインデックスの幅（インデックスに含まれるカラム数）によりますが、可能な限り避けたいタイプの一つです。
• ALL は、ほとんどの場合で避けるべきです。巨大なテーブルで ALL が出ている場合は、深刻なパフォーマンス問題を示唆しています。

3.6 possible_keys – 使用できる可能性のあるインデックス

MySQLがこのテーブルの検索に使用できると判断したインデックスのリストです。NULLの場合は、使用できるインデックスがないことを意味します。

これはあくまで候補であり、MySQLオプティマイザが最終的に使用するインデックス（key カラム）は、このリストの中から、またはこのリストに含まれないものから選ばれることもあります（オプティマイザの判断による）。

3.7 key – 実際に選択されたインデックス

MySQLオプティマイザが、このテーブルへのアクセスに実際に使用することを選択したインデックスを示します。NULL の場合は、インデックスが使用されなかったことを意味します（通常 type が ALL になります）。

possible_keys に表示されているにも関わらず、key が NULL になっている場合、MySQLは「インデックスを使うよりもフルスキャンの方が速い」と判断したことになります。これは、テーブルが非常に小さい場合、WHERE句で多くの行がヒットする場合、あるいはオプティマイザの統計情報が古いために誤った判断をした場合などが考えられます。

key に表示されているインデックス名を確認し、それが意図したインデックスであるか、そしてそのインデックスが適切に利用されているか（key_len や type と合わせて確認）をチェックします。

3.8 key_len – 使用されたインデックスの長さ

実際に使用されたインデックスのバイト単位の長さを示します。複合インデックスを使用している場合、この値はインデックスのどの部分までが利用されたかを示します。

例えば、INDEX (colA, colB, colC) という複合インデックスがあり、key_len が colA と colB の長さを合計した値になっている場合、そのクエリではインデックスの colA と colB の部分までが使われていることを意味します。これは、WHERE句が colA と colB を利用している場合や、colA のみを利用している場合などに発生します（最左一致の原則）。

key_len を確認することで、複合インデックスが期待通りに利用されているか、あるいはWHERE句やJOIN句がインデックスのどの範囲まで有効に使えているかを確認できます。例えば、WHERE colA = 1 AND colB = 2 というクエリで、インデックス (colA, colB, colC) に対して key_len が colA の長さしか含まない場合、colB の部分はインデックスで効率的にフィルタリングできていないことを示唆します。

データ型によって長さが異なります。CHAR/VARCHAR、数値型、NULL許容かどうかも長さに影響します。正確な計算は少し複雑ですが、おおよその長さからどのカラムまで使われているかを推測できます。

3.9 ref – インデックスの参照元

key カラムで使用されたインデックスと結合される（または比較される）定数、または前のテーブルのカラムを示します。

• const: 定数と比較されている場合。例: WHERE id = 1 (idがPK) のようなconstアクセス。
• dbname.table_name.column_name: JOINにおいて、前のテーブル (dbname.table_name) の column_name と比較されている場合。例: JOIN orders o ON u.id = o.user_id のJOINにおけるo.user_idへのアクセスで、refがdbname.u.idとなる場合。
• NULL: 比較対象がない場合（範囲検索など）。

3.10 rows – クエリがテーブルから読み込むと予測される行数

MySQLオプティマイザが、このテーブルから読み込む必要があると予測した行数です。これはWHERE句の条件を満たす行数ではなく、条件を評価するためにアクセスする必要がある行数の推定値です。

このカラムは非常に重要です。 この数値が大きいほど、そのテーブルへのアクセスは非効率である可能性が高いです。

type が ALL の場合は、テーブルの全行数が表示されるか、それに近い大きな値になります。type が index の場合も、インデックスに含まれる全行数が表示されるか、それに近い値になります。一方、type が const, eq_ref, ref, range の場合は、通常は小さい値になります。

ただし、この値はあくまでオプティマイザの推定値であり、統計情報に基づいて計算されるため、実際のクエリ実行で読み込まれる行数と乖離することがあります。特に統計情報が古い場合や、複雑なWHERE句の場合に予測が外れやすいです。EXPLAIN ANALYZE を使用すると、この推定値と実際の値を比較できます。

3.11 filtered – WHERE句によってフィルタリングされる行の割合 (MySQL 5.1+)

MySQL 5.1以降で追加されたカラムです。rows カラムで読み込まれると予測された行数のうち、WHERE句によってフィルタリングされ、次のJOINや処理に進む行の割合（パーセント）を示します。

例えば、rows が1000で filtered が10.00の場合、1000行読み込むが、WHERE句で条件を満たすのはそのうちの10%、つまり100行だと予測していることになります。

• filtered の値が高い（100.00に近い）ほど、読み込んだ行の多くが条件を満たしている、またはWHERE句によるフィルタリングが効率的に行われていることを意味します。
• filtered の値が低い場合、多くの行を読み込んでいるにも関わらず、そのほとんどがWHERE句で捨てられている（つまり、WHERE句の条件がインデックスで効率的に絞り込めていない）可能性を示唆します。これは type が ALL や index で filtered が低い場合に特に問題となります。

rows と filtered を組み合わせることで、どのステップで大量の不要な行を読み込み、そして捨てているのかを把握できます。

3.12 Extra – 追加情報

Extra カラムは、MySQLがクエリを実行する上で、標準のアクセス方法（type）以外に行う特別な処理に関する追加情報を提供します。このカラムも type と同様に非常に重要です。 ここに表示される情報によって、パフォーマンス問題の根源が明らかになることがよくあります。

代表的な値を以下に示します。これらの多くは、パフォーマンスのボトルネックを示唆するサインです。

• Using index: クエリに必要な全ての情報（SELECTリスト、WHERE句、JOIN句など）がインデックスのみから取得できる場合（カバーリングインデックス）。テーブル本体へのアクセスが不要になるため、非常に高速です。パフォーマンスが良いクエリの理想的なサインの一つです。
• Using where: WHERE句を使用して、ストレージエンジン（InnoDBなど）から返された行をさらにフィルタリングしている場合。type が ALL や index の場合に Using where が付いていると、読み込んだ全行またはインデックスの全行に対してWHERE句を適用していることを意味し、非効率な可能性があります。インデックスでフィルタリング可能な場合は、type が ref や range などになり、Using where は付かないか、あるいはごく少数の行に対してのみ適用されます。
• Using temporary: クエリの処理中に一時テーブルを作成している場合。これは主に GROUP BY や DISTINCT、UNION、特定のサブクエリなどで、インデックスが効率的に使えない場合に発生します。一時テーブルはメモリ（tmp_table_size, max_heap_table_sizeの設定による）またはディスク上に作成されます。ディスクに作成されると非常に遅くなります。
• Using filesort: ORDER BY句で指定された順序でインデックスが存在しないか、またはインデックスが利用できない場合に、MySQLが自分でメモリやディスクを使って結果をソートしている場合。これもデータ量が多い場合に大きなオーバーヘッドとなります。ソートバッファ（sort_buffer_size）の設定や、一時ファイルの使用に影響されます。
• Using join buffer (Block Nested Loop): JOINの際に、Join Bufferを使用して結合を効率化しようとしている場合。これは通常、JOINされるテーブルのどちらか一方に適切なインデックスがなく、type が ALL や index になっている場合に発生します。MySQLは一方のテーブルの行をメモリ上のバッファに読み込み、もう一方のテーブルをスキャンしながらバッファ内の行とマッチさせます。効率的とは言えません。
• Using sort_union(…), Using union(…), Using intersect(…): index_merge の type と共に表示され、どのように複数のインデックスを組み合わせているかを示します。sort_union や union はOR条件、intersect はAND条件などで発生します。
• Distinct: DISTINCTキーワードが使われている場合で、重複排除のために余分な処理が行われていることを示唆します。
• Not exists: WHERE句で NOT EXISTS サブクエリが最適化された場合に表示されます。効率的な場合が多いです。
• Impossible WHERE: WHERE句の条件が常に偽であると判断され、結果が0行になる場合。
• Select tables optimized away: クエリがMIN()やMAX()、COUNT(*)などの集計関数のみで構成されており、テーブルアクセスなしに結果が決定できる場合。非常に高速です。例: SELECT COUNT(*) FROM users; (InnoDBの場合はインデックス情報から高速に取得できる)
• Using index condition: MySQL 5.6で導入された Index Condition Pushdown (ICP) 最適化が使用されている場合。インデックス自体で条件の一部を評価することで、ストレージエンジンからMySQLサーバーレイヤーに転送される行数を減らします。type が range や ref など、インデックスが使われている場合に表示されることがあります。効率が良いサインです。
• Using where; Using index: これが同時に表示される場合があります。これは、インデックスを使って行を絞り込めたものの、WHERE句の条件の一部がインデックスに含まれていないか、あるいはインデックスの後半部分で評価されるために、さらにテーブル本体にアクセスして最終的なフィルタリングを行っている場合です。Using index だけの場合（カバーリングインデックス）よりは効率が落ちます。

パフォーマンスチューニングの観点から（特に注意すべき Extra の値）:

• Using filesort
• Using temporary
• Using join buffer
• type=ALL と組み合わされた Using where

これらの Extra の値が出ている場合は、そのクエリが非効率な処理（ソート、一時テーブル、非インデックスJOIN、フルスキャン後のフィルタリング）を行っている可能性が高く、改善の最優先ターゲットとなります。一方、Using index や Using index condition は良いサインです。

第4章：実行計画から問題点を見つける具体的なステップと例

EXPLAIN出力の各カラムの意味がわかったところで、実際に遅いクエリのEXPLAINを見て、問題点を見つけ、改善策を検討する具体的なステップに進みましょう。

ステップ1：遅いクエリを特定する

まず、どのクエリが遅いのかを特定します。
* アプリケーションのログやパフォーマンス監視ツール
* MySQLのスロークエリログ
* SHOW PROCESSLIST や Performance Schema によるリアルタイム監視

これらの方法で、実行時間が長い、ロック待ちが多い、リソースを大量に消費しているクエリを見つけ出します。

ステップ2：特定したクエリに対してEXPLAINを実行する

問題のあるクエリが見つかったら、そのクエリの前に EXPLAIN を付けて実行します。必要に応じて EXPLAIN FORMAT=JSON や EXPLAIN ANALYZE も使用します。

ステップ3：EXPLAIN出力を読み解き、BADサインを探す

出力されたEXPLAINの結果を、第3章で解説した各カラムの意味に基づき、特に以下のBADサインに注目して確認します。

• type が ALL や index （特に ALL）になっているテーブルはないか？
• rows が異常に大きな値になっているテーブルはないか？
• Extra に Using filesort や Using temporary はないか？
• Extra に Using join buffer はないか？
• type が ALL や index で filtered が低いテーブルはないか？
• select_type に DEPENDENT SUBQUERY はないか？
• JOINにおいて type が ALL や index のテーブルがないか？

ステップ4：BADサインの原因を特定し、改善策を検討する

見つかったBADサインに基づいて、クエリやインデックス、データベース設計のどこに問題があるのかを特定し、適切な改善策を検討します。以下に、代表的なBADパターンとその改善策を具体例と共に示します。

---

BADパターン別：EXPLAIN出力例と改善策

ここでは、仮想的なusersテーブル（id, name, email, created_at, last_login_at, status, city, birth_dateなど）とordersテーブル（order_id, user_id, order_date, amount, statusなど）を例に解説します。

例1：フルスキャン(type=ALL)とインデックス不在

クエリ:
最近ログインしたアクティブなユーザーを、登録日時の降順で取得する。

sql
SELECT id, name, email
FROM users
WHERE last_login_at > '2023-01-01' AND status = 'active'
ORDER BY created_at DESC;

EXPLAIN出力例:

id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra
---|-------------|-------|------------|------|---------------|------|---------|------|----------|----------|-----------------------------
1 | SIMPLE | users | NULL | ALL | NULL | NULL | NULL | NULL | 1000000 | 10.00 | Using where; Using filesort

問題点:
* type が ALL：users テーブル全体をフルスキャンしています。
* possible_keys が NULL：WHERE句やORDER BY句で使われているカラムに適切なインデックス候補がないか、MySQLがインデックスを使えないと判断しました。
* rows が 100万：テーブルの全行を読み込んでいます。
* Extra に Using where：読み込んだ全行に対して last_login_at > '2023-01-01' AND status = 'active' の条件を適用しています。
* Extra に Using filesort：created_at DESC でソートするために、インデックスを使わずに一時領域でソート処理を行っています。

このクエリは、テーブルサイズ（rows = 100万）が大きい場合、非常に遅くなります。

原因:
last_login_at、status、created_at のいずれにも、クエリを効率化できるようなインデックスが存在しないか、利用できていません。

改善策:

1. WHERE句にインデックスを追加: まず、WHERE句で絞り込みに使われる last_login_at と status にインデックスを追加することを検討します。
   sql
ALTER TABLE users ADD INDEX idx_last_login_status (last_login_at, status);
   または個別のインデックスでも良いですが、WHERE句で同時に使われる場合は複合インデックスが有効なことが多いです。

   このインデックスを追加した場合のEXPLAIN出力は、type が range や ref になり、Using where が消えるか filtered が改善される可能性があります。しかし、ORDER BY created_at DESC はまだインデックスでカバーされていないため、Using filesort は残る可能性が高いです。

2. WHERE句とORDER BY句をカバーする複合インデックスを検討: クエリのパフォーマンスを最大限に引き出すためには、WHERE句とORDER BY句の両方に対応できる複合インデックスを検討します。この場合、インデックスの定義順序が重要です。ORDER BY句のカラムをインデックスの最後に追加することで、ソート処理もインデックス内で行えるようにする「カバーリングインデックス」を狙います。
   sql
ALTER TABLE users ADD INDEX idx_login_status_created (last_login_at, status, created_at DESC);
   （created_at のソート方向もインデックス定義に合わせるのが良い場合があります。）

   この複合インデックスを追加した場合のEXPLAIN出力例:

   id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra
---|-------------|-------|------------|-------|----------------------|-------------------------|---------|------|--------|----------|-----------------------------
1 | SIMPLE | users | NULL | range | idx_last_login_status,idx_login_status_created | idx_login_status_created | 10+... | NULL | 10000 | 100.00 | Using index condition; Using where
   * type が range に変化：インデックスを使って範囲検索を行っています。
   * key が新しく作成した複合インデックスになりました。
   * rows が 10万 -> 1万に減少（例）：読み込む行数がインデックスによって大幅に減りました。
   * Extra から Using filesort が消えた（理想）：ORDER BY処理がインデックス内で完結しました。
   * Using index condition; Using where: インデックスで絞り込み（ICP）、さらにWHERE句の残りの条件（status）でフィルタリングしています。もしstatusも完全にインデックスに含まれていればUsing indexになる可能性もあります。

   注意点: 複合インデックスの順序は、WHERE句でよく使われるカラムを先頭にし、等価検索（=）に使われるカラムを範囲検索（>, <, BETWEENなど）に使われるカラムより前に置くのが一般的です。ORDER BY句のカラムは、WHERE句のカラムの後に置きます。この例では last_login_at が範囲検索のため、status との順序はどちらが先でも効果が変わらない可能性がありますが、WHERE句での絞り込み効果が高いカラムを先に置くのがセオリーです。

例2：JOINキーのインデックス不在

クエリ:
特定期間の全ての注文と、その注文を行ったユーザーの名前を取得する。

sql
SELECT u.name, o.order_id, o.order_date, o.amount
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.order_date BETWEEN '2023-10-01' AND '2023-10-31';

EXPLAIN出力例:

id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra
---|-------------|--------|------------|------|---------------|------|---------|----------------|----------|----------|-----------------------------
1 | SIMPLE | o | NULL | ALL | idx_order_date| NULL | NULL | NULL | 10000000 | 10.00 | Using where
1 | SIMPLE | u | NULL | ALL | PRIMARY | NULL | NULL | dbname.o.user_id | 1000000 | 100.00 | Using join buffer (Block Nested Loop)

問題点:
* o テーブルの type が ALL：orders テーブル全体をフルスキャンしています。WHERE o.order_date BETWEEN ... の条件があるにも関わらず、key が NULL になっており、idx_order_date が使われていません。
* u テーブルの type が ALL：users テーブル全体をフルスキャンしています。
* u テーブルの Extra に Using join buffer (Block Nested Loop)：非効率なJOIN方法が選択されています。前のテーブル (o) の結果に対して、users テーブルをフルスキャンしてJOINしています。

原因:
* orders.order_date にインデックスがある（possible_keys に表示）が、なぜか使われていない（統計情報の問題、複雑なWHERE句など）。
* orders.user_id にインデックスがない（users.id は通常PRIMARY KEYでインデックスがある前提）。JOIN条件の片方 (o.user_id) にインデックスがないため、JOINが非効率になっています。

改善策:

1. orders.order_date にインデックスがあることを確認し、利用されない原因を探る: まず idx_order_date が本当に存在するか確認します。存在しても使われない場合は、オプティマイザの統計情報を最新にする（ANALYZE TABLE orders;）などの対処が必要かもしれません。クエリによっては FORCE INDEX ヒントを使うことも考えられますが、これは推奨されません。

2. orders.user_id にインデックスを追加: これが最も重要な改善策です。JOIN条件で頻繁に使用されるカラムには必ずインデックスが必要です。
   sql
ALTER TABLE orders ADD INDEX idx_user_id (user_id);

   idx_order_date と idx_user_id の両方が存在し、適切に利用された場合のEXPLAIN出力例:

   id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra
---|-------------|--------|------------|-------|---------------|---------------|---------|----------------|--------|----------|-----------------------
1 | SIMPLE | o | NULL | range | idx_order_date,idx_user_id | idx_order_date| 3 | NULL | 100000 | 100.00 | Using where
1 | SIMPLE | u | NULL | eq_ref| PRIMARY | PRIMARY | 8 | dbname.o.user_id | 1 | 100.00 | Using index
   * o テーブルの type が range に変化：idx_order_date が使われ、期間で効率的に絞り込まれています。rows が大幅に減少しています。
   * u テーブルの type が eq_ref に変化：o.user_id から u.id へのJOINでPRIMARY KEYが効率的に使われています。rows が 1 になっています。
   * Extra から Using join buffer (Block Nested Loop) が消えました。

   これでJOIN処理が劇的に高速化されるはずです。

例3：複合インデックスの最左一致性とkey_len

クエリ:
特定の都市のユーザーを、作成日時の降順で取得する。

sql
SELECT id, name, created_at
FROM users
WHERE city = 'Tokyo'
ORDER BY created_at DESC;

インデックス: INDEX idx_city_created (city, created_at) が存在する。

EXPLAIN出力例:

id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra
---|-------------|-------|------------|-------|-----------------|------------------|---------|--------|-------|----------|-----------------------------
1 | SIMPLE | users | NULL | ref | idx_city_created| idx_city_created | 78 | const | 10000 | 100.00 | Using where; Using filesort

問題点:
* type が ref：city = 'Tokyo' の条件でインデックス (idx_city_created) が使われています。
* key が idx_city_created：意図したインデックスが選択されています。
* key_len が 78（例: VARCHAR(64) + α）：これは city カラムのインデックス長に対応しています。しかし、created_at カラムのインデックス長（通常8バイト）が含まれていません。
* Extra に Using where：WHERE city = 'Tokyo' の条件がインデックスで絞り込めています。
* Extra に Using filesort：ORDER BY created_at DESC のためにファイルソートが発生しています。

原因:
複合インデックス (city, created_at) は、city が等価検索 (=) で使用されているため、最左一致の原則により city の部分はインデックスで効率的に検索できます。しかし、created_at はWHERE句の条件には使われていないため、インデックスの2番目のカラムですが、このクエリではORDER BY句にしか使われていません。MySQLは、インデックス全体を順序通りにスキャンしてソート処理を省略する (type=index) か、インデックスの一部を使って絞り込み (type=ref)、別途ソートするかを選択します。この場合、オプティマイザは type=ref を選びましたが、ORDER BY句の created_at の部分はインデックス順序を直接利用できなかったため、Using filesort が発生しました。

改善策:
ORDER BY句でインデックスを利用するためには、WHERE句で使われるカラムとORDER BY句で使われるカラムを適切な順序で含む複合インデックスが必要です。このクエリの場合、WHERE city = ... AND ... ORDER BY created_at の形に近いですが、WHERE句で使われているのは city だけです。

ORDER BY句をインデックスでカバーするためには、インデックスの定義順序の先頭部分がWHERE句で等価検索されている必要があります。このケースでは city = 'Tokyo' という等価検索があります。したがって、(city, created_at) という複合インデックスは、理論的には city = 'Tokyo' で絞り込んだ後、その結果を created_at の順序でインデックスから取得できるはずです。

しかし、EXPLAINで Using filesort が出ているということは、オプティマイザがそのインデックスをソートに使うと判断しなかった、あるいは何らかの理由で使えなかったことを意味します。考えられる原因はいくつかあります。
* WHERE句とORDER BY句でカラムが分かれている場合、単一のインデックスで両方を最適化するのは難しいことがあります。
* city で絞り込まれる行数がまだ多すぎて、その後の created_at でインデックス順に読み込むよりも、一度メモリやディスクに読み込んでソートした方が速いとオプティマイザが判断した。
* オプティマイザの統計情報が古い、あるいは判断を誤った。

もし city のカーディナリティ（種類の数）が低く、特定の都市に大量のユーザーがいる場合、city = 'Tokyo' で絞り込んでも多くの行がヒットし、その後のソートコストが大きくなる可能性があります。

より確実にORDER BYをインデックスでカバーしたい場合、インデックスの定義順序を (city, created_at DESC) とし、クエリのORDER BY句の方向と合わせることも重要です。

sql
-- 既存のインデックスがあれば削除
DROP INDEX idx_city_created ON users;
-- 新しいインデックスを追加 (ORDER BY方向を考慮)
ALTER TABLE users ADD INDEX idx_city_created (city, created_at DESC);

このインデックスを追加した場合、EXPLAIN出力から Using filesort が消える可能性があります。

id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra
---|-------------|-------|------------|-------|-----------------|------------------|---------|--------|-------|----------|-----------------------------
1 | SIMPLE | users | NULL | ref | idx_city_created| idx_city_created | 78 | const | 10000 | 100.00 | Using index condition; Using where
（※ Using index が付かないのは、SELECTリストにインデックスに含まれないカラム（name, id）があるため。これらのカラムもインデックスに含めれば Using index になりますが、インデックスサイズが大きくなるトレードオフがあります。）

例4：LIKE ‘%…’ とインデックス

クエリ:
名前に「山田」を含むユーザーを検索する。

sql
SELECT id, name FROM users WHERE name LIKE '%山田%';

インデックス: INDEX idx_name (name) が存在する。

EXPLAIN出力例:

id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra
---|-------------|-------|------------|------|---------------|------|---------|------|----------|----------|-------------
1 | SIMPLE | users | NULL | ALL | NULL | NULL | NULL | NULL | 1000000 | 100.00 | Using where

問題点:
* type が ALL：テーブル全体をフルスキャンしています。
* possible_keys が NULL：name カラムにインデックス (idx_name) があるにも関わらず、候補にも挙がっていません。
* key が NULL：インデックスが全く使われていません。

原因:
B-treeインデックスは、値の先頭からの比較に基づいて構築されます。LIKE '%...' のようにパターンがワイルドカード (%) で始まる場合、先頭からの比較ができないため、B-treeインデックスを利用できません（最左一致の原則が適用されない）。

改善策:

1. LIKE句の書き換え: 可能であれば LIKE '山田%' のように前方一致にする。この場合はB-treeインデックスが利用可能です。
2. 全文検索インデックスの利用: 部分一致やキーワード検索には、FULLTEXT インデックスが適しています。ただし、これはMyISAMまたはInnoDBテーブルで、特定の文字セットでのみ利用可能です。
   sql
ALTER TABLE users ADD FULLTEXT INDEX ft_name (name);
-- クエリの書き換え
SELECT id, name FROM users WHERE MATCH(name) AGAINST ('山田');
   FULLTEXT検索は、単純なLIKE検索とは異なるロジック（分かち書き、ストップワードなど）で動作するため、要件に合うか検討が必要です。
3. N-gramなどの特殊なインデックスの利用: 全文検索インデックスが適さない場合、N-gramなどの手法を使ったインデックスを自作するか、対応したストレージエンジンを利用するなどの方法がありますが、これは高度な対応となります。
4. アプリケーション側での対処: 検索対象のデータ量がそれほど多くない場合、あるいは精度が求められない場合は、諦めてフルスキャンを受け入れる、あるいはキャッシュなどを利用することも検討します。

例5：WHERE句での関数使用

クエリ:
生まれた年に1990を含むユーザーを検索する。

sql
SELECT id, name FROM users WHERE YEAR(birth_date) = 1990;

インデックス: INDEX idx_birth_date (birth_date) が存在する。

EXPLAIN出力例:

id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra
---|-------------|-------|------------|------|---------------|------|---------|------|--------- |----------|-------------
1 | SIMPLE | users | NULL | ALL | NULL | NULL | NULL | NULL | 1000000 | 10.00 | Using where

問題点:
* type が ALL：フルスキャン。
* possible_keys が NULL, key が NULL：birth_date にインデックスがあるにも関わらず使われていません。

原因:
WHERE句で検索対象のカラム (birth_date) に対して関数 (YEAR()) を使用しているため、インデックスが利用できません。MySQLは、インデックスに格納されている birth_date の値を直接検索条件と比較するのではなく、全行に対して YEAR() 関数を適用し、その結果を 1990 と比較する必要があると判断します。このため、インデックスをスキップしてフルスキャンを行います。

改善策:

1. 関数を使わない検索条件に書き換える: 可能であれば、関数を使わずにカラムの値を直接比較できる形に書き換えます。この例の場合、「生まれた年が1990」というのは「birth_date が ‘1990-01-01 00:00:00’ 以上かつ ‘1990-12-31 23:59:59’ 以下」という範囲検索に相当します。
   sql
SELECT id, name FROM users WHERE birth_date BETWEEN '1990-01-01' AND '1990-12-31 23:59:59';
   このクエリであれば、birth_date カラムのB-treeインデックスが range タイプで効率的に利用されます。

   id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra
---|-------------|-------|------------|-------|---------------|---------------|---------|------|-------|----------|-----------------------
1 | SIMPLE | users | NULL | range | idx_birth_date| idx_birth_date| 8 | NULL | 10000 | 100.00 | Using index condition
   （例: rows が1万に減少し、type が range に、Extra に Using index condition が付いた）

2. MySQL 8.0+ の関数インデックスを利用: MySQL 8.0から、関数や式の結果にインデックスを張る「関数インデックス」がサポートされました。
   sql
ALTER TABLE users ADD INDEX idx_birth_year ((YEAR(birth_date)));
   このように関数インデックスを作成すれば、元のクエリ SELECT id, name FROM users WHERE YEAR(birth_date) = 1990; のままでもインデックスが利用可能になります。

   id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra
---|-------------|-------|------------|-------|-----------------|--------------|---------|-------|------|----------|-----------------------
1 | SIMPLE | users | NULL | ref | idx_birth_year | idx_birth_year | 8 | const | 1000 | 100.00 | Using index
   （例: type が ref になり、rows が激減、Extra に Using index が付いた）

例6：GROUP BY/DISTINCTとUsing temporary

クエリ:
都市ごとのユーザー数をカウントする。

sql
SELECT city, COUNT(*) FROM users GROUP BY city;

インデックス: INDEX idx_city (city) が存在する。

EXPLAIN出力例:

id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra
---|-------------|-------|------------|-------|---------------|----------|---------|------|----------|----------|--------------------------
1 | SIMPLE | users | NULL | index | idx_city | idx_city | 78 | NULL | 1000000 | 100.00 | Using index; Using temporary

問題点:
* type が index：idx_city インデックス全体をスキャンしています（この例では、SELECTリストにcityしかないのでカバーリングインデックスになっています）。これはALLよりはマシですが、行数が多いと時間がかかります。
* Extra に Using temporary：GROUP BY処理のために一時テーブルが作成されています。

原因:
GROUP BY city を行う際、city カラムのインデックス idx_city が存在するため、MySQLはインデックスの順序を利用してテーブル本体へのアクセスを省略 (Using index) できます。しかし、インデックスの順序は city でソートされていますが、そのままでは各 city の COUNT(*) を効率的に計算するために一時テーブルが必要になる場合があります。MySQLは、インデックスを使って順序を保証しつつ、その結果を一時テーブルに入れて集計処理を行うことを選択しました。特に、GROUP BY と ORDER BY が混在する場合、または集計関数が複雑な場合に一時テーブルが発生しやすいです。

改善策:
GROUP BY や DISTINCT による一時テーブルの発生は、適切なインデックスによって完全に回避できるとは限りません。MySQLがインデックスを使ってソート・グループ化処理を内部的に行えると判断した場合、一時テーブルは回避されます。

この例の場合、idx_city インデックスが存在するため、MySQLはインデックスをスキャンして city の値を取得し、グループ化処理を行います。Using temporary が表示されているのは、おそらくそのグループ化処理の過程で一時テーブルが必要になったためです。

考えられる対策：

1. インデックスの確認: city カラムにインデックスが正しく存在し、key カラムでそれが使われているか再確認します。
2. クエリの確認: クエリが複雑な場合、一時テーブルを回避するために書き換えを検討します。ただし、この単純な GROUP BY クエリでは、書き換えは難しいかもしれません。
3. MySQL設定の調整: 一時テーブルがディスクに作成されている場合は、tmp_table_size や max_heap_table_size の値を増やして、メモリ上に一時テーブルが作成されるように調整します。ただし、これはシステム全体の設定であり、他のクエリにも影響するため慎重に行う必要があります。また、これらの設定値よりも一時テーブルが大きくなった場合は、やはりディスクに書き出されます。
4. 集計済みテーブルの利用: もし、頻繁に同じ集計を行う必要がある場合は、事前に集計した結果を別のテーブルに格納しておき、そちらを参照する（マテリアライズドビューのような考え方）という方法もあります。

この例のように、type=index かつ Using index が出ていても Using temporary が付くことはあります。これは、インデックスは使って効率的にデータを取得したが、最終的な集計やソート処理のために一時テーブルが必要になっている状態です。一時テーブルのコストはデータ量に比例するため、rows が大きい場合は問題となります。

---

上記の例は、代表的なBADパターンの一部です。EXPLAIN出力には、これらの他にも様々な組み合わせが現れます。重要なのは、type、rows、Extra の3つのカラムを中心に、出力全体を総合的に判断することです。

• type=ALL と rows が大きい → インデックスの不在または不適切
• Using filesort → ORDER BY句のインデックス不足または不適切
• Using temporary → GROUP BY/DISTINCT/UNION/サブクエリなどでのインデックス不足または非効率
• Using join buffer → JOIN条件の片方または両方に適切なインデックスがない
• type が ALL や index で filtered が低い → WHERE句の絞り込みがインデックスでできていない

これらのサインが見つかったら、原因となっているカラムを特定し、適切なインデックスの追加・修正、またはクエリの書き換えを検討します。

第5章：EXPLAINだけじゃない！その他の性能分析ツール

EXPLAINはクエリの「計画」を見る強力なツールですが、それだけでは特定できない原因もあります。クエリが実際にどのように実行されたか、システム全体の状態、ロックの状況などを把握するためには、他のツールも併せて活用する必要があります。

5.1 スロークエリログの詳細な設定と分析方法

スロークエリログ（Slow Query Log）は、設定した閾値（long_query_time で指定した秒数）を超えて実行されたクエリを記録するログファイルです。どのクエリが実際に遅かったのかを特定する上で非常に役立ちます。

設定:

• slow_query_log = ON: スロークエリログを有効にする。
• long_query_time = 2: 何秒以上のクエリを記録するか（例: 2秒）。
• slow_query_log_file = /var/log/mysql/slow-query.log: ログファイルのパスを指定。
• log_queries_not_using_indexes = ON: インデックスを使用しないクエリも記録するか（たとえ long_query_time 未満でも記録されることがある）。インデックスの問題を発見するのに役立ちます。
• log_output = FILE: 出力形式（FILE または TABLE）。TABLEにすると mysql.slow_log テーブルに記録されます。

これらの設定は、MySQLの設定ファイル（my.cnfまたはmy.ini）に記述し、MySQLサーバーを再起動または設定をリロードすることで反映されます。

分析:

スロークエリログは、単にログファイルを開いて読むこともできますが、大量のクエリが記録されている場合、手動での分析は非現実的です。専用の解析ツールを使用するのが一般的です。

• mysqldumpslow: MySQLに含まれる基本的なツールです。スロークエリログファイルを読み込み、クエリをパターンごとに集計して、実行回数、平均実行時間、合計実行時間、ロック時間などで並べ替えて表示できます。
• pt-query-digest (Percona Toolkit): より高機能なツールです。スロークエリログ、SHOW PROCESSLIST、Performance Schemaなど様々なソースから情報を取得し、詳細なレポートを生成します。最も遅いクエリ、最も多く実行されたクエリ、ロック時間の長いクエリなどを特定し、EXPLAIN情報も自動で取得・表示させることができます。スロークエリ分析のデファクトスタンダードツールと言えます。

スロークエリログを分析することで、遅いクエリを洗い出し、そのクエリに対してEXPLAINを実行するというワークフローが確立できます。

5.2 SHOW PROCESSLIST / SHOW ENGINE INNODB STATUS

• SHOW PROCESSLIST: 現在MySQLサーバーで実行されている全てのスレッド（クライアント接続や内部タスク）のリストを表示します。特に State カラム（スレッドの状態：Sending data, Sorting result, Locked など）や Time カラム（その状態にいる時間）を確認することで、長時間実行されているクエリや、特定の状態でスタックしているスレッド（ロック待ちなど）を発見できます。フル権限がある場合は SHOW FULL PROCESSLIST でクエリ全文を表示できます。
• SHOW ENGINE INNODB STATUS: InnoDBストレージエンジンの詳細な状態を表示します。特に TRANSACTIONS セクションでは、現在のトランザクション、ロックされているリソース、ロック待ちの情報（LATEST DETECTED DEADLOCK など）を確認できます。ロック競合による遅延やデッドロックの原因特定に非常に有効です。

これらのコマンドは、リアルタイムの状況把握に役立ちます。

5.3 パフォーマンススキーマとSys Schema

• Performance Schema: MySQL 5.5以降で導入された、低レベルのサーバーアクティビティに関する非常に詳細な情報を収集する機能です。実行されたクエリ、待機イベント（ロック待ち、I/O待ちなど）、ステージイベント（ソート、コピーなど）、リソース消費（メモリ、ファイル）など、多岐にわたるメトリクスを取得できます。
• Sys Schema: Performance Schemaの生のデータを、より人間が理解しやすい形式（テーブルやビュー）で提供するスキーマです（MySQL 5.6以降、MySQL 5.7で標準装備）。例えば、実行時間の長いクエリトップN、テーブルごとのI/O待機時間、インデックスの使用状況、メモリ割り当て状況などを簡単に確認できます。

Performance SchemaとSys Schemaは非常に強力ですが、収集する情報量が多いため、有効化や設定には注意が必要です（オーバーヘッドが発生する可能性があるため）。しかし、詳細なボトルネック（CPU使用率が高いのか、ディスクI/Oが遅いのか、特定のイベントで待機しているのかなど）を特定するには不可欠なツールです。sys スキーマの statements_summary や waits_global_by_event などのビューは、遅延原因分析に役立ちます。

5.4 Optimizer Trace の活用

MySQL 5.6以降で利用できる Optimizer Trace は、MySQLオプティマイザがクエリの実行計画を決定するまでの詳細な過程をJSON形式で出力する機能です。

• なぜそのインデックスが選ばれたのか？
• なぜ特定のインデックスが候補から外されたのか？
• なぜJOIN順序がそのようになったのか？
• なぜフルスキャンを選択したのか？

といった、オプティマイザの判断理由を知ることができます。EXPLAINだけでは「結果」しかわかりませんが、Optimizer Traceを使えば「なぜそうなったか」を深く掘り下げて分析できます。

sql
SET SESSION optimizer_trace='enabled=on';
-- 分析したいクエリを実行
SELECT ... FROM ... WHERE ... ;
-- トレース結果を取得
SELECT * FROM information_schema.optimizer_trace;
SET SESSION optimizer_trace='enabled=off';

Optimizer Traceは出力が膨大になるため、特定のクエリに対して一時的に有効にして使用するのが一般的です。

第6章：実行計画を改善するための具体的なチューニング手法

EXPLAINやその他のツールを使って遅延の原因を特定したら、いよいよ改善策を実行します。主なチューニング手法は以下の通りです。

6.1 適切なインデックスの設計と作成

多くのクエリ遅延はインデックスの問題に起因します。適切なインデックス戦略が最も重要です。

• インデックスを作成すべきカラム:
  • WHERE句で頻繁に検索条件に使われるカラム。
  • JOIN句で結合条件に使われるカラム。
  • ORDER BY句やGROUP BY句でソート・集計に使われるカラム。
• インデックスの種類:
  • B-tree: MySQLの標準的なインデックスです。等価検索、範囲検索、最左一致でのプレフィックス検索、ORDER BY、GROUP BYに利用できます。ほとんどのケースでこれを使います。
  • Hash: MEMORYストレージエンジンで使用できます（InnoDBでも非公式には利用できますが推奨されません）。等価検索には高速ですが、範囲検索やORDER BYには使えません。
  • Full-text: 全文検索用。
  • Spatial: 地理空間データ用。
• カーディナリティ: カラムに含まれる値の種類が多い（カーディナリティが高い）ほど、インデックスによる絞り込み効果が高くなります。例えば、性別のようなカーディナリティが非常に低いカラムに単一のインデックスを張っても、インデックスによる絞り込み効果は限定的です。
• 複合インデックスの順序: 複数のカラムを含む複合インデックスの場合、カラムの定義順序が非常に重要です。MySQLのB-treeインデックスは「最左一致の原則」に基づき、インデックスの先頭から順に利用されます。
  • INDEX (colA, colB, colC) の場合、WHERE colA = ...、WHERE colA = ... AND colB = ...、WHERE colA = ... AND colB = ... AND colC = ... のようなクエリでインデックスを利用できます。
  • WHERE colB = ... や WHERE colC = ... だけでは、この複合インデックスは利用できません。
  • WHERE colA = ... AND colC = ... の場合、colA は利用できますが、その次の colB がWHERE句にないため、colC の部分はインデックス順序を直接利用できません（Index Condition Pushdownで効率化される場合はあります）。
  • 戦略: WHERE句で等価検索 (=) に使われるカラムを先に、範囲検索 (<, >, BETWEEN) に使われるカラムをその後に、最後にORDER BY/GROUP BYに使われるカラムを配置するのが一般的なセオリーです。
• カバーリングインデックス: SELECTリストに含まれる全てまたはほとんどのカラムをインデックスに含めることで、テーブル本体へのアクセス（ディスクI/O）を完全に回避し、インデックスオンリースキャン（Extra: Using index）を実現します。非常に高速ですが、インデックスのサイズが大きくなる、更新時のコストが増えるといったトレードオフがあります。
• 冗長インデックス・未使用インデックスの排除: 不要なインデックスは、ディスク容量を消費し、INSERT/UPDATE/DELETE操作のオーバーヘッドを増やし、オプティマイザの選択肢を不必要に増やすため、パフォーマンスに悪影響を与える可能性があります。pt-duplicate-key-checker や Sys Schema の schema_unused_indexes ビューなどで未使用・冗長なインデックスを特定し、削除を検討します。

6.2 クエリのリライトテクニック

非効率な実行計画になってしまうクエリは、書き換えることで効率化できる場合があります。

• 非効率なサブクエリのJOINへの書き換え: 特に DEPENDENT SUBQUERY になっているサブクエリは、JOINに書き換えることで性能が向上することが多いです。
• OR条件のUNION ALLへの書き換え: WHERE colA = val1 OR colB = val2 のように、異なるカラムに対するOR条件でそれぞれにインデックスがある場合、MySQLは index_merge を選択することがありますが、これが非効率な場合があります。このような場合、SELECT ... WHERE colA = val1 UNION ALL SELECT ... WHERE colB = val2 のようにUNION ALLに書き換えることで、各条件でインデックスを効率的に利用できる可能性があります。（ただし、UNION ALLは重複排除を行わない点に注意。重複排除が必要ならUNION DISTINCTを使いますが、別途重複排除処理のコストがかかります。）
• LIMIT/OFFSETのページネーション問題: LIMIT offset, count のようなページネーションは、offsetの値が大きくなると遅くなります。MySQLはoffsetまでの行を読み捨ててからcount件を取得するためです。改善策として、直前に取得した行のIDを利用して次に取得する範囲を指定する方法などがあります（例: WHERE id > last_id ORDER BY id LIMIT count）。
• SELECT :* 必要なカラムだけをSELECTするようにします。特にTEXT/BLOB型のカラムはI/O負荷が高いため、本当に必要な場合以外は取得しないようにします。
• COUNT(*) の効率化: InnoDBでは正確な行数取得のためにテーブルスキャンが必要な場合があります。頻繁に正確な件数が必要な場合は、別途カウント用のテーブルを用意する、あるいは一定時間の概算値で良いか検討します。COUNT(primary_key_col) は COUNT(*) と同等の速度になることが多いです。
• 非効率な集計: DISTINCTやGROUP BYで一時テーブルが発生する場合、インデックスの追加や、クエリのロジックの見直しで一時テーブルを回避できないか検討します。

6.3 データベース設計の見直し

より根本的な解決策として、データベース設計自体を見直すことも必要になる場合があります。

• 非正規化の検討: 読み取り性能を極端に重視する場合、意図的に冗長性を持たせてJOINを減らす非正規化を検討します。ただし、データの一貫性維持が難しくなる、更新時のコストが増えるといったデメリットがあります。
• データ型の最適化: カラムに適切なデータ型を選択します。例えば、数値をVARCHARで格納しない、必要以上に大きな数値型や文字列型を使用しないなどです。データ型の不一致はインデックスが使えない原因にもなります。
• パーティショニング: 巨大なテーブル（数千万～数億行）の場合、パーティショニングによって物理的にデータを分割し、管理性や特定のクエリの性能（パーティションプルーニング）を向上させることができます。

6.4 MySQLサーバー設定のチューニング

ハードウェアリソースを効率的に使うために、MySQLの設定（my.cnf/my.ini）を適切にチューニングします。

• innodb_buffer_pool_size: InnoDBで最も重要な設定です。データやインデックスがキャッシュされるメモリ領域のサイズです。サーバーの物理メモリの70-80%程度を割り当てるのが一般的な推奨値です。この値が大きいほど、ディスクI/Oを減らすことができます。
• sort_buffer_size: ソート処理に使用されるバッファサイズ。Using filesort が頻繁に出る場合に効果があることがありますが、スレッドごとに割り当てられるため、あまり大きくしすぎるとメモリを浪費します。
• tmp_table_size / max_heap_table_size: メモリ上の一時テーブルの最大サイズ。Using temporary が頻繁に出る場合に、一時テーブルがディスクではなくメモリに作成されるようにこれらの値を増やすことが有効です。これもスレッドごとの設定であり、システム全体のメモリ使用量に注意が必要です。
• join_buffer_size: Join Bufferのサイズ。Using join buffer が出る場合に影響しますが、JOINされる側のテーブルにインデックスがないことが根本原因のため、インデックスを追加する方が優先度は高いです。
• スレッドキャッシュ、ネットワーク関連設定など: システムの特性に合わせて他の設定も調整します。

これらの設定変更はシステム全体に影響を与えるため、影響を理解した上で慎重に行い、変更後は効果測定と監視を必ず行います。

6.5 アプリケーション側の工夫

データベース側のチューニングだけでなく、アプリケーション側でもパフォーマンス改善のための工夫ができます。

• コネクションプーリング: データベースへの接続確立はコストが高い処理です。アプリケーションでコネクションプールを利用し、接続の再利用を行うことでオーバーヘッドを減らします。
• N+1問題の解消: ORMなどを使用している場合に発生しやすい問題です。親テーブルのレコードN件を取得した後、そのN件それぞれに対して子テーブルの情報を取得するクエリが発行される、といったパターンです。これをJOINやIN句を使った1つのクエリにまとめることで大幅に効率化できます。
• バルクインサート/アップデート: 複数件のデータを挿入・更新する場合、1行ずつ実行するのではなく、まとめて1つのクエリで実行することで効率が向上します。
• キャッシュ層の導入: 頻繁にアクセスされるが更新頻度の低いデータは、MemcachedやRedisなどのキャッシュシステムに格納し、データベースへのアクセスを減らします。

第7章：継続的なパフォーマンス監視と改善のサイクル

データベースのパフォーマンスチューニングは、一度行えば完了というものではありません。データ量は常に増加し、アクセスパターンも変化します。継続的な監視と改善のサイクルを確立することが重要です。

1. 監視ツールの導入: データベースのCPU、メモリ、ディスクI/O、ネットワークといったリソース使用状況、クエリの実行時間、ロック待ちなどをリアルタイムで監視できるツール（Prometheus+Grafana、Mackerel、Zabbix、Datadogなど）を導入します。
2. 定期的なEXPLAIN分析とスロークエリログのチェック: 定期的にスロークエリログを確認し、新しく遅くなったクエリがないかチェックします。見つかったクエリに対してEXPLAINを実行し、原因を分析します。
3. パフォーマンス低下の兆候検知と対応: 監視アラートやユーザーからの報告などでパフォーマンス低下の兆候を早期に検知し、原因を特定して対応します。
4. チューニングの効果測定: チューニングを行った後は、その変更によって実際にパフォーマンスが向上したかどうかを、監視メトリクスやスロークエリログなどで確認します。期待通りの効果が得られなかった場合は、別の原因を探るか、別の改善策を検討します。
5. キャパシティプランニング: データ量の増加やアクセス増加を予測し、事前にハードウェア増強やシャード化などの対策を検討します。

終章：まとめ – パフォーマンスチューニングは旅である

MySQLの遅いクエリの原因は多岐にわたりますが、その多くは実行計画（EXPLAIN）を読み解くことで特定の手がかりを得られます。EXPLAINは、MySQLが「どのように」クエリを実行しようとしているか、「なぜ」そう判断したのかを理解するための強力なツールです。

• type カラムでアクセス方法の効率を判断する。ALL は危険信号。
• rows カラムで読み込み予測行数の多寡を確認する。
• Extra カラムで非効率な追加処理（Using filesort, Using temporary, Using join bufferなど）がないかチェックする。
• これらの情報から問題点を見つけ、インデックスの追加・修正、クエリの書き換え、データベース設計の見直しなどの改善策を検討・実施する。
• EXPLAINだけでなく、スロークエリログ、SHOW PROCESSLIST、Performance Schemaなども活用し、多角的に原因を分析する。

データベースパフォーマンスの最適化は、一度行えば終わりというものではなく、システムの変化に合わせて継続的に取り組むべき課題です。EXPLAINの見方・活用法を習得し、他のツールと組み合わせることで、遅いクエリの原因特定と改善を効果的に行えるようになります。

パフォーマンスチューニングは、まさに終わりのない旅のようなものです。しかし、この「旅」を効果的に進めるための羅針盤として、EXPLAINは常にあなたの強力な味方となるでしょう。この記事が、あなたのMySQLパフォーマンスチューニングの旅の一助となれば幸いです。

---