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

MySQL_8.0.22で Derived_Condition_Pushdown_Optimization を試した

hmatsu47
October 29, 2020

MySQL_8.0.22で Derived_Condition_Pushdown_Optimization を試した

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

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