我创建了一个包含以下列的表。所有列都是唯一键(列)——我的表中没有主键。
表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子句创建不同的索引？
聚集索引与非聚集索引非常不同。实际上，两种类型的索引都包含根据指定列排序的数据。然而，
- 聚集索引还包含表中的其余数据(除了nvarchar(max)之类的一些数据)。您可以将其视为保存在数据库中的方式
- 非聚集索引仅包含索引中包含的列
如果没有聚集索引，则有一个"堆"。它们没有PK，而是内置了一行标识符。
在您的情况下，由于主键是非聚集，因此使用相同字段创建另一个索引是没有意义的。要读取数据，它必须从PK中获取行标识符，然后从堆中读取数据。
另一方面，如果主键是clustered(这是默认值)，则在某些情况下，对字段使用非聚集索引可能会很有用。但请注意，添加的每个非聚集索引也会减慢更新、插入和删除的速度(因为索引也必须维护)。
在您的示例中，假设您有一个字段，该字段在包含大量信息的行上是varchar(8000)。甚至要从聚集索引中读取一行，它必须从其他字段中读取(比如)100个字节，并从该新字段中读取最多8000个字节。换句话说，它将你需要阅读的量乘以80倍。
我倾向于看到有两种类型的数据的表格
- 您聚合的数据
- 您只关心逐行级别的数据
例如，在事务表中，您可能有transaction_id、transaction_date、transaction_aunt、transaction_description、transaction_entered_by_user_id。
- 在大多数情况下，每当你得到总额等时，你经常需要交易金额、查看总额的日期(例如，本周的交易总额是多少？)
- 另一方面，描述和user_id仅在引用特定行时使用(例如，谁做了这个特定事务？)
- 在这些情况下，我经常在聚合中使用的字段上放置非聚集索引，即使它们与聚集索引重叠。它只是减少了所需的读取量
Brent Ozar制作了一个非常好的视频，名为"如何像SQL Server引擎一样思考"，我强烈建议使用它，因为它对我理解如何使用索引有很大帮助。
关于您的具体示例，在索引中有两件事需要查找：
- "查找"数据集中特定点的能力(基于索引的排序)
- 减少读取量的能力
在允许查找方面，您需要以最合适的方式对索引进行排序。在进行筛选(例如，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排序
- 不需要第二个非聚集索引，因为没有太多其他内容可供读取