使用GUID作为主键的最佳做法是什么，具体涉及性能？ [英] What are the best practices for using a GUID as a primary key, specifically regarding performance?

问题描述

我有几个应用程序使用GUID作为主键几乎所有的表，我已经看到，当使用GUID作为主键时，有关于性能的问题。老实说，我没有看到任何问题，但我即将开始一个新的应用程序，我仍然希望使用GUID作为主键，但我正在考虑使用复合主键（GUID，也可能是另一个字段。）

我正在使用GUID，因为当您有不同的环境，如生产，测试和开发数据库时，它们很好，易于管理，以及数据库之间的迁移数据。

我将使用Entity Framework 4.3，并在应用程序代码中将Guid插入数据库之前分配Guid。 （即我不想让SQL生成Guid）。

创建基于GUID的主键的最佳做法是什么，以避免与此方法相关的假设性能匹配？

解决方案

GUID可能似乎是您的主键的自然选择 - 如果你真的必须，你可能会争辩将它用于表的PRIMARY KEY。我强烈建议不做使用GUID列作为集群密钥，默认情况下，SQL Server将执行此操作，除非您不具体告知它。 p>

你真的需要分开两个问题：

1. >主键是一个逻辑结构 - 唯一可靠地标识表中每一行的候选键之一。这可以是任何事情，真的 - 一个 INT ，一个 GUID ，一个字符串 - 选择最适合你的场景




2. 聚簇键（定义表中聚簇索引的列或列） - 这是一个物理存储相关的东西，而这里，一个小而稳定的，不断增长的数据类型是你最好的选择 - INT 或 BIGINT 作为您的默认选项。

默认情况下，SQL Server表上的主键也用作聚类键 - 但是， t需要这样！在将以前的基于GUID的主/集群密钥分解成两个单独的密钥（GUID中的主（逻辑））以及单独的 INT IDENTITY（1,1）列。

As Kimberly Tripp - 索引的女王 - 和其他人已经说了很多次 - 一个 GUID 作为聚类关键是不是最佳的，因为由于它的随机性，它将导致大量的页面和索引碎片，并且一般表现不佳。

是的，我知道 - 有code> newsequentialid（）在SQL Server 2005和更高版本 - 但即使这不是真正和完全顺序的，因此也遇到与 GUID - 稍微不那么突出。

然后还有另外一个问题要考虑：表上的聚类键将被添加到每个条目上的每个条目以及您桌子上的每个非聚集索引，因此您真的想确保它尽可能的小。通常，对于绝大多数表，具有2亿多行的 INT 应该足以满足大多数表 - 而且与 GUID 作为集群密钥，您可以在磁盘和服务器内存中节省数百兆字节的存储空间。

快速计算 - 使用 INT vs. GUID as主要和聚集密钥：

• 具有1'000'000行的基表（3.8 MB对比15.26 MB）


• 6个非聚簇索引（22.89 MB对91.55 MB）

总计：25 MB vs. 106 MB - 这只是一张桌子！

更多的食物为思想 - 金佰利Tripp的优秀的东西 - 阅读它，再次阅读，消化！这是SQL Server索引福音，真的。

• GUID


• 聚集索引辩论继续


• 不断增加的聚类密钥 - 聚集索引辩论..........再次！


• 磁盘空间便宜 - 这不是点！

PS：当然，如果你只处理几百或几千行 - 大多数这些参数都不会真的对你有很大的影响。但是，如果您进入数十或数十万行，或者您开始​​以百万计算 - 然后，这些点变得非常重要，非常重要。

更新：如果您希望将 PKGUID 列作为主键（而不是您的集群键），另一个列 MYINT （ INT IDENTITY ）作为您的群集键 - 使用此：

  CREATE TABLE dbo.MyTable 
（PKGUID UNIQUEIDENTIFIER NOT NULL，
 MyINT INT IDENTITY（1,1）NOT NULL，
 .. ..根据需要添加更多列......）
 
 ALTER TABLE dbo.MyTable 
 ADD CONSTRAINT PK_MyTable 
 PRIMARY KEY NONCLUSTERED（PKGUID）
 
 CREATE UNIQUE CLUSTERED INDEX CIX_MyTable ON dbo.MyTable（MyINT）
  

基本上：你只需要明确告诉 PRIMARY KEY 约束，它的 NONCLUSTERED （否则，它被创建为聚簇索引，默认情况下） - 然后您创建一个第二个索引定义为 CLUSTERED

这将工作 - 这是一个有效的选项，如果你有一个现有的系统需要重新设计的性能。对于一个新的系统，如果从头开始，并且不在复制场景中，那么我总是会选择 ID INT IDENTITY（1,1）作为我的集群主键 - 比其他任何东西更有效率！

I have an application that uses GUID as the Primary Key in almost all tables and I have read that there are issues about performance when using GUID as Primary Key. Honestly, I haven't seen any problem, but I'm about to start a new application and I still want to use the GUIDs as the Primary Keys, but I was thinking of using a Composite Primary Key (The GUID and maybe another field.)

I'm using a GUID because they are nice and easy to manage when you have different environments such as "production", "test" and "dev" databases, and also for migration data between databases.

I will use Entity Framework 4.3 and I want to assign the Guid in the application code, before inserting it in the database. (i.e. I don't want to let SQL generate the Guid).

What is the best practice for creating GUID-based Primary Keys, in order to avoid the supposed performance hits associated with this approach?

解决方案

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:

1. 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, a GUID, a string - pick what makes most sense for your scenario.

2. 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.

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 - 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.

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.

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.

Quick calculation - using INT vs. GUID as Primary and Clustering Key:

• Base Table with 1'000'000 rows (3.8 MB vs. 15.26 MB)
• 6 nonclustered indexes (22.89 MB vs. 91.55 MB)

TOTAL: 25 MB vs. 106 MB - and that's just on a single table!

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!

PS: of course, if you're dealing with just a few hundred or a few thousand rows - most of these arguments won't really have much of an impact on you. However: if you get into the tens or hundreds of thousands of rows, or you start counting in millions - then those points become very crucial and very important to understand.

Update: if you want to have your PKGUID column as your primary key (but not your clustering key), and another column MYINT (INT IDENTITY) as your clustering key - use this:

CREATE TABLE dbo.MyTable
(PKGUID UNIQUEIDENTIFIER NOT NULL,
 MyINT INT IDENTITY(1,1) NOT NULL,
 .... add more columns as needed ...... )

ALTER TABLE dbo.MyTable
ADD CONSTRAINT PK_MyTable
PRIMARY KEY NONCLUSTERED (PKGUID)

CREATE UNIQUE CLUSTERED INDEX CIX_MyTable ON dbo.MyTable(MyINT)

Basically: you just have to explicitly tell the PRIMARY KEY constraint that it's NONCLUSTERED (otherwise it's created as your clustered index, by default) - and then you create a second index that's defined as CLUSTERED

This will work - and it's a valid option if you have an existing system that needs to be "re-engineered" for performance. For a new system, if you start from scratch, and you're not in a replication scenario, then I'd always pick ID INT IDENTITY(1,1) as my clustered primary key - much more efficient than anything else!

这篇关于使用GUID作为主键的最佳做法是什么，具体涉及性能？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！