MySQL 递归 CTE 怎样限制深度并检测路径环

直接答案：MySQL 递归 CTE 不应只依赖服务器的 cte_max_recursion_depth。业务查询里至少要携带两个状态：depth 用来限制允许展开的层数，path 用来记录当前分支已经访问过的节点；新节点已存在于当前路径时，将它标记为环并停止继续展开。

线上再叠加两道保险：会话级 cte_max_recursion_depth 限制递归层数，查询级 MAX_EXECUTION_TIME 限制执行时间。深度限制、路径环检测、服务器层数限制和超时解决的是四个不同问题，不能互相替代。

MySQL 8.4 官方文档：https://dev.mysql.com/doc/refman/8.4/en/with.html

触发信号：什么时候要立刻检查递归 CTE

以下任一信号出现，都应把递归查询当成潜在故障处理，而不是简单调大参数：

• 查询报错，提示递归层数超过 cte_max_recursion_depth。
• 同一个节点在结果中反复出现，路径长度持续增长。
• 结果行数远高于层级表的节点数，内部临时表迅速膨胀。
• CPU、临时表磁盘写入或单条 SQL 执行时间突然升高。
• 新导入或批量修改父节点后，原本正常的树查询开始超时。

MySQL 官方文档说明，递归成员必须有让递归终止的条件。服务器默认的递归深度上限是 1000 层，但它只是失控时的最后保护，不表示业务真的允许 1000 层。

快速判断：深链、成环还是结果分叉

先区分这三类问题很重要。单纯限制深度能阻止无限向下，却不能解释哪条父子边成环；路径检测能阻止当前分支重复，却无法阻止一层产生几十万条合法分支。

最小安全模板：depth 与 path 两道业务保护

下面假设层级表名为 category_node，主键是数值型 id，父节点列是 parent_id。锚点深度设为 0，递归成员每次加 1；路径使用前后都有斜杠的形式，例如 /12/37/81/，避免节点 1 与节点 11 发生子串误判。

