logo标签列表

SQL中的'like'和'='性能比较

sqlperformance
108

这个问题大致涉及到了我所关心的内容，但是答案并没有明确解决我的问题。

一般来说，在使用通配符时，'='比'like'更快。这似乎是常识。然而，假设我有一个包含有限数量不同的固定varchar标识符的列，并且我想选择与其中一个匹配的所有行:

select * from table where value like 'abc%'
并且
select * from table where value = 'abcdefghijklmn'

'Like' 只需要测试前三个字符以找到匹配项，而 '=' 必须比较整个字符串。在这种情况下，如果其他所有条件相等，似乎 'like' 会有优势。

这是一个普遍的学术问题，因此使用哪种数据库并不重要，但它在使用SQL Server 2005时出现。

30
你忽略了一个重要的问题，那就是value是否有索引。如果有索引，那么=就是一个简单的查找，不需要进行全表扫描，并且将击败你使用任何LIKE语句。请注意，不改变原意。 - Daniel DiPaolo
8
我认为那是不正确的。在LIKE语句末尾使用通配符是可搜索参数（SARGable），因此会在索引上执行范围查找，没有表扫描。该范围查找可以与= 语句相竞争，并且在许多情况下（例如，如果所有满足条件的行都在一个页面上，这是一个不太可能的情况），性能可能完全相同，涉及相同数量的读取。 - ErikE
我的“其他所有条件相等”是为了涵盖“是否索引”的问题，但是根据我对其他答案的评论，似乎至少存在一些争议，即这会产生多大的差异。 - MickeyfAgain_BeforeExitOfSO
看我的回答。我最初测试了未索引的表，性能是相同的（两个表扫描完全相同）。我假设在我的测试场景中它将被索引，否则你为什么会关心性能呢？ - JNK
6
这个问题和回答中频繁出现“like”的用法让我们听起来像一群高中女生，就像，完全是这样。 - JulianR
10个回答
79
看这里的引用：
引用如下: 索引使用LIKE的规则大致如下:
• 如果您的筛选条件使用等于=而且该字段已经建立了索引，那么它很可能会使用索引/聚集索引查询。
• 如果您的筛选条件使用LIKE，没有使用通配符（例如，如果在Web报告的参数中可以使用％但您却使用了完整字符串），那么它几乎与＃1一样容易使用索引。增加的成本几乎为零。
• 如果您的筛选条件使用LIKE，但是在开头使用了通配符（例如Name0 LIKE '%UTER'），那么使用索引的可能性要小得多，但它仍然可以在索引的整个或部分范围内执行索引扫描。
• 但是，如果您的筛选条件使用LIKE，但首先从一个字符串开始，并且在此之后有通配符（例如Name0 LIKE 'COMP%ER'），则SQL可能仅使用索引搜索快速查找具有相同起始字符的行，然后在这些行中查找精确匹配项。
• （还要记住，SQL引擎仍然可能不按照您期望的方式使用索引，这取决于查询中正在进行的其他操作以及您加入的表是什么。 SQL引擎保留在某种程度上重写您的查询，以便以它认为最有效的方式获取数据。)

高效的查询可能包括索引扫描(INDEX SCAN)，而不是索引查找(INDEX SEEK)。

56

这是一个可衡量的差异。

运行以下代码：

Create Table #TempTester (id int, col1 varchar(20), value varchar(20))
go

INSERT INTO #TempTester (id, col1, value)
VALUES
(1, 'this is #1', 'abcdefghij')
GO

INSERT INTO #TempTester (id, col1, value)
VALUES
(2, 'this is #2', 'foob'),
(3, 'this is #3', 'abdefghic'),
(4, 'this is #4', 'other'),
(5, 'this is #5', 'zyx'),
(6, 'this is #6', 'zyx'),
(7, 'this is #7', 'zyx'),
(8, 'this is #8', 'klm'),
(9, 'this is #9', 'klm'),
(10, 'this is #10', 'zyx')
GO 10000

CREATE CLUSTERED INDEX ixId ON #TempTester(id)CREATE CLUSTERED INDEX ixId ON #TempTester(id)

CREATE NONCLUSTERED INDEX ixTesting ON #TempTester(value)

接下来：

SET SHOWPLAN_XML ON

然后：

SELECT * FROM #TempTester WHERE value LIKE 'abc%'

SELECT * FROM #TempTester WHERE value = 'abcdefghij'

生成的执行计划显示第一个操作——LIKE比较的成本约为第二个操作——=比较的成本的10倍。

如果可以使用=比较，请尽量使用它。

4
+1 表示对实际测试的肯定。仅查看执行计划可能无法完全了解情况。我将进行一些自己的测试，并在发现任何意外情况时通知所有人。 - Tom H
1
Tom - 是的，但它让我足够确定这两者在幕后没有被以相同的方式处理。 - JNK
1
执行计划中显示的成本是错误的。它们不反映实际性能。在第一个计划中，它们基于估计的行数19.95，因此SQL Server会产生额外的19个关键查找成本，但实际上从未实现（即使在实际执行计划中，所显示的成本也是基于估计子树成本）。 - Martin Smith
1
我刚刚完成了你的测试，还有一个大约有100万行的测试，两种情况下性能和查询计划都是相同的。这是在SQL 2008上进行的，因为我在这台机器上没有安装2005版。 - Tom H
1
@JNK - 刚试了一下 - 差别微乎其微，不过差异仍然存在。LIKE用了327毫秒，而=只用了203毫秒。我预计如果我运行更多的测试并取得准确的平均值，临时表和真实表之间将没有实质性的区别。 - Will A
显示剩余13条评论
14

请注意，在使用like时，一些SQL方言会忽略索引，这会影响性能。尤其是当您不使用类似于您示例中的“以...开头”的模式时，更要注意。

