主页 / dba / 问题 / 112876
Accepted
Ian Boyd
Asked: 2015-09-02 11:14:35 +0800 CST

如果复合索引包含主键，我应该将它标记为唯一吗？

  • 772

给定一些带有主键的表，例如：

CREATE TABLE Customers (
   CustomerID int NOT NULL PRIMARY KEY,
   FirstName nvarchar(50),
   LastName nvarchar(50),
   Address nvarchar(200),
   Email nvarchar(260)
   --...
)

我们有一个唯一的主键CustomerID。

传统上我可能需要一些额外的覆盖索引；例如，通过CustomerID或快速找到用户Email：

CREATE INDEX IX_Customers_CustomerIDEmail ON Customers
(
   CustomerID,
   Email
)

这些是我几十年来创建的索引类型。

它不需要是唯一的，但它实际上是

索引本身的存在是为了避免表扫描；它是一个覆盖索引以提高性能（该索引不作为强制唯一性的约束）。

今天我想起了一点信息——SQL Server 可以使用以下事实：

  • 列具有外键约束
  • 列具有唯一索引
  • 一个约束是可信的

为了帮助它优化其查询执行。事实上，来自SQL Server Index Design Guide：

如果数据是唯一的并且您希望强制执行唯一性，则在相同的列组合上创建唯一索引而不是非唯一索引可为查询优化器提供额外信息，从而生成更有效的执行计划。在这种情况下，建议创建唯一索引（最好通过创建 UNIQUE 约束）。

鉴于我的多列索引包含主键，这个复合索引实际上是唯一的。这不是我特别需要 SQL Server 在每次插入或更新期间强制执行的约束；但事实是这个非聚集索引是唯一的。

将这个事实上的唯一索引标记为实际唯一有什么好处吗？

在客户上创建唯一索引 IX_Customers_CustomerIDEmail
(
   客户ID，
   电子邮件
)

在我看来，SQL Server可以足够聪明地意识到我的索引已经是唯一的，因为它包含主键这一事实。

  • 但也许它不知道这一点，如果我将索引声明为唯一，优化器就有优势。
  • 除了现在可能会导致插入和更新过程中的速度变慢，它必须执行唯一性检查——以前从来没有这样做过。
  • 除非它知道索引已经保证是唯一的，因为它包含主键。

当复合索引包含主键时，我找不到来自 Microsoft 的任何指导。

唯一索引的好处包括：

  • 确保已定义列的数据完整性。
  • 提供了有助于查询优化器的附加信息。

如果复合索引已经包含主键，我应该将它标记为唯一吗？或者 SQL Server 可以自己解决这个问题吗？

sql-server index

2 个回答

  1. Best Answer

    如果复合索引已经包含主键，我应该将它标记为唯一吗？

    可能不是。优化器通常可以使用有关所包含键列的唯一性的信息，因此没有真正的优势。

    还有一个重要的后果是，在更新计划上将索引标记为唯一，修改该索引的键以考虑：

    设置

    CREATE TABLE dbo.Customers 
(
   CustomerID int NOT NULL PRIMARY KEY,
   FirstName nvarchar(50),
   LastName nvarchar(50),
   [Address] nvarchar(200),
   Email nvarchar(260)
);

CREATE NONCLUSTERED INDEX 
    IX_Customers_CustomerIDEmail 
ON dbo.Customers
(
   CustomerID,
   Email
);

-- Pretend we have some rows
UPDATE STATISTICS dbo.Customers 
WITH ROWCOUNT = 100000, PAGECOUNT = 20000;

    每个索引更新计划（非唯一索引）

    UPDATE dbo.Customers 
SET Email = N'New', [Address] = 'New Address'
WHERE Email = N'Old' 
OPTION (QUERYTRACEON 8790); -- Per-index update plan

    执行计划：

    拆分和过滤

    优化器通常在按行（“窄”计划）或按索引（“宽”计划）更新非聚集索引之间做出基于成本的决定。默认策略（内存中 OLTP 表除外）是宽计划。

    窄计划（非聚集索引与堆/聚集索引同时维护）是针对小型更新的性能优化。并非所有情况都实施此优化 - 使用某些功能（如索引视图）意味着关联的索引将在一个广泛的计划中维护。

    详细信息：优化更改数据的 T-SQL 查询

    在这种情况下，我使用了未记录的跟踪标志 8790 来强制执行广泛的更新计划：因此，该计划显示了单独维护的聚集索引和非聚集索引。

    拆分将每个更新变成一个单独的删除和插入对；过滤器过滤掉不会导致索引更改的任何行。

    更多信息：（非更新更新）由 SQL Server QO 团队提供。

    每个索引更新计划（唯一索引）

    -- Same index, but unique
CREATE UNIQUE INDEX IX_Customers_CustomerIDEmail ON Customers
(
   CustomerID,
   Email
)
WITH (DROP_EXISTING = ON);

UPDATE dbo.Customers 
SET Email = N'New', [Address] = 'New Address'
WHERE Email = N'Old' 
OPTION (QUERYTRACEON 8790); -- Per-index update plan

    执行计划：

    拆分排序折叠

    当索引被标记为唯一时，请注意额外的 Sort 和 Collapse 运算符。

    更新唯一索引的键时需要这种拆分-排序-折叠模式，以防止中间唯一键违规。

    更多信息：Craig Freedman维护唯一索引

    特别是排序可能是一个问题。这不仅是不必要的额外成本，而且如果估计不准确，它可能会溢出到磁盘。

    关于非聚集键

    另一个要考虑的因素是，非聚集索引结构在索引的每个级别上始终是唯一的，即使UNIQUE未指定也是如此。聚集键（如果聚集索引未标记为唯一，则可能是唯一标识符）被添加到所有级别的非唯一非聚集索引。

    因此，以下索引定义：

    CREATE INDEX IX_Customers_CustomerIDEmail ON Customers
(
   Email
)
WITH (DROP_EXISTING = ON);

    ...实际上包含所有级别的键（Email、CustomerID）。因此，它在两列上都是“可搜索的”：

    SELECT * 
FROM dbo.Customers AS C WITH (INDEX(IX_Customers_CustomerIDEmail))
WHERE C.Email = N'Email'
AND C.CustomerID = 1;

    寻求

    更多信息：Kalen Delaney 的关于非聚集索引键的更多信息

    • 12

  2. SQL 已经知道它是唯一的（如果它包括 PK，它就不能再唯一了），无论您是否明确告诉它。

    非唯一索引和唯一索引之间的最大区别在于，非唯一索引在索引的更高级别需要聚集索引键（如果 CIX 未声明为唯一，则具有唯一值），而不仅仅是叶等级。

    在您的情况下，您已经在键中拥有 CIX，这意味着它已经在索引的每个级别。

    但是您可以创建一个具有单独 PK（唯一）和 CIX（没关系）的表。然后创建一个非唯一索引，该索引在其键中包含 PK。将一些行放入表中，包括 CIX 列的一些容易找到的 varchar 值。放入足够多的行以产生多个级别的索引。然后你可以用 DBCC IND 来查找你的 NCIX 中的页面，然后用 DBCC PAGE 打开一些查看数据，看看 CIX 键值是否在更高的级别。

    • 8

相关问题