用于解决树形结构存储的闭包表，凭什么能快速获取某个节点的祖先节点/父节点/子节点？ - 讨论/求助

前言

看到网上很多文章吐槽，邻接表模型用递归获取祖先节点/后代节点，导致性能很差。

而用闭包表就能空间换时间，很快获取。书本也是这么夸闭包表的。

可是我看着闭包表的结构，没搞懂它是如何走索引，来快速获取祖先节点/父节点/子节点的？

image-20220707092706430.jpg（102.61 KB）

闭包表结构

引入一个实际例子说吧。假设闭包表结构为：（简化，后续用 节点指代的名称 代替 节点ID）

CREATE TABLE 闭包表 (
    祖先节点ID INT,
    后代节点ID INT,
    这俩节点距离 INT,
    PRIMARY KEY (祖先节点ID, 后代节点ID)
);

现有一张约 66W 行数据的 5 级地区表，各级数量为：(参考自中华人民共和国行政区划（五级）)

省	市	区	街	村
31	342	2990	41356	618134

那么，建立的闭包表的行数量为：

1*1 (根, 根) + 31*2 (根, 省), (省, 省) + 342*3 + 2990*4 + 41356*5 + 618134*6 = 3928633 行

1. 如何快速获取 31 个省份？

网上很多的 SQL 是这样的：

SELECT 后代节点 FROM 闭包表 WHERE 祖先节点 = '根节点' AND /* 缺失：后代节点 = ? AND */ 距离 = 1

可是，根据最左匹配原则，距离 = 1 是无法利用的，所以上述 SQL 要扫描 66W 行（每个地区都有到根节点的记录），才能获得结果？？

2. 如何获取“杭州”所属省份？

网上的 SQL：

SELECT 祖先节点 FROM 闭包表 WHERE /* 缺失：祖先节点 = ? AND */ 后代节点 = '杭州' AND 距离 = 1

根据最左匹配原则，利用不了索引，所以上述 SQL 要扫描 390W 行，才能获得“杭州”的父节点？？

3. 如何获取“哈尔滨市zf亚布力滑雪度假区管理委员会虚拟社区”的省市区街村全称？（挑了个名字最长的。。）

网上的 SQL：

SELECT 祖先节点 FROM 闭包表 WHERE /* 缺失：祖先节点 = ? AND */ 后代节点 = '...' ORDER BY 距离 DESC

同理，这也是要扫描 390W 行，才能得到结果？？

先 @ 万能的 @老虎会游泳

@无名啊，在SQL语句前加explain可以看到它是如何执行的。

@无名啊，我会这样创建索引：

CREATE TABLE 闭包表 (
    祖先节点ID INT,
    后代节点ID INT,
    这俩节点距离 INT,
    PRIMARY KEY (祖先节点ID, 后代节点ID),
    KEY (这俩节点距离, 祖先节点),
    KEY (这俩节点距离, 后代节点)
);

@老虎会游泳，加过 explain 了，就是我说的这些情况

这个索引，基本就变成 1170W 的表了。。

@老虎会游泳，噢，我想起你之前提到过的：

老虎会游泳：如果索引只有两个键，就是遍历两个键，对其值的数量进行求和。

无名啊：你是说，一百行的表，对其中一个值域大小为2的字段建索引，该索引只有两条记录？每条记录指向50个主键ID？（假设均匀分布）

老虎会游泳：对呀，难道不是吗，B+树的一个节点不是能存很多指针吗？除非一个节点存不下，才会需要存储在相邻节点吧。

我后面找到的答案是：不是

下面这张图出自这个博客。可以看到：

表格前 7 行是 Record Header，无论是聚集/非聚集的叶子/非叶子页上的每个记录，都会有这个 5+ 字节的记录头，先不管。
表格后 2 行，表明二级索引的叶子页上，只会存着索引键和主键，没有你说的【合并相同的索引键为一行记录】的结构

@无名啊，

这个索引，基本就变成 1170W 的表了。。

但查询变成仅扫描索引了啊。

@老虎会游泳，这闭包表本身，和你的 KEY (这俩节点距离, 祖先节点) 或 KEY (这俩节点距离, 后代节点) 也差不多大啊，扫索引所有行也快不到哪儿去吧。。

而且，对于问题三（获取某节点所有祖先），这 1170W 的表还是要扫 390W 行：

