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

问题描述

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

我一直在寻找添加外键的地方，并依次索引这些列来提高连接的性能。然而，我们的许多表都加入了一个id，它是一个 GUID 类型，插入一个项目后生成，与其他表中的项目相关的数据通常是包含GUID的列 item_id 。

我已经读过，将聚簇索引添加到GUID类型列是一个非常糟糕的决定，因为索引需要不断重构才能生效。但是，我想知道，在上述场景中使用非聚集索引是否有任何不利之处？还是有理由认为这会有助于表现？我可以提供更多的信息，如果需要的话。

解决方案

< anytype> 是迄今为止您必须改善连接和单例查找的最佳选择。缺乏这个索引查询将永远不得不扫描整个表的端到端（往往）糟糕的性能结果和并发下降。

这是真的那么 uniqueidentifier 由于您提到的原因而使索引选择不当，但绝不意味着您应该创建这些索引。如果可能，将数据类型更改为INT或BIGINT将是明智的。使用 NEWSEQUENTIALID（） 或 UuidCreateSequential 来生成它们将有助于解决碎片问题。如果所有的选择都失败了，你可能不得不比其他索引更频繁地进行索引维护（重建，重组）操作。 但是，这些缺点绝不会超过使用索引的好处！

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

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.

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.

解决方案

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.

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屋！