MySQL递归查询（多对多关联中间表）性能优化求助

问题分析 #

当前查询存在几个核心性能瓶颈：

1. 不必要的表关联：初始CTE中关联users表完全冗余，user_user.user_id直接对应users.id，且已有索引支持，无需额外关联。
2. 冗余字段导致CTE膨胀：pivot_parent_id、pivot_user_id等非必要字段会增大CTE数据集，拖慢递归过程。
3. 事后去重效率低下：使用DISTINCT在所有递归数据生成后去重，当数据量较大时，会产生极高的CPU和内存开销。
4. 路径生成错误：concat( path, 4, users.id )中的硬编码4属于逻辑错误，会导致路径混乱，可能引发不必要的重复递归。

优化步骤 #

1. 简化初始查询，移除冗余关联

直接从user_user表获取初始节点，无需关联users表，减少IO开销：

WITH recursive cte AS (
    SELECT
        uu.user_id AS id,
        1 AS depth,
        CAST(uu.user_id AS CHAR(65535)) AS path
    FROM user_user uu
    WHERE uu.parent_id = 4 -- 目标父节点ID
    UNION ALL
    SELECT
        uu.user_id AS id,
        c.depth + 1 AS depth,
        CONCAT(c.path, ',', uu.user_id) AS path
    FROM user_user uu
    INNER JOIN cte c ON c.id = uu.parent_id
    -- 防止循环引用+避免重复处理同一节点
    WHERE NOT FIND_IN_SET(uu.user_id, c.path)
)
SELECT id FROM cte;

2. 优化索引结构

为user_user表创建复合覆盖索引，让查询无需回表即可获取所需数据：

CREATE INDEX idx_user_user_parent_user ON user_user (parent_id, user_id);

该索引直接覆盖递归查询中parent_id过滤、user_id获取的需求，大幅提升关联效率。

3. 递归过程中主动去重

通过FIND_IN_SET(uu.user_id, c.path)检查当前节点是否已在路径中，从根源避免重复节点进入CTE，彻底替代事后DISTINCT的低效操作。

4. 避免循环引用

如果树形结构存在循环（如A是B的父，B又是A的父），NOT FIND_IN_SET(...)会终止递归，防止无限循环导致的资源耗尽。

针对"A用户(id=1)所有子节点"的优化查询 #

将初始条件改为parent_id=1即可，同样适用上述优化逻辑：

WITH recursive cte AS (
    SELECT
        uu.user_id AS id,
        1 AS depth,
        CAST(uu.user_id AS CHAR(65535)) AS path
    FROM user_user uu
    WHERE uu.parent_id = 1
    UNION ALL
    SELECT
        uu.user_id AS id,
        c.depth + 1 AS depth,
        CONCAT(c.path, ',', uu.user_id) AS path
    FROM user_user uu
    INNER JOIN cte c ON c.id = uu.parent_id
    WHERE NOT FIND_IN_SET(uu.user_id, c.path)
)
SELECT id FROM cte;

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