预排序遍历树算法

嵌套集模型(The Nested Set Model)示意图

 

 

 

上图表现了数据新的层级关系,关系表和数据如下

CREATE TABLE nested_category (
category_id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(20) NOT NULL,
lft INT NOT NULL,
rgt INT NOT NULL
);


INSERT INTO nested_category
VALUES(1,'ELECTRONICS',1,20),(2,'TELEVISIONS',2,9),(3,'TUBE',3,4),
(4,'LCD',5,6),(5,'PLASMA',7,8),(6,'PORTABLE ELECTRONICS',10,19),
(7,'MP3 PLAYERS',11,14),(8,'FLASH',12,13),
(9,'CD PLAYERS',15,16),(10,'2 WAY RADIOS',17,18);


SELECT * FROM nested_category ORDER BY category_id;

这里将 left,right 修改为 lft,rgt因为这两个词在MYSQL中属于关键字 下面我们将插入的数据标识在图上:

 

 

 

同样,我们将数据标识在原来的结构上:

 

 

 

怎么样,是不是很明确了

下面使我自己标定一种形式,方便理解

[1
      [2
           [3 4] 
           [5 6] 
           [7 8]
      9] 
      [10
           [11
                 [12 13]
           14]
           [15 16]
           [17 18]
      19]
20]

--遍历整个树,查询子集 条件:左边 > 父级L, 右边 < 父级R

 1 SELECT node.name
 2 FROM nested_category AS node,
 3 nested_category AS parent
 4 WHERE node.lft BETWEEN parent.lft AND parent.rgt
 5 AND parent.name = 'ELECTRONICS'
 6 ORDER BY node.lft;
 7 
 8 +----------------------+
 9 | name                 |
10 +----------------------+
11 | ELECTRONICS          |
12 | TELEVISIONS          |
13 | TUBE                 |
14 | LCD                  |
15 | PLASMA               |
16 | PORTABLE ELECTRONICS |
17 | MP3 PLAYERS          |
18 | FLASH                |
19 | CD PLAYERS           |
20 | 2 WAY RADIOS         |
21 +----------------------+

 --查询所有无分支的节点 条件:右边 = 左边L + 1

1 SELECT name
2 FROM nested_category
3 WHERE rgt = lft + 1;

--查询某个字节点到根节点的路径

 1 SELECT parent.name
 2 FROM nested_category AS node,
 3 nested_category AS parent
 4 WHERE node.lft BETWEEN parent.lft AND parent.rgt
 5 AND node.name = 'FLASH'
 6 ORDER BY parent.lft;
 7 
 8 
 9 SELECT node.name, (COUNT(parent.name) - 1) AS depth
10 FROM nested_category AS node,
11 nested_category AS parent
12 WHERE node.lft BETWEEN parent.lft AND parent.rgt
13 GROUP BY node.name
14 ORDER BY node.lft;

--查询子节点的深度

 1 SELECT node.name, (COUNT(parent.name) - (sub_tree.depth + 1)) AS depth
 2 FROM nested_category AS node,
 3     nested_category AS parent,
 4     nested_category AS sub_parent,
 5     (
 6         SELECT node.name, (COUNT(parent.name) - 1) AS depth
 7         FROM nested_category AS node,
 8         nested_category AS parent
 9         WHERE node.lft BETWEEN parent.lft AND parent.rgt
10         AND node.name = 'PORTABLE ELECTRONICS'
11         GROUP BY node.name
12         ORDER BY node.lft
13     )AS sub_tree
14 WHERE node.lft BETWEEN parent.lft AND parent.rgt
15     AND node.lft BETWEEN sub_parent.lft AND sub_parent.rgt
16     AND sub_parent.name = sub_tree.name
17 GROUP BY node.name
18 ORDER BY node.lft;

--插入新节点 

1 LOCK TABLE nested_category WRITE;
2 SELECT @myRight := rgt FROM nested_category
3 WHERE name = 'TELEVISIONS';
4 UPDATE nested_category SET rgt = rgt + 2 WHERE rgt > @myRight;
5 UPDATE nested_category SET lft = lft + 2 WHERE lft > @myRight;
6 INSERT INTO nested_category(name, lft, rgt) VALUES('GAME CONSOLES', @myRight + 1,@myRight + 2);
7 UNLOCK TABLES;

先找到右边的值,例如节点TELEVISIONS右边的值

1 LOCK TABLE nested_category WRITE;
2 SELECT @myRight := rgt FROM nested_category
3 WHERE name = 'TELEVISIONS';

在 R = 9(L8, R9)与 L = 10(L10,R11) 节点之间插入一个新节点

那么所有 左值 和 右值 > 9 的节点的左值和右值需要 + 2

1 UPDATE nested_category SET rgt = rgt + 2 WHERE rgt > @myRight;
2 UPDATE nested_category SET lft = lft + 2 WHERE lft > @myRight;

左值 = 插入位置左边节点记录的右值 + 1, 右值 = 插入位置左边节点记录的右值 + 2

1 INSERT INTO nested_category(name, lft, rgt) VALUES('GAME CONSOLES', @myRight + 1,@myRight + 2);
2 UNLOCK TABLES;

