MySQL 故障诊断：MySQL 占用 CPU 过高问题定位及优化

CPU 占用过高常见现象
在使用 MySQL 的过程中会遇到各种瓶颈问题，常见的是 IO 瓶颈，但是有时候会出现服务器 CPU 使用率超过 100%，应用页面访问慢，登录服务器负载很高。而导致这个问题竟然是 MySQL 进程，按理说如果 MySQL 运行稳定，服务器的 CPU 资源并不会跑满，如果出现这个问题，初步可以断定，是 MySQL 实例中出现了问题 SQL。

CPU 占用过高常见原因：
服务器硬件问题
内存溢出
业务高并发
如果是业务高并发引起，可以理解为一种业务繁忙的状态，有可能业务猛增，有可能是定期或者临时的并发窗口：

数据库对象设计不合理
触发器导致
表索引设计不合理
数据库锁导致，如行锁冲突、行锁等待、锁超时、死锁等
系统架构没有缓存中间件
读写分离配置不合理
OLTP 系统承载了 OLAP 的业务需求
未合理升级改造为集群环境
未配置异构数据分析系统
MySQL 系统参数设置不合理
问题 SQL 导致
SQL 问题导致 CPU 使用率过高是最常见的现象，比如 group by、order by、join 等，这些很大程度影响 SQL 执行效率，从而占用大量的系统资源。

说了这么多常见原因，其实总结一句话来说就是现有系统的现有配置下的现有环境提供不了所需要的数据查询、分析、执行能力，针对这个问题，首先我们要发现问题的所在，就是说我们要准确的定位问题，然后针对问题进行优化，再考虑其他升级改造的事情。

定位分析问题 SQL
光说不练假把式，接下来我们比划比划。

首先，搭建一套模拟环境，这里我让我的得力助手 DBdeployer 来帮我搭建一套模拟环境。

DBdeployer 是用 Go 语言实现的一款非常强大与高效的部署数据库环境的开源工具，我们完全可以用它来部署开发、测试环境。

使用 DBdeployer 来部署一个单点 5.7.27 版本的 MySQL，并配置一个模拟 CPU 占比高的测试环境。

复制
[root@localhost ~]# dbdeployer deploy single 5.7.27
Database installed in /dbdata/sandboxes/msb_5_7_27
run ‘dbdeployer usage single’ for basic instructions’
… sandbox server started
1.
2.
3.
4.
创建测试表：

复制
[root@localhost ~]# cd /dbdata/sandboxes/msb_5_7_27/
[root@localhost msb_5_7_27]# ./use
mysql [localhost:5727] {msandbox} ((none)) > use test
Database changed
mysql [localhost:5727] {msandbox} (test) > create table t_cpu(id int primary key auto_increment);
Query OK, 0 rows affected (0.39 sec)
1.
2.
3.
4.
5.
6.
插入大量模拟测试数据：

复制
mysql [localhost:5727] {msandbox} (test) > insert into t_cpu values(),(),(),(),();
Query OK, 5 rows affected (0.06 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql [localhost:5727] {msandbox} (test) > insert into t_cpu select id+(select count(*) from t_cpu) from t_cpu;
Query OK, 5 rows affected (0.05 sec)
Records: 5 Duplicates: 0 Warnings: 0

…
…

mysql [localhost:5727] {msandbox} (test) > insert into t_cpu select id+(select count(*) from t_cpu) from t_cpu;
Query OK, 5242880 rows affected (1 min 8.84 sec)
Records: 5242880 Duplicates: 0 Warnings: 0

mysql [localhost:5727] {msandbox} (test) > select count() from t_cpu;
±---------+
| count() |
±---------+
| 10485760 |
±---------+
1 row in set (2.22 sec)
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
至此我们已经造了 10485760 条数据。

有 1000W+ 的数据量了，我们模拟一个业务场景，让 CPU 嗨(high)起来。

执行一个慢查询：

复制
mysql [localhost:5727] {msandbox} (test) > select * from t_cpu order by rand() limit 1;
1.
另外开一个会话，top 看看进程：

复制
top - 15:21:20 up 33 days, 23:10, 3 users, load average: 0.38, 0.19, 0.21
Tasks: 202 total, 2 running, 200 sleeping, 0 stopped, 0 zombie
%Cpu(s): 23.3 us, 3.6 sy, 0.0 ni, 73.1 id, 0.0 wa, 0.0 hi, 0.0 si, 0.0 st
KiB Mem : 8173716 total, 2867684 free, 1059276 used, 4246756 buff/cache
KiB Swap: 2097148 total, 2097140 free, 8 used. 6761912 avail Mem

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
32232 root 20 0 1443252 356688 11748 S 107.0 4.4 2:03.82 mysqld
296 root 20 0 67044 15588 1440 S 0.7 0.2 325:32.46 plymouthd
600 root 20 0 162032 2360 1580 R 0.3 0.0 0:04.21 top
1901 gnome-i+ 20 0 714116 22436 9168 S 0.3 0.3 98:46.20 gsd-color
1 root 20 0 191264 4192 2632 S 0.0 0.1 18:39.97 systemd
2 root 20 0 0 0 0 S 0.0 0.0 0:01.28 kthreadd
4 root 0 -20 0 0 0 S 0.0 0.0 0:00.00 kworker/0:0H
6 root 20 0 0 0 0 S 0.0 0.0 0:00.95 ksoftirqd/0
7 root rt 0 0 0 0 S 0.0 0.0 0:02.08 migration/0
8 root 20 0 0 0 0 S 0.0 0.0 0:00.00 rcu_bh
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
通过 top 我们发现有一个 32232 的进程使得 CPU 使用率已经超过了 100%。

接下来我们具体分析分析，究竟是什么导致 CPU 使用率达到 100%以上的。

检查内存使用情况
复制
[root@localhost ~]# free -m
total used free shared buff/cache available
Mem: 7982 1033 2801 40 4147 6604
Swap: 2047 0 2047
1.
2.
3.
4.
内存使用率还可以，应该不是内存影响导致的问题。

检查服务器线程
方法一：
复制
top -H -p <mysqld 进程 id>
1.
先通过 top 找出占用 CPU 使用率 100% 的 MySQL 进程 32232，在具体查看该进程下的线程情况。

复制
[root@localhost ~]# top -H -p 32232
top - 15:30:32 up 33 days, 23:20, 3 users, load average: 0.18, 0.15, 0.18
Threads: 28 total, 1 running, 27 sleeping, 0 stopped, 0 zombie
%Cpu(s): 24.4 us, 3.2 sy, 0.0 ni, 72.4 id, 0.0 wa, 0.0 hi, 0.0 si, 0.0 st
KiB Mem : 8173716 total, 2867420 free, 1059520 used, 4246776 buff/cache
KiB Swap: 2097148 total, 2097140 free, 8 used. 6761668 avail Mem

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
32272 root 20 0 1443252 356688 11748 R 99.7 4.4 2:25.74 mysqld
32244 root 0 -20 1443252 356688 11748 S 3.7