如何用SQL递归查询替代JPA递归实现零件谱系链查询以提升性能？

现有两张表Part和Part_Lineage，表结构及零件谱系关系如下：

Part表 #

id part
1  AA
2  AB
3  AC
4  AD
5  AE
6  AF
7  AG
8  BA
9  BB
10 BC
11 BD
12 BE
13 BF

Part_Lineage表 #

id part_id  replaces_part
1  1        2
2  2        4
3  4        5
4  4        6
5  5        7
6  5        8
7  6        9
8  7        10
9  7        12
10 8        11
11 9        13

谱系关系说明：AA替换AB，AB替换AD，以此类推。例如：

• 查询AA的完整谱系链为：AA, AB, AD, AE, AF, AG, BA, BB, BC, BD, BE, BF
• 查询AF的完整谱系链为：AF, BB, BF

当前使用JPA递归Java方法实现查询，但当谱系链包含200个零件时，API响应速度过慢。需求是通过单次SQL查询获取完整谱系零件以提升性能，且不存在循环谱系（如AA替换AB且AB替换AA）的情况。

现有JPA实现的性能瓶颈 #

当前递归Java方法存在核心性能问题：

• 每次递归调用都会发起两次数据库查询（findById和findByPartId），形成N+1查询问题，当谱系节点达200个时，会产生数百次DB请求，大幅增加网络开销和数据库负载
• Java层需维护集合并执行contains内存判断，数据量大时额外消耗性能

解决方案：SQL递归CTE（公共表表达式） #

主流关系型数据库（MySQL 8.0+、PostgreSQL、Oracle 11g+等）均支持递归CTE，可通过单次SQL遍历整个谱系树，直接返回完整结果集，彻底避免多次数据库交互。

1. 标准递归CTE写法（MySQL 8.0+、PostgreSQL通用）

以查询AA（id=1）的谱系为例：

WITH RECURSIVE part_lineage_tree AS (
    -- 初始节点：定位目标零件
    SELECT p.id, p.part
    FROM Part p
    WHERE p.id = 1
    UNION ALL
    -- 递归遍历：获取当前节点所有被替换的零件
    SELECT p.id, p.part
    FROM Part p
    JOIN Part_Lineage pl ON p.id = pl.replaces_part
    JOIN part_lineage_tree plt ON plt.id = pl.part_id
)
SELECT GROUP_CONCAT(part ORDER BY id SEPARATOR ', ') AS lineage_chain
FROM part_lineage_tree;

查询任意零件时，仅需修改初始节点的WHERE p.id = ?条件（比如查询AF时替换为p.id = 6）。

2. Oracle数据库写法（CONNECT BY语法）

Oracle支持CONNECT BY实现递归查询：

SELECT LISTAGG(p.part, ', ') WITHIN GROUP (ORDER BY LEVEL) AS lineage_chain
FROM Part p
CONNECT BY PRIOR p.id = (SELECT pl.replaces_part FROM Part_Lineage pl WHERE pl.part_id = p.id)
START WITH p.id = 1;

额外性能优化 #

• 为Part_Lineage表的关联字段创建索引，加速递归查询的关联匹配：

  CREATE INDEX idx_part_lineage_part_id ON Part_Lineage(part_id);
  CREATE INDEX idx_part_lineage_replaces ON Part_Lineage(replaces_part);
  

JPA中集成该SQL #

通过@Query注解在Repository中直接定义原生SQL查询，避免Java层递归：

@Repository
public interface PartRepository extends JpaRepository<Part, Long> {
    @Query(value = "WITH RECURSIVE part_lineage_tree AS (" +
                   "    SELECT p.id, p.part FROM Part p WHERE p.id = :id " +
                   "    UNION ALL " +
                   "    SELECT p.id, p.part FROM Part p " +
                   "    JOIN Part_Lineage pl ON p.id = pl.replaces_part " +
                   "    JOIN part_lineage_tree plt ON plt.id = pl.part_id" +
                   ") SELECT GROUP_CONCAT(part ORDER BY id SEPARATOR ', ') FROM part_lineage_tree",
           nativeQuery = true)
    String getLineageChainById(@Param("id") Long id);
}

调用时仅需一次数据库请求，直接获取拼接好的谱系字符串，性能会有质的提升。

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