MySQL 8.0通用表表达式（CTE）：递归查询与查询优化的深层影响 - 智猿学院-前后端，数据库，人工智能，云计算等领域前沿技术讲座

MySQL 8.0 通用表表达式（CTE）：递归查询与查询优化的深层影响

大家好，今天我们深入探讨 MySQL 8.0 中通用表表达式（Common Table Expression，CTE）的核心概念，特别是其在递归查询和查询优化方面的强大功能。我们将通过实际的代码示例和逻辑分析，揭示 CTE 的工作原理及其对数据库性能的潜在影响。

1. CTE 基础：定义与语法

CTE 本质上是一个命名的临时结果集，它在单个查询的执行范围内存在。可以将 CTE 视为一个临时表，但它只在定义它的查询中可用，并且不会持久化到数据库中。

语法:

WITH CTE_name AS (
    SELECT_statement
)
SELECT_statement;

WITH 关键字：标志着 CTE 定义的开始。
CTE_name：CTE 的名称，在查询中用于引用该 CTE。
SELECT_statement：定义 CTE 结果集的 SELECT 语句。
后面的 SELECT_statement：使用 CTE 的主查询。

简单示例：

假设我们有一个 employees 表，包含员工姓名和部门信息：

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(255),
    department VARCHAR(255)
);

INSERT INTO employees (employee_id, employee_name, department) VALUES
(1, 'Alice', 'Sales'),
(2, 'Bob', 'Marketing'),
(3, 'Charlie', 'Sales'),
(4, 'David', 'IT'),
(5, 'Eve', 'Marketing');

我们可以使用 CTE 来查找所有属于 ‘Sales’ 部门的员工：

WITH SalesEmployees AS (
    SELECT employee_name
    FROM employees
    WHERE department = 'Sales'
)
SELECT employee_name
FROM SalesEmployees;

这个例子很简单，但它展示了 CTE 的基本结构。CTE SalesEmployees 定义了一个包含所有 ‘Sales’ 部门员工姓名的结果集，然后主查询从该 CTE 中选择数据。

2. 递归 CTE：处理层次结构数据

递归 CTE 是 CTE 的一个高级特性，允许我们处理层次结构或树状结构的数据。例如，组织架构、族谱、物料清单（BOM）等都可以用层次结构来表示。

递归 CTE 的结构：

递归 CTE 必须包含两个部分：

锚成员（Anchor Member）： 这是一个非递归的 SELECT 语句，用于定义递归的起始点。
递归成员（Recursive Member）： 这是一个引用 CTE 自身结果集的 SELECT 语句。它使用 UNION ALL 运算符与锚成员连接。

语法：

WITH RECURSIVE CTE_name AS (
    -- 锚成员
    SELECT_statement_anchor
    UNION ALL
    -- 递归成员
    SELECT_statement_recursive
)
SELECT_statement;

示例：组织架构

假设我们有一个 employee_hierarchy 表，表示员工之间的上下级关系：

CREATE TABLE employee_hierarchy (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(255),
    manager_id INT,
    FOREIGN KEY (manager_id) REFERENCES employee_hierarchy(employee_id)
);

INSERT INTO employee_hierarchy (employee_id, employee_name, manager_id) VALUES
(1, 'John', NULL),  -- CEO
(2, 'Alice', 1),
(3, 'Bob', 1),
(4, 'Charlie', 2),
(5, 'David', 2),
(6, 'Eve', 3),
(7, 'Frank', 3);

manager_id 列指向员工的直接上级。如果 manager_id 为 NULL，则表示该员工是 CEO。

我们可以使用递归 CTE 来查找某个员工的所有下属：

WITH RECURSIVE Subordinates AS (
    -- 锚成员：查找 CEO (employee_id = 1)
    SELECT employee_id, employee_name, manager_id, 0 AS level
    FROM employee_hierarchy
    WHERE employee_id = 1
    UNION ALL
    -- 递归成员：查找所有直接下属
    SELECT e.employee_id, e.employee_name, e.manager_id, s.level + 1 AS level
    FROM employee_hierarchy e
    INNER JOIN Subordinates s ON e.manager_id = s.employee_id
)
SELECT employee_id, employee_name, level
FROM Subordinates;

代码解释：

