Linux服务器上MySQL CPU使用率过高问题排查与定位

文章目录

- 一、CPU高负载常见成因分析
- - 1.1 全表扫描与索引缺失
  - 1.2 复杂计算与临时表
  - 1.3 锁竞争与线程上下文切换
  - 1.4 查询优化器误判
  - 1.5 硬件资源瓶颈
- 二、操作系统级初步定位
- - 2.1 使用top定位MySQL进程
  - 2.2 用pidstat分析线程级CPU
  - 2.3 vmstat分析系统负载
- 三、数据库层深度诊断
- - 3.1 SHOW PROCESSLIST锁定问题SQL
  - 3.2 EXPLAIN分析执行计划
  - 3.3 SHOW PROFILE分析阶段耗时
- 四、实战案例：订单查询模块CPU飙升排查
- - 4.1 问题现象
  - 4.2 操作系统层排查
  - 4.3 数据库层诊断
  - 4.4 优化方案与验证
- 五、预防措施与日常监控
- - 5.1 建立索引优化机制
  - 5.2 开启慢查询日志
  - 5.3 自动化监控脚本
- 总结

在Linux服务器环境中，MySQL数据库出现CPU使用率过高是常见的性能故障。本文将结合实际排查流程，通过具体工具和案例，详细讲解如何定位与分析MySQL CPU高负载问题。内容涵盖常见成因分析、操作系统级监控、数据库层诊断及实战优化案例，全程以实操为导向，避免理论堆砌。

一、CPU高负载常见成因分析

1.1 全表扫描与索引缺失

当查询语句未命中索引时，MySQL会触发全表扫描（type: ALL），导致大量CPU消耗在磁盘数据读取与过滤上。典型场景包括：

WHERE条件字段未建立索引
索引因字段类型不匹配、函数运算等原因失效

案例：某电商订单表查询语句SELECT * FROM orders WHERE create_time > '2023-01-01'未在create_time字段建索引，执行时扫描1000万条记录，CPU使用率飙升至80%。

1.2 复杂计算与临时表

包含大量计算函数（如COUNT(DISTINCT)、GROUP_CONCAT）或隐式创建临时表的查询（如EXPLAIN结果中Extra包含Using temporary），会消耗大量CPU进行数据处理。例如：

-- 含DISTINCT和分组的复杂查询
SELECT user_id, COUNT(DISTINCT product_id) AS cnt 
FROM order_items 
GROUP BY user_id 
HAVING cnt > 10;

1.3 锁竞争与线程上下文切换

InnoDB行锁竞争或表锁冲突会导致线程频繁等待锁释放，伴随大量上下文切换（Context Switch）。通过vmstat工具查看cs（上下文切换次数）值，若远超正常水平（如每秒>10万次），需排查锁问题：

# 每2秒采样一次，共5次
vmstat 2 5

1.4 查询优化器误判

MySQL优化器可能因统计信息过时（如未执行ANALYZE TABLE），选择非最优执行计划。例如误判扫描行数，导致放弃索引改用全表扫描：

-- 执行计划显示扫描100行，实际扫描10万行
EXPLAIN SELECT * FROM users WHERE status = 'active';

1.5 硬件资源瓶颈

当CPU核心数不足或内存带宽受限，即使查询优化良好，也可能出现CPU瓶颈。需通过nproc查看CPU核心数，free -h检查内存使用情况：

# 查看逻辑CPU核心数
nproc --all

二、操作系统级初步定位

2.1 使用top定位MySQL进程

通过top命令实时监控系统进程，按shift + p以CPU使用率排序，确认mysqld进程是否为CPU高占用源头：

top -c  # -c参数显示完整命令行

关键信息：

%CPU：进程占用CPU百分比（多核CPU需按核心数折算，如8核CPU中某进程%CPU达160%表示占用2个核心）
COMMAND：显示当前执行的SQL片段（若开启show_compatibility_56参数）

案例：发现mysqld进程%CPU持续在150%（8核系统），命令行显示SELECT * FROM logs WHERE user_id = 123，初步判断为该查询引发。

2.2 用pidstat分析线程级CPU

pidstat可按线程维度统计CPU使用情况，定位具体线程ID（TID）：

# 监控mysqld进程（PID=12345）的线程，每2秒采样一次
pidstat -t -p 12345 2

输出解读：

