树状结构数据的数据库表设计及使用 - 5. 四种模型的性能比较与分析

最新推荐文章于 2024-07-11 13:14:03 发布
最新推荐文章于 2024-07-11 13:14:03 发布
阅读量1.3k 收藏 6
点赞数 2
分类专栏： mysql 理论学习 数据结构与算法 文章标签： 数据库 database sql 树状结构数据
原文链接：https://www.modb.pro/db/378607
版权

本文以 MySQL 为例。文档比较长，故分为5部分发出：

📝 邻接表（Adjacency List）模型

🎨 路径枚举（Path Enumeration）模型

🧑‍💻 闭包表（Closure Table）模型

🤹 嵌套集（Nested Set）模型

🧮 性能比较与分析

附：
树状结构数据的数据库表设计及使用 - 1. 邻接表
树状结构数据的数据库表设计及使用 - 2. 路径枚举（Path Enumeration）模型
树状结构数据的数据库表设计及使用 - 3. 闭包表（Closure Table）模型
树状结构数据的数据库表设计及使用 - 4. 嵌套集（Nested Set）模型

性能比较

这里比较一下不同模型类似数据量（大概600多条）下的性能。

优化前

这里除了闭包表的关联表都未加索引。

获取全树：
SET @@profiling = 0;

SET @@profiling_history_size = 0;

SET @@profiling_history_size = 100; 

SET @@profiling = 1;



-- 邻接表

WITH RECURSIVE T1(ID,APPROVALGROUPNAME,FATHERID) AS (

SELECT T0.ID,T0.APPROVALGROUPNAME,T0.FATHERID FROM APPROVAL_GROUP T0 WHERE

    T0.FATHERID = -1 AND SHOWFLAG=1

UNION ALL

SELECT T2.ID,T2.APPROVALGROUPNAME,T2.FATHERID FROM APPROVAL_GROUP T2, T1

  WHERE T2.FATHERID = T1.ID AND SHOWFLAG=1

)

SELECT * FROM T1;



-- 路径枚举表

SELECT nodeid,node FROM AG_PathEnum WHERE path_string LIKE '16060/%';



-- 闭包表

SELECT n3.ID,n3.APPROVALGROUPNAME FROM APPROVAL_GROUP n1

INNER JOIN AG_Closure n2 ON n1.ID = n2.ancestor

INNER JOIN APPROVAL_GROUP n3 ON n2.descendant = n3.ID

WHERE n1.FATHERID = -1 AND n2.distance != 0;



-- 嵌套集

SELECT node.node,node.nodeid

FROM AG_Stack AS node,

        AG_Stack AS parent

WHERE node.lft > parent.lft AND node.rgt < parent.rgt

        AND parent.lft = 1

ORDER BY node.lft;



-- 查看性能

SHOW PROFILES;

+----------+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| Query_ID | Duration   | Query                                                                                                                                                                                                                                                                                                        |

+----------+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

|        1 | 0.01553425 | WITH RECURSIVE T1(ID,APPROVALGROUPNAME,FATHERID) AS ( SELECT T0.ID,T0.APPROVALGROUPNAME,T0.FATHERID FROM APPROVAL_GROUP T0 WHERE T0.FATHERID = -1 AND SHOWFLAG=1 UNION ALL SELECT T2.ID,T2.APPROVALGROUPNAME,T2.FATHERID FROM APPROVAL_GROUP T2, T1 WHERE T2.FATHERID = T1.ID AND SHOWFLAG=1 ) SELECT  |

|        2 | 0.00199475 | SELECT nodeid,node FROM AG_PathEnum WHERE path_string LIKE '16060/%' |

|        3 | 0.01929400 | SELECT n3.ID,n3.APPROVALGROUPNAME FROM APPROVAL_GROUP n1

INNER JOIN AG_Closure n2 ON n1.ID = n2.ancestor

INNER JOIN APPROVAL_GROUP n3 ON n2.descendant = n3.ID

WHERE n1.FATHERID = -1 AND n2.distance != 0 |

|        4 | 0.00121350 | SELECT node.node,node.nodeid FROM AG_Stack AS node, AG_Stack AS parent WHERE node.lft > parent.lft AND node.rgt < parent.rgt AND parent.lft = 1 ORDER BY node.lft                                                                                                                            |

