MySQL - EXPLAINのソースを表示

== 概要 ==
<code>EXPLAIN</code> 文は、MySQLがSELECT文をどのように実行するかを表示する、クエリ実行計画の解析ツールである。<br>
クエリ最適化の第一歩として、<code>EXPLAIN</code> を使用することで、インデックスの使用状況、テーブルスキャンの有無、結合の順序等を確認できる。<br>
<br>
<code>EXPLAIN</code> は、パフォーマンスボトルネックの特定に不可欠であり、フルテーブルスキャン、ファイルソート、一時テーブルの使用等の問題を検出できる。<br>
<br>
MySQL 5.6以降では、<code>EXPLAIN</code> の出力形式として、従来の表形式に加えて、JSON形式、TREE形式 (8.0.16以降)、ANALYZE形式 (8.0.18以降) がサポートされている。<br>
<br>
<code>EXPLAIN</code> は、SELECT文だけでなく、DELETE文、INSERT文、REPLACE文、UPDATE文に対しても使用できる。<br>
これにより、更新系クエリのパフォーマンスも分析できる。<br>
<br><br>

== 基本構文 ==
==== EXPLAIN文の構文 ====
<code>EXPLAIN</code> 文の基本構文を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 EXPLAIN [explain_type] {SELECT文 | DELETE文 | INSERT文 | REPLACE文 | UPDATE文}
 
 explain_type:
    {FORMAT = {TRADITIONAL | JSON | TREE}}
    | {ANALYZE}
 </syntaxhighlight>
<br>
下表に、<code>EXPLAIN</code> のバリエーションを示す。<br>
<br>
<center>
{| class="wikitable"
|-
! 構文 !! 説明
|-
| <code>EXPLAIN</code> || デフォルトの表形式 (TRADITIONAL形式) で実行計画を表示する。
|-
| <code>EXPLAIN FORMAT=JSON</code> || JSON形式で実行計画を表示する。<br>コスト情報等の詳細情報を含む。
|-
| <code>EXPLAIN FORMAT=TREE</code> || ツリー形式で実行計画を表示する。(MySQL 8.0.16以降)
|-
| <code>EXPLAIN ANALYZE</code> || 実際にクエリを実行して、推定値と実測値を比較した結果を表示する。(MySQL 8.0.18以降)
|}
</center>
<br>
==== EXPLAINの実行方法 ====
基本的な <code>EXPLAIN</code> の実行例を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 # 最もシンプルなEXPLAIN
 EXPLAIN SELECT * FROM users WHERE id = 100;
 
 # 複雑な結合クエリの実行計画
 EXPLAIN
 SELECT u.name, o.order_date, oi.product_name
    FROM users u
    INNER JOIN orders o ON u.id = o.user_id
    INNER JOIN order_items oi ON o.id = oi.order_id
    WHERE u.status = 'active'
       AND o.order_date >= '2024-01-01';
 
 # サブクエリを含むクエリの実行計画
 EXPLAIN
 SELECT * FROM users
    WHERE id IN (SELECT user_id FROM orders WHERE status = 'completed');
 
 # UPDATE文の実行計画
 EXPLAIN UPDATE users SET status = 'inactive' WHERE last_login < '2023-01-01';
 
 # DELETE文の実行計画
 EXPLAIN DELETE FROM logs WHERE created_at < '2024-01-01';
 </syntaxhighlight>
<br>
<code>EXPLAIN</code> は実際にクエリを実行しないため、SELECT文以外でも安全に使用できる。<br>
ただし、<code>EXPLAIN ANALYZE</code> は実際にクエリを実行するため、DELETE文やUPDATE文には注意が必要である。<br>
<br><br>

== EXPLAIN出力カラム ==
==== 出力カラム一覧 ====
<code>EXPLAIN</code> の出力には、以下の12個のカラムが含まれる。<br>
<br>
<center>
{| class="wikitable"
|+ EXPLAIN出力カラム一覧
! カラム名 !! 説明
|-
| id || SELECTの識別子 (クエリ内のSELECT文の実行順序)
|-
| select_type || SELECTのタイプ (SIMPLE, PRIMARY, UNION, SUBQUERY等)
|-
| table || 参照しているテーブル名
|-
| partitions || 使用されるパーティション (パーティションテーブルの場合)
|-
| type || アクセスタイプ (結合タイプ) (system, const, ref, range, index, ALL等)
|-
| possible_keys || 使用可能なインデックスのリスト
|-
| key || 実際に使用されたインデックス
|-
| key_len || 使用されたインデックスのバイト長
|-
| ref || インデックスと比較される値またはカラム
|-
| rows || 検査される行数の推定値
|-
| filtered || フィルタリングされる行の割合 (%)
|-
| Extra || 追加情報 (Using where, Using index, Using filesort等)
|}
</center>
<br>
これらのカラムを理解することで、クエリのパフォーマンス特性を把握できる。<br>
特に、<u>type</u>、<u>key</u>、<u>rows</u>、<u>Extra</u> は重要な指標である。<br>
<br>
==== id ====
<u>id</u> カラムは、SELECT文の識別子である。<br>
クエリ内の各SELECT文に対して、一意の番号が割り当てられる。<br>
<br>
<u>id</u> の値が同じ行は、同じSELECT文の一部として実行される。<br>
<u>id</u> の値が異なる場合、数値が大きい方が先に実行される。(外側から内側へ)<br>
<br>
例を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 EXPLAIN
 SELECT * FROM users
    WHERE id IN (SELECT user_id FROM orders WHERE status = 'completed');
 </syntaxhighlight>
