INSTR和LIKE哪个更快？

Question

如果您的目标是测试字符串是否存在于MySQL列中(类型为'varchar‘、'text’、'blob‘等)，那么使用以下哪一项更快/更高效/更好，为什么？

或者，有没有比这两种方法更好的方法呢？

INSTR( columnname, 'mystring' ) > 0

vs

columnname LIKE '%mystring%'

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要快得多。

Answer 2

MySQL - INSTR vs LOCATE vs LIKE vs REGEXP

对我来说，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;

Answer 3

在“前通配符”的情况下(即“像'%...'”)谓词)这里似乎就是这样，和LIKE应该执行大致相同的。

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

通配符的类型和它的选择性之所以重要的原因是，带有INSTR()的谓词将系统地导致表扫描(SQL不能对INSTR的语义做出任何假设)，从而SQL可以利用它对LIKE谓词的语义的理解来使用索引来帮助它仅测试一组减少的可能匹配项。

正如问题本身下面的评论所建议的那样，全文索引将比快得多。不同之处取决于文本中单词的具体分布，以及整个表格的大小等，但预计会有两倍到10倍的速度。

除了创建这种索引的一般开销之外，使用at全文索引的一个可能的缺点是，除非在配置这个索引时非常小心(例如:定义停用词列表，使用特定的搜索语法来避免词形变化等)，否则可能会出现fulltext提供的结果不是预期的的情况。例如，搜索"SAW“(一种切割木材的工具)，可能会得到许多记录的命中结果，包括动词"to see"，这些记录具有不同的连词形式。

当然，全文索引的这些语言感知特性通常可以被覆盖，也可以认为这些特性实际上是一个优点，而不是缺点。我只是在这里提到这一点，因为我们将其与普通的通配符搜索进行比较。