MySQL 递归 CTE 设置深度上限的安全边界

递归 CTE 最容易出问题的地方，不是语法，而是把“最多递归多少层”误当成了唯一的安全阀。一次层级组织查询变慢时，真正需要同时看的有三条线：业务允许的深度、可能生成的行数，以及单条 SQL 能占用的时间。

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

先分清递归深度、结果行数和执行时间

MySQL 8.4 中，cte_max_recursion_depth 的默认值是 1000，作用域同时支持 GLOBAL 和 SESSION。它限制的是递归层数，不等于结果行数：一个层级分支很多的组织树，几十层也可能产生大量行；反过来，线性链路可能递归很多层却只返回少量数据。

排障时可以先把三种上限画开。业务条件负责“树最多走到哪一层”；cte_max_recursion_depth 负责“服务器最多允许多少轮”；递归查询中的 LIMIT 负责“最多产出多少行”；MAX_EXECUTION_TIME 负责“单条 SELECT 最多运行多久”。它们是互补关系，不能用一个参数替代全部边界。

故障触发点通常是缺少业务深度条件

以组织树查询为例，接口允许调用方传入最大层级。原 SQL 只写了自连接，却没有把层级列带进递归成员；当脏数据形成环，或者调用方把上限放大时，查询就会一直扩展，最终表现为响应变慢、错误 3636，甚至临时表占用增加。

修复的第一步是让终止条件进入 SQL，而不是把希望寄托在服务器默认值上：

WITH RECURSIVE org_tree AS (
  -- 锚点只取目标组织，并把初始层级设为 0
  SELECT id, parent_id, name, 0 AS depth
  FROM org_unit
  WHERE id = ?
  UNION ALL
  -- 每轮只扩展下一层，并用参数限制业务深度
  SELECT child.id, child.parent_id, child.name, parent.depth + 1
  FROM org_unit AS child
  JOIN org_tree AS parent ON child.parent_id = parent.id
  WHERE parent.depth + 1 

第二个参数应由服务端校验后传入，例如把目录浏览限制在 32 层，而不是直接接受任意大整数。这个条件解决的是正常查询和异常环路的业务边界，不能替代服务器级保护。

把保护措施放在正确的作用域

低风险、可预期的查询可以使用会话级配置；高风险接口更适合把保护收窄到单条语句，避免污染连接池里后续请求。MySQL 文档同时给出了执行时间和语句提示的组合方式：

-- 仅为当前连接设置较小的递归保险值，执行完应由连接池重置
SET SESSION cte_max_recursion_depth = 64;

-- 语句级限制只影响本次 SELECT，并同时限制时间和输出行数
WITH RECURSIVE nums (n) AS (
  -- 锚点从 1 开始，避免空输入导致无意义递归
  SELECT 1
  UNION ALL
  -- 递归成员按明确条件递增
  SELECT n + 1 FROM nums WHERE n 

SET SESSION 会影响当前连接；SET_VAR 和 MAX_EXECUTION_TIME 更接近单语句边界。实际项目中不应把客户端传来的数值原样放进提示词或配置，应先做类型、范围和业务权限校验。

用复查动作防止下一次回归

发布修复后，先在同一连接查看实际会话值，再用 EXPLAIN 观察递归部分。递归 CTE 的成本是按迭代估算，不能只看一轮成本；结果过大时还可能触发内部临时表转磁盘。层级表应保证 parent_id 有合适索引，并把最大深度、返回行数、超时次数纳入监控。

-- 复查当前连接真正生效的递归上限
SHOW SESSION VARIABLES LIKE 'cte_max_recursion_depth';

-- 复查优化器是否识别出递归查询部分
EXPLAIN WITH RECURSIVE org_tree AS (
  -- 用固定锚点演示计划检查，不把用户输入拼进 SQL
  SELECT id, parent_id, 0 AS depth FROM org_unit WHERE id = 1
  UNION ALL
  SELECT child.id, child.parent_id, parent.depth + 1
  FROM org_unit child JOIN org_tree parent ON child.parent_id = parent.id
  WHERE parent.depth 

如果只是需要少量结果，优先缩小业务深度和递归 LIMIT；如果树本身可能很深，则把查询拆成分页或异步任务，并保留明确的超时。安全边界的目标不是让递归永远成功，而是让异常输入在可预测的范围内失败。

相关问题

把 cte_max_recursion_depth 调大就能解决报错吗？

不一定。它只能放宽递归层数，不能修复缺少终止条件、环路、行数爆炸或执行时间过长的问题。先确认业务深度和数据关系，再决定是否需要提高会话值。

LIMIT 能不能代替递归深度限制？

不能。LIMIT 主要限制返回行数，分支很多时仍可能在达到行数上限前消耗大量资源；深度条件、递归上限和时间上限应按风险组合使用。