如何解决 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;

3. 分析慢查询日志：开启慢查询日志后，发现该查询语句的执行时间超过了 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);

2. 优化查询语句：考虑到用户可能只关心最新的几条评论，可以在查询语句中添加 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;

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

   [mysqld]
   innodb_buffer_pool_size = 4G

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

实施效果

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

最后

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