代码之家 › 专栏 › 技术社区 › Brian Kim

智能选择SQL 2005中表的主键和聚集索引,以提高选择单个记录或多个记录的性能

clustered-index primary-key performance sql-server-2005

1

Brian Kim · 技术社区 · 16 年前

编辑: 我添加了“slug”列来解决特定记录选择的性能问题。

我的表中有以下列。

Id Int - Primary key (identity, clustered by default)
Slug varchar(100)
...
EntryDate DateTime

大多数情况下,我都是按entrydate排序select语句,如下所示。

Select T.Id, T.Slug, ..., T.EntryDate
From (
    Select Id, Slug, ..., EntryDate,  
        Row_Number() Over (Order By EntryDate Desc, Id Desc) AS RowNum
    From TableName
    Where ...
) As T
Where T.RowNum Between ... And ...

我按EntryDate和ID排序,以防有重复的EntryDate。

当我选择一个记录时,我会执行以下操作。

Select Id, Slug, ..., EntryDate
From TableName
Where Slug = @slug And Year(EntryDate) = @entryYear 
    And Month(EntryDate) = @entryMonth

我有一把独特的Slug&EntryDate钥匙。

在我的情况下,如何明智地选择密钥和索引?我面临着性能问题,可能是因为我按一个没有聚集索引的列排序。

我应该将id设置为非聚集主键,将entrydate设置为聚集索引吗?

谢谢你的帮助。谢谢。

编辑:

我没有尝试在entrydate上添加非聚集索引。从后端插入数据,所以插入的性能对我来说不是什么大问题。此外,EntryDate并不总是插入日期。可能是过去的约会。后端用户选择日期。

5 回复 | 直到 15 年前

1

mrdenny 16 年前

基于当前的表布局,您需要这样的索引。

CREATE INDEX IX_YourTable_1 ON dbo.YourTable
(EntryDate, Id)
INCLUDE (SLug)
WITH (FILLFACTOR=90)

CREATE INDEX IX_YourTable_2 ON dbo.YourTable
(EntryDate, Slug)
INCLUDE (Id)
WITH (FILLFACTOR=80)

添加要返回到包含行的任何其他列。

将第二个查询更改为类似的内容。

Select Id, Slug, ..., EntryDate
From TableName
Where Slug = @slug 
    AND EntryDate BETWEEN CAST(CAST(@EntryYear AS VARCHAR(4) + CAST(@EntryMonth AS VARCHAR(2)) + '01' AS DATE) AND DATEADD(mm, 1, CAST(CAST(@EntryYear AS VARCHAR(4) + CAST(@EntryMonth AS VARCHAR(2)) + '01' AS DATE))

当前写入第二个查询的方式将永远不会使用索引。如果您可以将slug列更改为相关表,它将提高您的性能并降低您的存储需求。

2

0

E.J. Brennan 16 年前

您是否尝试简单地在EntryDate上添加一个非聚集索引,以查看您获得了什么样的性能提升?

另外,添加新数据的频率是多少?添加的新数据是否总是>=最后一个入口日期?

3

0

Mainegreen 16 年前

您希望将ID保留为聚集索引,因为您很可能会从ID中加入表,而不是加入日期。

只包含日期字段的简单非聚集索引可以加快速度。

4

0

Diodeus - James MacFarlane 16 年前

集群有点像“索引分页”,索引是“分块的”,而不是简单的长列表。当你有很多数据的时候,这很有用。数据库可以在集群范围内搜索,然后查找单个记录。它使索引变小,因此搜索速度更快,但不太具体。一旦在集群中找到正确的点,它就需要在集群中搜索。

数据量大时速度更快,数据量小时速度较慢。

如果使用主键搜索的次数不多,则对日期进行群集,并使主键保持非群集状态。这实际上取决于您的查询与联接其他表的复杂程度。

5

0

dkretz 16 年前

如果返回的是一组记录,并且返回的某些字段不是索引的一部分,那么聚集索引就只会有任何不同。否则没有好处。

您需要首先了解查询计划告诉您的是,为什么当前查询速度较慢。如果没有这一点,则主要是闲置的推测(在优化查询时通常会适得其反)。

我不会尝试任何东西(由我或任何其他人建议),除非有一个坚实的查询计划来比较,至少知道你做的是好还是坏。