背景
前些时候遇到一个文件夹搜索的需求：用户有时会忘记文件放在哪里，希望能在当前文件夹，模糊搜索里面嵌套所有的子文件。类似这种树形结构的搜索也是很常见的，比如以下场景：
- 一个设计师在公共项目文件夹中寻找某个特定的设计文件
- 一个程序员需要在代码仓库中快速查看一个特定的类
- 一个新人需要在庞杂的文档库中检索特定文档
这个需求特殊在于，用户开始搜索的位置，往往是文件夹根部附近，在数据量较大时，如何既保证查询效率，又不会影响其他已有功能（如移动文件夹）的效率。
假如目前已有表结构为：文件夹表folder通过parent_id形成嵌套结构，文件表file通过folder_id关联文件夹
CREATE TABLE `folder` (
`id` bigint NOT NULL AUTO_INCREMENT COMMENT 'id',
`folder_name` varchar(64) NOT NULL DEFAULT '' COMMENT '文件夹名称',
`parent_id` bigint NOT NULL DEFAULT '0' COMMENT '父文件夹id',
PRIMARY KEY (`id`),
KEY `idx_parent_id` (`parent_id`)
) ENGINE=InnoDB COMMENT='文件夹表';
CREATE TABLE `file` (
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '文件id',
`folder_id` bigint NOT NULL COMMENT '文件夹ID',
`file_name` varchar(64) NOT NULL DEFAULT '' COMMENT '文件名称',
PRIMARY KEY (`id`),
KEY `idx_folder_id` (`folder_id`),
) ENGINE=InnoDB COMMENT='文件表';
这种表结构设计能轻松满足文件（新增/移动/删除）和文件夹（新增/移动/删除）操作。可是如果要查询指定文件夹下所有嵌套文件却不太方便。
本文会详细介绍几种方案的具体实现，主要包括表结构设计和查询实现，此外，各个方案的移动文件夹实现差距较大，本文也会详细介绍。其余操作较为简单且各方案差距不大，因此略过。最后通过性能测试对比其性能，找出各种方案适合的场景。如有错误，欢迎指正。
解决方案
目前比较常见的有以下3种方案
一、递归CTE
如果在业务代码中递归调用sql显然是不合适的，如果层数过多，会导致不必要的开销。那么如果由DB来进行递归是否可行呢？mysql8 中提供了递归 CTE（Common Table Expression），借助它便能在不改变表结构的情况下仅靠1条sql实现功能。parent_id加上索引提高效率，仅一次数据库连接
WITH RECURSIVE cte AS (
-- 初始查询，找到当前文件夹下子文件夹
SELECT id, parent_id FROM folder WHERE parent_id = #{folderId}
UNION all
-- 递归查询，往下找出所有子文件夹id
SELECT f.id, f.parent_id FROM folder f JOIN cte ON f.parent_id = cte.id
)
-- 得到递归cte结果时 查询符合条件的记录
SELECT f.id, f.file_name FROM cte join file f on cte.id = f.folder_id
WHERE f.file_name LIKE CONCAT('%', #{fileName}, '%');
其余操作不变，如移动文件夹仍只需将folder_id修改即可，不需要额外sql。
注意：如果表中有脏数据，比如a/b/c/a这种死循环文件夹，递归CTE会进入死循环，而由于递归默认上限为1000次，因此sql会报错Recursive query aborted after 1001 iterations.
二、闭包表
第二个方案，则是用一张额外表，存储所有祖先节点和后代节点的关系映射。
CREATE TABLE `folder_closure` (
`parent_id` bigint NOT NULL COMMENT '祖先文件夹ID',
`child_id` bigint NOT NULL COMMENT '后代文件夹ID',
PRIMARY KEY (`parent_id`,`child_id`),
KEY `idx_child_id` (`child_id`)
) ENGINE=InnoDB COMMENT='文件夹闭包表';
举个例子，实际文件夹架构和存储值如下（保存自己到自己的映射是为了查询逻辑的统一，不用额外特殊处理自身）
1(根)
├── 2(一级)
│ ├── 3(二级)
│ │ └── 4(三级)
└── 5(一级)
parent_id |child_id |
-----------+----------+
1| 1|
1| 2|
1| 3|
1| 4|
1| 5|
2| 2|
2| 3|
2| 4|
3| 3|
3| 4|
4| 4|
5| 5|
查询
闭包表中已经有文件夹下所有后代的文件夹id，所以查询文件夹下文件名称的sql如下：
select f.id, f.file_name from folder_closure fc
join file f on fc.child_id = f.folder_id
WHERE fc.parent_id = #{folderId}
and f.file_name LIKE CONCAT('%', #{fileName}, '%');
移动文件夹
既然有文件夹结构，自然就有移动文件夹功能。然而闭包表移动文件夹却相当麻烦，除了修改folder_id，还需要额外操作：第一步要删除原祖孙关系，但其中要保留自身的关系映射，思路是查出原文件夹下所有子孙，再join查出这些子孙所有的祖先，进行删除。第二步是插入新的祖孙关系，通过笛卡尔积，将新父节点的所有祖先和移动节点的所有后代进行组合。在大数据量时，闭包表数据量极大，大量删除和插入操作的性能不佳
-- 删除原祖孙关系（保留自身映射）
delete fc from folder_closure fc
join folder_closure temp on fc.child_id = temp.child_id
where temp.parent_id = #{sourceId}
and fc.parent_id != fc.child_id
-- 插入新祖孙关系
insert into folder_closure (parent_id, child_id)
select a.parent_id, b.child_id
from folder_closure a
cross join folder_closure b
where a.child_id = #{targetId}
and b.parent_id = #{sourceId}
三、路径枚举
递归CTE方案不需要额外存储空间，闭包表需要大量存储空间，而路径枚举则是折中的方案，不需要额外一张表，只需要在文件夹表folder中增加path字段，表示文件夹路径
alter table folder add column `path` varchar(500) NOT NULL DEFAULT '' COMMENT '文件夹id路径，如/1/2/3';
id |folder_name |path |parent_id|
------+-------------+------------+---------+
211|现代单位 |/2/21/211/ | 21|
212|现代部门 |/2/21/212/ | 21|
213|智能专区 |/2/21/213/ | 21|
214|自然项目 |/2/21/214/ | 21|
215|经典模块 |/2/21/215/ | 21|
216|简约专区 |/2/21/216/ | 21|
217|绿色项目 |/2/21/217/ | 21|
218|独特项心 |/2/21/218/ | 21|
219|科技项目 |/2/21/219/ | 21|
220|智能平台 |/2/21/220/ | 21|
221|精美产品 |/2/22/221/ | 22|
222|时尚框架 |/2/22/222/ | 22|
223|独特框架 |/2/22/223/ | 22|
224|经典方案 |/2/22/224/ | 22|
225|精致产品 |/2/22/225/ | 22|
226|精致框架 |/2/22/226/ | 22|
227|独特计划 |/2/22/227/ | 22|
228|古老项目 |/2/22/228/ | 22|
查询
查询比较简单，注意path和folder_id需要加索引
select f.* from folder fd
join file f on fd.id = f.folder_id
where fd.path like CONCAT(#{path}, '%')
and f.file_name LIKE CONCAT('%', #{fileName}, '%');
移动文件夹
使用冗余存储的代价就是不方便修改路径。如果要实现移动文件夹功能，需要修改所有原文件夹下属子文件夹的记录。移动的文件夹越接近根部，代价越大，如果移动的是根文件夹，影响到的数据量将极大，性能堪忧。sql如下：
update folder set path = REPLACE(path, #{sourcePath}, concat(#{targetPath}, #{sourceId}, '/'))
where path like concat(#{sourcePath}, '/')
性能测试
在数据量很少的情况下，毫无疑问应该选择递归CTE，性能不差且开发和维护成本极低。但如果数据量较大，嵌套层数较多，递归CTE还能满足性能需求吗，大概多大的数据量会出现性能问题？为了搞懂这个问题，我们可以模拟用户的文件夹目录结构，造一些测试数据，分别测试3种方案的性能。
测试环境
- 数据库版本：MySQL 8.0.37
- 操作系统：Windows11
- 硬件配置：
- CPU：14650HX
- 内存：32GB
- 磁盘：SSD
测试方案
用户创建文件夹和文件时，基本都集中在前几层。所以首先在根目录下设定10个根文件夹。其中每个文件夹内包含10个子文件夹和10个文件，后面依次类推，3层后减少至5个子文件夹和5个文件，随后减至1子文件夹和1文件。随后为了验证递归的性能，可以故意将嵌套继续进行下去，层数大约35层后，数据量就达到了：文件夹表70w，文件表70w，闭包表1400w。
测试结果
在根文件夹进行搜索和移动，得到结果如下：
| 递归CTE（耗时） | 闭包表（耗时） | 路径枚举（耗时） | |
|---|---|---|---|
| 搜索根文件夹 | 1s | 800ms | 1s |
| 移动根文件夹（额外耗时） | 无 | timeout | 12s |
移动文件夹性能差距较为明显，且符合认知。但查询性能看上去差不多，生成测试数据的规则是否会影响查询性能呢。为了更加严谨，多次生成不同权重分布、不同层数、更大数据量、不同文件数量/比例的数据用于测试。取其中一次实验数据举例，生成完测试数据后，表中数据条数为：文件夹表170w，文件表219w，闭包表2700w。在这种方案下，进行查询的耗时如下
| 层级 | 递归CTE（耗时/ms） | 闭包表（耗时/ms） | 路径枚举（耗时/ms） |
|---|---|---|---|
| 0（根文件夹） | 6428 | 5770 | 7778 |
| 1 | 5363 | 4921 | 6417 |
| 2 | 1808 | 1563 | 1703 |
| 3 | 782 | 688 | 750 |
| 4 | 342 | 301 | 331 |
| 5 | 132 | 109 | 150 |
| 6 | 50 | 39 | 50 |
| 7 | 13 | 13 | 15 |
| 8 | 8 | 8 | 12 |
| 9 | 4 | 6 | 4 |
| 10 | 2 | 4 | 2 |
| 11 | 1 | 3 | 1 |
| ... | <1 | 2 | <1 |
可以看到，不同数据量，不同文件结构确实会影响测试结果，但无论怎么调整，3种方案的查询性能差距都不大。基本都能保持在同一数量级。而且，递归CTE的性能瓶颈并不在递归次数上，即使故意创建了很深的层数，相比于其他方案也依旧不会有明显的性能波动。
根据测试数据，我们可以得出3种方案的优缺点
- 闭包表
- 优点：查询性能最好
- 缺点：虽然查询性能最好，但代价是需要大量额外存储空间，数据量大时无法进行移动操作。上线初始化和维护成本极高。可读性差
- 路径枚举
- 优点：额外存储开销不大。直观易理解
- 缺点：查询性能一般，路径长度有限制。移动操作较慢。上线初始化和维护成本高
- 递归CTE
- 优点：实现最简单，只需一条sql。移动操作无额外耗时。无需额外冗余存储。方便增删改查
- 缺点：mysql需达到8.0及以上。查询性能一般
总结
| 方案 | 查询性能 | 存储开销 | 移动成本 | 初始化成本 | 实现复杂度 |
|---|---|---|---|---|---|
| 递归CTE | 中 | - | - | - | 低 |
| 闭包表 | 高 | 高 | 高 | 高 | 中 |
| 路径枚举 | 中 | 中 | 中 | 中 | 中 |
路径枚举和闭包表维护成本高，尤其在较大数据量时进行移动文件夹/上线初始化都算得上是灾难。如果项目当前或未来有移动文件夹功能，或已经有了较大的数据量，尽量不要使用。
而递归CTE代码实现简单且后续维护方便，几乎不会出现bug，查询性能也不差。在大部分情况下，应优先考虑使用递归CTE方案
