Upgrade to Pro — share decks privately, control downloads, hide ads and more …

MySQL_8.0.22で Derived_Condition_Pushdown_Optimi...

Avatar for hmatsu47 hmatsu47
October 29, 2020

MySQL_8.0.22で Derived_Condition_Pushdown_Optimization を試した

MySQL Release note でわいわい言う勉強会 8.0.22 2020/10/29

Avatar for hmatsu47

hmatsu47

October 29, 2020
Tweet

More Decks by hmatsu47

Other Decks in Technology

Transcript

  1. MySQL 8.0.22で Derived Condition Pushdown Optimization を試した MySQL Release note

    でわいわい言う勉強会 8.0.22 2020/10/29 まつひさ(hmatsu47)
  2. Derived Condition Pushdown Optimization • サブクエリの外側に記された条件( WHERE 句など)を、 • サブクエリに直接適用(プッシュダウン)することで、

    • クエリの実行を高速化する …という機能です。が。  試し方をいきなり間違えたので、迷い道へ… 4
  3. 間違い③ 公式マニュアルの適用例が間違ってた • 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)
  4. エラー or 結果が不定になる原因は、 • GROUP_BY 句と Window 関数を単純に併用すると、 • SUM(◦)

    OVER (PARTITION BY △) の ◦ が不定になるから そして、 • SUM(SUM(◦)) OVER (PARTITION BY △) で SUM() を二重化 すると、 ONLY_FULL_GROUP_BY 問題を避けられる 8
  5. というわけで、 • こうなりました 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)
  6. というわけで、 • こうなりました 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) ↑ここにプッシュダウン ←これは外側に残る
  7. 参考までに、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 と同様に
  8. でも実は、 • この適用例は全く速くなりません! • 「Derived Condition Pushdown Optimization の意味ない じゃん!」となるとマズいので、↓の記事の最後では速くなる

    例を示しています。 ◦ MySQL 8.0.22 で Derived Condition Pushdown Optimization を試してみた ◦ https://qiita.com/hmatsu47/items/c15b1c778d050913201d 14