MySQL 优化实战记录

阅读本文大概需要 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

 

 

往期精彩回顾

程序员接私活的7大平台利器

Java程序员的成长之路

白话TCP为什么需要进行三次握手

Java性能优化的50个细节(珍藏版)

设计电商平台优惠券系统

一个对话让你明白架构师是做什么的?

教你一招用 IDE 编程提升效率的骚操作!

送给程序员们的经典电子书大礼包

已标记关键词 清除标记
相关推荐
<p> <strong><span style="font-size:20px;color:#FF0000;">本课程主要针对计算机相关专业的正在做毕设的学生与需要项目实战练习的Java学习者</span></strong> </p> <p> <span style="color:#FF0000;"><strong><span style="font-size:18px;">1. 包含:<span style="color:#FFFF00;background-color:#FF0000;">项目源码、</span><span style="color:#FFFF00;background-color:#FF0000;">项目文档、数据库脚本、软件工具</span>等所有资料</span></strong></span> </p> <p> <span style="color:#FF0000;"><strong><span style="font-size:18px;">2. 手把手的带你从零开始部署运行本套系统</span></strong></span> </p> <p> <span style="color:#FF0000;"><strong><span style="font-size:18px;">3. 该项目附带的源码资料可作为毕设使用</span></strong></span> </p> <p> <span style="color:#FF0000;"><strong><span style="font-size:18px;">4. 提供技术答疑和远程协助指导</span></strong></span><strong><span style="font-size:18px;"></span></strong> </p> <p> <br /> </p> <p> <span style="font-size:18px;"><strong>项目运行截图:</strong></span> </p> <p> <strong><span style="font-size:18px;">1)系统登陆界面</span></strong> </p> <p> <strong><span style="font-size:18px;"><img src="https://img-bss.csdn.net/202002241015433522.png" alt="" /><br /> </span></strong> </p> <p> <strong><span style="font-size:18px;"><strong><span style="font-size:18px;">2)学生模块</span></strong></span></strong> </p> <p> <strong><span style="font-size:18px;"><img src="https://img-bss.csdn.net/202002241015575966.png" alt="" /></span></strong> </p> <p> <strong><span style="font-size:18px;"><strong><span style="font-size:18px;">3)教师模块</span></strong></span></strong> </p> <p> <strong><span style="font-size:18px;"><img src="https://img-bss.csdn.net/202002241016127898.png" alt="" /></span></strong> </p> <p> <strong><span style="font-size:18px;"><strong><span style="font-size:18px;">4)系统管理员</span></strong></span></strong> </p> <p> <strong><span style="font-size:18px;"><img src="https://img-bss.csdn.net/202002241016281177.png" alt="" /></span></strong> </p> <p> <strong><span style="font-size:18px;"><img src="https://img-bss.csdn.net/202002241016369884.png" alt="" /></span></strong> </p> <p> <strong><span style="font-size:18px;"><br /> </span></strong> </p> <p> <strong><span style="font-size:18px;"><strong><span style="font-size:18px;">更多Java毕设项目请关注我的毕设系列课程 <a href="https://edu.csdn.net/lecturer/2104">https://edu.csdn.net/lecturer/2104</a></span></strong></span></strong> </p> <p> <strong><span style="font-size:18px;"><br /> </span></strong> </p>
©️2020 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页
实付 9.90元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值