SQL 'like' vs '=' 性能 [英] SQL 'like' vs '=' performance
问题描述
这个问题围绕着我的疑惑，但答案并没有完全解决它.
This question skirts around what I'm wondering, but the answers don't exactly address it.
在使用通配符时，一般来说 '=' 似乎比 'like' 更快.这似乎是传统智慧.但是，假设我有一列包含有限数量的不同固定、硬编码、varchar 标识符，并且我想选择与其中一个匹配的所有行:
It would seem that in general '=' is faster than 'like' when using wildcards. This appears to be the conventional wisdom. However, lets suppose I have a column containing a limited number of different fixed, hardcoded, varchar identifiers, and I want to select all rows matching one of them:
select * from table where value like 'abc%'
和
select * from table where value = 'abcdefghijklmn'
'Like' 应该只需要测试前三个字符来找到匹配，而 '=' 必须比较整个字符串.在这种情况下，在我看来，在所有其他条件相同的情况下，喜欢"会占优势.
'Like' should only need to test the first three chars to find a match, whereas '=' must compare the entire string. In this case it would seem to me that 'like' would have an advantage, all other things being equal.
这是一个一般性的学术问题，因此与哪个数据库无关，但它是使用 SQL Server 2005 产生的.
This is intended as a general, academic question, and so should not matter which DB, but it arose using SQL Server 2005.
推荐答案
引自:
LIKE 索引使用规则大致是这样的:
the rules for index usage with LIKE are loosely like this:
如果您的过滤条件使用 equals =并且该字段被索引，那么大多数可能会使用 INDEX/CLUSTERED索引搜索
If your filter criteria uses equals = and the field is indexed, then most likely it will use an INDEX/CLUSTERED INDEX SEEK
如果您的过滤条件使用 LIKE，没有通配符(就像你有一个网络报告中的参数可以有一个 % 但你改为使用完整的字符串)，它的可能性与#1 一样使用索引.增加的成本几乎没有.
If your filter criteria uses LIKE, with no wildcards (like if you had a parameter in a web report that COULD have a % but you instead use the full string), it is about as likely as #1 to use the index. The increased cost is almost nothing.
如果您的过滤条件使用 LIKE，但以通配符开头(如在 Name0 LIKE '%UTER') 中要少得多可能会使用索引，但它仍然至少可以执行一个索引扫描索引的全部或部分范围.
If your filter criteria uses LIKE, but with a wildcard at the beginning (as in Name0 LIKE '%UTER') it's much less likely to use the index, but it still may at least perform an INDEX SCAN on a full or partial range of the index.
但是，如果您的过滤条件使用LIKE，但以 STRING FIRST 开头并且在那之后的某处有通配符(如 Name0 LIKE 'COMP%ER')，然后是 SQL可能只是使用 INDEX SEEK 来快速首先找到具有相同的行开始字符，然后看通过这些行进行精确匹配.
HOWEVER, if your filter criteria uses LIKE, but starts with a STRING FIRST and has wildcards somewhere AFTER that (as in Name0 LIKE 'COMP%ER'), then SQL may just use an INDEX SEEK to quickly find rows that have the same first starting characters, and then look through those rows for an exact match.
(还要记住，SQL 引擎仍然可能不会像这样使用索引你期待，取决于什么其他正在您的查询中进行，并且你要加入什么表.这SQL引擎保留权利稍微重写您的查询以获得以它认为最有效的方式提供数据高效，这可能包括INDEX SCAN 而不是 INDEX SEEK)
(Also keep in mind, the SQL engine still might not use an index the way you're expecting, depending on what else is going on in your query and what tables you're joining to. The SQL engine reserves the right to rewrite your query a little to get the data in a way that it thinks is most efficient and that may include an INDEX SCAN instead of an INDEX SEEK)
这篇关于SQL 'like' vs '=' 性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
