一次ISV诉求引出的SQL优化步骤的分享 _SQL优化

我自横刀向天笑，去留肝胆两昆仑。这篇文章主要讲述一次ISV诉求引出的SQL优化步骤的分享相关的知识，希望能为你提供帮助。
目录环境症状问题原因解决方案环境系统平台：中标麒麟（CPU海光）7 版本：4.3.4.8 症状近期ISV反馈同一个web分页查询功能在开发环境速度很快几乎是1秒左右，而生产服务器则执行需要14秒左右。反差挺大，应ISV的要求对该问题进行了分析。
问题原因经过分析问题原因有以下几个：

开发环境与生产环境的架构不同，开发环境是x86的，而生产环境是国产架构，国产的性能比起x86架构还是差一点；
开发环境是同一网段的局域网，而生产环境则是比较复杂的网络结构（如下图所示）；
数据库参数work_mem需要调优；
SQL语句存在嵌入式子查询。SQL如下：

SELECT a.qymc, a.zzjgdm, d.sfzybhpz, d.sftgyzxpj, d.sfzc, d.sfytgcfhc, c.yppzwh, c.gcjkbs, c.sfjy, c.sfotc, c.sfgjmyghcp, c.sfdxyp, c.sfhdxyp, c.sfmzyp, c.sfjsyp, c.sfhjsyp, c.dydjsypfl, c.sfsxfj, c.sfhxfj, c.sffsxyp, c.sfhfsxyp, c.sfyzdyp, c.sfhyzdyp, c.sf, c.zcfl, c.yppzwhyxq, c.yplb, c.yptymc, c.jx, c.gg, c.pzrq, c.sfcfy, c.sfhmzyp, c.ssxkcyr FROM cppt_gy a INNER JOIN cppt_gyjcsjgxb b ON a.id=b.qyid INNER JOIN cppt_jcsjtm c ON b.yppjxxid=c.jcsjid LEFT JOIN (SELECT * FROM cppt_scfyxx WHERE jgdqyx=1) d ON d.yppzwh=c.yppzwh WHERE b.zt=1 ORDER BY a.qymc,c.yppzwh limit 10 OFFSET 0;

服务器拓扑图：

文章图片
解决方案针对这四点的解决方案如下：

芯片和操作系统我们无法改变；
根据上面的网络拓扑图，可以看出中间经过了防火墙，可以从执行SQL或运行web应用的主机上使用路由跟踪命令（tracert或traceroute）进行跟踪，或者ping命令测试相应时间。经过测试，生产环境经过了6，7个设备，同时ping命令的响应时间也不同，2秒左右。经过运维团队的最大优化，只提升了一点点；
数据库参数work_mem的默认值是4M，修改为8M，单独测试SQL，count的执行速度上去了，但是查询数据的速度不但没加快，反而变慢了。改为16M速度提升较大，但work_mem不宜调的这样大，因为每个连接的SQL操作进程都会占用16M的内存，对于内存小的服务器很显然不可取。实践证明，默认的4MB足够用了；
最后就要对SQL进行优化了，一般来说尽量少用子查询。将子查询“(SELECT * FROM cppt_scgyxx WHERE jgdqyx=1)”使用with来代替，代替后的SQL如下：

with d as (SELECT sfzybhpz,sftgyzxpj,sfzc,sfytgcfhc,yppzwh FROM cppt_scfyxx WHERE jgdqyx=1) SELECT a.qymc, a.zzjgdm, d.sfzybhpz, d.sftgyzxpj, d.sfzc, d.sfytgcfhc, c.yppzwh, c.gcjkbs, c.sfjy, c.sfotc, c.sfgjmyghcp, c.sfdxyp, c.sfhdxyp, c.sfmzyp, c.sfjsyp, c.sfhjsyp, c.dydjsypfl, c.sfsxfj, c.sfhxfj, c.sffsxyp, c.sfhfsxyp, c.sfyzdyp, c.sfhyzdyp, c.sf, c.zcfl, c.yppzwhyxq, c.yplb, c.yptymc, c.jx, c.gg, c.pzrq, c.sfcfy, c.sfhmzyp, c.ssxkcyr FROM cppt_gy a INNER JOIN cppt_gyjcsjgxb b ON a.id=b.qyid INNER JOIN cppt_jcsjtm c ON b.yppjxxid=c.jcsjid LEFT JOINd ON d.yppzwh=c.yppzwhWHERE b.zt=1 ORDER BY a.qymc,c.yppzwh limit 10 OFFSET 0;

使用优化后的SQL响应时间缩减很明显，而且很稳定。web响应时间为1s左右，基本上与开发环境相差无几。

修改work_mem与SQL优化的响应时间统计如下图所示：