哪个更快 — INSTR 还是 LIKE？

Question

如果您的目标是测试一个字符串是否存在于MySQL列中（类型为'varchar'，'text'，'blob'等），以下哪种方法更快/更有效/更好使用，为什么？

或者，是否有其他方法可以超越这两种方法？

INSTR( columnname, 'mystring' ) > 0

对比

columnname LIKE '%mystring%'

- Grekker

---

如果您的文本字符串很长，启用全文搜索索引可能会更快。 - kibibu

使用 column regexp 'mystring' 通常比 like 更快。 - Seth

你的问题在 [tag:like-operator] 标签下至少有 5 票。能否请您建议将 [tag:sql-like] 作为其 synonym？ - Kermit

作为对下面“REGEXP”的替代方案，您也可以尝试使用POSITION('mystring' in columnname) > 0。 - Takedasama

Answer 1

如kibibu在上面的评论中所指出的那样，使用全文搜索绝对会更快。

然而：

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)

在我的测试中，它们的性能表现完全相同。它们都是不区分大小写的，并且通常执行完整表扫描，在处理高性能MySQL时不推荐使用。

除非您正在对已索引列进行前缀搜索：

mysql> select COUNT(ID) FROM table WHERE Name LIKE 'search%';
+-----------+
| COUNT(ID) |
+-----------+
|         7 | 
+-----------+
1 row in set (3.88 sec)

如果只使用后缀通配符的LIKE语句会更快。

- razzed

---

+1 点赞你实际进行了这个实验！太多的表现“智慧”都是基于直觉。 - kibibu

巨大的改进，思路非常明显。非常感谢！ - wfolkerts

Answer 2

MySQL - 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;

- pdolinaj

---

我重复进行这些测试，看到了类似的结果！ - Ragen Dazs

但是从表中选择可能会有所不同，特别是如果您有一个索引并且查询是 LIKE 'foo%'，那么速度比 INSTR 更快。 - endo64

Answer 3

在这种情况下，即存在“前通配符”的情况（即“LIKE'%...'”谓词），INSTR和LIKE的执行效果应该大致相同。

当通配符不是“前通配符”时，LIKE方法应该更快，除非通配符的选择性不太高。

为什么通配符的类型和选择性很重要的原因是，具有INSTR()的谓词将系统地导致表扫描（SQL不能做出关于INSTR语义的任何假设），SQL可以利用其对LIKE谓词语义的理解来可能使用索引，以帮助它只测试一组减少了可能匹配的数据。

如问题本身下的评论所建议的那样，全文索引会更快。差异取决于文本中单词的特定分布以及整个表的大小等等，但期望速度可能是两倍甚至十倍之间。

使用全文索引的一个可能的缺点，除了创建此类索引的一般开销外，还有一个问题，即除非非常小心地配置此索引（例如，定义停用词列表，使用特定的搜索语法以避免屈折形式等...），FullText提供的结果可能与预期不同。例如，在搜索“SAW”（一种切割木材的工具）时，可能会得到包含动词“看”的各种变形形式的记录的许多命中。
当然，这些全文索引的语言意识特性通常可以被覆盖，同时也可以考虑这些特性实际上是一种优势，而不是缺点。我在这里提到这一点，因为我们正在将其与普通通配符搜索进行比较。

- mjv

Answer 4

很少需要添加到razzed的测试中。但是显然使用regexp会增加更重的处理负载，而不像Seth在他的评论中指出的那样。

以下测试假定您在my.ini中将query_caching设置为On。

query_cache_type = 1
query_cache_size = 64M

测试

• 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 , 30
  

  Initial: 0.0035s
  Cached: 0.0005s

• REGEXP

  SELECT * FROM `domain_model_offers` WHERE `description` REGEXP 'inform' LIMIT 0 , 30
  

  Initial: 0.01s
  Cached: 0.0004s

结果

LIKE 或 INSTR 的速度肯定比 REGEXP 快。

尽管差别很小，但缓存时间的差异可能足以值得进一步调查。

在一个可能配置良好的MySQL系统上，全文索引通常应该比非索引搜索更快或至少与其相当。因此，在长篇人类语言文本中使用索引，无论间歇标记代码如何。

- Lorenz Lo Sauer