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 whereになる。

mysql> EXPLAIN SELECT * FROM Country WHERE Name = 'Japan';
+----+-------------+---------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+---------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | Country | ALL  | NULL          | NULL | NULL    | NULL |  239 | Using where | 
+----+-------------+---------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.01 sec)

また、範囲検索（LIKE含む）やユニークでないインデックスを用いた場合にもUsing whereと表示される。

mysql> EXPLAIN SELECT * 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 | 
+----+-------------+---------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.02 sec)

Using index

クエリがインデックスだけを用いて解決できることを示す。（フルインデックススキャン、アクセスタイプがindexの場合とは違うので混同しないように注意！）Using indexとなるようにするには、WHERE句で指定したインデックス付きのカラムだけをSELECTするようにすれば良い。

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)

インデックスだけでクエリが解決できると、行データにアクセスする必要がないのでとても高速である。頻繁にアクセスするカラムがごく僅かな場合には、それらのカラムをすべて含む複合インデックス（Covering Indexと呼ぶ）をつけるとクエリが高速になる。

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
 
mysql> EXPLAIN SELECT Name, Code FROM Country WHERE Continent = 'Asia' AND Population > 100000000;
+----+-------------+---------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table   | type  | possible_keys | key     | key_len | ref  | rows | Extra                    |
+----+-------------+---------+-------+---------------+---------+---------+------+------+--------------------------+
|  1 | SIMPLE      | Country | range | Cover_1       | Cover_1 | 5       | NULL |    6 | Using where; Using index | 
+----+-------------+---------+-------+---------------+---------+---------+------+------+--------------------------+
1 row in set (0.03 sec)

他のカラムが混じるとUsing indexは消えてしまう。

mysql> EXPLAIN SELECT Name, Code, GNP FROM Country WHERE Continent = 'Asia' AND Population > 100000000;
+----+-------------+---------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table   | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+---------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | Country | range | Cover_1       | Cover_1 | 5       | NULL |    6 | Using where | 
+----+-------------+---------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.03 sec)

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をしてしまうことになるが、膨大な行数をソートしてしまっている時にはチューニングしたほうがいい。

まずはJOINしてからソートされるパターン。Cityを先にJOINするが、ソートするカラムがCountry.PopulationなのでどうしてもJOINの後からでないとソートできない。Using where; Using temporary; Using filesortが表示される。

mysql> EXPLAIN SELECT * FROM City LEFT JOIN Country ON City.CountryCode = Country.Code WHERE City.Population > 1000000 ORDER BY Country.Population;
+----+-------------+---------+--------+---------------+------------+---------+------------------------+------+----------------------------------------------+
| id | select_type | table   | type   | possible_keys | key        | key_len | ref                    | rows | Extra                                        |
+----+-------------+---------+--------+---------------+------------+---------+------------------------+------+----------------------------------------------+
|  1 | SIMPLE      | City    | range  | Population    | Population | 4       | NULL                   |  238 | Using where; Using temporary; Using filesort | 
|  1 | SIMPLE      | Country | eq_ref | PRIMARY       | PRIMARY    | 3       | world.City.CountryCode |    1 |                                              | 
+----+-------------+---------+--------+---------------+------------+---------+------------------------+------+----------------------------------------------+
2 rows in set (0.02 sec)

City.PopulationでソートするとUsing temporaryが消える。最初にソートが行われてからJOINされているのが分かる。

mysql> EXPLAIN SELECT * FROM City LEFT JOIN Country ON City.CountryCode = Country.Code WHERE City.Population > 1000000 ORDER BY City.Population;
+----+-------------+---------+--------+---------------+---------+---------+------------------------+------+-----------------------------+
| id | select_type | table   | type   | possible_keys | key     | key_len | ref                    | rows | Extra                       |
+----+-------------+---------+--------+---------------+---------+---------+------------------------+------+-----------------------------+
|  1 | SIMPLE      | City    | ALL    | NULL          | NULL    | NULL    | NULL                   | 4079 | Using where; Using filesort | 
|  1 | SIMPLE      | Country | eq_ref | PRIMARY       | PRIMARY | 3       | world.City.CountryCode |    1 |                             | 
+----+-------------+---------+--------+---------------+---------+---------+------------------------+------+-----------------------------+
2 rows in set (0.03 sec)

City.Populationにインデックスをつけると、インデックスでソートを解決できる。

mysql> ALTER TABLE City ADD INDEX (Population);
Query OK, 4079 rows affected (5.88 sec)
Records: 4079  Duplicates: 0  Warnings: 0
 
