sql - 执行计划索引使用
问题描述
我尝试在我经常使用的一些查询中优化索引使用。最近我了解到在 where 子句中使用函数和隐式转换并不是最优的。令我惊讶的是,由于正确使用索引,隐式转换可以更快。
我有一张名为 Records 的表。聚集索引和主键位于 Id 列 (int) 上,非聚集索引位于 Created 列 (datetime) 上。为了避免隐式转换,我创建了 @dd 变量。
declare @dd Datetime
set @dd = '2019-08-25'
--1st option
select * from Records where Created > @dd
--2nd option
select * from Records where Created > '2019-08-25'
--3rd option
select * from Records where Year(Created) = 2019 and MONTH(Created) = 8 and DAY(Created) = 25
不幸的是,第一个选项使用索引扫描(列 ID)。第二个选项使用索引查找(创建列)和键查找,这很好,但我想知道为什么第一个选项不做同样的事情。我添加了第 3 个选项只是为了查看差异,它的行为与第 1 个选项相同。
我找到了查询执行计划:缺少索引,这导致了有关此行为的博客文章,但它没有解释为什么会发生这种情况。我可以看到估计的行数存在差异。当我将日期设置为时'2019-06-25'
,所有三个选项都使用类似的索引扫描计划。
那么当我可以预期行数会很低时,使用隐式转换是否是一个经验法则?我在这里很困惑。
解决方案
需要对日期时间文字进行隐式转换,因为 T-SQL 没有日期时间文字的语法。这不是性能问题。
隐式转换在由于数据类型优先(例如,将int
文字与 varchar 列进行比较)导致不可分割的表达式的情况下是一个问题。对列应用类似的函数YEAR
也会导致非 sargable 表达式,因为必须在比较之前评估函数结果。
您的查询中的问题是您使用的是变量而不是参数。您应该获得与文字相同的性能:
select * from Records where Created > @dd OPTION(RECOMPILE);
或参数化查询(假设缓存计划尚不存在):
EXEC sp_executesql N'select * from Records where Created > @dd'
, N'@dd datetime'
, @dd = '2019-08-25';
使用文字、参数和OPTION(RECOMPLE)
变量,SQL Server 在初始编译期间使用统计直方图(如果存在)来更好地估计可能返回的行数。在变量的情况下,SQL Server 使用平均密度统计来估计行数。结果可能会使用不同的计划,尤其是在数据分布不均的情况下。