Hierarchical Data Management, unlimited classification, design and optimization of MySQL _ MySQL

MySQL hierarchical data management, unlimited classification, design and optimization bitsCN.com

Hierarchical Data Management, unlimited classification, design and optimization of MySQL

1. This article describes common parent_id-based adjacent table models:

  CREATE TABLE category(      category_id INT AUTO_INCREMENT PRIMARY KEY,      name VARCHAR(20) NOT NULL,      parent INT DEFAULT NULL    );    +-------------+----------------------+--------+    | category_id | name                 | parent |    +-------------+----------------------+--------+    |           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 |    |          10 | 2 WAY RADIOS         |      6 |    +-------------+----------------------+--------+

And the Nested Set model based on the "first-order traversal algorithm:

CREATE TABLE nested_category (      category_id INT AUTO_INCREMENT PRIMARY KEY,      name VARCHAR(20) NOT NULL,      lft INT NOT NULL,      rgt INT NOT NULL    );    +-------------+----------------------+-----+-----+    | category_id | name                 | lft | rgt |    +-------------+----------------------+-----+-----+    |           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 |    +-------------+----------------------+-----+-----+

2. analysis and comment

I personally think of each of the above two algorithms and their advantages. The most common scenarios of the category on the page, on the web site, are:

1. "retrieve the direct subnode of a node"

2. "retrieve the complete subtree"

Scenario PK:

1. "retrieve the direct subnode of a node"

Is to find the direct subordinate element of a directory, such as querying the direct subordinate element of 'portable ELECTRONICS:

For the "parent_id-based adjacent table model", directly

    "SELECT id,name FROM category WHERE parent_id = 6;"

You can find all the elements of a specific parent_id.

For the "Nested Set model", the method in the original article can be complicated:

    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 noe.name         HAVING depth <= 1         ORDER BY node.lft;

This is the most common scenario. I believe that the performance of "nested sets" is not very good, and the "adjacent table model" is much better!

2. "retrieve the complete subtree"

For example, query the subtree with "portable electronics" as the root

For the "parent_id-based adjacent table model", it is very complicated and involves recursive operations. the client code will be very complicated, and the same recursive search with the stored procedure will not work.

For the "Nested Set model", it is quite simple:

  "SELECT id,name,parent_id FROM category WHERE lft BETWEEN 10 AND 19 ORDER BY lft" 

Here, the nested set model has a much better performance!

3. unlimited classification optimization

Can we integrate the "adjacent table model" and "nested set model? Let's try.

    CREATE TABLE category (      id INT AUTO_INCREMENT PRIMARY KEY,      name VARCHAR(20) NOT NULL,      lft INT NOT NULL,      rgt INT NOT NULL,      parent_id INT    );

On the surface, it seems to be a simple data integration. the functions of the above two modes are actually integrated,

For 1. scenario of "retrieving direct subnodes of a node" (using the features of the "adjacent table model"): "SELECT id, name FROM category WHERE parent_id = 6;" for 2. "retrieve the complete subtree" scenario (using the feature of "nested set model"): "SELECT id, name, parent_id FROM category WHERE lft BETWEEN 10 AND 19 ;"

This is "adjacent table-nested set-hybrid model ",

Compared with the "nested set model", simply adding the "parent_id" field gets the advantage of the "adjacent table model". the advantages of the combination of the adjacent table and nested set are very good.

BitsCN.com