文章目录
- 一、背景
- 二、涉及的相关知识
- 三、本人解决思路
- 四、优化中的坑:
一、背景
公司有一个搜索功能,数据量在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啥的代替