一场mysql查询引起的“卡顿” 作者: 灯小笼 时间: 2018-09-03 分类: 架构 一般我们说优化mysql，总是会将着眼点放在mysql的慢查询的优化上，或者字段的数据类型，mysql的分库/分表，读写分离，异或事务这些看上去高大上的东东上。但是，对于一些更加基础或者更加常识的地方，往往会加以忽视。就比如，本文将要提到的：mysql的查询本身的优化。 这次接到的优化任务出现的背景是： 我们有一个内部电话呼叫的系统，在下午使用客服相对较多的时候，在很随机的情况下，会出现突然的卡顿，这种现象维持的时间很短，检查的时候，往往发现cpu、内存、负载、网络，都很正常，查询错误日志也看不到异常。 系统用的是阿里云，2C4G，看负载的曲线也维持得很小很平稳，内存消耗也没有达到瓶颈。中间一度怀疑过对缓存的使用有问题，因为有很多临时数据都是通过cache在传输和保存的，调用频率也很高。于是，负责此项目的同学也做过很多方面的优化，想过很多招数。比如： 1、临时数据的保存从全量改为增量，大大降低了数据传输的量。 2、将数据库改成长连接，减少连接使用量。 原来我们使用的数据库是由阿里云提供的，后来降配了，长连接经常出现连接数不够的时候，又改为普通连接了。最后，我们又把mysql改成了自建，连接数2000，还是够用的。不过，即使使用长连接的时候，依然会出现卡顿，索性，就一直维持着使用普通连接了。 <!--more--> ## 问题浮出水面 最近，这个优化的任务转到我手头了，于是开始系统地对这个系统做了一些检查。各种看参数，也都正常，没有明显的问题。直到用`iftop`检查的时候，问题终于浮出水面来了。 ```bash iftop -n ```  右侧第1列表示2s的平均值，同一个ip对应两行，第1行表示发送，第2行表示接收。 标红的地方，分别对应db(172.30.0.66)和cache(172.30.0.65)。 可以发现，db接收到的流量(21.0M)远大于cache的流量(298K)。 往客户端发送流量大致为：总发送量 3.21M - （db发送量1.59M+cache发送量189k) = 1.44M，而接收的数据量则高达22M，1.44/22=6.5%，也就是仅有6.5%的数据被利用起来了，这个利用率还真是低的可怕。 那么，什么原因导致这样的结果呢？来看一个查询量较大的页面的压测数据： ```bash ab -c 7 -n 1000 -H https://xxxx.com/call/user/lists?leadin_id=1533534509 ... Concurrency Level: 7 Time taken for tests: 61.964 seconds Complete requests: 1000 Failed requests: 0 Total transferred: 251424000 bytes HTML transferred: 251108000 bytes Requests per second: 16.14 [#/sec] (mean) Time per request: 433.745 [ms] (mean) Time per request: 61.964 [ms] (mean, across all concurrent requests) Transfer rate: 3962.51 [Kbytes/sec] received Connection Times (ms) min mean[+/-sd] median max Connect: 23 40 13.3 36 144 Processing: 236 392 61.0 394 560 Waiting: 163 295 54.8 299 447 Total: 276 432 64.4 434 627 ... ``` 这是我们能做到的最大的并发数——7，而此时的`iftop`界面已经让人抓狂不已。  是的，没有看错，mysql数据发送量已经高达145M，1000M带宽已经完全被打满了，想要从mysql接收更多的数据已经不太可能。试想想，如果遇到这样的情况，怎么能不卡顿呢？ ## 找出深层原因 问题找到了，接下来就要找原因了，最简单粗暴的办法就是抓包，看看sql执行的具体情况了。 ```bash tcpflow -i eth0 -cp port 3306 > sql.log ``` 我们抓取了较长时间的sql，用sublime打开，发现大部分都是这样的场景：  咱们来看看划红线的三块： - 典型的`select *`问题，没有对真正要查询的数据字段进行过滤。 - 查询条件过于复杂，当然，这和业务的复杂有关系。 - 数据查询量过大，这当然和第1点有直接的关系。 结合到业务的场景，我们最后形成了如下的结论： 1、没有对返回的表字段加以控制，直接select *，导致单次sql查询返回数据量过大。 2、业务逻辑复杂，同一个页面sql查询次数较多，导致通页面总的查询次数以及返回的数据量过大。 3、没有对一些不常变化的数据加上缓存，导致频繁查用同样的数据。 ## 对症下药 找对了问题，找准了原因，接下来就是对症下药了。这个反而是水到渠成的事情，比前面的环节要简单地多了。我们做了这些针对性地处理： - 优化`select *`语句，只返回必要的字段。 - 缓存部分查询结果，因为中间的过程sql省掉了，因此能减少大量的mysql带宽占用。 - 有的页面没有使用查询条件时，不显示结果，必须加上过滤条件，才查询数据，减少数据集的大小。 ## 开花结果 经过几天的努力，我们的优化成果很快显现出来了。 先来看一张mysql服务器的tcp连接图：  再来看一张对应的mysql服务器的流量图：  我们的优化上线的时间大概在8月9日下午4点左右，上线后，mysql的流量一泻千里，径直从原来的20多M降低到平均5M左右。 下面来做大致的推算。以流量图的流入速率作为基准，选取连接图的对应时刻点的已经established的连接数作为除数，计算单个established的连接数对应的网卡流入速率。 * 未优化前，以8月8日18:00左右计算：19.83M/620 = 32.75k * 再以优化后的8月9日19:30左右计算：5.72M/550=10.65k * 后面继续优化了，以8月10日12:00左右计算：1.91M/305=6.41k | 时间 | 流入速度(M/s) | 绑定连接数(#/s) | 流入流量(k/#) | 降低 | |------------|--------------|----------------|-------------|--------| | 8月8日18:00 | 19.83 | 620 | 32.75 | - | | 8月9日19:30 | 5.72 | 550 | 10.65 | 67.5% | | 8月10日12:00 | 1.91 | 305 | 6.41 | 80.4% | 这个80.4%的优化还是来得相当给力的！！！而从实际的效果来看，后面也没有人再因为网站卡顿的问题而抱怨过了。 ## 结论 优化首先讲究的是对症下药，找准了方向，结合业务场景分析，剖析对了原因，如何解决问题就是水到渠成的事情了。因此，我们也需要掌握一些更加系统更加基础的分析问题的方法和手段，能帮我们更好地驾驭各种问题，提升解决问题的能力。 标签: mysql, iftop
