id 列或聚集主键/数据库一致性

Question

如果我有一个包含以下列的表：

• 艺术家
• 专辑
• 歌曲
• NumberOfListens

...最好在艺术家、专辑和歌曲上放置一个聚集主键，或者有一个自动增量 id 列并对艺术家、专辑和歌曲放置唯一约束歌曲。

数据库一致性有多重要？如果我的一半表具有聚集主键，另一半是具有唯一约束的 id 列，那么这很糟糕还是无关紧要？两种方式对我来说似乎都是一样的，但我不知道行业标准是什么，或者哪种更好以及为什么。

Answer 1

我永远不会在长文本列上放置主键，例如：艺术家、专辑和歌曲。使用作为集群 PK 的自动增量 ID。如果您希望艺术家、专辑和歌曲是唯一的，请在这三者上添加唯一索引。如果您想按专辑或歌曲进行搜索，而不依赖于独立的艺术家，您将需要为每个专辑或歌曲提供一个索引，这会拉动 PK，因此拥有较小的 PK 可以节省您在每个其他索引上的时间。节省的不仅仅是磁盘空间，还包括内存缓存以及页面上的更多键。

Answer 2

您确实需要区分两个问题：

1）主键是一个逻辑构造 - 唯一且可靠地标识表中每一行的候选键之一。这实际上可以是任何东西——一个 INT、一个 GUID、一个字符串——选择对你的场景最有意义的。您在外键约束中引用主键，因此这些对于数据库的完整性至关重要。永远使用它们。

2）聚集键（定义表上“聚集索引”的一列或多列） - 这是一个物理与存储相关的东西，这里是一个小的，独特、稳定、不断增加的数据类型是您的最佳选择 - INT 或 BIGINT 作为您的默认选项。

默认情况下，SQL Server 表上的主键也用作集群键 - 但不一定如此，您可以轻松选择不是主键的列作为集群键。

然后还有另一个问题需要考虑：表上的聚集键也将添加到表上每个非聚集索引的每个条目中 - 因此您确实希望确保它尽可能小。通常，具有 2+ 十亿行的 INT 对于绝大多数表来说应该足够了 - 与 VARCHAR(20) 左右作为集群键相比，您可以在磁盘和服务器内存中节省数百兆字节的存储空间。

还有一些值得深思的东西 - Kimberly Tripp 写的很棒的东西 - 读它，再读它，消化它！这确实是 SQL Server 索引的福音。

• GUID 作为主键和/或聚集键
• 聚集索引辩论仍在继续
• 不断增加的聚类键 - 聚类索引争论......再次！

Marc

Answer 3

聚集索引非常适合基于范围的查询。例如，日志日期或订单日期。当您插入新行时，在“艺术家”、“专辑”和“歌曲”上放置一个[可能]会导致碎片。

如果您的数据库支持它，请在艺术家、专辑和歌曲上添加非聚集主键并称其为良好。或者只是在艺术家、专辑和歌曲上添加唯一的键。

仅当您必须具有对另一个表的引用完整性时，拥有自动增量主键才会真正有用。

Answer 4

在不知道确切要求的情况下，通常您可能会有一个艺术家表，也可能有专辑表。歌曲表将是艺术家 ID、专辑 ID 和歌曲的唯一组合。我将根据应用程序通过索引或约束强制执行唯一性，并使用 id 作为主键。

Answer 5

首先，这里已经存在一个问题，因为数据没有标准化。应尽可能避免在一堆文本列上创建任何类型的索引。即使这些列不是文本（我怀疑它们是），将艺术家、专辑和歌曲放在同一个表中仍然没有意义。一个更好的设计是：

Artists (
    ArtistID int NOT NULL IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
    ArtistName varchar(100) NOT NULL)

Albums (
    AlbumID int NOT NULL IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
    ArtistID int NOT NULL,
    AlbumName varchar(100) NOT NULL,
    CONSTRAINT FK_Albums_Artists FOREIGN KEY (ArtistID)
        REFERENCES Artists (ArtistID))

Songs (
    SongID int NOT NULL IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
    AlbumID int NOT NULL,
    SongName varchar(100) NOT NULL,
    NumberOfListens int NOT NULL DEFAULT 0
    CONSTRAINT FK_Songs_Albums FOREIGN KEY (AlbumID)
        REFERENCES Albums (AlbumID))

一旦你有了这个设计，你就可以搜索个人专辑和艺术家以及歌曲。您还可以添加覆盖索引来加快查询速度，索引将小得多，因此比原始设计更快。

如果您不需要进行范围查询（您可能不需要），那么您可以将 IDENTITY 键替换为 ROWGUID（如果这更适合您的设计）；在这种情况下，这并不重要，我会坚持使用简单的 IDENTITY。

您必须小心集群键。如果你聚集在一个完全不连续的键上（艺术家、专辑和歌曲名称绝对符合非连续的条件），那么你最终会出现页面分割和其他令人讨厌的情况。你不想要这个。正如 Marc 所说，这个密钥的副本会添加到每个索引中，当您的密钥长度为 300 或 600 字节时，您肯定不希望这样。

如果你希望能够通过艺术家、专辑和歌曲名快速查询某首特定歌曲的收听量，通过上面的设计，其实很简单，你只需要正确索引即可：

CREATE UNIQUE INDEX IX_Artists_Name ON Artists (ArtistName)
CREATE UNIQUE INDEX IX_Albums_Artist_Name ON Albums (ArtistID, AlbumName)
CREATE UNIQUE INDEX IX_Songs_Album_Name ON Songs (AlbumID, SongName)
    INCLUDE (NumberOfListens)

现在这个查询会很快：

SELECT ArtistName, AlbumName, SongName, NumberOfListens
FROM Artists ar
INNER JOIN Albums al
    ON al.ArtistID = ar.ArtistID
INNER JOIN Songs s
    ON s.AlbumID = al.AlbumID
WHERE ar.ArtistName = @ArtistName
AND al.AlbumName = @AlbumName
AND s.SongName = @SongName

如果你检查执行计划，你会看到 3 个索引查找 - 这是你能得到的最快速度。我们保证了与原始设计完全相同的独特性，并针对速度进行了优化。更重要的是，它是标准化的，因此艺术家和专辑都有自己的特定身份，这使得从长远来看更容易管理。搜索“艺术家 X 的所有专辑”要容易得多。搜索“专辑 Y 上的所有歌曲”更加更加容易和快捷。

设计数据库时，规范化应该是您首先考虑的问题，索引应该是第二个问题。您可能会发现，一旦您有了规范化的设计，最佳的索引策略就变得显而易见了。