您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 資料庫相關知識

PostgreSQL 遞歸查詢構建 Disqus 風格評論樹：MySQL 無法比擬的階層查詢能力

編輯：MySQL 資料庫相關知識

背景：Disqus 的評論系統挑戰

Disqus 作為大型評論平台，面臨百萬級評論樹的即時渲染需求。MySQL 缺乏原生遞歸查詢，常用「取全表記憶體組裝」或「修改前序遍歷」等變通方案，擴展性差。

PostgreSQL 遞歸 CTE (Common Table Expressions)

PG 8.4+ 支援標準 SQL:1999 遞歸 CTE，語法：

WITH RECURSIVE cte_name AS (
    -- 錨點查詢：根節點
    SELECT ...
    UNION ALL
    -- 遞歸查詢：子節點關聯父節點
    SELECT ... FROM table JOIN cte_name ON ...
)
SELECT * FROM cte_name ORDER BY path;

實戰：評論樹模型與遞歸查詢

建表

CREATE TABLE comments (
    id SERIAL PRIMARY KEY,
    message VARCHAR,
    author VARCHAR,
    parent_id INTEGER REFERENCES comments(id)
);

測試資料

INSERT INTO comments (message, author, parent_id) VALUES
('This thread is really cool!', 'David', NULL),      -- id=1 根
('Ya David, we love it!', 'Jason', 1),               -- id=2 回覆 1
('I agree David!', 'Daniel', 1),                     -- id=3 回覆 1
('gift Jason', 'Anton', 2),                          -- id=4 回覆 2
('Very interesting post!', 'thedz', NULL),           -- id=5 根
('You sir, are wrong', 'Chris', 5),                  -- id=6 回覆 5
('Agreed', 'G', 5),                                  -- id=7 回覆 5
('Fo sho, Yall', 'Mac', 5);                          -- id=8 回覆 5

遞歸查詢：按層級順序輸出完整樹

WITH RECURSIVE comment_tree AS (
    -- 錨點：根評論
    SELECT 
        id, message, author, 
        ARRAY[id] AS path,      -- 路徑陣列：用於排序
        parent_id, 
        1 AS depth
    FROM comments
    WHERE parent_id IS NULL
    
    UNION ALL
    
    -- 遞歸：子評論關聯父評論
    SELECT 
        c.id, c.message, c.author,
        ct.path || c.id,        -- 陣列拼接：父路徑 + 自己
        c.parent_id,
        ct.depth + 1
    FROM comments c
    JOIN comment_tree ct ON c.parent_id = ct.id
)
SELECT id, message, author, path, depth
FROM comment_tree
ORDER BY path;  -- 陣列排序自動實現樹狀順序

輸出結果

 id | message                      | author | path       | depth
----+------------------------------+--------+------------+-------
  1 | This thread is really cool!  | David  | {1}        | 1
  2 | Ya David, we love it!        | Jason  | {1,2}      | 2
  4 | gift Jason                   | Anton  | {1,2,4}    | 3
  3 | I agree David!               | Daniel | {1,3}      | 2
  5 | Very interesting post!       | thedz  | {5}        | 1
  6 | You sir, are wrong           | Chris  | {5,6}      | 2
  7 | Agreed                       | G      | {5,7}      | 2
  8 | Fo sho, Yall                 | Mac    | {5,8}      | 2

核心優勢分析

方案	MySQL (8.0 前)	PostgreSQL 遞歸 CTE
語法支援	無原生遞歸，需變通	標準 SQL WITH RECURSIVE
效能	全表掃描 + 應用層組裝	資料庫內部優化執行
網路傳輸	傳輸全表資料	僅傳輸結果集
排序邏輯	應用層實現	`ORDER BY path` 原生支援
深度控制	困難	`WHERE depth <= 3` 簡單
並行執行	無	PG 9.6+ 支援並行查詢

進階應用：分頁、深度限制、統計

只查詢前 2 層

WITH RECURSIVE ct AS (
    SELECT id, message, author, ARRAY[id] AS path, parent_id, 1 AS depth
    FROM comments WHERE parent_id IS NULL
    UNION ALL
    SELECT c.id, c.message, c.author, ct.path || c.id, c.parent_id, ct.depth + 1
    FROM comments c JOIN ct ON c.parent_id = ct.id
    WHERE ct.depth < 2  -- 限制深度
)
SELECT * FROM ct ORDER BY path;

計算每個根評論的總回覆數

WITH RECURSIVE ct AS (
    SELECT id, parent_id, ARRAY[id] AS path FROM comments WHERE parent_id IS NULL
    UNION ALL
    SELECT c.id, c.parent_id, ct.path || c.id FROM comments c JOIN ct ON c.parent_id = ct.id
)
SELECT 
    path[1] AS root_id,
    COUNT(*) - 1 AS reply_count  -- 減去根節點自己
FROM ct
GROUP BY path[1];

MySQL 8.0 追趕：CTE 支援

MySQL 8.0 同樣支援 WITH RECURSIVE，語法幾乎相同：

WITH RECURSIVE comment_tree AS (
    SELECT id, message, author, CAST(id AS CHAR(1000)) AS path, parent_id, 1 AS depth
    FROM comments WHERE parent_id IS NULL
    UNION ALL
    SELECT c.id, c.message, c.author, CONCAT(ct.path, ',', c.id), c.parent_id, ct.depth + 1
    FROM comments c JOIN comment_tree ct ON c.parent_id = ct.id
)
SELECT * FROM comment_tree ORDER BY path;

差異：MySQL 無陣列型別，用字串模擬路徑；PG 陣列操作更自然、效能更好。