與代理整數鍵相比，自然鍵在 SQL Server 中提供更高還是更低的性能？

我是代理鍵的粉絲。我的發現存在確認偏倚的風險。

我在這里和http://stackoverflow.com看到的許多問題都使用自然鍵而不是基於IDENTITY()值的代理鍵。

我在電腦系統方面的背景告訴我，對整數執行任何比較操作都比比較字元串要快。

這個評論讓我質疑我的信念，所以我想我會創建一個系統來研究我的論點，即整數比字元串更快地用作 SQL Server 中的鍵。

由於小型數據集可能幾乎沒有可辨別的差異，因此我立即想到了兩個表設置，其中主表有 1,000,000 行，輔助表有 10 行，主表中的每一行總共有 10,000,000 行輔助表。我的測試的前提是創建兩組這樣的表，一組使用自然鍵，一組使用整數鍵，並在一個簡單的查詢上執行計時測試，例如：

SELECT *
FROM Table1
   INNER JOIN Table2 ON Table1.Key = Table2.Key;

以下是我作為測試平台創建的程式碼：

USE Master;
IF (SELECT COUNT(database_id) FROM sys.databases d WHERE d.name = 'NaturalKeyTest') = 1
BEGIN
   ALTER DATABASE NaturalKeyTest SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
   DROP DATABASE NaturalKeyTest;
END
GO
CREATE DATABASE NaturalKeyTest 
   ON (NAME = 'NaturalKeyTest', FILENAME = 
       'C:\SQLServer\Data\NaturalKeyTest.mdf', SIZE=8GB, FILEGROWTH=1GB) 
   LOG ON (NAME='NaturalKeyTestLog', FILENAME = 
       'C:\SQLServer\Logs\NaturalKeyTest.mdf', SIZE=256MB, FILEGROWTH=128MB);
GO
ALTER DATABASE NaturalKeyTest SET RECOVERY SIMPLE;
GO
USE NaturalKeyTest;
GO
CREATE VIEW GetRand
AS 
   SELECT RAND() AS RandomNumber;
GO
CREATE FUNCTION RandomString
(
   @StringLength INT
)
RETURNS NVARCHAR(max)
AS
BEGIN
   DECLARE @cnt INT = 0
   DECLARE @str NVARCHAR(MAX) = '';
   DECLARE @RandomNum FLOAT = 0;
   WHILE @cnt < @StringLength
   BEGIN
       SELECT @RandomNum = RandomNumber
       FROM GetRand;
       SET @str = @str + CAST(CHAR((@RandomNum * 64.) + 32) AS NVARCHAR(MAX)); 
       SET @cnt = @cnt + 1;
   END
   RETURN @str;
END;
GO
CREATE TABLE NaturalTable1
(
   NaturalTable1Key NVARCHAR(255) NOT NULL 
       CONSTRAINT PK_NaturalTable1 PRIMARY KEY CLUSTERED 
   , Table1TestData NVARCHAR(255) NOT NULL 
);
CREATE TABLE NaturalTable2
(
   NaturalTable2Key NVARCHAR(255) NOT NULL 
       CONSTRAINT PK_NaturalTable2 PRIMARY KEY CLUSTERED 
   , NaturalTable1Key NVARCHAR(255) NOT NULL 
       CONSTRAINT FK_NaturalTable2_NaturalTable1Key 
       FOREIGN KEY REFERENCES dbo.NaturalTable1 (NaturalTable1Key) 
       ON DELETE CASCADE ON UPDATE CASCADE
   , Table2TestData NVARCHAR(255) NOT NULL  
);
GO

/* insert 1,000,000 rows into NaturalTable1 */
INSERT INTO NaturalTable1 (NaturalTable1Key, Table1TestData) 
   VALUES (dbo.RandomString(25), dbo.RandomString(100));
GO 1000000 

/* insert 10,000,000 rows into NaturalTable2 */
INSERT INTO NaturalTable2 (NaturalTable2Key, NaturalTable1Key, Table2TestData)
SELECT dbo.RandomString(25), T1.NaturalTable1Key, dbo.RandomString(100)
FROM NaturalTable1 T1
GO 10 

