MySQL - 处理这种分层数据的最佳方法？

Question

这是以下内容的后续：
MySQL - 是否可以获取所有层次结构中的子项？

我有一个任意深度的邻接列表模型表（我现在可以将其转换为 >嵌套集合模型。

我阅读了有关如何使用嵌套集合模型的MySQL数据，尽管执行插入、更新和删除等基本功能似乎变得越来越复杂，并且非常复杂，

另一个博客展示了如何 使用嵌套集合模型。使用带有邻接列表模型的触发器系统来保留将每个对象与其祖先相关联的祖先表。

---

现在我需要能够返回给定节点的所有子节点的列表，以更改或删除它们。结构一旦创建就不会一直改变，但是会有大量的层次结构，

我看到的三种方法是：

1. 创建一个存储过程，它将执行递归。返回所有子项的查询。

2. 转换为嵌套集模型，这需要了解复杂性，并可能创建一个存储过程来在其中添加、编辑和删除。

3. 创建上述插入/删除触发器上的祖先表以处理所有数据。

如果还有我没有探索的其他方法，请告诉我，我将更新此列表。

Answer 1

Quassnoi 对嵌套集模型和邻接列表模型进行了一些性能测试，并在他的博客文章中记录了结果和建议邻接列表与嵌套集：MySQL。执行摘要是：

• 嵌套集可以更快地获取所有子节点或所有父节点。
• 如果您经常需要更新表，则嵌套集不是一个好主意。

这是他文章的结论：

在 MySQL 中，如果层次结构的更新不频繁，并且在更新期间锁定表（在长表上可能需要几分钟）是可以承受的，则应首选嵌套集模型。

这意味着使用 MyISAM 存储引擎创建表，创建如上所述的 GEOMETRY 类型的边界框，使用 SPATIAL 索引对其进行索引，并将级别保留在表中。

如果表的更新比较频繁，或者更新所带来的长时间锁定表的负担是难以承受的，那么应该使用邻接表模型来存储分层数据。

这需要创建一个函数来查询表。

本文的其余部分将展示如何定义表、实现查询并提供性能测量。使用空间索引是一个聪明的想法，可以提高您可能不熟悉的嵌套集模型的性能。

---

如果您也在考虑不使用 MySQL 的方法，那么您可能需要看看 PostgreSQL，这是另一个免费且开放的-源数据库。 PostgreSQL 支持 递归公用表表达式 形式的递归查询这使得查询层次结构数据比在 MySQL 中更容易，并且性能也更好。 Quassnoi 还写了一篇文章 邻接列表与嵌套设置：PostgreSQL 显示详细信息。

当我们谈论其他方法时，Oracle 的数据库也值得一提。 Oracle 还有一个自定义扩展CONNECT BY，它使查询层次结构数据变得非常容易和快速。 Quassnoi 的文章邻接列表与嵌套集：Oracle< /a> 再次涵盖性能细节。在这种情况下，获取所有子项所需的查询非常简单：

SELECT *
FROM yourtable
START WITH id = 42
CONNECT BY parent = PRIOR id

Answer 2

为了简单和方便，我总是选择嵌套集。我总是建议这篇文章。它很好地显示了处理此类分层数据所需的查询。我在这里看到的唯一缺点是，当层次结构达到一定程度的复杂性时，插入/更新新记录可能会变慢，但读取速度比我见过的许多其他解决方案要快。

只是给你一个上面文章中的例子：

SELECT t1.name AS lev1, t2.name as lev2, t3.name as lev3, t4.name as lev4
FROM category AS t1
LEFT JOIN category AS t2 ON t2.parent = t1.category_id
LEFT JOIN category AS t3 ON t3.parent = t2.category_id
LEFT JOIN category AS t4 ON t4.parent = t3.category_id
WHERE t1.name = 'ELECTRONICS';

+-------------+----------------------+--------------+-------+
| lev1        | lev2                 | lev3         | lev4  |
+-------------+----------------------+--------------+-------+
| ELECTRONICS | TELEVISIONS          | TUBE         | NULL  |
| ELECTRONICS | TELEVISIONS          | LCD          | NULL  |
| ELECTRONICS | TELEVISIONS          | PLASMA       | NULL  |
| ELECTRONICS | PORTABLE ELECTRONICS | MP3 PLAYERS  | FLASH |
| ELECTRONICS | PORTABLE ELECTRONICS | CD PLAYERS   | NULL  |
| ELECTRONICS | PORTABLE ELECTRONICS | 2 WAY RADIOS | NULL  |
+-------------+----------------------+--------------+-------+
6 rows in set (0.00 sec)

从 SQL 角度来说，我认为它不会变得更漂亮和更简单;)

我不知道存储过程方式。但由于它涉及递归（在您的情况下），我不知道层次结构中的许多级别是否会很快。我想你可以尝试一下。

Answer 3

也许您应该考虑使用面向文档的数据库，例如 MongoDB。它可以让你的生活变得更轻松。

Answer 4

在处理分层数据集时，我发现最好在考虑缓存的情况下进行处理。以这种方式处理此问题的主要好处之一是它不需要将数据库非规范化为可能更难以变异的内容。

由于对于简单的 id -> 来说，内存堆（memcache、redis 等）查找比 SQL 快得多。 data 分辨率，我将使用它们来缓存每个节点的直接子节点的 id 列表。这样，您可以通过递归算法为任何节点构建完整列表，从而获得不错的性能。

要添加/删除新节点，您只需使其直接父缓存失效O(1)。

如果这还不够快，您可以将另一层缓存添加到每个节点的所有子节点的列表中。为了使其能够处理适当可变的数据集，您应该记录每个节点的缓存性能（新鲜/缓存命中的比率），并设置存储缓存的容忍级别。由于它是非重要数据，因此也可以存储在内存堆中。

如果您使用这种更高级的缓存模型，您需要注意，当任何子节点发生更改O(log n)时，这些完整的子节点列表将需要失效。

获得子 ID 列表后，您可以使用 SQL 的 WHERE id IN( id1, id2, .... ) 语法来查询您想要的内容。

Answer 5

我曾经不得不在类似 SQL 的数据库管理器中存储一个复杂的分层任意深度的物料清单系统，但这并不能真正胜任这项任务，最终导致了混乱和棘手的索引、数据定义、查询等从头开始重新启动后，使用数据库管理器仅提供一个用于在简单索引键上进行记录读取和写入的API，并在外部代码中完成所有实际的输入/操作/报告，最终结果实现起来更快，更容易。理解，并且更容易维护和增强。所需的最复杂的查询本质上是 SELECT A FROM B。

因此，不要在 MySQL 的限制内嵌入逻辑和操作，而是考虑敲出代码来执行您想要的操作，并仅依赖 MySQL 进行最低级别的获取/放置。