Linux 5.4.0-105-generic (server01)   2024-12-10 14:30:00   _x86_64_    (8 CPU)14:30:00  UID       PID   TID    %usr %system  %guest   %wait    %CPU   CPU  Command
14:30:02    1001    12345 12346  15.00   5.00    0.00    0.00   20.00     1  mysqld
14:30:02    1001    12345 12347  18.00   7.00    0.00    0.00   25.00     3  mysqld

TID：线程ID（需转换为16进制，用于后续SHOW PROCESSLIST匹配）
%CPU：该线程占用CPU百分比

2.3 vmstat分析系统负载

通过vmstat查看系统整体负载与CPU状态：

vmstat -n 2 5  # 每秒采样，共5次

关键指标：

r（运行队列长度）：等待CPU资源的进程数，若持续大于CPU核心数2倍以上，表明CPU瓶颈
us（用户态CPU）：应用程序消耗CPU占比
sy（系统态CPU）：内核操作消耗CPU占比
cs（上下文切换）：每秒上下文切换次数

案例：发现r=6（8核CPU），us=70%，sy=25%，cs=80000/s，判断为用户态应用导致CPU高负载，伴随大量上下文切换。

三、数据库层深度诊断

3.1 SHOW PROCESSLIST锁定问题SQL

通过SHOW PROCESSLIST查看当前活跃线程，重点关注：

State：线程状态（如Sending data、Copying to temporary table）
Time：持续执行时间（秒）
Info：具体SQL语句

SHOW PROCESSLIST;

输出示例：

+-----+------+-----------+------+---------+------+-------+----------------------------------------+
| Id  | User | Host      | db   | Command | Time | State | Info                                   |
+-----+------+-----------+------+---------+------+-------+----------------------------------------+
| 123 | root | localhost | test | Query   | 120  | Sending data | SELECT * FROM large_table WHERE id < 100000 |
| 124 | root | localhost | test | Sleep   | 3600 |       | NULL                                   |
+-----+------+-----------+------+---------+------+-------+----------------------------------------+

对Time较长（如>60秒）的线程，可通过KILL [Id]终止
记录Id对应的TID（需通过SELECT CONNECTION_ID();获取当前线程ID与操作系统TID的映射关系）

3.2 EXPLAIN分析执行计划

对SHOW PROCESSLIST中定位的慢查询，使用EXPLAIN分析执行计划，重点检查：

type：是否为低效的ALL（全表扫描）或index（索引全扫描）
key：是否使用预期索引
rows：预估扫描行数是否与实际数据量匹配
Extra：是否包含Using temporary、Using filesort等性能损耗标记

案例：慢查询SELECT * FROM orders WHERE status = 'paid' AND create_time > '2024-01-01'的执行计划：

EXPLAIN SELECT * FROM orders 
WHERE status = 'paid' AND create_time > '2024-01-01';

+----+-------------+--------+------------+------+---------------+------+---------+------+--------+----------+----------------------------------------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra                                        |
+----+-------------+--------+------------+------+---------------+------+---------+------+--------+----------+----------------------------------------------+
|  1 | SIMPLE      | orders | NULL       | ALL  | idx_status    | NULL | NULL    | NULL | 100000 |    10.00 | Using where; Using temporary; Using filesort |
+----+-------------+--------+------------+------+---------------+------+---------+------+--------+----------+----------------------------------------------+

问题定位：

type=ALL：全表扫描
Extra包含Using temporary和Using filesort：触发临时表和文件排序
未使用status或create_time索引

3.3 SHOW PROFILE分析阶段耗时

通过SHOW PROFILE获取SQL执行各阶段耗时，定位瓶颈环节：

-- 开启profiling
SET profiling = 1;-- 执行目标SQL
SELECT * FROM orders WHERE ... ;-- 获取最近一次查询的profile
SHOW PROFILE FOR QUERY (SELECT query_id FROM information_schema.PROFILING ORDER BY query_id DESC LIMIT 1);

典型输出：

+----------------------+----------+------------+-------------+
| Status               | Duration | CPU_user   | CPU_system  |
+----------------------+----------+------------+-------------+
| starting             | 0.00003  | 0.00001    | 0.00001     |
| checking permissions | 0.00001  | 0.00000    | 0.00000     |
| Opening tables       | 0.00002  | 0.00001    | 0.00001     |
| System lock          | 0.00001  | 0.00000    | 0.00000     |
| optimizing           | 0.00005  | 0.00003    | 0.00002     |
| statistics           | 0.00012  | 0.00008    | 0.00004     |
| preparing            | 0.00004  | 0.00002    | 0.00002     |
| executing            | 0.00003  | 0.00001    | 0.00001     |
| Sending data         | 2.56789  | 1.89023    | 0.67766     |
| end                  | 0.00002  | 0.00001    | 0.00001     |
+----------------------+----------+------------+-------------+

