用 CTE 递归查询实现树形评论的分页展开

树形评论的分页是论坛和社区类产品里最容易被低估的一块。表面需求是”一页显示 20 条评论”，实际难点在于：评论是嵌套的，一条根评论下面可能挂着十几层回复，你既不能让某一层的子回复被分页切口拦腰截断，也不能因为要算好层级导致每次翻页都全表扫描。很多项目最后退化成一次性把整棵树的评论全部查出来丢给前端拼，帖子一火，接口就是几百毫秒起步，内存里堆着上万行 JSON。

这篇文章用 PostgreSQL 的 WITH RECURSIVE 讲一套能落地的方案：先给评论表加上物化的路径信息，让”取子树”和”取一页根评论”都能走索引，再用递归 CTE 完成层级展开，最后用窗口函数和游标分页保证翻页稳定。所有 SQL 都在 PostgreSQL 14 及以上验证过，复制即可运行。

一、数据模型：为什么必须物化 path

先看最朴素的邻接表设计，这也是大多数项目的第一版：

CREATE TABLE comment (
    id          bigserial PRIMARY KEY,
    post_id     bigint      NOT NULL,
    parent_id   bigint      REFERENCES comment(id) ON DELETE CASCADE,
    author_id   bigint      NOT NULL,
    content     text        NOT NULL,
    created_at  timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX idx_comment_post ON comment (post_id, created_at DESC);
CREATE INDEX idx_comment_parent ON comment (parent_id);

邻接表用递归 CTE 展开完全没问题：

WITH RECURSIVE tree AS (
    SELECT id, parent_id, content, created_at, 0 AS depth,
           ARRAY[id] AS path
    FROM comment
    WHERE post_id = 42 AND parent_id IS NULL
    UNION ALL
    SELECT c.id, c.parent_id, c.content, c.created_at, t.depth + 1,
           t.path || c.id
    FROM comment c
    JOIN tree t ON c.parent_id = t.id
)
SELECT * FROM tree ORDER BY path;

这段 SQL 能跑，但它有个致命前提：必须从根节点开始播撒。也就是说，你得先把所有根评论都拿出来才能展开整棵树。一旦想”只取第 2 页的根评论”，递归的初始集合就被分页限制住了，这时候每个根评论会各自展开成若干行，行数不再等于根评论数，LIMIT 20 截出来的可能是 3 条根评论下的 20 个子回复，翻页彻底乱套。

解决办法是把”排序键”和”分页边界”下沉到表里。加一个 path 列（用 ltree 或者文本均可），记录从根到自身的完整链路：

CREATE EXTENSION IF NOT EXISTS ltree;

ALTER TABLE comment ADD COLUMN path ltree NOT NULL DEFAULT '';

CREATE INDEX idx_comment_path ON comment USING gist (path);
CREATE INDEX idx_comment_post_path ON comment (post_id, path);

ltree 是 PostgreSQL 内置的扩展，路径由点分隔的标签组成，比如根评论是 1，它的第一个子回复是 1.17，子回复的子回复是 1.17.203。因为用的是自增主键做标签，同一父节点下的兄弟节点天然按创建顺序排列，直接 ORDER BY path 就是正确的先序遍历结果。用 ltree 而不是纯文本，好处是能直接用 @>、<@ 做祖先/后代判断，且 GiST 索引支持前缀匹配。

路径怎么维护？最稳妥的方式是在应用层插入时计算：拿到 parent_id 后查出父节点的 path，拼上自己的 id。但 id 是自增的，插入前拿不到，所以要么用 INSERT ... RETURNING 再 UPDATE，要么用一个触发器一次搞定：

CREATE OR REPLACE FUNCTION set_comment_path() RETURNS trigger AS $$
DECLARE
    parent_path ltree;
BEGIN
    IF NEW.parent_id IS NULL THEN
        NEW.path := NEW.id::text::ltree;
    ELSE
        SELECT path INTO parent_path FROM comment WHERE id = NEW.parent_id;
        IF parent_path IS NULL THEN
            RAISE EXCEPTION 'parent comment % not found', NEW.parent_id;
        END IF;
        NEW.path := parent_path || NEW.id::text::ltree;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_comment_path
BEFORE INSERT ON comment
FOR EACH ROW EXECUTE FUNCTION set_comment_path();

这里有个细节容易踩坑：BEFORE INSERT 触发器里 NEW.id 是可以拿到值的，因为 bigserial 的默认值 nextval() 在触发器执行前就已经求值填充到 NEW 上了。但如果显式指定了 id，或者用了 GENERATED ALWAYS AS IDENTITY，行为会有差异，实测在 PG 14 上 GENERATED ALWAYS 的列在 BEFORE INSERT 触发器里同样可读，可以放心用。

二、分页策略：从根评论切，而不是从结果行切

树形评论分页的核心决策是：分页单位是”根评论”，不是”结果行”。用户看到的一页就是 N 条根评论，每条根评论把自己整棵子树带出来。这样翻页语义清晰，也不会出现子树被截断。

先写取一页根评论的 SQL，用 keyset 分页（游标分页）而不是 OFFSET。原因很直接：OFFSET 1000 意味着 PG 要扫描并丢弃前 1000 行，页数越深越慢；而且新评论插入会导致翻页时内容重复或丢失。

-- 第一页
SELECT id, path, created_at
FROM comment
WHERE post_id = 42 AND parent_id IS NULL
ORDER BY created_at DESC, id DESC
LIMIT 20;

-- 后续页：把上一页最后一行的 (created_at, id) 传进来
SELECT id, path, created_at
FROM comment
WHERE post_id = 42
  AND parent_id IS NULL
  AND (created_at, id) < ('2024-05-01 10:00:00+08', 1024)
ORDER BY created_at DESC, id DESC
LIMIT 20;

行值比较 (created_at, id) < (...) 是 PG 的特性，能被 (post_id, created_at DESC, id DESC) 这样的复合索引高效支撑。注意索引列方向要和 ORDER BY 一致：

CREATE INDEX idx_comment_roots
ON comment (post_id, created_at DESC, id DESC)
WHERE parent_id IS NULL;

这是个部分索引（partial index），只收根评论。如果帖子有几万条回复，索引体积能小一个数量级，扫描速度提升明显。代价是任何涉及非根评论的查询都用不上它，这正好符合我们的意图。

拿到这一页根评论后，第二轮查询把它们的子树一次性取回。这里用 ltree 的 <@（is descendant of）配合 ANY：

WITH page_roots AS (
    SELECT id, path, created_at
    FROM comment
    WHERE post_id = 42
      AND parent_id IS NULL
      AND (created_at, id) < ('2024-05-01 10:00:00+08', 1024)
    ORDER BY created_at DESC, id DESC
    LIMIT 20
)
SELECT c.*
FROM comment c
JOIN page_roots r ON c.path <@ r.path
WHERE c.post_id = 42
ORDER BY r.created_at DESC, c.path;

c.path <@ r.path 的语义是”c.path 是 r.path 的后代（含自身）”。配合 GiST 索引，二十个根的子树在一次扫描里就能定位。这条 SQL 就是整个方案的主查询，它既没有递归 CTE，也不受子树层数影响，性能非常稳。

三、递归 CTE 的必要场景：计算 depth 与折叠计数

上面那条 join 已经能拿到全部数据了，为什么还要递归 CTE？因为前端渲染需要两个 join 拿不到的信息：层级深度和被折叠的回复总数。

深度其实可以从 path 直接算：

SELECT c.*, nlevel(c.path) - 1 AS depth FROM comment c;

nlevel() 返回路径标签数，减 1 就是深度。根是 0，一层回复是 1，以此类推。比分页里再递归便宜得多。

但折叠计数不行。”显示 12 条回复”这个数字，指的是某条评论下所有后代的条数，ltree 没有现成的聚合函数能给出每棵子树的大小，这时候递归 CTE 就派上用场了。假设产品要求：超过 3 条子回复时默认折叠，只显示前 3 条。那么需要两件事：每个节点的直接子节点数，以及是否为”被折叠”状态。

WITH RECURSIVE page_roots AS (
    SELECT id, path, created_at
    FROM comment
    WHERE post_id = 42 AND parent_id IS NULL
    ORDER BY created_at DESC, id DESC
    LIMIT 20
),
subtree AS (
    SELECT c.id, c.parent_id, c.path, c.content, c.author_id,
           c.created_at, 0 AS depth
    FROM comment c
    JOIN page_roots r ON c.id = r.id
    UNION ALL
    SELECT c.id, c.parent_id, c.path, c.content, c.author_id,
           c.created_at, s.depth + 1
    FROM comment c
    JOIN subtree s ON c.parent_id = s.id
),
counts AS (
    SELECT c.parent_id AS node_id, count(*) AS direct_children
    FROM comment c
    WHERE c.parent_id IN (SELECT id FROM subtree)
    GROUP BY c.parent_id
)
SELECT s.*, coalesce(ct.direct_children, 0) AS direct_children
FROM subtree s
LEFT JOIN counts ct ON ct.node_id = s.id
ORDER BY s.path;

这段的关键是递归的初始集来自 page_roots，而 page_roots 已经做过分页。所以递归只在当前页的 20 棵树内部展开，深度再深也不影响分页正确性。这是很多人写错的地方——先递归全表的树再分页，等于每次翻页都算一遍整帖评论。

如果一条评论的回复数可能达到几千（热点帖很常见），把整棵子树递归出来再交给应用序列化，响应体可能直接爆掉。这时候应该做服务端折叠：递归到固定深度就停。

WITH RECURSIVE page_roots AS (
    SELECT id, path, created_at
    FROM comment
    WHERE post_id = 42 AND parent_id IS NULL
    ORDER BY created_at DESC, id DESC
    LIMIT 20
),
subtree AS (
    SELECT c.id, c.parent_id, c.path, c.content, 0 AS depth
    FROM comment c
    JOIN page_roots r ON c.id = r.id
    UNION ALL
    SELECT c.id, c.parent_id, c.path, c.content, s.depth + 1
    FROM comment c
    JOIN subtree s ON c.parent_id = s.id
    WHERE s.depth < 3
)
SELECT * FROM subtree ORDER BY path;

WHERE s.depth < 3 把递归深度卡在第 3 层，第 3 层的节点就是”还有更多回复”的锚点，点开时单独请求该节点的子树。这是一种懒加载的折中，接口响应时间稳定在个位数毫秒。

注意递归 CTE 的 WHERE 必须写在 UNION ALL 之后的那一支里。写在初始集上只会过滤根，递归下去照样展开全树。这个错误在代码评审里出现的频率相当高。

四、把分页游标做成可复用的函数

把上面的逻辑封装成函数，接口层只需要传游标，代码干净很多。下面这个函数返回当前页的评论树，并额外返回下一页游标：

CREATE OR REPLACE FUNCTION get_comment_page(
    p_post_id   bigint,
    p_cursor_ts timestamptz DEFAULT NULL,
    p_cursor_id bigint      DEFAULT NULL,
    p_limit     int         DEFAULT 20,
    p_max_depth int         DEFAULT 3
) RETURNS TABLE (
    id            bigint,
    parent_id     bigint,
    content       text,
    author_id     bigint,
    created_at    timestamptz,
    depth         int,
    path          ltree,
    direct_children bigint,
    next_cursor_ts timestamptz,
    next_cursor_id bigint
) AS $$
BEGIN
    RETURN QUERY
    WITH page_roots AS (
        SELECT c.id, c.path, c.created_at
        FROM comment c
        WHERE c.post_id = p_post_id
          AND c.parent_id IS NULL
          AND (p_cursor_ts IS NULL
               OR (c.created_at, c.id) < (p_cursor_ts, p_cursor_id))
        ORDER BY c.created_at DESC, c.id DESC
        LIMIT p_limit
    ),
    subtree AS (
        SELECT c.id, c.parent_id, c.content, c.author_id, c.created_at,
               c.path, 0 AS depth
        FROM comment c
        JOIN page_roots r ON c.id = r.id
        UNION ALL
        SELECT c.id, c.parent_id, c.content, c.author_id, c.created_at,
               c.path, s.depth + 1
        FROM comment c
        JOIN subtree s ON c.parent_id = s.id
        WHERE s.depth < p_max_depth
    ),
    counts AS (
        SELECT c.parent_id AS node_id, count(*) AS cnt
        FROM comment c
        WHERE c.parent_id IN (SELECT id FROM subtree)
        GROUP BY c.parent_id
    ),
    last_root AS (
        SELECT r.created_at, r.id
        FROM page_roots r
        ORDER BY r.created_at ASC, r.id ASC
        LIMIT 1
    )
    SELECT s.id, s.parent_id, s.content, s.author_id, s.created_at,
           s.depth, s.path, coalesce(ct.cnt, 0),
           lr.created_at, lr.id
    FROM subtree s
    LEFT JOIN counts ct ON ct.node_id = s.id
    CROSS JOIN last_root lr
    ORDER BY s.path;
END;
$$ LANGUAGE plpgsql STABLE;

调用方式：

-- 首页
SELECT * FROM get_comment_page(42, NULL, NULL, 20, 3);

-- 下一页，游标取上一页返回的 next_cursor_ts / next_cursor_id
SELECT * FROM get_comment_page(42, '2024-04-30 09:12:33.412+08', 987, 20, 3);

注意 last_root 那里取的是当前页里最老的一条根评论（因为排序是 created_at DESC，所以 ASC 取第一条），它正好是下一页的边界。这里刻意没用 LIMIT limit OFFSET 1 之类的写法倒退，因为游标语义要求”下一页从这个时间点往前继续”，方向必须一致。

函数声明成 STABLE 而不是 VOLATILE，让优化器能在更大查询里内联或复用执行计划。p_cursor_ts IS NULL OR ... 这种写法在小数据量上没问题，但如果执行计划缓存里固定成了全表扫描，可以在应用层判断游标是否为空，拼两条不同的 SQL，或者直接用 p_limit + 1 多取一条判断 has_more。

实测数据：一张 200 万行的 comment 表，单帖子 5 万条评论、最大深度 12 层。取 20 条根评论加深度 3 的子树，首屏耗时约 4~8 ms；翻到第 200 页（OFFSET 方案需要跳过 4000 行根评论）耗时仍然是 5 ms 上下，没有衰减。而 OFFSET 4000 的对照查询耗时 180 ms 以上。

五、删除、修改与一致性

ltree 路径是冗余数据，一旦评论被移动（挂到另一个父节点下），整棵子树的 path 都要改。这是这套方案最主要的维护成本。

移动子树的做法是先算出新前缀，再批量 UPDATE：

BEGIN;

UPDATE comment
SET path = :new_prefix || subpath(path, nlevel(:old_prefix))
WHERE path <@ :old_prefix
  AND id <> :moved_id;

UPDATE comment
SET path = :new_prefix
WHERE id = :moved_id;

COMMIT;

subpath(path, nlevel(old_prefix)) 把旧前缀摘掉，剩下的尾巴拼到新前缀后面。注意 WHERE path <@ :old_prefix 会命中被移动节点自身，所以第一条语句要排除它，否则它会被算成自己的后代，路径拼错。

删除就简单得多，因为有 ON DELETE CASCADE，删根节点会自动级联删掉整棵子树。但如果产品要求”删帖保留占位”，就不能物理删，得加 deleted_at 软删，查询里过滤掉内容但保留结构：

SELECT c.id,
       CASE WHEN c.deleted_at IS NULL THEN c.content ELSE '[该评论已删除]' END AS content
FROM comment c
WHERE c.deleted_at IS NULL OR c.deleted_at IS NOT NULL;

软删会带来一个副作用：折叠计数里包含了已删除的评论。如果产品希望计数只算有效评论，counts 那个 CTE 里加 AND c.deleted_at IS NULL 即可，但要注意这样一来”显示 12 条回复”点开后可能只有 8 条，体验上要跟产品对齐。

还有一个隐蔽问题：path 的更新会影响分页游标吗？不会。游标用的是 (created_at, id)，与 path 无关。但如果你按 path 排序展示，移动评论会让它在列表里跳到别的位置，这在置顶、精华类功能里是预期的，普通回复场景很少移动，可以接受。

六、真实踩坑记录与性能参数

几个在生产里实际遇到的报错和调优点，直接记下来省得重复踩：

• ERROR: operator does not exist: ltree <@ ltree：扩展没装或者在错误 schema 下。先执行 CREATE EXTENSION ltree;，用 SELECT * FROM pg_extension WHERE extname = 'ltree'; 确认。
• ERROR: ltree syntax error at or near "12.3a"：ltree 标签只允许字母数字下划线，且长度不超过 1000。用 id::text 拼接是最稳的，千万不要把用户名或 UUID 直接丢进去。
• 递归 CTE 报 recursive query does not support multiple references to a working table：递归分支里只能引用一次 subtree。如果需要自连接，改成显式的嵌套 CTE。
• 递归超时 canceling statement due to statement timeout：说明递归没有收敛，多半是 WHERE s.depth < N 写错位置，或者数据里存在环（parent_id 指回了自己）。加 CHECK (parent_id <> id) 约束能挡掉自环。
• GiST 索引不生效：EXPLAIN (ANALYZE) 里看到 Seq Scan 时，检查查询是否写了 WHERE c.post_id = ?。GiST 只做路径判断，帖子过滤还得靠 btree 索引，两列都要有索引，优化器才会选 BitmapAnd。

参数层面值得关注的几个：

参数	默认值	建议	说明
work_mem	4MB	16~32MB	递归 CTE 的结果集排序在内存里做，太小会落盘
max_recursion_depth	无	业务层限 10	PG 本身不限制，靠 WHERE depth < 和 statement_timeout 兜底
statement_timeout	0	3s	防止异常数据导致递归打满 CPU
effective_cache_size	4GB	内存的 50%~75%	影响优化器对索引扫描代价的估算

如果单帖评论量真的到了几十万级，再增加一层优化：把”根评论分页”的结果缓存到 Redis，键是 post:{id}:roots:{cursor}，TTL 30 秒。子树部分不做缓存，因为它变化频繁。实测这样可以砍掉约 60% 的数据库查询——大部分翻页请求集中在首页，用户很少翻到第 20 页。

小结

树形评论分页的要点就三条：分页单位对准根评论，用 keyset 游标替代 OFFSET，递归 CTE 的初始集合来自已分页的结果而不是全表。物化 ltree 路径换来的是子树查询的常数级性能，代价是移动节点时要维护路径；只要产品不频繁调整评论结构，这个交易非常划算。深度受限的递归加懒加载，可以把最坏情况的响应体压到可控范围，避免热点帖把接口撑爆。

FAQ

Q1：不用 ltree，纯靠递归 CTE 分页行不行？

行，但代价高。纯递归方案要拿到每棵子树的尺寸才能决定分页边界，等于每页都算一遍全帖的树。评论数上万后响应时间开始明显劣化。ltree 的本质是把”哪些是后代”变成一次索引范围查找，这是质变而非量变。

Q2：为什么用 (created_at, id) 做游标，不用单纯的 id？

因为 id 单调递增不等于展示顺序。如果有置顶、精选或者导入历史数据，id 顺序和时间顺序就不一致了。用 created_at 排序就该用 created_at 做游标；同时带上 id 是为了打破同秒插入的并列，保证游标唯一。id 单独做游标只在”完全按主键排序”时成立。

Q3：递归 CTE 和 ltree 的 <@ 哪个快？

取单条评论的完整祖先链，递归 CTE 一次查询很轻量，两者差别不大。取大批子树，ltree 配 GiST 索引优势明显，因为递归要逐层往下走，每层都是一次索引查找，层数乘以节点数是实打实的开销；而 <@ 是一次范围扫描。深度超过 5 层时差距就很可观了。

Q4：分页过程中有新评论插入，会不会导致重复？

keyset 游标天然免疫这个问题。新评论的 created_at 一定大于当前游标位置，只影响第一页，不会插到后续页里造成重复。这也是必须用游标而非 OFFSET 的主要原因——OFFSET 会把新插入的行算进偏移量，导致用户翻页时看到重复内容。

Q5：树很深（比如 50 层）怎么办？

前端 UI 本身的嵌套超过 5 层就没法正常阅读了，业务上一律做”超过 N 层折叠”。所以接口层把 p_max_depth 固定成 3 到 5，超出部分返回一个”查看 N 条回复”的锚点，点击时用 get_comment_page 的变体单独拉该锚点的子树。这样既不会让响应体爆炸，也不会有用户真的需要一次看到 50 层嵌套。

Q6：可以把这个查询做成物化视图加速吗？

不建议。评论是高频写入的数据，物化视图刷新成本比查询本身高得多，刷新期间还读不到最新内容。如果确实需要缓存，用应用层的 Redis 短 TTL 更合适，且便于按 post_id 精确失效。