More than 3 years have passed since last update.

MySQL 8.0.22 で Derived Condition Pushdown Optimization を試してみた

Last updated at 2020-11-11Posted at 2020-10-24

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 WHERE clause can sometimes be pushed down separately according to the rules already given. In the query 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, the predicate i > 10 references the column used in PARTITION BY, and so can be pushed down directly; min < 3 does not reference any columns in either of the PARTITION BY or GROUP BY clauses but can be pushed down as a HAVING condition. 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の行）結果を出すイメージです。

TREE形式でEXPLAIN（プッシュダウン無効）

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の行）結果を出すイメージです。

TREE形式でEXPLAIN（プッシュダウン有効）

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 を追加します。

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 文を流してみます。

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 の薄い本（無料で配布中！）

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up