你应该查看查询的执行计划，并尽可能少地猜测它正在做什么。

话虽如此，“以...开头”的模式可以优化，并且在SQL Server中已经得到了优化。它将使用表索引。EF 4.0因为这个原因切换到like用于StartsWith。

2
任何值得一提的关系型数据库在查询中包含like模式且通配符位于末尾时，都不会忽略索引。如果您绑定了值并且数据库支持与查询准备分离的绑定，则情况可能有所不同。 - Dave W. Smith
这也是我的直觉告诉我的，但我只有在SQL Server方面的实践经验，所以我特别关注它。 - Blindy
8
您正在询问错误的问题。在数据库中，运算符的性能并不重要，重要的是表达式的 SARGability 和整个查询的 coverability。运算符本身的性能基本上是无关紧要的。 那么，在 SARGability 方面，LIKE 和 = 如何比较？当使用一个不以常量开头的表达式（例如使用 LIKE '%something'）时，LIKE 的定义是非 SARGabale。 但是，= 或 LIKE 'something%' 是否可 SARGable 呢？不是。与关于 SQL 性能的任何问题一样，答案并不在查询文本中，而在已部署的架构中。如果存在索引来满足这些表达式，则可以SARGable 。 事实上，在 = 和 LIKE 之间存在细微差别。但是在 SQL 中询问一个运算符是否比另一个运算符“更快”就像询问“哪个车跑得更快，红色的还是蓝色的？”。您应该询问发动机大小和车辆重量等问题，而不是颜色......在优化关系表的问题上，查询的优化点应该是您的 索引 和 WHERE 子句中的表达式（和其他子句，但通常从 WHERE 开始）。
8
一个关于使用mysql 5.5的个人例子：我在两个表之间进行了内连接，一个表有三百万行，另一个表有一万行。当对索引使用like（没有通配符）时，需要大约30秒钟：
where login like '12345678'

使用“explain”，我得到了以下结果：
在相同的查询中使用“=”时，大约需要0.1秒钟。
where login ='12345678'

使用“explain”我得到了以下翻译：
如您所见，like 完全取消了索引查找，因此查询花费的时间增加了 300 倍。
你也可以简单地查看执行计划来确认这一点。 - LittleBobbyTables - Au Revoir
谢谢@LittleBobbyTables。我会看一下的。 - Aris
我不知道是不是因为我的最新版本（5.7）的原因，但在这里使用LIKE并没有破坏我的唯一索引。 - Sebas
@Sebas 也许还要看数据量的大小吧？你的情况是什么？ - Aris
7
如果value未被索引，两者都会导致表扫描。在这种情况下，性能差异将是可以忽略不计的。
如果value被索引了，正如丹尼尔在他的评论中指出的那样，=将导致索引查找，其性能为O（log N）。LIKE（根据选择性）将（很可能）导致对索引的部分扫描>= 'abc'和<'abd'，这将需要比=更多的工作。
请注意，我在这里谈论的是SQL Server - 并非所有的DBMS都支持LIKE。
我认为你不知道二分查找的工作原理。如果SQL识别出模式（它确实能够识别），那么“=”和“`like '...%'”两种情况会表现相同，因为在这两种情况下，子树都是基于比较关系选择的。 - Blindy
是的，但 OP 的例子假设该范围内只有一个值，因此考虑到这一点，比较结果将会相同。 - Blindy
有效观点 - 并不完全清楚这是否是OP所说的，但我认为这更有可能是情况，并且如果是这种情况，性能几乎相同。 - Will A
LIKE语句的范围搜索很可能与等号（=）语句相当竞争，并且在许多情况下（例如，如果所有满足条件的行在一页上，这是一个不太可能的条件），性能可能完全相同，涉及相同数量的读取。我认为说“需要更多努力”是一种错误的笼统陈述。 - ErikE
如果我理解这里所说的话，那么如果未索引，则性能可以忽略不计，因为表扫描比每行（正如我的问题所暗示的那样）所需的比较成本高得多。但是那行扫描将会对结果产生影响-这是我的问题的核心-是吗？另一方面，如果索引，并且在每个范围内只有一个值，性能也几乎相同。 - MickeyfAgain_BeforeExitOfSO
显示剩余2条评论
5

= 比 LIKE 快得多，即使没有通配符。我在 MySQL 上测试了 11GB 的数据和超过 1 亿条记录，其中 f_time 列已经被索引。

SELECT * FROM XXXXX WHERE f_time = '1621442261' 
#took 0.00sec and return 330 records

SELECT * FROM XXXXX WHERE f_time LIKE '1621442261' 
#took 44.71sec and return 330 records
3
除了所有答案之外，还有一些需要考虑的问题:
“like”不区分大小写，因此每个字符都需要比较两次，而“=”只对相同字符进行一次比较。
无论是否使用索引，都会出现这个问题。
你的意思是，大小写敏感吗？ - Kitanga Nday
1

我正在使用一个拥有超过400M记录的大型数据库，而我的搜索查询中使用了LIKE。以下是最终结果。

有三个表格tb1、tb2和tb3。当我在所有表格的查询中使用EQUAL时，响应时间为193毫秒。而当我在其中一个表格中使用LIKE时，响应时间为19.22秒，并且对于所有表格的LIKE响应时间为112秒。

0

也许你正在寻找关于全文搜索的信息。

与全文搜索相比，LIKE Transact-SQL谓词仅适用于字符模式。此外，您不能使用LIKE谓词查询格式化的二进制数据。 此外，针对大量非结构化文本数据的LIKE查询比针对相同数据的等效全文查询慢得多。针对数百万行文本数据的LIKE查询可能需要几分钟才能返回；而针对相同数据的全文查询只需几秒钟或更短时间，具体取决于返回的行数。

网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接