MySQL 5.7のoptimizer_switch、derived_mergeとは何ぞや

このエントリはMySQL Casual Advent Calendar 2015の8日目です。

MySQL 5.7.6からoptimizer_switchにderived_mergeが追加されデフォルトで有効になっている。基本的にこれはほっといたらだいたいサブクエリが速くなるやつなので気にしなくてもいいんですが、ちょっと非互換があるのでさくっと説明します。

root@localhost [mysqlcasual] > CREATE TABLE t1 (a int);
Query OK, 0 rows affected (0.03 sec)

root@localhost [mysqlcasual] > CREATE TABLE t2 (b int);
Query OK, 0 rows affected (0.03 sec)

root@localhost [mysqlcasual] > INSERT INTO t1 VALUES (1),(2),(3),(4),(5);
Query OK, 5 rows affected (0.02 sec)
Records: 5  Duplicates: 0  Warnings: 0

root@localhost [mysqlcasual] > INSERT INTO t2 VALUES (1),(2),(3),(4),(5);
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

これまでFROM句のサブクエリは一旦マテリアライズされてから外側のクエリとかWHERE句とかと結合されていた。

root@localhost [mysqlcasual] > SET optimizer_switch = 'derived_merge=off';
Query OK, 0 rows affected (0.00 sec)

root@localhost [mysqlcasual] > EXPLAIN SELECT * FROM t1 JOIN (SELECT * FROM t2) dt;
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+---------------------------------------+
| id | select_type | table      | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                 |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+---------------------------------------+
|  1 | PRIMARY     | t1         | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    5 |   100.00 | NULL                                  |
|  1 | PRIMARY     | <derived2> | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    5 |   100.00 | Using join buffer (Block Nested Loop) |
|  2 | DERIVED     | t2         | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    5 |   100.00 | NULL                                  |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+---------------------------------------+
3 rows in set, 1 warning (0.00 sec)

これがderived_merge=onだとマテリアライズせずに外側の条件とマージできそうなときはマージされるようになるのだ！

root@localhost [mysqlcasual] > SET optimizer_switch = 'derived_merge=on';
Query OK, 0 rows affected (0.00 sec)

root@localhost [mysqlcasual] > EXPLAIN SELECT * FROM t1 JOIN (SELECT * FROM t2) dt;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                 |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    5 |   100.00 | NULL                                  |
|  1 | SIMPLE      | t2    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    5 |   100.00 | Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------------+
2 rows in set, 1 warning (0.00 sec)

これでサブクエリが多い日も安心！！

しかしその代償に以下の更新クエリが通らなくなった。

root@localhost [mysqlcasual] > UPDATE t2 SET b=1 WHERE b IN (SELECT b FROM (SELECT * FROM t2) dt WHERE b=1);
ERROR 1093 (HY000): You can't specify target table 't2' for update in FROM clause

どうもMySQLにはひとつの更新系クエリ(UPDATE, DELETE)で更新するテーブルと同じテーブルをFROM句の中で両方同時に参照できないという制約があるらしく、いままでマテリアライズされて別テーブルになってたから通ってたクエリが最適化によってマージされてそのまま参照されることでこの制約に引っかかるようになるみたいです。

回避策としては、derived_merge=offにするか、サブクエリをマージできない(マテリアライズされる)クエリに書き換えるとよいです(DISTINCTã‚„LIMITをつけるとマージできなくなる)。

root@localhost [mysqlcasual] > UPDATE t2 SET b=1 WHERE b IN (SELECT b FROM (SELECT DISTINCT * FROM t2) dt WHERE b=1);
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

root@localhost [mysqlcasual] > SET optimizer_switch = 'derived_merge=off';
Query OK, 0 rows affected (0.00 sec)

root@localhost [mysqlcasual] > UPDATE t2 SET b=1 WHERE b IN (SELECT b FROM (SELECT * FROM t2) dt WHERE b=1);
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

じつはVIEWã‚‚derived tableの仲間なので、ALGORITHM=MERGEなVIEWもこの制約に引っかかるので注意です。

• MySQL 5.6 リファレンスマニュアル :: 20.5.2 ビュー処理アルゴリズム