当使用SQL时,在WHERE子句中使用=而不是LIKE有任何好处吗?
没有任何特殊的运算符,LIKE和=是一样的,对吧?
当使用SQL时,在WHERE子句中使用=而不是LIKE有任何好处吗?
没有任何特殊的运算符,LIKE和=是一样的,对吧?
当前回答
要解决最初关于性能的问题,可以归结为索引利用率。当进行简单的表扫描时,“LIKE”和“=”是相同的。当涉及到索引时,这取决于LIKE子句是如何形成的。更具体地说,通配符的位置是什么?
考虑以下几点:
CREATE TABLE test(
txt_col varchar(10) NOT NULL
)
go
insert test (txt_col)
select CONVERT(varchar(10), row_number() over (order by (select 1))) r
from master..spt_values a, master..spt_values b
go
CREATE INDEX IX_test_data
ON test (txt_col);
go
--Turn on Show Execution Plan
set statistics io on
--A LIKE Clause with a wildcard at the beginning
DBCC DROPCLEANBUFFERS
SELECT txt_Col from test where txt_col like '%10000'
--Results in
--Table 'test'. Scan count 3, logical reads 15404, physical reads 2, read-ahead reads 15416, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
--Index SCAN is 85% of Query Cost
--A LIKE Clause with a wildcard in the middle
DBCC DROPCLEANBUFFERS
SELECT txt_Col from test where txt_col like '1%99'
--Results in
--Table 'test'. Scan count 1, logical reads 3023, physical reads 3, read-ahead reads 3018, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
--Index Seek is 100% of Query Cost for test data, but it may result in a Table Scan depending on table size/structure
--A LIKE Clause with no wildcards
DBCC DROPCLEANBUFFERS
SELECT txt_Col from test where txt_col like '10000'
--Results in
--Table 'test'. Scan count 1, logical reads 3, physical reads 2, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
--Index Seek is 100% of Query Cost
GO
--an "=" clause = does Index Seek same as above
DBCC DROPCLEANBUFFERS
SELECT txt_Col from test where txt_col = '10000'
--Results in
--Table 'test'. Scan count 1, logical reads 3, physical reads 2, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
--Index Seek is 100% of Query Cost
GO
DROP TABLE test
当使用"="和"LIKE"时,在查询计划的创建中也可能存在可以忽略不计的差异。
其他回答
这是我对SQL 'like' vs '='性能问题的另一个答案的复制/粘贴:
一个使用mysql 5.5的个人例子:我在2个表之间有一个内部连接,一个是300万行,一个是1万行。
在索引上使用like时(没有通配符),大约需要30秒:
where login like '12345678'
使用'explain'我得到:
当对同一个查询使用'='时,大约需要0.1秒:
where login ='12345678'
使用“explain”我得到:
如您所见,类似的操作完全取消了索引seek,因此查询花费了300多倍的时间。
取决于数据库系统。
通常没有特殊字符,是的,=和LIKE是一样的。
但是,某些数据库系统可能会对不同的操作符使用不同的排序设置。
例如,在MySQL中,字符串上的=默认情况下总是不区分大小写的,所以没有特殊字符的LIKE是一样的。在其他一些RDBMS上,LIKE是不区分大小写的,而=则不是。
要解决最初关于性能的问题,可以归结为索引利用率。当进行简单的表扫描时,“LIKE”和“=”是相同的。当涉及到索引时,这取决于LIKE子句是如何形成的。更具体地说,通配符的位置是什么?
考虑以下几点:
CREATE TABLE test(
txt_col varchar(10) NOT NULL
)
go
insert test (txt_col)
select CONVERT(varchar(10), row_number() over (order by (select 1))) r
from master..spt_values a, master..spt_values b
go
CREATE INDEX IX_test_data
ON test (txt_col);
go
--Turn on Show Execution Plan
set statistics io on
--A LIKE Clause with a wildcard at the beginning
DBCC DROPCLEANBUFFERS
SELECT txt_Col from test where txt_col like '%10000'
--Results in
--Table 'test'. Scan count 3, logical reads 15404, physical reads 2, read-ahead reads 15416, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
--Index SCAN is 85% of Query Cost
--A LIKE Clause with a wildcard in the middle
DBCC DROPCLEANBUFFERS
SELECT txt_Col from test where txt_col like '1%99'
--Results in
--Table 'test'. Scan count 1, logical reads 3023, physical reads 3, read-ahead reads 3018, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
--Index Seek is 100% of Query Cost for test data, but it may result in a Table Scan depending on table size/structure
--A LIKE Clause with no wildcards
DBCC DROPCLEANBUFFERS
SELECT txt_Col from test where txt_col like '10000'
--Results in
--Table 'test'. Scan count 1, logical reads 3, physical reads 2, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
--Index Seek is 100% of Query Cost
GO
--an "=" clause = does Index Seek same as above
DBCC DROPCLEANBUFFERS
SELECT txt_Col from test where txt_col = '10000'
--Results in
--Table 'test'. Scan count 1, logical reads 3, physical reads 2, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
--Index Seek is 100% of Query Cost
GO
DROP TABLE test
当使用"="和"LIKE"时,在查询计划的创建中也可能存在可以忽略不计的差异。
实际上,这取决于您希望查询做什么。如果你的意思是精确匹配,那么使用=。如果你的意思是一个模糊匹配,那么使用LIKE。对代码来说,表达自己的意思通常是一个很好的策略。
LIKE和=是不同的。LIKE是在搜索查询中使用的。它还允许像_(简单字符通配符)和%(多字符通配符)这样的通配符。
如果你想要精确匹配,应该使用=,它会更快。
这个网站解释了LIKE