MySQL 哪个更快——INSTR 还是 LIKE？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2451608/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Which is faster — INSTR or LIKE?
提问by Grekker
If your goal is to test if a string exists in a MySQL column (of type 'varchar', 'text', 'blob', etc) which of the following is faster / more efficient / better to use, and why?
如果您的目标是测试 MySQL 列（类型为“varchar”、“text”、“blob”等）中是否存在字符串，以下哪个更快/更有效/更好用，为什么？
Or, is there some other method that tops either of these?
或者，是否有其他方法可以超越其中任何一个？
INSTR( columnname, 'mystring' ) > 0
vs
对比
columnname LIKE '%mystring%'
回答by razzed
FULLTEXT searches are absolutely going to be faster, as kibibu noted in the comments above.
正如 kibibu 在上面的评论中指出的那样，全文搜索绝对会更快。
However:
但是：
mysql> select COUNT(ID) FROM table WHERE INSTR(Name,'search') > 0;
+-----------+
| COUNT(ID) |
+-----------+
| 40735 |
+-----------+
1 row in set (5.54 sec)
mysql> select COUNT(ID) FROM table WHERE Name LIKE '%search%';
+-----------+
| COUNT(ID) |
+-----------+
| 40735 |
+-----------+
1 row in set (5.54 sec)
In my tests, they perform exactly the same. They are both case-insensitive, and generally they perform full-table scans, a general no-no when dealing with high-performance MySQL.
在我的测试中，它们的表现完全相同。它们都不区分大小写，并且通常执行全表扫描，这是处理高性能 MySQL 时的一般禁忌。
Unless you are doing a prefix search on an indexed column:
除非您对索引列进行前缀搜索：
mysql> select COUNT(ID) FROM table WHERE Name LIKE 'search%';
+-----------+
| COUNT(ID) |
+-----------+
| 7 |
+-----------+
1 row in set (3.88 sec)
In which case, the LIKE with only a suffix wildcard is much faster.
在这种情况下，只有后缀通配符的 LIKE 速度要快得多。
回答by pdolinaj
MySQL - INSTRvs LOCATEvs LIKEvs REGEXP
MySQL - INSTRvs LOCATEvs LIKEvs REGEXP
For me the INSTRand LOCATEperformed the fastest:
对我来说，INSTR和LOCATE 的执行速度最快：
# 5.074 sec
SELECT BENCHMARK(100000000,INSTR('foobar','foo'));
# 5.086 sec
SELECT BENCHMARK(100000000,LOCATE('foo','foobar'));
# 8.990 sec
SELECT BENCHMARK(100000000,'foobar' LIKE '%foo%');
# 14.433 sec
SELECT BENCHMARK(100000000,'foobar' REGEXP 'foo');
# 5.5.35-0ubuntu0.12.10.2
SELECT @@version;
回答by mjv
In the case of a "front wilcard" (i.e. a "LIKE '%...'" predicate) as seems to be the case here, INSTR and LIKE should perform roughly the same.
在“前通配符”（即“LIKE '%...'”谓词）的情况下，就像这里的情况一样，INSTR 和 LIKE 应该执行大致相同的.
When the wildcard is nota "front wildcard", the LIKE approach should be faster, unless the wildcard is not very selective.
当通配符不是“前端通配符”时，LIKE 方法应该更快，除非通配符不是很有选择性。
The reason why the type of wildcard and its selectivity matteris that a predicate with INSTR() will systematicallyresult in a table scan (SQL cannot make any assumptions about the semantics of INSTR), whereby SQL can leverage its understanding of the semantics of the LIKE predicate to maybe use an index to help it only test a reduced set of possible matches.
究其原因，为什么通配符的类型及其选择性事情是与INSTR（）谓词将系统地导致表扫描（SQL不能对INSTR的语义任何假设），其中SQL可以利用它的语义理解LIKE 谓词可能使用索引来帮助它仅测试减少的可能匹配集。
As suggested in comment under the question itself, a Full Text index will be much faster. The difference depends on the specific distribution of words within the text, and also the overall table size, etc. but expect anything from twice as fast to maybe 10 times as fast.
正如问题本身下的评论所建议的那样，全文索引会快得多。差异取决于文本中单词的具体分布，以及整体表格大小等，但期望速度从两倍到十倍不等。
A possible downside of using at fulltext index, in addition to the general overhead for creating such an index, is that unless one is very careful in configuring this index (ex: defining the stop word list, using specific search syntax to avoid inflectional forms and the like...), there may be cases where the results provided by FullText will not be as expected. For example, searching for a "SAW" (a tool to cut wood), one may get a lot of hits for records including the verb "to see", in its various conjugated forms.
Of course, these linguistic-aware features of fulltext indexes can typically be overridden and also one may consider that such features are effectively a advantage, not a drawback. I just mention this here since we're comparing this to a plain wildcard search.
除了创建这样一个索引的一般开销之外，使用全文索引的一个可能的缺点是，除非在配置这个索引时非常小心（例如：定义停用词列表，使用特定的搜索语法来避免屈折形式和之类的...），可能存在 FullText 提供的结果与预期不符的情况。例如，搜索“SAW”（一种切割木材的工具），您可能会获得很多记录，包括动词“看”的各种共轭形式。
当然，全文索引的这些语言感知特征通常可以被覆盖，而且人们可能会认为这些特征实际上是一个优势，而不是一个缺点。我只是在这里提到这一点，因为我们将其与普通通配符搜索进行比较。
回答by Lorenz Lo Sauer
There is little to add to razzed's test. But apparently using regexpdoes incur a much heavier processing load, unlike what Sethpoints out in his comment.
对 razzed 的测试几乎没有什么可添加的。但显然使用regexp确实会导致更重的处理负载，这与Seth在他的评论中指出的不同。
The following tests assume that you set query_cachingto Onin my.ini
以下测试假设您在 my.ini 中设置query_caching为On
query_cache_type = 1
query_cache_size = 64M
Tests
测试
The timings show the average performance, out of three measurements (with the cache cleared intermittently):
LIKE
SELECT * FROM `domain_model_offers` WHERE `description` LIKE '%inform%' LIMIT 0 , 30Initial: 0.0035s
Cached: 0.0005sREGEXP
SELECT * FROM `domain_model_offers` WHERE `description` REGEXP 'inform' LIMIT 0 , 30Initial: 0.01s
Cached: 0.0004s
计时显示了三个测量值中的平均性能（间歇性清除缓存）：
喜欢
SELECT * FROM `domain_model_offers` WHERE `description` LIKE '%inform%' LIMIT 0 , 30初始：0.0035s
缓存：0.0005s正则表达式
SELECT * FROM `domain_model_offers` WHERE `description` REGEXP 'inform' LIMIT 0 , 30初始：0.01s
缓存：0.0004s
Result
结果
LIKEor INSTRis definitely faster than REGEXP.
LIKE或者INSTR肯定比REGEXP.
Though minimal, the cache timing difference is probably sufficient to warrant further investigation.
虽然很小，但缓存时间差异可能足以保证进一步调查。
On a probably configured MySQL system, fulltext indexing should generally be always faster or at least on par with a nonindexed search. So use indexing, especially on long human language texts, regardless of intermittent markup code.
在可能配置的 MySQL 系统上，全文索引通常应该总是更快或至少与非索引搜索相当。所以使用索引，特别是在长的人类语言文本上，不管间歇性标记代码如何。
