作者:Guilhem Bichot
翻译:管长龙
使用 LATERAL,JOIN 可以具有第二个表 - 基于子查询的派生表 - 基于第一个表的列的值进行定义,因此可以为第一个表的每一行重新计算。典型:
SELECT ... FROM t1, LATERAL (SELECT ... FROM t2
^ WHERE t2.col=t1.col ... ) AS derived;
| |
| |
+---------------------------+
在第二个表(派生的)中,t1.col 是第一个表 t1 的“横向外部引用”。引用的表被放置在“派生表”的“旁边”(即两者都是同一 FROM 子句的一部分)。
在实现此 LATERAL 功能时,我同时添加了另一个相关功能:支持派生表中的非横向外部引用。
分层数据示例:
CREATE TABLE employees (
id INT PRIMARY KEY NOT NULL,
name VARCHAR(100) NOT NULL,
manager_id INT NULL,
INDEX (manager_id),
FOREIGN KEY (manager_id) REFERENCES employees (id)
);
INSERT INTO employees VALUES
(333, "Yasmina", NULL), # Yasmina is the CEO (manager_id is NULL)
(198, "John", 333), # John has ID 198 and reports to 333 (Yasmina)
(692, "Tarek", 333),
(29, "Pedro", 198),
(4610, "Sarah", 29),
(72, "Pierre", 29),
(123, "Adil", 692);
每个人接收到直接和间接报告的数量?此过程包含 MySQL 递归语法
SELECT emp.*,
(
WITH RECURSIVE reports AS
(
SELECT emp.id
UNION ALL
SELECT e.id
FROM reports AS rep JOIN employees AS e
ON rep.id = e.manager_id
)
SELECT COUNT(*)-1 FROM reports # 每次计算返回的统计结果
) AS count_of_all_reports
FROM employees AS emp;
描述:对于每位员工:
-
评估一个标量子查询(第 2-12 行)count_of_all_reports,其中:
-
通过递归查找员工的所有直接和间接报告来构建 CTE(第 3-10 行)
-
计算 CTE 的行数(第 11 行),减去一行不计算员工
-
返回计数。
CTE 意为共用表达式(Common Table Expression),通常用于构建复杂查询。
结果:
+------+---------+------------+----------------------+
| id | name | manager_id | count_of_all_reports |
+------+---------+------------+----------------------+
| 29 | Pedro | 198 | 2 |
| 72 | Pierre | 29 | 0 |
| 123 | Adil | 692 | 0 |
| 198 | John | 333 | 3 |
| 333 | Yasmina | NULL | 6 |
| 692 | Tarek | 333 | 1 |
| 4610 | Sarah | 29 | 0 |
+------+---------+------------+----------------------+
7 rows in set (0.02 sec)
CTE 的解释:从 SELECT emp.id 开始递归,这是对我们想要计算的当前员工的引用;这个emp.id 来自于其中一行 emp (CTE 之外)。
如果我们从“引用”到“引用列”绘制一个箭头,则此箭头从 CTE 开始,遍历到边界,再遍历到周围的标量子查询的边界,并最终到达顶部查询。这就是为什么它不是“横向外部引用”。
SELECT emp.*,
(
WITH RECURSIVE reports AS
( +----------------------------------+
| |
SELECT emp.id |
UNION ALL |
SELECT e.id |
FROM reports AS rep JOIN employees AS e |
ON rep.id = e.manager_id |
) | crosses CTE's bounds
SELECT COUNT(*)-1 FROM reports |
) AS count_of_all_reports | crosses scalar subquery's bounds
FROM employees AS emp; |
^ |
| |
+-----------------------------+ reaches to farthest outside
在 MySQL 8.0.14 之前,这是不可能的(MySQL 在 CTE 的定义中不知道 emp.id 是什么)。
新版本 MySQL 检测到这个引用;它得出结论,必须为 emp.id 的每一行重新计算 标量子查询 及其包含的 CTE。
查看 EXPLAIN 查询:
+----+--------------------+------------+------------+------+---------------+------------+---------+--------+------+----------+------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------------+------------+------------+------+---------------+------------+---------+--------+------+----------+------------------------+
| 1 | PRIMARY | emp | NULL | ALL | NULL | NULL | NULL | NULL | 7 | 100.00 | NULL |
| 2 | DEPENDENT SUBQUERY | | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | NULL |
| 3 | DEPENDENT DERIVED | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
| 4 | UNCACHEABLE UNION | rep | NULL | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | Recursive; Using where |
| 4 | UNCACHEABLE UNION | e | NULL | ref | manager_id | manager_id | 5 | rep.id | 1 | 100.00 | Using index |
+----+--------------------+------------+------------+------+---------------+------------+---------+--------+------+----------+------------------------+
我们看到 MySQL 已经认识到 标量子查询 是“依赖的”(取决于外部数据),对于派生表也是如此。它还看到 CTE 中 UNION 的内容是“不可缓存的”,每次都必须重新计算。
回顾一下,从 MySQL 8.0.14 开始:
-
默认情况下,在解析派生表的定义时,MySQL 接受非横向外部引用,如上面的示例查询中所示。
-
如果你添加 LATERAL 关键字,MySQL 也接受横向外部引用;换句话说,它还在包含派生表的 FROM 子句中进行搜索。
注意:报告计数问题还有其他解决方案。一种解决方案是使用一个递归 CTE 在一次传递中构建一个大结果,列出所有员工和每个间接管理器之间的所有连接,然后使用这个大的结果来聚合每个经理。它有效,但很难阅读。相反,我们上面所做的是从层次结构中逐个生成较小的集合。所以它是“走层次 / 聚合 / 重复的一部分”而不是“走整个层次 / 聚合”。