数据库设计

一、树形字典的应用场景

树形字典在业务系统中应用广泛，典型场景包括：

• 地区/行政区划字典（省-市-区-县四级联动）
• 组织架构/部门层级管理（总公司-分公司-部门-小组）
• 商品分类/类目管理（一级分类-二级分类-三级分类）
• 权限菜单/系统菜单的层级结构
• 评论回复/帖子楼中楼的嵌套关系

---

二、常见树形字典设计方案

1. 邻接表（Adjacency List）

核心结构

通过每个节点记录直接父节点的方式表示层级关系，核心字段如下：

CREATE TABLE tree_dict (
    id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '主键ID',
    name VARCHAR(64) NOT NULL COMMENT '节点名称',
    parent_id BIGINT NOT NULL DEFAULT 0 COMMENT '父节点ID，根节点为0',
    sort INT DEFAULT 0 COMMENT '排序号',
    is_deleted TINYINT DEFAULT 0 COMMENT '逻辑删除标记'
);

特点与适用场景

• 优点：结构简单、新增/修改节点方便，单条记录仅需维护 parent_id
• 缺点：查询子树/父链需递归查询，大数据量下性能较差
• 适用场景：层级不深（≤5级）、增删改频繁、查询不频繁的场景

常见操作示例

• 查询直接子节点：SELECT * FROM tree_dict WHERE parent_id = #{parentId}
• 查询所有父节点（递归）：需通过CTE或程序递归实现

---

2. 闭包表（Closure Table）

核心结构

通过单独的关系表记录所有祖先-后代关系，核心字段如下：

-- 主表
CREATE TABLE tree_dict (
    id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '主键ID',
    name VARCHAR(64) NOT NULL COMMENT '节点名称',
    sort INT DEFAULT 0 COMMENT '排序号',
    is_deleted TINYINT DEFAULT 0 COMMENT '逻辑删除标记'
);

-- 关系表（闭包表）
CREATE TABLE tree_dict_closure (
    ancestor_id BIGINT NOT NULL COMMENT '祖先节点ID',
    descendant_id BIGINT NOT NULL COMMENT '后代节点ID',
    depth INT NOT NULL COMMENT '层级深度（根节点到自身为0）',
    PRIMARY KEY (ancestor_id, descendant_id)
);

特点与适用场景

• 优点：查询子树/父链性能极高，仅需一次关联查询即可获取所有关系
• 缺点：新增/删除节点需维护关系表，插入操作复杂度高，关系表数据量较大
• 适用场景：层级深、查询频繁、增删改不频繁的场景（如地区字典）

常见操作示例

• 查询某节点的所有后代：

SELECT d.* FROM tree_dict d
JOIN tree_dict_closure c ON d.id = c.descendant_id
WHERE c.ancestor_id = #{nodeId};

• 查询某节点的所有祖先：

SELECT d.* FROM tree_dict d
JOIN tree_dict_closure c ON d.id = c.ancestor_id
WHERE c.descendant_id = #{nodeId}
ORDER BY c.depth DESC;

---

3. 路径枚举（Path Enumeration）

核心结构

通过节点的完整路径字符串表示层级关系，核心字段如下：

CREATE TABLE tree_dict (
    id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '主键ID',
    name VARCHAR(64) NOT NULL COMMENT '节点名称',
    path VARCHAR(255) NOT NULL COMMENT '节点完整路径，如/1/3/5',
    sort INT DEFAULT 0 COMMENT '排序号',
    is_deleted TINYINT DEFAULT 0 COMMENT '逻辑删除标记'
);

特点与适用场景

• 优点：查询子树/父链仅需字符串匹配，实现简单
• 缺点：路径长度受字段限制，修改父节点需更新所有后代的路径，扩展性差
• 适用场景：层级固定、极少修改的场景（如系统常量字典）

常见操作示例

