阅读本文大概需要 2 分钟。
背景
本次SQL优化是针对javaweb中的表格查询做的。
部分网络架构图
业务简单说明
N个机台将业务数据发送至服务器，服务器程序将数据入库至MySQL数据库。服务器中的javaweb程序将数据展示到网页上供用户查看。
原数据库设计
1、windows单机主从分离
2、已分表分库，按年分库，按天分表
3、每张表大概20w左右的数据
原查询效率
3天数据查询70-80s
目标
3-5s
业务缺陷
无法使用sql分页，只能用java做分页。
问题排查
前台慢 or 后台慢
1、如果你配置了druid，可在druid页面中直接查看sql执行时间和uri请求时间
2、在后台代码中用System.currentTimeMillis计算时间差。
结论 ： 后台慢，且查询sql慢
sql有什么问题
1、sql拼接过长，达到了3000行，有的甚至到8000行，大多都是union all的操作，且有不必要的嵌套查询和查询了不必要的字段
2、利用explain查看执行计划，where条件中除时间外只有一个字段用到了索引
备注 ： 因优化完了，之前的sql实在找不到了，这里只能YY了。
查询优化
去除不必要的字段
效果没那么明显
去除不必要的嵌套查询
效果没那么明显
分解sql
将union all的操作分解，例如(一个union all的sql也很长)
将如上sql分解成若干个sql去执行，最终汇总数据，最后快了20s左右。
将分解的sql异步执行
利用java异步编程的操作，将分解的sql异步执行并最终汇总数据。这里用到了CountDownLatch和ExecutorService，示例代码如下：
结果又快了20-30s
优化MySQL配置
以下是我的配置示例。加了skip-name-resolve,快了4-5s。其他配置自行断定
根据业务，再加上筛选条件
快4-5s
将where条件中除时间条件外的字段建立联合索引
效果没那么明显
将where条件中索引条件使用inner join的方式去关联
针对这条，我自身觉得很诧异。原sql,b为索引
应该之前有union all，union all是一个一个的执行，最后汇总的结果。修改为
结果快了3-4s
性能瓶颈
根据以上操作，3天查询效率已经达到了8s左右，再也快不了了。查看mysql的cpu使用率和内存使用率都不高，到底为什么查这么慢了，3天最多才60w数据，关联的也都是一些字典表，不至于如此。继续根据网上提供的资料，一系列骚操作，基本没用，没辙。
环境对比
因分析过sql优化已经ok了，试想是不是磁盘读写问题。将优化过的程序，分别部署于不同的现场环境。一个有ssd，一个没有ssd。发现查询效率悬殊。用软件检测过发现ssd读写速度在700-800M/s,普通机械硬盘读写在70-80M/s。
优化结果及结论
优化结果：达到预期。
优化结论：sql优化不仅仅是对sql本身的优化，还取决于本身硬件条件，其他应用的影响，外加自身代码的优化。
小结
优化的过程是自身的一个历练和考验，珍惜这种机会，不做只写业务代码的程序员。希望以上可以有助于你的思考，不足之处望指正。
原文链接：
https://my.oschina.net/xiaozhutefannao/blog/2243432
·END·
程序员的成长之路
路虽远，行则必至
微信ID:cxydczzl
往期精彩回顾
扫一扫
294
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
