我应该使用哪种非聚集索引？

Question

这里我正在研究SQL Server Management Studio上的非聚集索引。

我创建了一个包含超过 100 万条记录的表。该表有一个主键。

CREATE TABLE [dbo].[Customers](
    [CustomerId] [int] IDENTITY(1,1) NOT NULL,
    [CustomerName] [varchar](100) NOT NULL,
    [Deleted] [bit] NOT NULL,
    [Active] [bit] NOT NULL,
 CONSTRAINT [PK_Customers] PRIMARY KEY CLUSTERED 
(
    [CustomerId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF,     ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

这是我将用来查看显示的执行计划的查询：

SELECT CustomerName FROM Customers

好吧，在没有附加非聚集索引的情况下执行此命令，它会导致执行计划向我显示：

I/O cost = 3.45646
Operator cost = 4.57715

现在我正在尝试查看是否可以为了提高性能，所以我为此表创建了一个非聚集索引：

1) 第一个非聚集索引

CREATE NONCLUSTERED INDEX [IX_CustomerID_CustomerName] ON [dbo].[Customers] 
(
    [CustomerId] ASC,
    [CustomerName] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF,
IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON,
ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

再次对 Customers 表执行 select，执行计划向我显示：

I/O cost = 2.79942
Operator cost = 3.92001

看起来更好。现在我删除了刚刚创建的非聚集索引，以便创建一个新索引：

2) 第一个非聚集索引

CREATE NONCLUSTERED INDEX [IX_CustomerIDIncludeCustomerName] ON [dbo].[Customers] 
(
    [CustomerId] ASC
)
INCLUDE ( [CustomerName]) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF,  
SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF,   
ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

使用这个新的非聚集索引，我执行了 select 语句再次执行计划向我显示相同结果：

I/O cost = 2.79942
Operator cost = 3.92001

那么，我应该使用哪个非聚集索引？ 为什么 I/O 和 Operator 的执行计划成本相同？ 我做错了什么还是这是预期的？

谢谢

Answer 1

这是因为“CustomerName”在第二个索引中被 INCLUDE 编辑（请参阅 这关于已包含的列）。

基本上，两个索引的工作方式完全相同 - 它们是第一个索引列与 WHERE 子句不匹配的覆盖索引。

这意味着这两种情况下的查询都将扫描索引但不接触表。

我希望对于该特定查询性能更好的索引将是仅针对 CustomerName 的索引。

Answer 2

在通过使用 WHERE 过滤结果、使用 ORDER 对结果进行排序或将结果连接到索引列上的另一个表来使用索引之前，您不会注意到有或没有索引的太大差异。

尝试在没有索引的情况下执行如下查询：

SELECT *
FROM Customers
WHERE CustomerName = 'Marcus Adams'

然后在 CustomerName 列上添加索引并重试。

您还需要表中有足够的行，以便数据库系统实际使用索引，并且足够多的行，以便您会注意到扫描行和使用索引之间的区别。

Answer 3

实际上，您的两个非聚集索引都没有多大意义。

要点是：聚集索引的列（在您的例子中为 CustomerId）- 已包含在您拥有的每个非聚集索引的每个条目中。毕竟，如果找到条目，则该聚类列将用于实际数据查找。因此，将其添加到非聚集索引通常是多余的，而且只是浪费空间。

问题更多：如何选择想要显示的行？ WHERE 子句中将显示哪些列？

如果您在这里找到一个模式（例如您总是按City进行选择），那么满足您需求的非聚集索引就是

CREATE NONCLUSTERED INDEX [IX_Customer_City] ON [dbo].[Customers] 
(
    [City] ASC
)
INCLUDE ( [CustomerName]) 

这样，您为SQL Server提供了一种方法来轻松查找匹配的行给定的城市，并包括您想要返回的列 (CustomerName)，允许 SQL Server 直接从索引页获取必要的信息（使其成为所谓的覆盖索引< /code> - 它涵盖您的查询，例如返回所需的所有信息） - 您不需要执行“书签查找”，例如从实际数据页面获取整个 Customer 数据行（通过 CustomerId 找到它，它也位于非聚集索引中，因为它是聚集键）。