为什么这个查询没有使用正确的索引? [英] Why doesn't this query use the proper index?
问题描述
表定义:
CREATE TABLE [dbo].[AllErrors](
[ID] [int] IDENTITY(1,1) NOT NULL,
[DomainLogin] [nvarchar](50) NULL,
[ExceptionDate] [datetime] NULL,
[ExceptionDescr] [nvarchar](max) NULL,
[MarketName] [nvarchar](50) NULL,
[Version] [nvarchar](50) NULL,
CONSTRAINT [PK_AllErrors] PRIMARY KEY CLUSTERED ([ID] ASC)
)
-- Add an index on the date
CREATE NONCLUSTERED INDEX [IX_ExceptionDate] ON [dbo].[AllErrors] ([ExceptionDate] ASC)
我运行以下查询:
declare @yesterday datetime
select @yesterday = getdate() - 1
SELECT * INTO #yst
from AllErrors
where ExceptionDate between @yesterday and @yesterday + 1
此代码不使用我的IX_ExceptionDate(从执行计划中收集).它对主键索引进行集群扫描.但是，下面的代码确实使用了IX_ExceptionDate索引:
This code does not use my IX_ExceptionDate (as gleaned from the execution plan). It does a clustered scan on the primary key index. However, the code below does use the IX_ExceptionDate index:
SELECT * INTO #yst
from AllErrors
where ExceptionDate between @yesterday and @yesterday + 1
AND ExceptionDate = ExceptionDate
这是为什么?
添加了视觉执行计划.
下面的文本执行计划.
查询1:
|-表插入(OBJECT:([[yst]]，SET:([#yst].[ID] = [Expr1006]，[#yst].[DomainLogin] = [MarketStats].[dbo] .. [AllErrors].[DomainLogin]，[#yst].[ExceptionDate] = [MarketStats].[dbo].[AllErrors].[ExceptionDate]，[#yst].[ExceptionDescr] = [MarketStats].[dbo] .. [AllErrors].[ExceptionDescr]，[#yst].[MarketName] = [MarketStats].[dbo].[AllErrors].[MarketName]，[#yst].[Version] = [MarketStats].[dbo] .[AllErrors].[版本])) |-顶部(ROWCOUNT est 0) |-计算标量(DEFINE:([[Expr1006] = setidentity([MarketStats].[dbo].[AllErrors .. [ID]，(-7)，(0)，N'#yst')))) |-聚集索引扫描(OBJECT:([[MarketStats].[dbo].[AllErrors].[PK_AllErrors])，WHERE:([MarketStats].[dbo].[AllErrors .. [ExceptionDate]> = [@ yesterday] ]和[MarketStats].[dbo].[AllErrors .. [ExceptionDate]< = [@ yesterday] +'1900-01-02 00:00:00.000'))
|--Table Insert(OBJECT:([#yst]), SET:([#yst].[ID] = [Expr1006],[#yst].[DomainLogin] = [MarketStats].[dbo].[AllErrors].[DomainLogin],[#yst].[ExceptionDate] = [MarketStats].[dbo].[AllErrors].[ExceptionDate],[#yst].[ExceptionDescr] = [MarketStats].[dbo].[AllErrors].[ExceptionDescr],[#yst].[MarketName] = [MarketStats].[dbo].[AllErrors].[MarketName],[#yst].[Version] = [MarketStats].[dbo].[AllErrors].[Version])) |--Top(ROWCOUNT est 0) |--Compute Scalar(DEFINE:([Expr1006]=setidentity([MarketStats].[dbo].[AllErrors].[ID],(-7),(0),N'#yst'))) |--Clustered Index Scan(OBJECT:([MarketStats].[dbo].[AllErrors].[PK_AllErrors]), WHERE:([MarketStats].[dbo].[AllErrors].[ExceptionDate]>=[@yesterday] AND [MarketStats].[dbo].[AllErrors].[ExceptionDate]<=[@yesterday]+'1900-01-02 00:00:00.000'))
查询2:
|-表插入(OBJECT:([[yst]]，SET:([#yst].[ID] = [Expr1006]，[#yst].[DomainLogin] = [MarketStats].[dbo] .. [AllErrors].[DomainLogin]，[#yst].[ExceptionDate] = [MarketStats].[dbo].[AllErrors].[ExceptionDate]，[#yst].[ExceptionDescr] = [MarketStats].[dbo] .. [AllErrors].[ExceptionDescr]，[#yst].[MarketName] = [MarketStats].[dbo].[AllErrors].[MarketName]，[#yst].[Version] = [MarketStats].[dbo] .[AllErrors].[版本])) |-顶部(ROWCOUNT est 0) |-计算标量(DEFINE:([[Expr1006] = setidentity([MarketStats].[dbo].[AllErrors .. [ID]，(-7)，(0)，N'#yst')))) |-嵌套循环(内部联接，外部参考:([MarketStats].[dbo].[AllErrors].[ID]，[Expr1008])使用无序预取进行了优化) |-索引寻求(OBJECT:([[MarketStats].[dbo].[AllErrors].[IX_ExceptionDate])，SEEK:([MarketStats].[dbo].[AllErrors .. [ExceptionDate]> = [@yesterday] AND [MarketStats].[dbo].[AllErrors].[ExceptionDate]< = [@yesterday] +'1900-01-02 00:00:00.000')，位置:([MarketStats].[dbo].[ AllErrors].[ExceptionDate] = [MarketStats].[dbo].[AllErrors .. [ExceptionDate])ORDERED FORWARD) |-聚集索引寻求(OBJECT:([[MarketStats].[dbo].[AllErrors].[PK_AllErrors])，SEEK:([MarketStats].[dbo].[AllErrors].[ID] = [MarketStats]. [dbo].[AllErrors .. [ID])LOOKUP FORDER FORWARD)
|--Table Insert(OBJECT:([#yst]), SET:([#yst].[ID] = [Expr1006],[#yst].[DomainLogin] = [MarketStats].[dbo].[AllErrors].[DomainLogin],[#yst].[ExceptionDate] = [MarketStats].[dbo].[AllErrors].[ExceptionDate],[#yst].[ExceptionDescr] = [MarketStats].[dbo].[AllErrors].[ExceptionDescr],[#yst].[MarketName] = [MarketStats].[dbo].[AllErrors].[MarketName],[#yst].[Version] = [MarketStats].[dbo].[AllErrors].[Version])) |--Top(ROWCOUNT est 0) |--Compute Scalar(DEFINE:([Expr1006]=setidentity([MarketStats].[dbo].[AllErrors].[ID],(-7),(0),N'#yst'))) |--Nested Loops(Inner Join, OUTER REFERENCES:([MarketStats].[dbo].[AllErrors].[ID], [Expr1008]) OPTIMIZED WITH UNORDERED PREFETCH) |--Index Seek(OBJECT:([MarketStats].[dbo].[AllErrors].[IX_ExceptionDate]), SEEK:([MarketStats].[dbo].[AllErrors].[ExceptionDate] >= [@yesterday] AND [MarketStats].[dbo].[AllErrors].[ExceptionDate] <= [@yesterday]+'1900-01-02 00:00:00.000'), WHERE:([MarketStats].[dbo].[AllErrors].[ExceptionDate]=[MarketStats].[dbo].[AllErrors].[ExceptionDate]) ORDERED FORWARD) |--Clustered Index Seek(OBJECT:([MarketStats].[dbo].[AllErrors].[PK_AllErrors]), SEEK:([MarketStats].[dbo].[AllErrors].[ID]=[MarketStats].[dbo].[AllErrors].[ID]) LOOKUP ORDERED FORWARD)
推荐答案
编译查询时，它不知道变量的值是什么.您可以尝试OPTION (RECOMPILE).
It doesn't know what the value of the variables will be when it compiles the query. You could try OPTION (RECOMPILE).
我认为在查询中添加AND子句(即使从逻辑上讲，它根本没有更多选择)必须误导优化器以更高的选择性估算查询，从而为您提供所需的计划！
I presume that the addition of the AND clause in the query (even though logically it makes it no more selective at all) must mislead the optimiser into estimating the query with greater selectivity thus giving you the plan that you wanted!
您在评论中说，不带ExceptionDate = ExceptionDate的版本估计为88234.8行，带带8823.48的版本估计为
You say in the comments that the version without the ExceptionDate = ExceptionDate is estimated at 88234.8 rows and the version with 8823.48
通常，在没有可用统计信息的情况下，SQL Server会依赖于谓词中比较运算符的类型而采用启发式方法.
Generally in the absence of usable statistics SQL Server falls back to heuristics dependant upon the type of comparison operator in the predicate.
例如，假设>谓词将返回30％的行，而=谓词将返回10％的行，因此看起来它只是将其直接应用于第一个行的结果估计.有趣的是，它没有考虑到等于这一事实本身就是针对此列的事实！
It assumes that a > predicate will return 30% of the rows for example and that an = predicate will return 10% of the rows so it looks like it is just applying that directly to the result of the first estimate. Interesting that it does not take account of the fact that the equals is against the column itself here!
这篇关于为什么这个查询没有使用正确的索引?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
