評論表設計 - 路徑列舉、巢狀集、閉包表

boring發表於2021-07-01
巢狀

場景

設想現在有個需求：設計一個評論系統，要求使用者可以評論文章以及相互回覆，無層級數限制。
(程式設計師最常用的鄰接表這裡就不展開講了，小夥伴們可以自行百度。)

路徑列舉

路徑列舉是一個由連續的直接層級關係組成的完整路徑。如 /usr/local/lib 的 UNIX 路徑是檔案系統的一個路徑列舉，其中 usr 是 local 的父親，這也就意味著 usr 是 lib 的祖先。

在 comments 表中，我們使用型別為 VARCHAR 的 path 欄位來儲存內容為當前節點的最頂層的祖先到它自己的序列，就像UNIX的路徑一樣，你甚至可以使用 ‘/’ 作為路徑中的分割符。

表結構：

CREATE TABLE `comments` (
  `comment_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `path` varchar(100) DEFAULT NULL,
  `bug_id` bigint(20) unsigned NOT NULL,
  `author` varchar(60) NOT NULL,
  `comment_date` datetime NOT NULL,
  `comment` text NOT NULL,
  PRIMARY KEY (`comment_id`),
  KEY `bug_id` (`bug_id`)
);

INSERT INTO `comments` VALUES (1, '1/', 1, 'Fran', '2021-05-23 10:27:22', '這個Bug的成因是什麼');
INSERT INTO `comments` VALUES (2, '1/2/', 1, 'Ollie', '2021-05-23 10:29:26', '我覺得是一個空指標');
INSERT INTO `comments` VALUES (3, '1/2/3/', 1, 'Fran', '2021-05-23 10:30:00', '不，我查過了');
INSERT INTO `comments` VALUES (4, '1/4/', 1, 'Kukla', '2021-05-23 10:30:34', '我們需要查無效輸入');
INSERT INTO `comments` VALUES (5, '1/4/5/', 1, 'Ollie', '2021-05-23 10:31:01', '是的，那是個問題');
INSERT INTO `comments` VALUES (6, '1/4/6/', 1, 'Fran', '2021-05-23 10:31:19', '好，查一下吧');
INSERT INTO `comments` VALUES (7, '1/4/6/7', 1, 'Kukla', '2021-05-23 10:31:41', '解決了');
comment_id path author comment
1 1/ Fran 這個Bug的成因是什麼
2 1/2/ Ollie 我覺得是一個空指標
3 1/2/3/ Fran 不，我查過了
4 1/4/ Kukla 我們需要查無效輸入
5 1/4/5/ Ollie 是的，那是個問題
6 1/4/6/ Fran 好，查一下吧
7 1/4/6/7/ Kukla 解決了

image.png

你可以透過比較每個節點的路徑來查詢一個節點的祖先。比如，要找到評論#7（路徑為1/4/6/7）以及它祖先，可以這樣做：

SELECT * from comments AS c where '1/4/6/7/' like CONCAT(c.path,'%');

比如查詢評論#4（路徑為1/4）以及它的所有後代，可以使用如下的語句：

SELECT * from comments AS c where c.path like CONCAT('1/4/','%');

如果要計算從評論#4擴充套件出的所有評論中每個使用者的評論數量，可以這樣做：

SELECT author,count(*) from comments AS c where c.path like CONCAT('1/4/','%') GROUP BY c.author;

插入一個節點需要做的只是複製一份要插入節點的邏輯上的父親節點的路徑，並將這個新節點的ID追加到路徑末尾就行了。

INSERT INTO comments (author,comment_date,bug_id, comment) VALUES ('Ollie','2021-01-11', 1,'Good job!');

UPDATE comments 
SET path = ( SELECT b.path FROM ( SELECT CONCAT( path, '/8' ) AS path FROM comments WHERE comment_id = 7 ) AS b ) 
WHERE
    comment_id = 8;

路徑列舉的缺點：資料庫不能確保路徑的格式總是正確或者路徑中的節點確實存在。依賴於應用程式的邏輯程式碼來維護路徑的字串，並且驗證字串的正確性的開銷很大。無論將 VARCHAR 的長度設定為多大，依舊存在長度限制，因而並不能夠支援樹結構的無限擴充套件。

巢狀集

巢狀集解決方案是儲存子孫節點的相關資訊，而不是節點的直接祖先。我們使用兩個數字來編碼每個節點，從而表示這一資訊，可以將這兩個數字稱為 nsleft 和 nsright 。

表結構：

CREATE TABLE `comments` (
  `comment_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `nsleft` int(11) NOT NULL,
  `nsright` int(11) NOT NULL,
  `bug_id` bigint(20) unsigned NOT NULL,
  `author` varchar(200) NOT NULL,
  `comment_date` datetime NOT NULL,
  `comment` text NOT NULL,
  PRIMARY KEY (`comment_id`),
  KEY `bug_id` (`bug_id`)
);

