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

MySQL 8.0.22 がリリースされました。

**[MySQL 8.0 の薄い本](https://github.com/hmatsu47/mysql80_no_usui_hon)** を発行している身としては新機能や改良を試して 8.0.22 対応版の内容に組み入れていかねば…と思ったのですが、

**MySQL 8.0.22 本体には目立った新機能が見当たらない！**
（速度に影響する退行バグが修正されている、などの話は聞きますが…）

ということで、無理やり地味な改良を取り上げてみました。

# Derived Condition Pushdown Optimization とは

 - サブクエリがある SQL 文の実行時に
 - サブクエリの外側にある`WHERE`句などの条件の一部を
 - サブクエリに（あらかじめ）適用することで処理の効率化を図る

という、オプティマイザの最適化処理のことです。

なるほど。よくわかりません（説明下手で申し訳ない）。

# 試してみる

公式マニュアルに適用例の説明があるので、それに沿って試してみます。

 - **[8.2.2.5 Derived Condition Pushdown Optimization](https://dev.mysql.com/doc/refman/8.0/en/derived-condition-pushdown-optimization.html)（MySQL 8.0 Reference Manual）**

なお、先に断っておきますが、**2020/10/24 現在、このマニュアル記載の適用例には一部誤りがあります**。すでに中の人がドキュメントの不具合報告をあげられているそうなので、そのうち一旦削除されるか修正が行われるはずです。

※その「誤りがある適用例」をこの記事で取り上げて説明するのでした。

---

**2020/11/11 追記：**
11/05 時点で公式マニュアルの該当箇所が修正（削除）されたという連絡を受けました。

---

## テストデータを準備する

公式マニュアルの適用例にはテーブル定義が示されていないのですが、今回は主キーのないテーブルを作成してテストデータを投入してみました。

```sql:テーブル定義
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)
```

```sql:データ挿入
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:

```sql:
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`が有効な環境ではいきなりエラーになります**。

```sql:エラーになる
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 を無効にして試してみます。

```sql:プッシュダウン無効
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`の行）結果を出すイメージです。

```sql: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 を有効にして試してみます。

```sql:プッシュダウン有効
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`の行）結果を出すイメージです。

```sql: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`句に「移動」させると結果が変わってしまう**ので、ここは書き換えられずに外側（主クエリ側）に残ります。

```sql:書き換え後
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 を追加します。

```sql: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 文を流します。

```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: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関連記事](https://hmatsu47.hatenablog.com/qiita_mysql80)**
 - **[MySQL 8.0 の薄い本](https://github.com/hmatsu47/mysql80_no_usui_hon)（無料で配布中！）**