轉一篇MYSQL文章《資料庫表設計，沒有最好只有最適合》

標籤：刪除節點   get   一點   path   長度   格式   模型   其他   web   

http://mp.weixin.qq.com/s/a8klpzM5iam0_JYSw7-U4g

我們在設計資料庫的時候，是否會突破常規，找到最適合自己需求的設計方案，下面來舉個例子：

 

常用的鄰接表設計，都會添加 一個 parent_id 欄位，比如地區表（國、省、市、區）：

 

CREATE TABLE Area (

[id] [int]  NOT NULL,

[name] [nvarchar]  (50) NULL,

[parent_id] [int]  NULL,

[type] [int]  NULL );

 

name:地區的名稱， parent_id 是父ID，省的父ID是國，市的父ID 為省，以此類推。

 

type 是地區的階級： 1：國，2：省，3：市，4：區

 

在層級比較確定的情況下，這麼設計表格沒有什麼問題，調用起來也很方便。

 

但是使用這種鄰接表設計方式，並不能滿足所有的需求，當我們不確定層級的情況下，假設我有下面一個評論結構：

 

 

用鄰接表記錄這個評論的資料（comments 表）：

 

 

大家有沒發現，這麼設計表，如果要查詢一個節點的所有後代，是很難實現的，你可以使用關聯查詢來擷取一條評論和他的後代：

 

SELECT c1.*, c2.* FROM comments c1 LEFT OUTER JOIN comments c2 ON c2.parent_id = c1.comment_id;

 

然而這個查詢只能擷取兩層的資料。這種樹的特性就是可以任意深地拓展，你需要有相應的方法來擷取它的深度資料。比如，可能需要計算一個評論分支的數量，或者計算一個機械裝置的所有的總開銷。

 

某些情況下，在項目中使用鄰接表正好適用。鄰接表設計的優勢在於能快速的擷取一個給定節點的直接父子節點，它也很容易插入新節點。如果這樣的需求就是你的項目對於分層資料的全部操作，那使用鄰接表就可以很好的工作了。

 

遇到上述的樹模型，有幾種方案是可以考慮下的：路徑枚舉、嵌套集以及閉包表。這些解決方案通常看上去比鄰接表複雜很多，但它們的確使得某些使用鄰接表比較複雜或很低效的操作變得更簡單。如果你的項目確實需要提供這些操作，那麼這些設計會是鄰接表更好的選擇。

 

一、路徑枚舉

 

在comments 表中，我們使用類型varchar 的path 欄位來替代原來的parent_id 欄位。這個path 欄位所儲存的內容為當前節點的最頂層祖先到它的自己的序列，就像UNIX的路徑一樣，你甚至可以使用 ‘/’ 作為路徑的分隔字元。

 

 

你可以通過比較每個節點的路徑來查詢一個節點祖先。比如：要找到評論#7， 路徑是 1/4/5/7一 的祖先，可以這麼做：

 

SELECT * FROM comments AS c WHERE ‘1/4/5/7‘ LIKE c.path || ‘%‘ ;

 

這句話查詢語句會匹配到路徑為 1/4/5/%，1/4/% 以及 1/% 的節點，而這些節點就是評論#7的祖先。

 

同時還可以通過將LIKE 關鍵字兩邊的參數互換，來查詢一個給定節點的所有後代。比如查詢評論#4，路徑path為 ‘1/4’ 的所有後代，可以使用如下語句：

 

SELECT * FROM comemnts AS c WHERE c.path LIKE ‘1/4‘ || ‘%‘ ;

 

這句查詢語句所有能找到的後台路徑分別是：1/4/5、1/4/5/6、1/4/5/7。

 

一旦你可以很簡單地擷取一棵子樹或者從子孫節點到祖先節點的路徑，你就可以很簡單地實現更多的查詢，如查詢一顆子樹所有節點上值的總和。

 

插入一個節點也可以像使用鄰接表一樣地簡單。你所需要做的只是複製一份要插入節點的父親節點路徑，並將這個新節點的ID追加到路徑末尾即可。

 

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

 

二、 嵌套集

 

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

 

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

 

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

 

 

 

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

 

SELECT c2.* FROM comments AS c1 JOIN comments AS c2 ON c2.nsleft BETWEEN c1.nsleft

AND c1.nsright WHERE c1.comment_id = 4;

 