• 查询某节点的所有后代：SELECT * FROM tree_dict WHERE path LIKE CONCAT(#{nodePath}, '/%')
• 查询某节点的所有祖先：SELECT * FROM tree_dict WHERE #{nodePath} LIKE CONCAT(path, '/%')

---

4. 嵌套集（Nested Set）

核心结构

通过左值（lft）和右值（rgt）标记节点的嵌套范围，核心字段如下：

CREATE TABLE tree_dict (
    id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '主键ID',
    name VARCHAR(64) NOT NULL COMMENT '节点名称',
    lft INT NOT NULL COMMENT '左值',
    rgt INT NOT NULL COMMENT '右值',
    sort INT DEFAULT 0 COMMENT '排序号',
    is_deleted TINYINT DEFAULT 0 COMMENT '逻辑删除标记'
);

特点与适用场景

• 优点：查询子树性能极高，可直接通过区间查询获取所有后代
• 缺点：新增/删除节点时需更新大量节点的左右值，维护复杂
• 适用场景：层级固定、查询极频繁、增删改极少的场景（如商品分类）

常见操作示例

• 查询某节点的所有后代：

SELECT * FROM tree_dict WHERE lft BETWEEN #{nodeLft} AND #{nodeRgt};

---

三、四种方案对比

方案	查询子树	查询父链	新增节点	删除节点	修改父节点	适用场景
邻接表	差（需递归）	差（需递归）	优	优	优	层级浅、增删改频繁
闭包表	优（关联查询）	优（关联查询）	差（需维护关系表）	差（需维护关系表）	差（需维护关系表）	层级深、查询频繁
路径枚举	良（字符串匹配）	良（字符串匹配）	良	良	差（需更新所有后代路径）	层级固定、极少修改
嵌套集	优（区间查询）	良（需计算层级）	差（需更新左右值）	差（需更新左右值）	差（需更新左右值）	层级固定、查询极频繁

---

四、树形字典设计的最佳实践

1. 优先选择邻接表：绝大多数业务场景下，邻接表的简单性和可维护性更优，配合程序端缓存（如Redis）可解决性能问题
2. 使用闭包表优化高频查询：地区字典、商品分类等查询频繁的场景，可通过闭包表提升查询性能
3. 路径枚举配合索引：路径字段添加前缀索引，提升字符串匹配查询的性能
4. 嵌套集谨慎使用：仅在层级固定且无修改需求的场景使用，避免维护成本过高
5. 添加冗余字段：在邻接表中增加 level（层级）、path（路径）冗余字段，可减少递归查询的频率

---

五、面试相关问题

1. 问：数据库树形字典有哪些常见设计方案？各有什么优缺点？答： 常见方案包括邻接表、闭包表、路径枚举、嵌套集。

   • 邻接表：结构简单，增删改方便，但查询性能差，适合层级浅、增删改频繁的场景；
   • 闭包表：查询性能极高，但维护关系表成本高，适合层级深、查询频繁的场景；
   • 路径枚举：实现简单，但路径长度受限、修改父节点成本高，适合层级固定、极少修改的场景；
   • 嵌套集：查询性能极高，但增删改需更新大量节点，适合层级固定、查询极频繁的场景。

2. 问：邻接表查询子树性能差，有什么优化方案？答： 可通过以下方式优化：

   • 数据库层面：使用CTE递归查询（MySQL 8.0+支持），或在程序端递归查询并缓存结果；
   • 冗余字段：添加 path 字段，通过字符串匹配快速获取子树；
   • 缓存优化：将树形结构预加载到Redis中，前端直接从缓存获取数据；
   • 业务裁剪：限制查询层级，仅返回前端所需的层级数据。

3. 问：闭包表的关系表数据量如何计算？答： 闭包表的关系表数据量与树的节点数和层级深度有关，最坏情况下每个节点的祖先数等于节点数，总数据量为 O(n²)。实际场景中，若树的平均层级为k，总数据量约为 n*k，需提前评估数据量大小，避免关系表过大影响性能。

---