代码之家 › 专栏 › 技术社区 › Simon Hughes

惊人的SQL速度提升

performance sql

Simon Hughes · 技术社区 · 16 年前

我刚刚发现以下两个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%。那么速度有点不同。当我看到它时,我真的很震惊。

我一直在做左(XXX)='YYY'因为它读得很好。实际上,我是通过检查Linq生成的SQL和手工制作的SQL来发现这一点的。我假设类似的命令会慢一些,但实际上快得多。

我的问题是为什么左()比类似的“…”慢。它们毕竟是相同的?

另外,使用left()是否会损坏CPU?

7 回复 | 直到 9 年前

BradC 16 年前

更一般地说,在查询中不应该在WHERE子句的左侧使用函数。如果这样做,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'

mfx 16 年前

它看起来像是为完整表扫描的每一行计算表达式left(一些字符串字段,4),而“like”表达式将使用索引。

如果索引是前锚定模式,那么优化“like”使用索引比分析涉及字符串函数的任意表达式要容易得多。

Dan Sydner 16 年前

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

hamishmcn 16 年前

如果对具有索引的列使用函数,则数据库将不再使用该索引(至少对于Oracle是这样)。
所以我猜你的示例字段' some_string_field '上有一个索引,该索引不能用于带有'Left'的查询

FredV 16 年前

你为什么说它们是相同的?他们可以解决相同的问题,但他们的方法不同。至少看起来…

使用左边的查询优化了测试,因为它已经知道了前缀的长度等,所以在C/C++中…无论是程序还是没有索引,使用LEFT实现某种类似行为的算法都是最快的。但是与大多数非声明性语言不同,在SQL数据库中,很多操作优化都是为您完成的。例如,LIKE可能是通过首先查找%符号实现的,如果注意到%是字符串中的最后一个字符,则可以像使用LEFT一样优化查询,但直接使用索引。

所以,我真的认为你是毕竟,他们的方法可能是相同的。唯一的区别是,DB服务器可以使用LIKE在查询中使用索引,因为在WHERE子句中没有函数将列值转换为未知值。

David Aldridge 16 年前

这里所发生的事情要么是RDBMS不能在left()谓词上使用索引,并且能够在类似的情况下使用索引,要么只是进行了错误的调用,在这种调用中更合适的访问方法。

首先,对于某些RDBMS来说,向列应用函数可能会阻止使用基于索引的访问方法,但这不是一个普遍的事实,也没有任何逻辑原因需要这样做。基于索引的访问方法(如Oracle的完全索引扫描或快速完全索引扫描)可能是有益的,但在某些情况下,RDBMS不能在基于函数的谓词的上下文中进行操作。

其次,乐观者在估计不同可用访问方法的好处时可能会简单地犯错误。假设系统可以执行基于索引的访问方法,它必须首先通过在解析时对数据进行采样,或使用启发式规则(例如“假设5%的行匹配”)来估计将与谓词匹配的行数。然后必须评估全表扫描或可用的基于索引的方法的相对成本。有时算法会出错,有时统计数据会误导或不准确,有时启发式规则不适合数据集。

关键是要注意一些问题:

您的RDBMS支持哪些操作?
什么是最合适的操作你正在处理的案件?
系统选择是否正确?
如何才能使系统执行更有效的操作(例如添加缺少的非空约束、更新统计信息等)?

根据我的经验,这不是一项微不足道的任务,通常最好留给专家。或者另一方面,把问题贴到stackoverflow——我们中的一些人发现这个东西很吸引人,狗帮我们。

WorkRelated 9 年前

正如@bradc所提到的,如果您有索引并想利用它们,就不应该在where子句中使用函数。

如果您阅读标题为“当存在索引时,在WHERE子句中使用LIKE而不是LEFT()或SUBString()” from these SQL Performance Tips 有更多的例子。

它还提示您将在 MCSE SQL Server 2012 exams 如果你也有兴趣买这些。-)