+----------+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

可以看到嵌套集的查询速度对比邻接表是数量级上的快，600多条数据快了10倍；相较闭包表，嵌套集的速度也是快上6倍左右。而在没有优化的情况下，嵌套集与路径枚举表的速度差不多。

Explain分析

用 explain 语句分析一下四条 SQL，路径枚举表只用了1次简单查询，嵌套集2次，闭包表3次，邻接表4次。而且邻接表包含1个复杂查询、一个临时表的子查询和2个联合查询，其它模型都是简单查询（SIMPLE）。因为邻接表、路径枚举表和嵌套集都没有加索引，所以 type 都是 ALL 即全表扫描。

-- 邻接表

EXPLAIN

WITH RECURSIVE T1(ID,APPROVALGROUPNAME,FATHERID) AS (

SELECT T0.ID,T0.APPROVALGROUPNAME,T0.FATHERID FROM APPROVAL_GROUP T0 WHERE

    T0.FATHERID = -1 AND SHOWFLAG=1

UNION ALL

SELECT T2.ID,T2.APPROVALGROUPNAME,T2.FATHERID FROM APPROVAL_GROUP T2, T1

  WHERE T2.FATHERID = T1.ID AND SHOWFLAG=1

)

SELECT * FROM T1;

+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+

| id | select_type | table      | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                      |

+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+

|  1 | PRIMARY     | <derived2> | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  616 |   100.00 | NULL                                       |

|  2 | DERIVED     | T0         | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 2467 |     1.00 | Using where                                |

|  3 | UNION       | T1         | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   24 |   100.00 | Recursive                                  |

|  3 | UNION       | T2         | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 2467 |     1.00 | Using where; Using join buffer (hash join) |

+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+



-- 路径枚举表

EXPLAIN

SELECT nodeid,node FROM AG_PathEnum WHERE path_string LIKE '16060/%';

+----+-------------+-------------+------------+------+---------------+------+---------+------+------+----------+-------------+

| id | select_type | table       | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |

+----+-------------+-------------+------------+------+---------------+------+---------+------+------+----------+-------------+

|  1 | SIMPLE      | AG_PathEnum | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  683 |    11.11 | Using where |

+----+-------------+-------------+------------+------+---------------+------+---------+------+------+----------+-------------+



-- 闭包表

EXPLAIN

SELECT n3.ID,n3.APPROVALGROUPNAME FROM APPROVAL_GROUP n1

INNER JOIN AG_Closure n2 ON n1.ID = n2.ancestor

INNER JOIN APPROVAL_GROUP n3 ON n2.descendant = n3.ID

WHERE n1.FATHERID = -1 AND n2.distance != 0;

+----+-------------+-------+------------+--------+---------------+---------------+---------+--------------------------+------+----------+------------------------------------+

| id | select_type | table | partitions | type   | possible_keys | key           | key_len | ref                      | rows | filtered | Extra                              |

+----+-------------+-------+------------+--------+---------------+---------------+---------+--------------------------+------+----------+------------------------------------+

|  1 | SIMPLE      | n1    | NULL       | ALL    | PRIMARY       | NULL          | NULL    | NULL                     | 2467 |    10.00 | Using where                        |

|  1 | SIMPLE      | n2    | NULL       | ref    | uniq_anc_desc | uniq_anc_desc | 4       | OrgAndUser.n1.ID         |    5 |    90.00 | Using index condition; Using where |

|  1 | SIMPLE      | n3    | NULL       | eq_ref | PRIMARY       | PRIMARY       | 4       | OrgAndUser.n2.descendant |    1 |   100.00 | Using where                        |

+----+-------------+-------+------------+--------+---------------+---------------+---------+--------------------------+------+----------+------------------------------------+



-- 嵌套集

EXPLAIN

SELECT node.node,node.nodeid

FROM AG_Stack AS node,

        AG_Stack AS parent

WHERE node.lft > parent.lft AND node.rgt < parent.rgt

        AND parent.lft = 1

ORDER BY node.lft;

+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------+

| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                        |

+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------+

|  1 | SIMPLE      | parent | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  683 |    10.00 | Using where; Using temporary; Using filesort |

