记一次sql优化

今天做系统性能优化时，注意到一个请求响应时间竟然达到了7秒，排查发现是后台一个比较复杂的sql语句造成的，虽然这个语句只查出了仅仅12条结果集。

刚接触这个sql语句，很容易让人畏惧。

首先语句很长，初看一眼并不能知道它在做什么，我也不知道这个查询的需求是什么，并且这个查询还是前不久已经被另外一位同事优化过了的，所以这让人在心理上便不太愿意去尝试优化它。

一

硬着头皮，以我现有的sql编写能力和优化技巧对该sql做了一遍优化后，执行时间由超过7秒减少至不到3秒，虽然整体性能性能提升一倍多，但显然这个执行时间还是太长，我得想办法再优化。

经过一番研究，我发现最耗费时间的地方就在于一个对人员id转义的子查询，这个语句一旦去掉后，整体执行时间立刻降为不到0.1秒，也就是说对于这个语句来说有大概97％的执行时间都花费在了这个子查询上。

那么这个子查询为何如此慢呢？

经过分析，这个语句为了：把用逗号拼接的用户id的字符串转义为用逗号拼接的用户名的字符串，它里面对于索引字段(id)使用了oracle函数(instr)从而造成对用户表的全表扫描，而这个用户表又十分大(几万条数据)，所以执行时间就变得十分长了。

知道问题出在全表扫描了，接下来就是思考如何解决它。

我认为有两种思路，一是不对该字段使用函数的方式而采用其他sql语法来转义，二是该字段的转义不在同一个sql里完成，可以先返回id的结果集，再通过后台程序访问数据库，挨个查询每个用户id(当然重复id只需要查一次)来转义并用逗号拼接起来。

这两种思路本质上是一回事，都是为了避免全表扫描，而是用上索引查询。只不过前一种对于sql编写难度比较大，后一种虽然后台可控制不重复查询但由于访问数据库次数较多，预测时间可能会稍长。

但不过无论采用哪种都会比原先的函数转义快很多。

最终，对于第一种思路我没能找到合适的编写方式，所以只尝试了第二种方式，执行时间由3秒降到了大概0.3秒。

终上，本次优化，我成功的把一个原本执行需要7秒多的查询优化到只需要0.3秒。

二

经过此番sql的梳理分析和优化，有以下收获：

1.初步接触了sql执行计划的使用，这是一个分析sql执行效率和指导优化的绝好工具。

2.加深了几个oracle函数的印象，listagg within group,instr ,substr ,exists ,replace等等

3.函数的使用会导致原本加在字段上的索引失去效果，从而使查询不再走索引而是全表扫描，当表的数据比较大时就会使查询时间显著变长。

4.sql常见的优化思路：尽量少做重复的查询和不需要的查询。例如通常应该遵循内层语句不做转义只查外键等重要信息而在最外层统一查询其余字段。并且排序分组等也要尽量放在最外层。

5.数据库尽量不要设计为某个字段用逗号隔开拼接字符串的方式来存储一对多的数据。一旦要对该字段进行转义时，会很不方便，容易造成性能低下。

最后，对于本次优化工作总结。

首先，对于sql优化有两点技巧

1.尽量少做重复的事以及不要做无用的事。这也是吴军讲的提升效率的底层指导思想。(这对应到本次优化的7秒到3秒)

2.索引是提升查询效率的绝好工具，用与不用会有极大差别。(这对应到那3秒到0.3秒)

然后，对于写的复杂的很长的sql语句来说，很大程度上优化难度不在于它本身，而在于我自己对它的畏惧，一旦我在心理上排斥做一件事，那么行动上便更加无法使出全力了。