令人惊讶的SQL速度提升

Question

我刚刚发现以下两个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负担吗？

- Simon Hughes

Answer 1

总的来说，在查询语句的 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'

- BradC

---

第二行中的Typeof，它并不完全相同。 - Robert Wagner

太棒了！多亏了你，我现在不必再问自己的问题了。 :) - Ecyrb

似乎有人将你的示例代码复制到维基百科文章中，并进行了微小的替换。用“丰田”代替“福特”尤其明显。 - Ben Voigt

Answer 2

看起来表达式 LEFT(some_string_field, 4) 在进行全表扫描时会对每一行进行评估，而“like”表达式将使用索引。

如果一个“like”表达式是以前缀模式开头，那么优化它以使用索引比分析涉及字符串函数的任意表达式要容易得多。

- mfx

Answer 3

在where子句中使用函数调用会对SQL Server的性能产生巨大影响，因为它必须为每一行计算结果。另一方面，like是一种内置的语言特性，已经高度优化。

- dan s

Answer 4

如果您在带索引的列上使用函数，则数据库将不再使用该索引（至少在Oracle中是这样）。因此，我猜测您的示例字段 'some_string_field' 上有一个索引，该索引在使用 'LEFT' 的查询中未被使用。

- hamishmcn

---

这并不完全正确。索引仍然可以使用，但可能需要以不同的方式使用。如果谓词在索引上匹配的期望数量很小，并且索引的物理大小比表小得多，则可以利用索引全扫描或快速全扫描。 - David Aldridge

Answer 5

你为什么说它们是相同的？它们可能解决了同样的问题，但它们的方法不同。至少看起来是这样的...

使用 LEFT 的查询优化了测试，因为它已经知道前缀的长度等信息，所以在 C/C++/... 程序或没有索引的情况下，使用 LEFT 实现某种 LIKE 行为的算法将是最快的。但与大多数非声明性语言相比，在 SQL 数据库上，有很多优化工作是由数据库自动完成的。例如，LIKE 可能是通过首先查找 % 符号来实现的，如果注意到 % 是字符串中的最后一个字符，则可以像使用 LEFT 一样进行优化查询，但直接使用索引。

所以，我认为你最终是正确的，它们的方法可能是相同的。唯一的区别是，在使用 LIKE 的查询中，数据库服务器可以使用索引，因为 WHERE 子句中没有将列值转换为未知值的函数。

- FredV

---

'%'符号是在LIKE语句中的通配符，Fred。 - Kevin Fairchild

嗯，我不否认，我的观点是数据库可能已经对 "a like 'xxx%'" 进行了优化，变成了 "left(a,3) = 'xxx'"，但这并不重要，因为数据库可以使用索引，所以无论如何速度都会更快。 - FredV

Answer 6

这里发生的情况可能是RDBMS不能使用LEFT（）谓词上的索引，但却可以在LIKE上使用它；或者简单地选择了不太合适的访问方法。

首先，对于某些RDBMS，将函数应用于列会阻止使用基于索引的访问方法，但这并非普遍真实，也没有任何逻辑原因需要这样做。基于索引的访问方法（例如Oracle的全索引扫描或快速全索引扫描）可能会有益，但在某些情况下，RDBMS无法在基于函数谓词的上下文中执行该操作。

其次，优化器可能只是在估计可用访问方法的效益时出现了算术错误。假设系统可以执行基于索引的访问方法，则首先必须从表上的统计数据、列上的统计数据、解析时的数据抽样或使用启发式规则（例如，“假定5%的行匹配”）来估计将匹配谓词的行数。然后，需要评估全表扫描或可用基于索引的方法的相对成本。有时候计算出错，有时候统计数据会误导或不准确，有时候启发式规则不适用于数据集。

关键是要注意以下几个问题：

1. 你的RDBMS支持哪些操作？
2. 在你处理的情况下，最合适的操作是什么？
3. 系统的选择是否正确？
4. 可以采取什么措施来使系统执行更有效的操作（例如添加缺失的非空约束、更新统计信息等）？

根据我的经验，这不是一项微不足道的任务，通常最好交给专家。或者，将问题发布到Stackoverflow上--我们中的一些人会发现这些东西很有趣，上帝保佑我们。

- David Aldridge

Answer 7

正如@BradC所提到的，如果你使用了索引并希望充分利用它们，就不应该在WHERE子句中使用函数。

如果你阅读了“在存在索引的情况下，在WHERE子句中使用LIKE而不是LEFT()或SUBSTRING()”这一章节 从这些SQL性能提示 ，会有更多例子。

如果你对参加MCSE SQL Server 2012考试也感兴趣，它还暗示了你将遇到的问题。 :-)

- user4023224

---

我只是点了一下链接，想看看那些问题提示。虽然那里面没有很多，但仍然觉得有趣，因为问题居然被暗示了出来。 - user3810913