WITH RECURSIVE node_walk (
    id, parent_id, depth, path, is_cycle
) AS (
    SELECT
        n.id,
        n.parent_id,
        0 AS depth,
        -- 锚点决定 CTE 列宽，提前为增长中的路径预留空间
        CAST(CONCAT('/', n.id, '/') AS CHAR(2048)) AS path,
        0 AS is_cycle
    FROM category_node AS n
    WHERE n.id = ?

    UNION ALL

    SELECT
        child.id,
        child.parent_id,
        parent.depth + 1,
        CASE
            -- 检测到当前路径重复时保留原路径，避免继续膨胀
            WHEN LOCATE(CONCAT('/', child.id, '/'), parent.path) > 0
                THEN parent.path
            ELSE CONCAT(parent.path, child.id, '/')
        END AS path,
        LOCATE(CONCAT('/', child.id, '/'), parent.path) > 0 AS is_cycle
    FROM node_walk AS parent
    JOIN category_node AS child
      ON child.parent_id = parent.id
    WHERE parent.depth 

这里 parent.depth 允许生成深度为 50 的子节点，因为判断发生在父节点一侧。若业务把根节点记作第 1 层，可以把锚点改成 1，并相应调整条件，关键是团队必须统一“深度”和“层数”的口径。

为什么 path 必须在锚点中 CAST

MySQL 递归 CTE 的结果列类型由非递归部分推断，递归部分不会重新扩大列宽。如果锚点只产生短字符串，后续 CONCAT 生成更长路径时，严格模式会出现“Data too long”错误，非严格模式还可能截断路径。

路径一旦被截断，环检测就可能漏掉较早的节点。因此应根据最大深度和 ID 最大长度计算足够宽的 CHAR。示例使用 2048 只是演示值，不应不加评估地复制到所有表。

-- 以最大 50 层、每个数字 ID 最长 20 位估算路径容量
SET @max_depth = 50;
SET @max_id_chars = 20;

-- 每段额外保留一个分隔符，实际建模时再加入安全余量
SELECT @max_depth * (@max_id_chars + 1) AS estimated_path_chars;

如果 ID 本身可能包含斜杠，不能继续使用斜杠字符串方案，应改用不会冲突的编码或 JSON 数组。数值 ID 使用分隔字符串简单、直观，也便于在故障期间直接查看路径。

怎样把成环边单独查出来

主查询已经把首次重复节点标记为 is_cycle=1，只是最终业务结果过滤掉了它。排障时保留同一个 CTE，将外层查询改成只看环节点，就能看到哪一个子节点试图重新进入当前路径。

-- 复用前面的 node_walk CTE，仅输出被判定为环的边界节点
SELECT
    id AS repeated_id,
    parent_id,
    depth,
    path AS path_before_repeat
FROM node_walk
WHERE is_cycle = 1;

这类检测是“当前路径内重复”，因此不会错误阻止同一个节点通过另一条合法分支出现。对于严格树结构，一个节点本应只有一个父节点；对于有向无环图，同一节点从不同分支到达可能是合法的，是否去重需要单独定义。

服务器级保险：递归层数、超时与行数

MySQL 8.4 提供三类额外保护。cte_max_recursion_depth 控制递归层数，默认值是 1000；max_execution_time 或 MAX_EXECUTION_TIME 控制 SELECT 执行时间；递归成员中的 LIMIT 可以限制产生的总行数。

-- 仅对当前会话设置较小的递归保险，避免影响其他连接
SET SESSION cte_max_recursion_depth = 100;

-- 当前会话中的 SELECT 最长执行 3 秒，单位为毫秒
SET SESSION max_execution_time = 3000;

不要为了让报错消失就把深度上限改成极大的值。合理顺序是：先定义业务最大层级，再在 SQL 中加入 depth 终止条件，最后把会话上限设置为略高于业务值的保险。超时同样不能代替环检测，因为一个分支很少的环可能在超时前反复迭代很多次。

LIMIT 控制的是产生行数，不是路径深度。树的分支很宽时它很有用，但它可能截断合法结果，因此只适合作为已知接口的容量保护，并需要让调用方知道结果可能不完整。

线上失控时的止损与回滚路径

当递归 SQL 已经占满资源，先停止持续伤害，再修复查询和数据。不要在高负载期间直接执行无边界的全表诊断 CTE。

-- 找到持续运行的递归查询及其连接 ID
SHOW PROCESSLIST;

-- 只终止目标连接当前语句，保留连接本身
KILL QUERY 12345;

安全止损顺序可以按以下手册执行：

1. 暂停触发该查询的定时任务或临时关闭相关接口流量。
2. 通过进程列表确认 SQL、连接 ID、运行时长和来源应用。
3. 使用 KILL QUERY 终止目标语句，避免误杀无关连接。
4. 回滚到上一版带边界的查询，或临时返回有限层级结果。
5. 在只读副本或受限数据集上定位重复路径和错误父子边。
6. 修复数据后恢复流量，并观察超时、错误率和临时表指标。

数据修复前先锁定错误父子边

环通常来自错误更新，例如把祖先节点设置成后代节点的子节点。修复时不要只把任意一条边设为 NULL；应依据业务归属、审计记录或导入来源确认哪条边是错误的，并在事务中更新。

START TRANSACTION;

-- 示例：将确认错误的父节点关系恢复为正确父节点
UPDATE category_node
SET parent_id = 42
WHERE id = 81
  AND parent_id = 105;

-- 确认只修改了预期记录后再提交，异常则执行 ROLLBACK
COMMIT;

如果影响行数不符合预期，应回滚并重新核对。外键只能保证父节点存在，不能自动保证整张图无环；防环需要写入前检查“新父节点是否已经位于当前节点的后代集合中”。

索引与执行计划检查

向下遍历时，递归成员通过 child.parent_id = parent.id 找子节点，因此 parent_id 应有索引。缺少这个索引会让每一层都扫描大量记录，即使没有环也可能很慢。

-- 为每层查找直接子节点提供索引
CREATE INDEX idx_category_node_parent
    ON category_node(parent_id);

-- 检查递归成员的访问方式，Extra 中会标识 Recursive
EXPLAIN
WITH RECURSIVE node_walk AS (
    SELECT id, parent_id, 0 AS depth
    FROM category_node
    WHERE id = 1
    UNION ALL
    SELECT child.id, child.parent_id, parent.depth + 1
    FROM node_walk AS parent
    JOIN category_node AS child ON child.parent_id = parent.id
    WHERE parent.depth 

官方文档提醒，EXPLAIN 中递归部分的成本是每次迭代估算，优化器无法提前知道终止条件何时变为 false。因此除了执行计划，还要观察实际迭代深度、总行数和临时表是否落盘。

告警确认与复盘项

• 记录根节点、业务最大深度、实际最大深度和返回总行数。
• 统计 is_cycle=1 的次数，但不要把完整敏感路径写入公开日志。
• 监控递归查询 P95/P99 时延、超时数和深度上限错误数。
• 确认 parent_id 索引被使用，临时表磁盘写入没有持续增长。
• 复盘错误父子边由哪个写入入口产生，并在该入口增加祖先检查。
• 为自环、两节点环、长链、宽树和正常多分支分别补充测试数据。

常见问题

只用 UNION DISTINCT 能防止所有环吗？

不一定。它能消除完全相同的结果行，但当结果包含不断变化的 depth 或 path 时，同一节点的行并不完全相同，仍可能继续递归。显式的当前路径检测更可靠。

把 cte_max_recursion_depth 调大就可以了吗？

不建议。调大只会延后服务器终止时点，不能修复环，也不能控制宽树造成的行数膨胀。先完善业务终止条件，再按真实需求设置会话保险。

path 用 JSON 数组会不会更好？

JSON 数组能避免字符串分隔符冲突，适合字符串型或复杂 ID；分隔字符串对数值 ID 更容易查看和排障。两种方案都要评估路径容量与函数成本。

深度限制为什么不能替代环检测？

深度限制只能保证最终停止，但不会告诉你数据已经成环，也可能返回重复路径。环检测负责识别坏边，深度限制负责保护业务边界，两者应同时存在。

一条可上线的 MySQL 递归 CTE，应该同时具备业务深度、当前路径、环标记、查询超时和服务器层数保险。出现故障时先终止查询和降级流量，再修复错误父子边，最后把防环检查放回写入入口。这样才能从“查询不会无限跑”升级到“坏数据能够被定位并不再产生”。