MySQL的分层数据管理 无限级分类 设计与优化

标签: mysql 数据管理 无限 | 发表时间:2013-09-15 04:52 | 作者:irelandken
出处:http://blog.csdn.net
     最近做个一基于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.分析与点评 

上述两种算法我个人觉得各和优点,在页面上的类目,在web网站里,最常见的场景是
        1."检索节点的直接子节点"
        2."检索完整的子树"
场景PK:
 1." 检索节点的直接子节点"
         就是查找一个目录的直接下级元素,如查询'PORTABLE ELECTRONICS'的直接下级元素:
         对于"基于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 ELECTRONICS"为根的子树
       对于"基于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
    );

        


    表面看上去只是简单的数据整合,实际上述 两种模式的功能都整合起来了,
    对于1."检索节点的直接子节点"的场景(利用"邻接表模型"的特性): 
         "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"字段,就获得了"邻接表模型"的优点,邻接表与嵌套集合的优点整合,非常不错呢
   
 

作者:irelandken 发表于2013-9-14 20:52:01 原文链接
阅读:86 评论:0 查看评论

相关 [mysql 数据管理 无限] 推荐:

MySQL的分层数据管理 无限级分类 设计与优化

- - CSDN博客数据库推荐文章
     最近做个一基于SQL的无限级分类的目录模块,在网上看到了这个文章,非常不错. 原文是: http://ftp.nchu.edu.tw/MySQL/tech-resources/articles/hierarchical-data.html.      在看下面的无限级分类优化之前,请大家先看看原文先哈.

Hadoop的数据管理

- - 技术改变世界 创新驱动中国 - 《程序员》官网
本文主要介绍Hadoop的数据管理,主要包括Hadoop的分布式文件系统HDFS、分布式数据库HBase和数据仓库工具Hive. HDFS是分布式计算的存储基石,Hadoop分布式文件系统和其他分布式文件系统有很多类似的特性:. 对于整个集群有单一的命名空间;. 具有数据一致性,都适合一次写入多次读取的模型,客户端在文件没有被成功创建之前是无法看到文件存在的;.

Cue:移动个人数据管理

- - 天涯海阁|Web2.0Share
Greplin最近发布2.0版本,同时改名为Cue. 那让我们来看看Cue的前身Greplin. 一名19岁的以色列高中生毕业生Daniel Gross就开发了一款新的搜索引擎Greplin,这个搜索引擎在使用时需要获得用户授权,可以访问该用户的社交网站、微博、在线文档、购物记录等,从而帮助用户快速搜索出那些用普通搜索引擎无法找到的信息.

[原]数据仓库元数据管理

- - oycn2010的专栏
元数据管理, 简单的做就是EXCEL结合版本管理等传统工具管理, 专业点就用专门的元数据管理工具;. 数据字典--> 数据知识库. 业务元数据,技术元数据,管理元数据. 参照:SAP元数据管理平台:按业务(角色)分类,按技术类型分类(特征,关键值,DSO,InfoCube),数据流程图. 按照传统的定义,元数据(Metadata)是关于数据的数据.

数据管理:表象之下、有容乃大

- - 技术改变世界 创新驱动中国 - 《程序员》官网
如果让数据管理市场的各类产品都凑到一起演奏一场打击乐,那么NoSQL无疑是鼓声最强的. 近两年随着消费型数据的急剧膨胀,NoSQL数据库在媒体和各种技术会议中也是风生水起,以至于参加这些会议时更多听到的是传统关系型数据库的“不是”. 尽管我们可以将这些消费型数据称为“金矿”,但它们毕竟不是铸好的金砖,关键信息还是继续保存在传统的商用数据库中.

谷歌将整合用户数据管理

- - Deutsche Welle: DW-WORLD.DE Top Stories
Google在本周二发表声明,表示其正在修订用户数据保护政策,以及更改其收集与使用用户资料的方式,提供更具个人化的搜寻结果和广告. 这一新的用户数据保护政策的将在今年3月1日正式施行,Google将在这一日期前通过邮件和各站点公告的方式通知其用户. 新的数据政策最大的改动是,Google将会整合现有的针对不同服务的超过70份的数据保护规定,并以一份统一政策替代.

元数据驱动的主数据管理平台

- - 人月神话的BLOG
前面谈MDM主数据管理的文章也比较多,本篇文章主要还是想谈下元数据驱动下的MDM主数据管理平台的核心构建思路. 因为对于一个MDM系统更多应该理解为结合了元数据驱动和建模,结合了流程引擎和ETL服务能力的一个快速开发和配置平台. 这个思路和原来我们谈到IBM-CQ变更和缺陷管理系统的构建思路完全是一致的.

Linux Ksplice,MySQL and Oracle

- Syn - DBA Notes
Oracle 在 7 月份收购了 Ksplice. 使用了 Ksplice 的 Linux 系统,为 Kernel 打补丁无需重启动,做系统维护的朋友应该明白这是一个杀手级特性. 现在该产品已经合并到 Oracle Linux 中. 目前已经有超过 700 家客户,超过 10 万套系统使用了 Ksplice (不知道国内是否已经有用户了.

MySQL Replication 线程

- - CSDN博客推荐文章
Replication 线程. Mysql 的Replication 是一个异步的复制过程,从一个Mysql instace(我们称之为Master)复制到另一个Mysql instance(我们称之Slave). 在Master 与Slave 之间的实现整个复制过程主. 要由三个线程来完成,其中两个线程(Sql 线程和IO 线程)在Slave 端,另外一个线程(IO 线程)在Master 端.

mysql backup 脚本

- - ITeye博客
网上备份脚本很多,但考虑都不周全. 保证创建备份文件只能是创建者跟root可以访问,其他用户没有权限,保证了数据库备份的安全. 上面脚本是负责备份的份数管理,. 已有 0 人发表留言,猛击->> 这里<<-参与讨论. —软件人才免语言低担保 赴美带薪读研.