锚成员： SELECT employee_id, employee_name, manager_id, 0 AS level FROM employee_hierarchy WHERE employee_id = 1 选择 CEO 作为递归的起始点，并将级别（level）设置为 0。
递归成员： SELECT e.employee_id, e.employee_name, e.manager_id, s.level + 1 AS level FROM employee_hierarchy e INNER JOIN Subordinates s ON e.manager_id = s.employee_id 将 employee_hierarchy 表与 CTE Subordinates 连接，找到所有直接下属。e.manager_id = s.employee_id 表示员工的 manager_id 等于 CTE 中已存在的员工的 employee_id。级别（level）递增 1。
UNION ALL： 将锚成员和递归成员的结果集合并。递归成员会重复执行，直到找不到新的下属为止。
主查询： SELECT employee_id, employee_name, level FROM Subordinates 从 CTE Subordinates 中选择员工 ID、姓名和级别。

结果：

employee_id	employee_name	level
1	John	0
2	Alice	1
3	Bob	1
4	Charlie	2
5	David	2
6	Eve	2
7	Frank	2

结果显示了 CEO (John) 以及他的所有下属及其级别。

限制递归深度：

为了防止无限递归，MySQL 默认限制了递归深度。可以使用 max_execution_time 系统变量来控制递归查询的最大执行时间，或者使用 SET SESSION max_sp_recursion_depth = value; 来设置递归深度限制。

3. CTE 在查询优化中的作用

CTE 不仅仅是语法糖，它们还可以帮助优化器更好地理解查询意图，从而生成更有效的执行计划。

3.1 代码可读性和模块化

CTE 可以将复杂的查询分解为更小的、可理解的模块。这提高了代码的可读性和可维护性。我们可以将复杂的逻辑分解为多个 CTE，每个 CTE 负责一个特定的任务。

3.2 避免重复计算

如果一个复杂的子查询在查询中多次使用，可以将它定义为 CTE，避免重复计算。这可以显著提高性能，特别是当子查询涉及大量数据时。

示例：避免重复子查询

假设我们需要查找所有部门的平均工资高于公司平均工资的员工。

没有 CTE 的查询：

SELECT e.employee_name, e.department, e.salary
FROM employees e
WHERE e.salary > (SELECT AVG(salary) FROM employees)
AND e.department IN (SELECT department FROM employees GROUP BY department HAVING AVG(salary) > (SELECT AVG(salary) FROM employees));

在这个查询中，SELECT AVG(salary) FROM employees 子查询被执行了三次。

使用 CTE 的查询：

WITH CompanyAvgSalary AS (
    SELECT AVG(salary) AS avg_salary FROM employees
),
DepartmentAvgSalary AS (
    SELECT department, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department
    HAVING AVG(salary) > (SELECT avg_salary FROM CompanyAvgSalary)
)
SELECT e.employee_name, e.department, e.salary
FROM employees e
JOIN DepartmentAvgSalary d ON e.department = d.department
JOIN CompanyAvgSalary c ON e.salary > c.avg_salary;

在这个查询中，SELECT AVG(salary) FROM employees 子查询只被执行了一次。CompanyAvgSalary CTE 计算公司平均工资，DepartmentAvgSalary CTE 计算部门平均工资，并过滤掉低于公司平均工资的部门。主查询使用这两个 CTE 来查找符合条件的员工。

3.3 改善优化器的执行计划

在某些情况下，CTE 可以帮助优化器生成更好的执行计划。例如，优化器可以更好地推断 CTE 结果集的基数，从而选择更合适的连接方式。

3.4 物化 (Materialization) 与内联 (Inlining)

MySQL 优化器可以选择将 CTE 物化或内联。

物化： 将 CTE 的结果集存储在一个临时表中，并在后续查询中从该临时表读取数据。
内联： 将 CTE 的定义直接嵌入到主查询中。

优化器会根据 CTE 的大小、复杂度和使用方式来选择最佳的策略。从MySQL 8.0.19开始，引入了MATERIALIZED和NO_MATERIALIZED优化器提示，允许开发者显式地控制CTE的物化行为。

示例：使用 MATERIALIZED 提示

WITH tmp AS (SELECT * FROM t1) MATERIALIZED
SELECT * FROM tmp t1 JOIN t1 t2;

这个提示强制优化器将 CTE tmp 物化。

示例：使用 NO_MATERIALIZED 提示

WITH tmp AS (SELECT * FROM t1) NO_MATERIALIZED
SELECT * FROM tmp t1 JOIN t1 t2;

这个提示阻止优化器将 CTE tmp 物化。

4. CTE 的局限性

虽然 CTE 功能强大，但也有一些局限性需要注意：

生命周期： CTE 只在定义它的查询中有效。
不能创建索引： 不能在 CTE 上创建索引，因为它们是临时的。
更新限制： 在 MySQL 8.0 之前的版本中，不能在 CTE 中使用 INSERT、UPDATE 或 DELETE 语句。从 MySQL 8.0 开始，允许在 CTE 中使用这些语句，但需要满足一定的条件。
性能考虑： 虽然 CTE 可以帮助优化查询，但在某些情况下，过度使用 CTE 可能会导致性能下降。需要根据具体情况进行评估。

