MySQL入門：explainの出力について -vol.3-

今日は、mysqlのexplainについてご紹介します。

背景

mysqlのexplainを調査したの内容を備忘として残しました。

explainの一次情報には下記を利用しています。

EXPLAIN出力のExtra列

EXPLAIN出力のExtra列には、MySQLがクエリを解決する方法に関する追加情報が含まれています。以下のリストは、この列に表示される可能性のある値を説明します。各アイテムは、JSON形式の出力に対してExtra値を表示するプロパティを示します。これらのいくつかについては、特定のプロパティがあります。その他はメッセージプロパティのテキストとして表示されます。

クエリをできるだけ高速にする場合、Extra列の値がUsing filesortおよびUsing temporary、またはJSON形式のEXPLAIN出力ではusing_filesortおよびusing_temporary_tableプロパティがtrueに等しいことに注意してください。

Backward index scan (JSON: backward_index_scan)

オプティマイザはInnoDBテーブルの降順インデックスを使用できる状態です。Using indexと一緒に表示されます。

Child of ‘table’ pushed join@1 (JSON: message text)

このテーブルは、NDB Clusterでプッシュダウン結合が有効な場合、結合をNDBカーネルにプッシュダウンできるテーブルの子として参照されます。

const row not found (JSON property: const_row_not_found)

SELECT … FROM tbl_nameのようなクエリの場合、テーブルが空でした。

Deleting all rows (JSON property: message)

DELETEの場合、一部のストレージエンジン（MyISAMなど）は、テーブルのすべての行を簡単かつ高速に削除するためのハンドラメソッドをサポートしています。このExtra値は、エンジンがこの最適化を使用する場合に表示されます。

Distinct (JSON property: distinct)

MySQLは一意の値を探しており、現在の行の組み合わせについてさらに行を検索するのを停止します。

FirstMatch(tbl_name) (JSON property: first_match)

セミジョインのFirstMatch join ショートカット戦略がtbl_nameに使用されています。

Full scan on NULL key (JSON property: message)

オプティマイザがインデックスルックアップアクセス方法を使用できない場合のサブクエリ最適化のフォールバック戦略として発生します。

Impossible HAVING (JSON property: message)

HAVING句は常にfalseであり、行を選択できません。

Impossible WHERE (JSON property: message)

WHERE句が常にfalseであり、行を選択できません。

Impossible WHERE noticed after reading const tables (JSON property: message)

MySQLはすべてのconst（およびsystem）テーブルを読み取り、WHERE句が常にfalseであることに気付きました。

LooseScan(m..n) (JSON property: message)

セミジョインのLooseScan戦略が使用されています。mとnはキー部分の番号です。

No matching min/max row (JSON property: message)

クエリ（例: SELECT MIN(…) FROM … WHERE condition）の条件に一致する行がない。

no matching row in const table (JSON property: message)

結合を伴うクエリの場合、空のテーブルまたは一意のインデックス条件を満たす行がないテーブルがありました。

No matching rows after partition pruning (JSON property: message)

DELETEまたはUPDATEの場合、パーティションの剪定後に削除または更新する対象が見つからなかったため、Impossible WHEREのSELECTステートメントに対する意味に類似しています。

No tables used (JSON property: message)

クエリにFROM句が含まれていないか、FROM DUAL句が含まれています。INSERTまたはREPLACEステートメントの場合、SELECT部分が存在しない場合にEXPLAINがこの値を表示します。たとえば、EXPLAIN INSERT INTO t VALUES(10)のように表示されます。これは、EXPLAIN INSERT INTO t SELECT 10 FROM DUALと同等です。

Not exists (JSON property: message)

MySQLはクエリにLEFT JOIN最適化を適用でき、LEFT JOIN条件に一致する行が見つかった後、前の行の組み合わせのこのテーブルでさらに行を調べなくても済みます。この方法で最適化できるクエリの例は以下の通りです。

SELECT * FROM t1 LEFT JOIN t2 ON t1.id=t2.id
  WHERE t2.id IS NULL;

t2.idがNOT NULLであると仮定した場合、MySQLはt1をスキャンし、t1.idの値を使用してt2の行を検索します。MySQLはt2で一致する行を見つけると、t2.idは決してNULLにならないことを知っているため、t2内の同じid値を持つ残りの行をスキャンする必要はありません。言い換えれば、t1の各行に対して、MySQLはt2での一致する行がいくつあっても、たった1回の検索だけで済みます。

