T-SQL中LEFT()与LIKE运算符的速度比较

Question

我正在基于特定的nvarchar列的首字母而不是通常基于结果数量进行分页。

我面临的挑战不是使用LIKE运算符还是等号（=）运算符来过滤结果。

select *
from table
where name like @firstletter + '%'

对比。

select *
from table
where left(name, 1) = @firstletter

我曾尝试在网上搜索这两者之间的速度比较，但很难找到任何结果，因为大多数搜索结果都与LEFT JOINs有关，而不是LEFT函数。

- Robert Koritnik

---

你看过两者的查询计划吗？你自己进行了基准测试吗？ - Oded

不，我没有。我认为我不是第一个问自己这个问题的人，所以我想可能已经有其他人测试过了。由于LEFT主要涉及连接，我似乎找不到这些数据。因此，我想知道是否有人在某个地方有链接。我怀疑LEFT应该会更快。 - Robert Koritnik

第一个方法（使用 LIKE）可以利用 name 索引，而第二个方法（针对列值的函数）则不能。 - Marcus Adams

@MarcusAdams 是正确的。当使用任何函数，如LEFT、SUBSTRING等时，服务器无法使用索引。 - Free Coder 24

谢谢，我不知道。 - Robert Koritnik

like 的可搜索性使其成为 SQL Server 中优秀的默认选择。即使没有索引，它也可以更好地利用列上的字符串统计信息，并为匹配行数提供更好的基数估计。 - Martin Smith

Answer 1

“Left”与“Like”——在实现索引的情况下，应尽可能使用“Like”，因为“Like”不是一个函数，因此可以利用数据上可能存在的任何索引。

另一方面，“Left”是一个函数，因此无法利用索引。 此网页描述了使用差异及其示例。这意味着SQL服务器必须为返回的每个记录计算函数。

“Substring”和其他类似的函数也是罪魁祸首。

- user3810913

---

虽然我理解并同意，但我认为MySQL代码库应该足够智能，能够将简单的LEFT()调用转换为LIKE语句，或者利用LEFT()的第一个操作数上的索引。就像OP一样，我在我的代码中偶然发现了LEFT()的缓慢性，并惊讶地发现它的性能没有像LIKE一样得到优化。 - Codemonkey

Answer 2

你最好的选择是在真实的生产数据上测量性能，而不是猜测（或问我们）。这是因为性能有时取决于你正在处理的数据，尽管在这种情况下似乎不太可能（但我不知道，因此您应该检查）。

如果这是你经常要查询的内容，你应该考虑添加另一个（索引）列，其中包含name的小写第一个字母，并通过插入/更新触发器进行设置。

这将以最小存储增加的代价使此查询变得非常快速：

select * from table where name_first_char_lower = @firstletter

这是因为大多数数据库的读取次数远高于写入次数，这将把计算成本（只针对写入）分摊到所有读取操作中。

这会引入冗余数据，但只要你理解并缓解了相关后果，并且需要额外的性能，为了提高性能这样做就没问题。

- paxdiablo

---

这是一个非常好的想法，尽管在所有读取中只有不到5％的读取将以这种方式读取此表中的数据。其他读取将根据其他内容进行过滤，而不是名称... 因此，索引似乎不可行。 - Robert Koritnik

在名称列上的索引可用于 name like 'a%'，无需计算列。 - Andomar

Andomar，说得好 - 这样做无法处理不区分大小写的情况，尽管我知道一些DBMS允许在列上计算索引（因此它会自动转换为小写）。 - paxdiablo

是否区分大小写取决于 SQL Server 中的列排序规则。该问题标记为 TSQL。 - Martin Smith

Answer 3

我有一个类似的问题，并对两者进行了测试。这是我的代码。

where (VOUCHER like 'PCNSF%'
    or voucher like 'PCLTF%'
    or VOUCHER like 'PCACH%'
    or VOUCHER like 'PCWP%'
    or voucher like 'PCINT%')

用1分51秒返回了1434行数据。

对比：

where (LEFT(VOUCHER,5) = 'PCNSF'
    or LEFT(VOUCHER,5)='PCLTF'
    or LEFT(VOUCHER,5) = 'PCACH'
    or LEFT(VOUCHER,4)='PCWP'
    or LEFT (VOUCHER,5) ='PCINT')

1分钟27秒内返回了1434行结果。

使用左侧的5个选项可以使我的数据查询更快。顺带一提，我的整体查询确实使用了一些索引。

- PCSgtL

---

有人想到相同的想法，并编写了自己的基准测试，用于比较 LEFT、RIGHT、LIKE、SUBSTRING 和 CHARINDEX。链接为：http://cc.davelozinski.com/sql/like-vs-substring-vs-leftright-vs-charindex。 - user3810913

Answer 4

我建议在搜索列包含索引时使用like运算符。我在生产环境中测试了上述查询，使用select count(column_name) from table_name where left(column_name,3)='AAA' OR left(column_name,3)= 'ABA' OR ...一直到9个OR子句。我的计数显示有7301477条记录，left用了4秒，而like只用了1秒，即where column_name like 'AAA%' OR Column_Name like 'ABA%' or ...一直到9个like子句。

在where子句中调用函数不是最佳实践。请参阅http://blog.sqlauthority.com/2013/03/12/sql-server-avoid-using-function-in-where-clause-scan-to-seek/。

- Kannan.C

Answer 5

Entity Framework Core 用户们

你可以使用 EF.Functions.Like(columnName, searchString + "%") 来代替 columnName.startsWith(...)，这样在生成的 SQL 中只会有一个 LIKE 函数，而不是所有这些 'LEFT' 的疯狂操作！

根据你的需求，你可能需要预处理 searchString。

另请参阅https://github.com/aspnet/EntityFrameworkCore/issues/7429

这个函数在 Entity Framework（非核心版）EntityFunctions中不存在，所以我不确定如何在 EF6 中实现它。

- Simon_Weaver