|  1 | SIMPLE      | node   | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  683 |    11.11 | Using where; Using join buffer (hash join)   |

+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------+

优化后

这里对几个模型分别加上检索字段的索引：

-- 邻接表

CREATE UNIQUE INDEX APPROVAL_GROUP_TEMP_ID_IDX USING BTREE ON APPROVAL_GROUP_TEMP (ID);

CREATE INDEX APPROVAL_GROUP_TEMP_FATHERID_IDX USING BTREE ON APPROVAL_GROUP_TEMP (FATHERID);

CREATE INDEX APPROVAL_GROUP_TEMP_SHOWFLAG_IDX USING BTREE ON APPROVAL_GROUP_TEMP (SHOWFLAG);



-- 路径枚举

CREATE UNIQUE INDEX AG_PathEnum_path_string_IDX USING BTREE ON AG_PathEnum (path_string);



-- 闭包表

CREATE INDEX AG_Closure_ancestor_IDX USING BTREE ON AG_Closure (ancestor);

CREATE INDEX AG_Closure_descendant_IDX USING BTREE ON AG_Closure (descendant);

CREATE INDEX AG_Closure_distance_IDX USING BTREE ON AG_Closure (distance);



-- 嵌套集

CREATE UNIQUE INDEX AG_Stack_lft_IDX USING BTREE ON AG_Stack (lft);

CREATE UNIQUE INDEX AG_Stack_rgt_IDX USING BTREE ON AG_Stack (rgt);

重新执行前面的全树查询，SHOW PROFILES
的结果是：

+----------+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| Query_ID | Duration   | Query                                                                                                                                                                                                                                                                                                        |

+----------+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

|       12 | 0.00990250 | WITH RECURSIVE T1(ID,APPROVALGROUPNAME,FATHERID) AS (

SELECT T0.ID,T0.APPROVALGROUPNAME,T0.FATHERID FROM APPROVAL_GROUP T0 WHERE

    T0.FATHERID = -1 AND SHOWFLAG=1

UNION ALL

SELECT T2.ID,T2.APPROVALGROUPNAME,T2.FATHERID FROM APPROVAL_GROUP T2, T1

  WHERE T2.FATHERID = T1.ID AND SHOWFLAG=1

)

SELECT  |

|       13 | 0.00184200 | SELECT nodeid,node FROM AG_PathEnum WHERE path_string LIKE '16060/%'     |

|       14 | 0.00384525 | SELECT n3.ID,n3.APPROVALGROUPNAME FROM APPROVAL_GROUP n1

INNER JOIN AG_Closure n2 ON n1.ID = n2.ancestor

INNER JOIN APPROVAL_GROUP n3 ON n2.descendant = n3.ID

WHERE n1.FATHERID = -1 AND n2.distance != 0                                                                                                   |

|       15 | 0.00235000 | SELECT node.node,node.nodeid

FROM AG_Stack AS node,

        AG_Stack AS parent

WHERE node.lft > parent.lft AND node.rgt < parent.rgt

        AND parent.lft = 1

ORDER BY node.lft                                                                                                                            |

+----------+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

看起来路径枚举与嵌套集差距不大，嵌套集反而比未优化前慢了一倍，这个应该与数据库的缓存及构建索引有关，再次运行同样的 SQL 就更快了。

我们再比较一下寻找父节点的性能：
-- 路径枚举

SELECT P2.* FROM AG_PathEnum AS P1, AG_PathEnum AS P2 

WHERE P1.nodeid = 18903 AND POSITION(P2.path_string IN P1.path_string)= 1 ORDER BY P2.nodeid;



-- 嵌套集

SELECT parent.node,parent.nodeid FROM

  AG_Stack AS node,

  AG_Stack AS parent

WHERE node.lft > parent.lft AND node.rgt < parent.rgt

  AND node.nodeid = 18903

ORDER BY parent.lft DESC;



SHOW PROFILES;

+----------+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| Query_ID | Duration   | Query                                                                                                                                                                                                                                                                                                        |

+----------+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

|       19 | 0.00192450 | SELECT P2.* FROM AG_PathEnum AS P1, AG_PathEnum AS P2 

WHERE P1.nodeid = 18903 AND POSITION(P2.path_string IN P1.path_string)= 1 ORDER BY P2.nodeid                                                                                                                                                           |