<br>
この場合、サブクエリ (orders) の <u>id</u> が大きく、先に実行される。<br>
その後、メインクエリ (users) が実行される。<br>
<br>
UNIONクエリの場合、各SELECTに異なる <u>id</u> が割り当てられ、UNIONの結果をマージする行には <u>id</u> が <code>NULL</code> となる。<br>
<br>
==== select_type ====
<u>select_type</u> カラムは、SELECTのタイプを示す。<br>
主要な値を以下に示す。<br>
<br>
<center>
{| class="wikitable"
|+ select_typeの値
! 値 !! 説明
|-
| SIMPLE || 単純なSELECT (サブクエリやUNIONを含まない)
|-
| PRIMARY || 最も外側のSELECT (サブクエリやUNIONを含む場合)
|-
| UNION || UNIONの2番目以降のSELECT
|-
| DEPENDENT UNION || UNIONの2番目以降のSELECTで、外側のクエリに依存
|-
| UNION RESULT || UNIONの結果を取得する処理
|-
| SUBQUERY || サブクエリ内の最初のSELECT
|-
| DEPENDENT SUBQUERY || サブクエリ内の最初のSELECTで、外側のクエリに依存
|-
| DERIVED || FROM句のサブクエリ (派生テーブル)
|-
| MATERIALIZED || マテリアライズされたサブクエリ
|-
| UNCACHEABLE SUBQUERY || キャッシュできないサブクエリ (変数を含む等)
|-
| UNCACHEABLE UNION || キャッシュできないUNION
|}
</center>
<br>
<u>SIMPLE</u> は、最もシンプルで高速なクエリである。<br>
<u>DEPENDENT SUBQUERY</u> は、外側のクエリの各行に対してサブクエリが実行されるため、パフォーマンスが低下する可能性がある。<br>
<br>
==== table ====
<u>table</u> カラムは、その行が参照しているテーブル名を示す。<br>
<br>
通常のテーブル名以外に、以下の特殊な値が表示される場合がある。<br>
* <code>&lt;derivedN&gt;</code>
*: 派生テーブル (FROM句のサブクエリ) で、Nは対応する <u>id</u> の値。
* <code>&lt;unionM,N&gt;</code>
*: UNIONの結果で、MとNはUNION対象の <u>id</u> の値。
* <code>&lt;subqueryN&gt;</code>
*: マテリアライズされたサブクエリで、Nは対応する <u>id</u> の値。
<br>
==== partitions ====
<code>partitions</code> カラムは、クエリがアクセスするパーティションを示す。<br>
パーティションテーブルを使用していない場合、このカラムは <code>NULL</code> となる。<br>
<br>
パーティションテーブルを使用している場合、アクセスされるパーティション名がカンマ区切りで表示される。<br>
パーティションプルーニング (不要なパーティションのスキップ) が適切に行われているかを確認できる。<br>
<br>
例を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 # パーティションテーブルの例
 CREATE TABLE logs (
    id INT,
    created_at DATE,
    message TEXT
 )
 PARTITION BY RANGE (YEAR(created_at)) (
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025)
 );
 
 # 特定の年のデータを取得
 EXPLAIN SELECT * FROM logs WHERE created_at >= '2024-01-01';
 # partitionsカラムには 'p2024' のみが表示される
 </syntaxhighlight>
<br>
==== type (アクセスタイプ) ====
<u>type</u> カラムは、テーブルへのアクセス方法 (結合タイプ) を示す。<br>
パフォーマンスの観点から、最も重要なカラムの1つである。<br>
<br>
アクセスタイプを性能の良い順 (上から順) に以下に示す。<br>
<br>
<center>
{| class="wikitable"
|+ アクセスタイプ (性能順)
! タイプ !! 説明 !! パフォーマンス
|-
| system || テーブルに1行のみ (システムテーブル) || 最速
|-
| const || PRIMARY KEYまたはUNIQUEインデックスで定数と比較 (最大1行) || 非常に高速
|-
| eq_ref || 結合で、各行に対してPRIMARY KEYまたはUNIQUEインデックスを使用 (1行) || 非常に高速
|-
| ref || 非UNIQUE インデックスを使用 (複数行の可能性) || 高速
|-
| fulltext || FULLTEXTインデックスを使用 || 高速 (全文検索)
|-
| ref_or_null || ref と同様だが、NULLも検索 || 高速
|-
| index_merge || 複数のインデックスを使用してマージ || 中速
|-
| unique_subquery || IN句のサブクエリでUNIQUEインデックスを使用 || 中速
|-
| index_subquery || IN句のサブクエリで非UNIQUEインデックスを使用 || 中速
|-
| range || インデックスを使用した範囲スキャン || 中速
|-
| index || インデックスフルスキャン (インデックス全体を読む) || 遅い
|-
| ALL || フルテーブルスキャン (テーブル全体を読む) || 最も遅い
|}
</center>
<br>
理想的には、<u>const</u>、<u>eq_ref</u>、<u>ref</u>、<u>range</u> を目指す。<br>
<u>index</u> や <u>ALL</u> が表示される場合、インデックスの追加または最適化を検討すべきである。<br>
<br>
アクセスタイプの例を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 # constの例 (PRIMARY KEYで定数検索)
 EXPLAIN SELECT * FROM users WHERE id = 100;
 # type: const
 
 # refの例 (非UNIQUEインデックスで検索)
 EXPLAIN SELECT * FROM users WHERE status = 'active';
 # type: ref (statusにインデックスがある場合)
 
 # rangeの例 (範囲検索)
 EXPLAIN SELECT * FROM users WHERE created_at BETWEEN '2024-01-01' AND '2024-12-31';
 # type: range (created_atにインデックスがある場合)
 
 # ALLの例 (フルテーブルスキャン)
 EXPLAIN SELECT * FROM users WHERE YEAR(created_at) = 2024;
 # type: ALL (関数を使用しているためインデックスが使用されない)
 </syntaxhighlight>