比如搜尋評論#6及其所有祖先，可以通過搜尋#6的ID在哪些節點的nsleft 和 nsright 範圍之間，例：

 

SELECT c2.* FROM comments AS c1 JOIN comments AS c2 ON c1.nsleft BETWEEN c2.nsleft

AND c2.nsright WHERE c1.comment_id = 6;

 

使用嵌套集設計的主要優勢是，當你想要刪除一個非葉子節點時，它的後代會自動替代被刪除的節點，成為其直接祖先節點的直接後代。就是說已經自動減少了一層。

 

然而，某些在鄰接表的設計中表現得很簡單的查詢，比如擷取一個節點的直接父親或者直接後代，在嵌套集設計中會變得比較複雜。在嵌套集中，如果需要查詢一個節點的直接父親，我們會這麼做，比如要找到評論#6 的直接父親：

 

SELECT parent.* FROM comments AS c JOIN comments AS parent ON c.nsleft BETWEEN parent.nsleft ANDparent.nsright

LEFT OUTER JOIN comments AS in_between ON c.nsleft BETWEEN in_between.nsleft AND in_between.nsright

AND in_between.nsleft BETWEEN parent.nsleft AND parent.nsright WHERE c.comment_id = 6

AND in_between.comment_id IS NULL;

 

總之有些複雜。

 

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

 

如果簡單快速查詢是整個程式中最重要的部分，嵌套集是最好的選擇，比操作單獨的節點要方便快捷很多。然而，嵌套集的插入和移動節點是比較複雜的，因為需要重新分配左右值，如果你的應用程式需要頻繁的插入、刪除節點，那麼嵌套集可能並不合適。

 

三、閉包表

 

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

 

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

 

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

 

 

通過treepaths 表來擷取祖先和後代比使用嵌套集更加的直接。例如要擷取評論#4的後代，只需要在 treepaths 表中搜尋祖先是評論 #4的行就行了。同樣擷取後代也是如此。

 

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

 

INSERT INTO treepaths (ancestor, descendant)

SELECT t.ancestor, 8

FROM treepaths AS t

WHERE t.descendant = 6

UNION ALL SELECT 8, 8;

 

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

 

DELETE FROM treepaths WHERE descendant = 7;

 

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

 

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

 

然而你可以最佳化閉包表來使它更方便地查詢直接父親節點或者子節點： 在 treepaths 表中添加一個 path_length 欄位。一個節點的自我引用的path_length 為0，到它直接子節點的path_length 為1，再下一層為2，以此類推。這樣查詢起來就方便多了。

 

總結：你該使用哪種設計？

 

每種設計都各有優劣，如何選擇設計，依賴於應用程式的哪種操作是你最需要效能上的最佳化。

 

 

層級資料設計比較

 

1、鄰接表是最方便的設計，並且很多程式員都瞭解它

 

2、如果你使用的資料庫支援WITH 或者 CONNECT BY PRIOR 的遞迴查詢，那能使得鄰接表的查詢更高效。

 

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

 

4、嵌套集是一個聰明的解決方案，但可能過於聰明，它不能確保參考完整性。最好在一個查詢效能要求很高而對其他要求一般的場合來使用它。

 

5、閉包表是最通用的設計，並且以上的方案也只有它能允許一個節點屬於多棵樹。它要求一張額外的表來儲存關係，使用空間換時間的方案減少操作過程中由冗餘的計算所造成的消耗。

 

這幾種設計方案只是我們日常設計中的一部分，開發中肯定會遇到更多的選擇方案。選擇哪一種方案，是需要切合實際，根據自己項目的需求，結合方案的優劣，選擇最適合的一種。

 

我遇到一些開發人員，為了敷衍了事，在設計資料庫表時，只考慮能否完成眼下的任務，不太注重以後拓展的問題，不考慮查詢起來是否耗效能。可能前期資料量不多的時候，看不出什麼影響，但資料量稍微多一點的話，就已經顯而易見了（例如：可以使用外聯結查詢的，偏偏要使用子查詢）。

 

我覺得設計資料庫是一個很有趣且充滿挑戰的工作，它有時能體現你的視野有多寬廣，有時它能讓你睡不著覺，總之痛並快樂著。

 

轉一篇MYSQL文章《資料庫表設計，沒有最好只有最適合》