Extra field

提供：MySQL Practice Wiki

ExtraフィールドはEXPLAINで一番右に表示されるフィールドで、オプティマイザがどのような最適化手法を利用したかということについてヒントを与えてくれる。Extraという名前でありながら、実は最も大事な大事な項目かも知れない。

Using where

恐らく最もよく目にする追加情報であろう。大半のクエリで目にするのではないだろうか。実はUsing whereが出てようが出まいがあまり関係ないのでそんなに気にする必要のない情報である。

Using whereはWHERE句に検索条件が指定されており、なおかつインデックスの条件を指定するだけでは適切な結果が得られないような場合に表示される。従って次のように、PRIMARY KEYを用いたクエリではUsing whereにはならない。

mysql> EXPLAIN SELECT * FROM Country WHERE Code = 'JPN';
+----+-------------+---------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table   | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+---------+-------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | Country | const | PRIMARY       | PRIMARY | 3       | const |    1 |       | 
+----+-------------+---------+-------+---------------+---------+---------+-------+------+-------+
1 row in set (0.03 sec)

Using index

クエリがインデックスだけを用いて解決できることを示す。（フルインデックススキャン、アクセスタイプがindexの場合とは違うので混同しないように注意！）Using indexとなるようにするには、WHERE句で指定したインデックス付きのカラムだけをSELECTするようにすれば良い。 <highlightSyntax language="mysql"> mysql> EXPLAIN SELECT Code FROM Country WHERE Code LIKE 'J%'; +----+-------------+---------+-------+---------------+---------+---------+------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------+-------+---------------+---------+---------+------+------+--------------------------+ | 1 | SIMPLE | Country | range | PRIMARY | PRIMARY | 3 | NULL | 3 | Using where; Using index | +----+-------------+---------+-------+---------------+---------+---------+------+------+--------------------------+ 1 row in set (0.01 sec) </highlightSyntax> インデックスだけでクエリが解決できると、行データにアクセスする必要がないのでとても高速である。頻繁にアクセスするカラムがごく僅かな場合には、それらのカラムをすべて含む複合インデックス（Covering Indexと呼ぶ）をつけるとクエリが高速になる。 <highlightSyntax language="mysql"> mysql> ALTER TABLE Country ADD INDEX Cover_1 (Continent, Population, Name, Code); Query OK, 239 rows affected (0.79 sec) Records: 239 Duplicates: 0 Warnings: 0

Using filesort

SQL文でORDER BYの指定があるときに、MySQLがインデックスを利用してソートを解決できない場合に表示される追加情報である。filesortで用いられるアルゴリズムはクイックソートである。Using filesortについてはブログで詳しく解説しているのでそちらを見て欲しい。

Using filesortが表示される場合とそうでない場合を含めると、JOINにおけるソート処理の方法には3パターン存在する。

最初のテーブルをインデックスを用いてソートして、それからJOINするパターン・・・Using filesortの表示がない。
最初のテーブルをFilesortしてからJOINするパターン・・・EXPLAINの最初の行にUsing filesortの表示。
全てのテーブルをJOINしてからFilesortをするパターン・・・EXPLAINの最初の行にUsing temporary; Using filesortの表示。

もちろんインデックスでソートを解決できれば最も速い（一番目のパターン）のだがそれには適切なインデックスが必要である。どんなときにインデックスが利用されるかということについてもブログで詳しく解説しているのでそちらを見て欲しい。適切なインデックスがない場合にはUsing filesortをしてしまうことになるが、膨大な行数をソートしてしまっている時にはチューニングしたほうがいい。

City.Populationにインデックスをつけると、インデックスでソートを解決できる。 <highlightSyntax language="mysql"> mysql> ALTER TABLE City ADD INDEX (Population); Query OK, 4079 rows affected (5.88 sec) Records: 4079 Duplicates: 0 Warnings: 0

Filesortは高速なので、100件程度までならあまり気にしなくて良い。しかし、クエリを高速に実行してサーバの負荷を減らすには、WHERE句でしっかりと検索条件を指定して、ソートの前に行数を絞り込んでやる必要がある。LIMIT句はJOINもソートも全て済んだ後に適用されるので、LIMIT句による行数の絞り込みはJOINするときはあまり意味がない。

Using temporary

クエリの実行にテンポラリテーブルが必要なことを示す。テンポラリテーブルが利用されるのは次の場合。

JOINの結果をソートする場合（上記）
ORDER BYとDISTINCTを併用した場合。
集計関数を使う場合
UNION
DERIVEDテーブル

