搞定MySql的字符串模糊查询 - 哈喽比特

2794次阅读 | 发布于5年以前

最近开源了一个几年前写的在线阅读源代码的网站，参考Android OS源代码在线阅读开源了，其中，搜索功能只是简单的使用文件名进行模糊匹配搜索，刚开发时以为以Android源代码的量级，处理搜索就是一个sql like语句就可以搞定，等数据入库后，发现总量数据居然达到了近500万条（10几个版本的代码，API版本从8~22）。搜索的性能问题就暴露出来了。现将方案整理如下：

建立普通索引

建立普通索引后，MySql对字符串的查询基本上没有问题。比如以前当数据库的的量级进行查询，结果如下：

select count(*) from `android_os_search`  WHERE filename like 'Activity.java%'
执行成功，当前返回：[1]行，耗时：[3ms.]

select count(*) from `android_os_search`  WHERE filename like 'ActivityManagerService.java%'
执行成功，当前返回：[1]行，耗时：[111ms.]

select count(*) from `android_os_search`  WHERE filename like 'ActivityManagerServiceTest.java%'
执行成功，当前返回：[1]行，耗时：[3ms.]

但正如查询示例中的SQL所示，为了让索引生效，模糊匹配%只能放在最后面。如果索引失败，查询结果是不可接受的，如下所示：

select * from `android_os_search`  where filename like '%ActivityTT.java%' limit 50 offset 100
执行成功，当前返回：[0]行，耗时：[227464ms.]

select * from `android_os_search`  where filename like '%ActivityT.java%' limit 50
执行成功，当前返回：[0]行，耗时：[242358ms.]

select count(*) from `android_os_search`  WHERE filename like '%Activity.java'
执行成功，当前返回：[1]行，耗时：[229044ms.]

通过这几个查询我样可以得出规律，MySql扫描一次当前表，所用的时间大至为229044ms。约等于4分钟，显示这是无法接受的。

使用全文索引解决问题

为了解决这个问题，针对字段filename建立全文索引。全文索引的工作原理是先分词再插入，然后查询的时候，先分词，再按词精确匹配。所以其实全文索引以后，查询最后是精确查询。

MySql建立全文检索字段，需要将字段所在表的存储引擎设置为MyISAM引擎(5.6以后的版本没有这一要求)，InnoDB的存储引擎不支持全文检索，关于这2个存储的区别，简单说来就是：MyISAM类型不支持事务处理等高级处理但强调的是性能，其执行数度比InnoDB类型更快，而InnoDB提供事务支持以及外部键等高级数据库功能。

建立全文索引后，进行测试，结论如下：

select count(*) from `android_os_search`  WHERE MATCH(filename) AGAINST('+Activity.java')
执行成功，当前返回：[1]行，耗时：[784ms.]

select count(*) from `android_os_search`  WHERE MATCH(filename) AGAINST('+ActivityT.java')
执行成功，当前返回：[1]行，耗时：[781ms.]

select count(*) from `android_os_search`  WHERE MATCH(filename) AGAINST('+ActivityTT.java')
执行成功，当前返回：[1]行，耗时：[769ms.]

时间稍稍有点长，但还能接受。但等等，为什么这次通过select count(*) fromandroid_os_searchWHERE MATCH(filename) AGAINST('+Activity.java')查询到的结果集有399768行之多，而精确匹配下只有14行，加上后匹配（Activity.java%）也只是41行。原来正如之前提到的分词原理，Activity.java被分词为Activity + java,所以所有.java后缀的文件也在结果之中了。

这是一个小问题，使用全文索引的BOOLEAN MODE即可解决：

select count(*) from `android_os_search`  WHERE MATCH(filename) AGAINST('+Activity.java' IN BOOLEAN MODE)
执行成功，当前返回：[1]行，耗时：[4ms.]

此时结果集只有351行了，然后发现时间也只有8ms了，堪称完美。

改进，全文索引下的精确搜索

至此，支持搜索业务的技术点算是解决了，不过，更进一步，如果在基于模糊的搜索之上，再给用户一个精确的搜索机制是否更好？比如已经精确知道了代码的文件名。简单思考后便写下了这样的sql:

select count(*) from `android_os_search`  WHERE filename='Activity.java'

然后问题出现了，全文索引下，这个sql的执行时间要将近1秒，而普通索引，这个查询时间只要0.003秒。相相全文索引的分词机制，也就不难理解了，每一套算法或者机制后面都有具体的问题，很难有一个算法能应对任何问题。理清了原因，解决方法自然就简单了，给filename再建一个普通索引就好。即在当前的表android_os_search中，filename建立了2个索引，分别为普通索引和全文索引，当然，这个解决方法会带来磁盘空间的增加。

总结

搜索是一个很大的话题，本文只是在特定条件下讨论处理问题。过于依赖MySql进行搜索似乎总归不太自由与完美，Lucene等专门的全文检索框架也是不错的选择，而代码的搜索本身不能只依靠文件扣丁书屋，后续开源项目会进一步进行解释。