执行 WITH RECURSIVE 查询长时间无结果问题分析

文章目录环境症状问题原因解决方案环境系统平台银河麒麟 海光版本9.0.4症状执行如下递归查询时SQL 长时间无返回结果数据库会话一直处于执行状态无法正常结束。WITHRECURSIVE subordinatesAS(SELECTid,name,manager_id,1ASlevelFROMemployeeWHEREid4UNIONALLSELECTe.id,e.name,e.manager_id,s.level1FROMemployee eJOINsubordinates sONe.manager_ids.id)SELECT*FROMsubordinates;示例数据如下id | name | manager_id ------------------------ 1 | 张总 | 2 | 李经理 | 1 3 | 王主管 | 2 4 | 赵组长 | 4 5 | 小孙 | 4 6 | 小周 | 4 7 | 钱经理 | 1 8 | 吴主管 | 7 9 | 郑组长 | 8执行 SQL 后终端一直无结果返回CPU 使用率持续升高需手动取消 SQL。问题原因原因是 员工表中存在循环引用Cycle数据。以上数据中id 4 manager_id 4 即 赵组长 │ └──────► 赵组长自己管理自己递归查询的执行过程如下第一次递归 查询 ID4 得到 4 第二次递归 查找 manager_id4 的员工 得到 4 5 6由于 ID4 再次被查询出来递归又会继续执行4 → 4、5、6 → 4、5、6 → 4、5、6 ……因此递归永远不会结束。由于 SQL 使用的是UNION ALL不会自动去除重复记录因此数据库会不断生成新的递归结果导致 SQL 一直执行直到达到资源限制或被人工终止。解决方案方案一修正错误数据首先检查是否存在员工管理自己的情况SELECT*FROMemployeeWHEREidmanager_id;如果查询到结果例如id | name | manager_id ------------------------ 4 | 赵组长 | 4说明数据存在异常。 应根据实际业务修改为正确的上级例如UPDATEemployeeSETmanager_id2WHEREid4;修改完成后再次执行递归查询即可正常返回结果。方案二检查是否存在循环引用除了自己管理自己还可能存在多个员工互相管理例如A → B B → C C → A这同样会导致递归无法结束。建议在导入或维护组织架构数据时检查是否存在循环引用避免形成闭环关系。方案三递归查询增加层级限制如果无法立即确认数据是否存在循环引用可以为递归增加最大层级限制避免 SQL 无限执行。 例如WITHRECURSIVE subordinatesAS(SELECTid,name,manager_id,1ASlevelFROMemployeeWHEREid4UNIONALLSELECTe.id,e.name,e.manager_id,s.level1FROMemployee eJOINsubordinates sONe.manager_ids.idWHEREs.level10)SELECT*FROMsubordinates;上述 SQL 最多递归 10 层即使存在异常数据也不会无限循环。方案四记录已访问节点避免重复递归对于层级查询建议记录已经访问过的节点避免重复访问同一员工。示例WITHRECURSIVE subordinatesAS(SELECTid,name,manager_id,ARRAY[id]ASpathFROMemployeeWHEREid4UNIONALLSELECTe.id,e.name,e.manager_id,s.path||e.idFROMemployee eJOINsubordinates sONe.manager_ids.idWHERENOTe.idANY(s.path))SELECT*FROMsubordinates;该方法能够有效避免因循环引用导致的无限递归是生产环境中推荐的递归查询写法。