在 SQL Server 中的 guid 类型列上使用非聚集索引 [英] Use of non-clustered index on guid type column in SQL Server

问题描述

我想优化我的团队用于应用程序的数据库的性能.

I would like optimize the performance of a database that my team is using for an application.

我一直在寻找可以添加外键的区域，然后对这些列进行索引以提高连接的性能.但是，我们的许多表都连接在 GUID 类型的 id 上，在插入项目时生成，并且在其他表中与该项目关联的数据通常具有列 item_id 包含 GUID.

I have been looking for areas to add foreign keys, and in turn index those columns to improve the performance of joins. However, many of our tables are joined on an id that is a GUID type, generated upon insertion of an item, and the data associated with that item in other tables is generally has column item_id containing the GUID.

我已经读过，将聚集索引添加到 GUID 类型的列是一个非常糟糕的决定，因为索引需要不断地重建才能有效.但是，我想知道，在上述场景中使用非聚集索引是否有任何损害?或者假设它会有助于提高性能是否合理?如果需要，我可以提供更多信息.

I have read that adding clustered indexes to GUID type columns is a very bad decision because the index will need to be constantly reconstructed in order to be effective. However, I was wondering, is there any detriment to utilizing a non-clustered index in the scenario described above? Or is it reasonable to assume that it would help performance? I can provide more information if needed.

推荐答案

<anytype> 上的索引是迄今为止改进连接和单例查找的最佳选择.缺少此索引，查询将始终不得不端到端扫描整个表，而性能结果(通常)非常糟糕，并发性会被淘汰.

An index on a <anytype> is by far the best option you have to improve joins and singleton lookups. Lacking this index the query will always have to scan the entire table end-to-end with (often) abysmal performance results and concurrency gone down the drain.

确实，出于您提到的原因，uniqueidentifier 对索引的选择不佳，但这绝不意味着您应该不创建这些索引.如果可能，建议将数据类型更改为 INT 或 BIGINT.使用 NEWSEQUENTIALID() 或 UuidCreateSequential 生成它们将有助于解决碎片问题.如果所有替代方案都失败了，您可能需要比其他索引更频繁地执行索引维护(重建、重组)操作.但是，这些缺点中的任何一个都绝不会超过首先拥有索引的好处！

It is true that uniqueidentifier makes poor choice for indexes for the reasons you mention, but by no means does that implies that you should not create these indexes. Changing the data type to INT or BIGINT would be advisable, if possible. Using NEWSEQUENTIALID() or UuidCreateSequential to generate them would help with fragmentation issues. If all alternatives fail you may have to do index maintenance (Rebuild, reorganize) operations more often than for other indexes. But by no means do any of these drawbacks outweigh the benefit of having the index in the first place!

这篇关于在 SQL Server 中的 guid 类型列上使用非聚集索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！