Share
Embed
MySQL_8.0.22で Derived_Condition_Pushdown_Optimization を試した
MySQL Release note でわいわい言う勉強会 8.0.22 2020/10/29
Transcript
14 slides
-
MySQL 8.0.22で Derived Condition Pushdown Optimization を試した MySQL Release note
でわいわい言う勉強会 8.0.22 2020/10/29 まつひさ(hmatsu47) -
Derived Condition Pushdown Optimization • サブクエリの外側に記された条件( WHERE 句など)を、 • サブクエリに直接適用(プッシュダウン)することで、
• クエリの実行を高速化する …という機能です。が。 試し方をいきなり間違えたので、迷い道へ… 4 -
間違い③ 公式マニュアルの適用例が間違ってた • ONLY_FULL_GROUP_BY が有効だとエラーに • ONLY_FULL_GROUP_BY が無効だと結果が不定に ◦ 行数は変わるし、Empty
set になることも 7 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; ↓この結果が、 あるときは、 45 rows in set (0.76 sec) またあるときは、 891 rows in set (0.45 sec) さらに… Empty set (0.20 sec) -
エラー or 結果が不定になる原因は、 • GROUP_BY 句と Window 関数を単純に併用すると、 • SUM(◦)
OVER (PARTITION BY △) の ◦ が不定になるから そして、 • SUM(SUM(◦)) OVER (PARTITION BY △) で SUM() を二重化 すると、 ONLY_FULL_GROUP_BY 問題を避けられる 8 -
しかし、 • そのままでは Derived Condition Pushdown Optimization が効かない • 公式マニュアルの少し上を読むと…?
9 i じゃなくて j 、ですよね…? -
しかし、 • そのままでは Derived Condition Pushdown Optimization が効かない←すみません、実験ミスによる勘違いでした • 公式マニュアルの少し上を読むと…?
10 i じゃなくて j 、ですよね…? ↑どっちでも行けました -
というわけで、 • こうなりました 11 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 | | 430 | 58 | 1 | 190592 | | 96 | 58 | 1 | 190592 | (中略) | 290 | 974 | 1 | 380480 | | 309 | 986 | 2 | 280480 | | 323 | 994 | 2 | 356160 | +-----+-----+------+--------+ 44 rows in set (0.78 sec) -
というわけで、 • こうなりました 12 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) ↑ここにプッシュダウン ←これは外側に残る -
参考までに、PostgreSQL 11 の場合は、こう 13 subquery_test=> EXPLAIN SELECT * FROM (SELECT
i, j, MIN(k) AS min, SUM(SUM(k)) OVER (PARTITION BY j) AS sum FROM testschema.t1 GROUP BY i, j) AS dt WHERE j > 10 AND min < 3; QUERY PLAN -------------------------------------------------------------------------------------------------------------- Subquery Scan on dt (cost=61813.16..63733.16 rows=21333 width=44) Filter: (dt.min < 3) -> WindowAgg (cost=61813.16..62933.16 rows=64000 width=44) -> Sort (cost=61813.16..61973.16 rows=64000 width=20) Sort Key: t1.j -> Finalize GroupAggregate (cost=36070.71..56704.11 rows=64000 width=20) Group Key: t1.i, t1.j -> Gather Merge (cost=36070.71..54784.11 rows=128000 width=20) Workers Planned: 2 -> Partial GroupAggregate (cost=35070.68..39009.71 rows=64000 width=20) Group Key: t1.i, t1.j -> Sort (cost=35070.68..35730.49 rows=263922 width=12) Sort Key: t1.i, t1.j -> Parallel Seq Scan on t1 (cost=0.00..6793.33 rows=263922 width=12) Filter: (j > 10) (15 rows) 少し表現は違うものの MySQL 8.0 と同様に -
でも実は、 • この適用例は全く速くなりません! • 「Derived Condition Pushdown Optimization の意味ない じゃん!」となるとマズいので、↓の記事の最後では速くなる
例を示しています。 ◦ MySQL 8.0.22 で Derived Condition Pushdown Optimization を試してみた ◦ https://qiita.com/hmatsu47/items/c15b1c778d050913201d 14