mysql> EXPLAIN SELECT * FROM City LEFT JOIN Country ON City.CountryCode = Country.Code WHERE City.Population > 1000000 ORDER BY City.Population;
+----+-------------+---------+--------+---------------+------------+---------+------------------------+------+-------------+
| id | select_type | table   | type   | possible_keys | key        | key_len | ref                    | rows | Extra       |
+----+-------------+---------+--------+---------------+------------+---------+------------------------+------+-------------+
|  1 | SIMPLE      | City    | range  | Population    | Population | 4       | NULL                   |  238 | Using where | 
|  1 | SIMPLE      | Country | eq_ref | PRIMARY       | PRIMARY    | 3       | world.City.CountryCode |    1 |             | 
+----+-------------+---------+--------+---------------+------------+---------+------------------------+------+-------------+
2 rows in set (0.02 sec)

ちなみに、City.Population > 1000000という条件をとってやるとやっぱりUsing filesortになってしまう。これはオプティマイザが全件スキャンのときはインデックスを辿るよりクイックソートの方が高速だと判断するためである。

mysql> EXPLAIN SELECT * FROM City LEFT JOIN Country ON City.CountryCode = Country.Code ORDER BY City.Population;
+----+-------------+---------+--------+---------------+---------+---------+------------------------+------+----------------+
| id | select_type | table   | type   | possible_keys | key     | key_len | ref                    | rows | Extra          |
+----+-------------+---------+--------+---------------+---------+---------+------------------------+------+----------------+
|  1 | SIMPLE      | City    | ALL    | NULL          | NULL    | NULL    | NULL                   | 4079 | Using filesort | 
|  1 | SIMPLE      | Country | eq_ref | PRIMARY       | PRIMARY | 3       | world.City.CountryCode |    1 |                | 
+----+-------------+---------+--------+---------------+---------+---------+------------------------+------+----------------+
2 rows in set (0.02 sec)

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

Using temporary

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

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

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

mysql> EXPLAIN SELECT DISTINCT Name FROM City ORDER BY Name;
+----+-------------+-------+------+---------------+------+---------+------+------+---------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                           |
+----+-------------+-------+------+---------------+------+---------+------+------+---------------------------------+
|  1 | SIMPLE      | City  | ALL  | NULL          | NULL | NULL    | NULL | 4079 | Using temporary; Using filesort | 
+----+-------------+-------+------+---------------+------+---------+------+------+---------------------------------+
1 row in set (0.01 sec)

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）で指定してもいいし、セッション変数を変更してセッションごとに使うかどうかを指定してもいい。

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)
 
mysql> EXPLAIN SELECT * FROM City WHERE Population > 10000000;
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                             |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------+
|  1 | SIMPLE      | City  | ALL  | NULL          | NULL | NULL    | NULL | 4079 | Using where with pushed condition | 
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------+
1 row in set (0.00 sec)

Using index for group-by

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

インデックスがない場合には、GROUP BYをつけるとUsing temporary; Using filesortになってしまう。（Using filesortはORDER BY NULLをつけると消える。）

mysql> EXPLAIN SELECT Continent, MAX(Population) FROM Country GROUP BY Continent;
+----+-------------+---------+------+---------------+------+---------+------+------+---------------------------------+
| id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows | Extra                           |
+----+-------------+---------+------+---------------+------+---------+------+------+---------------------------------+
|  1 | SIMPLE      | Country | ALL  | NULL          | NULL | NULL    | NULL |  239 | Using temporary; Using filesort | 
+----+-------------+---------+------+---------------+------+---------+------+------+---------------------------------+
1 row in set (0.02 sec)

GROUP BYで指定されるカラムと、MAX()/MIN()で指定されるカラムに複合インデックスをつけるとUsing index for group-byとなる。

mysql> ALTER TABLE Country ADD INDEX Cover_1 (Continent, Population);
Query OK, 239 rows affected (0.39 sec)
Records: 239  Duplicates: 0  Warnings: 0
 
mysql> EXPLAIN SELECT Continent, MAX(Population) FROM Country GROUP BY Continent;
+----+-------------+---------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table   | type  | possible_keys | key     | key_len | ref  | rows | Extra                    |
+----+-------------+---------+-------+---------------+---------+---------+------+------+--------------------------+
|  1 | SIMPLE      | Country | range | NULL          | Cover_1 | 1       | NULL |    8 | Using index for group-by | 
+----+-------------+---------+-------+---------------+---------+---------+------+------+--------------------------+
1 row in set (0.01 sec)

