Pre-sequencing traversal tree algorithm (non-recursive infinite pole classification algorithm)

Multilayer data structures estimate that all web developers are not unfamiliar, and the classification of various software is based on multi-layered structure.

The following is a typical multilayer data structure:

Related CREATE DATA statement:
CREATE TABLE Category (
category_id INT auto_increment PRIMARY KEY,
Name VARCHAR () is not NULL,
Parent INT DEFAULT NULL);


INSERT into category
VALUES (1, ' ELECTRONICS ', NULL), (2, ' televisions ', 1), (3, ' TUBE ', 2),
(4, ' LCD ', 2), (5, ' PLASMA ', 2), (6, ' portable ELECTRONICS ', 1),
(7, ' MP3 PLAYERS ', 6), (8, ' FLASH ', 7),
(9, ' CD PLAYERS ', 6), (Ten, ' 2-radios ', 6);

SELECT * from category ORDER by category_id;

In this data structure, between the layers through the field parent to form the adjacency table, we query some level of the relationship is generally recursive way, traverse a hierarchical relationship of SQL query number will increase along the hierarchy, think at the level of 20, Take the number of queries from the top-level node to a certain underlying node.

To solve this problem, people come up with nested set models (the Nested set model), see:

Remains the same hierarchical relationship as the graph one, but replaces one form of representation the following is a new relational table and data (the relationship and data are the same as before, but the table structure is not the same):

CREATE TABLE Nested_category (
category_id INT auto_increment PRIMARY KEY,
Name VARCHAR () is 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), (Ten, ' 2-radios ', 17,18);


SELECT * from Nested_category ORDER by category_id;

This changes the left,right to LFT,RGT because these two words belong to the keyword in mysql below the data we will insert is identified on the graph:

Again, we identify the data in the original structure:

Well, isn't it clear?

Let me calibrate a form to make it easy to understand

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

traverse entire tree, query subset condition: Left > Parent L, right < parent R

SELECT Node.name
From Nested_category as node,
Nested_category as Parent
WHERE node.lft between Parent.lft and PARENT.RGT
and parent.name = ' ELECTRONICS '
ORDER by Node.lft;

+----------------------+
| name |
+----------------------+
| ELECTRONICS |
| Televisions |
| TUBE |
| LCD |
| PLASMA |
| Portable ELECTRONICS |
| MP3 PLAYERS |
| FLASH |
| CD PLAYERS |
| 2-Radios |
+----------------------+

-Query all nodes without branching condition: right = left L + 1

SELECT Name
From Nested_category
WHERE RGT = lft + 1;

-Query The path of a word node to the root node

SELECT Parent.name
From Nested_category as node,
Nested_category as Parent
WHERE node.lft between Parent.lft and PARENT.RGT
and node.name = ' FLASH '
ORDER by Parent.lft;


SELECT Node.name, (COUNT (Parent.name)-1) as depth
From Nested_category as node,
Nested_category as Parent
WHERE node.lft between Parent.lft and PARENT.RGT
GROUP by Node.name
ORDER by Node.lft;

-Query the depth of child nodes
SELECT Node.name, (COUNT (Parent.name)-(sub_tree.depth + 1)) as depth
From Nested_category as node,
Nested_category as parent,
Nested_category as Sub_parent,
(
SELECT Node.name, (COUNT (Parent.name)-1) as depth
From Nested_category as node,
Nested_category as Parent
WHERE node.lft between Parent.lft and PARENT.RGT
and node.name = ' portable ELECTRONICS '
GROUP by Node.name
ORDER by Node.lft
) as Sub_tree
WHERE node.lft between Parent.lft and PARENT.RGT
and node.lft between Sub_parent.lft and SUB_PARENT.RGT
and sub_parent.name = Sub_tree.name
GROUP by Node.name
ORDER by Node.lft;

-Insert new node
Detailed algorithm:
1. All categories left and right values > Insert node left node record right value of all + 2
2. Insert node Lvalue = insert position left node record right value + 1, right value = insert position left node record right value + 2
Example:
Insert a new node between R = 9 (L8, R9) and L = ten (L10,r11) nodes
Then all left and right values > 9 of the node's left and right values need to be + 2
For example, the right-hand node (L10,R11) of the new node must have a value of 2 and the new value after insertion is L12 R13
The left value of the new node is 9 + 1 = 10 The right value is 9 + 2 = 11
SQL statement Implementation
LOCK TABLE nested_category WRITE;
SELECT @myRight: = RGT from Nested_category
WHERE name = ' televisions ';
UPDATE nested_category SET RGT = rgt + 2 WHERE rgt > @myRight;
UPDATE nested_category SET lft = lft + 2 WHERE lft > @myRight;
INSERT into Nested_category (name, LFT, RGT) VALUES (' GAME consoles ', @myRight + 1, @myRight +2);
UNLOCK TABLES;

-Delete new node
The algorithm to delete a node is the opposite of the algorithm for adding a node

Delete a node that has no child nodes
LOCK TABLE nested_category WRITE;
SELECT @myLeft: = LfT, @myRight: = RGT, @myWidth: = rgt-lft + 1
From Nested_category
WHERE name = ' GAME consoles ';
DELETE from Nested_category WHERE lft between @myLeft and @myRight;
UPDATE nested_category SET RGT = rgt-@myWidth WHERE rgt > @myRight;
UPDATE nested_category SET lft = lft-@myWidth WHERE lft > @myRight;
UNLOCK TABLES;

Delete a branch node and all of its child nodes
LOCK TABLE nested_category WRITE;
SELECT @myLeft: = LfT, @myRight: = RGT, @myWidth: = rgt-lft + 1
From Nested_category
WHERE name = ' MP3 PLAYERS ';
DELETE from Nested_category WHERE lft between @myLeft and @myRight;
UPDATE nested_category SET RGT = rgt-@myWidth WHERE rgt > @myRight;
UPDATE nested_category SET lft = lft-@myWidth WHERE lft > @myRight;
UNLOCK TABLES;

After deleting a node, move its byte point to
LOCK TABLE nested_category WRITE;
SELECT @myLeft: = LfT, @myRight: = RGT, @myWidth: = rgt-lft + 1
From Nested_category
WHERE name = ' portable ELECTRONICS ';
DELETE from nested_category WHERE lft = @myLeft;
UPDATE nested_category SET RGT = rgt-1, LfT = lft-1 WHERE lft between @myLeft and @myRight;
UPDATE nested_category SET RGT = rgt-2 WHERE rgt > @myRight;
UPDATE nested_category SET lft = lft-2 WHERE lft > @myRight;
UNLOCK TABLES;

Summarize:

The core of a pre-ordered traversal tree algorithm is to sacrifice write performance in exchange for read performance

When your development application encounters such problems (read pressure > Write pressure), try using the pre-ordered traversal tree algorithm to improve the performance of your program.

Pre-sequencing traversal tree algorithm (non-recursive infinite pole classification algorithm)