MySQL 8.0.22 がリリースされました。
MySQL 8.0 の薄い本 を発行している身としては新機能や改良を試して 8.0.22 対応版の内容に組み入れていかねば…と思ったのですが、
MySQL 8.0.22 本体には目立った新機能が見当たらない！
（速度に影響する退行バグが修正されている、などの話は聞きますが…）
ということで、無理やり地味な改良を取り上げてみました。
Derived Condition Pushdown Optimization とは
- サブクエリがある SQL 文の実行時に
- サブクエリの外側にある
WHERE句などの条件の一部を - サブクエリに（あらかじめ）適用することで処理の効率化を図る
という、オプティマイザの最適化処理のことです。
なるほど。よくわかりません（説明下手で申し訳ない）。
試してみる
公式マニュアルに適用例の説明があるので、それに沿って試してみます。
- 8.2.2.5 Derived Condition Pushdown Optimization（MySQL 8.0 Reference Manual）
なお、先に断っておきますが、2020/10/24 現在、このマニュアル記載の適用例には一部誤りがあります。すでに中の人がドキュメントの不具合報告をあげられているそうなので、そのうち一旦削除されるか修正が行われるはずです。
※その「誤りがある適用例」をこの記事で取り上げて説明するのでした。
2020/11/11 追記：
11/05 時点で公式マニュアルの該当箇所が修正（削除）されたという連絡を受けました。
テストデータを準備する
公式マニュアルの適用例にはテーブル定義が示されていないのですが、今回は主キーのないテーブルを作成してテストデータを投入してみました。
mysql> CREATE DATABASE subquery_test;
Query OK, 1 row affected (0.02 sec)
mysql> USE subquery_test;
Database changed
mysql> CREATE TABLE t1 (i INT NOT NULL, j INT NOT NULL, k INT NOT NULL);
Query OK, 0 rows affected (0.12 sec)
INSERT INTO t1 VALUES(1000 * RAND(), 1000 * RAND(), 1000 * RAND());
※これで 2 万行挿入後、INSERT INTO ... SELECT を 5 回繰り返して 64 万行に増やした
適用例を試してみる（Window 関数を含むケース）
ここでは、いきなりページの最後の例として示されている、「Window 関数を含むケースの適用例」を試してみます。
ただし、前述のとおり誤りがある適用例なので、そのうちマニュアルから消える or 修正されるはずです。2020/10/24 現在は、以下のような記述になっています。
In cases in which the derived table uses a window function, predicates in the outer
WHEREclause can sometimes be pushed down separately according to the rules already given. In the querySELECT * FROM (SELECT i, j, MIN(k) AS min, SUM(k) OVER (PARTITION BY i) AS sum FROM t1 GROUP BY i, j) AS dt WHERE i > 10 AND min < 3, the predicatei > 10references the column used inPARTITION BY, and so can be pushed down directly;min < 3does not reference any columns in either of thePARTITION BYorGROUP BYclauses but can be pushed down as aHAVINGcondition. This means that the query can be rewritten like this:
SELECT * FROM (
SELECT i, j, MIN(k) AS min, SUM(k) OVER (PARTITION BY i) AS sum
FROM t1
WHERE i > 10
GROUP BY i, j
HAVING MIN < 3
) AS dt;
↑は Derived Condition Pushdown Optimization 適用によりこのようにクエリが書き換えられて実行される、という説明ですがこれも間違っています。そもそも別名のminが大文字でMINになっている時点で不穏ですね…。
さて、書き換え前の SQL 文を実行すると、ONLY_FULL_GROUP_BYが有効な環境ではいきなりエラーになります。
mysql> SELECT * FROM (SELECT i, j, MIN(k) AS min, SUM(k) OVER (PARTITION BY i) AS sum FROM t1 GROUP BY i, j) AS dt WHERE i > 10 AND min < 3;
ERROR 1055 (42000): Expression #4 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'subquery_test.t1.k' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
4 列目の値、SUM(k) OVER (PARTITION BY i) AS sumの k が不定になるためで、ここはSUM(SUM(k)) ...にする必要があります。
※余談ですが、ONLY_FULL_GROUP_BYを無効にして無理やり実行すると、（インデックスを作るなどして）実行計画が変化する度に結果の行数が変わる（場合によってはEmpty set）という、なかなか恐ろしい挙動に…。
さらに、WHERE句の条件を付加する前の SQL 文と比較してみると、PARTITION BY iはPARTITION BY jの、WHERE i > 10はWHERE j > 10の間違いであることが分かります（これらがiのままだと、エラーなく実行できたとしても Derived Condition Pushdown Optimization は働きません）。
2020/10/26 訂正：
ここはPARTITION BY i・WHERE i > 10のままでも Derived Condition Pushdown Optimization が働きました（うっかりPARTITION BY j・WHERE i > 10で実験していました）。ただし、↓の適用例はPARTITION BY j・WHERE j > 10に変更した状態で実行しています。
というわけで、まずはNO_DERIVED_CONDITION_PUSHDOWNヒントを付けて Derived Condition Pushdown Optimization を無効にして試してみます。
mysql> SELECT /*+ NO_DERIVED_CONDITION_PUSHDOWN() */ * FROM (SELECT i, j, MIN(k) AS min, SUM(SUM(k)) OVER (PARTITION BY j) AS sum FROM t1 GROUP BY i, j) AS dt WHERE j > 10 AND min < 3;
+-----+-----+------+--------+
| i | j | min | sum |
+-----+-----+------+--------+
| 380 | 28 | 1 | 244608 |
| 94 | 56 | 0 | 225664 |
（中略）
| 309 | 986 | 2 | 280480 |
| 323 | 994 | 2 | 356160 |
+-----+-----+------+--------+
44 rows in set (0.78 sec)
mysql> EXPLAIN SELECT /*+ NO_DERIVED_CONDITION_PUSHDOWN() */ * FROM (SELECT i, j, MIN(k) AS min, SUM(SUM(k)) OVER (PARTITION BY j) AS sum FROM t1 GROUP BY i, j) AS dt WHERE j > 10 AND min < 3\G
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: <derived2>
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 638976
filtered: 11.11
Extra: Using where
*************************** 2. row ***************************
id: 2
select_type: DERIVED
table: t1
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 638976
filtered: 100.00
Extra: Using temporary; Using filesort
2 rows in set, 2 warnings (0.00 sec)
サブクエリを処理する際に 64 万行分読み込んで同じ行数の派生テーブルを作り（id:2の行）、その全行からフィルタリングして（id:1の行）結果を出すイメージです。
mysql> EXPLAIN FORMAT=TREE SELECT /*+ NO_DERIVED_CONDITION_PUSHDOWN() */ * FROM (SELECT i, j, MIN(k) AS min, SUM(SUM(k)) OVER (PARTITION BY j) AS sum FROM t1 GROUP BY i, j) AS dt WHERE j > 10 AND min < 3\G
*************************** 1. row ***************************
EXPLAIN: -> Filter: ((dt.j > 10) and (dt.min < 3))
-> Table scan on dt (cost=71887.30 rows=638976)
-> Materialize
-> Window aggregate with buffering: sum(sum(t1.k)) OVER (PARTITION BY t1.j )
-> Sort: t1.j
-> Table scan on <temporary>
-> Aggregate using temporary table
-> Table scan on t1 (cost=65468.60 rows=638976)
1 row in set (0.00 sec)
Filter: ((dt.j > 10) and (dt.min < 3))が一番上に来ています（最後にフィルタリング）。
次に、Derived Condition Pushdown Optimization を有効にして試してみます。
mysql> SELECT * FROM (SELECT i, j, MIN(k) AS min, SUM(SUM(k)) OVER (PARTITION BY j) AS sum FROM t1 GROUP BY i, j) AS dt WHERE j > 10 AND min < 3;
+-----+-----+------+--------+
| i | j | min | sum |
+-----+-----+------+--------+
| 380 | 28 | 1 | 244608 |
| 94 | 56 | 0 | 225664 |
（中略）
| 309 | 986 | 2 | 280480 |
| 323 | 994 | 2 | 356160 |
+-----+-----+------+--------+
44 rows in set (0.97 sec)
mysql> EXPLAIN SELECT * FROM (SELECT i, j, MIN(k) AS min, SUM(SUM(k)) OVER (PARTITION BY j) AS sum FROM t1 GROUP BY i, j) AS dt WHERE j > 10 AND min < 3\G
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: <derived2>
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 212970
filtered: 33.33
Extra: Using where
*************************** 2. row ***************************
id: 2
select_type: DERIVED
table: t1
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 638976
filtered: 33.33
Extra: Using where; Using temporary; Using filesort
2 rows in set, 2 warnings (0.00 sec)
サブクエリを処理する際に（64 万行分読み込んだものを）フィルタリングして 1/3 程度に絞り込んで派生テーブルを作り（id:2の行）、20 万行強の派生テーブルからさらにフィルタリングして（id:1の行）結果を出すイメージです。
mysql> EXPLAIN FORMAT=TREE SELECT * FROM (SELECT i, j, MIN(k) AS min, SUM(SUM(k)) OVER (PARTITION BY j) AS sum FROM t1 GROUP BY i, j) AS dt WHERE j > 10 AND min < 3\G
*************************** 1. row ***************************
EXPLAIN: -> Filter: (dt.min < 3)
-> Table scan on dt (cost=23961.62 rows=212970)
-> Materialize
-> Window aggregate with buffering: sum(sum(t1.k)) OVER (PARTITION BY t1.j )
-> Sort: t1.j
-> Table scan on <temporary>
-> Aggregate using temporary table
-> Filter: (t1.j > 10) (cost=65468.60 rows=212971)
-> Table scan on t1 (cost=65468.60 rows=638976)
1 row in set (0.00 sec)
Filter: (t1.j > 10)が下のほうに来ました（派生テーブルを作る時点でフィルタリング）。Filter: (dt.min < 3)のほうは一番上のままです（最後にフィルタリング）。
見た目上、処理が効率化されているので高速化する…かと思うのですが、全然高速化していませんね（かえって遅い？ように見えますが、何度か実行して平均を取ると、結果は「ほぼ同じ」でした）。
この例では（動きを分かりやすく見せる目的で）サブクエリの内容を他テーブルと結合することなくそのまま主クエリで処理しているために、サブクエリによって作られる派生テーブルの行数の多寡が SQL 文全体の処理効率に与える影響が小さいことが原因ではないかと思います。
テストに使うテーブルの容量の多少の「嵩増し」や他テーブルとの結合などを試してみましたが、残念ながら速くなることが（誤差の範囲を除いてほぼ）ありませんでした。現状では、「プッシュダウン処理による負荷増≒派生テーブル行数減による負荷減」ということなのでしょうか。派生テーブルがメモリではなくディスクに生成されるサイズになると結果が変わってきそうな気はしますが。
結局、Derived Condition Pushdown Optimization での書き換え後の SQL 文は以下のようになります。min < 3をサブクエリ側のHAVING句に「移動」させると結果が変わってしまうので、ここは書き換えられずに外側（主クエリ側）に残ります。
SELECT * FROM (
SELECT i, j, MIN(k) AS min, SUM(SUM(k)) OVER (PARTITION BY j) AS sum
FROM t1
WHERE j > 10
GROUP BY i, j
) AS dt
WHERE min < 3;
2020/10/25 追記：
もっとシンプルな適用例を試してみる
このままでは、「Derived Condition Pushdown Optimization は効果が出ないのでは？」と誤解されそうなので、普通に考えて効きそうな例を試してみます。
まず最初に、先ほどのテーブルに i 列と j 列 の複合 INDEX を追加します。
mysql> ALTER TABLE t1 ADD INDEX (i, j);
Query OK, 0 rows affected (3.52 sec)
Records: 0 Duplicates: 0 Warnings: 0
この状態で、Derived Condition Pushdown Optimization を無効にして、i と j の範囲のみを条件とする SQL 文を流します。
mysql> SELECT /*+ NO_DERIVED_CONDITION_PUSHDOWN() */ * FROM (SELECT i, j, SUM(k) FROM t1 GROUP BY i, j) AS dt WHERE i < 10 AND j > 990;
+---+-----+--------+
| i | j | SUM(k) |
+---+-----+--------+
| 9 | 998 | 30784 |
+---+-----+--------+
1 row in set (1.05 sec)
mysql> EXPLAIN SELECT /*+ NO_DERIVED_CONDITION_PUSHDOWN() */ * FROM (SELECT i, j, SUM(k) FROM t1 GROUP BY i, j) AS dt WHERE i < 10 AND j > 990\G
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: <derived2>
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 638976
filtered: 11.11
Extra: Using where
*************************** 2. row ***************************
id: 2
select_type: DERIVED
table: t1
partitions: NULL
type: index
possible_keys: i
key: i
key_len: 8
ref: NULL
rows: 638976
filtered: 100.00
Extra: NULL
2 rows in set, 1 warning (0.00 sec)
mysql> EXPLAIN FORMAT=TREE SELECT /*+ NO_DERIVED_CONDITION_PUSHDOWN() */ * FROM (SELECT i, j, SUM(k) FROM t1 GROUP BY i, j) AS dt WHERE i < 10 AND j > 990\G
*************************** 1. row ***************************
EXPLAIN: -> Filter: ((dt.i < 10) and (dt.j > 990))
-> Table scan on dt (cost=71887.30 rows=638976)
-> Materialize
-> Group aggregate: sum(t1.k)
-> Index scan on t1 using i (cost=64290.35 rows=638976)
1 row in set (0.00 sec)
派生テーブル生成が INDEX に対するフルスキャンになっています。
次に、Derived Condition Pushdown Optimization を有効にして、同じ SQL 文を流してみます。
mysql> SELECT * FROM (SELECT i, j, SUM(k) FROM t1 GROUP BY i, j) AS dt WHERE i < 10 AND j > 990;
+---+-----+--------+
| i | j | SUM(k) |
+---+-----+--------+
| 9 | 998 | 30784 |
+---+-----+--------+
1 row in set (0.00 sec)
mysql> EXPLAIN SELECT * FROM (SELECT i, j, SUM(k) FROM t1 GROUP BY i, j) AS dt WHERE i < 10 AND j > 990\G
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: <derived2>
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 2058
filtered: 100.00
Extra: NULL
*************************** 2. row ***************************
id: 2
select_type: DERIVED
table: t1
partitions: NULL
type: range
possible_keys: i
key: i
key_len: 4
ref: NULL
rows: 6176
filtered: 33.33
Extra: Using index condition
2 rows in set, 1 warning (0.00 sec)
mysql> EXPLAIN FORMAT=TREE SELECT * FROM (SELECT i, j, SUM(k) FROM t1 GROUP BY i, j) AS dt WHERE i < 10 AND j > 990\G
*************************** 1. row ***************************
EXPLAIN: -> Table scan on dt (cost=234.03 rows=2058)
-> Materialize
-> Group aggregate: sum(t1.k)
-> Index range scan on t1 using i, with index condition: ((t1.i < 10) and (t1.j > 990)) (cost=2779.46 rows=6176)
1 row in set (0.00 sec)
今度は INDEX の範囲検索によって 1/100 程度に絞り込まれた状態で派生テーブルが生成され、SQL 文も高速に実行されました。
- Qiitaに投稿したMySQL 8.0関連記事
- MySQL 8.0 の薄い本（無料で配布中！）