<br>
==== possible_keys ====
<u>possible_keys</u> カラムは、MySQLが使用を検討したインデックスのリストを示す。<br>
実際に使用されたインデックスは <u>key</u> カラムに表示される。<br>
<br>
<u>possible_keys</u> が <code>NULL</code> の場合、使用可能なインデックスが存在しないことを示す。<br>
この場合、WHERE句やJOIN条件で使用されているカラムにインデックスを追加することを検討する。<br>
<br>
複数のインデックスが候補として表示される場合、MySQLのオプティマイザが最適なインデックスを選択する。<br>
ただし、オプティマイザの選択が最適でない場合もあるため、<u>key</u> カラムと比較して確認する。<br>
<br>
==== key ====
<u>key</u> カラムは、実際に使用されたインデックス名を示す。<br>
<u>possible_keys</u> の中から、オプティマイザが選択したインデックスである。<br>
<br>
<u>key</u> が <code>NULL</code> の場合、インデックスが使用されていないことを示す。<br>
これは、フルテーブルスキャンが発生していることを意味し、パフォーマンス改善の余地がある。<br>
<br>
<u>key</u> が <code>PRIMARY</code> の場合、PRIMARY KEYが使用されている。<br>
その他の値は、インデックス名 (CREATE INDEX時に指定した名前) である。<br>
<br>
例を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 # インデックスが使用される例
 EXPLAIN SELECT * FROM users WHERE status = 'active';
 # possible_keys: idx_status
 # key: idx_status
 
 # インデックスが使用されない例
 EXPLAIN SELECT * FROM users WHERE UPPER(email) = 'ADMIN@EXAMPLE.COM';
 # possible_keys: NULL
 # key: NULL (関数を使用しているためインデックスが使用されない)
 </syntaxhighlight>
<br>
==== key_len ====
<u>key_len</u> カラムは、使用されたインデックスのバイト長を示す。<br>
複合インデックスの場合、どの部分まで使用されたかを判断できる。<br>
<br>
<u>key_len</u> の計算方法を以下に示す。<br>
<br>
<center>
{| class="wikitable"
|+ key_lenの計算 (主要なデータ型)
! データ型 !! バイト長 !! NULL許容の場合 !! 可変長の場合
|-
| INT || 4 || +1 || -
|-
| BIGINT || 8 || +1 || -
|-
| CHAR(N) || N × 文字セットのバイト数 || +1 || -
|-
| VARCHAR(N) || N × 文字セットのバイト数 || +1 || +2
|-
| DATE || 3 || +1 || -
|-
| DATETIME || 8 || +1 || -
|-
| TIMESTAMP || 4 || +1 || -
|}
</center>
<br>
例を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 # 複合インデックスの例
 CREATE INDEX idx_user_status_created ON users(status, created_at);
 
 # statusのみが使用される場合
 EXPLAIN SELECT * FROM users WHERE status = 'active';
 # key_len: 50 (VARCHAR(50) の場合、50 + 1(NULL) + 2(可変長) = 53 程度)
 
 # statusとcreated_atの両方が使用される場合
 EXPLAIN SELECT * FROM users WHERE status = 'active' AND created_at > '2024-01-01';
 # key_len: 53 + 8 + 1 = 62 程度 (DATETIMEの場合)
 </syntaxhighlight>
<br>
<u>key_len</u> が短い場合、複合インデックスの一部のみが使用されていることを示す。<br>
インデックスの順序を見直すことで、パフォーマンスを改善できる可能性がある。<br>
<br>
==== ref ====
<u>ref</u> カラムは、インデックスと比較される値またはカラムを示す。<br>
<br>
<u>ref</u> の主要な値を以下に示す。<br>
<br>
<center>
{| class="wikitable"
|-
! 値 !! 説明
|-
| <code>const</code> || 定数値と比較
|-
| <code>データベース名.テーブル名.カラム名</code> || 他のテーブルのカラムと比較 (結合)
|-
| <code>func</code> || 関数の結果と比較
|-
| <code>NULL</code> || 比較対象なし (rangeスキャン等)
|}
</center>
<br>
例を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 # constの例
 EXPLAIN SELECT * FROM users WHERE id = 100;
 # ref: const
 
 # 結合の例
 EXPLAIN
 SELECT u.name, o.order_date
    FROM users u
    INNER JOIN orders o ON u.id = o.user_id
    WHERE u.status = 'active';
 # usersのref: const
 # ordersのref: database_name.u.id
 </syntaxhighlight>
<br>
==== rows ====
<u>rows</u> カラムは、MySQLがクエリ実行時に検査すると推定する行数を示す。<br>
この値は推定値であり、実際の行数とは異なる場合がある。<br>
<br>
<u>rows</u> の値が大きい場合、多くの行を検査する必要があることを示す。<br>
インデックスの追加、WHERE句の見直し、クエリの最適化を検討する。<br>
<br>
複数のテーブルを結合する場合、<u>rows</u> の値を掛け合わせることで、全体の検査行数を推定できる。<br>
例えば、テーブルAの <u>rows</u> が100、テーブルBの <u>rows</u> が200の場合、推定検査行数は 100 × 200 = 20,000 となる。<br>
<br>
例を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 # rowsが小さい例 (インデックスを使用)
 EXPLAIN SELECT * FROM users WHERE id = 100;
 # rows: 1
 
 # rowsが大きい例 (フルテーブルスキャン)
 EXPLAIN SELECT * FROM users WHERE status = 'active';
 # rows: 50000 (インデックスがない場合、全行を検査)
 
 # インデックスを追加後
 CREATE INDEX idx_status ON users(status);
 EXPLAIN SELECT * FROM users WHERE status = 'active';
 # rows: 5000 (インデックスにより絞り込まれる)
 </syntaxhighlight>
<br>
==== filtered ====
<u>filtered</u> カラムは、WHERE句の条件によってフィルタリングされる行の割合を示す。<br>
値は0から100のパーセンテージで表示される。<br>
<br>
<u>filtered</u> の値が小さい場合、多くの行が条件でフィルタリングされることを示す。<br>
これは、インデックスが適切に使用されていない、または条件が非効率的である可能性を示唆する。<br>
<br>
例を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 # filteredが高い例 (条件が効率的)
 EXPLAIN SELECT * FROM users WHERE id = 100;
 # filtered: 100.00 (PRIMARY KEYで完全一致)
 
 # filteredが低い例 (条件が非効率的)
 EXPLAIN SELECT * FROM users WHERE status = 'active' AND email LIKE '%@example.com';
 # filtered: 10.00 (LIKE '%...' は非効率的)
 </syntaxhighlight>