INSERT INTO `comments` VALUES (1, 1, 14, 1, 'Fran', '2021-06-16 18:50:51', '這個Bug的成因是什麼');
INSERT INTO `comments` VALUES (2, 2, 5, 1, 'Ollie', '2021-06-16 18:53:07', '我覺得是一個空指標');
INSERT INTO `comments` VALUES (3, 3, 4, 1, 'Fran', '2021-06-16 18:53:36', '不，我查過了');
INSERT INTO `comments` VALUES (4, 6, 13, 1, 'Kukla', '2021-06-16 18:53:58', '我們需要查無效輸入');
INSERT INTO `comments` VALUES (5, 7, 8, 1, 'Ollie', '2021-06-16 18:54:19', '是的，那是個問題');
INSERT INTO `comments` VALUES (6, 9, 12, 1, 'Fran', '2021-06-16 18:54:47', '好，查一下吧');
INSERT INTO `comments` VALUES (7, 10, 11, 1, 'Kukla', '2021-06-16 18:55:06', '    解決了');

每個節點透過如下的方式確定 nsleft 和 nsright 的值：nsleft 的數值小於該節點所有後代的ID，同時 nsright 的值大於該節點所有後代的 ID。這些數字和 comment_id 的值並沒有任何關聯。

確定這三個值（nsleft，comment_id，nsrigh）的簡單方法是對樹進行一次深度優先遍歷，在逐層深入的過程中依次遞增地分配 nsleft 的值，並在返回時依次遞增地分配 nsright 的值。

comment_id nsleft nsright author comment
1 1 14 Fran 這個Bug的成因是什麼
2 2 5 Ollie 我覺得是一個空指標
3 3 4 Fran 不，我查過了
4 6 13 Kukla 我們需要查無效輸入
5 7 8 Ollie 是的，那是個問題
6 9 12 Fran 好，查一下吧
7 10 11 Kukla 解決了

image.png

一旦你為每個節點分配了這些數字，就可以使用它們來找到給定節點的祖先和後代。比如，可以透過搜尋哪些節點的ID在評論#4的 nsleft 和 nsright 範圍之間來獲取評論#4及其所有後代

SELECT
    c2.* 
FROM
    comments AS c1
    LEFT JOIN comments AS c2 ON c2.nsleft BETWEEN c1.nsleft 
    AND c1.nsright 
WHERE
    c1.comment_id = 4;

透過搜尋評論#6的ID在哪些節點的 nsleft 和 nsright 範圍之內，可以獲取評論#6及其所有祖先：

SELECT
    c2.*
FROM
    comments AS c1
    JOIN comments AS c2 ON c1.nsleft BETWEEN c2.nsleft AND c2.nsright 
WHERE
    c1.comment_id = 6;

對樹進行操作，比如插入和移動節點，使用巢狀集會比其他的設計複雜很多。當插入一個新節點時，你需要重新計算新插入節點的相鄰兄弟節點、祖先節點和它祖先節點的兄弟，來確保它們的左右值都比這個新節點的左值大。同時，如果這個新節點是一個非葉子節點，你還要檢查它的子孫節點。假設新插入的節點是一個葉子節點，如下的語句可以更新每個需要更新的地方：

