如何解决 MySQL 数据库服务器 CPU 飙升的情况

大家好，我是 V 哥。当 MySQL 数据库服务器 CPU 飙升时，我们应该怎么办？从何入手解决问题，有没有什么套路，因为自古真情留不住，唯有套路得人心，虽然这是一句玩笑话，也算很贴切，遇到这种问题，你有哪些手段去排查是致关重要的，下面是 V 哥整理的套路，可按以下步骤来解决问题。先赞再看，你必腰缠万贯。

先来看一下有哪些套路

1. 定位问题

• **使用工具监控**：通过系统监控工具（如 Linux 下的 top、htop、vmstat 等）查看 MySQL 进程占用 CPU 的情况。还可以使用 MySQL 自带的性能监控工具，如 SHOW PROCESSLIST 查看当前正在执行的 SQL 语句，找出执行时间长或占用资源多的查询。

SHOW PROCESSLIST;

• **查看慢查询日志**：开启慢查询日志，它可以记录执行时间超过指定阈值的 SQL 语句。通过分析慢查询日志，能找出可能导致 CPU 飙升的慢查询。

-- 查看慢查询日志是否开启

SHOW VARIABLES LIKE 'slow\_query\_log';

-- 开启慢查询日志

SET GLOBAL slow\_query\_log = 'ON';

-- 设置慢查询时间阈值（单位：秒）

SET GLOBAL long\_query\_time = 1;

2. 优化 SQL 查询

• **优化查询语句**：对慢查询语句进行优化，避免使用复杂的子查询、全表扫描等低效操作。例如，将子查询转换为连接查询，合理使用索引来提高查询效率。

-- 原查询：使用子查询

SELECT \* FROM orders WHERE customer\_id IN (SELECT customer\_id FROM customers WHERE country = 'China');

-- 优化后：使用连接查询

SELECT orders.\* FROM orders JOIN customers ON orders.customer\_id = customers.customer\_id WHERE customers.country = 'China';

• **添加合适的索引**：根据查询条件和经常排序、分组的字段添加索引，但要注意避免创建过多索引，因为索引会增加写操作的开销。

-- 为 customers 表的 country 字段添加索引

CREATE INDEX idx\_country ON customers (country);

3. 调整 MySQL 配置参数

• **调整缓冲池大小**：innodb\_buffer\_pool\_size 参数控制 InnoDB 存储引擎的缓冲池大小，适当增大该参数可以减少磁盘 I/O，降低 CPU 使用率。

[mysqld]

innodb\_buffer\_pool\_size = 2G

• **调整线程池参数**：如果 MySQL 版本支持线程池，可以调整线程池的相关参数，如 thread\_pool\_size 来优化线程管理，减少 CPU 上下文切换的开销。

[mysqld]

thread\_pool\_size = 64

4. 优化数据库架构

• **表分区**：对于大表，可以考虑使用表分区技术，将数据分散存储在不同的分区中，提高查询效率。

-- 创建一个按范围分区的表

CREATE TABLE sales (

    id INT,

    sale\_date DATE,

    amount DECIMAL(10, 2)

)

PARTITION BY RANGE (YEAR(sale\_date)) (

    PARTITION p2023 VALUES LESS THAN (2024),

    PARTITION p2024 VALUES LESS THAN (2025),

    PARTITION pmax VALUES LESS THAN MAXVALUE

);

• **垂直拆分和水平拆分**：如果表的字段过多，可以进行垂直拆分，将不常用的字段分离到其他表中；如果表的数据量过大，可以进行水平拆分，将数据分散到多个表中。

5. 检查硬件资源

• **增加 CPU 资源**：如果服务器的 CPU 核心数不足或性能较低，可以考虑升级 CPU 或者增加服务器的 CPU 核心数。
• **检查磁盘 I/O**：高 CPU 使用率可能是由于磁盘 I/O 瓶颈导致的。可以使用工具（如 Linux 下的 iostat）检查磁盘 I/O 情况，如果磁盘 I/O 过高，可以考虑使用更快的磁盘（如 SSD）或者优化磁盘配置。

