MySQL 8.0.14 で LATERAL 句を使ってみる

先日、緊急リリースで MySQL 8.0.15 が出たばかりですが（注：残念ながら「待ち焦がれた CHECK 制約」は 8.0.16 に先送りとなりました）、8.0.14 で`LATERAL`句がサポートされたという話が聞こえてきたので（自分では見逃してました）、とりあえず試してみました。

 * **[Support for LATERAL derived tables added to MySQL 8.0.14](https://mysqlserverteam.com/support-for-lateral-derived-tables-added-to-mysql-8-0-14/)（MySQL Server Blog）**

# やってみる

本来なら機能の解説を書いてから「やってみる」なのですが…うまく説明できない（笑）。ので、一応参考になりそうな記事へのリンクを付けておきます。

 * **[LATERALを使ってみよう](https://lets.postgresql.jp/documents/technical/lateral/1)（Let's Postgres）**

このページに、

> 内部的な挙動としては、まずLATERAL以外のサブクエリやリレーションが評価され結果が生成されます。その後、その結果の1行ごとにLATERAL内のサブクエリが評価されます。いわば、非LATERALをouter、LATERALをinnerとしたNestLoop Joinのような処理となります。 

と書かれているような処理になるわけです。

## テスト：年月・店舗別の売上集計っぽいやつ

以下のとおり、テーブルを作ってテストデータを流し込みます。

```sql:テスト準備
mysql> CREATE DATABASE lateral_test;
Query OK, 1 row affected (0.00 sec)

mysql> USE lateral_test;
Database changed
mysql> CREATE TABLE shop (
    ->     shop_id INT PRIMARY KEY NOT NULL,
    ->     shop_name VARCHAR(20)
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE sales (
    ->     sales_id INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
    ->     sales_date DATE NOT NULL,
    ->     shop_id INT NOT NULL,
    ->     product_id INT NOT NULL,
    ->     total_price INT NOT NULL,
    ->     total_count INT NOT NULL
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO shop SET shop_id=1, shop_name='東京本店';
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO shop SET shop_id=2, shop_name='横浜支店';
Query OK, 1 row affected (0.00 sec)

（以降省略）

mysql> SELECT * FROM shop;
+---------+-----------------+
| shop_id | shop_name       |
+---------+-----------------+
|       1 | 東京本店        |
|       2 | 横浜支店        |
|       3 | 大阪支店        |
|       4 | 名古屋支店      |
|       5 | 札幌支店        |
|       6 | 福岡支店        |
|       7 | 仙台支店        |
|       8 | 神戸支店        |
+---------+-----------------+
8 rows in set (0.00 sec)

mysql> SELECT * FROM sales;
+----------+------------+---------+------------+-------------+-------------+
| sales_id | sales_date | shop_id | product_id | total_price | total_count |
+----------+------------+---------+------------+-------------+-------------+
|        1 | 2019-01-03 |       2 |          5 |       12000 |           3 |
|        2 | 2019-01-03 |       4 |          3 |       10000 |           5 |
|        3 | 2019-01-03 |       1 |          5 |        9000 |           2 |
|        4 | 2019-01-05 |       5 |          3 |       15000 |           8 |
|        5 | 2019-01-05 |       8 |          2 |       18000 |           4 |
|        6 | 2019-01-08 |       7 |          1 |       12000 |           5 |
|        7 | 2019-01-09 |       6 |          2 |       12000 |           3 |
|        8 | 2019-01-10 |       3 |          4 |       22000 |           8 |
|        9 | 2019-01-10 |       1 |          3 |       12000 |           3 |
|       10 | 2019-01-10 |       2 |          5 |       13000 |           5 |
|       11 | 2019-01-10 |       3 |          4 |       18000 |           9 |
|       12 | 2019-01-10 |       7 |          2 |       12000 |           4 |
|       13 | 2019-01-15 |       4 |          5 |       16000 |           8 |
|       14 | 2019-01-17 |       2 |          4 |       12000 |           8 |
|       15 | 2019-01-19 |       6 |          1 |       15000 |           5 |
|       16 | 2019-01-19 |       7 |          1 |       14000 |           7 |
|       17 | 2019-01-25 |       3 |          2 |       16000 |           4 |
|       18 | 2019-01-28 |       8 |          4 |       15000 |           3 |
|       19 | 2019-01-30 |       1 |          3 |       17500 |           5 |
|       20 | 2019-01-31 |       5 |          5 |       21500 |           5 |
|       21 | 2019-02-01 |       5 |          4 |       10800 |           6 |
|       22 | 2019-02-01 |       7 |          3 |       12800 |           8 |
|       23 | 2019-02-03 |       2 |          2 |       16400 |           4 |
|       24 | 2019-02-03 |       1 |          2 |       14400 |           4 |
|       25 | 2019-02-04 |       3 |          1 |       19200 |           8 |
|       26 | 2019-02-04 |       2 |          1 |       19800 |           6 |
|       27 | 2019-02-04 |       1 |          2 |       16800 |           8 |
|       28 | 2019-02-05 |       6 |          5 |       24000 |          12 |
|       29 | 2019-02-05 |       8 |          4 |       20000 |          10 |
|       30 | 2019-02-05 |       7 |          2 |       18000 |           8 |
+----------+------------+---------+------------+-------------+-------------+
30 rows in set (0.00 sec)
```

### `LATERAL`を使わずに普通にやってみる

まずは普通に`GROUP BY`句で集計してみます。

```sql:普通にやってみる
mysql> SELECT
    ->     DATE_FORMAT(sa.sales_date, '%Y%m') ym, sh.shop_id, sh.shop_name, SUM(sa.total_price) tp, SUM(sa.total_count) tc
    -> FROM
    ->     shop sh, sales sa
    -> WHERE
    ->     sh.shop_id = sa.shop_id
    -> GROUP BY
    ->     sh.shop_id, ym
    -> ORDER BY
    ->     ym, sh.shop_id;
+--------+---------+-----------------+-------+------+
| ym     | shop_id | shop_name       | tp    | tc   |
+--------+---------+-----------------+-------+------+
| 201901 |       1 | 東京本店        | 38500 |   10 |
| 201901 |       2 | 横浜支店        | 37000 |   16 |
| 201901 |       3 | 大阪支店        | 56000 |   21 |
| 201901 |       4 | 名古屋支店      | 26000 |   13 |
| 201901 |       5 | 札幌支店        | 36500 |   13 |
| 201901 |       6 | 福岡支店        | 27000 |    8 |
| 201901 |       7 | 仙台支店        | 38000 |   16 |
| 201901 |       8 | 神戸支店        | 33000 |    7 |
| 201902 |       1 | 東京本店        | 31200 |   12 |
| 201902 |       2 | 横浜支店        | 36200 |   10 |
| 201902 |       3 | 大阪支店        | 19200 |    8 |
| 201902 |       5 | 札幌支店        | 10800 |    6 |
| 201902 |       6 | 福岡支店        | 24000 |   12 |
| 201902 |       7 | 仙台支店        | 30800 |   16 |
| 201902 |       8 | 神戸支店        | 20000 |   10 |
+--------+---------+-----------------+-------+------+
15 rows in set (0.00 sec)
```

はい、至って普通です（笑）。

### `LATERAL`を使ってやってみる

正直、このケースでは`LATERAL`句の中にも`GROUP BY`句が入ってしまうため例としては微妙ですが…。

```sql:LATERALでやってみる
mysql> SELECT
    ->     t.ym, sh.shop_id, sh.shop_name, t.tp, t.tc
    -> FROM
    ->     shop sh,
    ->     LATERAL (
    ->         SELECT
    ->             DATE_FORMAT(sa.sales_date, '%Y%m') ym, SUM(sa.total_price) tp, SUM(sa.total_count) tc
    ->         FROM
    ->             sales sa
    ->         WHERE
    ->             sh.shop_id = sa.shop_id
    ->         GROUP BY
    ->             ym
    ->     ) t
    -> ORDER BY
    ->     t.ym, sh.shop_id;
+--------+---------+-----------------+-------+------+
| ym     | shop_id | shop_name       | tp    | tc   |
+--------+---------+-----------------+-------+------+
| 201901 |       1 | 東京本店        | 38500 |   10 |
| 201901 |       2 | 横浜支店        | 37000 |   16 |
| 201901 |       3 | 大阪支店        | 56000 |   21 |
| 201901 |       4 | 名古屋支店      | 26000 |   13 |
| 201901 |       5 | 札幌支店        | 36500 |   13 |
| 201901 |       6 | 福岡支店        | 27000 |    8 |
| 201901 |       7 | 仙台支店        | 38000 |   16 |
| 201901 |       8 | 神戸支店        | 33000 |    7 |
| 201902 |       1 | 東京本店        | 31200 |   12 |
| 201902 |       2 | 横浜支店        | 36200 |   10 |
| 201902 |       3 | 大阪支店        | 19200 |    8 |
| 201902 |       5 | 札幌支店        | 10800 |    6 |
| 201902 |       6 | 福岡支店        | 24000 |   12 |
| 201902 |       7 | 仙台支店        | 30800 |   16 |
| 201902 |       8 | 神戸支店        | 20000 |   10 |
+--------+---------+-----------------+-------+------+
15 rows in set (0.00 sec)
```

当然、結果は同じですが、イメージはついたでしょうか？

この程度の内容であれば`LATERAL`句を使わないほうがシンプルですが、もう少し複雑なものになれば、`LATERAL`句を使うほうが処理の内容を分かりやすく記述できるケースがあると思います。

## `EXPLAIN`を比較してみる

ついでに、`EXPLAIN`の結果も比較してみます。

```sql:EXPLAIN比較
mysql> EXPLAIN
    -> SELECT
    ->     DATE_FORMAT(sa.sales_date, '%Y%m') ym, sh.shop_id, sh.shop_name, SUM(sa.total_price) tp, SUM(sa.total_count) tc
    -> FROM
    ->     shop sh, sales sa
    -> WHERE
    ->     sh.shop_id = sa.shop_id
    -> GROUP BY
    ->     sh.shop_id, ym
    -> ORDER BY
    ->     ym, sh.shop_id;
+----+-------------+-------+------------+--------+---------------+---------+---------+-------------------------+------+----------+---------------------------------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref                     | rows | filtered | Extra                           |
+----+-------------+-------+------------+--------+---------------+---------+---------+-------------------------+------+----------+---------------------------------+
|  1 | SIMPLE      | sa    | NULL       | ALL    | NULL          | NULL    | NULL    | NULL                    |   30 |   100.00 | Using temporary; Using filesort |
|  1 | SIMPLE      | sh    | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | lateral_test.sa.shop_id |    1 |   100.00 | NULL                            |
+----+-------------+-------+------------+--------+---------------+---------+---------+-------------------------+------+----------+---------------------------------+
2 rows in set, 1 warning (0.00 sec)

mysql> SHOW WARNINGS\G
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: /* select#1 */ select date_format(`lateral_test`.`sa`.`sales_date`,'%Y%m') AS `ym`,`lateral_test`.`sh`.`shop_id` AS `shop_id`,`lateral_test`.`sh`.`shop_name` AS `shop_name`,sum(`lateral_test`.`sa`.`total_price`) AS `tp`,sum(`lateral_test`.`sa`.`total_count`) AS `tc` from `lateral_test`.`shop` `sh` join `lateral_test`.`sales` `sa` where (`lateral_test`.`sh`.`shop_id` = `lateral_test`.`sa`.`shop_id`) group by `lateral_test`.`sh`.`shop_id`,`ym` order by `ym`,`lateral_test`.`sh`.`shop_id`
1 row in set (0.00 sec)

mysql> EXPLAIN
    -> SELECT
    ->     t.ym, sh.shop_id, sh.shop_name, t.tp, t.tc
    -> FROM
    ->     shop sh,
    ->     LATERAL (
    ->         SELECT
    ->             DATE_FORMAT(sa.sales_date, '%Y%m') ym, SUM(sa.total_price) tp, SUM(sa.total_count) tc
    ->         FROM
    ->             sales sa
    ->         WHERE
    ->             sh.shop_id = sa.shop_id
    ->         GROUP BY
    ->             ym
    ->     ) t
    -> ORDER BY
    ->     t.ym, sh.shop_id;
+----+-------------------+------------+------------+------+---------------+------+---------+------+------+----------+-------------------------------------------------------------+
| id | select_type       | table      | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                                       |
+----+-------------------+------------+------------+------+---------------+------+---------+------+------+----------+-------------------------------------------------------------+
|  1 | PRIMARY           | sh         | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    8 |   100.00 | Using temporary; Using filesort; Rematerialize (<derived2>) |
|  1 | PRIMARY           | <derived2> | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |   100.00 | NULL                                                        |
|  2 | DEPENDENT DERIVED | sa         | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   30 |    10.00 | Using where; Using temporary                                |
+----+-------------------+------------+------------+------+---------------+------+---------+------+------+----------+-------------------------------------------------------------+
3 rows in set, 2 warnings (0.00 sec)

mysql> SHOW WARNINGS\G
*************************** 1. row ***************************
  Level: Note
   Code: 1276
Message: Field or reference 'lateral_test.sh.shop_id' of SELECT #2 was resolved in SELECT #1
*************************** 2. row ***************************
  Level: Note
   Code: 1003
Message: /* select#1 */ select `t`.`ym` AS `ym`,`lateral_test`.`sh`.`shop_id` AS `shop_id`,`lateral_test`.`sh`.`shop_name` AS `shop_name`,`t`.`tp` AS `tp`,`t`.`tc` AS `tc` from `lateral_test`.`shop` `sh` join lateral (/* select#2 */ select date_format(`lateral_test`.`sa`.`sales_date`,'%Y%m') AS `ym`,sum(`lateral_test`.`sa`.`total_price`) AS `tp`,sum(`lateral_test`.`sa`.`total_count`) AS `tc` from `lateral_test`.`sales` `sa` where (`lateral_test`.`sh`.`shop_id` = `lateral_test`.`sa`.`shop_id`) group by `ym`) `t` order by `t`.`ym`,`lateral_test`.`sh`.`shop_id`
2 rows in set (0.00 sec)
```

WARNING に**`Message: Field or reference 'lateral_test.sh.shop_id' of SELECT #2 was resolved in SELECT #1`**なんてのが出てますね。

なお、PostgreSQL ではこんな話もありますが、MySQL 8.0 ではどういう実行計画になるのか？是非試してみてください！

 * **[クエリチューニング: GROUP BY から LATERAL への書き換え](https://qiita.com/fujii_masao/items/009967db6933ececc498)（fujii_masao さん）**

---

 * **[Qiitaに投稿したMySQL 8.0関連記事](https://hmatsu47.hatenablog.com/qiita_mysql80)**