MySQL递归子查询优化求助：10万条数据表查询过慢问题

针对你用WITH RECURSIVE查询目标fact_id=69298的3级前置关联节点时出现的慢查询甚至无法完成的问题，结合你的表结构和现有索引情况，给出以下可落地的优化方案：

一、先排查递归CTE的写法逻辑 #

很多时候查询跑不完是因为递归逻辑错误导致无限循环或数据量爆炸。先确保你的递归语句正确限制了层级，且关联条件匹配业务逻辑。针对你的需求，正确的递归CTE模板应该是这样：

WITH RECURSIVE cte AS (
    -- 初始节点：目标fact_id的记录
    SELECT 
        fact_id, 
        from_id, 
        to_id, 
        1 AS level
    FROM icews14s
    WHERE fact_id = 69298
    UNION ALL
    -- 递归部分：查找前置节点（即to_id匹配上一级from_id的记录）
    SELECT 
        i.fact_id, 
        i.from_id, 
        i.to_id, 
        c.level + 1 AS level
    FROM icews14s i
    JOIN cte c ON i.to_id = c.from_id
    WHERE c.level < 3 -- 强制限制只递归到3级，避免无限循环
)
SELECT * FROM cte;

如果你的原语句没有加c.level < 3的限制，会触发无限递归（直到达到MySQL默认的cte_max_recursion_depth阈值），这必然导致查询无法完成。

二、优化索引适配递归查询 #

你现有的单字段索引from_id、to_id、link_time无法支撑高效的递归JOIN操作，需要创建复合索引来覆盖查询的关联和返回字段：

• 针对递归JOIN的核心条件i.to_id = c.from_id，创建复合索引：

  CREATE INDEX idx_toid_fromid_factid ON icews14s(to_id, from_id, fact_id);
  

  这个索引可以让MySQL快速定位到符合to_id匹配的记录，同时直接返回from_id和fact_id，避免回表查询全量字段。
• 如果你的查询需要用到relation_id或link_time，可以扩展索引包含这些字段：

  CREATE INDEX idx_toid_fromid_full ON icews14s(to_id, from_id, fact_id, relation_id, link_time);
  

三、改用固定层级JOIN替代递归CTE（推荐） #

因为你只需要固定3级的前置节点，用三次JOIN的方式往往比递归CTE更高效——MySQL对多表JOIN的优化逻辑更成熟，且能精准控制每一层级的数据范围：

-- 1级前置：目标节点的直接上游
SELECT 
    t1.fact_id AS target_fact,
    t2.fact_id AS level1_fact,
    t2.from_id AS level1_from,
    t2.to_id AS level1_to,
    t2.relation_id AS level1_rel
FROM icews14s t1
LEFT JOIN icews14s t2 ON t2.to_id = t1.from_id
WHERE t1.fact_id = 69298
UNION ALL
-- 2级前置：1级节点的上游
SELECT 
    t1.fact_id AS target_fact,
    t3.fact_id AS level2_fact,
    t3.from_id AS level2_from,
    t3.to_id AS level2_to,
    t3.relation_id AS level2_rel
FROM icews14s t1
LEFT JOIN icews14s t2 ON t2.to_id = t1.from_id
LEFT JOIN icews14s t3 ON t3.to_id = t2.from_id
WHERE t1.fact_id = 69298 AND t2.fact_id IS NOT NULL
UNION ALL
-- 3级前置：2级节点的上游
SELECT 
    t1.fact_id AS target_fact,
    t4.fact_id AS level3_fact,
    t4.from_id AS level3_from,
    t4.to_id AS level3_to,
    t4.relation_id AS level3_rel
FROM icews14s t1
LEFT JOIN icews14s t2 ON t2.to_id = t1.from_id
LEFT JOIN icews14s t3 ON t3.to_id = t2.from_id
LEFT JOIN icews14s t4 ON t4.to_id = t3.from_id
WHERE t1.fact_id = 69298 AND t3.fact_id IS NOT NULL;

这种方式可以避免递归CTE的额外开销，同时利用我们创建的复合索引大幅加速JOIN操作。

四、排查数据量异常情况 #

如果调整后查询依然缓慢，检查目标节点fact_id=69298的from_id对应的匹配记录数：如果某一层级的匹配记录数特别多（比如上万条），会导致后续层级数据量指数级增长。这种情况下，可以考虑按link_time过滤取最新的N条，或者确认业务逻辑是否真的需要所有关联节点。

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