<br>
<u>filtered</u> と <u>rows</u> を掛け合わせることで、実際に返される行数の推定値を計算できる。<br>
例えば、<u>rows</u> が1000、<u>filtered</u> が10.00の場合、推定される結果行数は 1000 × 0.10 = 100 となる。<br>
<br>
==== Extra ====
<u>Extra</u> カラムは、クエリ実行に関する追加情報を示す。<br>
パフォーマンスチューニングにおいて、重要な手がかりとなる。<br>
<br>
主要な <u>Extra</u> の値を以下に示す。<br>
<br>
<center>
{| class="wikitable"
|+ 主要なExtra値
! 値 !! 説明 !! パフォーマンス
|-
| Using index || カバリングインデックス (インデックスのみでクエリを満たす) || 非常に良い
|-
| Using where || WHERE句の条件でフィルタリング || 普通
|-
| Using index condition || インデックスコンディションプッシュダウン (ICP) を使用 || 良い
|-
| Using filesort || ファイルソートが必要 (ORDER BY用) || 悪い
|-
| Using temporary || 一時テーブルを使用 || 悪い
|-
| Using join buffer || 結合バッファを使用 || 注意
|-
| Impossible WHERE || WHERE句が常にFALSE || 警告
|-
| Select tables optimized away || <code>COUNT(*)</code> や <code>MIN</code> / <code>MAX</code> の最適化 || 非常に良い
|-
| No tables used || FROMがない、またはFROM DUAL || 普通
|-
| Distinct || DISTINCTの最適化が行われた || 良い
|-
| Using MRR || Multi-Range Read最適化を使用 || 良い
|-
| FirstMatch || FirstMatch準結合戦略を使用 || 良い
|-
| LooseScan || LooseScan準結合戦略を使用 || 良い
|-
| Start temporary, End temporary || Duplicate Weedout準結合戦略を使用 || 普通
|}
</center>
<br>
Using filesort と Using temporary は、パフォーマンス問題の兆候であることが多い。<br>
<br>
===== Using filesort =====
Using filesort は、ORDER BY句の結果をソートするために、ファイルソート (メモリまたはディスク上でのソート) が必要であることを示す。<br>
<br>
ファイルソートを避けるには、ORDER BY句で使用するカラムにインデックスを作成する。<br>
<br>
 <syntaxhighlight lang="mysql">
 # Using filesortが発生する例
 EXPLAIN SELECT * FROM users WHERE status = 'active' ORDER BY created_at DESC;
 # Extra: Using where; Using filesort
 
 # インデックスを追加してUsing filesortを回避
 CREATE INDEX idx_status_created ON users(status, created_at);
 EXPLAIN SELECT * FROM users WHERE status = 'active' ORDER BY created_at DESC;
 # Extra: Using where (Using filesortが消える)
 </syntaxhighlight>
<br>
===== Using temporary =====
Using temporary は、クエリ実行中に一時テーブルが作成されることを示す。<br>
GROUP BY、DISTINCT、UNION、ORDER BY等で一時テーブルが使用される。<br>
<br>
一時テーブルを避けるには、インデックスの最適化、クエリの書き換えを検討する。<br>
<br>
 <syntaxhighlight lang="mysql">
 # Using temporaryが発生する例
 EXPLAIN SELECT DISTINCT status FROM users ORDER BY created_at;
 # Extra: Using temporary; Using filesort
 
 # インデックスを使用してUsing temporaryを回避
 CREATE INDEX idx_status ON users(status);
 EXPLAIN SELECT status FROM users GROUP BY status;
 # Extra: Using index (一時テーブルが不要)
 </syntaxhighlight>
<br>
===== Using index (カバリングインデックス) =====
Using indexは、クエリがインデックスのみで満たされることを示す。(カバリングインデックス)<br>
テーブルデータへのアクセスが不要なため、非常に高速である。<br>
<br>
 <syntaxhighlight lang="mysql">
 # カバリングインデックスの例
 CREATE INDEX idx_status_email ON users(status, email);
 
 EXPLAIN SELECT status, email FROM users WHERE status = 'active';
 # Extra: Using index (テーブルデータを読まずにインデックスのみで完結)
 </syntaxhighlight>
<br>
===== Using index condition (ICP) =====
Using index conditionは、インデックスコンディションプッシュダウン (Index Condition Pushdown, ICP) が使用されていることを示す。<br>
ICPは、WHERE句の条件をストレージエンジン層で評価することで、不要なテーブルアクセスを削減する最適化である。<br>
<br>
 <syntaxhighlight lang="mysql">
 # ICPが使用される例
 CREATE INDEX idx_name ON users(name);
 
 EXPLAIN SELECT * FROM users WHERE name LIKE 'John%' AND age > 30;
 # Extra: Using index condition
 </syntaxhighlight>
<br>
ICPにより、インデックスで絞り込んだ後、テーブルアクセス前にWHERE条件を評価できる。<br>
<br><br>

== EXPLAIN FORMAT=JSON ==
==== JSON形式の構文 ====
<code>EXPLAIN FORMAT=JSON</code> は、実行計画をJSON形式で出力する。<br>
表形式よりも詳細な情報 (コスト情報、実行順序等) を含む。<br>
<br>
 <syntaxhighlight lang="mysql">
 EXPLAIN FORMAT=JSON
 SELECT u.name, o.order_date
    FROM users u
    INNER JOIN orders o ON u.id = o.user_id
    WHERE u.status = 'active';
 </syntaxhighlight>
