在数据库中实现分层数据结构

2021-11-20 00:00:00 database hierarchical-data mysql

我知道有两种方法:邻接表和嵌套树.据说由于大量查询，邻接列表在遍历时会变得很慢.但我不知道这方面的任何现实数字.我正在制作的网站将有 200 页的区域.生成(例如)站点地图的遍历时间是否会超过大约 0.3 秒?

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")
  • 嵌套集
  • 路径枚举
  • 闭表(又名邻接关系)

查看我对将平面表解析为树的最有效/最优雅的方法是什么?"

或者几本书:

  • "Smarties 的 SQL 中的树和层次结构" 作者:乔塞尔科.
  • "SQL 设计模式" by Vadim Tropashko.
  • "Trees and Hierarchies in SQL for Smarties" by Joe Celko.
  • "SQL Design Patterns" by Vadim Tropashko.

相关文章