计算有限深度树中的子级数
我在 MySQL 中有一个树结构，每个节点都使用parent_id 字段。建筑规模相当大，但深度只有八层左右。每个节点还有一个 child_counter 字段。
我正在寻找一种高性能的方法来计算（和更新）每个节点拥有的子节点数量（按子节点计算，我包括子节点的子节点等），最好没有太多 SQL 调用。我不期望这会超级快，只是足够好。
我希望随着算法的迭代，可能有某种方法可以进行批量更新。
I have a tree structure in MySQL using a parent_id field for each node. The structure is quite large but only about 8 levels deep. Each node also has a child_counter field.
I'm looking for a performant way to calculate (and update) the number of children each node has (by children I am including children of children etc.), preferably with not too many SQL calls. I don't expect this to be super fast, just good enough.
I'm hoping there might be some way to do a mass update as the algorithm iterates.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
我以前已经实现过类似的东西，但不完全是。您是否能够在每个节点上存储“深度”整数，以标记它们在树中的深度？如果是这样，您可以使用 8 个查询来完成此操作，如果索引正确，这将是相当合理的。我想是这样的（手边没有mysql）。您可以从最低深度（即 8）向上查询：
UPDATE 节点 SET child_counter = ((SELECT SUM(child_counter) FROM Node WHERE Parent_id = id) + (SELECT COUNT(0) FROM Node WHERE Parent_id = id)) WHERE depth = xI've implemented something like this before, but not quite. Are you able to store a "depth" integer on each node, to mark their depth in the tree? If so, you could do this with 8 queries, which if properly indexed would be quite reasonable. Something like this I think (don't have mysql handy). You would query from the lowest depth (i.e. 8) upwards:
UPDATE Node SET child_counter = ((SELECT SUM(child_counter) FROM Node WHERE parent_id = id) + (SELECT COUNT(0) FROM Node WHERE parent_id = id)) WHERE depth = x这是一篇好文章：管理 MySQL 中的分层数据
目前使用带有附加计数列的邻接列表模型。但实际上，您尝试从分层数据中获取的内容看起来可以使用嵌套集模型更轻松地进行建模。
Here is a good article: Managing Hierarchical Data in MySQL
You're currently using an Adjacency List Model with additional count column. But actually what you try to get from hierarchical data looks like it could be modeled much easier with the Nested Set Model.