1、樹形結構應用場景

有時我們需要保存一些樹形的數據，比如組織架構、話題討論、知識管理、商品分類等，這些數據之間存在一種遞歸關系，很多開發人員想到的第一個解決方案往往是記錄每個節點的父節點，例如以下的評論表。

CREATE TABLE comments (

? ? comment_id int(10)? NOT NULL,

? ? parent_id int(10)? DEFAULT NULL,

? ? comment text? NOT NULL,

? ? PRIMARY KEY (comment_id)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

采用這樣的結構，就需要編寫復雜的代碼遞歸檢索很多記錄，查詢的效率就會很低。如果數據量不大、討論內容相對固定，數據的層次較少，那么采用這樣的結構就會是簡單的、清晰的，這種情況下此結構還是合適的；但如果數據量很大，查詢就會變得很復雜。下面介紹兩種更通用，擴展性更好的解決方案：路徑枚舉和閉包表。

2、路徑枚舉（增加一個字段存儲所有父級節點信息，用特殊符號分割開）

基于上面的表結構，可以增加一個字段path，用于記錄節點的所有祖先信息。記錄的方式是把所有的祖先信息組織成一個字符串。

因為路徑（path）字段包含了該節點的所有祖先信息，所以可以輕易地獲取某個節點的所有祖先節點，可以用程序先獲取path字符串，然后再使用切割字符串的函數處理得到所有的祖先節點。

如果要查找某個節點的所有后代，例如查找comment_id等于3的所有后代，可以使用如下的查詢語句。

SELECT * FROM comments WHERE path LIKE ‘1/3/_%’;

如果要查找下一層子節點，可以使用如下的查詢語句

SELECT * FROM comments WHERE path REGEXP “^1/3/[0-9]+/$”;

插入操作也比較簡單，只需要復制一份父節點的路徑，并將新節點的ID值（comment_id）添加到路徑末尾就可以了。

枚舉路徑的方式使得查詢子樹和祖先都變得更加簡單，查看分隔符即可知道節點的層次，雖然冗余存儲了一些數據，應用程序需要額外增加代碼以確保路徑信息的正確性，但這種設計的擴展性更好，更能適應未來數據的不斷增長。表5-2中，仍然保留了parent_id列，是為了使一些操作更加方便，也可以用來校驗路徑信息是否正確。

3、閉包表（增加一張父子節點關聯關系表）

閉包表也是一種通用的方案，它需要額外增加一張表，用于記錄節點之間的關系。它不僅記錄了節點之間的父子關系，也記錄了樹中所有節點之間的關系。

使用如下命令語句新建表path

CREATE TABLE path (

? ? ancestor int(11) NOT NULL,

? ? descendant int(11) NOT NULL,

? ? PRIMARY KEY (ancestor,descendant)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

ancestor表示祖先，descendant表示后代，存儲的是comment_id值。

如果要統計comment_id等于3的所有后代（不包括其自身），可以直接搜索path表祖先是3的記錄即可得到，查詢語句如下：

SELECT COUNT(*) FROM path WHERE ancestor=3 AND descendant <> 3;

為了更方便地查詢直接父節點/子節點，可以增加一個path_length字段以表示深度，節點的自我引用path_length等于0，到它的直接子節點的path_length等于1，再下一層為2，以此類推。

如上所述的數據結構，新增了一個表，用于存儲節點之間的信息，是一種典型的“以空間換時間”的方案，而且一個節點可以屬于多棵樹。相對于路徑枚舉，閉包表的節點關系更容易維護。

文章基于MySQL DBA修煉之道整理，版權屬于原作者

總結

以上是生活随笔為你收集整理的MySQL存储树形数据优化技笔记的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。