<br>
JSON形式の出力例を以下に示す。(一部抜粋)<br>
<br>
 <syntaxhighlight lang="json">
 {
   "query_block": {
     "select_id": 1,
     "cost_info": {
       "query_cost": "1234.56"
     },
     "nested_loop": [
       {
         "table": {
           "table_name": "u",
           "access_type": "ref",
           "possible_keys": ["idx_status"],
           "key": "idx_status",
           "used_key_parts": ["status"],
           "key_length": "50",
           "ref": ["const"],
           "rows_examined_per_scan": 5000,
           "rows_produced_per_join": 5000,
           "filtered": "100.00",
           "cost_info": {
             "read_cost": "100.00",
             "eval_cost": "500.00",
             "prefix_cost": "600.00",
             "data_read_per_join": "1M"
           },
           "used_columns": ["id", "name", "status"]
         }
       },
       {
         "table": {
           "table_name": "o",
           "access_type": "ref",
           "possible_keys": ["idx_user_id"],
           "key": "idx_user_id",
           "used_key_parts": ["user_id"],
           "key_length": "4",
           "ref": ["database.u.id"],
           "rows_examined_per_scan": 10,
           "rows_produced_per_join": 50000,
           "filtered": "100.00",
           "cost_info": {
             "read_cost": "500.00",
             "eval_cost": "5000.00",
             "prefix_cost": "6100.00",
             "data_read_per_join": "2M"
           },
           "used_columns": ["id", "user_id", "order_date"]
         }
       }
     ]
   }
 }
 </syntaxhighlight>
<br>
==== 主要なJSONフィールド ====
JSON形式の出力に含まれる主要なフィールドを以下に示す。<br>
<br>
<center>
{| class="wikitable"
|-
! フィールド !! 説明
|-
| <code>query_block</code> || クエリブロック全体の情報
|-
| <code>cost_info</code> || コスト情報 (クエリ全体のコスト)
|-
| <code>nested_loop</code> || ネストループ結合の配列 (テーブルの処理順序)
|-
| <code>table_name</code> || テーブル名
|-
| <code>access_type</code> || アクセスタイプ (ref, range, ALL等)
|-
| <code>possible_keys</code> || 使用可能なインデックスの配列
|-
| <code>key</code> || 実際に使用されたインデックス
|-
| <code>used_key_parts</code> || 使用されたインデックスの部分 (複合インデックスの場合)
|-
| <code>rows_examined_per_scan</code> || スキャンごとに検査される行数
|-
| <code>rows_produced_per_join</code> || 結合後に生成される行数
|-
| <code>filtered</code> || フィルタリング割合
|-
| <code>read_cost</code> || 読み取りコスト
|-
| <code>eval_cost</code> || 評価コスト
|-
| <code>prefix_cost</code> || 累積コスト
|-
| <code>used_columns</code> || 使用されるカラムの配列
|}
</center>
<br>
==== コスト情報の読み方 ====
JSON形式の <code>cost_info</code> には、以下のコスト情報が含まれる。<br>
<br>
<center>
{| class="wikitable"
|-
! フィールド !! 説明
|-
| <code>read_cost</code> || データを読み取るためのコスト
|-
| <code>eval_cost</code> || 行を評価するためのコスト (rows × 0.1)
|-
| <code>prefix_cost</code> || このテーブルまでの累積コスト (read_cost + eval_cost + 前のテーブルのコスト)
|-
| <code>query_cost</code> || クエリ全体のコスト
|}
</center>
<br>
コスト値は相対的な値であり、絶対的な実行時間ではない。<br>
異なるクエリのコストを比較することで、どちらが効率的かを判断できる。<br>
<br>
コスト値が大きいテーブルは、ボトルネックになっている可能性がある。<br>
インデックスの追加、クエリの書き換えを検討する。<br>
<br><br>

== EXPLAIN FORMAT=TREE ==
==== TREE形式の構文 ====
<code>EXPLAIN FORMAT=TREE</code> は、実行計画をツリー形式で表示する。(MySQL 8.0.16以降)<br>
クエリの処理フローを視覚的に理解しやすい形式である。<br>
<br>
 <syntaxhighlight lang="mysql">
 EXPLAIN FORMAT=TREE
 SELECT u.name, o.order_date
    FROM users u
    INNER JOIN orders o ON u.id = o.user_id
    WHERE u.status = 'active'
    ORDER BY o.order_date DESC;
 </syntaxhighlight>
<br>
TREE形式の出力例を以下に示す。<br>
<br>
 -> Nested loop inner join  (cost=6100.00 rows=50000)
    -> Filter: (u.status = 'active')  (cost=600.00 rows=5000)
       -> Index lookup on u using idx_status (status='active')  (cost=600.00 rows=5000)
    -> Index lookup on o using idx_user_id (user_id=u.id)  (cost=0.25 rows=10)

 -> Sort: o.order_date DESC  (cost=6100.00 rows=50000)
<br>
==== TREE形式の読み方 ====
TREE形式は、上から下、内側から外側の順に処理される。<br>
インデント (矢印 <code>-&gt;</code>) が深いほど、内側 (先に実行される) の処理である。<br>
<br>
TREE形式の主要な要素を以下に示す。<br>
<br>
<center>
{| class="wikitable"
|-
! 要素 !! 説明
|-
| <code>Nested loop inner join</code> || ネストループ内部結合
|-
| <code>Filter</code> || フィルタリング条件
|-
| <code>Index lookup</code> || インデックス検索
|-
| <code>Table scan</code> || フルテーブルスキャン
|-
| <code>Sort</code> || ソート処理
|-
| <code>Aggregate</code> || 集約処理 (GROUP BY, COUNT等)
|-
| <code>Materialize</code> || マテリアライズ (サブクエリの結果を一時テーブルに保存)
|}
</center>
<br>
コストと推定行数が各ステップに表示されるため、ボトルネックを特定しやすい。<br>
<br>
例を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 EXPLAIN FORMAT=TREE
 SELECT status, COUNT(*) AS cnt
    FROM users
    WHERE created_at >= '2024-01-01'
    GROUP BY status;
 </syntaxhighlight>
<br>
出力例を以下に示す。<br>
<br>
 -> Group aggregate: count(0)  (cost=2000.00 rows=10)
    -> Filter: (users.created_at >= '2024-01-01')  (cost=1000.00 rows=5000)
       -> Index range scan on users using idx_created_at  (cost=1000.00 rows=10000)
