mysql无限层表设计方案分析

目录

需求接口

思路1：记录父ID

思路2：闭包表（空间换时间）

思路3：存储路径

三种方式对比

---

本文所用的层级关系图 

需求接口

1、获取子级/所有子级列表/所有子级树

2、获取父级

3、增删改

思路1：记录父ID

CREATE TABLE `XXX` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增ID',
  `parent_id` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT '上级ID',
  `is_leaf` tinyint(2) NOT NULL DEFAULT '0' COMMENT '叶子结点标记：0非叶子，1叶子',
  ...
} 

存储结果（根据文章开始的图中ID）

思路2：闭包表（空间换时间）

两个表，每个关系都存起来

create table XXX_base{
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增ID',
  `name` varchar() NOT NULL COMMENT '',
  ...
}

create table XXXrelation{
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增ID',
  `base_id` bigint(20) unsigned NOT NULL COMMEMT '基础信息表ID',
  `base_parent_id` bigint(20) unsigned NOT NULL COMMEMT '上级的基础信息ID',
  `diff_level` tinyint(2) NOT NULL DEFAULT '0' COMMENT '与父节点相差的层级',
  `is_leaf` tinyint(2) NOT NULL DEFAULT '0' COMMENT '叶子结点标记：0非叶子，1叶子',
  ...
}

存储结果（根据文章开始的图中ID）

思路3：存储路径

存储从第一层到自己的路径

CREATE TABLE `XXX` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增ID',
  `parent_id` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT '上级ID',
  `path` varchar(255) NOT NULL DEFAULT '' COMMENT '层级路径',
  `is_leaf` tinyint(2) NOT NULL DEFAULT '0' COMMENT '叶子结点标记：0非叶子，1叶子',
  ...
} 

存储结果（根据文章开始的图中ID）

三种方式对比

三种思路对比	记录父ID	闭包表	存储路径
获取子级	where parent_id = ? O(1)	where base_parent_id = ? and diff_level = 1 O(1)	where parent_id = ? O(1)
获取子级列表 获取子级树	递归向下查询，并且循环访问数据库 O(n)	where base_parent_id = ? O(1)	where path like 'pathPrefix-%' O(1)
获取父级	select parent_id where id = ？O(1)	select base_parent_id where base_id = ? and diff_level = 1 O(1)	select parent_id where id = ？O(1)
是否有层级关系	递归向下查询，并且循环访问数据库 最坏O(n)	where base_id = ? and base_parent_id = ? O(1)	fromPath.get(1).equals(toPath.get(1)) ？O(1)
索引	parent_id	base_id；base_parent_id	parent_id；path
查询性能分析比较	获取所有子级递归性能非常低，大量IO	关系表存储数据有重复性，如果一棵树一共有100个节点，那树根需要存储100条数据，影响查询效率	很清晰，查询最优
新增（可绑定父级、子级list）	1、id = insert into 2、入参父级is_leaf为1 3、select * where id in (入参子级list)：处理原父级is_leaf && update set parent_id = id O(1)	1、id = insert into 2、入参父级is_leaf为1 3、delete from where base_id in (入参子级list) select * where base_id = 入参父级，获取所有父级记录，按照入参父级新增记录 select * from base_parent_id in (入参子级list)，处理子级列表：删除、按照父级记录新增 O(1)	1、id = insert into 2、入参父级is_leaf为1 3、select * where id in (入参子级list)：处理原父级is_leaf && 处理path、parent_id 循环入参子级list：select * where path like '子节点pathPrefix-%' ：批量update path O(n)
修改（父级更新：原父级 -> 更新父级）	1、处理原父级is_leaf 2、update parent_id = 新父级 O(1)	1、处理原父级is_leaf 2、删除当前节点记录，按照新父级记录新增 3、select * from base_parent_id = 当前节点，获取所有子级，删除、按照当前节点的记录新增 O(1)	1、处理原父级is_leaf 2、select * where path like '当前节点pathPrefix%' ：处理path、parent_id O(1)
删除（只能删除叶子节点）	1、delete from where id = ？ 2、处理父级is_leaf：获取子级为空则为is_leaf = 0 O(1)	1、delete from where base_id = ？ 2、处理父级is_leaf：获取子级为空则为is_leaf = 0 O(1)	1、delete from where id = ？ 2、处理父级is_leaf：获取子级为空则为is_leaf = 0 O(1)
优缺点分析	优：复杂度低，维护性能高 缺：多层查询性能低	优：性能高 缺：复杂度高，存储空间大	优：查询性能最高，复杂度低 缺：新增性能低