Sql-Server

非聚集主鍵和覆蓋索引在性能方面的比較

  • December 9, 2020

我有一個包含非聚集主鍵的表。我打算刪除這個主鍵並在同一列上創建一個唯一的覆蓋索引。

那麼該表將沒有主鍵,而是一個唯一的覆蓋索引。

我搜尋了Google,但找不到相關主題。這從根本上是錯誤的嗎?或者沒關係。

更新:

為什麼我需要將此索引轉換為覆蓋索引?

因為這是主鍵,我有很多基於這個鍵的選擇查詢。目前,對於每個選擇,都需要 RID 查找,因為這是一個聚集鍵。如果我將它轉換為覆蓋索引,那麼對於那些選擇查詢,這個 RID 查找將消失,希望會有更好的性能(至少執行計劃是這樣說的)。

當然,完整性也是意料之中的,這就是我將其設為唯一覆蓋索引的原因。主鍵是 a uniqueidentifier,由應用程序生成(這解釋了為什麼我們不將其設為集群)..

我想知道的是,這是否會對其他任何事情產生負面影響。

之前的執行計劃:

在此處輸入圖像描述

之後的執行計劃:

在此處輸入圖像描述

僅供參考,主鍵是uniquidentifier在程式碼(應用程序)中生成的。在負載測試中,我們得出了使其成為非集群的結論。

通過刪除主鍵並添加具有更多列的唯一覆蓋索引,您從根本上改變了表接受數據的方式。可能是由於覆蓋索引到位,表格設計不再符合其設計目的。

舉個例子:

USE tempdb;

IF OBJECT_ID(N'dbo.T1', N'U') IS NOT NULL
DROP TABLE dbo.T1;
CREATE TABLE dbo.T1
(
   ID1 int NOT NULL
   , ID2 int NOT NULL
   , SomeDate datetime2(7) NOT NULL
   , CONSTRAINT PK_T1
       PRIMARY KEY NONCLUSTERED
       (ID1, ID2)
);

INSERT INTO dbo.T1 (ID1, ID2, SomeDate)
VALUES (1, 1, GETDATE())
WAITFOR DELAY '00:00:00.003';
INSERT INTO dbo.T1 (ID1, ID2, SomeDate)
VALUES (1, 1, GETDATE());

輸出:

(1 行受影響)

消息 2627,級別 14,狀態 1,第 18 行

違反主鍵約束“PK_T1”。無法在對象“dbo.T1”中插入重複鍵。重複鍵值為 (1, 1)。

該語句已終止。

表設計只允許元組(ID1, ID2)唯一的行。

然後看這個,主鍵不存在的地方,已經換成了更廣泛的唯一覆蓋索引:

IF OBJECT_ID(N'dbo.T2', N'U') IS NOT NULL
DROP TABLE dbo.T2;
CREATE TABLE dbo.T2
(
   ID1 int NOT NULL
   , ID2 int NOT NULL
   , SomeDate datetime NOT NULL
);

CREATE UNIQUE INDEX IX_T2
ON dbo.T2 (ID1, ID2, SomeDate);

INSERT INTO dbo.T2 (ID1, ID2, SomeDate)
VALUES (1, 1, GETDATE())
WAITFOR DELAY '00:00:00.003';
INSERT INTO dbo.T2 (ID1, ID2, SomeDate)
VALUES (1, 1, GETDATE());

(1 行受影響)

(1 行受影響)

T2有兩行,可以說,第二次插入應該失敗,表應該只有一行。*在這個設計中,*表現在接受元組不一定唯一的(ID1, ID2)行。

我會仔細考慮您的表設計,並且可能只添加覆蓋索引而不刪除主鍵。

您可以將主鍵預設更改為 newsequentiaid(),然後將非集群 pk 替換為集群 pk。這解決了非順序增加的主鍵問題,同時保留唯一標識符作為主鍵列類型。

與聚集鍵解決方案相比,最大的缺點(只要您注意唯一索引邏輯)是創建覆蓋非聚集索引需要您儲存兩次表數據。插入和更新也必須同時維護兩個副本,從而影響性能。額外包含的列儲存在非聚集索引的葉頁以及原始表中。它們使用與附加鍵列相同的空間,但在鍵的搜尋和其他操作期間不考慮(有關詳細資訊,請參閱內容)。

使用聚集主鍵,表數據的單個副本與聚集鍵一起按索引順序儲存(因此沒有鍵查找)。

引用自:https://dba.stackexchange.com/questions/181876