我应该摆脱Guid列上的聚集索引吗 [英] Should I get rid of clustered indexes on Guid columns

问题描述

我正在一个通常使用GUID作为主键的数据库上工作.

I am working on a database that usually uses GUIDs as primary keys.

默认情况下，SQL Server在主键列上放置一个聚集索引.我知道这对于GUID列是一个愚蠢的想法，并且非聚集索引更好.

By default SQL Server places a clustered index on primary key columns. I understand that this is a silly idea for GUID columns, and that non-clustered indexes are better.

您怎么看-我应该摆脱所有聚集索引，并用非聚集索引替换它们吗?

What do you think - should I get rid of all the clustered indexes and replace them with non-clustered indexes?

SQL的性能调节器为什么不提供此建议呢?

Why wouldn't SQL's performance tuner offer this as a recommendation?

推荐答案

聚集索引的一个重要原因是，当您经常想检索给定列的一系列值的行时.由于数据是按物理顺序排列的，因此可以非常有效地提取行.

A big reason for a clustered index is when you often want to retrieve rows for a range of values for a given column. Because the data is physically arranged in that order, the rows can be extracted very efficiently.

像GUID这样的东西虽然对主键非常有用，但可能会对性能产生不利影响，因为插入会增加成本，并且对选择没有明显的好处.

Something like a GUID, while excellent for a primary key, could be positively detrimental to performance, as there will be additional cost for inserts and no perceptible benefit on selects.

是的，不要在GUID上聚集索引.

So yes, don't cluster an index on GUID.

关于为什么不推荐它的原因，我建议调谐器意识到这一事实.

As to why it's not offered as a recommendation, I'd suggest the tuner is aware of this fact.

这篇关于我应该摆脱Guid列上的聚集索引吗的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！