SQL 服务器 : NonClustered index not used

Question

我已经阅读了很多关于索引和它们之间差异的内容。现在我正在我的项目中进行查询优化。我已经创建了非聚集索引，它应该用于查询执行，但事实并非如此。详情如下:

表格:

索引:

CREATE NONCLUSTERED INDEX [_IXProcedure_Deleted_Date] ON [por].[DailyAsset]
(
    [Deleted] ASC,
    [Date] DESC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

Entity Framework 生成的查询:

exec sp_executesql N'SELECT 
[Project1].[C1] AS [C1], 
[Project1].[AssetId] AS [AssetId], 
[Project1].[Active] AS [Active], 
[Project1].[Date] AS [Date]
FROM ( SELECT 
    [Extent1].[AssetId] AS [AssetId], 
    [Extent1].[Active] AS [Active], 
    [Extent1].[Date] AS [Date], 
    1 AS [C1]
    FROM [por].[DailyAsset] AS [Extent1]
    WHERE (0 = [Extent1].[Deleted]) AND ([Extent1].[Date] < @p__linq__0)
)  AS [Project1]
ORDER BY [Project1].[Date] DESC',N'@p__linq__0 datetime2(7)',@p__linq__0='2014-05-01 00:00:00'

执行计划:

缺少索引详细信息:

The Query Processor estimates that implementing the following index could improve the query cost by 23.8027%.


CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [por].[DailyAsset] ([Deleted],[Date])
INCLUDE ([AssetId],[Active])

我知道将 AssetId 和 Active 列纳入索引后，将使用索引。

现在，为什么不包含列就无法工作？

这是另一个查询的简化示例，其中提取所有列作为结果。 (强制)使用索引查找的唯一解决方案是在索引中包含所有列，这些列具有相同的估计子树成本(显而易见)。

另一个恼人的问题是排序无知。日期列在索引中并设置为降序。它完全被忽略了，当然，排序操作在执行计划中占据了昂贵的位置。

更新 1:

正如@Jayachandran 所指出的，IndexSeek + KeyLookUp 应该在上面的查询中使用，但是覆盖索引有很好的记录，并且它假设应该包括 AssetId 和 Active 列。我同意这一点。

我正在创建 UPDATE 1 来演示覆盖索引在下面的查询中的有用性。同一张表，更大的结果集。据我所知，索引中不应该使用任何一个列，并且索引仍然为 Date 和 Deleted 列创建。

exec sp_executesql N'SELECT 
[Project1].[DailyAssetId] AS [DailyAssetId], 
[Project1].[AssetId] AS [AssetId], 
[Project1].[CreatedByUserId] AS [CreatedByUserId], 
[Project1].[UpdatedByUserId] AS [UpdatedByUserId], 
[Project1].[TimeCreated] AS [TimeCreated], 
[Project1].[TimeUpdated] AS [TimeUpdated], 
[Project1].[Deleted] AS [Deleted], 
[Project1].[TimeDeleted] AS [TimeDeleted], 
[Project1].[DeletedByUserId] AS [DeletedByUserId], 
[Project1].[Active] AS [Active], 
[Project1].[Date] AS [Date], 
[Project1].[Quantity] AS [Quantity], 
[Project1].[TotalBookValue] AS [TotalBookValue], 
[Project1].[CostPrice] AS [CostPrice], 
[Project1].[CostValue] AS [CostValue], 
[Project1].[FairPrice] AS [FairPrice], 
[Project1].[FairValue] AS [FairValue], 
[Project1].[UnsettledQuantity] AS [UnsettledQuantity], 
[Project1].[UnsettledValue] AS [UnsettledValue], 
[Project1].[SettlementDate] AS [SettlementDate], 
[Project1].[EffectiveDate] AS [EffectiveDate], 
[Project1].[PortfolioId] AS [PortfolioId]
FROM ( SELECT 
    [Extent1].[DailyAssetId] AS [DailyAssetId], 
    [Extent1].[AssetId] AS [AssetId], 
    [Extent1].[CreatedByUserId] AS [CreatedByUserId], 
    [Extent1].[UpdatedByUserId] AS [UpdatedByUserId], 
    [Extent1].[TimeCreated] AS [TimeCreated], 
    [Extent1].[TimeUpdated] AS [TimeUpdated], 
    [Extent1].[Deleted] AS [Deleted], 
    [Extent1].[TimeDeleted] AS [TimeDeleted], 
    [Extent1].[DeletedByUserId] AS [DeletedByUserId], 
    [Extent1].[Active] AS [Active], 
    [Extent1].[Date] AS [Date], 
    [Extent1].[Quantity] AS [Quantity], 
    [Extent1].[TotalBookValue] AS [TotalBookValue], 
    [Extent1].[CostPrice] AS [CostPrice], 
    [Extent1].[CostValue] AS [CostValue], 
    [Extent1].[FairPrice] AS [FairPrice], 
    [Extent1].[FairValue] AS [FairValue], 
    [Extent1].[UnsettledQuantity] AS [UnsettledQuantity], 
    [Extent1].[UnsettledValue] AS [UnsettledValue], 
    [Extent1].[SettlementDate] AS [SettlementDate], 
    [Extent1].[EffectiveDate] AS [EffectiveDate], 
    [Extent1].[PortfolioId] AS [PortfolioId]
    FROM [por].[DailyAsset] AS [Extent1]
    WHERE (0 = [Extent1].[Deleted]) AND ([Extent1].[Date] < @p__linq__0)
)  AS [Project1]
ORDER BY [Project1].[Date] DESC',N'@p__linq__0 datetime2(7)',@p__linq__0='2014-05-01 00:00:00'

Answer 1

在这种情况下，扫描和查找(使用键查找)的区别在于返回的行数。体积太大，所以优化器选择了一个更便宜的计划——只扫描整个表。这将比使用 NC 索引更快。

想象一下，如果您强制它使用 NC 索引并且它必须对表中 40% 的行执行键查找。这就像执行多次的 foreach 循环。所以 SQL 选择只扫描表，因为它比循环更快。

关于您关于如何考虑可能包含在其他查询中的其他列的问题，确实有两种选择。您可以创建一个包含最常用列的覆盖索引，或者您可以更改主键以将其定向到您最常用的访问路径。即按日期、删除和唯一性标识列。

另一方面，对主键使用 guid 会导致聚集索引和所有其他索引出现各种问题(因为 PK 的键将包含在所有其他索引中)。 guid 的随机排序导致行以随机顺序插入到页面中。由于索引是有序的，因此必须不断拆分页面以容纳新行。创建一个自然递增的索引会好得多，这也可能有助于解决上述问题，具体取决于编写的查询类型。