新节点右边的节点(L10,R11)左值右值都需要 + 2 那么插入后的新值为 L12 R13
新节点的左值为 9 + 1 = 10 右值为 9 + 2 = 11

--删除新节点

删除节点的算法与添加一个节点的算法相反

删除一个没有子节点的节点

1 LOCK TABLE nested_category WRITE;
2 SELECT @myLeft := lft, @myRight := rgt, @myWidth := rgt - lft + 1
3 FROM nested_category
4 WHERE name = 'GAME CONSOLES';
5 DELETE FROM nested_category WHERE lft BETWEEN @myLeft AND @myRight;
6 UPDATE nested_category SET rgt = rgt - @myWidth WHERE rgt > @myRight;
7 UPDATE nested_category SET lft = lft - @myWidth WHERE lft > @myRight;
8 UNLOCK TABLES;

删除一个分支节点和它所有的子节点

1 LOCK TABLE nested_category WRITE;
2 SELECT @myLeft := lft, @myRight := rgt, @myWidth := rgt - lft + 1
3 FROM nested_category
4 WHERE name = 'MP3 PLAYERS';
5 DELETE FROM nested_category WHERE lft BETWEEN @myLeft AND @myRight;
6 UPDATE nested_category SET rgt = rgt - @myWidth WHERE rgt > @myRight;
7 UPDATE nested_category SET lft = lft - @myWidth WHERE lft > @myRight;
8 UNLOCK TABLES;

删除一个节点后移动到其他节点

1 LOCK TABLE nested_category WRITE;
2 SELECT @myLeft := lft, @myRight := rgt, @myWidth := rgt - lft + 1
3 FROM nested_category
4 WHERE name = 'PORTABLE ELECTRONICS';
5 DELETE FROM nested_category WHERE lft = @myLeft;
6 UPDATE nested_category SET rgt = rgt - 1, lft = lft - 1 WHERE lft BETWEEN @myLeft AND@myRight;
7 UPDATE nested_category SET rgt = rgt - 2 WHERE rgt > @myRight;
8 UPDATE nested_category SET lft = lft - 2 WHERE lft > @myRight;
9 UNLOCK TABLES;

总结:

预排序遍历树算法的核心就是牺牲了写的性能来换取读取的性能

在你的开发的应用遇到此类问题的时(读压力 > 写压力),尝试下使用预排序遍历树算法来提高你的程序的性能吧。

参考地址:http://be-evil.org/post-168.html

http://www.cnblogs.com/BigIdiot/archive/2013/04/02/2995248.html

转载于:https://www.cnblogs.com/phpfans/p/3282909.html

Taihom原创,因为是原创才要分的请支持一下, 该存储过程可以在版本>=MSSQL2000下使用 但在MSSQL2000下,MPTT_NODEAction的resetnode操作不能使用,但不影响整个分类的主体应用 感谢ben一同测试。如果你支持原创,请保留存储过程中对作者Taihom的文字注释和描述。 ---------------------------------------- MPTT分类算法的添加,修改,删除其实很容易,但是这个算法排序就不是这么容易了。 我这里已经把分类的移动和排序都重新处理了,实现了MPTT分类的排序和移动 为了保证分类左右节点的连续性,这个存储过程有检测节点连续性和完整性的处理。 理论上不会因为在添加、修改、删除、移动或者排序的操作中出现节点不正确的情况。 另外,这个分类也同时兼容传统的递归。表中的PID就是上一级的父节点。 完成和发布时间:17:23 2009/5/7 ---------------------------------------- 参考文档: http://dev.mysql.com/tech-resources/articles/hierarchical-data.html ---------------------------------------- 存储过程说明: MPTT_NODEAction @Act, @ID, @Name ---------------------------------------- 这个存储过程主要处理分类的添加删除修改和恢复节点等操作 ------------------参数说明-------------- @Act add:@ID=在哪个节点下添加,@Name=添加的名称 mod:@ID=修改哪个ID,@Name=修改的名称 del:@ID=删除哪个ID remove:@ID=从数据库删除已经被删除的ID resetnode:初始化所有节点,把所有节点初始化成根节点下的子节点 restore:恢复节点:@ID,把删除的节点恢复到哪个节点下,@Name(节点ID):恢复哪个节点 ---------------------------------------- MPTT_NODEMove @ID1 int,--从哪里移动 @ID2 int,--移动到哪里? @Dir varchar(2)='>>' --移动方式 ---------------------------------------- 这个存储过程主要负责节点的移动、排序 ------------------参数说明-------------- @Dir='<' 把节点@ID1移动到@ID2的前面 @Dir='>' 把节点@ID1移动到@ID2的后面 @Dir='>>' 把节点@ID1加入到@ID2,并且作为节点@ID2的最后一个子节点 ---------------------------------------- MPTT_NODEGet @Act nvarchar(10), @ID INT=0 ---------------------------------------- 这个存储过程主要负责节点的筛选和选择 ------------------参数说明-------------- @Act='chklink' 用来检测节点的排序连接是否断开,0表示正常,没有断开,不等于0表示排序有错 -------------- @Act='subnode' 用来获取节点@ID的子节点 -------------- @Act='fullpath' 用来获取节点@ID的全路径 -------------- @Act='delnode' 用来获取被删除的节点列表
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值