一、背景

 公司有一个搜索功能,数据量在150W左右(没有分表,没有做缓存,不知道上一批人怎么想的),现在让我改成多关键词搜索,一次最多可以输入5个关键词。即原来搜索框只能输入一个词,现在可以用逗号或者空格隔开输入多个词进行查询。
数据库为mysql5.7

二、涉及的相关知识

 in和exists的区别和原理;
 mybatis循环语法;
 索引失效原因;
 联合索引最左原则;
 正则表达式

三、本人解决思路

上一批人单个词搜索的sql写的很烂,搜一次居然需要27秒左右。
请添加图片描述
换成多关键词搜索肯定不能走JAVA的循环,1个关键词都搜26秒左右,再来个循环直接超时,只能在sql层面和索引下功夫(union all是业务需要可以忽略)
原来的sql:
在这里插入图片描述
请添加图片描述
 目前在子查询用count函数显示查询出10W条数据,而外查询只有1K条数据。先别管多关键词,试试在搜索一次的基础上优化。分别用in和exists改写原来的sql

请添加图片描述
请添加图片描述
这种情况明显exists快。

但是要是在我后面发现,子查询一堆重复的数据,加了distinct之后才只有几条数据,所以后面我用的是in。

在data_status和item_name2加了联合索引后,直接变成4秒!!但还不够,要继续优化
在这里插入图片描述

 这里说明一下,in是先执行子查询,再执行主查询,然后把得出的结果做笛卡尔积,子查询的数据比较主查询小则用in效率高。
 exists是执行主查询,然后把主查询得出的结果对照子查询where后面的条件是否成立来返回true或false,true则保留数据,false则移除
 总结:要用小表驱动大表。

还有就是in和exists都是可以走索引的,有时数据量占比大了就不走了(百度上说结果占表的20%-30%就不走索引了),真实情况可以用explain + sql语句自己查看。

有了优化思路就可以写多关键词的实现啦,多关键词搜索实现代码如下
在这里插入图片描述
在这里插入图片描述
collection为外面传进来的集合名称,可以加@Param自己定义名字,
如果没用@Param则按照传进来集合的名称命名,如Array、List
index为下标,可以随便命名
item为元素名,就是你循环的那个元素名,跟下面那个value对应
open和close分别为循环完后的内容,首尾要加的东西,我这边是没加
separate是每次遍历元素之间加的分隔符

打印出来的原生sql如下图
在这里插入图片描述
explain一下
在这里插入图片描述
发现主查询走了索引而字查询是全表扫描(意料之中,因为like前置通配符%的话,索引会失效,我根本没在item_name4加索引),如果子查询要是不扫描表那肯定效率会更高。查询许多资料决定用正则比较好一点,直接给我干到0.2秒左右了!感动。
在这里插入图片描述
原生写sql好写,但mybatis那边有点坑,要实现上图的效果,得这样写。首先呢正则匹配的是字符串不能用占位符#,要用$符(因为是字符串所以不怕sql注入),不然会报参数越界的错Parameter index out of range (2 > number of parameters, which is 1)因为#预编译时会把参数部分用一个占位符 ? 代替。
请添加图片描述

四、优化中的坑:

1.联合索引除了要遵守最左原则,还要求数据类型要一致,下图data_status是char类型,写成data_status=1也能实现功能数据库会自动帮你转化类型,但是这样就不走索引。
在这里插入图片描述
在这里插入图片描述
2.mybatis的foreach在用${}拼接字符串时会自动添加的前后空格还有你留有的过行,所以这时候foreach标签开头、结尾和拼接的字符都必须写在同一行,保险起见还可以用sql中的replace去掉所有空格,所以你需要拼接的字符串有空格的话就不能用这个函数咯

#格式:replace(‘字符串’,‘ ’,‘’)。
#例子:replace(<foreach collection="list" item="item" separator="," open="(" close=")"> #{item} </foreach>,' ','')

3.in和exists有重复数据的话要distinct掉,不然子查询数据量大的话in做笛卡尔积和exists做判断都会影响效率,属于低级错误(呜呜呜),主查询看业务需要不要一昧distinct。

4.我在拼接字符串的时候曾经想利用foreach中的index来控制拼接内容,出现了如下图的错误
在这里插入图片描述
在这里插入图片描述
补充一点mybatis的sql不能用大于小于符号,可以转义符或者between啥的代替
在这里插入图片描述

Logo

为开发者提供学习成长、分享交流、生态实践、资源工具等服务,帮助开发者快速成长。

更多推荐