MySQL_8.0.22で Derived_Condition_Pushdown_Optimization を試した

Transcript

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

   でわいわい言う勉強会 8.0.22　2020/10/29 まつひさ（hmatsu47）

2. MySQL 8.0.22 のリリースノートを見て、 • 困った、取り上げるネタがない • しょうがない、何か選ぶか… …というわけで、 2

3. MySQL 8.0.22 のリリースノートを見て、 • 困った、取り上げるネタがない • しょうがない、何か選ぶか… …というわけで、 3 （リリースノート「Optimizer

   Notes」より）

4. Derived Condition Pushdown Optimization • サブクエリの外側に記された条件（ WHERE 句など）を、 • サブクエリに直接適用（プッシュダウン）することで、

   • クエリの実行を高速化する …という機能です。が。 　試し方をいきなり間違えたので、迷い道へ… 4

5. 間違い① いきなり難しい適用例にチャレンジ • Window 関数を含む例にチャレンジ ◦ 公式マニュアルの最後に記されている（いた？） ◦ https://dev.mysql.com/doc/refman/8.0/en/derived-condition-pushdown-optimization.html 5

6. 間違い② テスト環境の設定がデフォルトと違った • ONLY_FULL_GROUP_BY が無効になっていた • 結果として、間違い③に気付くのが遅れた 6 ↑この時点で十分怪しい

7. 間違い③ 公式マニュアルの適用例が間違ってた • 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)

8. エラー or 結果が不定になる原因は、 • GROUP_BY 句と Window 関数を単純に併用すると、 • SUM(◦)

   OVER (PARTITION BY △) の ◦ が不定になるから そして、 • SUM(SUM(◦)) OVER (PARTITION BY △) で SUM() を二重化 すると、 ONLY_FULL_GROUP_BY 問題を避けられる 8

9. しかし、 • そのままでは Derived Condition Pushdown Optimization が効かない • 公式マニュアルの少し上を読むと…？

   9 i じゃなくて j 、ですよね…？

10. しかし、 • そのままでは Derived Condition Pushdown Optimization が効かない←すみません、実験ミスによる勘違いでした • 公式マニュアルの少し上を読むと…？

    10 i じゃなくて j 、ですよね…？ ↑どっちでも行けました

11. というわけで、 • こうなりました 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. というわけで、 • こうなりました 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) ↑ここにプッシュダウン ←これは外側に残る

13. 参考までに、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 と同様に

14. でも実は、 • この適用例は全く速くなりません！ • 「Derived Condition Pushdown Optimization の意味ない じゃん！」となるとマズいので、↓の記事の最後では速くなる

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