GUID性能 [英] GUID Performance
问题描述
我们正在开发一个非常大的OLTP数据库（SQL Server 2012），并考虑使用GUID作为主键（我记住不要使其成为集群），但是我们不确定后果是特殊的性能。我们首先使用EF代码。
We are developing a very large OLTP Database (SQL server 2012) and considering the use of GUID as primary key (I'm keeping in mind not to make it clustered), but, we are not sure of the consequences specially performance. We are using EF code first.
有人可以帮我们决定吗？请附上文章链接。感谢
Can someone please help us decide? please include links to articles. Thanks
推荐答案
GUID 似乎是您自己的主键 - 如果你真的必须，你可能会争辩将它用于表的PRIMARY KEY。强烈建议不要使用 GUID 列作为集群密钥，SQL Server会默认情况下，除非你明确告诉它不要。
GUIDs may seem to be a natural choice for your primary key - and if you really must, you could probably argue to use it for the PRIMARY KEY of the table. What I'd strongly recommend not to do is use the GUID column as the clustering key, which SQL Server does by default, unless you specifically tell it not to.
你真的需要分开两个问题：
You really need to keep two issues apart:
-
主键是一个逻辑结构 - 唯一可靠地标识表中每一行的候选键之一。这可以是任何事情，真的 - 一个
INT，一个GUID，一个字符串 - 选择最适合你的场景
the primary key is a logical construct - one of the candidate keys that uniquely and reliably identifies every row in your table. This can be anything, really - an
INT, aGUID, a string - pick what makes most sense for your scenario.
聚簇键（定义表上的聚簇索引的列或列） - 这是一个与物理存储相关的东西，在这里，一个小而稳定的，不断增长的数据类型是你最好的选择 - INT 或 BIGINT 作为您的默认选项。
the clustering key (the column or columns that define the clustered index on the table) - this is a physical storage-related thing, and here, a small, stable, ever-increasing data type is your best pick - INT or BIGINT as your default option.
默认情况下，SQL Server表上的主键也用作聚类键 - 但是， t需要这样！在将以前的基于GUID的主/集群密钥分解成两个单独的密钥（GUID中的主（逻辑））以及单独的
By default, the primary key on a SQL Server table is also used as the clustering key - but that doesn't need to be that way! I've personally seen massive performance gains when breaking up the previous GUID-based Primary / Clustered Key into two separate key - the primary (logical) key on the GUID, and the clustering (ordering) key on a separate INT IDENTITY(1,1) column.
As Kimberly Tripp - 索引的女王 - 和其他人已经说了很多次 - 一个GUID作为聚类键不是最佳的，因为由于它的随机性，它将导致大量页面和索引碎片，并且性能普遍不佳。
As Kimberly Tripp - the Queen of Indexing - and others have stated a great many times - a GUID as the clustering key isn't optimal, since due to its randomness, it will lead to massive page and index fragmentation and to generally bad performance.
是的，我知道 - 有$ code> newsequentialid（）在SQL Server 2005和更高版本 - 但即使这不是真正和完全顺序的，因此也受到与GUID相同的问题 - 只是稍微不那么突出。
Yes, I know - there's newsequentialid() in SQL Server 2005 and up - but even that is not truly and fully sequential and thus also suffers from the same problems as the GUID - just a bit less prominently so.
那么还有一个需要考虑的问题：表上的聚集键将被添加到表上每个非聚集索引的每一个条目上，因此你真的想确保它尽可能的小。通常，对于绝大多数表，具有2-10亿行的INT应该足够，并且与作为集群密钥的GUID相比，可以在磁盘和服务器内存中节省数百兆字节的存储空间。
Then there's another issue to consider: the clustering key on a table will be added to each and every entry on each and every non-clustered index on your table as well - thus you really want to make sure it's as small as possible. Typically, an INT with 2+ billion rows should be sufficient for the vast majority of tables - and compared to a GUID as the clustering key, you can save yourself hundreds of megabytes of storage on disk and in server memory.
快速计算 - 使用 INT 与GUID作为主要和聚集密钥：
Quick calculation - using INT vs. GUID as Primary and Clustering Key:
- 具有1'000'000行（3.8 MB对15.26 MB）的基本表格
- 6个非聚集索引（22.89 MB vs. 91.55 MB）
TOTAL：25 MB vs. 106 MB - 这只是在一张桌子上！
TOTAL: 25 MB vs. 106 MB - and that's just on a single table!
有些更多的食物为思想 - 金佰利Tripp优秀的东西 - 读它，再读一遍，消化！这是SQL Server索引福音，真的。
Some more food for thought - excellent stuff by Kimberly Tripp - read it, read it again, digest it! It's the SQL Server indexing gospel, really.
- GUIDs as PRIMARY KEY and/or clustered key
- The clustered index debate continues
- Ever-increasing clustering key - the Clustered Index Debate..........again!
- Disk space is cheap - that's not the point!
这篇关于GUID性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