SELECT 祖先节点 FROM 闭包表 WHERE /* 缺失：祖先节点 = ? AND */ 后代节点 = '...' ORDER BY 距离 DESC

@老虎会游泳，我是读到一篇博文《在数据库中存储一棵树，实现无限级分类》时知道闭包表的。

（这篇博文还流传很广泛，我在必应上搜 “ClosureTable”，大半文章也参考/引用这篇博文内容/图片）

但看了文章下来后，没看懂为啥高效。。问了博主后，博主将结构改成了：

CREATE TABLE 闭包表 (
+   后代节点ID INT,
    祖先节点ID INT,
-   后代节点ID INT,
    这俩节点距离 INT,
-   PRIMARY KEY (祖先节点ID, 后代节点ID),
+   PRIMARY KEY (后代节点ID, 祖先节点ID)
);

我拿他文中的数据和修改前后的表结构去测试。结论是：

无论是原来的闭包表结构，还是修改后的，在查询祖先/父/子/后代节点时，都有不同程度的大范围扫表现象。

下面的 SQL，能自动根据该博主的数据表，建立俩修改前后的闭包表，并进行各种测试，以观察扫了多少行闭包表

-- -------- 原来的闭包表结构 --------

CREATE TABLE IF NOT EXISTS 原来的闭包表 (
  祖先 VARCHAR(16),
  后代 VARCHAR(16),
  距离 INT,
  PRIMARY KEY (祖先, 后代, 距离))
SELECT REPLACE(b.`name`, 'root', '根') 祖先,
       REPLACE(c.`name`, 'root', '根') 后代,
       a.distance 距离
  FROM category_tree a
  JOIN category b ON b.id = a.ancestor
  JOIN category c ON c.id = a.descendant;

-- -------- 修改后的闭包表结构 --------

CREATE TABLE IF NOT EXISTS 修改后闭包表 (
  后代 VARCHAR(16),
  祖先 VARCHAR(16),
  距离 INT,
  PRIMARY KEY (后代, 祖先, 距离))
SELECT REPLACE(b.`name`, 'root', '根') 后代,
       REPLACE(c.`name`, 'root', '根') 祖先,
       a.distance 距离
  FROM category_tree a
  JOIN category b ON b.id = a.descendant
  JOIN category c ON c.id = a.ancestor;

-- 1. 查找子节点
EXPLAIN SELECT 后代 FROM 原来的闭包表 WHERE 祖先 = '根' AND 距离 = 1; -- rows: 14 = COUNT(category)
EXPLAIN SELECT 后代 FROM 修改后闭包表 WHERE 祖先 = '根' AND 距离 = 1; -- rows: 54 = COUNT(category_tree)

-- 2. 查找父节点
EXPLAIN SELECT 祖先 FROM 原来的闭包表 WHERE 后代 = '电脑配件' AND 距离 = 1; -- rows: 54
EXPLAIN SELECT 祖先 FROM 修改后闭包表 WHERE 后代 = 'RTX3080' AND 距离 = 1; -- rows: 6 = 所在层数
-- 上面这行复杂度是 O(N)。若是 1000 层的节点获取父节点，需要扫 1000 行表。

-- 3. 查找祖先节点
EXPLAIN SELECT 祖先 FROM 原来的闭包表 WHERE 后代 = '电脑配件' ORDER BY 距离 DESC; -- rows: 54
EXPLAIN SELECT 祖先 FROM 修改后闭包表 WHERE 后代 = 'RTX3080' ORDER BY 距离 DESC; -- rows: 6

-- 4. 查找后代节点（下面查的，实际是最后一层，没后代的）
EXPLAIN SELECT 后代 FROM 原来的闭包表 WHERE 祖先 = 'RTX3080'; -- rows: 1
EXPLAIN SELECT 后代 FROM 修改后闭包表 WHERE 祖先 = 'RTX3080'; -- rows: 54

@老虎会游泳，那个博主给出新的解决方案了，速度上确实应该会很快，但空间占用也很恐怖。。

更改表结构和建立索引

那个博主新的表结构为：

CREATE TABLE 闭包表 (
    祖先 INT,
    后代 INT,
    距离 INT,
    PRIMARY KEY (后代, 距离),
    KEY (祖先, 距离)
);

使得有如下两种索引：

聚集索引：(后代, 距离, 祖先)
二级索引：(祖先, 距离, 后代)

就能高效应对下列查询了：

