今度はWHERE句に使う時（相関サブクエリ）の順番も考察してみる。
毎度の投げ遣りサンプルテーブルはFROM句サブクエリの時と一緒。
あと、今回はt1テーブルをまるっとtt1テーブルにもコピーして2つにしておく。
取り敢えずこんなサブクエリは如何だろうか。
mysql> SELECT * FROM t1 WHERE val IN (SELECT val FROM tt1 WHERE num BETWEEN 1 AND 10);
+-----+----------------------------------+
| num | val |
+-----+----------------------------------+
| 1 | c4ca4238a0b923820dcc509a6f75849b |
| 2 | c81e728d9d4c2f636f067f89cc14862c |
| 3 | eccbc87e4b5ce2fe28308fd9f2a7baf3 |
| 4 | a87ff679a2f3e71d9181a67b7542122c |
| 5 | e4da3b7fbbce2345d7772b0674a318d5 |
| 6 | 1679091c5a880faf6fb5e6087eb1b2dc |
| 7 | 8f14e45fceea167a5a36dedd4bea2543 |
| 8 | c9f0f895fb98ab9159f51fd0297e236d |
| 9 | 45c48cce2e2d7fbdea1afc51c7c6ad26 |
| 10 | d3d9446802a44259755d38e6d163e820 |
+-----+----------------------------------+
10 rows in set (21.69 sec)
mysql> EXPLAIN SELECT * FROM t1 WHERE val IN (SELECT val FROM tt1 WHERE num BETWEEN 1 AND 10);
+----+--------------------+-------+-------+---------------+---------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+-------+-------+---------------+---------+---------+------+--------+-------------+
| 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 984096 | Using where |
| 2 | DEPENDENT SUBQUERY | tt1 | range | PRIMARY | PRIMARY | 4 | NULL | 10 | Using where |
+----+--------------------+-------+-------+---------------+---------+---------+------+--------+-------------+
2 rows in set (0.00 sec)
想像通り、相関サブクエリ素敵に重い。
mysql> SHOW PROFILE;
+--------------------------------+----------+
| Status | Duration |
+--------------------------------+----------+
| starting | 0.000023 |
| Waiting for query cache lock | 0.000007 |
| checking query cache for query | 0.000092 |
| checking permissions | 0.000007 |
| checking permissions | 0.000008 |
| Opening tables | 0.000024 |
| System lock | 0.000018 |
| init | 0.000048 |
| optimizing | 0.000015 |
| statistics | 0.000014 |
| preparing | 0.000013 |
| executing | 0.000006 |
| Sending data | 0.000038 |
| optimizing | 0.000011 |
| statistics | 0.000039 |
| preparing | 0.000026 |
| executing | 0.000006 |
| Sending data | 0.000025 |
| executing | 0.000006 |
| Sending data | 0.000006 |
| executing | 0.000005 |
| Sending data | 0.000017 |
..
| executing | 0.000005 |
| Sending data | 0.000014 |
| executing | 0.000005 |
| Sending data | 0.000047 |
| end | 0.000010 |
| query end | 0.000008 |
| closing tables | 0.000011 |
| freeing items | 0.000050 |
| logging slow query | 0.000006 |
| logging slow query | 0.000063 |
| Opening table | 0.000075 |
| System lock | 0.000046 |
| cleaning up | 0.000008 |
+--------------------------------+----------+
2000025 rows in set (4.78 sec)
executingが100万回くらい発生している。
これというのも相関サブクエリは`外側のクエリを先に評価して'から`内側のクエリを毎回評価する'のが原因らしい。
（FROM句のサブクエリの時は`内側が先'だった）
100万行のt1をフェッチする。
num = 1の行を評価する為に、tt1をさらうクエリを実行(executing)
tt1をさらった結果を使って(Sending data) t1のnum = 1の行がマッチするかどうか判定。
num = 2の行を評価する為に ..
と、外側クエリの行数だけ内側クエリが実行される。
いくら内側クエリがPRIMARY KEYのレンジスキャンで10行しか返さないといっても、
100万回も反復問い合わせしてればそれは遅い罠。。ってことで。
FROM句サブクエリはDERIVEDだから一度にどかんで済んだけれど、
DEPENDENT SUBQUERYは反復になるので外側クエリが大きくなればなるほど地獄が見える。
こういうのはパズルだと思ってJOINに書き換えるしかない。
mysql> SELECT t1.* FROM t1 LEFT JOIN tt1 ON t1.val = tt1.val WHERE tt1.num BETWEEN 1 AND 10;
+-----+----------------------------------+
| num | val |
+-----+----------------------------------+
| 1 | c4ca4238a0b923820dcc509a6f75849b |
| 2 | c81e728d9d4c2f636f067f89cc14862c |
| 3 | eccbc87e4b5ce2fe28308fd9f2a7baf3 |
| 4 | a87ff679a2f3e71d9181a67b7542122c |
| 5 | e4da3b7fbbce2345d7772b0674a318d5 |
| 6 | 1679091c5a880faf6fb5e6087eb1b2dc |
| 7 | 8f14e45fceea167a5a36dedd4bea2543 |
| 8 | c9f0f895fb98ab9159f51fd0297e236d |
| 9 | 45c48cce2e2d7fbdea1afc51c7c6ad26 |
| 10 | d3d9446802a44259755d38e6d163e820 |
+-----+----------------------------------+
10 rows in set (1.49 sec)
mysql> show profile;
+--------------------------------+----------+
| Status | Duration |
+--------------------------------+----------+
| starting | 0.000025 |
| Waiting for query cache lock | 0.000008 |
| checking query cache for query | 0.000053 |
| checking permissions | 0.000006 |
| checking permissions | 0.000008 |
| Opening tables | 0.000022 |
| System lock | 0.000016 |
| init | 0.000022 |
| optimizing | 0.000015 |
| statistics | 0.000047 |
| preparing | 0.000047 |
| executing | 0.000010 |
| Sending data | 1.489763 |
| end | 0.000130 |
| query end | 0.000125 |
| closing tables | 0.000018 |
| freeing items | 0.000064 |
| logging slow query | 0.000006 |
| logging slow query | 0.000057 |
| Opening table | 0.000020 |
| System lock | 0.000046 |
| cleaning up | 0.000009 |
+--------------------------------+----------+
22 rows in set (0.08 sec)
mysql> EXPLAIN SELECT t1.* FROM t1 LEFT JOIN tt1 ON t1.val = tt1.val WHERE tt1.num BETWEEN 1 AND 10;
+----+-------------+-------+-------+---------------+---------+---------+------+--------+--------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+------+--------+--------------------------------+
| 1 | SIMPLE | tt1 | range | PRIMARY | PRIMARY | 4 | NULL | 10 | Using where |
| 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 984096 | Using where; Using join buffer |
+----+-------------+-------+-------+---------------+---------+---------+------+--------+--------------------------------+
2 rows in set (0.00 sec)
valにINDEX作ってないからjoin bufferになってるけど、Sending dataが1回だけになってるしずっと高速化している。
valにINDEX作ってやったら結果が返ってくるまでが0.00secまで落ちたので、多分これが正解。
相関サブクエリの方が直感的に意図が理解しやすいんだけどね。。
0 件のコメント :
コメントを投稿