MySQL 优化实战记录

布满荆棘的人生 2022-01-13 00:15 232阅读 0赞

阅读本文大概需要 2 分钟。

**背景**

本次SQL优化是针对javaweb中的表格查询做的。

**部分网络架构图**

![169d692510e2ea35?w=690&h=441&f=jpeg&s=19115][169d692510e2ea35_w_690_h_441_f_jpeg_s_19115]

### 业务简单说明 ###

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也很长)

![169d692512da5254?w=628&h=167&f=png&s=19676][169d692512da5254_w_628_h_167_f_png_s_19676]

将如上sql分解成若干个sql去执行，最终汇总数据，最后快了20s左右。

![169d692510df6319?w=629&h=30&f=png&s=4310][169d692510df6319_w_629_h_30_f_png_s_4310]

![169d692512ecce5d?w=633&h=35&f=png&s=4423][169d692512ecce5d_w_633_h_35_f_png_s_4423]

#### 将分解的sql异步执行 ####

利用java异步编程的操作，将分解的sql异步执行并最终汇总数据。这里用到了CountDownLatch和ExecutorService，示例代码如下：

![169d6925136859be?w=650&h=978&f=jpeg&s=60576][169d6925136859be_w_650_h_978_f_jpeg_s_60576]

结果又快了20-30s

### 优化MySQL配置 ###

以下是我的配置示例。加了skip-name-resolve,快了4-5s。其他配置自行断定

![169d692512d913c5?w=614&h=2039&f=jpeg&s=190519][169d692512d913c5_w_614_h_2039_f_jpeg_s_190519]

##### 根据业务，再加上筛选条件 #####

快4-5s

##### 将where条件中除时间条件外的字段建立联合索引 #####

效果没那么明显

##### 将where条件中索引条件使用inner join的方式去关联 #####

针对这条，我自身觉得很诧异。原sql,b为索引

![169d69252cdfcda5?w=672&h=28&f=png&s=5021][169d69252cdfcda5_w_672_h_28_f_png_s_5021]

应该之前有union all，union all是一个一个的执行，最后汇总的结果。修改为

![169d6925293266f4?w=639&h=237&f=png&s=18016][169d6925293266f4_w_639_h_237_f_png_s_18016]

结果快了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·**

**程序员的成长之路**

路虽远，行则必至

![169d69252834c1ad?w=258&h=258&f=jpeg&s=18190][169d69252834c1ad_w_258_h_258_f_jpeg_s_18190]

微信ID:cxydczzl

往期精彩回顾

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

[Java程序员的成长之路][Java]

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

[Java性能优化的50个细节（珍藏版）][Java_50]

[设计电商平台优惠券系统][Link 1]

[一个对话让你明白架构师是做什么的？][Link 2]

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

[送给程序员们的经典电子书大礼包][Link 3]

转载于:https://blog.51cto.com/14057963/2372145

[169d692510e2ea35_w_690_h_441_f_jpeg_s_19115]: https://user-gold-cdn.xitu.io/2019/4/1/169d692510e2ea35?w=690&h=441&f=jpeg&s=19115
[169d692512da5254_w_628_h_167_f_png_s_19676]: https://user-gold-cdn.xitu.io/2019/4/1/169d692512da5254?w=628&h=167&f=png&s=19676
[169d692510df6319_w_629_h_30_f_png_s_4310]: https://user-gold-cdn.xitu.io/2019/4/1/169d692510df6319?w=629&h=30&f=png&s=4310
[169d692512ecce5d_w_633_h_35_f_png_s_4423]: https://user-gold-cdn.xitu.io/2019/4/1/169d692512ecce5d?w=633&h=35&f=png&s=4423
[169d6925136859be_w_650_h_978_f_jpeg_s_60576]: https://user-gold-cdn.xitu.io/2019/4/1/169d6925136859be?w=650&h=978&f=jpeg&s=60576
[169d692512d913c5_w_614_h_2039_f_jpeg_s_190519]: https://user-gold-cdn.xitu.io/2019/4/1/169d692512d913c5?w=614&h=2039&f=jpeg&s=190519
[169d69252cdfcda5_w_672_h_28_f_png_s_5021]: https://user-gold-cdn.xitu.io/2019/4/1/169d69252cdfcda5?w=672&h=28&f=png&s=5021
[169d6925293266f4_w_639_h_237_f_png_s_18016]: https://user-gold-cdn.xitu.io/2019/4/1/169d6925293266f4?w=639&h=237&f=png&s=18016
[169d69252834c1ad_w_258_h_258_f_jpeg_s_18190]: https://user-gold-cdn.xitu.io/2019/4/1/169d69252834c1ad?w=258&h=258&f=jpeg&s=18190
[7]: http://mp.weixin.qq.com/s?__biz=MzUyNDkzNzczNQ==&mid=2247485759&idx=1&sn=6189c69534c0f51b0a3a6d1b26f689ff&chksm=fa24f657cd537f41df3b688e63cfc88f6e68cb9262ec07e1418089aa6cdc5d794aeb681e4786&scene=21#wechat_redirect
[Java]: http://mp.weixin.qq.com/s?__biz=MzUyNDkzNzczNQ==&mid=2247486053&idx=1&sn=d3ab081c851786f4c9dbf1b323f187ea&chksm=fa24f50dcd537c1b34253c8e5ad027dfb317c0e7001b0ef48e336c27a17841717a197bb0b0c2&scene=21#wechat_redirect
[TCP]: http://mp.weixin.qq.com/s?__biz=MzUyNDkzNzczNQ==&mid=2247486048&idx=1&sn=fe9d125ee560875239deeafc801a04c8&chksm=fa24f508cd537c1e576e88b1a179fd870a517c40b6526e9ca2cffb02d6991f75439c579e5b2c&scene=21#wechat_redirect
[Java_50]: http://mp.weixin.qq.com/s?__biz=MzUyNDkzNzczNQ==&mid=2247486015&idx=1&sn=c865a942d05aa13e19208ec9deb1f8ef&chksm=fa24f557cd537c418e605a112a08a4125105a80d36ac2b2b29e2ef6f456fb8b7ee5c91e11986&scene=21#wechat_redirect
[Link 1]: http://mp.weixin.qq.com/s?__biz=MzUyNDkzNzczNQ==&mid=2247485996&idx=1&sn=70a1c8de8ee03d91130011494997c6a6&chksm=fa24f544cd537c524e83b4218067241cfc1eb8a5bf9b90fb64d3f13f699d57492fdbc96d8b7f&scene=21#wechat_redirect
[Link 2]: http://mp.weixin.qq.com/s?__biz=MzUyNDkzNzczNQ==&mid=2247485982&idx=1&sn=25a51a789918462326bc3d3d0c39a110&chksm=fa24f576cd537c6038ba1a6f903d38f9aa4c7695e7bee136b649fd2d22d4371e58e1db2fe83b&scene=21#wechat_redirect
[IDE]: http://mp.weixin.qq.com/s?__biz=MzUyNDkzNzczNQ==&mid=2247485965&idx=1&sn=b09bd68122a7a610cc6bcbad0b765ed2&chksm=fa24f565cd537c7393b174170c39fb2d7ba810b4cac1404aefb2360bb6552133e88a32016e44&scene=21#wechat_redirect
[Link 3]: http://mp.weixin.qq.com/s?__biz=MzUyNDkzNzczNQ==&mid=2247485875&idx=1&sn=0801f98619e13eec9db30f218cb4d9af&chksm=fa24f6dbcd537fcd2cf3c36698702c4aecbb2ce18f935a7dfdbf1ab39cc1514f53b546be6ca7&scene=21#wechat_redirect