主键可以是聚集表上的非聚集索引吗？

Question

我正在设计一个成员表来存储网站的用户。每次用户登录网站时都会使用它，并且偶尔会访问它来更新用户详细信息。

用户将使用电子邮件地址和密码登录，每个帐户都有一个唯一的电子邮件地址。因此，成员表的电子邮件列应该是它的聚集索引似乎是合乎逻辑的，因为当用户登录时，该表上的大多数查询将针对电子邮件列。使电子邮件列唯一并成为聚集索引的键，应该可以在用户登录时快速查询用户数据并提高性能。

但据我了解，将电子邮件列设为主键是错误的，原因有两个。第一，主键应该是不变的，因此如果用户决定更改他们的电子邮件地址，那么所有外键都必须更新，这会很糟糕。其次，电子邮件地址是字符串，这会使连接速度比 PK 为整数时慢。

那么我可以将非聚集索引作为主键吗？那么该表既有一个以 Email 作为唯一键的聚集索引，又有一个 int 主键作为顶部的非聚集索引？

谢谢， 邓肯

Answer 1

主键是一种逻辑数据库设计，只需唯一且非NULL（通过索引实现）。

此外，您还可以选择单个聚集索引，该索引应该是窄的、唯一的、递增的和静态的（电子邮件可能不适合这样做）。

我会创建一个 IDENTITY int 主键并在其上进行集群。

我会在电子邮件上添加一个唯一的非聚集索引并“包含”附加列，以便覆盖您最频繁的繁重查询（即密码哈希）。请注意，您不需要将聚集键添加到包含的列，因为它始终作为书签包含在非聚集索引中）。

查看执行计划以确保您没有在用户表中看到任何表扫描或聚集索引扫描。

我想补充一点，人们通常认为看到查询使用聚集索引是一件好事。我认为，在索引覆盖的查询中使用的非聚集索引扫描或查找在堆（没有聚集索引的表）上与聚集索引上一样好，并且比聚集索引扫描或查找更好。我还认为，聚集索引这个名称会导致人们对事物产生各种假设（首先，它实际上并不是表上的索引，它表明表完全存储在索引结构中）和误解关于它的重要性。聚集索引在非常大的操作中最重要，因为在这些操作中需要按聚集顺序排列大量数据。

典型 OLTP 查询的实际（读取）查询速度来自于在查询中的所有表上使用尽可能窄的非聚集索引覆盖查询，并且每列都按适当的顺序以及查询/参数的正确排序方向。

Answer 2

您绝对可以在主键中创建非聚集索引。

然而，我认为你有点倒退了。
电子邮件地址将形成一个特别糟糕的聚集索引，因为它本质上不是有序的。随着表的增长，由于页面分割、重新排序等，您将失去 INSERT 性能。

正如 @Cade Roux 所说，我会将 autonum 设为聚集索引，强制电子邮件的唯一性地址。

编辑：聚集索引表示数据如何物理存储在磁盘上。非顺序聚集索引会损害 INSERT 性能，因为数据必须重新排序（导致页面拆分）。
对于扫描用户表中的单行，您可能会发现聚集索引和非聚集索引之间的差异可以忽略不计。然而，根据 @gbn 发布的优秀链接，您可能在范围选择上有更好的性能，因为数据是连续的。尽管如此，我个人必须认真考虑是否决定使用字符串（或任何本质上无序的数据）作为聚集索引。

EDIT2：我能想到的一个例外情况是，如果您通常按电子邮件地址按字母顺序选择用户块...您的INSERT仍然会较慢，但是你应该能够更快地检索这些分组...正如 @Cade Roux 在评论中所说：你不应该期望单行 SELECT 的性能更高，因为到聚集索引。

Answer 3

是的，你可以。创建表时，设置列如下：

CREATE TABLE Members
(
  ID INT NOT NULL IDENTITY(10000,1),
  Email Varchar(200) NOT NULL CONSTRAINT pk_Members PRIMARY KEY NONCLUSTERED,
  Otherstuff ...
)

CREATE CLUSTERED INDEX cdx_Members ON Members(ID)