提高聚簇索引 GUID 主键的性能
我有一个包含大量行（10K+）的表，它的主键是 GUID。 主键是聚集的。 该表的查询性能相当低。 请提供建议以提高效率。
I've a table with large number of rows (10K+) and it primary key is GUID. The primary key is clustered. The query performance is quite low on this table. Please provide suggestions to make it efficient.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（6）
GUID 上的聚集索引不是一个好的设计。 GUID 的本质在于它是随机的，而聚集索引则通过键对记录进行物理排序。 这两件事是完全矛盾的。 对于每个插入 SQL 都必须重新排序磁盘上的记录！ 从此索引中删除聚类！
使用聚类的时间是当您对数据有“自然”顺序时：插入时间、帐号等。对于时间字段，聚类几乎是免费的。 对于帐号，它可能是免费的或便宜的（当帐号按顺序分配时）。
虽然可能有解决 GUID 问题的技术方法，但最好的想法是了解何时使用集群。
A clustered index on GUID is not a good design. The very nature of GUID is that it's random, while a clustered index physically orders the records by the key. The two things are completely at odds. For every insert SQL has to reorder the records on disk! Remove clustering from this index!
The time to use clustering is when you have a "natural" order to the data: time inserted, account number, etc. For time fields, clustering is almost free. For account number, it might be free or cheap (when account numbers are assigned sequentially).
While there may be technical ways around the GUID issue, the best idea is to understand when to use clustering.
使用GUID作为主键是没有问题的。 只需确保当您实际将 GUID 设置为主键时，然后将其自动创建的索引设置为非聚集类型。 很多人忘记（或不知道）在 SQL Server 中执行此操作。
切勿在 GUID 上使用聚集索引。 这将导致磁盘上的 GUID 周围的物理排序，这显然是没有意义的（正如其他人已经指出的那样）
There is no problem with using a GUID as the primary key. Just make sure that when you actually set the GUID to be the primary key then set the index it automatically creates to be of type Non-clustered. A lot of people forget (or dont know) to do this in SQL Server.
NEVER use a clustered index on a GUID. This will cause a physical ordering around the GUID on disk, which is obviously pointless (as others have already pointed out)
您需要使用 newsequentialid() 代替，请参阅此处 一些要显示的简单代码Newid和Newsequentialid的区别
You need to use newsequentialid() instead see here Some Simple Code To Show The Difference Between Newid And Newsequentialid
您可以尝试顺序GUIDS，这将使索引更有效。 信息此处。
You can try sequential GUIDS, which will make the index more effective. Info here.
您需要分析您的查询。 我们只能猜测为什么您的查询执行得很差，而无需查看执行计划（您可以从 SQL Server 或 Oracle 轻松获得执行计划）。
考虑到 GUID 是 128 位值（如果原始存储），GUID 会将数据和索引块的密度减少多达 50%（在主键索引的情况下），因此请确保 GUID 合适。
但这可能不是问题所在，因此请检查查询计划。 这可能是其他几个问题。
You need to analyze your query. We can only guess why your queries perform badly without viewing the execution plan (which you can get quiet easily from SQL Server or Oracle).
Considering that a GUID is a 128-bit value (if stored raw), a GUID cuts the density of the data and index blocks by as much as 50% (in the case of the primary key index) so make sure GUID is appropriate.
But that might not be the problem, so review the query plan. It could be several other issues.
请避免为长字符串列创建聚集索引。 GUID 将有 36 个字符。 即使您创建了聚集索引，它也会降低查询性能。 为了更好的实践，请使用整数标识列。
Please avoid creating clustered index for lenghty string columns. GUID will have 36 char. It will reduce the query performance even you have created as clustered index. for better practice, use integer identity columns.