具有唯一標識符的表上的主鍵選擇

我正在重新考慮我在表格上選擇的主鍵，並且想要一些輸入。

一些上下文：我們正在建構一個 REST API，客戶端訪問由 GUID 標識的“資產”。因此，我們數據庫中的 Asset 表如下所示：

CREATE TABLE [dbo].[Asset](
   [Id] [uniqueidentifier] NOT NULL,
   [TypeId] [int] NOT NULL,
   [Date] [datetime] NOT NULL,
   [Title] [varchar](1000) NULL,
   [Description] [varchar](max) NULL,
   [Created] [datetime] NOT NULL,
   [Modified] [datetime] NOT NULL,
   [PublisherFields] [xml] NULL,
   [StatusId] [int] NOT NULL,
CONSTRAINT [PK_Asset] PRIMARY KEY CLUSTERED 
(
   [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)

我們遇到了這種結構的一些問題，特別是表上的聚集索引很快就會變得碎片化，我們現在已經到了無法重建它的地步（因為 Azure 對查詢時間的限制）。

進一步的研究表明，使用 uniqueidentifier 作為聚集索引並不總是一個好的選擇，因為 newid() 不會按順序生成 ID（除非您使用 newsequentialid()，但 Azure 也不允許這樣做） .

簡單的解決方案似乎是引入一個虛擬列並將聚集索引添加到該列。設計指南說好的聚集索引應該是順序的和不變的，所以最簡單的答案似乎是一個標識列。這將使我的表變成這樣：

CREATE TABLE [dbo].[Asset](
       [Id] [bigint] IDENTITY(1,1), <---- NEW
       [AssetId] [uniqueidentifier] NOT NULL, <---- Renamed
       [TypeId] [int] NOT NULL,
       [Date] [datetime] NOT NULL,
       [Title] [varchar](1000) NULL,
       [Description] [varchar](max) NULL,
       [Created] [datetime] NOT NULL,
       [Modified] [datetime] NOT NULL,
       [PublisherFields] [xml] NULL,
       [StatusId] [int] NOT NULL,
    CONSTRAINT [PK_Asset] PRIMARY KEY NONCLUSTERED <--- Now NonClustered
   (
       [Id] ASC
   )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
   )

聚集索引也將添加到ID列中。這似乎是一個明智的選擇嗎？我可能會質疑在IDENTITY表上有一列不是主鍵的意義，但我的理由是，IDENTITY列用於獲得適合聚集索引的自動遞增性質。這也可能令人困惑……當有人引用“AssetID”時，他們在談論哪一列？[ AssetId] 列，還是資產表的 [ ID] 列？

歡迎提出建議和替代方案。

這裡有幾點需要考慮：

1. 這張表中最常查找的數據是怎樣的？
2. 該表中的數據最常如何排序？
3. 此表是否作為父記錄與其他表相關（即其他表是否會 FK 到此表的 PK）？

另外，請記住：

1. 聚集索引的關鍵欄位被複製到同一張表的非聚集索引中
2. 對於不是主鍵（隱含唯一）或至少聲明為的聚集索引，UNIQUE隱藏的“唯一性”欄位將添加到否則會重複的行中。
3. NEWSEQUENTIALID()每次重新啟動 SQL Server 服務時都是連續的。重啟後的起始值可能小於之前的最低值。

因此：

• 如果此表的 PK 欄位在其他表中顯示為 FK 欄位，則選擇使用 aUNIQUEIDENTIFIER而不是 an對性能有一定的影響，INT因為 FKed 表將在其中具有更大的 FK 欄位。
• 您真正希望在此表中有多少行？INT 為 4 個字節（與 BIGINT 的 8 個字節相比），最大值為 2,147,483,647。如果您可能有略多於 21.4 億個項目，您還可以從每個數據類型的最小值開始 IDENTITY 範圍，對於 INT，該最小值為 -2,147,483,648。從低端開始，您可以使用全部 42.94 億個值。如果與Created欄位一起使用，則與 DATETIME 欄位的 8 個字節相比，加上另一個欄位的大小以使其唯一，或任何重複行的唯一符。
• 由於聚集索引的關鍵欄位包含在非聚集索引中，這增加了在不需要INCLUDE其他列的情況下擁有覆蓋索引的機會。意思是，如果您在 INT PK 上有聚集索引，在 UNIQUEIDENTIFIER 上有一個非聚集索引，那麼在 WHERE 子句中指定 GUID 值的同時加入到該 INT PK 欄位上的另一個表（假設此表中沒有其他欄位在查詢）不必返回表，因為非聚集索引將包含兩個必需的欄位。該Created領域是否提供相同的好處？可能不會。

如果沒有其他表加入此表：

• 那麼將Created欄位用作非唯一聚集索引並將Id欄位用作非聚集 PK 可能是可以的。
• ELSE 通常最好添加一個 INT（除非您需要超過 42.94 億個值）IDENTITY 欄位，AssetId作為聚群 PK，並將IdUNIQUEIDENTIFIER 欄位作為非聚群索引。由於您可能已經有將 UNIQUEIDENTIFIER 欄位引用為的程式碼Id，因此我不會更改該名稱。

由於您的 API 是圍繞作為這些行的鍵的 UUID 建構的，所以此時您將其作為您的功能主鍵，儘管這本身並不是一件壞事。儘管由於傳入值的隨機性，不建議在 UUID 列上進行集群 - 正如您所注意到的，這會導致集群索引上出現大量頁面拆分和碎片。

您建議添加一個新的遞增整數鍵作為聚集索引可以正常工作，但我不會將其作為主鍵，因為從功能上講 UUID 仍然是主鍵。保持數據層和應用程序層在此同步將避免以後潛在的混淆。將 UUID 保留為 PK 還可以避免您必須更改與該表具有外鍵關係的所有表（以及與這些表互動的程式碼）以考慮鍵中的新數據類型。

以這種方式為聚集鍵添加新整數值本質上是模擬基於堆的表的 RID（沒有聚集索引的表），儘管它小於 RID 或您的 UUID（RID 為 4 個字節而不是 8 個字節或 16 用於 UUID），假設您堅持使用 INT 而不是 BIGINT，因此將使用此鍵和表中的每個非聚集索引（具有聚集鍵的表上的非聚集索引包含聚集每行的鍵值，因此通過從 UUID 到集群鍵的 INT，您可以為每個索引的每行節省 12 個字節）。這並不意味著你應該總是使用 INT 作為您的聚集鍵，因為除了索引大小之外，還有其他一些考慮因素會影響任何給定表的聚集鍵的最佳選擇（例如：如果您的真實數據中有一個候選鍵可能會受到範圍的影響查詢通常是最好的選擇，但並非總是如此）。

引用自：https://dba.stackexchange.com/questions/80964