Skip navigation links
5.2.2 クエリパフォーマンスの推定 »
Section Navigation      [Toggle]

5.2.1. EXPLAIN 構文（SELECT に関する情報の取得）

    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 の出力は、次のカラムで構成されます。

  • id

    SELECT に割り当てられた ID。クエリ内におけるこの SELECT の順序番号。

  • select_type

    SELECT 節の種類、次のいずれかが示される。

    • SIMPLE

      単純な SELECT（UNION やサブクエリを使用しない）。

    • PRIMARY

      最外部の SELECT

    • UNION

      UNION 内の第 2 およびそれ以降の SELECT ステートメント。

    • DEPENDENT UNION

      UNION 内の第 2 およびそれ以降の SELECT ステートメント、外側のサブクエリに依存する。

    • SUBQUERY

      サブクエリ内の第 1 SELECT。

    • DEPENDENT SUBQUERY

      第 1 SELECT、外側のサブクエリに依存する。

    • DERIVED

      派生テーブル SELECT（FROM 節内のサブクエリ）。

  • table

    結果を得るために参照するテーブル。

  • type

    結合型。各結合型を最適なものから順に紹介する。

    • system

      1 レコードのみで構成されるテーブル（= システムテーブル）。これは、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_null

      ref と同様だが、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_keys

    possible_keys カラムは、このテーブル内のレコードの検索に MySQL で使用可能なインデックスを示す。このカラムはテーブルの順序にはまったく依存しないことに注意する。すなわち、possible_keys のキーの一部は、生成されたテーブルの順序では事実上使用できないことになる。

    このカラムが NULL の場合は、対応するインデックスがない。この場合は、WHERE 節でインデックス作成に適するカラムを 1 つ以上参照しているかどうかを調べることでクエリのパフォーマンスを改善できる。参照している場合は適切なインデックスを作成し、再度 EXPLAIN を使用してクエリをチェックする。 See 項6.5.4. 「ALTER TABLE 構文」。

    テーブルにあるインデックスを調べるには SHOW INDEX FROM tbl_name を使用する。

  • key

    key カラムは、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_len

    key_len カラムは、MySQL が使用を決定したキーの長さを示す。key が NULL の場合、この長さは NULL になる。これによって、複合キーで MySQL が実際に使用するパート数が示されることに注意する。

  • ref

    ref カラムは、テーブルからレコードを選択する際に key とともに使用されるカラムまたは定数を示す。

  • rows

    rows カラムは、クエリの実行に際して調べる必要があると MySQL によって判定されたレコードの数を示す。

  • Extra

    このカラムには、MySQL でどのようにクエリが解決されるかに関する追加情報が記載される。以下は、このカラムに記載できる各種テキスト文字列の説明である。

    • Distinct

      マッチした最初のレコードが検索されると、MySQL は現在のレコードの組み合わせによるその後のレコード検索を続行しないことを示す。

    • Not exists

      MySQL でクエリに対する 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;

この例では以下のように想定しています。

  • 比較対象のカラムは以下のように宣言されます。

    テーブル カラム カラムの型
    tt ActualPC CHAR(10)
    tt AssignedPC CHAR(10)
    tt ClientID CHAR(10)
    et EMPLOYID CHAR(15)
    do CUSTNMBR CHAR(15)

  • テーブルには以下のインデックスがあります。

    テーブル インデックス
    tt ActualPC
    tt AssignedPC
    tt ClientID
    et EMPLOYID（主キー）
    do CUSTNMBR（主キー）

  • 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 節でテーブルの順序を変えて一覧表示してみるとパフォーマンスを改善できます。

This is a translation of the MySQL Reference Manual that can be found at dev.mysql.com. The original Reference Manual is in English, and this translation is not necessarily as up to date as the English version.