假設新插入的節點是一個葉子節點（插入到第 5 個節點下，左右值為8，9）：

UPDATE comments
SET nsleft =
CASE

        WHEN nsleft >= 8 THEN
        nsleft + 2 ELSE nsleft 
    END,
    nsright = nsright + 2 
WHERE
    nsright >= 7;

INSERT INTO comments ( nsleft, nsright, bug_id, author, comment_date, COMMENT )
VALUES
    ( 8, 9, 1, 'Fran', '2021-06-16 19:55:06', 'Me too!' );

image.png

如果簡單快速地查詢是整個程式中最重要的部分，巢狀集是最佳選擇——比操作單獨的節點要方便快捷很多。然而，巢狀集的插入和移動節點是比較複雜的，因為需要重新分配左右值，如果你的應用程式需要頻繁的插入、刪除節點，那麼巢狀集可能並不適合，而且在巢狀集中查詢一個節點的直接父節點或者直接子節點，SQL 語句會很長很複雜。

閉包表

閉包表是解決分級儲存的一個簡單而優雅的解決方案，它記錄了樹中所有節點間的關係，而不僅僅只有那些直接的父子關係。

在設計評論系統時，我們額外建立了一張叫做 treepaths 的表，它包含兩列，每一列都是一個指向 comments 中的comment_id。

表結構：

