尽管有聚簇索引，SQL Server仍在使用非聚簇索引

问题描述：

我在名为Shopper的表上有两个索引。尽管有聚簇索引，SQL Server仍在使用非聚簇索引

聚集索引：

CREATE CLUSTERED INDEX [CI_EMail_ShopperNumID] 
ON [dbo].[Shopper] ([EMail] ASC, [ShopperNumID] ASC)

非聚集索引

CREATE NONCLUSTERED INDEX [nci_wi_Shopper_D8E9A1BB0660D0838F923BB8587C7115] 
ON [dbo].[Shopper] ([EMail] ASC) 
INCLUDE ([DateCreated], [FirstName], [LastLoginDate], [LastName], 
    [MaxEmailVolume], [ShopperNumID], [ShopperSourceCD], [ShopperSourceOther])

我运行一个非常简单的SELECT：

SELECT ShopperNumID 
FROM shopper 
WHERE Email = '[email protected]'

在分析执行计划，我注意到，非正在使用聚簇索引：

现在，我把非聚集索引：

DROP INDEX IF EXISTS [nci_wi_Shopper_D8E9A1BB0660D0838F923BB8587C7115] 
ON [dbo].[Shopper] 
GO

并重新运行我的选择注意到，聚簇索引（最终）使用

。

有人可以解释为什么（庞大的）非聚集索引被优化引擎使用，而不是（首选）聚集索引？

的Microsoft SQL Server 2016（RTM-GDR）（KB3194716） - 在Windows 10 13.0.1722.0（X64）
开发版（64位）专业版6.3（建设14393 :)

UPDATE： 基于收到的输入，为了进一步评估，我在表上创建了另一个非聚集索引，与已有的聚集索引非常相似。

CREATE NONCLUSTERED INDEX [NCI_EMail_ShopperNumID] 
ON [dbo].[Shopper] ([EMail] ASC, [ShopperNumID] ASC)

目前，该表有3个指标，能够支持我的SELECT：

聚集索引[CI_EMail_ShopperNumID]
非聚集索引[nci_wi_Shopper_D8E9A1BB0660D0838F923BB8587C7115]
非聚集索引[NCI_EMail_ShopperNumID]

现在，当我运行s AME SELECT：

SELECT ShopperNumID 
FROM shopper 
WHERE Email = '[email protected]'

和分析执行计划，我注意到新创建非聚集索引正在使用：

好像优化是坚定的关于使用非聚集索引，不管怎样！

聚集索引不是真的索引，它是表本身。因此，它包含所有表格列，索引列仅定义数据存储的排序顺序。 –

你的NONCLUSTERED覆盖索引的用例是什么？由于它只包含[email]列，并假定它存储在同一个文件组中，所以在CLUSTERED索引上的性能没有提高。 – uhleeka

我很好奇 - 哪一个实际上更快？（清除缓存后）。 “SET STATISTICS IO”是否显示任何特别不同的输出？ –

答

正在使用非聚簇索引，因为它已根据Email进行了优化以查找行。

您可能认为它很笨重，但是它被键入Email的事实使它成为您的查询的理想选择，即使它包含表中的每一列。

你可能没有意识到的是，聚集索引同样庞大，因为它隐式包含了表中的每个字段。因此，在最坏的情况下（不要设计这样的东西），你的索引都被键入Email，并且都包含每一列。优化器可以选择使用，真的。

如果你使用这个脚本就可以告诉你实际使用的空间量由非聚簇和聚簇索引：排序聚簇索引和数据行存储在基于表或视图：

