【日常运维】MySQL CPU利用率过高100%
一、现象描述:
当 MySQL数据库CPU利用率超过80%时，可能会出现业务响应变慢、超时、无法连接数据库等现象。
二、可能原因
MySQL 主要是两类线程占用 CPU：系统线程和用户线程。
1.用户线程
用户线程繁忙，大部分场景都是由“慢查询”、“计算量大”和“高 QPS”因素引起的。
（1）慢查询：进行长时间的计算，例如：order by，group by，临时表，join 等。这一类问题是查询效率不高，导致单个 SQL 语句长时间占用 CPU 时间。
（2）计算量大：单纯的数据量比较多，导致计算量巨大。
（3）高 QPS： 单纯的 QPS 压力高，所以 CPU 的时间被用满了，如：4 核的服务器用来支撑 20k 到 30k 的点查询，每个 SQL 占用的 CPU 时间并不多，但是因为整体的 QPS 很高，所以 CPU 的时间被占满了。
2.系统线程
在实际的环境中，系统线程遇到问题的情况会比较少，一般来说，多个系统线程很少会同时跑满，只要服务器的可用核心数大于等于 4 ，一般也不会遇到 CPU 利用率过高，当然有一些 bug 可能会有影响。
三、解决思路
1.慢查询：
（1）SHOW FULL PROCESSLIST：
查看正在执行的SQL，重点关注长时间运行或频繁出现的查询。SHOW FULL PROCESSLIST命令显示当前连接到MySQL服务器的所有用户会话的列表。它还提供有关每个会话的当前状态和活动的详细信息。
mysql> SHOW FULL PROCESSLIST;
+-------+------------------+--------------------+---------------+-------------+--------+-----------------------------+------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-------+------------------+--------------------+---------------+-------------+--------+-----------------------------+------------------------------------------+
| 1 | event_scheduler | localhost | NULL | Daemon | 13 | Waiting for next activation | NULL |
| 6 | azure_superuser | 127.0.0.1:33571 | NULL | Sleep | 115 | | NULL
|
| 24835 | adminuser | 10.1.1.4:39296 | classicmodels | Query | 7 | Sending data | select * from classicmodels.orderdetails;|
| 24837 | adminuser | 10.1.1.4:38208 | NULL | Query | 0 | starting | SHOW FULL PROCESSLIST |
+-------+------------------+--------------------+---------------+-------------+--------+-----------------------------+------------------------------------------+
5 rows in set (0.00 sec)
查询结果意义：
- Id: 就是这个线程的唯一标识。
- User: 就是指启动这个线程的用户。
- Host: 记录了发送请求的客户端的 IP 和 端口号。通过这些信息在排查问题的时候，我们可以定位到是哪个客户端的哪个进程发送的请求。
- DB: 当前执行的命令是在哪一个数据库上。如果没有指定数据库，则该值为 NULL 。
- Command: 是指此刻该线程正在执行的命令。
- Time: 表示该线程处于当前状态的时间。
- State: 线程的状态，和 Command 对应，下面单独解释。
- Info: 一般记录的是线程执行的语句。默认只显示前100个字符，也就是你看到的语句可能是截断了的，要看全部信息，需要使用 show full processlist。
具体详情请查看：MySQL SHOW PROCESSLIST协助故障诊断
注意：如果发现任何长时间运行的查询(慢SQL)可能会导致 CPU 峰值，就需要优化这些查询，以便优化资源。**
（2）查看正在执行的线程，并按 Time 倒排序，看看有没有执行时间特别长的线程
mysql> select * from information_schema.processlist where Command != 'Sleep' order by Time desc;
（3）应急处理：杀掉执行长时间一直组塞的线程
-- 通过SHOW PROCESSLIST获取线程ID
mysql> KILL <thread_id>;
（4）查询慢日志信息，优化慢SQL
4.1 慢日志查询：慢日志记录了所有执行时间超过参数 long_query_time设置值并且扫描记录数不小于
min_examined_row_limit 的所有的SQL语句的日志，默认未开启。long_query_time 默认为
10 秒，最小为 0， 精度可以到微秒。 如果需要开启慢查询日志，需要在MySQL的配置文件 /etc/my.cnf 中配置如下参数。
#慢查询日志
slow_query_log=1
#执行时间参数
long_query_time=2
默认情况下，不会记录管理语句，也不会记录不使用索引进行查找的查询。可以使用
log_slow_admin_statements和 更改此行为 log_queries_not_using_indexes，如下。
#记录执行较慢的管理语句
log_slow_admin_statements =1
#记录执行较慢的未使用索引的语句
log_queries_not_using_indexes = 1
4.2 SQL分析：通过EXPLAIN分析SQL执行计划，检查是否未使用索引（type=ALL）
EXPLAIN SELECT * FROM table WHERE column = 'value'; -- 查看是否走索引
2.计算量大
若数据量比较大，即使索引和执行计划没什么问题，也会导致 CPU 利用率过高，而且结合 MySQL one-thread-per-connection 的特性，并不需要太多的并发就能把 CPU 使用率跑满。一般来讲，这类问题有如下两种比较常规的解决方案：
- (1)读写分离，把这一类查询放到平时业务不怎么用的只读从库去。
- (2)在程序端拆分 SQL，把单个大查询拆分成多个小查询。
3.高 QPS
- 升级 CPU 的配置，进而提高数据库的整体性能。
- 挂载只读实例，分担主实例压力。
- 优化查询语句，提升执行效率。
