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

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