MySQL 8.0公用表表达式CTE递归查询优化与实战案例

CTE与递归CTE基础语法

MySQL 8.0引入的公用表表达式（Common Table Expression，CTE）通过WITH子句定义临时结果集，在一条查询中可多次引用。CTE分为非递归CTE和递归CTE两种，非递归CTE本质是可读性更好的子查询别名，递归CTE则支持自引用迭代，是处理层级数据的核心工具。

递归CTE的基本结构包含锚点成员和递归成员，用UNION ALL连接：

WITH RECURSIVE org_tree AS (    -- 锚点查询：起始层级    SELECT id, name, manager_id, 1 AS level    FROM employees    WHERE manager_id IS NULL        UNION ALL        -- 递归查询：逐层展开    SELECT e.id, e.name, e.manager_id, ot.level + 1    FROM employees e    INNER JOIN org_tree ot ON e.manager_id = ot.id)SELECT * FROM org_tree;

锚点查询返回递归起点，递归成员引用CTE自身不断扩展结果集，直到递归成员返回空结果集时终止。MySQL默认递归深度上限为1000，超过则报错。通过cte_max_recursion_depth系统变量调整上限。

递归CTE实现组织架构树遍历

组织架构树是最典型的递归查询场景。以下示例实现从指定节点向下遍历完整子树：

WITH RECURSIVE sub_tree AS (    SELECT         id, name, manager_id,         CAST(name AS CHAR(500)) AS path,        1 AS depth    FROM employees    WHERE id = 100  -- 起始节点        UNION ALL        SELECT         e.id, e.name, e.manager_id,        CONCAT(st.path, ' > ', e.name) AS path,        st.depth + 1 AS depth    FROM employees e    INNER JOIN sub_tree st ON e.manager_id = st.id)SELECT id, name, path, depthFROM sub_treeORDER BY depth, name;

path字段用CONCAT逐层拼接路径字符串，输出层级路径格式。CAST(name AS CHAR(500))确保path字段有足够长度，避免递归层数过多时截断。

递归CTE实现物料BOM展开

制造业BOM（物料清单）的层级展开需要聚合每层子件的数量，是递归CTE的高阶用法：

WITH RECURSIVE bom_expand AS (    -- 锚点：顶级成品    SELECT         parent_id, child_id, quantity,        child_id AS root_part,        quantity AS total_qty,        1 AS level    FROM bom    WHERE parent_id = 'PRODUCT-A'        UNION ALL        -- 递归：逐层展开子件    SELECT         b.parent_id, b.child_id, b.quantity,        be.root_part,        be.total_qty * b.quantity AS total_qty,        be.level + 1 AS level    FROM bom b    INNER JOIN bom_expand be ON b.parent_id = be.child_id)SELECT     child_id AS part_number,    SUM(total_qty) AS required_quantity,    MAX(level) AS max_depthFROM bom_expandGROUP BY child_idORDER BY max_depth;

关键点在于total_qty的计算：每层子件数量=父件总数量乘以本层单位用量。递归过程中逐层累乘，最终GROUP BY汇总每个物料的总需求量。

递归CTE性能优化策略

递归CTE的性能瓶颈集中在递归成员的执行频率上。每次递归迭代都会执行一次JOIN操作，N层深度意味着N次JOIN。优化手段：

1. 在递归JOIN字段上建立索引。上述示例中manager_id和child_id必须建索引，否则每次递归迭代都是全表扫描：

CREATE INDEX idx_emp_manager ON employees(manager_id);CREATE INDEX idx_bom_child ON bom(child_id);

2. 限制递归深度。业务场景通常层级有限（组织架构一般不超过10层），用WHERE条件提前终止递归：

-- 限制只查3层WHERE st.depth < 3

3. 避免递归CTE中的排序和聚合。递归成员中包含GROUP BY或ORDER BY会严重拖慢每次迭代，应将聚合操作放到外层查询。

4. 监控递归执行计划。用EXPLAIN ANALYZE查看递归CTE的执行计划，关注迭代次数和单次扫描行数。如果单次迭代扫描行数随递归深度指数增长，说明索引缺失或JOIN条件有问题。

CTE对比传统子查询的性能差异

非递归CTE在MySQL 8.0.14之前会被优化器内联展开，与子查询性能一致。8.0.14之后optimizer_switch的subquery_to_derived选项会物化CTE，对多次引用同一CTE的查询有性能提升——物化结果只计算一次，后续引用直接读缓存。

递归CTE对比存储过程+临时表方案的优势：单条SQL完成递归，无需创建临时表、无需循环控制逻辑、无需手动清理临时数据。缺点是递归CTE无法在递归过程中做条件分支（IF/CASE），复杂逻辑仍需存储过程实现。

原创文章，作者：小编，如若转载，请注明出处：https://www.yunthe.com/mysql80-gong-yong-biao-biao-da-shi-cte-di-gui-cha-xun-you/

CTE基础语法与非递归用法

公用表表达式（Common Table Expression，CTE）是MySQL 8.0引入的重要SQL增强特性。CTE通过WITH子句定义临时结果集，在后续查询中引用，相比子查询和临时表有更好的可读性和潜在性能优势。非递归CTE的典型用途是简化复杂聚合查询：

