生产环境sql优化日记——从几十分钟优化到几十秒钟

先说结论：不要在sql中使用太多的 left join , 尽可能减少 left join ；或者把逻辑移动到java代码中，可以适当增加sql数量来代替 left join 的逻辑。

2022.07.04

今天，业务反馈了个生产问题，问卷导出报表无法导出。
是一个有30道题的问卷，回答人数36人。
需要导出每个人每道题的答题详情、个人信息等，导出到xls。

查看日志发现，是Java Heap Space，内存不足，用了几十分钟都没有下载成功，浏览器一直转圈。

既然从浏览器无法导出，于是从日志中获取了相关sql，发现是一个行转列sql；由于每个问卷的问题数不相同，因此是sql是动态拼接的，比如这个sql就拼接了30个left join，为了查询出30道题每个人的答案。

（不方便上sql，总之可以设想下，如果用1个sql查询出这36个人、每道题的答案，需要怎么写；就是这样的1个sql）

这个问题之前就出现过，题目越多，回答人数越多，导出就越慢，几分钟到几十分钟是正常现象；
之前遇到时，就硬等几十分钟，凑付下载了报表就算了；
但是这次几十分钟后都不行，就得想办法了。

处理过程：
1.问题是java内存不足导致的（Java Heap Space），因此先尝试了调大tomcat的Xmx的办法；
调整后，内存是够了，但是又报另一个错：连接超时。

2.浏览器访问，等待几十分钟后报错：连接超时；于是从日志中把sql搞了出来，放到Navicat里跑；
几十分钟后，Navicat也报错了：

[Err] Out of memory

这下，直接跑sql的方法也失败了，只能尝试sql优化了。

2022.07.05

总结一下，需求是把有30道题、36人回答的一个问卷的报表，导出成xls；
目前使用了1句行转列sql，有30个left join，是为了把每个人的每道题的答案拼接成一行，结果应该有36行；
但是这句sql无法执行，报错：[Err] Out of memory

优化方法：
1.这句sql很长，30个left join，相当于把答案拼接逻辑都写到sql里了，跑不出来；那就得减少sql长度、把逻辑移动到java里、减轻数据库压力。
2.把这1句sql，拆分成了30句sql，每句sql查询人员id与一道题的答案，每句sql执行后有36行，是36个人回答这道题的答案。
3.java代码里，使用了30个map，用来装查询结果；其中key是人员id，value是答案；然后第一个map是第一道题的答案，第二个map是第二道题的答案，以此类推。
4.java代码里，使用1个List<Map>，有36行，代表36个人；每一个map里用来装一个人员的人员信息、每道题的答案，也就是把第2步里的30个map的答案按人分开、装到这个List<Map>里。
5.然后对List<Map>遍历处理，就可以导出xls报表了。

这样优化后，进行测试，原来几十分钟导不出来的报表，几十秒钟就导出来了。

总结

复杂逻辑应该写到代码里，拆分成多个SQL执行；而不是在1句SQL里堆复杂逻辑。