(孙)子/后代节点：走二级索引 (✔查询节点, ✔1 或 2 或任意, ❓<要获取的后代节点>)
(祖)父/祖先节点：走聚集索引 (✔查询节点, ✔1 或 2 或任意, ❓<要获取的祖先节点>)

下列查询会小范围扫表，但问题不大：

某后代节点与某祖先节点的距离：走聚集索引 (✔查询后代节点, ❓<要获取的距离>, ❌查询祖先节点)

只要 查询后代节点 层级没有深到离谱，扫表范围也就连续的几行几十行而已。

占的空间太大了，怀疑性价比

一个 66W 的 5 级地区表，就要配套一个相当于 780W 的闭包表，快接近 12 倍于主表的辅助表了。。

如此恐怖的空间换时间方案，到底能快多少呢？真的值得投入这么多空间来提速吗？

@无名啊，我没有回答这些问题所需的知识。如果我真的拿到这样的数据，我只能选择进行实际测试。

@老虎会游泳，老虎这么多年，没有啥树形数据要存吗？之前都用的啥方案？

@无名啊，只有这个
https://hu60.cn/q.php/bbs.newtopic.0.html

数据量太小，所以不需要辅助索引，直接select * from 表然后在内存里进行操作就行了

@老虎会游泳，我做了下『闭包表』和『改良后的邻接表』的测试 （结尾附上一键建表和查询的 SQL 供测试）

数据源：2022 年中国全国 5 级行政区划
数据库：MySQL 8.0.29 和 SQLite 3.39.0
表结构：『闭包表』和『 (<pid, id>, is_leaf) 型邻接表』
测试项：『查询根节点所有后代』和『查询根节点第 5 层后代』

结果如下 （多次测试稳定后）：

『查询根节点所有后代』速度对比

表结构	`MySQL`	`SQLite`
闭包表	1.3 秒	0.13 秒
递归邻接表	1.2 秒	0.60 秒
理想中递归损耗很小的邻接表	0.6 秒	0.12 秒

『查询根节点第 5 层后代』速度对比

表结构	`MySQL`	`SQLite`
闭包表	1.2 秒	0.12 秒
递归邻接表	0.5 秒	0.13 秒
理想中递归损耗很小的邻接表	0.4 秒	0.10 秒

目前观点

4W 多次的 ref 级 WHERE pid = ?，还是能和 66W 次 eq_ref 级的 WHERE id = ? 过过招，甚至更快的。而且，磁盘IO越慢，这个差异应该越大。
数据库们的 WITH RECURSIVE 查询，损耗有点大。
- MySQL 好歹每次递归都将上一次所有结果当作一张表来计算。但大概 5 次递归的耗时，就比非递归的多一倍了
- SQLite 最摆烂，每次递归只取以前结果的一行来计算，直到取完为止。所以有 66W 次的递归，耗时大概 5 倍多。。
  
  Extract a single row from the queue.
  
  Pretend that the single row just extracted is the only row in the recursive table and run the recursive-select, adding all results to the queue.

『查询根节点所有后代』通用 `SQL`

下面 SQL 基本可用于 MySQL 和 SQLite （不支持的特性，数据库会报错，改掉即可）

PRAGMA cache_size = -204800; -- 允许 SQLite 缓存 200 MB

-- 闭包表查询
SELECT COUNT(*), SUM(code), SUM(CHAR_LENGTH(name)) -- SQLite 写法：SUM(LENGTH(name))
  FROM closure_tree
 FORCE INDEX (idx_closure_tree) -- 我这测试，MySQL 不加这行，耗时翻好几倍。SQLite 需去掉此行
  JOIN closure ON id = descendant
 WHERE ancestor = 0;

-- 递归邻接表查询
WITH RECURSIVE
  find(id, code, name, is_leaf) AS (
    SELECT id, code, name, is_leaf
      FROM adjacent
     WHERE pid = 0
     UNION ALL
    SELECT b.id, b.code, b.name, b.is_leaf
      FROM find a
      JOIN adjacent b ON NOT a.is_leaf AND b.pid = a.id
  )
SELECT COUNT(*), SUM(code), SUM(CHAR_LENGTH(name)) -- SQLite 写法：SUM(LENGTH(name))
  FROM find;

