解决 MySQL 占用 CPU 过高的问题

问题描述

一项业务最近有不少用户打电话，说部分功能反应很慢，甚至慢到无法使用。

登录阿里云，在云监控功能的主机监控部分，查看 1 小时内的 CPU 使用率，发现时不时地就会飙到 100%，切换指标后，发现主要是被 cpu_user 占用了，也就是被非系统进程所占用。

同时查看内存占用，发现没有明显变化。

再查看磁盘指标，发现读写字节数持续在 40MB/s 左右，平时这个指标则不到 20。读写请求数持续在 3000 左右，平时这个指标则不到 1000，说明磁盘读写频繁。

再查看网络指标，发现在 CPU 占用高的时间段，各项网络指标也正常。

然后查看进程监控部分，查看最近 7 天的数据，依次点击各个进程查看资源占用，发现 MySQL 占用 CPU 很高，它的 CPU 使用率和打开文件数如下所示。

然后远程桌面登录阿里云服务器，对任务管理器中的进程按 CPU 使用率从高到低排列，持续观察一段时间之后，进一步确定是 mysqld.exe 这个进程占用了大量的 CPU。

最开始想的是能不能先限制 MySQL 的 CPU 占用，然后再慢慢解决问题。

Google 了 limit mysql cpu usage，参照着搜索结果中的第一个链接 Mysql process goes over 400% of CPU usage 的最高票回答，检查了一下 MySQL 的配置文件，发现配置是没有问题的，那就说明是使用 MySQL 的方法不正确。

除此之外，没有看到有什么方法可以从系统或软件层面直接限制 MySQL 的 CPU 占用，这条路走不通，那就换个思路。

再 Google mysql high cpu usage，几篇文章都提到了可以执行 show processlist 来查看 MySQL 当前正在执行的查询，从而找出是哪些查询大量占用 CPU。

进入 MySQL 安装目录下的 bin 子文件夹，执行：

.\mysqladmin.exe -uroot -p**** processlist，就可以看到 MySQL 中当前正在执行的 SQL 语句及语句用时。

因为不知道 MySQL 中各个用户的密码，所以参考查看Navicat已保存数据库密码中的方法，先拿到了 root 用户的密码。

show processlist 命令的说明见文章 MySQL CPU占用超过100% 及 MySQL high CPU usage。

多次执行该命令，发现语句都显著超过了 1 秒钟（输出结果中的 Time 列的单位为秒），普遍十几秒到几十秒，有的还达到了几分钟之久，难怪用户纷纷打电话投诉。

查看了业务相关的数据库，用下面两行代码来查看每张表的行数和索引情况。

SELECT COUNT(*) FROM db.table;
SHOW INDEX FROM db.table;

查询之后，确认有 5 张表的数据是十万级的，还有 5 张表的数据是万级的，剩下几张表，最多的也就是一两千行数据，更少的像是临时表，只有几条或十来条数据。

以上这些表，都是只给主键添加了索引，但是业务中绝大多数查询并不是查主键，所以导致查询效率低。

经过对业务代码进行梳理，并测试了各部分 SQL 语句的用时之后，确定了其中几张最常用也是数据量最大的表，是必须加索引的，而且对各类 SQL 语句的执行结果用时进行了截图保存，以便对比。

然后给这些数据表加上了索引，再执行之前那些比较耗时的 SQL 语句，果然飞快！基本上都是 0.0X 秒，更快的则是 0.00 秒。体验了一下相关的业务，的确也快多了，很棒啊！

按照文章 MySQL慢查询&分析SQL执行效率浅谈中的方法，在阿里云服务器上通过 Navicat 运行 MySQL 的命令行界面（可以窗口最大化，比 MySQL 自带的好用），然后依次输入如下命令，开启并设置 MySQL 的慢查询日志功能。

set long_query_time=1;
set global slow_query_log='ON';
set global log_queries_not_using_indexes=’ON’;

第一行的命令将慢查询的标准设置为 1 秒，超过该时长的查询都视为慢查询进行记录。第二行的命令很简单，全局开启 MySQL 的慢查询日志功能。第三行命令则对于未使用索引的查询也进行记录，至于是记录所有查询，还是只记录时长超过 long_query_time 的查询，还需要进一步确认。

而下面两行命令，则可以查询更新后的慢查询相关设置，包括日志文件的物理位置。

show variables like 'long%';
show variables like 'slow%';

重启服务器之后，发现以上设置又恢复到了默认状态，看来需要把这些设置写入 MySQL 的配置文件 my.ini 中才行。

TODO: 具体的操作，参考官方文档的 5.4.5 The Slow Query Log 这一节来做就行。后续需要继续优化业务的话，开启慢查询日志即可。