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

发布时间:2020-10-12 浏览次数:191

MySQL 数据库使用 LATERAL,JOIN 可以具有第二个表 - 基于子查询的派生表 - 基于第一个表的列的值进行定义,因此可以为第一个表的每一行重新计算。典型:

SELECT ... FROM t1, LATERAL (SELECT ... FROM t2                ^            WHERE t2.col=t1.col ... ) AS derived;                |                           |                |                           |                +---------------------------+

在第二个表(派生的)中,t1.col 是第一个表 t1 的“横向外部引用”引用的表被放置在“派生表”的“旁边”(即两者都是同一 FROM 子句的一部分)。

在实现此 LATERAL 功能时,我同时添加了另一个相关功能:支持派生表中的非横向外部引用。

分层数据示例:

  1. CREATE TABLE employees (

  2. id INT PRIMARY KEY NOT NULL,

  3. name VARCHAR(100) NOT NULL,

  4. manager_id INT NULL,

  5. INDEX (manager_id),

  6. FOREIGN KEY (manager_id) REFERENCES employees (id)

  7. );


  8. INSERT INTO employees VALUES

  9. (333, "Yasmina", NULL), # Yasmina is the CEO (manager_id is NULL)

  10. (198, "John", 333), # John has ID 198 and reports to 333 (Yasmina)

  11. (692, "Tarek", 333),

  12. (29, "Pedro", 198),

  13. (4610, "Sarah", 29),

  14. (72, "Pierre", 29),

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


上一篇: 新特性解读 | 窗口函数的适用场景

下一篇: 技术分享 | MySQL 网络延时参数设置建议

产品试用 产品试用
400-820-6580 免费电话