CREATE TABLE `comments` (
  `comment_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `bug_id` bigint(20) unsigned NOT NULL,
  `author` varchar(60) NOT NULL,
  `comment_date` datetime NOT NULL,
  `comment` text NOT NULL,
  PRIMARY KEY (`comment_id`),
  KEY `bug_id` (`bug_id`)
);

CREATE TABLE `treepaths` (
  `ancestor` bigint(20) unsigned NOT NULL,
  `descendant` bigint(20) unsigned NOT NULL,
  PRIMARY KEY (`ancestor`,`descendant`),
  KEY `descendant` (`descendant`)
);

INSERT INTO `comments` VALUES (1, 1, 'Fran', '2021-06-16 19:27:22', '這個Bug的成因是什麼');
INSERT INTO `comments` VALUES (2, 1, 'Ollie', '2021-06-16 19:29:26', '我覺得是一個空指標');
INSERT INTO `comments` VALUES (3, 1, 'Fran', '2021-06-16 19:30:00', '不，我查過了');
INSERT INTO `comments` VALUES (4, 1, 'Kukla', '2021-06-16 19:30:34', '我們需要查無效輸入');
INSERT INTO `comments` VALUES (5, 1, 'Ollie', '2021-06-16 19:31:01', '是的，那是個問題');
INSERT INTO `comments` VALUES (6, 1, 'Fran', '2021-06-16 19:31:19', '好，查一下吧');
INSERT INTO `comments` VALUES (7, 1, 'Kukla', '2021-06-16 19:31:41', '解決了');

INSERT INTO `treepaths` VALUES (1, 1);
INSERT INTO `treepaths` VALUES (1, 2);
INSERT INTO `treepaths` VALUES (1, 3);
INSERT INTO `treepaths` VALUES (1, 4);
INSERT INTO `treepaths` VALUES (1, 5);
INSERT INTO `treepaths` VALUES (1, 6);
INSERT INTO `treepaths` VALUES (1, 7);
INSERT INTO `treepaths` VALUES (2, 2);
INSERT INTO `treepaths` VALUES (2, 3);
INSERT INTO `treepaths` VALUES (3, 3);
INSERT INTO `treepaths` VALUES (4, 4);
INSERT INTO `treepaths` VALUES (4, 5);
INSERT INTO `treepaths` VALUES (4, 6);
INSERT INTO `treepaths` VALUES (4, 7);
INSERT INTO `treepaths` VALUES (5, 5);
INSERT INTO `treepaths` VALUES (6, 6);
INSERT INTO `treepaths` VALUES (6, 7);
INSERT INTO `treepaths` VALUES (7, 7);

我們不再使用 comments 表來儲存樹的結構，而是將樹中任何具有祖先—後代關係的節點對都儲存在 treepaths 表的一行中，即使這兩個節點之間不是直接的父子關係；同時，我們還增加一行指向節點自己。

祖先 後代 祖先 後代 祖先 後代
1 1 1 7 4 6
1 2 2 2 4 7
1 3 2 3 5 5
1 4 3 3 6 6
1 5 4 4 6 7
1 6 4 5 7 7

這裡以 comment_id 為1 作為例子，當 comment_id 為1 treepaths 需要儲存的結構則是表格標紅的內容

image.png

透過 treepaths 表來獲取祖先和後代比使用巢狀集更加地直接。例如要獲取評論#4的後代，只需要在 treepaths 表中搜尋祖先是評論#4的行就可以了：

SELECT
    c.*,
    t.* 
FROM
    comments AS c
    JOIN TreePaths AS t ON c.comment_id = t.descendant 
WHERE
    t.ancestor = 4;

要獲取評論#6的所有祖先，只需要在 treepaths 表中搜尋後代為評論#6的行就可以了：

SELECT
    c.* 
FROM
    comments AS c
    JOIN treepaths AS t ON c.comment_id = t.ancestor 
WHERE
    t.descendant = 6;

要插入一個新的葉子節點，比如評論#5的一個子節點，應首先插入一條自己到自己的關係，然後搜尋 treepaths 表中後代是評論#5的節點，增加該節點和新插入節點的“祖先—後代”關係（包括評論#5的自我引用）：

{int} 為新增的 comment_id


INSERT INTO comments (author,comment_date,bug_id, comment) VALUES ('Ollie','2021-01-11', 1,'Good job!');

INSERT INTO treepaths ( ancestor, descendant ) SELECT
t.ancestor,
{int} 
FROM
    TreePaths AS t 
WHERE
    t.descendant = 5 UNION ALL
SELECT
    {int},
    {int};

要刪除一個葉子節點，比如評論#7，應刪除所有 treepaths 表中後代為評論#7的行：

DELETE FROM treepaths WHERE descendant = 7;

要刪除一棵完整的子樹，比如評論#4和它所有的後代，可刪除所有在 treepaths 表中後代為#4的行，以及那些以評論#4的後代為後代的行：

DELETE 
FROM
    treepaths 
WHERE
    descendant IN ( SELECT descendant FROM ( SELECT descendant FROM treepaths WHERE ancestor = 4 ) AS b );

閉包表的設計比巢狀集更加地直接，兩者都能快捷地查詢給定節點的祖先和後代，但是閉包表能更加簡單地維護分層資訊。這兩個設計都比路徑列舉更方便地查詢給定節點的直接後代和父代。

然而，你可以最佳化閉包表來使它更方便地查詢直接父親節點或子節點：在 treepaths 表中增加一個 path_length 欄位。一個節點的自我引用的 path_length 為0，到它直接子節點的 path_length 為1，再下一層為2，以此類推。查詢評論#4的子節點就變得很直接：

SELECT *
FROM treepaths
WHERE ancestor = 4 AND path_length = 1;

總結

路徑列舉能夠很直觀地展示出祖先到後代之間的路徑，但同時由於它不能確保引用完整性，使得這個設計非常地脆弱。列舉路徑也使得資料的儲存變得比較冗餘。

巢狀集是一個聰明的解決方案——但可能過於聰明瞭，它不能確保引用完整性。最好在一個查詢效能要求很高而對其他需求要求一般的場合來使用它

閉包表是最通用的設計，它要求一張額外的表來儲存關係，使用空間換時間的方案減少操作過程中由冗餘的計算所造成的消耗。

參考

  • 《MySQL 反模式》
本作品採用《CC 協議》，轉載必須註明作者和本文連結

相關文章