また、適切なインデックスがあればGROUP BYだけでなくSELECT DISTINCTでも同様の最適化が可能である。

mysql> EXPLAIN SELECT DISTINCT Continent, Population FROM Country;
+----+-------------+---------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table   | type  | possible_keys | key     | key_len | ref  | rows | Extra                    |
+----+-------------+---------+-------+---------------+---------+---------+------+------+--------------------------+
|  1 | SIMPLE      | Country | range | NULL          | Cover_1 | 5       | NULL |  240 | Using index for group-by | 
+----+-------------+---------+-------+---------------+---------+---------+------+------+--------------------------+
1 row in set (0.01 sec)

Distinct

JOINにおいて、後から結合される方のテーブルのPRIMARYまたはUNIQUEキーでSELECT DISTINCTを処理できるときに表示される。以下の例ではCity.ID（PRIMARY KEY）を用いてDISTINCT（つまり一意性の保証）を行っている。Country.Capital以外のカラムをSELECTしようとするとDistinctは表示されなくなるので、かなり特殊な用法であると言える。

mysql> EXPLAIN SELECT DISTINCT Country.Capital FROM Country, City WHERE Country.Capital = City.ID AND Continent = 'Asia';
+----+-------------+---------+--------+---------------+---------+---------+-----------------------+------+------------------------------+
| id | select_type | table   | type   | possible_keys | key     | key_len | ref                   | rows | Extra                        |
+----+-------------+---------+--------+---------------+---------+---------+-----------------------+------+------------------------------+
|  1 | SIMPLE      | Country | ALL    | NULL          | NULL    | NULL    | NULL                  |  239 | Using where; Using temporary | 
|  1 | SIMPLE      | City    | eq_ref | PRIMARY       | PRIMARY | 4       | world.Country.Capital |    1 | Using index; Distinct        | 
+----+-------------+---------+--------+---------------+---------+---------+-----------------------+------+------------------------------+
2 rows in set (0.02 sec)

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を作成する。

mysql> ALTER TABLE Country ADD INDEX (GNP), ADD INDEX (GNPold);
Query OK, 239 rows affected (0.38 sec)
Records: 239  Duplicates: 0  Warnings: 0
 
mysql> SHOW INDEX FROM Country;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Country |          0 | PRIMARY  |            1 | Code        | A         |         239 |     NULL | NULL   |      | BTREE      |         | 
| Country |          1 | GNP      |            1 | GNP         | A         |         239 |     NULL | NULL   | YES  | BTREE      |         | 
| Country |          1 | GNPOld   |            1 | GNPOld      | A         |         239 |     NULL | NULL   | YES  | BTREE      |         | 
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
3 rows in set (0.07 sec)

この場合、PRIMARYが0x1、GNPが0x2、GNPOldが0x4となる。

従って、GNPをJOINの条件にすると(index map: 0x2)と表示される。

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)

GNPoldをJOINの条件にした場合は(index map: 0x4)となる。

mysql> EXPLAIN SELECT * FROM Country c1, Country c2 WHERE c2.GNPold BETWEEN c1.GNP AND c1.GNPold;
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------------------------+
| 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  | GNPOld        | NULL | NULL    | NULL |  239 | Range checked for each record (index map: 0x4) | 
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------------------------+
2 rows in set (0.01 sec)

両方のインデックスを利用する場合、つまりindex_mergeでJOINを行うような場合には、Nはそれぞれのインデックスの論理和（OR）をとった値になる。0x2 | 0x4 = 0x6である。

mysql> EXPLAIN SELECT * FROM Country c1, Country c2 WHERE c2.GNP BETWEEN c1.GNPold AND c1.GNP OR c2.GNPold BETWEEN c1.GNP AND c1.GNPold;
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------------------------+
| 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,GNPOld    | NULL | NULL    | NULL |  239 | Range checked for each record (index map: 0x6) | 
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------------------------+
2 rows in set (0.03 sec)

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

Not exists

NOT NULLとして定義されたカラムを使ったLEFT JOINで、右側のテーブルの検索条件としてIS NULLが指定されている時に表示される。

mysql> EXPLAIN SELECT * FROM Country LEFT JOIN City ON Country.Capital = City.ID WHERE City.ID IS NULL;
+----+-------------+---------+--------+---------------+---------+---------+-----------------------+------+-------------------------+
| id | select_type | table   | type   | possible_keys | key     | key_len | ref                   | rows | Extra                   |
+----+-------------+---------+--------+---------------+---------+---------+-----------------------+------+-------------------------+
|  1 | SIMPLE      | Country | ALL    | NULL          | NULL    | NULL    | NULL                  |  239 |                         | 
|  1 | SIMPLE      | City    | eq_ref | PRIMARY       | PRIMARY | 4       | world.Country.Capital |    1 | Using where; Not exists | 
+----+-------------+---------+--------+---------------+---------+---------+-----------------------+------+-------------------------+
2 rows in set (0.02 sec)

