@老虎会游泳，我是读到一篇博文《在数据库中存储一棵树，实现无限级分类》时知道闭包表的。
（这篇博文还流传很广泛，我在必应上搜 “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
老虎会游泳
