大家好，我是 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%。
问题排查过程
- 使用系统监控工具：运维人员使用 Linux 系统的
top命令查看系统进程，发现 MySQL 进程占用了大量的 CPU 资源。 - 查看 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;
- 分析慢查询日志：开启慢查询日志后，发现该查询语句的执行时间超过了 5 秒，属于慢查询。
问题原因分析
- 索引缺失：
products表和comments表在连接字段product_id上没有创建索引，导致在执行连接查询时需要进行全表扫描，增加了 CPU 的负担。 - 数据量过大：
comments表中存储了大量的用户评论信息，在进行排序操作时，需要对大量数据进行比较和排序，进一步消耗了 CPU 资源。
解决方法
- 添加索引：为
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);
- 优化查询语句：考虑到用户可能只关心最新的几条评论，可以在查询语句中添加
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;
- 调整 MySQL 配置参数：适当增大
innodb_buffer_pool_size参数，以提高缓存命中率，减少磁盘 I/O 操作，从而降低 CPU 使用率。
[mysqld]
innodb_buffer_pool_size = 4G
- 定期清理数据：对
comments表中一些陈旧的、用户不太关心的评论数据进行定期清理，减少表的数据量，提高查询效率。
实施效果
经过上述优化措施后，在促销活动期间再次监控 MySQL 服务器的 CPU 使用率，发现其稳定在 30% - 40% 左右，系统响应速度明显提升，用户体验得到了极大改善。
最后
唯有套路得人心，在理工男的字典里，啥都得有套路来尊循，如果还没有，那就去找到为止，希望这篇文章可以帮助到你，关注威哥爱编程，全栈之路就你行。