<br>
この例では、以下の順序で処理される。<br>
# <code>idx_created_at</code> インデックスで範囲スキャンする。(10000行を検査)
# <code>created_at &gt;= '2024-01-01'</code> でフィルタリング。(5000行に絞り込み)
# <code>status</code> でグループ化して <code>COUNT(*)</code> を集約。(10グループ)
<br><br>

== EXPLAIN ANALYZE ==
==== EXPLAIN ANALYZEの構文 ====
<code>EXPLAIN ANALYZE</code> は、クエリを実際に実行して、推定値と実測値を比較した結果を表示する。(MySQL 8.0.18以降)<br>
<br>
<u>※重要</u><br>
<u><code>EXPLAIN ANALYZE</code> は実際にクエリを実行するため、SELECT文以外 (UPDATE, DELETE等) に使用する場合は注意が必要である。</u><br>
<br>
 <syntaxhighlight lang="mysql">
 EXPLAIN ANALYZE
 SELECT u.name, o.order_date
    FROM users u
    INNER JOIN orders o ON u.id = o.user_id
    WHERE u.status = 'active'
    ORDER BY o.order_date DESC
    LIMIT 100;
 </syntaxhighlight>
<br>
<code>EXPLAIN ANALYZE</code> の出力例を以下に示す。<br>
<br>
 -> Limit: 100 row(s)  (actual time=0.123..5.678 rows=100 loops=1)
    -> Sort: o.order_date DESC, limit input to 100 row(s) per chunk  (actual time=5.567..5.678 rows=100 loops=1)
       -> Nested loop inner join  (cost=6100.00 rows=50000) (actual time=0.089..4.567 rows=45678 loops=1)
          -> Filter: (u.status = 'active')  (cost=600.00 rows=5000) (actual time=0.045..1.234 rows=4876 loops=1)
             -> Index lookup on u using idx_status (status='active')  (cost=600.00 rows=5000) (actual time=0.034..0.987 rows=4876 loops=1)
          -> Index lookup on o using idx_user_id (user_id=u.id)  (cost=0.25 rows=10) (actual time=0.002..0.003 rows=9 loops=4876)
<br>
==== 出力の読み方 ====
<code>EXPLAIN ANALYZE</code> の出力には、以下に示す情報が含まれる。<br>
<br>
<center>
{| class="wikitable"
|-
! 要素 !! 説明
|-
| <code>cost=...</code> || 推定コスト (オプティマイザの見積もり)
|-
| <code>rows=...</code> || 推定行数 (オプティマイザの見積もり)
|-
| <code>actual time=...</code> || 実際の実行時間 (ミリ秒) - 最初の行を取得するまでの時間..最後の行を取得するまでの時間
|-
| <code>rows=...</code> (actual) || 実際に返された行数
|-
| <code>loops=...</code> || 処理がループした回数
|}
</center>
<br>
<code>actual time</code> の形式は <u>開始時間..終了時間</u> である。<br>
例えば、<u>actual time=0.045..1.234</u> は、最初の行を取得するのに 0.045ミリ秒、全ての行を取得するのに 1.234ミリ秒掛かったことを示す。<br>
<br>
==== 推定値と実測値の差異 ====
<code>EXPLAIN ANALYZE</code> により、オプティマイザの推定値 (rows, cost) と実測値 (actual rows, actual time) を比較できる。<br>
<br>
推定値と実測値に大きな差がある場合、以下の原因が考えられる。<br>
* テーブル統計が古い
*: ANALYZE TABLE文を実行して統計を更新する。
* データ分布が偏っている
*: ヒストグラム統計を収集する。(MySQL 8.0以降)
* WHERE句の条件が複雑
*: オプティマイザが正確に推定できない。
<br>
推定値と実測値の差異を解消する方法を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 # テーブル統計を更新
 ANALYZE TABLE users;
 ANALYZE TABLE orders;
 
 # ヒストグラム統計を収集 (MySQL 8.0以降)
 ANALYZE TABLE users UPDATE HISTOGRAM ON status, created_at;
 ANALYZE TABLE orders UPDATE HISTOGRAM ON user_id, order_date;
 
 # 統計更新後に再度EXPLAIN ANALYZEを実行
 EXPLAIN ANALYZE
 SELECT u.name, o.order_date
    FROM users u
    INNER JOIN orders o ON u.id = o.user_id
    WHERE u.status = 'active';
 </syntaxhighlight>
<br>
推定行数が5000だが実際には50しか返されない場合、インデックスの選択が最適化される可能性がある。<br>
<br><br>

== 実践的な読み方 ==
==== 確認すべきポイント ====
<code>EXPLAIN</code> の出力を分析する際、以下に示すポイントを確認する。<br>
<br>
これらのポイントを確認することにより、パフォーマンスボトルネックを特定できる。<br>
<br>
<center>
{| class="wikitable"
|-
! 確認項目 !! 説明
|-
| <code>type</code> カラムを確認 || <code>ALL</code> (フルテーブルスキャン) や <code>index</code> (インデックスフルスキャン) は要注意。<br>理想は <code>const</code>、<code>eq_ref</code>、<code>ref</code>、<code>range</code>。
|-
| <code>key</code> カラムを確認 || <code>NULL</code> の場合、インデックスが使用されていない。インデックスの追加を検討。
|-
| <code>rows</code> カラムを確認 || 大きな値の場合、多くの行を検査している。インデックスで絞り込めないか確認。
|-
| <code>Extra</code> カラムを確認 || <code>Using filesort</code>、<code>Using temporary</code> はパフォーマンス問題の兆候。<br><code>Using index</code> (カバリングインデックス) は理想的。
|-
| 結合の順序を確認 || 小さいテーブルを先に処理しているか (ドライビングテーブルの選択)。
|-
| フィルタリング割合を確認 || <code>filtered</code> が低い場合、多くの行が無駄に処理されている。
|}
</center>
<br>
==== よくあるパフォーマンス問題の特定 ====
===== フルテーブルスキャン =====
<u>type</u> が <u>ALL</u> の場合、フルテーブルスキャンが発生している。<br>
テーブルの全行を読み取るため、テーブルサイズが大きい場合は非常に遅い。<br>
<br>
フルテーブルスキャンが発生する原因を以下に示す。<br>
* WHERE句で使用するカラムにインデックスがない。
* WHERE句で関数を使用している。
*: 例: <u>WHERE YEAR(created_at) = 2024</u>
* WHERE句で否定条件を使用している。
*: 例: <u>WHERE status != 'active'</u>
* インデックスが存在するが選択性が低い。(カーディナリティが低い)
<br>
対策を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 # 問題: フルテーブルスキャン
 EXPLAIN SELECT * FROM users WHERE status = 'active';
 # type: ALL
 # rows: 1000000
 
 # 対策: インデックスを追加
 CREATE INDEX idx_status ON users(status);
 
 # 改善後
 EXPLAIN SELECT * FROM users WHERE status = 'active';
 # type: ref
 # key: idx_status
 # rows: 50000
 </syntaxhighlight>