MySQL 8.0.17以降、この最適化は、NOT IN（subquery）またはNOT EXISTS（subquery）の形式のWHERE条件が内部的にアンチジョインに変換されたことを示すこともあります。この変換により、サブクエリが除去され、そのテーブルがトップクエリの計画に組み込まれ、コストの計画が改善されます。セミジョインとアンチジョインを組み合わせることで、オプティマイザは実行計画内でテーブルの順序をより自由に変更でき、一部のケースでより高速な計画が得られます。

特定のクエリでアンチジョイン変換が適用されたかどうかは、EXPLAIN文を実行した後のSHOW WARNINGSの出力のMessage列を確認するか、FORMAT=TREEオプションを使用してEXPLAINの出力を見ることで判別できます。

Plan isn't ready yet (JSON property: none)

この値は、名前付きの接続で実行中のステートメントの実行プランを最適化する進捗が完了していない場合に、EXPLAIN FOR CONNECTIONで発生します。実行プランの出力が複数行から成る場合、最適化プロセスの進行状況に応じて、どれかまたはすべての行がこの「Extra」値を持つ可能性があります。

Range checked for each record (index map: N) (JSON property: message)

MySQLは適切なインデックスを使用できないことを見つけましたが、一部のインデックスは前のテーブルからの列値がわかる後で使用できることがわかりました。前のテーブルの各行の組み合わせごとに、MySQLは行を取得するために範囲またはインデックスマージのアクセス方法を使用できるかどうかをチェックします。これは非常に高速ではありませんが、まったくインデックスのない結合を実行するよりも高速です。前のテーブルのすべての列値が既知であり、定数と考えられています。

インデックスは、テーブルのSHOW INDEXで表示される順序と同じ順序で1から始まる番号が付けられます。インデックスマップの値Nは、どのインデックスが候補となるかを示すビットマスク値です。たとえば、0x19（2進数で11001）の値は、インデックス1、4、および5が考慮されていることを意味します。

Recursive (JSON property: recursive)

これは、再帰的な共通テーブル式の再帰的なSELECT部分に適用される行を示しています。詳細はセクション13.2.20「WITH（共通テーブル式）」を参照してください。

Rematerialize (JSON property: rematerialize)

リマテリアライズ（X、…）は、テーブルTのEXPLAIN行に表示され、XがTの新しい行が読み込まれるとリマテリアライズがトリガーされる任意のラテラル派生テーブルを示します。例：

SELECT
  ...
FROM
  t,
  LATERAL (derived table that refers to t) AS dt
...

派生テーブルの内容は、トップクエリによって新しいtの行が処理されるたびに最新の状態に更新されるため、再マテリアライズされます。

Scanned N databases (JSON property: message)

これは、情報スキーマテーブルのクエリ処理中にサーバーが実行するディレクトリスキャンの回数を示しています。詳細はセクション8.2.3「情報スキーマクエリの最適化」に記載されています。Nの値は0、1、またはすべてであることがあります。

Select tables optimized away (JSON property: message)

最適化プログラムは、1）最大で1行が返されるべきであり、2）この行を生成するために決定論的な行セットを読む必要があることを判断しました。読むべき行が最適化フェーズで読まれる場合（たとえば、インデックス行を読むことによって）、クエリ実行中にはテーブルを読む必要はありません。

最初の条件は、クエリが暗黙的にグループ化されている場合に満たされます（集計関数が含まれているがGROUP BY句が含まれていない場合）。第二の条件は、使用されるインデックスごとに1行の検索が実行される場合に満たされます。読むべき行の数は読むべきインデックスの数を決定します。

次のような暗黙のグループ化されたクエリを考慮してください：

　SELECT MIN(c1), MIN(c2) FROM t1;

仮に、MIN(c1)は1つのインデックス行を読むことで取得でき、MIN(c2)は異なるインデックスから1行を読むことで取得できるとしましょう。つまり、各列c1およびc2に対して、その列がインデックスの最初の列であるインデックスが存在すると仮定します。この場合、2つの確定的な行を読むことで1行が返されます。

この「Extra」値は、読むべき行が確定的でない場合には発生しません。次のクエリを考えてみてください：

SELECT MIN(c2) FROM t1 WHERE c1 <= 10;

ここで、(c1, c2)がカバリングインデックスであると仮定します。このインデックスを使用して、c1が10以下のすべての行をスキャンして最小のc2の値を見つける必要があります。対照的に、次のクエリを考えてみてください：

