出于插入性能考虑,时间戳上的聚集索引应该是升序还是降序?
我刚刚意识到我在时间戳上有一个按降序排列的聚集索引。我正在考虑将其切换为升序,以便在插入新的、不断增加的时间戳时,将它们添加到表的末尾。就目前情况而言,我怀疑它必须将行添加到表的开头,并且我想知道 SQL Server 如何处理它。
是否可以在表的开头有效地分配新页,并有效地将新行插入到这些页中,或者更好地按时间戳顺序填充页并使用升序聚集索引在末尾分配新页。
I just realized I have a clustered index on a Timestamp in descending order. I'm thinking about switching it to ascending, so that as new, ever-increasing timestamps are inserted, they are added to the end of the table. As it stands now, I suspect it has to add rows to the beginning of the table, and I wonder how SQL Server handles that.
Can it efficiently allocate new pages at the beginning of the table, and efficiently insert new rows into those pages, or would it be better filling up pages in the order of the timestamps and allocating new pages at the end with an ascending clustered index.
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
![扫码二维码加入Web技术交流群](/public/img/jiaqun_03.jpg)
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(1)
其实无论是在开头添加还是在结尾添加都是一样的。
页面填满、页面拆分、分配新页面...
新页面可能是连续的,也可能不是连续的,无论是在开头还是结尾。这就是您定期运行 ALTER INDEX 等的原因。
在实践中,聚集索引的 ASC/DEC 顺序对于 SELECT/ORDER BY 更为重要...尽管我在 SQL Server 2005 及更高版本中注意到这一点较少。
It's actually the same whether you add at start or end.
Page fills up, page splits, new page is allocated...
The new page may or not be contiguous whether it's at the start or the end.. which is why you run ALTER INDEX etc regularly.
The ASC/DEC order of the clustered index will matter more for SELECT/ORDER BY in practice... although I've noticed this less in SQL Server 2005 and above.