MySQLのFROM句サブクエリの順番

他のDBMSはよく判らないけれど、MySQLのFROM句サブクエリは順番が大事。
ロジックを知らずにサブクエリを書くと、かなり遅くなることが多い。

取り敢えずサンプルテーブル。


mysql> DESC t1;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| num   | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| val   | char(32)         | YES  |     | NULL    |                |
+-------+------------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)




numは1 .. 1,000,000、valはmd5(num)。
バッファプールをあっためる為に一度テーブルスキャンしておく。
では考察。

mysql> SELECT sql_no_cache * FROM t1 WHERE num = 100;

+-----+----------------------------------+
| num | val                              |
+-----+----------------------------------+
| 100 | f899139df5e1059396431415e770c6dd |
+-----+----------------------------------+
1 row in set (0.00 sec)

mysql> SHOW PROFILE;

+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000091 |
| Opening table        | 0.000023 |
| System lock          | 0.000351 |
| checking permissions | 0.000011 |
| Opening tables       | 0.000019 |
| System lock          | 0.000017 |
| init                 | 0.000107 |
| optimizing           | 0.000019 |
| statistics           | 0.000280 |
| preparing            | 0.000025 |
| executing            | 0.000007 |
| Sending data         | 0.000016 |
| end                  | 0.000010 |
| query end            | 0.000008 |
| closing tables       | 0.000009 |
| freeing items        | 0.000046 |
| logging slow query   | 0.000006 |
| cleaning up          | 0.000008 |
+----------------------+----------+
18 rows in set (0.00 sec)

まずはサブクエリなし。Primary Keyの定数検索なので当然速い。

PROFILEの結果貼るまでもない感じだけど比較用。

mysql> SELECT sql_no_cache * FROM (SELECT * FROM t1) AS dummy WHERE num = 100;

+-----+----------------------------------+
| num | val                              |
+-----+----------------------------------+
| 100 | f899139df5e1059396431415e770c6dd |
+-----+----------------------------------+
1 row in set (0.73 sec)

次いでFROMの中身をサブクエリに書き換える。

とても遅い。

ここで最初の順番の話にに立ち返る。

まずはサブクエリ .. SELECT * FROM t1が展開される。100万行に対する振るフェッチ。

その100万行に対してPRIMARYのクエリ、SELECT .. FROM .. WHERE ..が実行される。

サブクエリの結果にはINDEXが無いので、これもまるまるフェッチ。

合計200万行フェッチしている算段。

EXPLAINで確認できる。

mysql> EXPLAIN SELECT sql_no_cache * FROM (SELECT * FROM t1) AS dummy WHERE num = 100;

+----+-------------+------------+------+---------------+------+---------+------+---------+-------------+

| id | select_type | table      | type | possible_keys | key  | key_len | ref  | rows    | Extra       |

+----+-------------+------------+------+---------------+------+---------+------+---------+-------------+

|  1 | PRIMARY     | <derived2> | ALL  | NULL          | NULL | NULL    | NULL | 1000000 | Using where |

|  2 | DERIVED     | t1         | ALL  | NULL          | NULL | NULL    | NULL | 1000521 |             |

+----+-------------+------------+------+---------------+------+---------+------+---------+-------------+

2 rows in set (0.74 sec)

EXPLAINじゃない方のPROFILIE。

mysql> SHOW PROFILE;

+---------------------------+----------+
| Status                    | Duration |
+---------------------------+----------+
| starting                  | 0.000079 |
| Opening table             | 0.000021 |
| System lock               | 0.000100 |
| checking permissions      | 0.000009 |
| Opening tables            | 0.000017 |
| System lock               | 0.000059 |
| optimizing                | 0.000008 |
| statistics                | 0.000011 |
| preparing                 | 0.000010 |
| executing                 | 0.000006 |
| Sending data              | 0.079637 |
| converting HEAP to MyISAM | 0.036832 |
| Sending data              | 0.484636 |
| init                      | 0.000032 |
| optimizing                | 0.000012 |
| statistics                | 0.000012 |
| preparing                 | 0.000018 |
| executing                 | 0.000006 |
| Sending data              | 0.098403 |
| end                       | 0.000095 |
| query end                 | 0.000057 |
| closing tables            | 0.000034 |
| removing tmp table        | 0.023328 |
| closing tables            | 0.000029 |
| freeing items             | 0.000050 |
| logging slow query        | 0.000006 |
| cleaning up               | 0.000007 |
+---------------------------+----------+
27 rows in set (0.01 sec)

