mysql层次存储与大树 [英] mysql hierarchy storage with large trees
问题描述
我不知道如何将我的分层数据存储在我的innoDB表中。
I don't know how to store my hierarchical data in my innoDB-table.
我已经阅读了很多关于存储方法的缺点 parent_id 。但现在的问题是，我有一个非常大的数据库（〜50万行）。
I've read a lot about the disadvantages of the method of storing the parent_id in each row. But now the problem is, that I have a very large database (~50 million rows). The hierarchy is mostly not very deep (3-6 levels).
许多网站建议采用嵌套集模型作为父级ID存储的更好的替代方案-方法。但是网站的用户总是做出改变（ UPDATE ， INSERT 等）因为我的表的大小，这将花费太多时间（因为嵌套集模型的变化具有非常低的性能）。
Many websites advise taking the "Nested Set Model" as a better alternative to the parent-id-storing-method. But there are always changes being made (UPDATE, INSERT etc.) by the users of the website and because of the size of my table, this would take too much time (since changes in the "Nested Set Model" have a very low performance).
所以我的问题是：您可以有效地存储许多 update / insert 命令的大层次数据？ （也阻塞整个表不是一个选项[ - > innoDB-table]）
So my question is: How do you store efficiently large hierarchical data with many update/insert commands? (Also blocking the whole table is not an option [-> innoDB-table])
推荐答案
嵌套集的设计是绝对困难的当你需要频繁更新树。您最终必须重新编号树的大部分。
The Nested Sets design is definitely difficult when you need to make frequent updates to the tree. You end up having to renumber large parts of the tree.
一个缓解这个问题的建议是使用浮点数而不是整数。如果在树中插入一个新节点，在新节点的父节点的嵌套集编号之间找到一些FLOAT数是相对容易的。你最终可能达到浮点数精度的极限，但因为你的树不是很深，不会长时间发生。
One suggestion for mitigating this is to use floating-point numbers instead of integers. If you insert a new node in the tree, it's relatively easy to find some FLOAT numbers in between the nested set numbers of the parent of the new node. You may eventually get to the limits of the precision of a floating-point number, but since your tree isn't very deep that won't happen for a long time.
我写的另一种技术，我调用 Closure Table 。这种存储层次结构的方法使得在大树中插入/更新/删除节点更加容易，而无需更新大量的树。您还可以在单个非递归SQL查询中查询整个树或任何子树。
Another technique which I have written about I call Closure Table. This method of storing hierarchies makes it much easier to insert/update/delete nodes in a large tree without needing to update a lot of your tree. And you can still query the whole tree or any subtree in a single non-recursive SQL query.
有关Closure Table的更多信息，请参阅：
To read more about Closure Table, see:
- What is the most efficient/elegant way to parse a flat table into a tree?
- Models for Hierarchical Data with SQL and PHP
- Moving Subtrees in Closure Table Hierarchies
- SQL Antipatterns: Avoiding the Pitfalls of Database Programming
回覆您的意见：
邻接列表很简单，具有最小冗余，并且它支持FK关系，嵌套集不支持。如果您使用递归查询，则邻接列表支持查询任意深度的整个树。 a>。但是MySQL不支持递归查询。
Adjacency List is simple, has a minimum of redundancy, and it supports FK relationships, which Nested Sets does not. Adjacency List supports querying a whole tree of arbitrary depth if you use recursive queries. But MySQL does not support recursive queries.
如果你只需要查询直接父子关系（即一级深度），或者只查询固定深度的树，那么邻接列表就好了。
If you need to query only immediate parent-child relationships (i.e. one level of depth), or otherwise query only trees of fixed depth, then Adjacency List is fine.
这篇关于mysql层次存储与大树的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
