[セットトップ]MySQLの階層データ管理無限レベル分類設計と最適化

最近SQLに基づく无限级の分类の目次のモジュールをして、ネット上でこの文章を见て、とても悪くありません.
原文は次のとおりです.http://ftp.nchu.edu.tw/MySQL/tech-resources/articles/hierarchical-data.html
http://wenku.baidu.com/view/53c68dd049649b6648d74746.html
以下の無限級分類最適化を見る前に、原文を見てください.
---------------------------------------------------------------------------------------------------

1.よくあるparentベースの記事を紹介しました.idの隣接テーブルモデル:

 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 | +-------------+----------------------+--------+

   "      "     (Nested Set)  :  

 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.上記の2つのアルゴリズムを分析し、評価する個人的には、ページ上のカテゴリで、webサイトで最もよく見られるシーンは1だと思います.「ノードの直接サブノードを取得する」2.[完全なサブツリーを取得]シーンPK:1.「ノードを検索する直接サブノード」とは、「PORTABLE ELE ELECTROONICS」の直接下位要素を検索することです.「parent_idベースの隣接テーブルモデル」に対して、「SELECT id,name FROM category WHERE parent_id=6;特定のparent_を検索idのすべての要素でいいです.「ネストされた集合(Nested Set)モデル」の場合、原文の方法は複雑です.

 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;

これは最も一般的なシーンで、私は“ネストの集合”のここの性能があまりよくないことを信じて、ここの“隣接する表の模型”の性能はとても良いです!　
 2.「完全なサブツリーを検索」例えば「PORTABLE ELE ELECTROONICS」をルートとするサブツリーは「parent_idベースの隣接テーブルモデル」に対して複雑であり、再帰操作に関わる、クライアントコードでは複雑であり、記憶プロセスでは同じように再帰的に検索する、性能は本当にだめである."ネスト集合(Nested Set)モデル"については、かなり簡単:"SELECT id,name,parent_id FROM category WHERE lft BETWEEN 10 AND 19 ORDER BY lft"ここで"ネスト集合モデル"は性能がいい!
3.無限レベルの分類最適化では、「隣接テーブルモデル」と「ネストされた集合モデル」を統合できますか?やってみよう
　
    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     );
の表面は単純なデータ統合に見えるが、実際には上記の2つのモードの機能が統合されている.「ノードの直接サブノードを検索する」シーン(「隣接テーブルモデル」の特性を利用):「SELECT id,name FROM category WHERE parent_id=6;2について「完全なサブツリーを検索」シーン(「ネストされた集合モデル」の特性を利用):「SELECT id,name,parent_id FROM category WHERE lft BETWEEN 10 AND 19;これは「隣接テーブル-ネスト集合-ハイブリッドモデル」ですが、「ネスト集合モデル」に対して「parent_id」フィールドを簡単に追加するだけで「隣接テーブルモデル」のメリットが得られます.隣接テーブルとネスト集合のメリットが統合されていて、とてもいいですね