<br>
===== filesortの発生 =====
<u>Extra</u> に <u>Using filesort</u> が表示される場合、ORDER BY句の結果をソートするために、ファイルソート処理が必要である。<br>
<br>
ファイルソートは、メモリ (<code>sort_buffer_size</code>) または ディスク上で行われる。<br>
ソート対象のデータが大きい場合、パフォーマンスが大幅に低下する。<br>
<br>
filesortを回避する方法を以下に示す。<br>
* ORDER BY句で使用するカラムにインデックスを作成
* 複合インデックスの順序をORDER BY句と一致させる
* WHERE句とORDER BY句の両方を考慮した複合インデックスを作成
<br>
対策を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 # 問題: Using filesort
 EXPLAIN SELECT * FROM users WHERE status = 'active' ORDER BY created_at DESC;
 # Extra: Using where; Using filesort
 
 # 対策: 複合インデックスを作成 (status, created_at)
 CREATE INDEX idx_status_created ON users(status, created_at);
 
 # 改善後
 EXPLAIN SELECT * FROM users WHERE status = 'active' ORDER BY created_at DESC;
 # Extra: Using where
 # (Using filesortが消える)
 </syntaxhighlight>
<br>
<u>※注意</u><br>
<u>複合インデックスの順序は重要である。</u><br>
<u><code>WHERE status = 'active' ORDER BY created_at</code> の場合、<code>idx_status_created (status, created_at)</code> が適切である。</u><br>
<u>逆の順序 <code>idx_created_status (created_at, status)</code> では、filesortは回避できない。</u><br>
<br>
===== 一時テーブルの使用 =====
<u>Extra</u> に <u>Using temporary</u> が表示される場合、一時テーブルが作成される。<br>
一時テーブルは、メモリ (<code>tmp_table_size</code>, <code>max_heap_table_size</code>) または ディスク上に作成される。<br>
<br>
一時テーブルが作成される主なケースを以下に示す。<br>
* GROUP BY句とORDER BY句で異なるカラムを使用
* DISTINCTとORDER BY句を併用
* UNION (UNION ALLは一時テーブルを使用しない)
* 派生テーブル (FROM句のサブクエリ)
<br>
対策を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 # 問題: Using temporary
 EXPLAIN SELECT DISTINCT status FROM users ORDER BY created_at;
 # Extra: Using temporary; Using filesort
 
 # 対策1: DISTINCTをGROUP BYに書き換え、インデックスを活用
 CREATE INDEX idx_status_created ON users(status, created_at);
 EXPLAIN SELECT status FROM users GROUP BY status ORDER BY MIN(created_at);
 # Extra: Using index (一時テーブルが不要)
 
 # 対策2: サブクエリを結合に書き換え
 # 問題
 EXPLAIN
 SELECT * FROM users
    WHERE id IN (SELECT DISTINCT user_id FROM orders);
 # Extra: Using temporary
 
 # 改善
 EXPLAIN
 SELECT DISTINCT u.* FROM users u
    INNER JOIN orders o ON u.id = o.user_id;
 # (インデックスがあれば一時テーブルを回避できる場合がある)
 </syntaxhighlight>
<br>
===== インデックスが使用されないケース =====
インデックスが存在するにもかかわらず、使用されない場合がある。<br>
<br>
主な原因を以下に示す。<br>
<br>
<center>
{| class="wikitable"
|-
! 原因 !! 説明 !! 対策
|-
| WHERE句で関数を使用 || <u>WHERE YEAR(created_at) = 2024</u> は、<u>created_at</u> のインデックスを使用できない。 || <u>WHERE created_at BETWEEN '2024-01-01' AND '2024-12-31'</u> に書き換える。
|-
| 暗黙的な型変換 || <u>WHERE user_id = '100'</u> (<u>user_id</u> がINT型の場合) || 正しい型で条件を指定する。<br><u>WHERE user_id = 100</u>
|-
| LIKE句の前方一致以外 || <u>WHERE name LIKE '%Smith'</u> や <u>WHERE name LIKE '%John%'</u> はインデックスを使用できない。 || <u>WHERE name LIKE 'Smith%'</u> (前方一致) に書き換える。
|-
| OR条件で片方のカラムにインデックスがない || <u>WHERE status = 'active' OR role = 'admin'</u> (<u>role</u> にインデックスがない場合) || 両方のカラムにインデックスを作成するか、UNION句に書き換える。
|-
| 複合インデックスの順序が一致しない || インデックスが <u>idx_abc (a, b, c)</u> の場合、<u>WHERE b = 10</u> はインデックスを使用できない。 || <u>WHERE a = 1 AND b = 10</u> のように、先頭カラムから順に条件を指定する。
|}
</center>
<br>
* 関数を使用している例と対策
*: <syntaxhighlight lang="mysql">
 # 問題: 関数を使用しているためインデックスが使用されない
 EXPLAIN SELECT * FROM users WHERE YEAR(created_at) = 2024;
 # type: ALL
 # key: NULL
 
 # 対策: 関数を使わずに範囲条件に書き換え
 EXPLAIN SELECT * FROM users
    WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';
 # type: range
 # key: idx_created_at
 </syntaxhighlight>
