一次 MySQL 调优经历

最近有一个朋友遇到一个问题,就是突然之间网站的响应时间变成了秒级,而且 MySQL 占用了大量的 CPU 资源。具体如下图:

  • CPU 资源占用:

CPU 资源

  • 响应时间:

响应时间

可以看到这一个很明显的异常值。所以,我就开始排查。一切都是常规的思路,连上服务器,然后看看当前都是些什么任务。

  • 当前任务:

image

可以看到,基本上所有的 query 都是在等待表锁。然后查了下这张表的表结构,还有 select 和 update 的执行情况。

  • 表结构:

表结构

  • SELECT 语句执行情况:

SELECT x FROM xx WHERE ( vote_id = xx ) AND ( token = ‘xx’ ) AND ( wecha_id = ‘x’ ) LIMIT 1 为例

执行情况

可以看到虽然是走了索引,但是依旧是扫描了 3w 多行数据。加上之前等待锁的时间。最后造成的问题就是,这个简单的 select 就已经花了3秒时间。

仔细研究了这几个出现最多的语句,发现这个和他们最近上线的一个投票业务相关。所以这个,可以断定就是有人在刷票。当然,最佳的方案就是,在前端代码加上防刷机制,比如 ip 校验什么的。

但是考虑到当时的情况。改代码这个基本就是不现实的处理方法。因为时间来不及。而且代码是第三方的,修改起来难保全无错误。

所以,这边主要考虑的还是从数据库下手。

因为,MySQL 是支持表级锁和行级锁的,所以这边我们主要是进行锁级的优化,比如把表级锁改成行级锁。完美。这段看到现在,有没有发现我是在一本正经的胡说八道。给位仔细看上面的表结构截图。我们可以看到由于历史遗留问题,这个库依旧采用了 MyISAM 引擎,这就带来了一个很严重的问题。

这货只有表级锁。但是,它支持插入和查询同时进行(有屁用,这个场景就是 update 和 select 频繁)。 所以,又有了一个方案,把这个数据库引擎从 MyISAM 改成 InnoDB。看似完美。

但是,InnoDB 的行级锁是基于索引的。也就是说,他所谓的行级锁,如果在索引不好的情况下,依旧会锁上一大堆数据。比如上述的执行情况来看,在走了索引的情况下,依旧扫描了3w 多行数据。可见这个索引真不咋地。

加索引?这是由多想不开在字符串列加索引,而且还是一个 update 极度频繁的业务场景下。

所以以上方案都被自己否决掉之后,就考虑到大招了。修改隔离级别,关于 MySQL 的隔离级别,建议不清楚的阅读我 2013 年写的文章 《MySQL 锁分析》

这个操作,反复和对方进行沟通,确认无资金交易,和其他重要业务之后,将隔离级别下调至 read-uncommitted

这个操作非常危险,因为它会带来脏读的风险,不过,他确实可以让 select 和 update 并行,虽然 update 之间还是要等待锁。

优化后的当前任务执行情况:

之后

然后就是结果了:

结果

可以看到,最后的下降非常明显。

最后的最后,面对这样的一个常 update 和 select 的场景,有没有好一点的实现解决思路呢?

因为这张表设计的真的不合理。常用来搜索的字段都是 char 型。所以,我最后提出的一个优化方案就是:

  1. 修改引擎为 InnoDB
  2. 增加字段 wechat_id_idx, token_idx,这两个字段都是 int 型,生成的方法就是(以最好的语言 PHP 举例):$token_idx = base_convert( md5($token), 16, 10);,将 char 转换成 int
  3. 基于上述两个字段建立索引

以上就是全部,最后的最后的最后。也是意外之喜,我的油费有找落了。 哈哈。

油费

2 Replies to “一次 MySQL 调优经历”

  1. 校园网加载你博客的站内图片几乎全军覆没…
    另外你博客背景上的鼠标效果和Links…我好像明白了什么….:)

Leave a Reply

Your email address will not be published. Required fields are marked *