单纯的树



                                                       树还是树，你还需要考虑些什么呢？

                                                                            ——罗纳德·里根

引言

 今天我们来考虑这样一个问题，有关于树节点的操作。

问题一：一个公司里面有很多的层级关系，那么如何把公司的组织架构图的关系在数据库中保存呢？

问题二：我们知道我们平常所写的博客有评论，评论这跟树类似有层叠关系，那么如何在数据库中表示这种关系呢？

 

邻接表

下面就是关于邻接表分层存储的结构关系

 

Comment_id

Parent_id

author

comment

1

Null

王三

你的博客写的不错

2

1

作者

谢谢夸奖

3

2

王四

不，我看过了，漏洞百出

4

1

李四

你什么眼神

5

4

作者

哎，不带这么骂人的

6

4

田七

请注意文明用语

 

 

 

 

很多时候，我们都会采取这种操作来设计我们的数据库，而这种操作也会成为我们的默认方案，但我们会发现这里面存在一个问题，它无法满足树操作中最普通的一项：查询一个节点的所有后代，你可以使用一个关联查询来获取一条评论和它的直接后代

 获取后代

 

<span style="font-family:SimSun;font-size:18px;">select c1.*,c2.* from comments c1 left outer join comments c2on c2.parent_id =c1.comment_id </span>

然而，这个查询只能获取两层的数据，而我们就我们所了解的而言，树的特性是可以任意的扩展，因此你必须需要有方法来获取任意深度的数据。比如，可能需要计算一个评论分支的数量等

当然在使用邻接表的时候，我们可以通过增加的连接的扩展来进行查询，如下的查询能够获得四层数据，不能够更多了：

四层查询

 

<span style="font-family:SimSun;font-size:18px;">select c1.*,c2.* from comments  c1   -- 1st levelleft outer join comments  c2on c2.parent_id=c1.comment_id        --2nd  levelleft outer join comments c3on c3.parent_id=c2.comment_id   --3rd  levelleft outer join comments c4on c4.parent_id=c3.comment_id   --4th</span>

我们看到这样的查询很笨拙，因为随着后代的逐渐增加，必须同等的增加连接的列，这使得执行一个聚合函数变得极其困难。

好处

1．增加一个叶子节点

<span style="font-family:SimSun;font-size:18px;">insert into comments(comment_id,parent_id,author,comment) values('8','6','王小','规范我们的用语')</span>

2.修改一个节点的位置或者一棵树的位置也是简单的

<span style="font-family:SimSun;font-size:18px;">update comments set parent_id='3' where comment_id=5</span>

3.如果从一棵树中删除一个节点会变得比较复杂，因此不得不执行多次的查询来找到所有的后代节点，然后逐个从最低级别开始删除这些节点以满足外键的完整性。

<span style="font-family:SimSun;font-size:18px;">select comment_id from comments where parent_id='4'  --返回5和6  select comment_id from comments where parent_id='5'   --返回空值  select comment_id from comments where parent_id='6'   --返回7  select comment_id from comments where parent_id='7'   --返回 空值  --进行删除操作  delete from comments where comment_id in (4,5,6,7)</span>

 

4.假如要删除一个非叶子节点并且提升它的子节点，或者它的子节点移动到另一个节点下，那么首先要修改子节点的Parent_id，然后才能删除这个节点。

 

<span style="font-family:SimSun;font-size:18px;"> select parent_id from comments where comment_id ='6'  --return 4  update comments set parent_id=4 where parent_id=6   --变化其子节点的父节点位置  delete from comments where comment_id='6'  --最后才能删除这个节点</span>

 

以上就是使用邻接表需要多步操作才能完成的查询范例，你不得不写额外多的代码来维护。

 

合理使用邻接表

  不可否认，在某些情况下邻接表可以满足我们的需求，邻接表的优势在于能快速的获取一个给定节点的直接父子节点，它也很容易插入新节点，如果这些是你的需求的话，刚好可以使用邻接表来满足需求。

 

 

路径枚举

路径枚举，顾名思义就是通过保存路径的方式来查询我们所需要的信息。路径枚举通过将所有祖先的信息联合成一个字符串，并保存为每个节点的一个属性，很巧妙的解决了这个问题。

路径枚举引用的是一个完整路径。如/c/books/English的文件夹路径，其中c是book的父亲，这也就意味着books是english的父亲。

