mysql的like查询优化?

相信大家都知道like的条件左边有%会导致索引失效，今天同事说了几个方法可以优化like的查询，对此非常怀疑，于是实测了一下。结果还真有点出乎意料。

References:

• https://blog.csdn.net/zuihongyan518/article/details/81131042
• https://stackoverflow.com/questions/7499438/mysql-like-vs-locate
• https://stackoverflow.com/questions/2451608/which-is-faster-instr-or-like/2451665
• https://dev.mysql.com/doc/refman/8.0/en/information-functions.html#function_benchmark
• https://www.zhihu.com/question/52718330

那么废话不多说，看下测试结果，测试数据为360W条。

• like查询：1.145s
  
• locate查询：1.032s
  
• instr查询：1.025s
  

综上，like确实垃圾。但其他方法也不咋地，贴一下sf上一位小哥的测试：

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

所以结论是instr > locate > like >> regexp。
不过这又如何呢，数据少的时候差不了多少，数据多的时候也差不了多少，正如一位老哥所说

Trying to decide between a cloth or plastic adhesive bandage is kind of silly, when you're trying to patch the hull of the Titanic.

知乎@Andy Young分享的优化方法

新增一列，存储该字段的反转。比如原字段是abcd，取反存储为dcba，查询%bcd改成查dcb%。仅供参考。

不失为一种非常trick的解决方式
但是真有刚需的话，上全文索引吧，lucene、solr这些等着你呢。

顺便的，这次测试的数据我傻傻的找了一个生成网站搞了这么多数据，没想到BENCHMARK函数一行解决，泪目。