自引用外键能否提升层级数据递归查询性能？含索引相关疑问

示例：计算某经理管辖的员工总数 #

针对给定的org表，使用递归CTE可以计算某经理（以ID=100为例）直接或间接管辖的员工总数，SQL语句如下：

WITH RECURSIVE subordinates AS (
    -- 获取该经理的直接下属
    SELECT id FROM org WHERE pid = 100
    UNION ALL
    -- 递归获取所有间接下属
    SELECT o.id FROM org o
    JOIN subordinates s ON o.pid = s.id
)
SELECT COUNT(*) AS total_subordinates FROM subordinates;

疑问解答 #

1. 自引用外键约束是否有助于提升递归CTE的性能？ #

不会直接提升性能。自引用外键的核心作用是保证数据完整性：它强制pid字段的值必须是org表中存在的id，避免出现指向无效员工的层级关系，防止递归查询时处理无意义的无效数据。但它本身不会为查询添加优化逻辑，也不会自动创建索引加速递归关联。

不过，由外键约束保证的合法层级数据，能让递归查询避免无效遍历，这算是间接减少了不必要的计算，但并非性能提升的直接因素。

2. 仅为ID字段加索引，能否提升示例查询的性能？ #

作用非常有限。递归CTE的性能瓶颈在于递归阶段的关联查询：每次递归都需要根据当前层级员工的id，查找所有pid等于该id的下属。这个过程的核心优化点是pid字段的索引——如果pid没有索引，每次关联都会触发全表扫描，数据量越大性能越差。

id作为主键本身已附带唯一索引，它仅在锚点查询（找直接下属）和递归关联的匹配环节发挥作用，但无法解决递归时查找下属的性能问题。所以仅为id加索引，对整个递归查询的性能提升几乎可以忽略。

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