mysql like 模糊搜索

Question

问题一 name like '%联想%' 是全表搜索，如何使用索引来优化查询？

            有推荐用mysql 函数  locate/instr

            explain SELECT * FROM product where name like '%联想%';
            explain SELECT * FROM product where LOCATE('联想',name)>0 ;
            explain SELECT * FROM product where instr(name, '联想')>0 ;

            看着解释没什么区别啊，都是用了全表搜索
            
            

问题二 name like '%联想%' 是匹配的 XX联想XX。如何匹配 XX联XX XX想XX，现在是把搜索内容拆分了，name like '%联%' or name like '%想%'

Answer 1

第一个问题: 如果左边有通配符%的话,是无法走索引的,只能走全表扫描,最好的结果是select中只查询索引覆盖的列,比如假设只有names列带索引,select name from product where name like '%联想%',走的是扫描整个索引,比全表扫描要快一点,但是应用很局限,一般不会只查被索引覆盖的列啊.

第二个问题: 这种分词查询简单点就用全文索引, 参考这个MySQL使用全文索引(fulltext index), 如果表超级大,性能差的话, 上搜索引擎,比如solr,Lucene、Sphinx.

Answer 2

一、这三种查询方法区别不大，都没办法优化的，除非做FULLTEXT索引；
二、还可以LIKE '%联%想%'，如果你只要联在想前面的话。

Answer 3

全文索引其实是比较耗损资源的东西，所以在mysql里面能不用就不用，对于网站或者app前端搜索频率比较高的模块建议是使用搜索引擎来处理的，业内用的比较多的ES、solr、Lucene，专业的产品做专业的事情。

Answer 4

like '%联想%', 这种用法是否无法使用索引的，只能用全表扫描的方式进行查询。

我自己本地的mysql测试了一下，100万的数据，有id,name两列，返回100行数据的话响应时间大约0.2秒，速度还可以接收。

mysql的全文索引，同样数据我测试了一下，返回100条数据响应时间0.002秒，速度很快，但返回接比like的方式少了很多，具体原因我也不清楚，根据mysql权威指南这本书的建议，不建议使用mysql的全文索引。

Answer 5

是不是需要考虑搭建自己的搜索引擎，比如Elasticsearch，如果是java编程的话，考虑一下Lucene，第三方的话可以考虑各个云厂商提供的搜索引擎服务。

Answer 6

这种情况，mysql层面没法做更好的优化，考虑搭建自己的搜索引擎更合适，借助sphinx或者Elasticsearch等等。

Answer 7

使用搜索引擎