代码之家 › 专栏 › 技术社区 › T.J. Crowder

是否在日志记录表中不断增加的日期时间列上对索引进行群集?

indexing sql-server

T.J. Crowder · 技术社区 · 15 年前

我不是DBA( “好!”你马上就会想起来的。 )

我有一个具有这些特征和使用模式的日志记录数据表:

一 datetime 用于存储日志时间戳的列,这些日志时间戳的值不断增加,并且大部分(但仅大部分)是唯一的
频繁的ISH插入(比如说一打一分钟),只在时间戳范围的末尾(记录新数据)
不经常从开始时间戳范围(清除旧数据)
完全没有更新
频繁的ISH选择使用时间戳列作为主标准,以及其他列上的辅助标准。
很少选择使用其他列作为标准(和不包括时间戳列)
大量的数据,但离存储空间还不够近

此外,目前有一个日常维护窗口,在此期间我可以进行表优化。

坦率地说,我不希望这个表会挑战它将要使用的服务器,即使我对它进行了一点索引错误,但是它似乎是一个要求输入SQL Server聚集索引的好机会。

我知道聚集索引决定实际表数据的存储(数据存储在索引本身的叶节点中),而非聚集索引是数据的独立指针。所以在查询术语中,聚集索引要比非聚集索引快——一旦找到索引值,数据就在那里。插入和删除都有成本(当然,更改聚集索引列的值的更新会特别昂贵)。

但我读 in this answer 删除后留下的空白直到/除非重建索引,否则不会被清除。

所有这些都表明我应该:

在时间戳列上使用100%填充因子放置聚集索引
将非聚集索引放在任何其他列上,这些列可用作查询中的条件,而该查询也不涉及聚集列(在我的例子中可能是其中的任何一列)。
安排批量删除在日常维护间隔期间发生
计划在批量删除之后立即重新生成聚集索引
放松,多出去

我疯了吗?我是否需要像那样频繁地重建索引以避免浪费大量空间?对于DBA来说,还有其他我应该做的事情吗?

事先谢谢。

4 回复 | 直到 15 年前

AdaTheDev 15 年前

我同意将聚集索引放在timestamp列上。我的查询是关于填充因子的——100%以牺牲写入性能为代价提供最佳的读取性能。你可能会因分页符而受伤。选择一个较低的填充因子将以牺牲读取性能为代价延迟页面拆分,因此它是一个很好的平衡行为,以获得最佳的适合您的情况。

在批量删除它的值之后,重建索引并更新统计信息。这不仅提高了性能,而且还将索引重置为指定的填充因子。

最后,是的,将非聚集索引放在其他适当的列上,但只放那些非常有选择的列,例如非位字段。但是记住索引越多,对写性能的影响就越大

marc_s 15 年前

与许多人所相信的相反,在表上有一个好的聚集索引实际上可以使插入之类的操作更快——是的,更快!

查看开创性的博客帖子 The Clustered Index Debate Continues.... 金伯利特里普-终极索引女王。

她提到(在文章中间提到):

在群集中插入速度更快表(但仅在“右侧”中) 群集表)与堆。这里的主要问题是在IAM/PFS中查找以确定堆中的插入位置是比聚集表中的慢 (插入位置已知时, 由聚集键定义)。插入物插入表格时速度更快其中定义了订单(cl)和这种秩序在不断增加。

关键是:只有右边当聚集索引是唯一的、狭窄的、稳定的并且以最佳方式不断增加时,聚集索引将能够获得好处。最好使用int identity列。

金伯利特里普也有一篇很好的文章,关于如何为您的表选择尽可能最好的集群密钥,以及它应该满足什么标准——见她的文章标题为 Ever-increasing clustering key - the Clustered Index Debate..........again!

如果您有这样一个列(例如代理主键),请使用它作为集群键,您应该在表中看到非常好的性能,即使在许多插入中也是如此。

Andomar 15 年前

索引高流量日志表有两种“最佳实践”方法:

作为主聚集键的整数标识列
作为主键的唯一标识符列,具有 DEFAULT NEWSEQUENTIALID()

这两种方法都允许SQL Server高效地增长表,因为它知道索引树将以特定的方向增长。

我不会在表上放置任何其他索引,也不会安排索引的重建,除非存在特定的性能问题。

Zachary Scott 15 年前

显而易见的答案是,这取决于您将如何查询它。索引的要点是在选择数据时减少比较的数量。当您考虑将要一起加载的数据和存储的阻塞因子时,聚集索引会有所帮助(一次读取就可以在64K块中加载一组数据)。如果您包含一个ID和一个日期时间作为主键,但在选择条件中不使用它们,那么它们只会妨碍您的性能。这就是为什么人们通常在加载数据之前在大容量插入时删除索引的原因。