百万级sql优化--mysql

一、背景

 公司有一个搜索功能，数据量在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>,' ','')
1
2

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

4.我在拼接字符串的时候曾经想利用foreach中的index来控制拼接内容，出现了如下图的错误


补充一点mybatis的sql不能用大于小于符号，可以转义符或者between啥的代替