若Sending data阶段耗时占比超70%，通常为结果集过大或网络传输瓶颈
若optimizing阶段耗时高，可能为查询优化器计算复杂，需更新统计信息或重构查询

四、实战案例：订单查询模块CPU飙升排查

4.1 问题现象

某电商平台订单查询页面响应缓慢，监控显示Linux服务器MySQL进程CPU使用率持续达180%（8核系统），top中mysqld进程%CPU为180%，COMMAND显示正在执行订单列表查询。

4.2 操作系统层排查

top确认进程：

top -c | grep mysqld
# 输出显示PID=23456，%CPU=180%，命令行包含"SELECT o.*, u.name FROM orders o JOIN users u ON o.user_id=u.id WHERE o.status='paid'"

pidstat线程分析：

pidstat -t -p 23456 2
# 发现TID=23458（16进制为0x5BCA）和TID=23459（0x5BCB）两个线程各占90% CPU

vmstat系统负载：

vmstat 2 5
# r=4（小于8核*2），us=85%，sy=10%，cs=60000/s，判断为用户态SQL查询导致

4.3 数据库层诊断

SHOW PROCESSLIST定位线程：

SHOW PROCESSLIST;
# 找到Id=1234对应的线程，User=app_user，Info=目标查询语句，Time=156秒

EXPLAIN执行计划分析：

EXPLAIN SELECT o.*, u.name FROM orders o 
JOIN users u ON o.user_id=u.id 
WHERE o.status='paid' AND o.create_time>'2024-01-01';

+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+---------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra                                       |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+---------------------------------------------+
|  1 | SIMPLE      | o     | NULL       | ALL  | idx_status    | NULL | NULL    | NULL | 100000 |    10.00 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | u     | NULL       | eq_ref| PRIMARY       | PRIMARY| 4       | o.user_id | 1      |   100.00 |                                             |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+---------------------------------------------+

问题点：

主表orders未使用索引，全表扫描10万条记录
触发临时表（Using temporary）和文件排序（Using filesort）

SHOW PROFILE阶段耗时：

SHOW PROFILE FOR QUERY ...;
# Sending data阶段耗时2.3秒，占总耗时92%，表明大量数据传输

4.4 优化方案与验证

添加复合索引：

ALTER TABLE orders ADD INDEX idx_status_create_time (status, create_time);

优化查询语句：

-- 避免SELECT *，只查询必要字段
SELECT o.order_id, o.total_amount, u.name 
FROM orders o 
JOIN users u ON o.user_id=u.id 
WHERE o.status='paid' AND o.create_time>'2024-01-01' 
ORDER BY o.create_time DESC 
LIMIT 20;

验证执行计划：

EXPLAIN SELECT ...;
# type变为range，key使用idx_status_create_time，rows预估1000条，Extra移除临时表和文件排序

CPU使用率验证：

top -c | grep mysqld
# %CPU降至20%，查询响应时间从156秒缩短至0.3秒

五、预防措施与日常监控

5.1 建立索引优化机制

定期通过pt-index-usage（Percona Toolkit工具）分析未使用索引
对高频查询执行EXPLAIN检查执行计划

5.2 开启慢查询日志

配置long_query_time=1，通过mysqldumpslow分析慢查询分布：

# 按查询时间排序，取前10慢查询
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

5.3 自动化监控脚本

编写Shell脚本定时采集CPU、线程状态等指标：

#!/bin/bash
DATE=$(date +%Y-%m-%d_%H:%M:%S)
CPU_USAGE=$(top -bn1 | grep 'Cpu(s)' | awk '{print $2}')
THREADS=$(mysql -e "SHOW STATUS LIKE 'Threads_running';" | awk '{print $2}')
echo "$DATE,$CPU_USAGE,$THREADS" >> mysql_monitor.log

总结

MySQL CPU使用率过高的排查需遵循“操作系统层定位进程→数据库层分析SQL→执行计划优化→效果验证”的流程。通过top、pidstat等工具锁定问题进程，利用SHOW PROCESSLIST、EXPLAIN、SHOW PROFILE深入分析SQL执行细节，结合索引优化、查询重构等手段解决性能瓶颈。实际操作中需注意多维度数据关联分析，避免单一工具误判，同时建立常态化监控机制预防问题复现。