MySQL 8の新機能 LATERAL句によるSQLチューニング
MySQL 8.0.14でLATERAL句がサポートされました。
一瞬、LITERALに空目しましたが、LATERAL（側面）でした。
LATERAL句を試してみます。
サンプルとしてこのようなテーブルを扱います。
従業員の一覧（employees）と、各従業員の特定の期間の月額給与（salaries）のテーブルです。
mysql> SELECT * FROM employees LIMIT 5;
+--------+--+-----------+
| emp_no |-| last_name |
+--------+--+-----------+
| 10001 |-| Facello |
| 10002 |-| Simmel |
| 10003 |-| Bamford |
| 10004 |-| Koblick |
| 10005 |-| Maliniak |
+--------+--+-----------+
5 rows in set (0.00 sec)
mysql> SELECT * FROM salaries LIMIT 5;
+--------+--------+------------+------------+
| emp_no | salary | from_date | to_date |
+--------+--------+------------+------------+
| 10001 | 60117 | 1986-06-26 | 1987-06-26 |
| 10001 | 62102 | 1987-06-26 | 1988-06-25 |
| 10001 | 66074 | 1988-06-25 | 1989-06-25 |
| 10001 | 66596 | 1989-06-25 | 1990-06-25 |
| 10001 | 66961 | 1990-06-25 | 1991-06-25 |
+--------+--------+------------+------------+
5 rows in set (0.00 sec)
さて、このテーブルから、各従業員の初任給の支給日と額を１回のSQLで取り出したいとします。
サブクエリで書くと、非常にわかりやすく書けます。
mysql> SELECT emp_no, last_name,
-> (SELECT MIN(from_date) FROM salaries
-> WHERE salaries.emp_no = employees.emp_no) AS first_salary_date,
-> (SELECT salary FROM salaries
-> WHERE salaries.emp_no = employees.emp_no
-> AND from_date = first_salary_date) AS first_salary
-> FROM employees;
+--------+------------------+-------------------+--------------+
| emp_no | last_name | first_salary_date | first_salary |
+--------+------------------+-------------------+--------------+
| 10001 | Facello | 1986-06-26 | 60117 |
| 10002 | Simmel | 1996-08-03 | 65828 |
| 10003 | Bamford | 1995-12-03 | 40006 |
| 10004 | Koblick | 1986-12-01 | 40054 |
| 10005 | Maliniak | 1989-09-12 | 78228 |
| 10006 | Preusig | 1990-08-05 | 40000 |
| 10007 | Zielinski | 1989-02-10 | 56724 |
300024 rows in set (6.99 sec)
実行計画を見ると、employees テーブルの各行に対して、salariesテーブルから、初任給の支給日と額を分けて読み取っています。
1人の従業員に対して、salariesテーブルを2回読み取っていて(DEPENDENT SUBQUERYが2回)、無駄に感じます。
+----+--------------------+-----------+--------+---------------+---------+---------+---------------------------------+--------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------------+-----------+--------+---------------+---------+---------+---------------------------------+--------+----------+-------------+
| 1 | PRIMARY | employees | ALL | NULL | NULL | NULL | NULL | 299600 | 100.00 | NULL |
| 3 | DEPENDENT SUBQUERY | salaries | eq_ref | PRIMARY | PRIMARY | 7 | employees.employees.emp_no,func | 1 | 100.00 | Using where |
| 2 | DEPENDENT SUBQUERY | salaries | ref | PRIMARY | PRIMARY | 4 | employees.employees.emp_no | 9 | 100.00 | Using index |
+----+--------------------+-----------+--------+---------------+---------+---------+---------------------------------+--------+----------+-------------+
salariesテーブルから、支給日と額を同時に読み出して高速化を測りたくなりますが・・・
mysql> SELECT emp_no, last_name,
-> (SELECT from_date, salary FROM salaries
-> WHERE salaries.emp_no = employees.emp_no
-> ORDER BY from_date
-> LIMIT 1) AS first_salary_date
-> FROM employees;
ERROR 1241 (21000): Operand should contain 1 column(s)
サブクエリは単一のカラムのみを返す必要があり、思い通りにいきません。
mysql> SELECT emp_no, last_name,
-> first_pay.from_date,
-> first_pay.salary
-> FROM employees,
-> (SELECT from_date, salary FROM salaries
-> WHERE salaries.emp_no = employees.emp_no
-> ORDER BY from_date LIMIT 1 ) AS first_pay;
ERROR 1054 (42S22): Unknown column 'employees.emp_no' in 'where clause'
FROMのほうでもダメです。FROM句では他のテーブルのカラムを参照することができないのです。
これが、LATERAL句を使うとFROMに書けるようになります。
salariesテーブルの読み取り回数が減ったぶん、実行時間が 6.99s→3.40s と速くなっています。
mysql> SELECT employees.emp_no, employees.last_name,
-> first_pay.from_date,
-> first_pay.salary
-> FROM employees,
-> LATERAL
-> (SELECT from_date, salary FROM salaries
-> WHERE salaries.emp_no = employees.emp_no
-> ORDER BY from_date LIMIT 1 ) AS first_pay;
300024 rows in set (3.40 sec)
+----+-------------------+------------+------+---------------+---------+---------+----------------------------+--------+----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------------+------------+------+---------------+---------+---------+----------------------------+--------+----------------------------+
| 1 | PRIMARY | employees | ALL | NULL | NULL | NULL | NULL | 299600 | Rematerialize () |
| 1 | PRIMARY | | ALL | NULL | NULL | NULL | NULL | 2 | NULL |
| 2 | DEPENDENT DERIVED | salaries | ref | PRIMARY | PRIMARY | 4 | employees.employees.emp_no | 9 | Using filesort |
+----+-------------------+------------+------+---------------+---------+---------+----------------------------+--------+----------------------------+
ちなにみ、このクエリはMySQL 8で新たにサポートされたWINDOW関数を使って書くこともできます。
今回のケースの場合、LATERALのほうが高速でした。
mysql> SELECT
-> DISTINCT
-> emp_no,
-> last_name,
-> FIRST_VALUE(from_date) OVER w,
-> FIRST_VALUE(salary) OVER w
-> FROM
-> (
-> SELECT
-> employees.emp_no,
-> employees.last_name,
-> salaries.from_date,
-> salaries.salary
-> FROM employees INNER JOIN salaries USING (emp_no)
-> ) x
-> WINDOW w AS (PARTITION BY x.emp_no ORDER BY x.from_date);
300024 rows in set (14.12 sec)
MySQL 8で新しい書き方が増えて、便利になった一方、パターンが増えて悩む。。。
一瞬、LITERALに空目しましたが、LATERAL（側面）でした。
LATERAL句を試してみます。
サンプルとしてこのようなテーブルを扱います。
従業員の一覧（employees）と、各従業員の特定の期間の月額給与（salaries）のテーブルです。
mysql> SELECT * FROM employees LIMIT 5;
+--------+-
| emp_no |
+--------+-
| 10001 |
| 10002 |
| 10003 |
| 10004 |
| 10005 |
+--------+-
5 rows in set (0.00 sec)
mysql> SELECT * FROM salaries LIMIT 5;
+--------+--------+------------+------------+
| emp_no | salary | from_date | to_date |
+--------+--------+------------+------------+
| 10001 | 60117 | 1986-06-26 | 1987-06-26 |
| 10001 | 62102 | 1987-06-26 | 1988-06-25 |
| 10001 | 66074 | 1988-06-25 | 1989-06-25 |
| 10001 | 66596 | 1989-06-25 | 1990-06-25 |
| 10001 | 66961 | 1990-06-25 | 1991-06-25 |
+--------+--------+------------+------------+
5 rows in set (0.00 sec)
さて、このテーブルから、各従業員の初任給の支給日と額を１回のSQLで取り出したいとします。
サブクエリで書くと、非常にわかりやすく書けます。
mysql> SELECT emp_no, last_name,
-> (SELECT MIN(from_date) FROM salaries
-> WHERE salaries.emp_no = employees.emp_no) AS first_salary_date,
-> (SELECT salary FROM salaries
-> WHERE salaries.emp_no = employees.emp_no
-> AND from_date = first_salary_date) AS first_salary
-> FROM employees;
+--------+------------------+-------------------+--------------+
| emp_no | last_name | first_salary_date | first_salary |
+--------+------------------+-------------------+--------------+
| 10001 | Facello | 1986-06-26 | 60117 |
| 10002 | Simmel | 1996-08-03 | 65828 |
| 10003 | Bamford | 1995-12-03 | 40006 |
| 10004 | Koblick | 1986-12-01 | 40054 |
| 10005 | Maliniak | 1989-09-12 | 78228 |
| 10006 | Preusig | 1990-08-05 | 40000 |
| 10007 | Zielinski | 1989-02-10 | 56724 |
300024 rows in set (6.99 sec)
実行計画を見ると、employees テーブルの各行に対して、salariesテーブルから、初任給の支給日と額を分けて読み取っています。
1人の従業員に対して、salariesテーブルを2回読み取っていて(DEPENDENT SUBQUERYが2回)、無駄に感じます。
+----+--------------------+-----------+--------+---------------+---------+---------+---------------------------------+--------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------------+-----------+--------+---------------+---------+---------+---------------------------------+--------+----------+-------------+
| 1 | PRIMARY | employees | ALL | NULL | NULL | NULL | NULL | 299600 | 100.00 | NULL |
| 3 | DEPENDENT SUBQUERY | salaries | eq_ref | PRIMARY | PRIMARY | 7 | employees.employees.emp_no,func | 1 | 100.00 | Using where |
| 2 | DEPENDENT SUBQUERY | salaries | ref | PRIMARY | PRIMARY | 4 | employees.employees.emp_no | 9 | 100.00 | Using index |
+----+--------------------+-----------+--------+---------------+---------+---------+---------------------------------+--------+----------+-------------+
salariesテーブルから、支給日と額を同時に読み出して高速化を測りたくなりますが・・・
mysql> SELECT emp_no, last_name,
-> (SELECT from_date, salary FROM salaries
-> WHERE salaries.emp_no = employees.emp_no
-> ORDER BY from_date
-> LIMIT 1) AS first_salary_date
-> FROM employees;
ERROR 1241 (21000): Operand should contain 1 column(s)
サブクエリは単一のカラムのみを返す必要があり、思い通りにいきません。
mysql> SELECT emp_no, last_name,
-> first_pay.from_date,
-> first_pay.salary
-> FROM employees,
-> (SELECT from_date, salary FROM salaries
-> WHERE salaries.emp_no = employees.emp_no
-> ORDER BY from_date LIMIT 1 ) AS first_pay;
ERROR 1054 (42S22): Unknown column 'employees.emp_no' in 'where clause'
FROMのほうでもダメです。FROM句では他のテーブルのカラムを参照することができないのです。
これが、LATERAL句を使うとFROMに書けるようになります。
salariesテーブルの読み取り回数が減ったぶん、実行時間が 6.99s→3.40s と速くなっています。
mysql> SELECT employees.emp_no, employees.last_name,
-> first_pay.from_date,
-> first_pay.salary
-> FROM employees,
-> LATERAL
-> (SELECT from_date, salary FROM salaries
-> WHERE salaries.emp_no = employees.emp_no
-> ORDER BY from_date LIMIT 1 ) AS first_pay;
300024 rows in set (3.40 sec)
+----+-------------------+------------+------+---------------+---------+---------+----------------------------+--------+----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------------+------------+------+---------------+---------+---------+----------------------------+--------+----------------------------+
| 1 | PRIMARY | employees | ALL | NULL | NULL | NULL | NULL | 299600 | Rematerialize (
| 1 | PRIMARY |
| 2 | DEPENDENT DERIVED | salaries | ref | PRIMARY | PRIMARY | 4 | employees.employees.emp_no | 9 | Using filesort |
+----+-------------------+------------+------+---------------+---------+---------+----------------------------+--------+----------------------------+
ちなにみ、このクエリはMySQL 8で新たにサポートされたWINDOW関数を使って書くこともできます。
今回のケースの場合、LATERALのほうが高速でした。
mysql> SELECT
-> DISTINCT
-> emp_no,
-> last_name,
-> FIRST_VALUE(from_date) OVER w,
-> FIRST_VALUE(salary) OVER w
-> FROM
-> (
-> SELECT
-> employees.emp_no,
-> employees.last_name,
-> salaries.from_date,
-> salaries.salary
-> FROM employees INNER JOIN salaries USING (emp_no)
-> ) x
-> WINDOW w AS (PARTITION BY x.emp_no ORDER BY x.from_date);
300024 rows in set (14.12 sec)
MySQL 8で新しい書き方が増えて、便利になった一方、パターンが増えて悩む。。。
