MySQL存储树形数据优化技笔记

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修炼之道整理，版权属于原作者