MySQL模糊查询的那些谣言

声明：此辟谣仅供个人使用，请勿轻易模仿。

 

       最近经常用到模糊查询，本着一名底层程序员的自我修养，当然要去浩瀚的互联网上看看SQL应该怎么写，才不会把服务器搞崩，于是什么like优化，LOCATE性能映入眼帘，整个浏览器都充满了欢乐的气息，可是，后来，我们发现事情没有那么简单。。。。。

 

问题1：使用like模糊查询效率低下，使用LOCATE，POSITION查询效率更高？

验证：

（1）benchmark测试

SELECT BENCHMARK(100000000,'gooohappy' LIKE '%goo%')
> OK
> Time: 3.703s


SELECT BENCHMARK(100000000, LOCATE('goo','gooohappy'))
> OK
> Time: 4.707s

SELECT BENCHMARK(100000000,'gooohappy' LIKE '%ppy%')
> OK
> Time: 7.584s


SELECT BENCHMARK(100000000, LOCATE('ppy','gooohappy'))
> OK
> Time: 15.447s

这里也可以发现，模糊查询的子串在字段中位置越靠后，查询时间越长，这可能跟like和LOCATE的实现算法有关。

MySQL版本8.0.16，此结果与stackoverflow一回答测试结果正好相反。这也是很有意思。

 

（2）实际数据测试

数据有44W条，其实还是有点小，但是真实，先这样测吧

SELECT * from shopinfo2 where shop_addr like '%杭州%'
> OK
> Time: 1.364s


SELECT * from shopinfo2 where LOCATE('杭州',shop_addr)
> OK
> Time: 1.37s

SELECT * from shopinfo2 where shop_addr like '%万达%'
> OK
> Time: 1.048s


SELECT * from shopinfo2 where LOCATE('万达',shop_addr)
> OK
> Time: 1.322s

其中，该字段无索引，杭州一般出现于该字段值的前半部分，万达一般出现于该字段值的后半部分

 

问题2：like '%%'形式不会用到索引，而LOCATE可以使用到索引，所以问题1里LOCATE慢是因为没有索引，加上索引后，LOCATE会比like快？

验证：

给shop_addr字段添加普通索引

SELECT * from shopinfo2 where shop_addr like '%万达%'

SELECT * from shopinfo2 where LOCATE('万达',shop_addr)

可见，LOCATE和like都没有用到索引。

 

附加测试：

SELECT * from shopinfo2 where shop_addr like '万达%'

可见，使用like查询以万达开头的数据，是可以用到刚才创建的索引的。

SELECT * from shopinfo2 where LOCATE('万达',shop_addr) = 1

而使用LOCATE，即便是查询万达开头的数据，也不会用到索引。

有索引
SELECT * from shopinfo2 where shop_addr like '万达%'
> OK
> Time: 0.486s

SELECT * from shopinfo2 where LOCATE('万达',shop_addr) = 1
> OK
> Time: 1.385s

无索引
SELECT * from shopinfo2 where shop_addr like '万达%'
> OK
> Time: 1.157s

SELECT * from shopinfo2 where LOCATE('万达',shop_addr) = 1
> OK
> Time: 1.407s

使用like查询万达开头数据时用到索引，明显提升性能。

 

结论：使用like进行模糊查询的效率并不比LOCATE差，甚至比LOCATE更好；而且在有索引的情况下，like在某些情况下还能使用索引，效率还会更好。