:: DEVELOPER ZONE
MySQL リファレンスマニュアル :: 5.2.1 EXPLAIN 構文（SELECT に関する情報の取得）
- 5.2 SELECT ステートメントおよびその他のクエリの最適化
- 5.2.1 EXPLAIN 構文（SELECT に関する情報の取得）
- 5.2.2 クエリパフォーマンスの推定
- 5.2.3 SELECT クエリの速度
- 5.2.4 MySQL による WHERE 節の最適化
- 5.2.5 MySQL による IS NULL の最適化
- 5.2.6 MySQL による DISTINCT の最適化
- 5.2.7 MySQL による LEFT JOIN と RIGHT JOIN の最適化
- 5.2.8 MySQL による ORDER BY の最適化
- 5.2.9 MySQL による LIMIT の最適化
- 5.2.10 INSERT クエリの速度
- 5.2.11 UPDATE クエリの速度
- 5.2.12 DELETE クエリの速度
- 5.2.13 その他の最適化のヒント
Additional languages
EXPLAIN tbl_name か EXPLAIN SELECT select_options
EXPLAIN tbl_name は、DESCRIBE tbl_name または SHOW COLUMNS FROM tbl_name のシノニムです。
キーワード EXPLAIN を SELECT ステートメントの前に置いた場合、MySQL によってテーブルの結合状況と順序に関する情報が提供され、テーブルの SELECT の処理方法が説明されます。
EXPLAIN を利用すると、より速くレコードを検索する SELECT を得るために、どの時テーブルにインデックスを追加しなければならないかを確認できます。
最適化方法の選択に影響を及ぼすキーの、カーディナリティなどのテーブル統計を更新するために、ANALYZE TABLE を定期的に実行する必要があります。
See 項4.6.2. 「ANALYZE TABLE 構文」。
また、オプティマイザが、テーブルを最適な順序で結合しているかどうかも確認することができます。
オプティマイザが特定の順番で結合を行うように強制するには、SELECT ステートメントに STRAIGHT_JOIN 節を追加します。
非単純結合の場合、EXPLAIN は SELECT ステートメントで使用される各テーブルに関する情報を返します。
テーブルは、読み取られた順序に従って一覧表示されます。
MySQL は、単一スイープ多結合メソッドを使用してすべての結合を解決します。これは、MySQL が最初のテーブルからレコードを読み取ってから、第 2 のテーブル、第 3 のテーブルといった順序で、一致するレコードの検索を行うことを意味します。
すべてのテーブルの処理が終わると、選択したカラムと、さらに一致レコードがあるテーブルが検索されるまでのテーブル一覧のバックトラックが出力されます。
次のレコードはこのテーブルから読み取られ、処理が次のテーブルから続行されます。
MySQL バージョン 4.1 では、EXPLAIN 出力が変更され、UNION ステートメント、サブクエリ、派生テーブルなどの構造での機能が改善されています。最も重要なことは、id と select_type という 2 つの新しいカラムが追加されたことです。
EXPLAIN の出力は、次のカラムで構成されます。
-
idSELECTに割り当てられた ID。クエリ内におけるこのSELECTの順序番号。 -
select_typeSELECT節の種類、次のいずれかが示される。-
SIMPLE単純な
SELECT（UNIONやサブクエリを使用しない）。 -
PRIMARY最外部の
SELECT -
UNIONUNION内の第 2 およびそれ以降のSELECTステートメント。 -
DEPENDENT UNIONUNION内の第 2 およびそれ以降のSELECTステートメント、外側のサブクエリに依存する。 -
SUBQUERYサブクエリ内の第 1
SELECT。 -
DEPENDENT SUBQUERY第 1
SELECT、外側のサブクエリに依存する。 -
DERIVED派生テーブル
SELECT（FROM節内のサブクエリ）。
-
-
table結果を得るために参照するテーブル。
-
type結合型。各結合型を最適なものから順に紹介する。
-
system1 レコードのみで構成されるテーブル（= システムテーブル）。これは、
const結合型の特殊なケースである。 -
constテーブルに、一致するレコードが最大で 1 つあり、クエリの開始時に読み取られる。レコードが 1 つしかないため、このレコードのカラムの値はオプティマイザによって定数と見なされる。
constテーブルは、1 回しか読み取られないため、非常に高速である。constは、PRIMARY/UNIQUEキーを定数と比較する場合に使用される。SELECT * FROM const_table WHERE primary_key=1; SELECT * FROM const_table WHERE primary_key_part1=1 AND primary_key_part2=2;
-
eq_ref前のテーブルのレコードの組み合わせのそれぞれに対して、このテーブルから 1 レコードずつ読み取られる。これは、
const型以外で最適な結合型である。結合でインデックスのすべての部分が使用され、このインデックスがUNIQUEまたはPRIMARY KEYである場合に使用される。=演算子と比較されるインデックスの張られたカラムには、eq_refを使用できる。比較対象のアイテムは定数でも、このテーブル以前に読み取られたテーブルのカラムを使用する式でもかまわない。下記の例では、
ref_tableでeq_refが使用される。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は、インデックスの左端の先頭部分のみが結合で使用される場合、またはインデックスがUNIQUEやPRIMARY KEYではない場合（すなわち、この結合において、インデックス値から1つのレコードをSELECTできない場合）に使用される。この結合型は、使用されるインデックスと一致するレコードが数レコードしかない場合に適している。=演算子と比較されるインデックスが張られたカラムには、refが使用される。下記の例では、
ref_tableでrefが示される。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;
-
ref_or_nullrefと同様だが、NULLを使用したレコードの補足検索も追加で実行される。 See 項5.2.5. 「MySQL によるIS NULLの最適化」。SELECT * FROM ref_table WHERE key_column=expr OR key_column IS NULL;
この結合型の最適化は、MySQL 4.1.1 の新機能で、主としてサブクエリを解決する場合に使用される。
-
rangeインデックスを使用して、一定の範囲にあるレコードのみが取り出される。
keyカラムに使用されるインデックスが示される。key_lenには使用される最長のインデックス部分が記載される。 この型では、refカラムがNULLになる。rangeは、インデックスを張っているカラムが=、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN、およびINを使用して定数と比較される場合に使用される。SELECT * FROM range_table WHERE key_column = 10; SELECT * FROM range_table WHERE key_column BETWEEN 10 and 20; SELECT * FROM range_table WHERE key_column IN (10,20,30); SELECT * FROM range_table WHERE key_part1= 10 and key_part2 IN (10,20,30);
-
indexこれは、インデックスツリーのみがスキャンされる点を除いて
ALLと同じである。一般にインデックスファイルはデータファイルより小さいため、通常はALLより高速である。これは、クエリで 1 インデックスの構成部分であるカラムのみが使用される場合にのみ使用される。
-
ALL前のテーブルのレコードの組み合わせのそれぞれに対して、フルテーブルスキャンが実行される。一般に、テーブルが
constの指定がない第 1 テーブルの場合には適さず、その他の場合はすべて非常に不適である。通常は、さらにインデックスを追加することでALLを回避し、定数値または以前のテーブルのカラム値を基準にレコードを取り出すようにすることができる。
-
-
possible_keyspossible_keysカラムは、このテーブル内のレコードの検索に MySQL で使用可能なインデックスを示す。このカラムはテーブルの順序にはまったく依存しないことに注意する。すなわち、possible_keysのキーの一部は、生成されたテーブルの順序では事実上使用できないことになる。このカラムが
NULLの場合は、対応するインデックスがない。この場合は、WHERE節でインデックス作成に適するカラムを 1 つ以上参照しているかどうかを調べることでクエリのパフォーマンスを改善できる。参照している場合は適切なインデックスを作成し、再度EXPLAINを使用してクエリをチェックする。 See 項6.5.4. 「ALTER TABLE構文」。テーブルにあるインデックスを調べるには
SHOW INDEX FROM tbl_nameを使用する。 -
keykeyカラムは、MySQL が実際に使用を決定したキー（インデックス）を示す。選択されたインデックスがない場合、このキーはNULLになる。MySQL でpossible_keysカラムに記載されたキーが使用されるように強制するには、クエリでUSE KEY/IGNORE KEYを使用する。 See 項6.4.1. 「SELECT構文」。また、テーブルで
myisamchk --analyze（see 項4.5.6.1. 「myisamchk起動構文」）またはANALYZE TABLE（see 項4.6.2. 「ANALYZE TABLE構文」）を実行することも、オプティマイザでより適したインデックスを選択する際に役立つ。 -
key_lenkey_lenカラムは、MySQL が使用を決定したキーの長さを示す。keyがNULLの場合、この長さはNULLになる。これによって、複合キーで MySQL が実際に使用するパート数が示されることに注意する。 -
refrefカラムは、テーブルからレコードを選択する際にkeyとともに使用されるカラムまたは定数を示す。 -
rowsrowsカラムは、クエリの実行に際して調べる必要があると MySQL によって判定されたレコードの数を示す。 -
Extraこのカラムには、MySQL でどのようにクエリが解決されるかに関する追加情報が記載される。以下は、このカラムに記載できる各種テキスト文字列の説明である。
-
Distinctマッチした最初のレコードが検索されると、MySQL は現在のレコードの組み合わせによるその後のレコード検索を続行しないことを示す。
-
Not existsMySQL でクエリに対する
LEFT JOIN最適化が実行でき、LEFT JOINに一致するレコードが 1 つ検索されると、前のレコードの組み合わせによるその後のテーブルのレコードについては調べないことを示す。この例は以下のとおりである。
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はt2.idではないと認識され、t2内の同じidを持つ残りのレコードのスキャンは行われない。言い換えると、t2にあるマッチするレコードの数に関わらず、MySQL で実行が必要なことはt1のレコードのそれぞれに対して、t2のルックアップを 1 回実行することだけである。 -
range checked for each record (index map: #)MySQL で使用に適した実際のインデックスを検索できなかったことを示す。代替として、先行テーブルのレコードの組み合わせのそれぞれに対して、使用するインデックス（存在する場合）のチェックが実行され、このインデックスがテーブルからのレコードの取り出しに使用される。非常に高速ではないが、インデックスなしの結合と比較すると高速である。
-
Using filesortレコードをソートして取り出す方法を決定するには、MySQL はパスを余分に実行しなくてはならないことを示す。
join typeに従ってすべてのレコードをスキャンし、WHERE条件に一致する全てのレコードに、ソートキー + 行ポインタを格納して、ソートは実行される。 その後キーがソートされる。 最後に、ソートされた順にレコードが取り出される。 -
Using indexインデックスツリーの情報のみを使用してカラム情報がテーブルから取り出され、実際のレコードを読み取るその後の検索を実行する必要がないことを示す。これは、そのテーブルで使用されたカラムがすべて同一インデックスの構成部分である場合に実行できる。
-
Using temporaryクエリの解決に MySQL で結果を保持するテンポラリテーブルの作成が必要であることを示す。これは一般に、
GROUP BYを実行したカラムセットと異なるカラムセットに対してORDER BYを実行した場合に発生する。 -
Using where次のテーブルとの一致が調べられるレコードまたはクライアントに送信されるレコードの限定に
WHERE節が使用されることを示す。この情報がなく、テーブルの型がALLまたはindexである場合はクエリが正常に実行されないことがある（テーブルのすべてのレコードの取得や検査を意図していない場合）。
クエリを最大限高速に実行する必要がある場合は、
Using filesortとUsing temporaryに注意する必要がある。 -
EXPLAIN 出力の rows カラムのすべての値を掛け算することで、結合がどの程度適しているかを示す指針を取得できます。これは、クエリの実行時に MySQL で調べる必要があるレコード数の概要を示します。この数値は、max_join_size 変数でクエリを制限する際にも使用されます。
See 項5.5.2. 「サーバパラメータのチューニング」。
下記の例は、EXPLAIN によって得られた情報を使用して、JOIN を累進的に最適化する方法を示しています。
ここでは、EXPLAIN を使用して、SELECT ステートメントを調べるとします。
EXPLAIN SELECT tt.TicketNumber, tt.TimeIn,
tt.ProjectReference, tt.EstimatedShipDate,
tt.ActualShipDate, tt.ClientID,
tt.ServiceCodes, tt.RepetitiveID,
tt.CurrentProcess, tt.CurrentDPPerson,
tt.RecordVolume, tt.DPPrinted, et.COUNTRY,
et_1.COUNTRY, do.CUSTNAME
FROM tt, et, et AS et_1, do
WHERE tt.SubmitTime IS NULL
AND tt.ActualPC = et.EMPLOYID
AND tt.AssignedPC = et_1.EMPLOYID
AND tt.ClientID = do.CUSTNMBR;
この例では以下のように想定しています。
-
比較対象のカラムは以下のように宣言されます。
テーブル カラム カラムの型 ttActualPCCHAR(10)ttAssignedPCCHAR(10)ttClientIDCHAR(10)etEMPLOYIDCHAR(15)doCUSTNMBRCHAR(15) -
テーブルには以下のインデックスがあります。
テーブル インデックス ttActualPCttAssignedPCttClientIDetEMPLOYID（主キー）doCUSTNMBR（主キー） tt.ActualPC値の分布が均一ではない。
当初、最適化の実行前は、EXPLAIN ステートメントで次の情報が生成されました。
table type possible_keys key key_len ref rows Extra
et ALL PRIMARY NULL NULL NULL 74
do ALL PRIMARY NULL NULL NULL 2135
et_1 ALL PRIMARY NULL NULL NULL 74
tt ALL AssignedPC,ClientID,ActualPC NULL NULL NULL 3872
range checked for each record (key map: 35)
各テーブルで type が ALL であるため、この出力は MySQL がすべてのテーブルのデカルト積を生成すると示しています。各テーブルのレコードの数の積の分量を調べる必要があるため、これは非常に時間がかかります。この例の場合は、レコードの数が 74 * 2135 * 74 * 3872 = 45,268,558,720 になります。テーブルがこれより大きい場合は、さらに時間がかかると考えられます。
ここでの問題の 1 つは、宣言の方法が異なると MySQL でカラムのインデックスを効率的に使用できないことにあります。この例では、VARCHAR と CHAR が異なる長さで宣言されていなければ同じになります。tt.ActualPC が CHAR(10) として、et.EMPLOYID が CHAR(15) として宣言されているため、長さの不一致が発生します。
カラムの長さの不一致を修正するため、ALTER TABLE を使用して ActualPC を 10 文字から 15 文字にします。
mysql> ALTER TABLE tt MODIFY ActualPC VARCHAR(15);
これで tt.ActualPC と et.EMPLOYID はいずれも VARCHAR(15) になりました。
ここでまた EXPLAIN を実行してみると、以下の結果が得られました。
table type possible_keys key key_len ref rows Extra
tt ALL AssignedPC,ClientID,ActualPC NULL NULL NULL 3872 Using where
do ALL PRIMARY NULL NULL NULL 2135
range checked for each record (key map: 1)
et_1 ALL PRIMARY NULL NULL NULL 74
range checked for each record (key map: 1)
et eq_ref PRIMARY PRIMARY 15 tt.ActualPC 1
これも完全ではありませんが、かなり改善されています（rows 値の積が 74 の係数分だけ減少）。このバージョンの場合実行に数秒かかります。
第 2 の変更を加えると、tt.AssignedPC = et_1.EMPLOYID と tt.ClientID = do.CUSTNMBR の比較でのカラム長の不一致を解消できます。
mysql> ALTER TABLE tt MODIFY AssignedPC VARCHAR(15),
-> MODIFY ClientID VARCHAR(15);
ここでは、EXPLAIN から以下の出力が生成されます。
table type possible_keys key key_len ref rows Extra
et ALL PRIMARY NULL NULL NULL 74
tt ref AssignedPC, ActualPC 15 et.EMPLOYID 52 Using where
ClientID,
ActualPC
et_1 eq_ref PRIMARY PRIMARY 15 tt.AssignedPC 1
do eq_ref PRIMARY PRIMARY 15 tt.ClientID 1
これでほとんど改善されています。
残りの問題は、MySQL ではデフォルトで tt.ActualPC カラムの値の分布が均一であると想定されますが、tt テーブルはこれにあてはまらないことです。これは容易に MySQL に示すことができます。
shell> myisamchk --analyze PATH_TO_MYSQL_DATABASE/tt shell> mysqladmin refresh
これで結合が完全になり、EXPLAIN で以下の結果が生成されます。
table type possible_keys key key_len ref rows Extra
tt ALL AssignedPC NULL NULL NULL 3872 Using where
ClientID,
ActualPC
et eq_ref PRIMARY PRIMARY 15 tt.ActualPC 1
et_1 eq_ref PRIMARY PRIMARY 15 tt.AssignedPC 1
do eq_ref PRIMARY PRIMARY 15 tt.ClientID 1
EXPLAIN の出力の rows カラムは、MySQL 結合オプティマイザの学習による推測であることに注意してください。クエリを最適化するには、この数値が実際に近いものであるかどうかを確認する必要があります。実際とかけ離れている場合は、SELECT ステートメントで STRAIGHT_JOIN を使用し、FROM 節でテーブルの順序を変えて一覧表示してみるとパフォーマンスを改善できます。
© 1995-2005 MySQL AB. All rights reserved.