在comments表中，我们使用path字段来存储从当前节点的最顶层祖先一直到它自己的路径，就像上面所提到的一样。

 

Comment_id

Path

author

comment

1

1/

王三

你的博客写的不错

2

1/2

作者

谢谢夸奖

3

1/2/3

王四

不，我看过了，漏洞百出

4

1/4

李四

你什么眼神

5

1/4/5

作者

哎，不带这么骂人的

6

1/4/6

田七

请注意文明用语

优点

通过这个表的设计，以后可以通过比较每个节点的路径来查询一个节点的祖先。

查询1的所有后代

<span style="font-family:SimSun;font-size:18px;">select *from comments as cwhere c.path  like '1/' + '%'</span>

结果是查询的结果表中的全部path字段的值

 

通过上述可知，一旦你可以很简单的获得一棵树或者从子孙节点到祖先节点的就可以实现更多的查询功能。

计算从评论#1扩展出来的所有评论中每个用户的评论数量：

<span style="font-family:SimSun;font-size:18px;">select count(*) from comments as cwhere c.path like '1/' + '%'group by c.author </span>

插入一个节点

 

<span style="font-family:SimSun;font-size:18px;">insert into comments(comment_id,path,author,comment) values('9','1/6/9','王红','做得好')</span>

缺点

路径枚举也存在一个缺点，由于引用的是路径，所以我们无法确保路径中的节点确实存在，并且验证字符串的正确性开销很大。

 

 

嵌套集

  嵌套集对此的解决方案是，为每一个节点都匹配了相应的数字，可以将这两个数字称为nsleft和nsright.

Nsleft:该数值小于该节点后所有后代的ID。

Nsright：该数值大于该节点后所有后代的ID。

注意：这些数值和comment_id没有任何关联。

 

如下图的分配方式

注意：编码的中的数字就是如图这种方式所分配的

一旦为每个节点分配了这些数字，就可以使用它们来找到给定节点的祖先和后代。

评论#4的所有后代。

<span style="font-family:SimSun;font-size:18px;">select c2.*from comments c1join comments c2on c2.nsleft between c1.nsleft and c1.nsright where c1.comment_id='4'</span>

 

优势

当你想要删除一个非叶子节点时，它的后代会自动的代替被删除的节点，成为其直接祖先节点的直接后代。尽管每个节点的左右两个值在示例图中是有序分配的，而每个节点也总是和它相邻的父兄节点进行比较，但嵌套集设计并不必须保存分层关系。因而当删除一个节点造成数值不连续时，并不会对树的结构产生任何影响。

 

比如：你可以计算给定节点的深度然后删除它的父亲节点，随后你再次计算这个深度时，它已经自动减少了一层。

<span style="font-family:SimSun;font-size:18px;">select c1.comment_id,count(c2.comment_id) as depthfrom comments as c1join comments as c2on c1.nsleft between c2.nsleft and c2.nsrightwhere c1.comment_id =7group by c1.comment_id --结果等于3delete from comments where comment_id =6select c1.comment_id,count(c2.comment_id) as depthfrom comments as c1join comments as c2on c1.nsleft between c2.nsleft and c2.nsrightwhere c1.comment_id =7group by c1.comment_id --结果等于2</span>

如果是简单快速地查询时整个程序中最重要的部分，嵌套集是最佳的选择，然而嵌套集的插入和移动节点时比较复杂的，应为需要重新分配左右值，如果应用程序需要频繁的插入和删除节点，那么嵌套集并不合适。

 

 

哪种设计适合你

每种设计都有优劣，如下图

设计

表

查询子

查询树

插入

删除

引用完整性

邻接表

1

简单

困难

简单

简单

是

递归查询

1

简单

简单

简单

简单

是

枚举路径

1

简单

简单

简单

简单

否

嵌套集

1

困难

简单

困难

困难

否

 

小结

邻接表：最方便的设计，几乎都会用到这种方案。

递归查询：如果使用的数据库支持WITH递归查询，那么结合邻接表的话，查询会更高效

枚举路径：能够很直观的展示出祖先到后代之间的路径，但是由于引用路径的问题，使得这个设计非常的脆弱。

嵌套集：不能确保引用完整性。最好在一个查询性能要求很高而对其他需求要求一般的场合来使用它。