sql-server - 在非聚集索引中添加所有主键约束

问题描述

我创建了一个包含以下列的表。所有列都是唯一键（列）我的表中没有主键。

表Product：

Bat_Key,
product_no,
value,
pgm_name,
status,
industry,
created_by,
created_date

我改变了我的表来添加约束

ALTER TABLE [dbo].[Product] 
    ADD CONSTRAINT [PRODUCT_PK] 
        PRIMARY KEY NONCLUSTERED ([Bat_Key] ASC, [product_no] ASC,
                                  [value] ASC, [pgm_name] ASC, )
                    WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
GO

如果我创建如下索引：

CREATE NONCLUSTERED INDEX [PRODUCT_BKEY_PNO_IDX] 
ON [dbo].[PRODUCT] ([Bat_Key] ASC, [product_no] ASC, [value], [pgm_name])
INCLUDE ([status], [industry]) 
         WITH (STATISTICS_NORECOMPUTE = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
GO

此设计是否适用于以下选择查询：

select * 
from Product 
where Bat_Key = ? and product_no=? 
order by product_no, pgm_name;

select * 
from Product 
where Bat_Key = ? and product_no=? and pgm_name = ? and value = ?

select * 
from Product 
where Bat_Key = ? and product_no=?

delete from Product 
where Bat_Key = ? and product_no=?

还是应该根据我的where条款创建不同的索引？

标签： sql-serverindexingddlnon-clustered-index

解决方案

聚集索引与非聚集索引非常不同。实际上，两个索引的类型都包含根据您指定的列排序的数据。然而，

聚集索引还包含表中的其余数据（除了 nvarchar(max) 之类的一些数据）。您可以认为这是它在数据库中的保存方式
非聚集索引仅包含您已包含在索引中的列

如果您没有聚集索引，则您有一个“堆”。它们不是 PK，而是内置的行标识符。

在您的情况下，由于您的主键是non-clustered，因此创建另一个具有相同 fields 的索引是没有意义的。要读取数据，它必须从您的 PK 中获取行标识符，然后从堆中读取数据。

另一方面，如果您的主键是聚集的（这是默认设置），那么在某些情况下，在字段上使用非聚集索引可能会很有用。但请注意，您添加的每个非聚集索引也会减慢更新、插入和删除的速度（因为也必须维护索引）。

在您的示例中 - 假设您有一个字段，该字段是包含大量信息的行上的 varchar(8000)。甚至要从聚集索引中读取一行，它必须从其他字段读取（例如）100 个字节，并且从该新字段读取最多 8000 个字节。换句话说，它将您需要阅读的数量乘以 80 倍。

我倾向于看到具有两种类型数据的表

您汇总的数据
您只关心逐行级别的数据

例如，在交易表中，您可能有 transaction_id、transaction_date、transaction_amount、transaction_description、transaction_entered_by_user_id。

在大多数情况下，每当您获得总计等时，您会经常需要交易金额、查看总计时的日期（例如，本周的交易总数是多少？）
另一方面，description 和 user_id 仅在您引用特定行时使用（例如，谁进行了此特定事务？）
在这些情况下，我经常在聚合中使用的字段上放置一个非聚集索引，即使它们与聚集索引重叠。它只是减少了所需的读取量。

关于这方面的一个非常好的视频是由 Brent Ozar 拍摄的，名为How to think like the SQL Server Engine - 我强烈推荐它，因为它对我理解如何使用索引有很大帮助。

关于您的具体示例 - 在索引中有两件事要查找：

“寻找”到数据集中特定点的能力（基于索引的排序）。
减少阅读量的能力。

在允许搜索方面，您需要以最合适的方式对索引进行排序。在进行过滤时（例如，WHERE 子句、JOIN，一个经验法则是首先查找“精确”匹配项。对于这些，它们的顺序无关紧要，只要它们全部匹配即可到那时。

在你的情况下，你有

where Bat_Key = ? and product_no=? 
where Bat_Key = ? and product_no=? and pgm_name = ? and value = ?

这表明您的前两个字段应该是 Bat_Key 和 product_no（按任意顺序）。然后你也可以拥有 pgm_name 和 value（也可以按任意顺序）。

你也有

where Bat_Key = ? and product_no=? 
order by product_no, pgm_name;

这向我建议第三个字段应该是 pgm_name（作为 Bat_Key、product_no 和 pgm_name 的索引将提供您需要的内容）。

但是 - 这是一个很大的问题 - 你有很多 * 在那里，例如，

select * 
from Product 
where Bat_Key = ? and product_no=?

因为您选择 *，所以任何不是聚集索引的索引也需要返回到实际行以获取包含在 * 中的其余内容。

由于这些需要表中的所有字段（不仅仅是索引中的字段），因此需要返回堆（在您的情况下）。如果您在上面的字段上有一个聚集索引以及一个非聚集索引，那么无论如何它都必须从聚集索引中读取，因为那里有您的查询所需的信息。

再一次 - 上面的视频 - 比我更好地解释了这一点。

因此，在您的情况下，我建议使用以下主键

ADD CONSTRAINT [PRODUCT_PK] 
        PRIMARY KEY CLUSTERED ([Bat_Key] ASC, [product_no] ASC,
                                  [pgm_name] ASC, [value] ASC)

差异

它是集群的而不是非集群的
重新排列第 3 和第 4 字段的顺序以帮助按 pgm_name 排序
没有真正需要第二个非聚集索引，因为没有太多其他要阅读的内容。

sql-server - 在非聚集索引中添加所有主键约束

问题描述

解决方案

推荐阅读