SELECT o.NAME AS TableOrViewName, 
     i.name As IndexName, 
     i.type_desc As IndexType, 
     i.index_id As IndexOrdinal, 
     s.Name AS SchemaName, 
     p.rows AS RowCounts, 
     p.data_compression_desc As CompressionType, 
     SUM(a.total_pages) * 8/1024.0 AS ObjectSpaceMB, 
     SUM(a.used_pages) * 8/1024.0 AS UsedSpaceMB 
     FROM sys.objects As o 
     LEFT JOIN sys.indexes i ON o.OBJECT_ID = i.object_id 
     JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id 
     JOIN sys.allocation_units a ON p.partition_id = a.container_id 
     LEFT JOIN sys.schemas s ON o.schema_id = s.schema_id 
     WHERE o.NAME NOT LIKE 'dt%' 
     AND o.is_ms_shipped = 0 
     AND i.OBJECT_ID > 255 
     GROUP BY o.Name, 
     i.name, 
     i.type_desc, 
     i.index_id, 
     s.Name, 
     p.data_compression_desc, 
     p.Rows;

答

从MSDN: Clustered and Nonclustered Indexes Described关键的价值。这些是包含在索引定义中的列。每个表只能有一个聚簇索引，因为数据行本身只能按一个顺序排序。

非聚簇索引覆盖（包括）附加的指定列，以便在引用任何包含的列时不需要返回到表。请参阅MSDN:Create Indexes with Included Columns。有效地，非聚集索引就像创建一个包含列的新表，按索引列排序。

对于您的查询，聚簇索引和非聚簇索引非常接近，唯一的区别是聚簇索引额外按[ShopperNumID]排序。也许查询优化器正在挑选非聚集索引，因为它名义上更合适。在这种情况下，更好的配合并不一定意味着更好的性能。

假设群集索引和非聚簇索引都位于同一存储介质上，则非聚簇索引占用空间，但不提供添加的性能值。

答

基本上，它是六个一个或另一个的另一半。

您的聚簇索引和非聚簇索引都具有电子邮件地址的b-tree结构。所以，要么能够非常快地找到匹配的电子邮件地址。

那么，优化程序如何选择要提取？那么，在这两种情况下，如果有一条记录，那么一个页面（数据页面或索引叶页）被提取。也许任意选择非聚集索引。

但是，优化器不知道电子邮件地址匹配的记录数。因此，它必须根据电子邮件匹配的数量做出决定。如果非聚集索引只有两列，那么这将是一件容易的事。索引页面将包含更多记录（因为“记录”只有两列），所以与电子邮件匹配的记录将在较少的页面上。

尽管如此，非聚集索引是一个包含所有列的覆盖索引。也许更多的这些适合索引页面而不是数据页面（在数据页面上有一些开销，它可能比索引页面上的开销更多）。

那么，我们在哪里得到了？基本操作是通过b-tree（对于两种索引类型都是相同的）进行搜索，然后读取匹配的记录。在大多数情况下，这两种索引结构在这些操作中将非常相当。对于非聚集索引，SQL Server可能会有轻微的偏好，因为索引页上的数据比数据页上的记录数多（这是一种猜测）。

感谢您的输入。请参阅我在帖子中所做的修改。 – AeyJey

答

首先，赞赏查看查询计划以查看正在使用哪个索引。查询优化器会尽量减少IO，但它可以做一些有趣的事情。一般来说，非聚集索引小于聚集索引。如果优化器可以看到非聚集索引可以使用更少的读取来回答查询，这是您的问题的答案。如果非聚集索引包含表中的所有列，则会发生异常。我怀疑这可能是你问题的关键。

尽管在聚簇索引中使用字符串的确有用处，但请记住聚簇索引始终包含在每个非聚簇索引中。你希望你的聚集索引很小，并且如果不是唯一的话就是有选择性的，它看起来像ShopperNumbId会符合这个标准，但是我们没有你的全表。考虑从你的聚集索引中删除电子邮件地址。

如果您的应用程序需要根据电子邮件地址查找记录，从而创建列的最小完整覆盖索引，您需要为您提供最佳性能，这就是nci_wi_Shopper_D8E9A1BB0660D0838F923BB8587C7115的样子。

尽管有聚簇索引，SQL Server仍在使用非聚簇索引

相关推荐