MySQL 5.6.5 から導入されたクエリ最適化戦略のMATERIALIZED について
新しいMySQL ではEXPLAIN にMATERIALIZED が出てくるようになったのに気付きました。
| 2 | MATERIALIZED | city | ALL | CountryCode | NULL | NULL | NULL | 4188 | Using where |
なんじゃこりゃ。
サブクエリ最適化の一種
http://dev.mysql.com/doc/refman/5.6/en/explain-output.html
EXPLAIN で表示されるこれはMaterialized subquery のことだと書いてあります。
http://dev.mysql.com/doc/refman/5.6/en/subquery-optimization.html#subquery-materialization
んで、Materialized subquery はサブクエリ最適化の一種とのこと。
今日は、ここの記事に書いてあることを元に、具体例も付けて紹介してみたいと思います。
これまでの方式
これまでは
SELECT * FROM t1 WHERE t1.a IN (SELECT t2.b FROM t2 WHERE where_condition);
を実行すると、以下のように絞り込み条件t1.a = t2.b を追加する書き換えが行われて、サブクエリ側で全件取得しなくてもいいような工夫がなされていました。
SELECT * FROM t1 WHERE EXISTS (SELECT t2.b FROM t2 WHERE where_condition AND t1.a=t2.b);
本当にそうなるか、試してみます。
なおMySQL のドキュメントページで配布されているサンプルデータベースを元に実験します。
http://dev.mysql.com/doc/index-other.html
例)人口が100万人以上の都市を持つ国を取得. MySQL 5.5.27 で確認
mysql> EXPLAIN SELECT * FROM Country -> WHERE Code IN (SELECT CountryCode FROM City WHERE Population > 1000000); +----+--------------------+---------+----------------+---------------+-------------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+--------------------+---------+----------------+---------------+-------------+---------+------+------+-------------+ | 1 | PRIMARY | Country | ALL | NULL | NULL | NULL | NULL | 245 | Using where | | 2 | DEPENDENT SUBQUERY | City | index_subquery | CountryCode | CountryCode | 3 | func | 8 | Using where | +----+--------------------+---------+----------------+---------------+-------------+---------+------+------+-------------+ 2 rows in set (0.08 sec)
City テーブルはインデックスを使った検索となっているので、明らかに
mysql> EXPLAIN SELECT * FROM Country -> WHERE EXISTS (SELECT CountryCode FROM City WHERE Population > 1000000 AND Country.Code = City.CountryCode);
のようにクエリが書き直された時の動きになっています。
今回はストレージエンジンから取得する行数の見積もりは245 + (245 * 8) = 約2250行ですね。
MySQL 5.6.5 からのMATERIALIZED
さて、ドキュメントの内容に戻ると、MySQL 5.6.5 からは最適化戦略として"サブクエリの実体化(Materialize)" が追加されました。
何が実体化なのかというと、サブクエリを実行するのに有利になるようなテンポラリテーブルが作られることを指しており、通常はメモリ上にハッシュインデックスが作られるようです。
この方式のメリットは、
- クエリの書き換え不要
- 再度サブクエリの実行が必要な時には、テンポラリテーブルを参照するだけでよい。これはインデックスを使って高速に動作。
です。
例)先ほどと同じ例をMySQL 5.6.16 で確認
+----+--------------+-------------+--------+---------------+------------+---------+--------------------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+--------------+-------------+--------+---------------+------------+---------+--------------------+------+-------------+ | 1 | SIMPLE | country | ALL | PRIMARY | NULL | NULL | NULL | 239 | Using where | | 1 | SIMPLE | <subquery2> | eq_ref | <auto_key> | <auto_key> | 3 | world.country.Code | 1 | NULL | | 2 | MATERIALIZED | city | ALL | CountryCode | NULL | NULL | NULL | 4188 | Using where | +----+--------------+-------------+--------+---------------+------------+---------+--------------------+------+-------------+ 3 rows in set (0.00 sec)
ばっちりMATERIALIZED と出ています。
このEXPLAIN からは、まずid=2 のCity テーブルが全件スキャンされテンポラリテーブルとして実体化されたあとで、id=1 のcountry、
なおNLJ など、そもそもEXPLAIN については「漢のコンピュータ道」の記事を参照になさってください。(いつも大変お世話になっております)
http://nippondanji.blogspot.jp/2009/03/mysqlexplain.html
最初に4188件を全件スキャンしてインデックス付きのテンポラリテーブルを作る処理がいかほどのものか気になりますが、一度それができてしまえば、クエリ本体の処理に必要なアクセス行数の見積もりは 239 + (239 * 1)...いえ、