この例では、City.ID（PRIMARY KEY）はNOT NULLであるが、City.ID IS NULLによって検索している。つまり、Country.Capital = City.IDという条件を満たす行がCityテーブルに存在しない（Country.CapitalがそもそもNULLである場合など）行を検索するようなクエリであるということを示すわけである。

Using join buffer

Join Bufferと呼ばれるバッファが使われていることを示す。Join Bufferが使われる最たる例はJOINをする際に全件スキャンを行う必要がある場合である。

mysql> EXPLAIN SELECT * FROM Country, City WHERE Country.Code LIKE 'J%';
+----+-------------+---------+-------+---------------+---------+---------+------+------+-------------------+
| id | select_type | table   | type  | possible_keys | key     | key_len | ref  | rows | Extra             |
+----+-------------+---------+-------+---------------+---------+---------+------+------+-------------------+
|  1 | SIMPLE      | Country | range | PRIMARY       | PRIMARY | 3       | NULL |    2 | Using where       | 
|  1 | SIMPLE      | City    | ALL   | NULL          | NULL    | NULL    | NULL | 4079 | Using join buffer | 
+----+-------------+---------+-------+---------------+---------+---------+------+------+-------------------+
2 rows in set (0.02 sec)

このようなクエリはとても遅いので、もしこのようなクエリを見かけたらすかさず書き換えよう。（CityテーブルのRecord access typeがALLになっていることから、都度全件スキャンが行われることが分かる。）

MySQL 6.0以降はBKA JOINという最適化手法が追加されているが、BKAにおいてもJoin Bufferが利用される。BKAは非常に高速なJOIN手法なので、FULL JOINと混同しないようにしよう。BKAを利用するには、join_cache_levelを5〜8に設定しなければならない。次の例では、join_cache_levelを変更することによってBKAが利用されるようになることが分かる。

mysql> EXPLAIN SELECT * FROM City,Country WHERE City.CountryCode = Country.Code;
+----+-------------+---------+--------+---------------+---------+---------+------------------------+------+-------+
| id | select_type | table   | type   | possible_keys | key     | key_len | ref                    | rows | Extra |
+----+-------------+---------+--------+---------------+---------+---------+------------------------+------+-------+
|  1 | SIMPLE      | City    | ALL    | CountryCode   | NULL    | NULL    | NULL                   | 4079 |       | 
|  1 | SIMPLE      | Country | eq_ref | PRIMARY       | PRIMARY | 3       | world.City.CountryCode |    1 |       | 
+----+-------------+---------+--------+---------------+---------+---------+------------------------+------+-------+
2 rows in set (0.00 sec)
 
mysql> SET @@join_cache_level=6;
Query OK, 0 rows affected (0.01 sec)
 
mysql> EXPLAIN SELECT * FROM City,Country WHERE City.CountryCode = Country.Code;
+----+-------------+---------+--------+---------------+---------+---------+------------------------+------+-------------------+
| id | select_type | table   | type   | possible_keys | key     | key_len | ref                    | rows | Extra             |
+----+-------------+---------+--------+---------------+---------+---------+------------------------+------+-------------------+
|  1 | SIMPLE      | City    | ALL    | CountryCode   | NULL    | NULL    | NULL                   | 4079 |                   | 
|  1 | SIMPLE      | Country | eq_ref | PRIMARY       | PRIMARY | 3       | world.City.CountryCode |    1 | Using join buffer | 
+----+-------------+---------+--------+---------------+---------+---------+------------------------+------+-------------------+
2 rows in set (0.00 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に変更しよう。

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
 
mysql> EXPLAIN SELECT Name, Name IN (SELECT Name FROM City) FROM Country;
+----+--------------------+---------+----------------+---------------+------+---------+------+------+-------------------------------------------------+
| id | select_type        | table   | type           | possible_keys | key  | key_len | ref  | rows | Extra                                           |
+----+--------------------+---------+----------------+---------------+------+---------+------+------+-------------------------------------------------+
|  1 | PRIMARY            | Country | index          | NULL          | Name | 53      | NULL |  239 | Using index                                     | 
|  2 | DEPENDENT SUBQUERY | City    | index_subquery | Name          | Name | 35      | func |    1 | Using index; Using where; Full scan on NULL key | 
+----+--------------------+---------+----------------+---------------+------+---------+------+------+-------------------------------------------------+
2 rows in set (0.00 sec)

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を使った検索では一つのインデックスを使って行を取得してから別のキーで行を絞り込んでもそれほど処理が遅くなるわけではないので、テーブルの統計情報次第では利用されないこともある。

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)