*: <br>
* OR条件の例と対策
*: <syntaxhighlight lang="mysql">
 # 問題: OR条件で片方にインデックスがない
 EXPLAIN SELECT * FROM users WHERE status = 'active' OR role = 'admin';
 # type: ALL (インデックスが使用されない)
 
 # 対策1: 両方のカラムにインデックスを作成
 CREATE INDEX idx_status ON users(status);
 CREATE INDEX idx_role ON users(role);
 EXPLAIN SELECT * FROM users WHERE status = 'active' OR role = 'admin';
 # type: index_merge (インデックスマージが使用される)
 # Extra: Using union(idx_status,idx_role)
 
 # 対策2: UNION句に書き換え
 EXPLAIN
 SELECT * FROM users WHERE status = 'active'
 UNION
 SELECT * FROM users WHERE role = 'admin';
 </syntaxhighlight>
<br>
==== EXPLAIN結果に基づく最適化の流れ ====
<code>EXPLAIN</code> の結果を基に、クエリを最適化する一般的な流れを以下に示す。<br>
<br>
# <code>EXPLAIN</code> を実行してクエリの実行計画を確認
#* <code>type</code>、<code>key</code>、<code>rows</code>、<code>Extra</code> を重点的に確認。
# ボトルネックを特定
#* フルテーブルスキャン (<code>type: ALL</code>)
#* ファイルソート (<code>Extra: Using filesort</code>)
#* 一時テーブル (<code>Extra: Using temporary</code>)
#* インデックス未使用 (<code>key: NULL</code>)
#* 大量の行検査 (<code>rows: 大きな値</code>)
# インデックスの追加または最適化
#* WHERE句、JOIN句、ORDER BY句で使用するカラムにインデックスを作成。
#* 複合インデックスの順序を最適化。
#* カバリングインデックスを検討。
# クエリの書き換え
#* 関数を削除または書き換え。
#* サブクエリをJOIN句に書き換え。
#* OR条件をUNION句に書き換え。
#* DISTINCT、GROUP BYの最適化。
# テーブル統計の更新
#* ANALYZE TABLE文を実行して統計を最新に保つ。
#* ヒストグラム統計を収集 (MySQL 8.0以降)。
# 再度 <code>EXPLAIN</code> を実行して改善を確認
#* <code>type</code> が改善されているか (<code>ALL</code> → <code>ref</code> / <code>range</code>)。
#* <code>rows</code> が減少しているか。
#* <code>Extra</code> から <code>Using filesort</code> / <code>Using temporary</code> が消えているか。
# 必要に応じて <code>EXPLAIN ANALYZE</code> で実測値を確認
#* 推定値と実測値の差異を確認。
#* 実行時間を測定。
<br>
最適化の例を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 # Step 1: 最初のクエリ
 EXPLAIN
 SELECT u.name, COUNT(*) AS order_count
    FROM users u
    INNER JOIN orders o ON u.id = o.user_id
    WHERE u.status = 'active'
       AND o.order_date >= '2024-01-01'
    GROUP BY u.id
    ORDER BY order_count DESC;
 
 # 結果: type: ALL, key: NULL, Extra: Using temporary; Using filesort
 
 # Step 2: インデックスを追加
 CREATE INDEX idx_status ON users(status);
 CREATE INDEX idx_user_order_date ON orders(user_id, order_date);
 
 # Step 3: テーブル統計を更新
 ANALYZE TABLE users;
 ANALYZE TABLE orders;
 
 # Step 4: 再度EXPLAINを実行
 EXPLAIN
 SELECT u.name, COUNT(*) AS order_count
    FROM users u
    INNER JOIN orders o ON u.id = o.user_id
    WHERE u.status = 'active'
       AND o.order_date >= '2024-01-01'
    GROUP BY u.id
    ORDER BY order_count DESC;
 
 # 結果: type: ref/range, key: idx_status/idx_user_order_date
 # rowsが大幅に減少、Using temporaryは残るがパフォーマンスは改善
 
 # Step 5: EXPLAIN ANALYZEで実測
 EXPLAIN ANALYZE
 SELECT u.name, COUNT(*) AS order_count
    FROM users u
    INNER JOIN orders o ON u.id = o.user_id
    WHERE u.status = 'active'
       AND o.order_date >= '2024-01-01'
    GROUP BY u.id
    ORDER BY order_count DESC;
 
 # 実行時間が大幅に短縮されたことを確認
 </syntaxhighlight>
<br>
<code>EXPLAIN</code> を活用することにより、クエリのパフォーマンス問題を体系的に分析・解決できる。<br>
<br>
[[MySQL - SELECT]]ページのパフォーマンス最適化セクションも参照して、さらなる最適化を行うことを推奨する。<br>
<br><br>


{{#seo:
|title={{PAGENAME}} : Exploring Electronics and SUSE Linux | MochiuWiki
|keywords=MochiuWiki,Mochiu,Wiki,Mochiu Wiki,Electric Circuit,Electric,pcb,Mathematics,AVR,TI,STMicro,AVR,ATmega,MSP430,STM,Arduino,Xilinx,FPGA,Verilog,HDL,PinePhone,Pine Phone,Raspberry,Raspberry Pi,C,C++,C#,Qt,Qml,MFC,Shell,Bash,Zsh,Fish,SUSE,SLE,Suse Enterprise,Suse Linux,openSUSE,open SUSE,Leap,Linux,uCLnux,電気回路,電子回路,基板,プリント基板
|description={{PAGENAME}} - 電子回路とSUSE Linuxに関する情報 | This page is {{PAGENAME}} in our wiki about electronic circuits and SUSE Linux
|image=/resources/assets/MochiuLogo_Single_Blue.png
}}

__FORCETOC__
[[カテゴリ:MySQL]]