使用 GUID 作为主键的最佳实践是什么，特别是在性能方面?

2021-12-02 00:00:00 sql-server database-design primary-key entity-framework guid

我有一个应用程序，它在几乎所有表中都使用 GUID 作为主键，我读到使用 GUID 作为主键时存在性能问题.老实说，我没有看到任何问题，但我即将开始一个新的应用程序，我仍然想使用 GUID 作为主键，但我正在考虑使用复合主键(GUID 和另一个字段.)

我使用 GUID 是因为当您拥有不同的环境(例如生产"、测试"和开发"数据库以及数据库之间的迁移数据)时，它们很好且易于管理.

我将使用 Entity Framework 4.3，我想在应用程序代码中分配 Guid，然后再将其插入数据库.(即我不想让 SQL 生成 Guid).

创建基于 GUID 的主键的最佳做法是什么，以避免与此方法相关的预期性能下降?

解决方案

GUID 似乎是您的主键的自然选择 - 如果您真的必须，您可能会争论将其用于表的 PRIMARY KEY.我强烈建议不要做的是使用 GUID 列作为群集键，SQL Server 默认情况下会这样做，除非您明确告诉它不要这样做.>

你真的需要把两个问题分开:

  1. 主键 是一种逻辑结构 - 唯一且可靠地标识表中每一行的候选键之一.这可以是任何东西，实际上 - INT、GUID、字符串 - 选择最适合您的场景的内容.

  2. 聚簇键(在表上定义聚簇索引"的一列或多列) - 这是一个物理存储相关的东西，在这里，小型、稳定、不断增加的数据类型是您的最佳选择 - INT 或 BIGINT 作为您的默认选项.

默认情况下，SQL Server 表上的主键也用作集群键 - 但不必如此！在将以前的基于 GUID 的主键/集群键分解为两个单独的键时，我个人看到了巨大的性能提升 - GUID 上的主(逻辑)键和单独 INT IDENTITY 上的集群(排序)键(1,1) 列.

作为 KimberlyTripp - 索引女王 - 和其他人已经说过很多次 - GUID 作为聚类键不是最佳的，因为由于其随机性，它会导致大量页面和索引碎片以及普遍较差的性能.

是的，我知道 - 在 SQL Server 2005 及更高版本中有 newsequentialid() - 但即使这样也不是真正和完全连续的，因此也会遇到与 GUID 相同的问题 - 稍微不那么突出.

然后还有另一个问题需要考虑:表上的聚簇键也将添加到表上每个非聚簇索引的每个条目中 - 因此您真的想确保它尽可能小.通常，具有 2+ 十亿行的 INT 应该足以满足绝大多数表 - 与作为集群键的 GUID 相比，您可以为自己节省数百兆字节磁盘和服务器内存中的存储空间.

快速计算 - 使用 INT 与 GUID 作为主键和聚类键:

  • 具有 1'000'000 行的基表(3.8 MB 与 15.26 MB)
  • 6 个非聚集索引(22.89 MB 与 91.55 MB)

总计:25 MB 对 106 MB - 这只是在一张桌子上！

一些值得深思的食物 - 金伯利·特里普 (Kimberly Tripp) 的优秀作品 - 阅读、再阅读、消化它！这是 SQL Server 索引的福音，真的.

  • GUID 作为PRIMARY KEY 和/或聚集键
  • 聚集索引的争论还在继续
  • 不断增加聚集键 - 聚集索引辩论..........再次！
  • 磁盘空间很便宜 - 不是 重点！

PS:当然，如果您只处理几百或几千行 - 这些论点中的大多数不会对您产生太大影响.但是:如果您进入数万或数十万行，或者您开始​​数以百万计 - 那么这些点就变得非常重要并且非常重要，需要理解.

更新:如果您希望将 PKGUID 列作为主键(但不是集群键)和另一列 MYINT(INT IDENTITY) 作为您的聚类键 - 使用:

创建表 dbo.MyTable(PKGUID 唯一标识符非空，MyINT INT IDENTITY(1,1) 非空，.... 根据需要添加更多列......)更改表 dbo.MyTable添加约束 PK_MyTable主键非聚集(PKGUID)在 dbo.MyTable(MyINT) 上创建唯一的集群索引 CIX_MyTable

基本上:您只需要明确告诉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!

相关文章