MariaDB 10.5递归SQL查询触发无限循环问题如何解决？

问题原因 #

• 存在自环无效边：原edges公共表表达式未过滤t1.ltrgr_lagr_id = t2.ltrgr_lagr_id的自关联场景，生成了大量指向自身的无效边，大幅增加了递归计算量。
• 重复行导致数据指数级膨胀：原递归CTE使用UNION ALL，不会自动去重，相同的路径行被反复生成并参与下一轮递归，数据量指数级增长，看起来像无限循环，最终导致数据库挂起。
• 访问判断效率低：使用字符串拼接+find_in_set判断节点是否已访问，随着路径变长匹配效率急剧下降，进一步放大性能问题。

修复方案 #

方案1：最小改动修复原SQL #

仅修改原SQL的两处问题即可解决无限循环：

1. 在edges层新增过滤条件去掉自环
2. 将UNION ALL替换为UNION DISTINCT自动去重，避免重复行无限累加
   修改后代码如下：

with
  recursive 
      edges as (
          select t1.ltrgr_lagr_id as lagr_id1, t2.ltrgr_lagr_id as lagr_id2
          from c_logistics_tran_group3 t1 
          inner join c_logistics_tran_group3 t2 on t2.ltrgr_ltran_id = t1.ltrgr_ltran_id
          where t1.ltrgr_lagr_id in(2154, 2156, 2157, 2158) 
            and t2.ltrgr_lagr_id in(2154, 2156, 2157, 2158)
            and t1.ltrgr_lagr_id != t2.ltrgr_lagr_id -- 新增：去掉自环边
      ),
      cte as (
          select lagr_id1, lagr_id2, concat(lagr_id1, ',', lagr_id2) as visited
          from edges
          union distinct -- 替换UNION ALL为UNION DISTINCT去重
          select c.lagr_id1, e.lagr_id2, concat(c.visited, ',', e.lagr_id2)
          from cte c
          inner join edges e on e.lagr_id1 = c.lagr_id2
          where not find_in_set(e.lagr_id2, c.visited)
      )
select * from cte;

方案2：高性能连通分量查询（无需完整路径场景） #

如果业务只需要获取所有互相连通的lagr_id集合，不需要输出所有路径，建议使用以下性能更高的写法，避免生成大量无效路径：

-- 先限制递归深度，避免极端场景挂库
SET max_recursive_iterations = 100;
WITH RECURSIVE connected_nodes AS (
    -- 初始节点：取所有待查询的lagr_id作为各连通分量起点
    SELECT ltrgr_lagr_id AS node, ltrgr_lagr_id AS component_id
    FROM c_logistics_tran_group3
    WHERE ltrgr_lagr_id IN (2154, 2156, 2157, 2158)
    GROUP BY ltrgr_lagr_id
    UNION DISTINCT
    -- 递归查找和当前节点共享ltran_id的其他关联lagr_id
    SELECT t2.ltrgr_lagr_id AS node, cn.component_id
    FROM connected_nodes cn
    JOIN c_logistics_tran_group3 t1 ON t1.ltrgr_lagr_id = cn.node
    JOIN c_logistics_tran_group3 t2 ON t2.ltrgr_ltran_id = t1.ltrgr_ltran_id
    WHERE t2.ltrgr_lagr_id IN (2154, 2156, 2157, 2158)
      AND t2.ltrgr_lagr_id NOT IN (SELECT node FROM connected_nodes WHERE component_id = cn.component_id)
)
-- 按连通分量分组输出关联的lagr_id集合
SELECT component_id, GROUP_CONCAT(DISTINCT node ORDER BY node) AS connected_lagr_ids
FROM connected_nodes
GROUP BY component_id;

额外建议 #

可以根据业务实际的连通分量最大长度，提前设置递归迭代次数上限，避免极端异常场景下数据库资源被长时间占用，示例：SET max_recursive_iterations = 100;

内容的提问来源于stack exchange，提问作者Vilius Gaidelis