sql-server - 为什么 SQL Server 执行计划取决于比较顺序
问题描述
我正在优化 SQL Server 上的查询并遇到了一些我没有预料到的事情。数据库中有一个表tblEvent
,除其他列之外,它还有IntegrationEventStateId
和ModifiedDateUtc
。这些列有一个索引:
create index IX_tblEvent_IntegrationEventStateId_ModifiedDateUtc
on dbo.tblEvent (
IntegrationEventStateId,
ModifiedDateUtc
)
当我执行以下语句时:
select *
from dbo.tblEvent e
where
e.IntegrationEventStateId = 1
or e.IntegrationEventStateId = 2
or e.IntegrationEventStateId = 5
or (e.IntegrationEventStateId = 4 and e.ModifiedDateUtc >= dateadd(minute, -5, getutcdate()))
我得到了这个执行计划(注意索引没有被使用):
但是当我执行这个语句时:
select *
from dbo.tblEvent e
where
1 = e.IntegrationEventStateId
or 2 = e.IntegrationEventStateId
or 5 = e.IntegrationEventStateId
or (4 = e.IntegrationEventStateId and e.ModifiedDateUtc >= dateadd(minute, -5, getutcdate()))
我得到了这个执行计划(注意索引确实被使用了):
两个语句之间的唯一区别是where
子句中的比较顺序。谁能解释为什么我得到不同的执行计划?
更新 1 - 完整的复制脚本如下
CREATE TABLE dbo.tblEvent
(
EventId INT IDENTITY PRIMARY KEY,
IntegrationEventStateId INT,
ModifiedDateUtc DATETIME,
OtherCol CHAR(1),
index IX_tblEvent_IntegrationEventStateId_ModifiedDateUtc(IntegrationEventStateId, ModifiedDateUtc)
);
INSERT INTO dbo.tblEvent
SELECT TOP 356525 3,
DATEADD(SECOND, ROW_NUMBER() OVER (ORDER BY @@SPID)%63424, GETUTCDATE()),
'A'
FROM sys.all_objects o1,
sys.all_objects o2;
UPDATE STATISTICS dbo.tblEvent WITH FULLSCAN
select *
from dbo.tblEvent e
where
e.IntegrationEventStateId = 1
or e.IntegrationEventStateId = 2
or e.IntegrationEventStateId = 5
or (e.IntegrationEventStateId = 4 and e.ModifiedDateUtc >= dateadd(minute, -5, getutcdate()))
select *
from dbo.tblEvent e
where
1 = e.IntegrationEventStateId
or 2 = e.IntegrationEventStateId
or 5 = e.IntegrationEventStateId
or (4 = e.IntegrationEventStateId and e.ModifiedDateUtc >= dateadd(minute, -5, getutcdate()))
更新 2 - 原始表的 DDL
CREATE TABLE [dbo].[tblEvent]
(
[EventId] [int] NOT NULL IDENTITY(1, 1),
[EventTypeId] [int] NOT NULL,
[ScorecardId] [int] NULL,
[ScorecardAreaId] [int] NULL,
[AreaId] [int] NULL,
[ScorecardTopicId] [int] NULL,
[TopicId] [int] NULL,
[ScorecardRequirementId] [int] NULL,
[RequirementId] [int] NULL,
[DocumentId] [int] NULL,
[FileId] [int] NULL,
[TopicTitle] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ScorecardTopicStatus] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[RequirementText] [nvarchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ScorecardRequirementStatus] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DocumentName] [nvarchar] (260) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CreatedByUserId] [int] NOT NULL,
[CreatedByUserSessionId] [int] NOT NULL,
[CreatedDateUtc] [datetime2] (4) NOT NULL CONSTRAINT [DF__tblEvent__Create__0737E4A2] DEFAULT (sysutcdatetime()),
[CreatedDateLocal] [datetime2] (4) NOT NULL CONSTRAINT [DF__tblEvent__Create__082C08DB] DEFAULT (sysdatetime()),
[ModifiedByUserId] [int] NOT NULL,
[ModifiedByUserSessionId] [int] NOT NULL,
[ModifiedDateUtc] [datetime2] (4) NOT NULL CONSTRAINT [DF__tblEvent__Modifi__09202D14] DEFAULT (sysutcdatetime()),
[ModifiedDateLocal] [datetime2] (4) NOT NULL CONSTRAINT [DF__tblEvent__Modifi__0A14514D] DEFAULT (sysdatetime()),
[IsDeleted] [bit] NOT NULL,
[RowVersion] [timestamp] NOT NULL,
[ScorecardRequirementPriority] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[AffectedUserId] [int] NULL,
[UserId] [int] NULL,
[CorrelationId] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[IntegrationEventStateId] [int] NULL,
[IntegrationEventId] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[IntegrationEventContent] [nvarchar] (max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[IntegrationEventType] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[IntegrationEventTryCount] [int] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblEvent] ADD CONSTRAINT [PK_dbo.tblEvent] PRIMARY KEY CLUSTERED ([EventId]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_tblEvent_IntegrationEventStateId_ModifiedDateUtc] ON [dbo].[tblEvent] ([IntegrationEventStateId], [ModifiedDateUtc]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblEvent] ADD CONSTRAINT [FK_dbo.tblEvent_dbo.tblEventType_EventTypeId] FOREIGN KEY ([EventTypeId]) REFERENCES [dbo].[tblEventType] ([EventTypeId])
GO
ALTER TABLE [dbo].[tblEvent] ADD CONSTRAINT [FK_dbo.tblEvent_dbo.tblIntegrationEventState_IntegrationEventStateId] FOREIGN KEY ([IntegrationEventStateId]) REFERENCES [dbo].[tblIntegrationEventState] ([IntegrationEventStateId])
GO
解决方案
这里有很多问题,但最重要的是基数估计 (CE)。
较新的(“默认”)CE 模型在尝试在没有匹配步骤的情况下针对直方图计算选择性时,很难使用谓词。
例如,初始基数估计返回的选择性为 1:
select *
from dbo.tblEvent e
where
1 = e.IntegrationEventStateId
or 2 = e.IntegrationEventStateId
or 5 = e.IntegrationEventStateId
or (4 = e.IntegrationEventStateId and e.ModifiedDateUtc >= dateadd(minute, -5, getutcdate()))
如使用跟踪标志 3604 和 2363 所示:
Begin selectivity computation
Input tree:
LogOp_Select
CStCollBaseTable(ID=1, CARD=356525 TBL: dbo.tblEvent AS TBL: e)
ScaOp_Logical x_lopOr
ScaOp_Comp x_cmpEq
ScaOp_Identifier QCOL: [e].IntegrationEventStateId
ScaOp_Const TI(int,ML=4) XVAR(int,Not Owned,Value=1)
ScaOp_Comp x_cmpEq
ScaOp_Identifier QCOL: [e].IntegrationEventStateId
ScaOp_Const TI(int,ML=4) XVAR(int,Not Owned,Value=2)
ScaOp_Comp x_cmpEq
ScaOp_Identifier QCOL: [e].IntegrationEventStateId
ScaOp_Const TI(int,ML=4) XVAR(int,Not Owned,Value=5)
ScaOp_Logical x_lopAnd
ScaOp_Comp x_cmpGe
ScaOp_Identifier QCOL: [e].ModifiedDateUtc
ScaOp_Identifier COL: ConstExpr1001
ScaOp_Comp x_cmpEq
ScaOp_Identifier QCOL: [e].IntegrationEventStateId
ScaOp_Const TI(int,ML=4) XVAR(int,Not Owned,Value=4)
Plan for computation:
CSelCalcCombineFilters_ExponentialBackoff (OR)
CSelCalcCombineFilters_ExponentialBackoff (AND)
CSelCalcColumnInInterval
Column: QCOL: [e].ModifiedDateUtc
CSelCalcColumnInInterval
Column: QCOL: [e].IntegrationEventStateId
CSelCalcColumnInInterval
Column: QCOL: [e].IntegrationEventStateId
Loaded histogram for column QCOL: [e].ModifiedDateUtc from stats with id 3
Loaded histogram for column QCOL: [e].IntegrationEventStateId from stats with id 2
Selectivity: 1
Stats collection generated:
CStCollFilter(ID=2, CARD=356525)
CStCollBaseTable(ID=1, CARD=356525 TBL: dbo.tblEvent AS TBL: e)
End selectivity computation
当基于成本的优化开始时,输入树的形式略有不同,要求 CE 计算更简单谓词的选择性:
Begin selectivity computation
Input tree:
LogOp_Select
CStCollBaseTable(ID=1, CARD=356525 TBL: dbo.tblEvent AS TBL: e)
ScaOp_Logical x_lopOr
ScaOp_Comp x_cmpEq
ScaOp_Identifier QCOL: [e].IntegrationEventStateId
ScaOp_Const TI(int,ML=4) XVAR(int,Not Owned,Value=1)
ScaOp_Comp x_cmpEq
ScaOp_Identifier QCOL: [e].IntegrationEventStateId
ScaOp_Const TI(int,ML=4) XVAR(int,Not Owned,Value=2)
ScaOp_Comp x_cmpEq
ScaOp_Identifier QCOL: [e].IntegrationEventStateId
ScaOp_Const TI(int,ML=4) XVAR(int,Not Owned,Value=5)
Plan for computation:
CSelCalcColumnInInterval
Column: QCOL: [e].IntegrationEventStateId
Selectivity: 1
Stats collection generated:
CStCollFilter(ID=3, CARD=356525)
CStCollBaseTable(ID=1, CARD=356525 TBL: dbo.tblEvent AS TBL: e)
End selectivity computation
这相当于:
SELECT *
FROM dbo.tblEvent AS TE
WHERE TE.IntegrationEventStateId IN (1, 2, 5);
在这两种情况下,CE 都会评估 100% 的行将匹配,尽管值 1、2 或 5 没有直方图步骤(样本数据只有值 3)。很容易将此归咎于CSelCalcColumnInInterval
计算器,因为它似乎将 {1, 2, 5} 视为单个区间 {1:5}。
通常情况下,“旧版”CE 在这里做得更好(更详细),因此您应该会发现以下提示会产生更好的计划:
OPTION (USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION'));
使用重现数据,这会产生一个希望的单一搜索和键查找。
请注意,搜索执行四个搜索操作,每个不相交谓词一个。
[1] Seek Keys[1]: Prefix: IntegrationEventStateId = 1
[2] Seek Keys[1]: Prefix: IntegrationEventStateId = 2
[3] Seek Keys[1]: Prefix: IntegrationEventStateId = 4, Start: ModifiedDateUtc >= dateadd(minute,(-5),getutcdate())
[4] Seek Keys[1]: Prefix: IntegrationEventStateId = 5
新的 CE 旨在比原来的 CE 更可预测,更容易维护/扩展。“旧版”有一些螺栓固定在上面,并在很长一段时间内进行了改进。这种复杂性有好处也有陷阱。较新的 CE 在某种程度上预计会出现回归和较低质量的估计。这应该会随着时间的推移而改善,但我们还没有做到。我会将此处显示的行为视为计算器的限制。也许他们会解决它。
请参阅使用 SQL Server 2014 基数估计器优化您的查询计划。
为什么计划形状取决于文本表示的问题更多的是一个次要问题。编译过程确实包含SelPredNorm
将谓词重写为规范化形式的逻辑(例如 rule ),并且两个 repro 查询都成功地重写为同一棵树。这样做是为了各种内部目的,包括索引和计算列匹配,并使逻辑简化更容易处理。
不幸的是,重写的形式仅在基于成本的优化之前使用。基于成本的优化器的输入保留了原始查询中存在的文本顺序差异。我相信这是故意的,这样做是为了防止意外的计划更改。人们有时会以稍微不同且不寻常的方式编写查询来实现特定的计划形状。如果优化器突然开始挫败那些逻辑上多余的尝试,人们会感到不安。对于查询存储和更有效的计划强制等问题,这可以说不是问题,但这些都是相对较新的创新。
换句话说,计划是不同的,因为人们过去依赖不同的文本产生不同的计划,而现在改变它会造成太大的破坏。
推荐阅读
- r - Haven 包将标签添加到 stata 数据
- ios - 快速处理多个状态
- python - 将多个 SQL 查询的结果附加到 Pandas 数据框或字典中
- c++ - 我需要转换一些代码,使其不包含'auto'关键字
- php - 如何获取具有相同 INNER JOIN'ed ID 的所有记录
- windows-services - Chef windows_service 资源 - 配置现有服务
- asp.net - 在 ASP 验证失败后,即使我提供了正确的输入,我也无法仅在第一次调用服务方法
- python - 在 Pycharm 中使用“控制台中的执行行”时无法使用相对路径
- typescript - 错误:“导航器”类型上不存在属性“通知”
- python - 如何在 Python 2.7 中没有公共键的两个列表的元素之间执行算术运算