在数据库中实现分层数据结构 [英] Implementing a hierarchical data structure in a database

问题描述

我知道有两种方法：邻接列表和嵌套树。据说，邻接列表可能变得缓慢使用遍历，因为大量的查询。但我不知道任何现实的数字。我在做的网站将有大约200页。

I know there are two approaches: adjacency list and nested tree. It's said that adjacency list can become slow to use on traversal because of numerous queries. But I don't know any realistic figures for this. The site I'm making will have in the region of 200 pages. Is traversal to generate (for example) a sitemap going to take longer than about 0.3 seconds?

使用LAMP堆栈在MySQL（innoDB）上运行

Running on MySQL (innoDB) with LAMP stack.

我更喜欢实现邻接，因为更简单的设计。

I'd prefer to implement adjacency if possible because of the more simplistic design.

谢谢。

推荐答案

有更多的选择，而不只是你提到的两个。有：

There are more options than just the two you mention. There are:

• 邻接列表（几乎每个人都使用的parent_id）


• 嵌套集


• 路径枚举


• 关闭表（又称邻接关系）

• Adjacency List (the "parent_id" one almost everyone uses)
• Nested Sets
• Path Enumeration
• Closure Table (aka Adjacency Relation)

查看我的答案

See my answer to "What is the most efficient/elegant way to parse a flat table into a tree?"

或者是一个最有效/最优雅的方法来解析一个平面表格几本书：

Or a couple of books:

• 
  
• SQL中的树和层次结构 / 0977671542rel =noreferrer> SQL设计模式。

• "Trees and Hierarchies in SQL for Smarties" by Joe Celko.
• "SQL Design Patterns" by Vadim Tropashko.

这篇关于在数据库中实现分层数据结构的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！