技术分享 | 派生表中的各种外部引用

翻译

ActionTech 2022-12-20 15:17:41 博主文章分类：MySQL

作者：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 在一次传递中构建一个大结果，列出所有员工和每个间接管理器之间的所有连接，然后使用这个大的结果来聚合每个经理。它有效，但很难阅读。相反，我们上面所做的是从层次结构中逐个生成较小的集合。所以它是“走层次 / 聚合 / 重复的一部分”而不是“走整个层次 / 聚合”。