CREATE TABLE IDTable1
(
   IDTable1Key INT NOT NULL CONSTRAINT PK_IDTable1 
   PRIMARY KEY CLUSTERED IDENTITY(1,1)
   , Table1TestData NVARCHAR(255) NOT NULL 
   CONSTRAINT DF_IDTable1_TestData DEFAULT dbo.RandomString(100)
);
CREATE TABLE IDTable2
(
   IDTable2Key INT NOT NULL CONSTRAINT PK_IDTable2 
       PRIMARY KEY CLUSTERED IDENTITY(1,1)
   , IDTable1Key INT NOT NULL 
       CONSTRAINT FK_IDTable2_IDTable1Key FOREIGN KEY 
       REFERENCES dbo.IDTable1 (IDTable1Key) 
       ON DELETE CASCADE ON UPDATE CASCADE
   , Table2TestData NVARCHAR(255) NOT NULL 
       CONSTRAINT DF_IDTable2_TestData DEFAULT dbo.RandomString(100)
);
GO
INSERT INTO IDTable1 DEFAULT VALUES;
GO 1000000
INSERT INTO IDTable2 (IDTable1Key)
SELECT T1.IDTable1Key
FROM IDTable1 T1
GO 10

上面的程式碼創建了一個數據庫和 4 個表，並用數據填充表，準備測試。我執行的測試程式碼是：

USE NaturalKeyTest;
GO
DECLARE @loops INT = 0;
DECLARE @MaxLoops INT = 10;
DECLARE @Results TABLE (
   FinishedAt DATETIME DEFAULT (GETDATE())
   , KeyType NVARCHAR(255)
   , ElapsedTime FLOAT
);
WHILE @loops < @MaxLoops
BEGIN
   DBCC FREEPROCCACHE;
   DBCC FREESESSIONCACHE;
   DBCC FREESYSTEMCACHE ('ALL');
   DBCC DROPCLEANBUFFERS;
   WAITFOR DELAY '00:00:05';
   DECLARE @start DATETIME = GETDATE();
   DECLARE @end DATETIME;
   DECLARE @count INT;
   SELECT @count = COUNT(*) 
   FROM dbo.NaturalTable1 T1
       INNER JOIN dbo.NaturalTable2 T2 ON T1.NaturalTable1Key = T2.NaturalTable1Key;
   SET @end = GETDATE();
   INSERT INTO @Results (KeyType, ElapsedTime)
   SELECT 'Natural PK' AS KeyType, CAST((@end - @start) AS FLOAT) AS ElapsedTime;

   DBCC FREEPROCCACHE;
   DBCC FREESESSIONCACHE;
   DBCC FREESYSTEMCACHE ('ALL');
   DBCC DROPCLEANBUFFERS;
   WAITFOR DELAY '00:00:05';
   SET @start = GETDATE();
   SELECT @count = COUNT(*) 
   FROM dbo.IDTable1 T1
       INNER JOIN dbo.IDTable2 T2 ON T1.IDTable1Key = T2.IDTable1Key;
   SET @end = GETDATE();
   INSERT INTO @Results (KeyType, ElapsedTime)
   SELECT 'IDENTITY() PK' AS KeyType, CAST((@end - @start) AS FLOAT) AS ElapsedTime;

   SET @loops = @loops + 1;
END
SELECT KeyType, FORMAT(CAST(AVG(ElapsedTime) AS DATETIME), 'HH:mm:ss.fff') AS AvgTime 
FROM @Results
GROUP BY KeyType;

這些是結果：

我在這裡做錯了什麼，還是 INT 鍵比 25 個字元的自然鍵快 3 倍？

注意，我在這裡寫了一個後續問題。

通常，SQL Server 使用B+Trees作為索引。索引查找的成本與此儲存格式中鍵的長度直接相關。因此，代理鍵在索引搜尋中的性能通常優於自然鍵。

預設情況下，SQL Server 在主鍵上聚集一個表。聚集索引鍵用於標識行，因此它作為包含列添加到每個其他索引。該鍵越寬，每個二級索引就越大。

更糟糕的是，如果二級索引沒有明確定義為UNIQUE聚集索引鍵，則會自動成為其中每個鍵的一部分。這通常適用於大多數索引，因為通常只有在要求強制唯一性時才將索引聲明為唯一。

因此，如果問題是自然與代理聚集索引，代理幾乎總是會獲勝。

另一方面，您將該代理列添加到表中，使表本身更大。這將導致聚集索引掃描變得更加昂貴。因此，如果您只有很少的二級索引並且您的工作負載需要經常查看所有（或大部分）行，那麼您實際上可能會更好地使用自然鍵來節省這幾個額外的字節。

最後，自然鍵通常更容易理解數據模型。在使用更多儲存空間的同時，自然主鍵會導致自然外鍵，從而增加本地資訊密度。

因此，在數據庫世界中，真正的答案是“視情況而定”。並且 - 始終使用真實數據在您自己的環境中進行測試。

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