Sending data = 行のフェッチ が2回発生していることと、
テンポラリテーブルがメモリ(この環境の設定では16M)に乗り切らずにMyISAM変換(DISKへの書込み)までかかってる。

泣きっ面に蜂状態。。

気を取り直してクエリを書き換える。

mysql> SELECT sql_no_cache * FROM (SELECT * FROM t1 WHERE num = 100) AS dummy;

+-----+----------------------------------+

| num | val                              |

+-----+----------------------------------+

| 100 | f899139df5e1059396431415e770c6dd |

+-----+----------------------------------+

1 row in set (0.00 sec)

サブクエリに書いてあったWHERE句を外側のクエリに移す。

サブクエリが遅い時に試す有名な手段。。だと思う。劇的に速い。

まずはSELECT * FROM t1 WHERE ..が展開される。

Primary Keyへの定数アクセスで1行が返る。

この1行に対してSELECT .. FROM ..が適用されて、1行をフェッチする。

合計2行のフェッチで済む様になった。

mysql> EXPLAIN SELECT sql_no_cache * FROM (SELECT * FROM t1 WHERE num = 100) AS dummy;

+----+-------------+------------+--------+---------------+---------+---------+------+------+-------+

| id | select_type | table      | type   | possible_keys | key     | key_len | ref  | rows | Extra |

+----+-------------+------------+--------+---------------+---------+---------+------+------+-------+

|  1 | PRIMARY     | <derived2> | system | NULL          | NULL    | NULL    | NULL |    1 |       |

|  2 | DERIVED     | t1         | const  | PRIMARY       | PRIMARY | 4       |      |    1 |       |

+----+-------------+------------+--------+---------------+---------+---------+------+------+-------+

2 rows in set (0.00 sec)

EXPLAINじゃない方のPROFILE。
フェッチ動作は2回あるけど、十分速い。

mysql> SHOW PROFILE;

+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000085 |
| Opening table        | 0.000022 |
| System lock          | 0.000082 |
| checking permissions | 0.000010 |
| Opening tables       | 0.000019 |
| System lock          | 0.000058 |
| optimizing           | 0.000012 |
| statistics           | 0.000045 |
| preparing            | 0.000012 |
| executing            | 0.000007 |
| Sending data         | 0.000031 |
| init                 | 0.000011 |
| optimizing           | 0.000006 |
| statistics           | 0.000008 |
| preparing            | 0.000008 |
| executing            | 0.000006 |
| Sending data         | 0.000044 |
| end                  | 0.000007 |
| query end            | 0.000008 |
| closing tables       | 0.000006 |
| removing tmp table   | 0.000010 |
| closing tables       | 0.000010 |
| freeing items        | 0.000099 |
| logging slow query   | 0.000011 |
| cleaning up          | 0.000007 |
+----------------------+----------+
25 rows in set (0.00 sec)

テンポラリテーブルは作ってるけどオンメモリだし1行だけなので全然痛くない。

というかFROM句サブクエリは結構JOINに書き換えられるのでJOINでやった方が往々にして速い。。

今回はFROM句サブクエリだけれど、

相関サブクエリはrowsの値を足し算じゃなくて掛け算になるから、

気を遣わないと加速度的に評価行数が増えて大変なことになる。

それはまた時間があれば。。


【2012/07/20】
相関サブクエリ（WHERE句のサブクエリ）の解析はこちら。