5. CTE 与其他技术对比

临时表： CTE 类似于临时表，但 CTE 更加轻量级，不需要显式创建和删除，并且只在单个查询中有效。
子查询： CTE 可以替代复杂的子查询，提高代码的可读性和可维护性。
视图： 视图是持久化的，而 CTE 是临时的。视图可以被多个查询使用，而 CTE 只能在定义它的查询中使用。

6. 示例：物料清单 (BOM) 递归查询

假设我们有一个 bom 表，表示物料清单：

CREATE TABLE bom (
    product_id INT,
    component_id INT,
    quantity INT,
    PRIMARY KEY (product_id, component_id)
);

INSERT INTO bom (product_id, component_id, quantity) VALUES
(1, 2, 2),  -- 产品 1 需要 2 个组件 2
(1, 3, 1),  -- 产品 1 需要 1 个组件 3
(2, 4, 3),  -- 产品 2 需要 3 个组件 4
(2, 5, 1),  -- 产品 2 需要 1 个组件 5
(3, 4, 2),  -- 产品 3 需要 2 个组件 4
(4, 6, 1),  -- 组件 4 需要 1 个组件 6
(5, 7, 2);  -- 组件 5 需要 2 个组件 7

我们可以使用递归 CTE 来查找某个产品的所有组件及其数量：

WITH RECURSIVE ProductComponents AS (
    -- 锚成员：查找产品 1 的直接组件
    SELECT product_id, component_id, quantity
    FROM bom
    WHERE product_id = 1
    UNION ALL
    -- 递归成员：查找组件的组件
    SELECT pc.product_id, b.component_id, pc.quantity * b.quantity AS quantity
    FROM ProductComponents pc
    JOIN bom b ON pc.component_id = b.product_id
)
SELECT component_id, SUM(quantity) AS total_quantity
FROM ProductComponents
GROUP BY component_id;

这个查询递归地查找产品 1 的所有组件，包括直接组件和间接组件，并计算每个组件的总数量。

7. 高级应用：图数据查询

递归 CTE 还可以用于处理图数据，例如社交网络、关系网络等。我们可以使用 CTE 来查找两个节点之间的路径、计算节点之间的距离等。

8. 最佳实践

命名规范： 使用清晰、描述性的 CTE 名称。
避免过度使用： 不要为了使用 CTE 而使用 CTE。只有在能够提高代码可读性或优化性能时才使用 CTE。
测试： 仔细测试包含 CTE 的查询，确保其正确性和性能。
监控： 监控包含 CTE 的查询的执行计划和性能，并根据需要进行调整。

9. 深入理解：性能调优案例

假设我们有一个电商网站，需要查询某个用户的订单历史，并按照订单日期排序。订单数据存储在 orders 表中，用户数据存储在 users 表中。

初始查询：

SELECT o.order_id, o.order_date, o.total_amount
FROM orders o
JOIN users u ON o.user_id = u.user_id
WHERE u.username = 'john.doe'
ORDER BY o.order_date DESC;

这个查询可能会比较慢，特别是当 orders 表非常大时。

使用 CTE 优化：

我们可以使用 CTE 来预先过滤用户数据，然后将过滤后的用户数据与订单数据连接。

WITH UserOrders AS (
    SELECT o.order_id, o.order_date, o.total_amount
    FROM orders o
    WHERE o.user_id = (SELECT user_id FROM users WHERE username = 'john.doe')
)
SELECT order_id, order_date, total_amount
FROM UserOrders
ORDER BY order_date DESC;

在这个查询中，UserOrders CTE 预先过滤了订单数据，只选择了属于用户 ‘john.doe’ 的订单。这可以减少后续排序的数据量，从而提高性能。

进一步优化：

还可以考虑在 orders 表的 user_id 列上创建索引，以加速过滤操作。

10. 总结与展望

通用表表达式（CTE）是 MySQL 8.0 中一个强大的特性，可以用于简化复杂的查询、提高代码可读性、优化查询性能，并且能够有效地处理递归数据结构。理解 CTE 的工作原理，可以更好地利用它们来构建高效、可维护的数据库应用程序。在未来的数据库发展中，我们期待看到 CTE 在图数据库、数据分析等领域发挥更大的作用。

MySQL 8.0 通用表表达式（CTE）：递归查询与查询优化的深层影响

发表回复 取消回复

发表回复取消回复