ただし、DERIVEDやUNIONではUsing temporaryは表示されない。従って、Using temporaryが表示されるのはテンポラリテーブルを使う必要のあるソート処理であることを意味する。

Using where with pushed condition

Engine Condition Pushdown最適化が行われていることを示す。Engine Condition Pushdownとは、ストレージエンジン側に検索条件を渡す（Push Downする）ことによって、ストレージエンジン側で効率的な行の絞り込みを行うというものである。現在（MySQL 5.1時点）ではMySQL Clusterで利用されるのみである。

Engine Condition Pushdownを利用するには、engine_condition_pushdown=ONを指定する必要がある。オプションファイル（my.cnf）で指定してもいいし、セッション変数を変更してセッションごとに使うかどうかを指定してもいい。

<highlightSyntax language="mysql"> mysql> ALTER TABLE City ENGINE NDB; Query OK, 4079 rows affected (3.05 sec) Records: 4079 Duplicates: 0 Warnings: 0

mysql> SELECT @@engine_condition_pushdown; +-----------------------------+ | @@engine_condition_pushdown | +-----------------------------+ | 1 | +-----------------------------+ 1 row in set (0.01 sec)

Using index for group-by

GROUP BYによって、MIN()またはMAX()が処理されるときに表示される。適切なインデックスがついていれば、MIN()またはMAX()はインデックスからそれぞれのエントリの最大値または最小値を読み込むだけでいいからだ。

GROUP BYで指定されるカラムと、MAX()/MIN()で指定されるカラムに複合インデックスをつけるとUsing index for group-byとなる。 <highlightSyntax language="mysql"> mysql> ALTER TABLE Country ADD INDEX Cover_1 (Continent, Population); Query OK, 239 rows affected (0.39 sec) Records: 239 Duplicates: 0 Warnings: 0

Distinct

Range checked for each record (index map: N)

JOINにおいてrangeまたはindex_mergeが利用される場合に表示される。つまり、一つ目のテーブルからフェッチした行ひとつひとつに対して、範囲検索により二つ目のテーブルから行をフェッチしてJOINするような場合に利用される。一つ目のテーブルからM行、二つ目のテーブルから平均N行の行がフェッチされるとすると、結果はM×N行になる。Range checked for each record (index map: N)が表示されたということは、クエリはそれほど高速であるとは言えないが、FULL JOIN（全件スキャン同士のJOIN）よりは高速であると言える。OLTP系の処理では禁物だが、BI系の処理ならばアリだろう。

(index map: N)のNは利用されるインデックスのビットマップで、一番目のインデックスが利用されるなら0x1、二番目のインデックスが利用されるなら0x2、以降0x4、0x8...と続く。次のようにINDEXを作成する。 <highlightSyntax language="mysql"> mysql> ALTER TABLE Country ADD INDEX (GNP), ADD INDEX (GNPold); Query OK, 239 rows affected (0.38 sec) Records: 239 Duplicates: 0 Warnings: 0

従って、GNPをJOINの条件にすると(index map: 0x2)と表示される。 <highlightSyntax language="mysql"> mysql> EXPLAIN SELECT * FROM Country c1, Country c2 WHERE c2.GNP BETWEEN c1.GNPold AND c1.GNP; +----+-------------+-------+------+---------------+------+---------+------+------+------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+------------------------------------------------+ | 1 | SIMPLE | c1 | ALL | GNP,GNPOld | NULL | NULL | NULL | 239 | | | 1 | SIMPLE | c2 | ALL | GNP | NULL | NULL | NULL | 239 | Range checked for each record (index map: 0x2) | +----+-------------+-------+------+---------------+------+---------+------+------+------------------------------------------------+ 2 rows in set (0.02 sec) </highlightSyntax>

このタイプのクエリが実行されると、Select_range_checkステータス変数がインクリメントされる。

Not exists

Using join buffer

mysql> SET @@join_cache_level=6; Query OK, 0 rows affected (0.01 sec)

Join Bufferのサイズはjoin_buffer_sizeオプションで指定することができる。BKA JOINで結果がとても大きくなる場合にはjoin_buffer_sizeを増やすと性能が向上する。

Full scan on NULL key

INサブクエリで、なおかつcol1 IN (SELECT key1 ...)の評価の結果そのものを表示しないといけない時だけに利用される最適化手法である。この最適化手法が有効なのは、col1がNULLになる可能性のあるカラムで、なおかつサブクエリはcol1がNULLの時以外はインデックスを利用する、つまりindex_subqueryまたはunique_subqueryの時である。具体的に書くと、クエリが次の構造のときに利用される。

