三种方式的树形结构数据库表设计的对比

65 阅读 0 评论 43 点赞

我是靠谱客的博主花痴橘子，最近开发中收集的这篇文章主要介绍三种方式的树形结构数据库表设计的对比，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

前言

最近项目中有用到无限层级的树结构场景，需求：树的层级无限（理论支持无限层级，实际业务场景最多6层级），查询效率高，树的节点能够实现任意拖拽

三种树结构表设计方案：

传统方式即邻接表模式
通过id,parentId来表达父子层级关系
优点：结构明了清晰，新增修改方便
缺点：查询需要层层递归，层级较深时查询效率较低，并且容易导致栈溢出
子编码=父编码+子扩展编码方式，也称物化路径
优点：结构明了，查询可以通过父编号前缀的规则like查询
缺点：层级深时编码字段会较长，导致无法给定合适的编码字段长度，过长的字段会导致索引创建效率低下
左右值树
通过左右值树形结构，每个节点都有左右两个值，所有子节点的左右值均在父节点的左右值范围内
优点：查询所有子节点时可以规避递归，提高查询效率。
缺点：表结构不好理解，并且修改和移动节点会导致大面积更新节点左右值的操作，并发过程中容易导致阻塞或死锁情况出现，数据一致性无法保证。

但是实际应用场景中树查询需求总大于新增和修改，所以可以考虑以“空间换时间”的策略，以牺牲一点新增和修改的开销来提高树查询的效率

三种方式树的建表语句如下：Oracle12c数据库

传统方式
– Create table
create table LC_TREE_01
(
id VARCHAR2(60) not null,
pid VARCHAR2(60),
node_name VARCHAR2(60),
node_level VARCHAR2(10),
node_des VARCHAR2(200),
node_name_en VARCHAR2(60)
);
– Add comments to the table
comment on table LC_TREE_01 is ‘树结构表-第一种方式’;
– Add comments to the columns
comment on column LC_TREE_01.id is ‘编号’;
comment on column LC_TREE_01.pid is ‘父编号’;
comment on column LC_TREE_01.node_name is ‘节点名称’;
comment on column LC_TREE_01.node_level is ‘节点层级’;
comment on column LC_TREE_01.node_des is ‘节点描述’;
comment on column LC_TREE_01.node_name_en is ‘节点英文简称’;
– Create/Recreate primary
alter table LC_TREE_01 add constraint PK_ID1 primary key (ID);
长编码方式，和传统方式对比表结构设计没有变化，只是父子节点编码具有一定规则
– Create table
create table LC_TREE_02
(
id VARCHAR2(60) not null,
pid VARCHAR2(60),
node_name VARCHAR2(60),
node_level VARCHAR2(10),
node_des VARCHAR2(200),
node_name_en VARCHAR2(60)
);
– Add comments to the table
comment on table LC_TREE_02 is ‘树结构表-第二种方式’;
– Add comments to the columns
comment on column LC_TREE_02.id is ‘编号’;
comment on column LC_TREE_02.pid is ‘父编号’;
comment on column LC_TREE_02.node_name is ‘节点名称’;
comment on column LC_TREE_02.node_level is ‘节点层级’;
comment on column LC_TREE_02.node_des is ‘节点描述’;
comment on column LC_TREE_02.node_name_en is ‘节点英文简称’;
– Create/Recreate primary
alter table LC_TREE_02 add constraint PK_ID2 primary key (ID);
左右值树，相比传统方式多了节点的左右值两个字段。
– Create table
create table LC_TREE_03
(
id VARCHAR2(60) not null,
pid VARCHAR2(60),
node_name VARCHAR2(60),
node_level VARCHAR2(10),
node_des VARCHAR2(200),
node_name_en VARCHAR2(60),
l_no VARCHAR2(60),
r_no VARCHAR2(60)
);
– Add comments to the table
comment on table LC_TREE_03 is ‘树结构表-第三种方式’;
– Add comments to the columns
comment on column LC_TREE_03.id is ‘编号’;
comment on column LC_TREE_03.pid is ‘父编号’;
comment on column LC_TREE_03.node_name is ‘节点名称’;
comment on column LC_TREE_03.node_level is ‘节点层级’;
comment on column LC_TREE_03.node_des is ‘节点描述’;
comment on column LC_TREE_03.node_name_en is ‘节点英文简称’;
comment on column LC_TREE_03.l_no is ‘节点左值’;
comment on column LC_TREE_03.r_no is ‘节点右值’;
– Create/Recreate indexes
create index INDEX_L_R on LC_TREE_03 (L_NO, R_NO)；
– Create/Recreate primary
alter table LC_TREE_03 add constraint PK_ID3 primary key (ID);