WITH dept_avg AS ( SELECT dept_id, AVG(salary) AS avg_salary FROM employees GROUP BY dept_id ), overall_avg AS ( SELECT AVG(salary) AS avg_salary FROM employees ) SELECT d.dept_name, da.avg_salary, ROUND(da.avg_salary - oa.avg_salary, 2) AS diff FROM dept_avg da JOIN departments d ON d.id = da.dept_id CROSS JOIN overall_avg oa ORDER BY diff DESC;

多个CTE用逗号分隔，后者可以引用前者。MySQL优化器对CTE的处理有两种策略：Merge（内联展开）和Materialization（物化为临时表）。简单CTE通常被Merge，被多次引用或包含聚合的CTE会被Materialization。

递归CTE实现层级数据遍历

递归CTE是处理树形和层级数据的核心工具，由锚点成员（Anchor Member）和递归成员（Recursive Member）两部分组成。以组织架构的上下级关系为例：

WITH RECURSIVE emp_hierarchy AS ( SELECT id, name, manager_id, 1 AS level, CAST(name AS CHAR(500)) AS path FROM employees WHERE manager_id IS NULL


    UNION ALL

SELECT e.id, e.name, e.manager_id, h.level + 1, CONCAT(h.path, ' -> ', e.name) FROM employees e JOIN emp_hierarchy h ON e.manager_id = h.id ) SELECT * FROM emp_hierarchy ORDER BY level, path;

递归CTE的执行过程：1) 执行锚点查询获得初始行集；2) 用初始行集与目标表JOIN执行递归查询；3) 将递归结果追加到CTE结果集；4) 重复步骤2-3直到递归查询返回空结果集；5) 返回所有迭代结果的UNION ALL。

递归深度控制与性能优化

MySQL默认限制递归深度为1000次迭代，可通过cte_max_recursion_depth参数调整。循环引用数据会导致递归无限执行，必须设计终止条件：

SET SESSION cte_max_recursion_depth = 100;


WITH RECURSIVE org_tree AS (
    SELECT id, parent_id, 1 AS depth, name
    FROM categories
    WHERE parent_id IS NULL
    UNION ALL

SELECT c.id, c.parent_id, ot.depth + 1, c.name FROM categories c JOIN org_tree ot ON c.parent_id = ot.id WHERE ot.depth < 5 ) SELECT * FROM org_tree;

性能优化要点：

1. 索引设计：递归JOIN条件（如manager_id = id）必须建立索引。缺少索引时每次递归迭代执行全表扫描，N层深度下复杂度从O(N)退化为O(N^2)

2. 提前终止：WHERE depth < N在递归成员中过滤，避免不必要的迭代。比cte_max_recursion_depth更精确——前者在语义层面截断，后者是硬性安全阀

3. 避免物化开销：递归CTE会被物化为临时表。如果递归结果集很大（百万级行），物化写盘会显著拖慢查询。限制深度和尽早过滤可减少物化规模

路径枚举与图遍历应用

递归CTE不仅适用于树结构，还支持图遍历。以社交网络的好友推荐为例，查找二度好友：

WITH RECURSIVE friend_chain AS ( SELECT user_id, friend_id, 1 AS degree, CAST(friend_id AS CHAR(200)) AS visited FROM friendships WHERE user_id = 100


    UNION ALL

SELECT fc.friend_id AS user_id, f.friend_id, fc.degree + 1, CONCAT(fc.visited, ',', f.friend_id) FROM friendships f JOIN friend_chain fc ON f.user_id = fc.friend_id WHERE fc.degree < 2 AND FIND_IN_SET(f.friend_id, fc.visited) = 0 ) SELECT DISTINCT friend_id, degree FROM friend_chain WHERE degree = 2 AND friend_id != 100 ORDER BY degree;

FIND_IN_SET检查避免环路，visited字段记录已访问节点。这种模式在推荐系统、物流路径规划、依赖关系分析中都有应用。

CTE与窗口函数组合使用

CTE与窗口函数的组合能解决复杂的层级排序问题。例如在组织架构中为每一层的员工按薪资排名：

WITH RECURSIVE org_level AS ( SELECT id, name, manager_id, 1 AS lvl FROM employees WHERE manager_id IS NULL UNION ALL SELECT e.id, e.name, e.manager_id, ol.lvl + 1 FROM employees e JOIN org_level ol ON e.manager_id = ol.id ), ranked AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY lvl ORDER BY salary DESC) AS rank_lvl, salary - AVG(salary) OVER (PARTITION BY lvl) AS salary_diff FROM employees ) SELECT lvl, name, salary, rank_lvl, salary_diff FROM ranked JOIN org_level USING (id) ORDER BY lvl, rank_lvl;

CTE完成层级计算，窗口函数在同一层级内做排名和统计，两者结合让复杂分析查询保持清晰可读。合理使用CTE替代嵌套子查询，是MySQL 8.0下提升SQL可维护性和性能的重要手段。