MySQL里派生表中的各种外部引用

发布时间:2020-05-07 浏览次数:191

使用 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.idUNION ALLSELECT e.idFROM reports AS rep JOIN employees AS eON 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 boundsSELECT 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 8.0新特性:什么是原子DDL?

下一篇: 当你开始执行一个 ALTER时遇到了可怕的元数据锁定等待

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