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

我正在设计一个分层数据库结构的设计,该结构模拟包含产品的目录(这与此问题类似).数据库平台是SQL Server 2005,目录非常大(750,000个产品,4个级别的8,500个目录部分),但是相对静态(每天重新加载一次),所以我们只关心READ性能.

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

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),但它只是不工作:)

谢谢你的帮助

詹姆士

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

示例:父/子
ParentID - ID
1 - 2
1 - 3
3 - 4
3 - 5
4 - 6

祖先/后裔
ID - 后代ID - 级别
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排序.我会根据您提供的基数强制Section to Product连接成为循环连接.关于目录部分的报告将转到闭包表以获取后代(使用聚簇索引搜索).然后,该子列表将用于通过循环索引查找使用索引从CatalogueProduct获取产品.然后,使用这些产品,您将获得进行排名所需的事实.