-- 理想中，没有递归损耗的邻接表查询
SELECT COUNT(*), SUM(b.code), SUM(CHAR_LENGTH(b.name)) -- SQLite 写法：SUM(LENGTH(b.name))
  FROM adjacent a
  LEFT JOIN adjacent b ON b.pid = a.id -- SQLite 需要 LEFT JOIN，否则耗时翻几倍
 WHERE NOT a.is_leaf;

『查询根节点第 5 层后代』通用 `SQL`

PRAGMA cache_size = -204800; -- 允许 SQLite 缓存 200 MB

-- 闭包表查询
SELECT COUNT(*), SUM(code), SUM(CHAR_LENGTH(name)) -- SQLite 写法：SUM(LENGTH(name))
  FROM closure_tree
 FORCE INDEX (idx_closure_tree) -- 我这测试，MySQL 不加这行，耗时翻好几倍。SQLite 需去掉此行
  JOIN closure ON id = descendant
 WHERE ancestor = 0
   AND distance = 5;

-- 递归邻接表查询
WITH RECURSIVE
  var(depth) AS (
    SELECT 5
  ),

  -- 递归部分查前 N - 1 层
  find(id, is_leaf, depth) AS (
    SELECT 0, FALSE, var.depth - 1
      FROM var
     UNION ALL
    SELECT b.id, b.is_leaf, a.depth - 1
      FROM find a
      JOIN adjacent b ON b.pid = a.id
     WHERE a.depth > 0
       AND NOT a.is_leaf
  )

-- 最后一次性 JOIN 第 N 层
SELECT COUNT(*), SUM(b.code), SUM(CHAR_LENGTH(b.name)) -- SQLite 写法：SUM(LENGTH(b.name))
  FROM find a
 CROSS JOIN adjacent b ON a.id = b.pid -- SQLite 要加 CROSS，否则耗时翻几倍
 WHERE a.depth = 0;

-- 理想中，没有递归损耗的邻接表查询（需要根据层数 N，动态生成 SQL）
SELECT COUNT(*), SUM(t5.code), SUM(CHAR_LENGTH(t5.name)) -- SQLite 写法：SUM(LENGTH(t5.name))
  FROM adjacent t1
  JOIN adjacent t2 ON t2.pid = t1.id
  JOIN adjacent t3 ON t3.pid = t2.id
  JOIN adjacent t4 ON t4.pid = t3.id
  JOIN adjacent t5 ON t5.pid = t4.id
 WHERE t1.pid = 0;

`MySQL` 一键建表 `SQL`

（在我低配笔记本和固态上，大约执行了 1 分钟）

-- 允许 200 MB 的内存表
SET max_heap_table_size = 200 << 20;

-- 建临时数据表，装载 csv 数据，以及计算序号和父子关系
CREATE TABLE data (
    code    BIGINT      NOT NULL,
    p_code  BIGINT      NOT NULL,
    type    TINYINT     NOT NULL,
    name    VARCHAR(25) NOT NULL,
    id      INT         NOT NULL,
    pid     INT         NOT NULL,
    PRIMARY KEY (code) USING BTREE,
    INDEX USING BTREE (id),
    INDEX USING BTREE (pid, id)
) ENGINE = MEMORY;

-- 加载 csv
LOAD DATA INFILE 'area_code_2022.csv'
INTO TABLE data
CHARACTER SET UTF8MB4
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
(code, name, type, p_code);

-- 按照 code 顺序计算 id
UPDATE data
  JOIN (SELECT code, ROW_NUMBER() OVER win row_num
          FROM data
        WINDOW win AS (ORDER BY code)) t USING(code)
   SET id = row_num;

-- 计算 parent_id（不存在的标0）
UPDATE data a
  LEFT JOIN data b ON b.code = a.p_code
   SET a.pid = IFNULL(b.id, 0);

-- 建邻接表，并从临时数据表填充数据
CREATE TABLE adjacent (
    id      INT         NOT NULL,
    pid     INT         NOT NULL,
    is_leaf BOOL        NOT NULL,
    type    TINYINT     NOT NULL,
    code    BIGINT      NOT NULL,
    name    VARCHAR(25) NOT NULL,
    PRIMARY KEY (pid, id)
)
SELECT -1 pid, 0 id, FALSE is_leaf, 0 type, 0 code, '' name
 UNION ALL
SELECT pid, id, type = 5 is_leaf, type, code, name
  FROM data;