SELECT col1, col1 IN (SELECT key1 FROM tbl2) FROM tbl1;

col1 IN (SELECT key1 FROM tbl2)は、col1がNULLになる可能性がなければTRUEまたはFALSEになるが、col1がNULLのときはFALSEまたはNULLになる必要がある。まとめると次のようになる。

col1がNULLでないとき。
1. サブクエリの結果にcol1と同じ値が含まれる→TRUE
2. サブクエリの結果にcol1と同じ値が含まれない→FALSE
col1がNULLのとき。
1. サブクエリの結果行がEmpty→FALSE
2. サブクエリの結果行がEmptyでない→NULL

このように、col1がNULLかNULLでないかによってサブクエリの評価方法が全く異なってしまう。「Full scan on NULL key」が示すのは、col1がNULLのときにはサブクエリにおいてテーブルスキャンをする必要があるということである。

以下のように、外部クエリ側のカラムがNULLになり得るデータ型で、サブクエリ側のカラムにインデックスがあればFull scan on NULL keyになる。当然ながらこのようなクエリは効率的ではない。できれば外部クエリ側のカラムをNOT NULLに変更しよう。 <highlightSyntax language="mysql"> mysql> ALTER TABLE City ADD INDEX (Name); Query OK, 4079 rows affected (0.14 sec) Records: 4079 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE Country MODIFY Name CHAR(52); Query OK, 239 rows affected (0.06 sec) Records: 239 Duplicates: 0 Warnings: 0

Index Merge

MySQL 5.0以降のバージョンではIndex Mergeという最適化が追加されている。MySQL 5.0より古いバージョン（4.1以前）では、一つのテーブルから行をフェッチする場合にインデックスは常に一つしか利用されなかった。しかし、MySQL 5.0以降のバージョンにはIndex Mergeアルゴリズムが追加されたことにより、ANDやORで複数のインデックスを指定した場合には複数のインデックスが使えるようになったのである。Index Meargeの種類は3つあるので、順に見ていこう。

Using intersect(...)

Intersectは共通集合（積集合）という意味で、2つのインデックスの共通集合を取りましょうという最適化手法である。つまり、AND条件において2つ以上のインデックスが利用される場合に使われる。次のように2つのインデックスを追加し、それぞれのインデックスを等価検索した上でANDで繋ぐ。ただしどちらかのインデックスがユニークな場合には、ユニークなインデックスを使った方が検索が速い（そのインデックスを使って一行とってくれば良い）ので、PRIMARY/UNIQUE以外のインデックスを同時に指定する時にintersectアルゴリズムが利用される。また、元々ANDを使った検索では一つのインデックスを使って行を取得してから別のキーで行を絞り込んでもそれほど処理が遅くなるわけではないので、テーブルの統計情報次第では利用されないこともある。 <highlightSyntax language="mysql"> mysql> ALTER TABLE City ADD INDEX (CountryCode), ADD INDEX (Population); Query OK, 4079 rows affected (0.48 sec) Records: 4079 Duplicates: 0 Warnings: 0

mysql> EXPLAIN SELECT * FROM City WHERE Population=90000 AND CountryCode='UKR'\G

- - - - 1. row ***************************

          id: 1
 select_type: SIMPLE
       table: City
        type: index_merge

possible_keys: CountryCode,Population

         key: Population,CountryCode
     key_len: 4,3
         ref: NULL
        rows: 1
       Extra: Using intersect(Population,CountryCode); Using where

1 row in set (0.00 sec) </highlightSyntax>

Using union(...)

等価比較による検索条件がORで繋がれているとき、それぞれのカラムにおいてインデックスが張られているときにUsing union(...)となる。5.0より古いバージョンのMySQLでは全件スキャンする以外になかったクエリであり、全件スキャンと比較するとIndex Mergeを使う効果は非常に期待出来る。 <highlightSyntax language="mysql"> mysql> EXPLAIN SELECT * FROM City WHERE Population=90000 OR CountryCode='UKR'\G

- - - - 1. row ***************************

          id: 1
 select_type: SIMPLE
       table: City
        type: index_merge

possible_keys: CountryCode,Population

         key: Population,CountryCode
     key_len: 4,3
         ref: NULL
        rows: 68
       Extra: Using union(Population,CountryCode); Using where

1 row in set (0.00 sec) </highlightSyntax>