6. 处理锁竞争问题

• **分析锁等待情况**：使用 SHOW ENGINE INNODB STATUS 查看 InnoDB 存储引擎的状态信息，分析是否存在锁等待的情况。

SHOW ENGINE INNODB STATUS;

• **优化事务**：尽量缩短事务的执行时间，避免长时间持有锁。可以将大事务拆分成多个小事务，减少锁的持有时间。

下面来看一个案例场景。

案例场景分析

案例背景是这样的，在电商业务系统中，数据库采用 MySQL 存储商品信息、订单信息、用户信息等。近期，运营部门反馈系统响应变慢，尤其是在每天晚上 8 点到 10 点的促销活动期间，系统几乎处于卡顿状态，经过监控发现 MySQL 服务器的 CPU 使用率飙升至接近 100%。

问题排查过程

1. **使用系统监控工具**：运维人员使用 Linux 系统的 top 命令查看系统进程，发现 MySQL 进程占用了大量的 CPU 资源。
2. **查看 MySQL 执行情况**：执行 SHOW PROCESSLIST 命令，发现有大量的查询语句处于执行状态，其中一条查询语句出现的频率很高，该语句用于查询某个热门商品的详细信息以及相关的用户评论。

SELECT p.\*, c.comment\_content 

FROM products p 

JOIN comments c ON p.product\_id = c.product\_id 

WHERE p.product\_id = 12345 

ORDER BY c.comment\_time DESC;

1. **分析慢查询日志**：开启慢查询日志后，发现该查询语句的执行时间超过了 5 秒，属于慢查询。

问题原因分析

1. **索引缺失**：products 表和 comments 表在连接字段 product\_id 上没有创建索引，导致在执行连接查询时需要进行全表扫描，增加了 CPU 的负担。
2. **数据量过大**：comments 表中存储了大量的用户评论信息，在进行排序操作时，需要对大量数据进行比较和排序，进一步消耗了 CPU 资源。

解决方法

1. **添加索引**：为 products 表和 comments 表的 product\_id 字段添加索引，同时为 comments 表的 comment\_time 字段添加索引，以提高排序效率。

-- 为 products 表的 product\_id 字段添加索引

CREATE INDEX idx\_products\_product\_id ON products (product\_id);

-- 为 comments 表的 product\_id 字段添加索引

CREATE INDEX idx\_comments\_product\_id ON comments (product\_id);

-- 为 comments 表的 comment\_time 字段添加索引

CREATE INDEX idx\_comments\_comment\_time ON comments (comment\_time);

1. **优化查询语句**：考虑到用户可能只关心最新的几条评论，可以在查询语句中添加 LIMIT 子句，减少需要排序和返回的数据量。

SELECT p.\*, c.comment\_content 

FROM products p 

JOIN comments c ON p.product\_id = c.product\_id 

WHERE p.product\_id = 12345 

ORDER BY c.comment\_time DESC 

LIMIT 10;

1. **调整 MySQL 配置参数**：适当增大 innodb\_buffer\_pool\_size 参数，以提高缓存命中率，减少磁盘 I/O 操作，从而降低 CPU 使用率。

[mysqld]

innodb\_buffer\_pool\_size = 4G

1. **定期清理数据**：对 comments 表中一些陈旧的、用户不太关心的评论数据进行定期清理，减少表的数据量，提高查询效率。

实施效果

经过上述优化措施后，在促销活动期间再次监控 MySQL 服务器的 CPU 使用率，发现其稳定在 30% - 40% 左右，系统响应速度明显提升，用户体验得到了极大改善。

最后

唯有套路得人心，在理工男的字典里，啥都得有套路来尊循，如果还没有，那就去找到为止，希望这篇文章可以帮助到你，关注威哥爱编程，全栈之路就你行。