代码之家 › 专栏 › 技术社区 › Avalanchis

聚集索引对数据库性能的影响

sql-server-2000 indexing database-design sql-server

9

Avalanchis · 技术社区 · 15 年前

我最近参与了一个新的软件项目,它使用SQLServer2000作为数据存储。

在查看项目时,我发现其中一个主表在其主键上使用了聚集索引,该索引由四列组成:

Sequence  numeric(18, 0)
Date      datetime
Client    varchar(9)
Hash      tinyint

现在,我是C++开发人员,而不是DB管理员,但我对这个表设计的第一印象是,将这些字段作为聚集索引对插入性能非常不利,因为数据必须在每个插入件上物理重新排序。

另外,我看不出这有什么好处,因为必须经常查询所有这些字段才能证明聚集索引的正确性,对吗?

5 回复 | 直到 14 年前

1

16

momo 10 年前

聚集索引应包含查询次数最多的列,以便最大程度地查找或使非聚集索引覆盖查询中的所有列。

主键和聚集索引不必相同。它们都是候选键,表通常有多个这样的键。

另外,我看不出这有什么好处,因为必须经常查询所有这些字段才能证明聚集索引的正确性,对吗?

正如其他人所说,尽可能减少聚集索引中的列/字节数非常重要。

很遗憾,序列是一个随机值,而不是递增值,但这可能没有帮助。答案 不是吗

将这些字段作为聚集索引将对insert性能非常不利,因为每次insert都必须对数据进行物理重新排序。

这不完全正确。

表现暗示。

不

我相信将datetime作为第二个CI列可能是有益的,因为您仍然需要处理需要在两个不同的序列值之间拆分的页面,但这并不像CI中的第二列也是随机的那么糟糕,因为您可以保证在每次插入时都进行页拆分,如果行可以被添加到页面中,那么使用升序值可以获得幸运,因为下一个序列号从下一页开始。

缩短表中所有列的数据类型和数量以及非聚集索引也可以提高性能,因为每页的行数越多,每个请求的页读取次数就越少。尤其是当引擎被迫执行表扫描时。将一堆很少查询的列移动到一个单独的1-1表中,可以为某些查询带来奇迹。

将Sequence列更改为bigint,以便为每行保存一个字节(8字节而不是9字节)。

摘要:CI应以查询最多的列开始。从CI中删除任何可以删除的列。尽可能缩短列(字节)。使用较低的fillfactor来减轻随机序列列导致的页拆分(如果由于查询次数最多而必须保持在第一位)。

哦,让你的在线碎片整理开始吧。如果表不能更改,至少可以经常重新组织以保持其最佳状态。也不要忽略统计数据,这样引擎就可以选择适当的执行计划。

更新

要考虑的另一个策略是,是否可以将表中使用的复合键转换为int,并创建一个值的查找表。假设在超过100行中重复一些少于全部4列的组合,例如,Sequence+Client+Hash,但只使用不同的日期值。然后,对带有标识列的单独SequenceClientHash表进行插入是有意义的,因为这样您就可以查找一次人工键并反复使用它。这还将使您的CI只在最后一页上添加新行(yay),并大大减少CI的大小,就像在所有非聚集索引(yippee)中重复的那样。但这只有在某些狭义的使用模式下才有意义。

现在,marc_建议只添加一个额外的int identity列作为聚集索引。这可能有助于使所有非聚集索引每页获得更多的行,但这完全取决于您希望性能的确切位置,因为这将保证表上的每个查询都必须使用书签查找,并且您永远无法获得表查找。

最终,这一切都归结为一个精确的系统和它独特的数据访问模式,以及关于哪些部分需要优化的决策。对于某些系统,只要选择总是快速的,插入速度较慢并不坏。对于其他人来说,有一致但稍慢的选择时间比有稍快但不一致的选择时间更重要。对于其他人来说,数据在被推送到数据仓库之前是不会被真正读取的,所以插入的速度必须尽可能快。此外,性能不仅与用户等待时间甚至查询响应时间有关,还与服务器资源有关(特别是在大规模并行的情况下),因此总吞吐量(例如,每时间单位的客户端响应)比任何其他因素都更重要。

2

5

Mike M. 15 年前

我不知道你的数据通常是怎么被击中的。通常,您会在标识列或另一个窄列上看到CI(因为此列也将“附加”到所有非聚集索引,如果不需要,我们不希望在每次获取时都添加大量数据)。数据可能在最常被查询的日期,这可能是一个不错的选择,但所有四列都是 可能的 http://msdn.microsoft.com/en-us/library/aa933131%28SQL.80%29.aspx

3

2

JohnFx 15 年前

关于SQL如何创建和使用索引,您有一些误解。

聚集索引 aren't necessarily physically ordered on disk by the clustered index

我不认为基于这种结构会对性能造成重大影响,而且在您实际确定与聚集索引相关的性能问题之前删除聚集索引显然是非常重要的 .

此外,索引也很有用(尤其是其中包含多个字段的索引),即使对于没有对其中包含的所有列进行排序或查询的搜索也是如此。

显然,创建一个由多部分组成的聚集索引应该是有道理的,就像任何索引一样,所以如果您认为它是随意添加的,那么要求它是有道理的。

一句话:在实际检测到insert的性能问题之前,不要为insert性能优化索引。这通常是不值得的。

4

1

marc_s 15 年前

如果您的表上只有一个聚集索引,那可能不会太糟。但是,聚集索引还用于查找实际数据页中非聚集索引中的任何命中-因此,聚集索引(其所有列)也是表中每个非聚集索引的一部分。

一个好的聚集索引应该是:

小(最佳匹配:一个4字节的INT)-你的非常糟糕,每个条目最多28字节
稳定(永不改变)
不断增加

我敢打赌你当前的设置至少违反了两个,如果不是更多的要求。不遵循这些建议将导致空间浪费,而且正如您正确地说的那样,大量的页和索引碎片以及页拆分(当插入发生在聚集索引的中间位置时,必须“重新排列”数据)。

老实说:只要加一个代理 ID INT IDENTITY(1,1)

有关什么是好的集群键以及它们的重要内容的更多背景信息,请参见:

5

0

momo 14 年前

我最终同意埃里克的最后一段话:

“归根结底,这一切都归结为一个精确的系统和它独特的数据访问模式,再加上关于哪些部分需要优化的决定……”

这是我强迫人们学习的最基本的东西: 没有普遍的解决办法。

你必须知道你的数据和对它执行的操作。您必须知道不同类型的操作的频率以及它们的影响和预期的执行时间(如果最终用户同意查询执行时间不是那么重要,您不必对一些很少执行的查询进行硬调优,也不必对其他所有操作产生影响——比如说,每周为某个报告等待几分钟是可以的)。当然,正如埃里克所说

如果这样的查询会影响服务器的整体性能,那么即使执行时间很短,也应该将其视为优化的重要候选对象。我见过一些非常快速的查询,它们在多处理器服务器上使用了大量CPU,而稍微慢一点的解决方案从资源利用率的角度来看是无与伦比的“轻量级”。那样的话,我几乎总是选慢一点的。

一旦你知道你的目标是什么,你就可以决定你需要多少索引,哪一个应该被聚集。唯一约束、过滤索引、包含列的索引是非常强大的调优工具。选择适当的列很重要,但通常选择适当的列顺序更为重要。最后,如果表经常被修改,不要用大量的索引来破坏insert/update性能。