Using sort_union(...)

比較演算子（<または>）による検索条件がORで繋がれているときに使われるアルゴリズムである。 <highlightSyntax language="mysql"> mysql> EXPLAIN SELECT * FROM City WHERE Population<90000 OR CountryCode='UKR'\G

- - - - 1. row ***************************

          id: 1
 select_type: SIMPLE
       table: City
        type: index_merge

possible_keys: CountryCode,Population

         key: Population,CountryCode
     key_len: 4,3
         ref: NULL
        rows: 187
       Extra: Using sort_union(Population,CountryCode); Using where

1 row in set (0.00 sec) </highlightSyntax>

InnoDBテーブルにおけるUsing intersect(...)

InnoDBはPRIMARY KEYがクラスタインデックスになっているため、PRIMARY KEYにおける大小の比較と、それ以外のキー（セカンダリインデックス）の等価比較をANDで繋いだときにIntersectによるIndex Mergeアルゴリズムが使われる。 <highlightSyntax language="mysql"> mysql> ALTER TABLE City ENGINE InnoDB; Query OK, 4079 rows affected (1.03 sec) Records: 4079 Duplicates: 0 Warnings: 0

mysql> EXPLAIN SELECT * FROM City WHERE ID < 1000 AND CountryCode='UKR'\G

- - - - 1. row ***************************

          id: 1
 select_type: SIMPLE
       table: City
        type: index_merge

possible_keys: PRIMARY,CountryCode

         key: CountryCode,PRIMARY
     key_len: 3,4
         ref: NULL
        rows: 23
       Extra: Using intersect(CountryCode,PRIMARY); Using where

1 row in set (0.00 sec) </highlightSyntax>

Using index condition

MySQL 6.0で追加されるIndex Condition Pushdown（ICPと略されることが多い）最適化が使われる場合に表示される。Engine Condition Pushdownと似ているが、ICPはインデックスだけに適用されるところが異なる。

<highlightSyntax language="mysql"> mysql> ALTER TABLE Country ADD INDEX Cover_1(Continent, Region, Name); Query OK, 239 rows affected (0.52 sec) Records: 239 Duplicates: 0 Warnings: 0

WHERE句で指定されるのは別々のインデックスであっても良い。 <highlightSyntax language="mysql"> mysql> ALTER TABLE Country ADD INDEX (Population); Query OK, 239 rows affected (0.54 sec) Records: 239 Duplicates: 0 Warnings: 0

mysql> EXPLAIN SELECT * FROM Country WHERE Continent = 'Asia' AND Population < 10000000\G

- - - - 1. row ***************************

          id: 1
 select_type: SIMPLE
       table: Country
        type: ref

possible_keys: Cover_1,Population

         key: Cover_1
     key_len: 1
         ref: const
        rows: 43
       Extra: Using index condition; Using where

1 row in set (0.00 sec) </highlightSyntax>

Using MRR

MySQL 6.0で追加されるMulti Read Range（MRRと略される）最適化が行われているときに表示される。MRRは先にインデックスから読み込むべき行を特定し、ストレージエンジンにとって都合の良い順序（ROWIDなど）で行を読み込むことによって高速化を図るという最適化手法である。MRRはもっぱらICTと併用される。

<highlightSyntax language="mysql"> mysql> EXPLAIN SELECT * FROM City WHERE ID IN (100, 10, 1000, 20, 200, 300, 50, 5000, 40)\G

- - - - 1. row ***************************

          id: 1
 select_type: SIMPLE
       table: City
        type: range

possible_keys: PRIMARY

         key: PRIMARY
     key_len: 4
         ref: NULL
        rows: 9
       Extra: Using index condition; Using MRR

1 row in set (0.00 sec)

mysql> EXPLAIN SELECT * FROM City WHERE ID < 100 OR ID > 4000\G

- - - - 1. row ***************************

          id: 1
 select_type: SIMPLE
       table: City
        type: range

possible_keys: PRIMARY

         key: PRIMARY
     key_len: 4
         ref: NULL
        rows: 180
       Extra: Using index condition; Using MRR

1 row in set (0.00 sec)

mysql> EXPLAIN SELECT * FROM City WHERE ID < 100 AND Population < 1000\G

- - - - 1. row ***************************

          id: 1
 select_type: SIMPLE
       table: City
        type: range

possible_keys: PRIMARY,Population

         key: Population
     key_len: 4
         ref: NULL
        rows: 12
       Extra: Using index condition; Using where; Using MRR

