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

よっしー

こんにちは。よっしーです(^^)

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

背景
EXPLAIN出力のtype列
おわりに

背景

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

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

fw_error_www

EXPLAIN出力のtype列

EXPLAIN出力のtype列は、テーブルの結合方法を説明します。JSON形式の出力では、これらはaccess_typeプロパティの値として見つかります。以下のリストは、最適な結合タイプから最悪のタイプまで順に説明します：

system

テーブルには1行しかありません（＝システムテーブル）。これはconst結合タイプの特殊なケースです。

const

テーブルには最大で1つの一致する行しかありません。この行はクエリの開始時に読み込まれます。1行しかないため、この行の列からの値はオプティマイザの残りによって定数と見なすことができます。constテーブルは非常に高速です。なぜなら、1度しか読み込まれないからです。

constは、PRIMARY KEYまたはUNIQUEインデックスのすべての部分を定数値と比較する場合に使用されます。次のクエリでは、tbl_nameはconstテーブルとして使用できます：

SELECT * FROM tbl_name WHERE primary_key=1;

SELECT * FROM tbl_name
  WHERE primary_key_part1=1 AND primary_key_part2=2;

eq_ref

このテーブルからは、前のテーブルの各組み合わせごとに1行が読み込まれます。システムおよびconstタイプ以外のものでは、これが最適な結合タイプです。これは、結合にインデックスのすべての部分が使用され、インデックスがPRIMARY KEYまたはUNIQUE NOT NULLインデックスの場合に使用されます。

eq_refは、=演算子を使用して比較されるインデックス化された列に使用できます。比較値は定数またはこのテーブルの前に読み取られるテーブルの列を使用する式であることがあります。以下の例では、MySQLはeq_ref結合を使用してref_tableを処理できます：

SELECT * FROM ref_table,other_table
  WHERE ref_table.key_column=other_table.column;

SELECT * FROM ref_table,other_table
  WHERE ref_table.key_column_part1=other_table.column
  AND ref_table.key_column_part2=1;

ref

前のテーブルの各組み合わせごとに、このテーブルから一致するインデックス値を持つすべての行が読み込まれます。refは、結合がキーの左端のプレフィックスのみを使用する場合、またはキーがPRIMARY KEYまたはUNIQUEインデックスでない場合（つまり、結合がキーの値に基づいて単一の行を選択できない場合）に使用されます。使用されるキーがわずかな行にのみ一致する場合、これは良い結合タイプです。

refは、=演算子または<=>演算子を使用して比較されるインデックス化された列に使用できます。以下の例では、MySQLはref結合を使用してref_tableを処理できます：

SELECT * FROM ref_table WHERE key_column=expr;

SELECT * FROM ref_table,other_table
  WHERE ref_table.key_column=other_table.column;

SELECT * FROM ref_table,other_table
  WHERE ref_table.key_column_part1=other_table.column
  AND ref_table.key_column_part2=1;

fulltext

FULLTEXTインデックスを使用して結合が実行されます。

ref_or_null

この結合タイプは、refに似ていますが、MySQLがNULL値を含む行を追加で検索することを追加しています。この結合タイプの最適化は、サブクエリの解決に最も頻繁に使用されます。以下の例では、MySQLはref_or_null結合を使用してref_tableを処理できます：

SELECT * FROM ref_table
  WHERE key_column=expr OR key_column IS NULL;

index_merge

この結合タイプは、Index Merge最適化が使用されていることを示します。この場合、出力行のkey列には使用されたインデックスのリストが含まれ、key_lenには使用されたインデックスの最長のキー部分のリストが含まれています。

unique_subquery

このタイプは、次の形式の一部のINサブクエリに対してeq_refを置き換えます：

value IN (SELECT primary_key FROM single_table WHERE some_expr)

unique_subquery は、効率を高めるためにサブクエリを完全に置き換える単なるインデックス検索関数です。

index_subquery

この結合タイプはunique_subqueryに似ています。INサブクエリを置き換えますが、以下の形式のサブクエリの非ユニークなインデックスに対して動作します：

value IN (SELECT key_column FROM single_table WHERE some_expr)

range

指定された範囲内の行のみを取得し、インデックスを使用して行を選択します。出力行のkey列は使用されたインデックスを示します。key_lenには使用された最長のキー部分が含まれています。このタイプの場合、ref列はNULLです。

rangeは、キー列が=、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN、LIKE、またはIN()演算子のいずれかを使用して定数と比較される場合に使用できます：

SELECT * FROM tbl_name
  WHERE key_column = 10;

SELECT * FROM tbl_name
  WHERE key_column BETWEEN 10 and 20;

SELECT * FROM tbl_name
  WHERE key_column IN (10,20,30);

SELECT * FROM tbl_name
  WHERE key_part1 = 10 AND key_part2 IN (10,20,30);

index

index結合タイプはALLと同じですが、インデックスツリーがスキャンされます。これは2つの方法で発生します：

クエリがクエリのすべてのデータを満たすために使用でき、カバリングインデックスの場合、インデックスツリーのみがスキャンされます。この場合、Extra列には「Using index」と表示されます。通常、インデックスのみのスキャンは通常テーブルデータよりもインデックスのサイズが小さいため、通常ALLよりも速くなります。
インデックスからデータ行を索引順に検索するために、インデックスから読み取りを使用してフルテーブルスキャンが実行されます。Extra列に「Using index」は表示されません。

MySQLは、クエリで単一のインデックスに含まれる列のみを使用する場合にこの結合タイプを使用できます。

ALL

前のテーブルの組み合わせごとにフルテーブルスキャンが実行されます。これは通常、テーブルがconst以外の最初のテーブルである場合は良くなく、それ以外のすべての場合は通常非常に悪いです。通常、ALLを回避するには、定数値または前のテーブルの列値に基づいてテーブルから行を取得できるようにするインデックスを追加します。