惊人的SQL速度提升

我刚刚发现以下两个select语句之间的执行计划性能大不相同:

select * from your_large_table
where LEFT(some_string_field, 4) = '2505'

select * from your_large_table
where some_string_field like '2505%'

执行计划分别为98%和2%.然后速度有点不同.当我看到它时,我真的很震惊.

因为它读得很好所以我总是完成LEFT(xxx)='yyy'.我实际上通过检查LINQ生成的SQL来反对我手工制作的SQL来找到它.我假设LIKE命令会更慢,但实际上要快得多.

我的问题是为什么LEFT()慢于LIKE'%..'.它们完全一样吗？

另外,使用LEFT()会导致CPU命中吗？

更一般地说,在查询中不应该在WHERE子句的LEFT侧使用函数.如果这样做,SQL将不使用索引 - 它必须为表的每一行评估函数.目标是确保你的where子句是" Sargable "

其他一些例子:

Bad: Select ... WHERE isNull(FullName,'') = 'Ed Jones'
Fixed: Select ... WHERE ((FullName = 'Ed Jones') OR (FullName IS NULL))

Bad: Select ... WHERE SUBSTRING(DealerName,4) = 'Ford'
Fixed: Select ... WHERE DealerName Like 'Ford%'

Bad: Select ... WHERE DateDiff(mm,OrderDate,GetDate()) >= 30
Fixed: Select ... WHERE OrderDate < DateAdd(mm,-30,GetDate()) 

Bad: Select ... WHERE Year(OrderDate) = 2003
Fixed: Select ... WHERE OrderDate >= '2003-1-1' AND OrderDate < '2004-1-1'

看起来像表格LEFT(some_string_field,4)是针对全表扫描的每一行进行评估,而"like"表达式将使用索引.

如果索引是前锚定模式,则优化"喜欢"使用索引比分析涉及字符串函数的任意表达式更容易进行优化.

在SQL Server必须计算每行的结果的子句中使用函数调用会产生巨大的影响.另一方面,like内置语言功能是高度优化的.