层次数据结构设计(嵌套集)

Question

我正在设计一个层次数据库结构，该结构对包含产品的目录进行建模(这与this question类似)。数据库平台是SQL Server 2005，目录相当大(750,000种产品，8,500个目录部分跨越4个级别)，但相对来说是静态的(每天重新加载一次)，因此我们只关心读取性能。

目录等级的一般结构是：-

• 级1科
  • 级2科
    • 级3科
      • 级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)，但它只是不能工作:)

谢谢你的帮助

詹姆斯

Answer 1

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

例:父母/子女

ParentID - ID

1-2

1-3

3-4

3-5

4-6

祖先/后代

ID - DescendantID - 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订购的索引。我将根据您所提供的基数强迫节到Product为be循环联接。目录部分的报告将转到闭包表以获取后代(使用聚集索引查找)。然后，该后人列表将被用于使用索引循环索引从CatalogueProduct获取产品。然后，有了这些产品，你就会得到做排名所必需的事实。

Answer 2

您可能能够用角色和treeId来解决客户组的问题，但是您必须向我们提供查询。

Answer 3

是否有可能在每天加载之后计算ProductCount和SubSectionCount？

如果数据每天只变化一次，那么计算这些数据肯定是值得的，即使需要进行某种非正态化。