记一次MySQL性能优化

记一次 MySQL 性能优化

最近接到反馈，客户端偶尔出现接口异常。通过初步的日志排查发现是MySQL的问题，于是针对这次的异常对MySQL进行了一次性能问题追踪和优化。事情搞定后就一直想写一篇总结记录下追踪的过程以及优化的思路，最后在磨蹭了一个星期后有了这遍笔记。（ 笔记里的数据表名和数据都为事后在本地模拟，可能难以反映出线上当时的真实耗时情况 ）

首先说一下问题的状况：客户端反应的状况是 mysql间歇性连接超时。 问题并不能明显地反馈在某个 sql 上，通过查看监控日志，发现 内存间歇性飙升。

最开始怀疑是定时任务的问题，因为业务在后台有很多定时任务，其中不乏对数据库大量数据做聚合操作的任务，有一些任务在实现时没有考虑其对性能的影响，往往会产生间歇性的数据库性能不稳定。但是通过观察发现这次内存飙升的时间间隔并不固定，初步排除是定时脚本的问题。

会不会是慢SQL的问题，由于项目迭代开发速度的问题缺乏很好的质量检测，一般情况下，这个问题是最常出现的性能问题，这次也不例外，第一时间将慢SQL日志调出来查看。然后。。。嗯？这次的慢SQL有点多啊，并且有点繁杂，反应出来就是其中某个表   partner  ,跟这个表相关的sql全部被标记为慢SQL。渐渐意识到问题有点严重呀！

然后随便拿出一条慢SQL执行，嗯? 8ms，这是啥慢SQL,这已经要比数据库的绝大多数哦SQL快了。索性在有一次问题复现时根据当时的 [show processlist]() 反馈，发现有会话数量很不稳定，忽高忽低，在内存飙升的延后几秒中内往往会有大量的进程。在案发现场，定位到一个条可疑SQL，(根据进程的状态、执行SQL的时间)。拿出来执行一下，发现确实是慢SQL，然后怀疑是当前内存不足的原因导致的慢SQL，于是将这条SQL拿到写数据库中执行，发现依旧是很慢，于是定位到一个问题，具体是不是它导致了全部问题需要先优化了它再分析，保存了当时的processlist后对这条sql进行优化。

先来看一下这条SQL： select uid from partners where id in (1,2,3,"4","5",...)

SQL的结构很简单，就是根据id搜索partners表的uid，其中uid和id都有索引，id为主键。问题SQL找到了下面就是SQL优化三板斧的工作了。

先进一步通过 profiles 来进一步定位问题。

可以很清楚的看到，语句在Sending data模块消耗太多时间，并且进行了大量的IO操作。sending data表示收集+发送数据，通常产生的情况有一下几种：

1. 存在大字段或返回数据量过大导致数据传输过慢。
2. sql可能没有走索引，扫了大量数据，从大量数据中找这一条记录。
3. 数据库服务器网络问题。

由于SQL返回结果只有1条，且不存在大字段，很容易定位到是数据收集阶段的问题，于是怀疑到了索引上。

再使用 explain或desc 看一下执行计划：

发现并没有使用主键也就是id索引。于是问题已经很明显了。最后给出优化方案。

在Mysql中id字段为int型，where条件中使用 in(1, 2, 3) 是可以命中索引的，使用 in("1","2","3") 通过mysql优化器的优化为int型后也可以命中索引，但当两者混用时，mysql不会对它进行优化，导致索引失败。后面就是业务层的事情了。

友链： https://www.yuque.com/threads...