分层数据结构设计（嵌套集）

我正在研究分层数据库结构的设计，该结构对包含产品的目录进行建模（这类似于这个问题 https://stackoverflow.com/questions/112866/database-schema-for-a-hierarchial-groups）。数据库平台是 SQL Server 2005，目录相当大（750,000 个产品，4 个级别的 8,500 个目录部分），但相对静态（每天重新加载一次），因此我们只关心 READ 性能。

目录层次结构的一般结构是：-

• Level 1 Section
  • Level 2 Section
    • Level 3 Section
      • 4 级部分（产品链接至此处）

我们使用嵌套集模式来存储层次结构级别并将该级别存在的产品存储在单独的链接表中。所以简化的数据库结构是

CREATE TABLE CatalogueSection
(
    SectionID INTEGER,
    ParentID INTEGER,
    LeftExtent INTEGER,
    RightExtent INTEGER
)

CREATE TABLE CatalogueProduct
(
    ProductID INTEGER,
    SectionID INTEGER
)

我们确实有一个额外的复杂性，因为我们有大约 1000 个独立的客户群体，他们可能会也可能不会看到目录中的所有产品。因此，我们需要为每个客户组维护目录层次结构的单独“副本”，以便当他们浏览目录时，他们只能看到自己的产品，也看不到任何空白部分。

为了促进这一点，我们维护一个从以下部分“汇总”的层次结构每个级别的产品数量表。因此，即使产品仅直接链接到层次结构的最低级别，它们也会一直计数到树上。该表的结构是

CREATE TABLE CatalogueSectionCount
(
    SectionID INTEGER,
    CustomerGroupID INTEGER,
    SubSectionCount INTEGER,
    ProductCount INTEGER
)

那么，说到问题层次结构顶层的性能非常差。显示所选目录部分（以及所有子部分）中“前 10 名”产品的一般查询大约需要 1 分钟才能完成。在层次结构的较低部分，速度更快，但仍然不够好。

我已经在所有关键表上放置了索引（包括适用的覆盖索引），通过查询分析器、索引调整向导等运行它，但仍然无法让它执行得足够快。

我想知道这个设计是否存在根本缺陷，或者是否是因为我们拥有如此大的数据集？我们有一个合理的开发服务器（3.8GHZ Xeon，4GB RAM），但它无法正常工作:)

谢谢你的帮助

James

使用闭合表。如果您的基本结构是具有 ID 和 ParentID 字段的父子结构，则闭包表的结构是 ID 和 DescendantID。换句话说，闭包表是一个祖先-后代表，其中每个可能的祖先都与所有后代相关联。如果需要，您可以包含 LevelsBetween 字段。闭包表实现通常包括自引用记录，即 ID 1 是后代 ID 1 的祖先，其 LevelsBetween 为零。

例子： 家长/孩子
家长 ID - ID
1 - 2
1 - 3
3 - 4
3 - 5
4 - 6

祖先/后代
ID - 后代 ID - LevelsBetween
1 - 1 - 0
1 - 2 - 1
1 - 3 - 1
1 - 4 - 2
1 - 6 - 3
2 - 2 - 0
3 - 3 - 0
3 - 4 - 1
3 - 5 - 1
3 - 6 - 2
4 - 4 - 0
4 - 6 - 1
5 - 5 - 0

该表旨在消除递归连接。您可以将递归连接的负载推入 ETL 循环中，每天加载一次数据时都会执行该循环。这将它从查询中移开。

此外，它还允许可变级别的层次结构。你不会被困在4点。

最后，它允许您将产品放入非叶节点中。许多目录在层次结构的较高级别创建“杂项”存储桶，以创建用于附加产品的叶节点。您不需要这样做，因为中间节点包含在闭包中。

就索引而言，我会在 ID/DescendantID 上创建聚集索引。

现在来看看您的查询性能。这会减少一部分，但不是全部。您提到了“前十名”。这意味着对您未提及的一组事实进行排名。我们需要细节来帮助调整这些。另外，这仅获取叶级部分，而不是产品。至少，您应该在 CatalogueProduct 上有一个按 SectionID/ProductID 排序的索引。我会根据您提供的基数强制部分到产品的连接成为循环连接。关于目录部分的报告将转到闭包表以获取后代（使用聚集索引查找）。然后，该后代列表将用于使用循环索引查找的索引从 CatalogueProduct 获取产品。然后，通过这些产品，您将获得进行排名所需的事实。