针对树结构优化的 SQL [英] Optimized SQL for tree structures

问题描述

如何从具有最佳性能的数据库中获取树结构数据?例如，假设您在数据库中有一个文件夹层次结构.其中文件夹数据库行具有 ID、名称 和 ParentID 列.

How would you get tree-structured data from a database with the best performance? For example, say you have a folder-hierarchy in a database. Where the folder-database-row has ID, Name and ParentID columns.

您是否会使用一种特殊的算法一次获取所有数据，从而最大限度地减少数据库调用量并在代码中对其进行处理?

Would you use a special algorithm to get all the data at once, minimizing the amount of database-calls and process it in code?

或者你会使用多次调用数据库并直接从数据库中获取结构?

Or would you use do many calls to the database and sort of get the structure done from the database directly?

也许有基于 x 数量的数据库行、层次结构深度或其他什么的不同答案?

Maybe there are different answers based on x amount of database-rows, hierarchy-depth or whatever?

编辑:我使用 Microsoft SQL Server，但其他观点的答案也很有趣.

Edit: I use Microsoft SQL Server, but answers out of other perspectives are interesting too.

推荐答案

这实际上取决于您将如何访问树.

It really depends on how you are going to access the tree.

一个聪明的技巧是给每个节点一个字符串 id，其中父节点的 id 是子节点的可预测子字符串.例如，父节点可能是01"，子节点可能是0100"、0101"、0102"等.这样你就可以一次从数据库中选择整个子树:

One clever technique is to give every node a string id, where the parent's id is a predictable substring of the child. For example, the parent could be '01', and the children would be '0100', '0101', '0102', etc. This way you can select an entire subtree from the database at once with:

SELECT * FROM treedata WHERE id LIKE '0101%';

因为条件是初始子字符串，所以 ID 列上的索引会加快查询速度.

Because the criterion is an initial substring, an index on the ID column would speed the query.

这篇关于针对树结构优化的 SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！