在审查项目时,我发现其中一个主表在其主键上使用聚集索引,其中包含四列：
Sequence numeric(18,0) Date datetime Client varchar(9) Hash tinyint
该表在正常运行过程中经历了大量插入.
现在,我是C开发人员,而不是DB管理员,但是我对该表设计的第一印象是将这些字段作为集群索引将是非常不利于插入性能,因为数据必须在物理上重新排序每个插入.
另外,我不能真正看到任何好处,因为人们必须频繁地查询所有这些字段来证明聚集索引是正确的？
所以基本上我需要一些弹药,当我去说服他们的权力,表格的设计应该改变.
解决方法@H_404_16@
聚集索引应包含最多查询的列,以最大的寻找机会或使非聚簇索引覆盖查询中的所有列.
主键和聚集索引不一定相同.它们都是候选键,表通常有不止一个这样的键.
你说
In addition,I can’t really see any benefit to this since one would have to be querying all of these fields frequently to justify the clustered index,right?
这不是真的.只需使用第一列或两个聚集索引即可.它可能是一个范围寻求,但它仍然是一个寻求.您不必指定它的所有列,以获得这个好处.但列的顺序很重要.如果您主要在客户端查询,则“序列”列是聚集索引中的第一个选择.第二列的选择应该是与第一列(而不是它自己)最多查询的项目.如果发现第二列本身几乎和第一列一样被查询,则非聚簇索引将会有所帮助.
正如其他人所说,尽可能多地减少聚集索引中的列/字节数.
这太糟糕了,序列是一个随机值而不是递增,但可能无法帮助.答案不是抛出一个标识列,除非您的应用程序可以开始将其用作该表上的主要查询条件(不太可能).现在,由于你遇到这个随机的Sequence列(假设它是最常被查询的),我们来看另外一个语句：
having these fields as a clustered index would be very detrimental to insert performance,since the data would have to be physically reordered on each insert.
这不是完全正确的.
磁盘上的物理位置并不是我们在这里所说的,而是在碎片化方面发挥作用,这是一种表现意义.
每个8k页面内的行都没有订购.只是每个页面中的所有行都比下一页更多,而不是上一行.当插入行并且页面已满时,会出现此问题：您将获得页面拆分.引擎必须将插入的行后的所有行复制到新页面,这可能是昂贵的.使用随机密钥,您将得到很多页面分割.您可以通过在重建索引时使用较低的fillfactor来改善问题.你必须使用它来获得正确的数字,但是70％或60％可能比90％更好.
我相信将datetime作为第二个CI列可能是有益的,因为您仍然需要处理需要在两个不同序列值之间分割的页面,但并不像CI中的第二列也是随机的那样糟糕,因为您将保证在每个插入页面上分页,如果该值为升值,则可以将该行添加到页面中获得幸运,因为下一个序列号将从下一页开始.
缩短表中所有列的数据类型和数量以及其非聚簇索引可以提高性能,因为每页更多的行=每个请求的页面读取更少.特别是如果引擎被迫做桌面扫描.将一堆很少查询的列移动到单独的1-1表可能会对您的一些查询产生奇迹.
最后,有一些设计调整也可以帮助(在我看来)：
>将Sequence列更改为bigint,以便为每行保留一个字节(8个字节而不是9个数字).
>使用4字节int标识列而不是varchar(9)的客户端查找表.这可以节省每行5个字节.如果可能,请使用2字节的smallint(-32768至32767),每行7个字节的节省更多.
摘要：CI应从最多查询的列开始.从CI中删除任何列.尽可能多地缩短列(字节).使用较低的fillfactor来减轻随机Sequence列引起的页面拆分(如果必须首先被查询最多).
哦,让你的在线碎片整理.如果表不能更改,至少可以经常重组,以保持最佳状态.也不要忽视统计,所以引擎可以选择适当的执行计划.
UPDATE
要考虑的另一个策略是如果表中使用的组合键可以转换为int,并且创建值的查找表.我们假设在超过100行中重复少于所有4列的某些组合,例如,Sequence Client Hash,但只有变化的Date值.然后插入到具有标识列的单独的SequenceClientHash表可能是有意义的,因为您可以查找人造键一次并重复使用它.这也可以让您的CI仅在最后一页(yay)添加新行,并大大减少所有非聚集索引(yippee)中重复的CI大小.但是,这在某些狭窄的使用模式中只会有意义.
现在,marc_s建议仅添加一个附加的int identity列作为聚簇索引.这可能有助于使所有非聚簇索引在每页上获得更多行,但这一切都完全取决于您希望执行性能的位置,因为这将保证表上的每一个查询都必须使用书签查找,你永远不会得到一个表寻求.
关于“吨的页面分割和错误的索引碎片”：正如我已经说过的,可以稍微改善一些较低的填充因子.另外,频繁的在线索引重组(与重建不一样)可以帮助减少这种影响.
最终,这一切都归结于确切的系统及其独特的数据访问模式以及您想要优化哪些部件的决策.对于某些系统来说,只要选择总是快速,插入速度较慢就不会坏.对于其他人来说,选择时间一致但稍慢的选择时间比选择时间稍长,但不一致更为重要.对于其他人来说,数据直到被推送到数据仓库才能真正读取,因此插入需要尽可能快.而混合增加的事实是,性能不仅仅是用户等待时间,甚至是查询响应时间,而且关于服务器资源,特别是在大规模并行性的情况下,因此总吞吐量(例如,在每个时间单位的客户端响应中)比任何其他因素更重要.
主键和聚集索引不一定相同.它们都是候选键,表通常有不止一个这样的键.
你说
In addition,I can’t really see any benefit to this since one would have to be querying all of these fields frequently to justify the clustered index,right?
这不是真的.只需使用第一列或两个聚集索引即可.它可能是一个范围寻求,但它仍然是一个寻求.您不必指定它的所有列,以获得这个好处.但列的顺序很重要.如果您主要在客户端查询,则“序列”列是聚集索引中的第一个选择.第二列的选择应该是与第一列(而不是它自己)最多查询的项目.如果发现第二列本身几乎和第一列一样被查询,则非聚簇索引将会有所帮助.
正如其他人所说,尽可能多地减少聚集索引中的列/字节数.
这太糟糕了,序列是一个随机值而不是递增,但可能无法帮助.答案不是抛出一个标识列,除非您的应用程序可以开始将其用作该表上的主要查询条件(不太可能).现在,由于你遇到这个随机的Sequence列(假设它是最常被查询的),我们来看另外一个语句：
having these fields as a clustered index would be very detrimental to insert performance,since the data would have to be physically reordered on each insert.
这不是完全正确的.
磁盘上的物理位置并不是我们在这里所说的,而是在碎片化方面发挥作用,这是一种表现意义.
每个8k页面内的行都没有订购.只是每个页面中的所有行都比下一页更多,而不是上一行.当插入行并且页面已满时,会出现此问题：您将获得页面拆分.引擎必须将插入的行后的所有行复制到新页面,这可能是昂贵的.使用随机密钥,您将得到很多页面分割.您可以通过在重建索引时使用较低的fillfactor来改善问题.你必须使用它来获得正确的数字,但是70％或60％可能比90％更好.
我相信将datetime作为第二个CI列可能是有益的,因为您仍然需要处理需要在两个不同序列值之间分割的页面,但并不像CI中的第二列也是随机的那样糟糕,因为您将保证在每个插入页面上分页,如果该值为升值,则可以将该行添加到页面中获得幸运,因为下一个序列号将从下一页开始.
缩短表中所有列的数据类型和数量以及其非聚簇索引可以提高性能,因为每页更多的行=每个请求的页面读取更少.特别是如果引擎被迫做桌面扫描.将一堆很少查询的列移动到单独的1-1表可能会对您的一些查询产生奇迹.
最后,有一些设计调整也可以帮助(在我看来)：
>将Sequence列更改为bigint,以便为每行保留一个字节(8个字节而不是9个数字).
>使用4字节int标识列而不是varchar(9)的客户端查找表.这可以节省每行5个字节.如果可能,请使用2字节的smallint(-32768至32767),每行7个字节的节省更多.
摘要：CI应从最多查询的列开始.从CI中删除任何列.尽可能多地缩短列(字节).使用较低的fillfactor来减轻随机Sequence列引起的页面拆分(如果必须首先被查询最多).
哦,让你的在线碎片整理.如果表不能更改,至少可以经常重组,以保持最佳状态.也不要忽视统计,所以引擎可以选择适当的执行计划.
UPDATE
要考虑的另一个策略是如果表中使用的组合键可以转换为int,并且创建值的查找表.我们假设在超过100行中重复少于所有4列的某些组合,例如,Sequence Client Hash,但只有变化的Date值.然后插入到具有标识列的单独的SequenceClientHash表可能是有意义的,因为您可以查找人造键一次并重复使用它.这也可以让您的CI仅在最后一页(yay)添加新行,并大大减少所有非聚集索引(yippee)中重复的CI大小.但是,这在某些狭窄的使用模式中只会有意义.
现在,marc_s建议仅添加一个附加的int identity列作为聚簇索引.这可能有助于使所有非聚簇索引在每页上获得更多行,但这一切都完全取决于您希望执行性能的位置,因为这将保证表上的每一个查询都必须使用书签查找,你永远不会得到一个表寻求.
关于“吨的页面分割和错误的索引碎片”：正如我已经说过的,可以稍微改善一些较低的填充因子.另外,频繁的在线索引重组(与重建不一样)可以帮助减少这种影响.
最终,这一切都归结于确切的系统及其独特的数据访问模式以及您想要优化哪些部件的决策.对于某些系统来说,只要选择总是快速,插入速度较慢就不会坏.对于其他人来说,选择时间一致但稍慢的选择时间比选择时间稍长,但不一致更为重要.对于其他人来说,数据直到被推送到数据仓库才能真正读取,因此插入需要尽可能快.而混合增加的事实是,性能不仅仅是用户等待时间,甚至是查询响应时间,而且关于服务器资源,特别是在大规模并行性的情况下,因此总吞吐量(例如,在每个时间单位的客户端响应中)比任何其他因素更重要.
