I am building web application that should have high write load and thousands, even millions of hierarchical records representing user defined/constructed trees. I am not trying to build forum with threads but huge database with thousands of small-sized hierarchies (trees with up to 10-20 descendants)...
我正在构建应该具有高写入负载的Web应用程序以及表示用户定义/构造树的数千甚至数百万个分层记录。我不是想用线程构建论坛，而是用数千个小型层次结构构建庞大的数据库（树木最多有10-20个后代）......
I am aware of many models for storing hierarchies - currently I am using Nested Sets but performance with huge data and load is issue. I am also doubtful that Adjacency Lists or something similar may resolve this.
我知道存储层次结构的许多模型 - 目前我使用的是嵌套集，但是具有大量数据和负载的性能是个问题。我也怀疑邻接列表或类似的东西可以解决这个问题。
I have been experimenting with Mongo database which is superfast key/value storage but I can use only MySQL.
我一直在试验Mongo数据库，它是超高速键/值存储但我只能使用MySQL。
I would like to hear about other people experiences with similar issues.
我想听听其他人遇到类似问题的经历。
2 个解决方案
#1
5
If you can install MySQL plugins, then OQGraph storage engine is what you need.
如果您可以安装MySQL插件，那么OQGraph存储引擎就是您所需要的。
#2
4
What is the problem with nested sets?
嵌套集有什么问题？
Is recomputing the lft/rgt values when you add/remove nodes?
添加/删除节点时是重新计算lft / rgt值吗？
Pretty sure with a bit of careful planning, you can tweak it so do only have to do rare recomputations. I've not actully tried it, but did do some planning for a system once (the client didnt want the system in the end!)
非常肯定通过一些精心策划，你可以调整它，所以只需做罕见的重新计算。我没有尝试过它，但确实做了一次系统规划（客户端最终不想要系统！）
One, is multiplying the values, by say 1000, when first calculating them. Then if you add a node, you can just insert numbers between the values. Its only when there is a large number of insertions, do you start running out of numbers. A low priority batch process, could recompute the tree to free up numbers for fresh insertions.
一，在首次计算时，将这些值乘以1000。然后，如果添加节点，则只需在值之间插入数字即可。它只有在有大量插入时才会开始耗尽数字。低优先级批处理过程可以重新计算树以释放数字以进行新插入。
Deleting can also be archived, with manipulating numbers. In fact a node without children is easy. No recomputation nedded. Gets more complicated if children, but I think should be doable.
删除也可以通过操纵数字进行存档。实际上，没有孩子的节点很容易。没有重新计算。如果孩子们变得更复杂，但我认为应该是可行的。
#1
5
If you can install MySQL plugins, then OQGraph storage engine is what you need.
如果您可以安装MySQL插件，那么OQGraph存储引擎就是您所需要的。
#2
4
What is the problem with nested sets?
嵌套集有什么问题？
Is recomputing the lft/rgt values when you add/remove nodes?
添加/删除节点时是重新计算lft / rgt值吗？
Pretty sure with a bit of careful planning, you can tweak it so do only have to do rare recomputations. I've not actully tried it, but did do some planning for a system once (the client didnt want the system in the end!)
非常肯定通过一些精心策划，你可以调整它，所以只需做罕见的重新计算。我没有尝试过它，但确实做了一次系统规划（客户端最终不想要系统！）
One, is multiplying the values, by say 1000, when first calculating them. Then if you add a node, you can just insert numbers between the values. Its only when there is a large number of insertions, do you start running out of numbers. A low priority batch process, could recompute the tree to free up numbers for fresh insertions.
一，在首次计算时，将这些值乘以1000。然后，如果添加节点，则只需在值之间插入数字即可。它只有在有大量插入时才会开始耗尽数字。低优先级批处理过程可以重新计算树以释放数字以进行新插入。
Deleting can also be archived, with manipulating numbers. In fact a node without children is easy. No recomputation nedded. Gets more complicated if children, but I think should be doable.
删除也可以通过操纵数字进行存档。实际上，没有孩子的节点很容易。没有重新计算。如果孩子们变得更复杂，但我认为应该是可行的。