SELECT MIN(c2) FROM t1 WHERE c1 = 10;

この場合、c1 = 10の最初のインデックス行には最小のc2の値が含まれています。返される行を生成するためには、1行だけ読む必要があります。

テーブルごとに正確な行数を維持するストレージエンジン（MyISAMなど、ただしInnoDBは該当しません）の場合、この「Extra」値は、WHERE句が欠落しているか常にtrueであり、GROUP BY句がないCOUNT(*)クエリに対して発生する可能性があります。これは、ストレージエンジンが確定的な行数を読むかどうかに影響を与える暗黙のグループ化されたクエリの一例です。

Skip_open_table, Open_frm_only, Open_full_table (JSON property: message)

これらの値は、INFORMATION_SCHEMAテーブルに対するファイルオープニングの最適化を示しています。

Skip_open_table：テーブルファイルを開く必要はありません。情報はデータディクショナリからすでに利用可能です。

Open_frm_only：テーブル情報を取得するにはデータディクショナリのみを読み取る必要があります。

Open_full_table：最適化されていない情報の検索。テーブル情報はデータディクショナリから読み取り、テーブルファイルから読み取る必要があります。

Start temporary, End temporary (JSON property: message)

これは、セミジョインのDuplicate Weedout戦略での一時テーブルの使用を示しています。

unique row not found (JSON property: message)

このメッセージは、テーブルの一意なインデックスまたは主キーに対する条件を満たす行がクエリによって見つからなかった場合に表示されます。たとえば、SELECT … FROM tbl_name のようなクエリが該当します。

Using filesort (JSON property: using_filesort)

MySQLは、行を並べ替えた順序で取得する方法を見つけるために追加のステップを行う必要があります。並べ替えは、結合タイプに従ってすべての行をスキャンし、WHERE句に一致するすべての行に対してソートキーと行へのポインタを保存して行われます。その後、キーはソートされ、行はソート順に取得されます。

Using index (JSON property: using_index)

この戦略は、クエリが単一のインデックスの一部である列のみを使用する場合に、インデックスツリー内の情報のみを使用してテーブルから列情報を取得します。この戦略は、クエリで単一のインデックスの列のみを使用する場合に使用できます。

Using index condition (JSON property: using_index_condition)

テーブルはインデックスのタプルにアクセスし、まずテストして、全体のテーブル行を読む必要があるかどうかを判断します。この方法により、必要でない限り、インデックス情報を使用して全体のテーブル行の読み取りを遅延させることができます。

Using index for group-by (JSON property: using_index_for_group_by)

Using index for group-byは、Using indexテーブルアクセスメソッドと似ており、MySQLがGROUP BYまたはDISTINCTクエリのすべての列を実際のテーブルへの追加のディスクアクセスなしに取得するために使用できるインデックスを見つけたことを示します。さらに、インデックスは最も効率的な方法で使用され、各グループごとにわずかなインデックスエントリのみが読み取られます。詳細については、セクション8.2.1.17「GROUP BYの最適化」を参照してください。

Using temporary (JSON property: using_temporary_table)

クエリを解決するために、MySQLは結果を保持するための一時テーブルを作成する必要があります。これは通常、クエリにGROUP BYおよびORDER BY句が含まれ、列が異なる方法でリストされている場合に発生します。

Using where (JSON property: attached_condition)

WHERE句は、次のテーブルと一致する行を制限するために使用されます。特にテーブルからすべての行を取得または調査する意図がない場合、Extraの値がUsing whereでなく、テーブルの結合タイプがALLまたはindexである場合、クエリに何か問題があるかもしれません。

Using where with pushed condition (JSON property: message)

このアイテムはNDBテーブルにのみ適用されます。これは、NDBクラスタが非索引列と定数の間の直接比較の効率を向上させるためにCondition Pushdown最適化を使用していることを意味します。この場合、条件はクラスタのデータノードに「プッシュダウン」され、すべてのデータノードで同時に評価されます。これにより、非一致の行をネットワークを介して送信する必要がなくなり、Condition Pushdownを使用しない場合に比べてクエリを5倍から10倍高速化することができます。

Zero limit (JSON property: message)

このクエリにはLIMIT 0節があり、いかなる行も選択できないことを示します。

おわりに

今日は、mysqlのexplainについてご紹介しました。