主键与聚集索引

在今天的文章里我想谈下SQL Server里非常重要的话题，当人们第一次使用SQL Server时是最会混淆的。即主键约束（Primary Key constraint）和聚集索引（Clustered Index）的区别。

什么是主键（Primary Key）

首先让我们谈下主键约束本身。顾名思义它只是个约束，使用这个约束你告诉SQL Server你想在特定列或特定一组列有唯一值。下列代码显示了一个非常简单的表定义，在第1个Col列指定了主键约束。

CREATE TABLE Foo
(
    Col1 INT NOT NULL PRIMARY KEY,
    Col2 INT NOT NULL,
    Col3 INT NOT NULL
)
GO

现在当你往表里插入记录，SQL Server确保在Col列总有唯一值。如果你尝试插入重复值，SQL Server返回错误信息。

-- Try to insert a duplicate value
INSERT INTO Foo Values (1, 1, 1), (1, 2, 2)
GO

主键约束本身在逻辑层定义——你就告诉SQL Server你想在特定列有唯一值。但SQL Server也在物理层强制那个唯一性——你存储你的表数据的数据结构里。在SQL Server情形里，唯一性使用索引结构在屋里层强制执行——使用聚集索引（Clustered Index）或非聚集索引（Non-Clustered Index）。我们来详细看下。

主键约束（Primary Key constraint）的强制执行

当你指定主键约束时，SQL Server在物理层默认通过使用唯一聚集索引（Unique Clustered Index）来强制执行。当你查看sys.indexes时，你会看到SQL Server内部已生成唯一聚集索引来强制执行主键约束。

-- SQL Server generates by default a Unique Clustered Index
SELECT * FROM sys.indexes
WHERE object_id = OBJECT_ID('Foo')
GO

我已经说过，默认是创建唯一聚集索引。你也可以使用如下代码所示的唯一非聚集索引来强制执行主键约束。

-- Enforces the Primary Key constraint with a Unique Non-Clustered Index
CREATE TABLE Foo1
(
    Col1 INT NOT NULL PRIMARY KEY NONCLUSTERED,
    Col2 INT NOT NULL,
    Col3 INT NOT NULL
)
GO

当你指定主键约束时，你可以指定下列2个选项：

• CLUSTERED
• NONCLUSTERED

CLUSTERED选项是默认的，因此你比需要指定它。当你再次查看sys.indexes时，现在你会看到在你面前有一个堆表（heap table）（没有聚集索引定义的表），SQL Server已经生成另外的唯一非聚集索引来强制执行主键约束。

-- SQL Server has generated now a Unique Non-Clustered Index to
-- enforce the Primary Key constraint
SELECT * FROM sys.indexes
WHERE object_id = OBJECT_ID('Foo1')
GO

 

主键不等于聚集索引

因此在SQL Server里并不意味着主键和聚集索引总是一样的。默认是一样的，但你可要修改这个如果你想要的话。主键约束总是在逻辑层，索引结构在是物理层来强制约束本身。

现在的问题是什么时候使用唯一非聚集索引来强制主键约束是有意义的？在上个月我写了一篇自增长的聚集键值不会扩展的文章，里面谈到了所谓的最后页插入闩锁竞争（Last Page Insert Latch Contention）问题：在SQL Server里，像INT IDENTITY列这样的自增长的聚集键列不会扩展（Scale）。

如果你想解决这个问题，或许在随机值上物理聚集/排序你的表数据——像UNIQUEIDENTIFIER列。在这个情况下，你还可以使用在原始的自增长键列上使用主键约束，但它是使用唯一非聚集索引来强制的，在随机键列上聚集你的表。下面代码显示了这个方法。 

-- Create the Primary Key constraint on an ever-increasing
-- key column
CREATE TABLE Foo2
(
    Col1 INT NOT NULL PRIMARY KEY NONCLUSTERED,
    Col2 UNIQUEIDENTIFIER NOT NULL,
    Col3 INT NOT NULL
)
GO

-- Create the Clustered Index on a random key column
CREATE UNIQUE CLUSTERED INDEX ci_Col2 ON Foo2(Col2)
GO

当你再次查看sys.indexes时，现在你会看到你已经创建了聚集和非聚集索引。但只有非聚集索引用来强制主键约束。

-- Now we have a Clustered and Non-Clustered Index
SELECT * FROM sys.indexes
WHERE object_id = OBJECT_ID('Foo2')
GO

 

小结

在SQL Server里，主键约束和聚集索引并不一样的。默认SQL Server使用唯一聚集索引来强制主键约束。但如果你想要的话，你可以使用唯一非聚集索引来代替。但这个方法默认是没有太大意义，因为你需要处理相关问题（最后页插入闩锁竞争）来使用这个方法。

感谢关注！

参考文章：

https://www.sqlpassion.at/archive/2015/06/29/primary-key-vs-clustered-index/