1 row in set (0.00 sec) </highlightSyntax>

Semi-Join

MySQL 6.0で追加されるSemi-Joinシリーズ。工事中！！

Table pullout

Start temporary/End temporary

6.0

Using FirstMatch(X)

6.0 <highlightSyntax language="mysql"> mysql> ALTER TABLE City ADD INDEX (Population); Query OK, 4079 rows affected (0.17 sec) Records: 4079 Duplicates: 0 Warnings: 0

mysql> EXPLAIN SELECT * FROM Country WHERE Code IN (SELECT CountryCode FROM City WHERE Population > Country.Population / 2)\G

- - - - 1. row ***************************

          id: 1
 select_type: PRIMARY
       table: Country
        type: ALL

possible_keys: PRIMARY

         key: NULL
     key_len: NULL
         ref: NULL
        rows: 239
       Extra:

- - - - 2. row ***************************

          id: 1
 select_type: PRIMARY
       table: City
        type: ALL

possible_keys: Population

         key: NULL
     key_len: NULL
         ref: NULL
        rows: 4079
       Extra: Range checked for each record (index map: 0x2); FirstMatch(Country)

2 rows in set (0.00 sec) </highlightSyntax>

LooseScan

6.0 <highlightSyntax language="mysql"> mysql> ALTER TABLE City ADD INDEX (Name); Query OK, 4079 rows affected (0.57 sec) Records: 4079 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE Country ADD INDEX (Continent); Query OK, 239 rows affected (0.18 sec) Records: 239 Duplicates: 0 Warnings: 0

Materialize/Scan/Start materialize/End materialize

mysql> ALTER TABLE City ADD INDEX (Population); Query OK, 4079 rows affected (0.17 sec) Records: 4079 Duplicates: 0 Warnings: 0

mysql> EXPLAIN SELECT * FROM Country WHERE Code IN (SELECT CountryCode FROM City WHERE Population > 1000000)\G

- - - - 1. row ***************************

          id: 1
 select_type: PRIMARY
       table: Country
        type: ALL

possible_keys: PRIMARY

         key: NULL
     key_len: NULL
         ref: NULL
        rows: 239
       Extra:

- - - - 2. row ***************************

          id: 1
 select_type: PRIMARY
       table: City
        type: range

possible_keys: Population

         key: Population
     key_len: 4
         ref: NULL
        rows: 238
       Extra: Using index condition; Using MRR; Materialize

2 rows in set (0.00 sec)

mysql> EXPLAIN SELECT * FROM Country WHERE Name IN (SELECT Name FROM City,CountryLanguage WHERE City.Name = CountryLanguage.Language AND Population < 1000)\G

- - - - 1. row ***************************

          id: 1
 select_type: PRIMARY
       table: City
        type: range

possible_keys: Population

         key: Population
     key_len: 4
         ref: NULL
        rows: 12
       Extra: Using index condition; Using MRR; Start materialize; Scan

- - - - 2. row ***************************

          id: 1
 select_type: PRIMARY
       table: CountryLanguage
        type: index

possible_keys: NULL

         key: PRIMARY
     key_len: 33
         ref: NULL
        rows: 984
       Extra: Using where; Using index; End materialize; Using join buffer

- - - - 3. row ***************************

          id: 1
 select_type: PRIMARY
       table: Country
        type: ALL

possible_keys: NULL

         key: NULL
     key_len: NULL
         ref: NULL
        rows: 239
       Extra: Using where; Using join buffer

3 rows in set (0.00 sec) </highlightSyntax>

INFORMATION_SCHEMAを使ったクエリについて

INFORMATION_SCHEMAからSELECTを行う場合には特別な最適化がされる。詳細はINFORMATION_SCHEMAを使ったクエリのExtra_Fieldを参照のこと。

Extra field

提供：MySQL Practice Wiki

目次

Using where

Using index

Using filesort

Using temporary

Using where with pushed condition

Using index for group-by

Distinct

Range checked for each record (index map: N)

Not exists

Using join buffer

Full scan on NULL key

Index Merge

Using intersect(...)

Using union(...)

Using sort_union(...)

InnoDBテーブルにおけるUsing intersect(...)

Using index condition

Using MRR

Semi-Join

Table pullout

Start temporary/End temporary

Using FirstMatch(X)

LooseScan

Materialize/Scan/Start materialize/End materialize

INFORMATION_SCHEMAを使ったクエリについて

表示

個人用ツール

案内

検索

ツールボックス