什么是闭包表？

闭包表模型（Closure Table）大白话教程

在现代的项目中，很多情况下我们需要存储和管理树形结构的数据，比如：

类目树：一个电商网站中的商品分类系统。
组织架构：公司中的部门、团队及员工之间的层级关系。
评论系统：文章下的评论及回复的层级结构。

闭包表（Closure Table）是一种常用的数据库设计模式，它用来表示这种层次结构的数据，同时能高效地处理查询树的某些操作，如：获取某个节点的所有子节点，或者获取某个节点的完整层级路径。

为什么使用闭包表？

1. 问题背景

在一个常见的树形结构中，每个节点只存储它的父节点的 ID（邻接表模型），比如类目系统：

ID | 名称       | 父ID
----------------------
1  | 电子产品   | NULL  （顶级类目）
2  | 手机       | 1     （父类目是电子产品）
3  | 笔记本电脑 | 1     （父类目是电子产品）
4  | 安卓手机   | 2     （父类目是手机）

在这种结构下，虽然每个节点知道它的父节点是谁，但如果我们想查询某个节点的所有子孙节点（比如找出所有属于“手机”的子类目），我们就需要进行递归查询，性能不高。对于深层次的树结构来说，递归会变得越来越复杂，查询性能下降。

2. 闭包表的优点

闭包表通过存储每个节点和它所有祖先及后代的关系，可以在常数时间内查询到一个节点的所有子孙节点或祖先节点，无需递归，大大提升了查询性能。

闭包表的工作原理

闭包表通过创建一个额外的表，来存储每个节点与其所有祖先和后代之间的关系。这个表有三个重要的字段：

ancestor_id：代表某个节点的祖先（父类目或更高层级的类目）。
descendant_id：代表某个节点的后代（子类目或更低层级的类目）。
depth：表示祖先节点与后代节点之间的距离（层级深度）。

通过这种方式，我们不仅存储了父子关系，还存储了所有祖先与后代之间的关系。

举个例子

假设我们有这样一个类目树：

电子产品
├── 手机
│   └── 安卓手机
└── 笔记本电脑

每个类目在类目表中的数据如下：

ID | 名称       | 父ID
----------------------
1  | 电子产品   | NULL
2  | 手机       | 1
3  | 笔记本电脑 | 1
4  | 安卓手机   | 2

在闭包表中，我们将存储每个类目与它所有祖先和后代的关系：

ancestor_id | descendant_id | depth
-------------------------------------
1           | 1             | 0  （自己与自己）
1           | 2             | 1  （电子产品是手机的祖先）
1           | 3             | 1  （电子产品是笔记本电脑的祖先）
1           | 4             | 2  （电子产品是安卓手机的祖先）
2           | 2             | 0  （自己与自己）
2           | 4             | 1  （手机是安卓手机的祖先）
3           | 3             | 0  （自己与自己）
4           | 4             | 0  （自己与自己）

这里每条记录表示了某个节点与其祖先或后代的关系。例如：

1 -> 4 说明“电子产品”是“安卓手机”的祖先，且两者相隔两层（depth = 2）。
2 -> 4 说明“手机”是“安卓手机”的父节点，相隔一层（depth = 1）。

闭包表如何使用？

现在让我们看看闭包表可以解决哪些问题，以及如何使用它。

1. 查询某个类目的所有子类目（包括多级子类目）

假设我们想查询“电子产品”这个类目下的所有子类目，包括多级的子类目。

SQL 查询：

SELECT descendant_id 
FROM category_closure 
WHERE ancestor_id = 1 AND depth > 0;

解释：

ancestor_id = 1：表示我们要找的是“电子产品”的子类目。
depth > 0：因为我们不想把“电子产品”自己也查出来，深度大于 0 就排除了自己。

结果：

descendant_id
--------------
2   （手机）
3   （笔记本电脑）
4   （安卓手机）

2. 查询某个类目的完整层级路径

假设我们想知道“安卓手机”这个类目从顶级类目开始的完整路径。

SQL 查询：

SELECT ancestor_id 
FROM category_closure 
WHERE descendant_id = 4 
ORDER BY depth;

解释：

descendant_id = 4：表示我们要查询的是“安卓手机”的层级路径。
ORDER BY depth：按深度排序，保证从最顶级的类目开始显示。

结果：

ancestor_id
-------------
1  （电子产品）
2  （手机）
4  （安卓手机）

这条查询告诉我们，“安卓手机”这个类目属于“电子产品”这个大类，下面有“手机”这一层级。

3. 删除类目及其所有子类目

如果我们想删除某个类目及其所有子类目，闭包表也可以帮助我们快速找到所有相关的类目。

SQL 查询：

DELETE FROM category_closure 
WHERE descendant_id IN (
    SELECT descendant_id 
    FROM category_closure 
    WHERE ancestor_id = 2
);

解释：

ancestor_id = 2：表示我们想删除的是“手机”这个类目及其所有子类目。
通过 DELETE 语句，我们会删除“手机”及其所有子类目（包括“安卓手机”）在闭包表中的记录。

4. 插入新的类目

当我们插入一个新的类目时，闭包表需要同步更新。假设我们在“手机”类目下新增了一个“5G 手机”类目。

首先，我们会把新类目插入到 category 表中，然后更新 category_closure 表。

-- 插入新类目到 category 表
INSERT INTO category (name, parent_id) VALUES ('5G 手机', 2);

-- 获取新类目 ID
SELECT id FROM category WHERE name = '5G 手机';

-- 更新闭包表
WITH RECURSIVE ancestors AS (
    SELECT ancestor_id, depth FROM category_closure WHERE descendant_id = 2
)
INSERT INTO category_closure (ancestor_id, descendant_id, depth)
SELECT ancestor_id, :new_category_id, depth + 1
FROM ancestors;

-- 插入新类目自身的关系
INSERT INTO category_closure (ancestor_id, descendant_id, depth)
VALUES (:new_category_id, :new_category_id, 0);

解释：

首先，我们通过递归查询找到“手机”类目的所有祖先节点。
然后，我们把这些祖先节点与新类目的关系插入到闭包表中，同时插入新类目与自己的关系。

闭包表的优缺点

优点：

查询效率高：可以快速查询某个节点的所有祖先或后代，无需递归操作，尤其在层级结构较深时优势明显。
支持复杂的层级操作：比如完整路径查询、子树查询、兄弟节点查询等操作都非常高效。
易于扩展：无论树有多深，闭包表都可以轻松处理。

缺点：

维护成本高：每次插入、更新、删除操作都需要更新闭包表中涉及到的所有祖先和后代关系，维护起来比较复杂，性能开销较大。
数据冗余：闭包表存储了每个节点与其所有祖先和后代的关系，会导致数据量增大。

总结

闭包表是一种非常实用的树形结构存储方式，尤其在查询深层次的层级结构时，能大大提高效率。尽管维护成本较高，但在需要频繁查询而更新较少的场景下（比如类目树、权限系统等），闭包表是一种非常合适的选择。