MySQL-处理此分层数据的最佳方法? [英] MySQL - Best method to handle this hierarchical data?
问题描述
这是对以下项目的后续行动:
MySQL-是否有可能获得全部层次结构中的子项目?
This is a followup to:
MySQL - Is it possible to get all sub-items in a hierarchy?
我有一个任意深度的邻接表模型表(我现在可以 将其转换为嵌套集模型
I have an arbitrary-depth adjacency list model table (I am at the point that I can convert it into a nested set model.
我阅读了有关如何使用嵌套集模型的MySQL数据，尽管执行诸如插入，更新和删除之类的基本功能似乎变得越来越复杂，而且非常复杂.
I read the MySQL data on how to use a nested set model, though it seemed to get increasingly complex and very complex to do basic functions such as inserting, updating and deleting.
另一个博客，展示了如何将触发器系统与邻接表模型一起使用，以保留将每个对象与其祖先相关联的祖先表.
Another blog showing how to use a trigger system with the adjacency list model to keep a table of ancestors that relates each object to its ancestors.
现在，我需要能够返回给定节点的所有子代的列表，以对其进行更改或删除.这种层次结构一旦创建就不会一直改变，但是会有大量的层次结构.
Right now I need to be able to return a list of all children of a given node, to change or delete them. This hierarchical structure won't be changing all the time once created, but there will be a mass amount of the hierarchical structures.
我看到的三种方法是:
-
创建了一个存储过程，它将执行一个递归查询，该查询将返回所有子项.
Created a Stored Procedure which would do a recursive query that returns all children.
转换为嵌套集模型，这可能需要了解复杂性并可能创建存储过程以在其中添加，编辑和删除.
Convert to Nested Set Model which would require to get into the complexities and possibly create a stored procedure to add, edit and delete in that.
在插入/删除触发器上创建上述的祖先表，以处理所有数据.
如果我没有其他方法，请告诉我，我将更新此列表.
If there are other methods I'm not exploring, please let me know and I'll update this list.
推荐答案
Quassnoi 对嵌套广告进行了一些性能测试设置模型和邻接表模型，并在他的博客文章邻接列表与嵌套集:MySQL .执行摘要是:
Quassnoi has run some performance tests on the nested sets model and the adjacency list model and documented the results and recommendations in his blog post Adjacency list vs. nested sets: MySQL. The executive summary is:
- 嵌套集可以更快地获取所有子节点或所有父节点.
- 如果您经常需要更新表，则嵌套集是一个坏主意.
这是他文章的结论:
在MySQL中，如果对层级结构的更新很少，并且在更新期间锁定表(在长表上可能要花几分钟)是可以承受的，则应首选嵌套集模型.
In MySQL, the nested sets model should be preferred if the updates to the hierarhical structure are infrequent and it is affordable to lock the table for the duration of an update (which can take minutes on a long table).
这意味着使用MyISAM存储引擎创建表，创建如上所述的GEOMETRY类型的边界框，并使用SPATIAL索引对其进行索引，然后将级别保留在表中.
This implies creating the table using MyISAM storage engine, creating the bounding box of a GEOMETRY type as described above, indexing it with a SPATIAL index and persisting the level in the table.
如果对表的更新频繁或由于更新而导致无法长时间锁定表，则应使用邻接表模型来存储层次结构数据.
If the updates to the table are frequent or it is inaffordable to lock the table for a long period of time implied by an update, then the adjacency list model should be used to store the hierarchical data.
这需要创建一个查询表的函数.
This requires creating a function to query the table.
本文的其余部分显示了如何定义表，实现查询并给出性能评估.使用空间索引是提高嵌套集模型(可能对您来说不新鲜)的性能的一个聪明主意.
The rest of the article shows how to define the table, implement the queries and gives performance measurements. The use of the spatial index is a clever idea to improve the performance of the nested set model that might be new to you.
如果您还在考虑不使用MySQL的方法，那么您可能想看看 PostgreSQL ，这是另一个免费和开源数据库. PostgreSQL支持递归公用表表达式形式的递归查询与MySQL中的查询相比，查询分层式数据更容易，并且性能更高. Quassnoi还写了一篇文章邻接列表与嵌套列表设置:显示详细信息的PostgreSQL .
If you're also considering approaches without MySQL then you might want to look at PostgreSQL which is another free and open-source database. PostgreSQL supports recursive queries in the form of recursive common table expressions which make querying heirarchical data easier than in MySQL and also give better performance. Quassnoi has also written an article Adjacency list vs. nested sets: PostgreSQL that shows the details.
尽管我们正在谈论其他方法，但是Oracle的数据库也值得一提. Oracle还具有一个自定义扩展名CONNECT BY，它使查询分层数据非常容易和快速. Quassnoi的文章邻接列表与嵌套集:Oracle 再次介绍了性能细节.在这种情况下，获取所有子代所需的查询非常简单:
While we are talking about looking at other approaches, Oracle's database is also worth a mention. Oracle also have a custom extension CONNECT BY which make querying heirarchical data very easy and fast. Quassnoi's article Adjacency list vs. nested sets: Oracle again covers the performance details. The query you need to get all children is extremely simple in this case:
SELECT *
FROM yourtable
START WITH id = 42
CONNECT BY parent = PRIOR id
这篇关于MySQL-处理此分层数据的最佳方法?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
