Help us understand the problem. What is going on with this article?

MySQL 8.0.14 で LATERAL 句を使ってみる

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

やってみる

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

このページに、

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

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

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

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

テスト準備
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句で集計してみます。

普通にやってみる
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句が入ってしまうため例としては微妙ですが…。

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の結果も比較してみます。

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 ではどういう実行計画になるのか?是非試してみてください!


Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away