-- 建闭包表主表，并从临时数据表填充数据
CREATE TABLE closure (
    id      INT         NOT NULL,
    type    TINYINT     NOT NULL,
    code    BIGINT      NOT NULL,
    name    VARCHAR(25) NOT NULL,
    PRIMARY KEY (id)
)
SELECT 0 id, 0 type, 0 code, '' name
 UNION ALL
SELECT id, type, code, name
  FROM data;

-- 建闭包表树形关系表
CREATE TABLE closure_tree (
    ancestor    INT     NOT NULL,
    descendant  INT     NOT NULL,
    distance    TINYINT NOT NULL,
    PRIMARY KEY (descendant, distance)
);

-- 递归构建树形关系
INSERT INTO closure_tree(ancestor, descendant, distance)
WITH RECURSIVE
  parent_of(orig_id, id, dist) AS (
    SELECT id, id, 0
      FROM data
     UNION ALL
    SELECT orig_id, pid, dist + 1
      FROM parent_of
      JOIN data USING(id)
     WHERE id
  )
SELECT id, orig_id, dist
  FROM parent_of;

-- 为闭包表树形关系表建二级索引
CREATE INDEX idx_closure_tree ON closure_tree (ancestor, distance);

-- 丢弃临时数据表
DROP TABLE data;

`SQLite` 一键建表 `SQL`

下列 SQL 需要依赖 SQLite Shell 的 .import --csv，核心 SQLite 库不提供此功能。

因此，需要使用命令行的 SQLite 来运行（Windows 可去官网下载个 1~2 MB 的 sqlite3.exe）。

下面使用 Bash Shell 来包装执行命令与 SQL，大约需要运行 30 秒，然后在同目录下生成 150 MB 左右的 test.db。

#!/bin/bash

sqlite3 :memory: <<'EOF'

-- 在内存中计算，最后整理紧凑才写入文件
PRAGMA TEMP_STORE = MEMORY;

-- 导入 csv 文件至临时表
CREATE TEMP TABLE csv (code INTEGER PRIMARY KEY, name TEXT, type INT, p_code INT);
.import --csv area_code_2022.csv csv

-- 建邻接表
CREATE TABLE adjacent (
    id      INT     NOT NULL,
    pid     INT     NOT NULL,
    is_leaf INT     NOT NULL,
    type    INT     NOT NULL,
    code    INT     NOT NULL,
    name    TEXT    NOT NULL,
    PRIMARY KEY (pid, id)
) WITHOUT ROWID;

-- 填充邻接表
INSERT INTO adjacent (pid, id, is_leaf, type, code, name)
SELECT -1, 0, FALSE, 0, 0, ""
 UNION ALL
SELECT p_code, ROW_NUMBER() OVER (), type = 5, type, code, name
  FROM csv
 ORDER BY code;

-- 建临时索引，提速 code 搜索
CREATE INDEX i ON adjacent (code);

-- 更新 pid
UPDATE adjacent
   SET pid = t2.id
  FROM adjacent t2
 WHERE adjacent.pid = t2.code;

-- 丢弃临时索引
DROP INDEX i;

-- 建 id -> pid 索引
CREATE INDEX idx_adjacent_id ON adjacent (id);

-- 建闭包表主表
CREATE TABLE closure (
    id      INTEGER PRIMARY KEY,
    type    INT     NOT NULL,
    code    INT     NOT NULL,
    name    TEXT    NOT NULL
);

-- 建闭包表树形关系表
CREATE TABLE closure_tree (
    ancestor    INT NOT NULL,
    descendant  INT NOT NULL,
    distance    INT NOT NULL,
    PRIMARY KEY (descendant, distance)
) WITHOUT ROWID;

-- 填充闭包表主表
INSERT INTO closure (id, type, code, name)
SELECT id, type, code, name
  FROM adjacent;

-- 递归构建树形关系
WITH RECURSIVE
  parent_of(orig_id, id, dist) AS (
    SELECT id, id, 0
      FROM adjacent
     UNION ALL
    SELECT orig_id, pid, dist + 1
      FROM parent_of
      JOIN adjacent USING(id)
     WHERE id
  )
INSERT INTO closure_tree (ancestor, descendant, distance)
SELECT id, orig_id, dist
  FROM parent_of;

-- 为闭包表树形关系表建二级索引
CREATE INDEX idx_closure_tree ON closure_tree (ancestor, distance);

-- 整理紧实数据库后，写入磁盘
ANALYZE;
VACUUM INTO 'test.db';

EOF

前言