|       20 | 0.00111425 | SELECT parent.node,parent.nodeid FROM

AG_Stack AS node,

AG_Stack AS parent

WHERE node.lft > parent.lft AND node.rgt < parent.rgt

AND node.nodeid = 18903

ORDER BY parent.lft DESC                                                                                                                            |

+----------+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

差距也不大。

总结

树状或层次结构的数据在数据库里的设计，以上的四种模型为久经考验的常用模型。
在这里插入图片描述在这里插入图片描述

  • 是否归一化：邻接表和路径枚举表因为可能出现回环路径，所以不是归一化的模型，需要通过编程实现归一化；

  • 是否无限深度：路径枚举表的路径长度有限，对一些深度需求很高的数据模型就不合适；

  • 查询是否需要递归：邻接表只能通过递归来实现全树搜索，其它模型则不需要

  • 需要几个表：闭包表需要额外的关系表，即通过空间换时间

  • 增删改是否简单：邻接表只需要对一条记录操作即可，路径枚举表和闭包表都需要对关联的节点进行操作，嵌套集也需要重新计算修改节点的后续节点左右值

  • 性能：如上测试，邻接表性能因为递归的原因最差，路径枚举和嵌套集因为简单查询及优化后性能都很好，闭包表查询的表多、次数较多，故性能一般

附：
树状结构数据的数据库表设计及使用 - 1. 邻接表
树状结构数据的数据库表设计及使用 - 2. 路径枚举（Path Enumeration）模型
树状结构数据的数据库表设计及使用 - 3. 闭包表（Closure Table）模型
树状结构数据的数据库表设计及使用 - 4. 嵌套集（Nested Set）模型

转：

树状结构数据的数据库表设计及使用 - 5. 四种模型的性能比较与分析