Using union(...)

等価比較による検索条件がORで繋がれているとき、それぞれのカラムにおいてインデックスが張られているときにUsing union(...)となる。5.0より古いバージョンのMySQLでは全件スキャンする以外になかったクエリであり、全件スキャンと比較するとIndex Mergeを使う効果は非常に期待出来る。

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)

Using sort_union(...)

比較演算子（<または>）による検索条件がORで繋がれているときに使われるアルゴリズムである。

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)

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

InnoDBはPRIMARY KEYがクラスタインデックスになっているため、PRIMARY KEYにおける大小の比較と、それ以外のキー（セカンダリインデックス）の等価比較をANDで繋いだときにIntersectによるIndex Mergeアルゴリズムが使われる。

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)

Using index condition

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

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
 
mysql> EXPLAIN SELECT * FROM Country WHERE Continent = 'Asia' AND Name LIKE 'J%';
+----+-------------+---------+------+---------------+---------+---------+-------+------+-----------------------+
| id | select_type | table   | type | possible_keys | key     | key_len | ref   | rows | Extra                 |
+----+-------------+---------+------+---------------+---------+---------+-------+------+-----------------------+
|  1 | SIMPLE      | Country | ref  | Cover_1       | Cover_1 | 1       | const |   43 | Using index condition | 
+----+-------------+---------+------+---------------+---------+---------+-------+------+-----------------------+
1 row in set (0.00 sec)

WHERE句で指定されるのは別々のインデックスであっても良い。

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)

Using MRR

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

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)

Semi-Join

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

Table pullout

mysql> EXPLAIN SELECT * FROM Country WHERE Capital IN (SELECT ID FROM City); 
+----+-------------+---------+--------+---------------+---------+---------+-----------------------+------+-------------+
| id | select_type | table   | type   | possible_keys | key     | key_len | ref                   | rows | Extra       |
+----+-------------+---------+--------+---------------+---------+---------+-----------------------+------+-------------+
|  1 | PRIMARY     | Country | ALL    | NULL          | NULL    | NULL    | NULL                  |  239 |             | 
|  1 | PRIMARY     | City    | eq_ref | PRIMARY       | PRIMARY | 4       | world.Country.Capital |    1 | Using index | 
+----+-------------+---------+--------+---------------+---------+---------+-----------------------+------+-------------+
2 rows in set (0.00 sec)

Start temporary/End temporary

6.0

Using FirstMatch(X)

6.0

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)

LooseScan

6.0

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
 
mysql> EXPLAIN SELECT * FROM City WHERE NAME IN (SELECT Continent FROM Country);
+----+-------------+---------+-------+---------------+-----------+---------+-------------------------+------+------------------------+
| id | select_type | table   | type  | possible_keys | key       | key_len | ref                     | rows | Extra                  |
+----+-------------+---------+-------+---------------+-----------+---------+-------------------------+------+------------------------+
|  1 | PRIMARY     | Country | index | Continent     | Continent | 1       | NULL                    |  239 | Using index; LooseScan | 
|  1 | PRIMARY     | City    | ref   | Name          | Name      | 35      | world.Country.Continent |    1 | Using index condition  | 
+----+-------------+---------+-------+---------------+-----------+---------+-------------------------+------+------------------------+
2 rows in set (0.01 sec)

Materialize/Scan/Start materialize/End materialize

mysql> EXPLAIN SELECT * FROM City WHERE ID IN (SELECT Capital FROM Country);
+----+-------------+---------+--------+---------------+---------+---------+-----------------------+------+-------------------+
| id | select_type | table   | type   | possible_keys | key     | key_len | ref                   | rows | Extra             |
+----+-------------+---------+--------+---------------+---------+---------+-----------------------+------+-------------------+
|  1 | PRIMARY     | Country | ALL    | NULL          | NULL    | NULL    | NULL                  |  239 | Materialize; Scan | 
|  1 | PRIMARY     | City    | eq_ref | PRIMARY       | PRIMARY | 4       | world.Country.Capital |    1 |                   | 
+----+-------------+---------+--------+---------------+---------+---------+-----------------------+------+-------------------+
2 rows in set (0.00 sec)
 
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)

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

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