菠萝y
关注
专栏目录
树形结构的数据库表Schema设计
愤怒的小狐狸----博客专栏
07-30 9万+
树形结构的数据库表Schema设计：一种基于左、右值编码前序遍历的树形结构数据库表Schema设计方案，消除了频繁的“递归”操作，提升了树形结构查询效率~
第四章-空间数据管理库-空间数据库管理模型优秀文档.ppt
11-14
1. 层次模型：采用树状结构，数据以父节点和子节点的形式组织，如IBM的IMS系统。 2. 网络模型：图数据结构，允许数据之间的多对多关系，如CODASYL模型。 3. 关系模型：最广泛使用的模型，基于二维表格，操作简单，...
树形结构数据表的设计（左右设计）
07-11
树形结构数据表的设计（左右设计）
数据库树状查询
最新发布
寒冰的暖
07-11 269
oracle 树状查询，sql server 树状查询
数据库设计 （一）树形结构
897371388
11-22 333
引言 曾经听说过一句经典俗语“思想决定思路，思路决定出路”，一句话道出了大智若愚的真理，从表面上我们可以知道一个人一定要由思想，也表明了思想为什么会是重要的思想是看不见摸不着的东西，如果大谈思想的东西文章可能就会变成专业软文，这是因为你的思想没有付诸行动、没有量化到细节，即没有做到实事求是。 说得有点远了不过同这篇博客的主旨并没有北辙，社会、工作、生活等各个方面都由不同层次结构的万物构成...
树状 _ 层次 结构数据的数据库表设计对比及使用
卷心菜投手
01-08 887
树状结构或层次结构的数据 数据库
Mysql树状模型
hh_xpnzdnh的博客
03-02 173
Mysql没有Oralce中的Connect By层次查询方法，下面介绍一种方法可以供大家参考 例如:要建立一个如下的树状模型，如果使用子父表left join的方式需要套好几层，使用左右节点方法就来的比较容易；   先建立表结构: /*创建百科分类表*//*==============================================================*/...
LDAP目录数据库详细使用手册及原理分析.doc
09-15
### LDAP目录数据库详细使用手册及原理分析 #### 一、LDAP简介 LDAP（Lightweight Directory Access Protocol，轻量级目录访问协议）是一种基于X.500标准的目录访问协议，但进行了大量的简化以适应互联网环境下的...
第3章-数据库系统基础知识-PPT课件.ppt
11-19
逻辑模型如层次模型以树状结构表示数据，每个节点代表一个实体，父子关系的一对多联系简化了数据表示，但对多对多联系处理不够灵活，可能导致数据冗余和不一致。 层次模型的优点在于结构简单，对于预定义联系的应用...
电信设备-一种公共信息模型与关系型数据库之间的映射方法.zip
09-19
在IT行业中，公共信息模型（Public Information Model，PIM）是一种用于统一通信和网络设备管理的数据模型，它提供了一种标准化的方式来描述和交换设备配置、性能数据和其他相关信息。关系型数据库（Relational ...
树形结构数据库设计.zip
06-17
树形结构数据库设计是一种常见的非关系型数据模型，它在数据结构学习中占据了重要地位，尤其对大学生来说，掌握这一概念对于深入理解计算机科学至关重要。在这个“树形结构数据库设计.zip”压缩包中，很可能是包含了...
树形结构数据库设计
05-23
树形结构数据库设计
树形结构的数据库表Schema设计1
08-08
一、基本数据    本文列举了一个食品族谱的例子进行讲解，通过类别、颜色和品种组织食品，树形结构图如下：二、继承关系驱动的Schema设计    对树形结构最直
树形结构数据库设计和显示
08-22
树形结构数据库设计，和java获取json到前台显示。
java树结构，树结构表设计
12-01
前些天面试，发现面试官很喜欢问树的构造问题，我当时回答树设计就是通过一pid来保存父结点的id值来实现，通过递归来生成一棵树，但是面试官说如果树比较大的话这样做的效率太低，网上找好像也都是这种方法，后来发现以前看用友的表结构设计里没有用这种方法，他们采用的是下面的表结构设计。
常见数据库设计（4）——树形结构数据
weixin_34221112的博客
08-29 397
1 概述树形数据，主要关注的是：1&gt; 如何将数据高效地以树形的形式展现给用户2&gt; 通过某个节点找到所有的父节点。3&gt; 获取某个节点的所有的后继节点（包括子节点的子节点）至于添加、修改、删除和通过一个父节点获取对应的子节点，都是可以很容易的实现。 2 邻接模型2.1业务：文件存放位置，在档案管理中，需要为文件的存放位置建模，文件存在抽屉，然后抽屉在某个柜子中，柜子在某个房间中。2...
数据库设计总结学习
CZBing308722240的专栏
05-13 650
一、树型关系的数据表 　　不少程序员在进行数据库设计的时候都遇到过树型关系的数据，例如常见的类别表，即一个大类，下面有若干个子类，某些子类又有子类这样的情况。当类别不确定，用户希望可以在任意类别下添加新的子类，或者删除某个类别和其下的所有子类，而且预计以后其数量会逐步增长，此时我们就会考虑用一个数据表来保存这些数据。按照教科书上的教导，第二类程序员大概会设计出类似这样的数据表结构： 类别表_1
树形结构的数据库表设计
热门推荐
凌晨1点21分的专栏
03-25 1万+
树形结构的数据库表设计 程序设计过程中，我们常常用树形结构来表征某些数据的关联关系，如企业上下级部门、栏目结构、商品分类等等，通常而言，这些树状结构需要借助于数据库完成持久化。然而目前的各种基于关系的数据库，都是以二维表的形式记录存储数据信息，因此是不能直接将Tree存入DBMS，设计合适的Schema及其对应的CRUD算法是实现关系型数据库中存储树形结构的关键。     理想中树形结构应该具
树状结构数据的数据库表设计及使用 - 3. 闭包表（Closure Table）模型
不吃_花椒的博客
06-22 6341
本文以 MySQL 为例。文档比较长，故分为5部分发出： 闭包表模型 闭包表（Closure Table）是一种通过空间换时间的模型，它是用一个专门的关系表（其实这也是我们推荐的归一化方式）来记录树上节点之间的层级关系以及距离。 为了防止插入数据出错，我们需要一个存储过程： 然后我们插入一些数据，这里以在论坛里发帖回帖为例： 前面的存储过程会在关系表里插入每条贴子与自身和它上级贴子的关系以及距离： 获取闭包表全树或子树 通过关联表的父子关系，去掉自指的记录，使用内连接获取所有子节点。 叶节点的特征是没有
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值