数据库服务器CPU占用过高怎么办?慢SQL、锁等待与执行计划排查

监控告警响了:数据库服务器 CPU 95%,应用响应从 20ms 涨到 3 秒。这种时刻最忌讳的是凭直觉下判断——"是不是该加索引了?""要不要重启一下?"。CPU 高只是一个症状,它可能来自慢 SQL 的扫描开销、可能来自大量并发的上下文切换、也可能来自 InnoDB 内部的自旋锁竞争,甚至可能来自磁盘 IO 阻塞导致的进程堆积。这几种情况的处置方式完全不同。本文给出一套从系统层到数据库层的完整定位流程。

一、第一步:先分清是哪一类 CPU 高

在登录数据库之前,先用系统工具把问题归类。这一步只需要 30 秒,但能避免后面几小时的弯路。

# 1. 看整体 CPU 分解
top -c
# 重点关注三行:
#   %us  用户态:SQL 计算、排序、扫描、加锁逻辑 —— 通常指向慢 SQL
#   %sy  内核态:系统调用、上下文切换 —— 通常指向并发过高
#   %wa  IO 等待:等磁盘 —— 指向 IO 瓶颈而非 CPU 瓶颈
#   还要看 load average 与 CPU 核数的关系

# 2. 更精细的每核分解
mpstat -P ALL 1 5

# 3. 看进程级的 CPU 与上下文切换
pidstat -u -w -p $(pidof mysqld) 1 5
#   %usr / %system / %guest / %CPU
#   cswch/s  自愿上下文切换(等资源,如锁、IO)
#   nvcswch/s 非自愿切换(时间片用完,说明真在忙)

# 4. 看系统整体切换与中断
vmstat 1 10
#   r  运行队列长度,持续 > CPU 核数说明 CPU 不够用
#   cs 上下文切换次数/秒,突增说明并发暴涨
#   us/sy/id/wa 与上同

# 5. 抓取 mysqld 内部热点函数(需要 debug 符号或至少能看到库名)
perf top -p $(pidof mysqld) -g

1.1 三种典型画像

画像特征最可能原因处置方向
计算型%us 高、%wa 低、nvcswch/s 高慢 SQL:全表扫描、大排序、临时表、JOIN 算法差抓慢 SQL,优化执行计划
并发型%sy 偏高、cs 突增、r 远大于核数连接数暴涨、线程调度开销、热点行/热点索引竞争限流、降并发、查应用连接泄漏
阻塞型%wa 高,或 %us 低但 load 很高IO 瓶颈,或锁等待导致大量线程堆积查 IO、查锁等待链
自旋型%us 极高但 QPS 很低InnoDB 自旋锁竞争(innodb_spin_wait_delay)、MySQL 5.7 的 query cache mutex调 innodb_thread_concurrency,关闭 query cache

一个重要的提醒:load average 高不等于 CPU 高。load 包含运行队列和不可中断睡眠(D 状态,通常在等 IO 或等锁)的进程。很多"CPU 高"的告警实际是"load 高",而 CPU 使用率其实很低——这种情况要去查 IO 和锁,不是去优化 SQL。

二、第二步:抓出正在跑的慢 SQL

2.1 实时:看当前所有连接

-- 看所有连接,重点关注 Time 列(该状态持续秒数)
SHOW FULL PROCESSLIST;
-- 或更全面的 performance_schema 视图
SELECT id, user, host, db, command, time, state, LEFT(info, 120) AS sql_text
FROM information_schema.PROCESSLIST
WHERE command != 'Sleep' AND time > 2
ORDER BY time DESC LIMIT 20;

State 列是判断瓶颈在哪的关键线索,几个高频值:

State 值含义说明
Sending data正在读取并处理数据(不只是"发送")最常见,通常是扫描行数过多
Copying to tmp table正在创建内存临时表GROUP BY / DISTINCT 导致
Copying to tmp table on disk临时表已落到磁盘结果集超过 tmp_table_size,性能急剧下降
Sorting result正在排序ORDER BY 无法走索引
Creating sort index正在为 ORDER BY 建排序索引同上
update / updating正在更新结合 Time 判断是大事务还是被锁
Locked / waiting for ... lock等待锁见锁等待章节
statistics / preparing优化器正在生成执行计划表太多或统计信息有问题

一个快速判断法:如果 SHOW PROCESSLIST 里几十条 SQL 都停在 Sending data 且 Time 持续上涨,那就是典型的扫描量过大;如果大量连接停在 update 或 waiting for lock,那就是锁问题。

2.2 历史:慢查询日志与 pt-query-digest

-- 确认慢日志是否开启
SHOW VARIABLES LIKE 'slow_query_log%';
SHOW VARIABLES LIKE 'long_query_time';
SHOW VARIABLES LIKE 'log_queries_not_using_indexes';
SHOW VARIABLES LIKE 'min_examined_row_limit';

-- 临时开启(重启失效,生产建议写进 my.cnf)
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;
SET GLOBAL log_output = 'FILE';
SET GLOBAL slow_query_log_file = '/var/log/mysql/mysql-slow.log';

long_query_time 默认 10 秒,对现代应用来说太长了,生产环境建议设 1 秒(甚至 0.5 秒)。log_queries_not_using_indexes = ON 会记录所有未走索引的查询,但开启后日志量可能暴涨,建议配合 min_examined_row_limit = 1000 过滤掉小表。

# 用 Percona Toolkit 分析慢日志,找出 Top SQL
pt-query-digest /var/log/mysql/mysql-slow.log --limit 20

# 只看最近一天的
pt-query-digest --since '2024-10-14 00:00:00' /var/log/mysql/mysql-slow.log

# 分析 TCP 抓包(不改配置也能抓)
tcpdump -s 65535 -x -nn -q -tttt -i any -c 1000 port 3306 > /tmp/tcpdump.log
pt-query-digest --type tcpdump /tmp/tcpdump.log

pt-query-digest 的输出按"查询指纹"(把具体参数替换成 ?)聚合,每个指纹给出:总耗时占比、调用次数、平均/最大/95 分位耗时、扫描行数(Rows examined)与返回行数(Rows sent)的比值。最该优先处理的不是总耗时最高的,而是"总耗时高 + 单次扫描行数极大"的——一条每秒执行 100 次、每次扫 50 万行的 SQL,比一条每小时执行一次、耗时 60 秒的报表 SQL 危害大得多。

2.3 用 performance_schema 做统计分析(不依赖日志)

-- 按总耗时排序的 Top 20 语句
SELECT DIGEST_TEXT,
       COUNT_STAR                          AS exec_count,
       ROUND(SUM_TIMER_WAIT/1000000000000, 2) AS total_sec,
       ROUND(AVG_TIMER_WAIT/1000000000, 2)    AS avg_ms,
       ROUND(MAX_TIMER_WAIT/1000000000, 2)    AS max_ms,
       SUM_ROWS_EXAMINED, SUM_ROWS_SENT,
       SUM_NO_INDEX_USED
FROM performance_schema.events_statements_summary_by_digest
WHERE SCHEMA_NAME NOT IN ('mysql','sys','performance_schema','information_schema')
ORDER BY SUM_TIMER_WAIT DESC LIMIT 20\G

-- 只看全表扫描次数最多的
SELECT DIGEST_TEXT, COUNT_STAR, SUM_NO_INDEX_USED, SUM_ROWS_EXAMINED
FROM performance_schema.events_statements_summary_by_digest
WHERE SUM_NO_INDEX_USED > 0
ORDER BY SUM_NO_INDEX_USED DESC LIMIT 10\G

-- 用 sys schema 的便捷视图(MySQL 5.7+/8.0 自带)
SELECT * FROM sys.statements_with_full_table_scans LIMIT 10;
SELECT * FROM sys.statements_with_runtimes_in_95th_percentile LIMIT 10;
SELECT * FROM sys.statements_with_sorting LIMIT 10;
SELECT * FROM sys.statements_with_temp_tables LIMIT 10;

-- 清空统计重新采集(便于对比优化前后)
CALL sys.ps_truncate_all_tables(FALSE);

用 sys schema 的视图是最省事的方式,它把 performance_schema 的原始数据包装成了人类可读的报表。注意 events_statements_summary_by_digest 有容量上限(performance_schema_digests_size,默认 10000),SQL 种类非常多的实例可能被截断,可以适当调大。

三、第三步:读懂执行计划

抓到慢 SQL 之后,用 EXPLAIN 看优化器选了什么路径。MySQL 8.0.18 之后还支持 EXPLAIN ANALYZE,它会真正执行这条 SQL并给出每个步骤的实际耗时和行数,这在排查"优化器估算错误"时非常有用(注意:EXPLAIN ANALYZE 会真实执行,不要在生产的 UPDATE/DELETE 上用)。

EXPLAIN SELECT o.order_no, u.nickname
FROM orders o JOIN users u ON o.user_id = u.id
WHERE o.status = 1 AND o.created_at > '2024-10-01'
ORDER BY o.created_at DESC LIMIT 20\G

-- 8.0.18+
EXPLAIN ANALYZE SELECT ... ;

-- 按 JSON 输出,信息更全(能看到 cost 估算)
EXPLAIN FORMAT=JSON SELECT ...\G

3.1 EXPLAIN 关键字段速查

字段关键取值判断标准
typesystem > const > eq_ref > ref > range > index > ALL出现 ALL(全表扫描)或大表上的 index(全索引扫描)就要警惕;目标至少到 range,最好 ref/eq_ref
possible_keys候选索引列表为 NULL 说明完全没有可用索引
key实际使用的索引为 NULL 说明没走索引;与 possible_keys 不符说明优化器选错了
key_len实际使用的索引字节数判断联合索引用到了几列(只用到第一列时 key_len 明显偏小)
rows预估扫描行数这是最重要的估算指标,与实际返回行数比值越大越糟糕
filtered经过条件过滤后剩余百分比很低(如 1%)说明索引过滤性差,可以考虑更好的联合索引
ExtraUsing index / Using where / Using filesort / Using temporary见下表

3.2 Extra 字段的危险信号

Extra 值含义严重性优化方向
Using index覆盖索引,所需列全在索引里,无需回表好现象保持
Using index condition索引条件下推(ICP)较好—
Using where在存储引擎返回后再过滤一般结合 rows 看,rows 大就要优化
Using filesort需要额外排序(不一定真的用文件)中为 ORDER BY 建合适索引,注意联合索引的列顺序与 ASC/DESC 一致
Using temporary创建了临时表(GROUP BY / DISTINCT / UNION)高优化 GROUP BY 走索引;或调大 tmp_table_size
Using temporary; Using filesort又建临时表又排序很高优先处理,通常是大结果集聚合
Using join buffer (Block Nested Loop)JOIN 未走索引,用缓冲区嵌套循环高给 JOIN 字段加索引
Range checked for each record每行都要重新判断能否用索引高通常因 JOIN 字段类型不一致

3.3 索引失效的六大常见原因

  1. 隐式类型转换:WHERE phone = 13800138000 而 phone 是 varchar 类型,MySQL 会对列做 CAST,索引失效。必须写成字符串。
  2. 对列使用函数:WHERE DATE(created_at) = '2024-10-14' 会让索引失效,应改写成范围查询 created_at >= '2024-10-14 00:00:00' AND created_at < '2024-10-15 00:00:00'。
  3. 违背最左前缀:联合索引 (a, b, c),查询条件只有 b 和 c 则用不上。注意范围查询右侧的列会失效:WHERE a=1 AND b>10 AND c=5 中 c 用不到索引过滤。
  4. 前导模糊查询:LIKE '%keyword' 无法使用 B+ 树索引,LIKE 'keyword%' 可以。全文搜索需求应该用全文索引或 ES。
  5. OR 连接不同列:WHERE a=1 OR b=2,如果 a、b 各自有索引,优化器可能做 index merge,也可能直接全表扫描。改写成两个查询用 UNION ALL 通常更可控。
  6. 统计信息不准:InnoDB 的索引基数(cardinality)是抽样估算的,数据分布剧变后可能严重偏差,导致优化器选错索引。用 ANALYZE TABLE t 重新采样,或调大 innodb_stats_persistent_sample_pages(默认 20)。
-- 查看索引基数与统计信息
SHOW INDEX FROM orders;
SELECT * FROM mysql.innodb_table_stats WHERE database_name='orderdb'\G
SELECT * FROM mysql.innodb_index_stats WHERE database_name='orderdb' AND table_name='orders'\G

-- 重新采样统计信息
ANALYZE TABLE orders;

-- 强制/忽略某个索引做对比测试(仅用于验证,不要长期留在代码里)
SELECT ... FROM orders FORCE INDEX(idx_status_created) WHERE ...;
SELECT ... FROM orders IGNORE INDEX(idx_status) WHERE ...;
SELECT ... FROM orders USE INDEX(idx_status_created) WHERE ...;

四、第四步:排查锁等待

如果 CPU 高但 QPS 很低,或者大量连接堆积在 update 状态,就要查锁。详见同系列的锁等待专篇,这里给出快速定位命令:

-- 1. 查看当前所有运行中的事务
SELECT trx_id, trx_state, trx_started, trx_wait_started,
       trx_rows_locked, trx_rows_modified,
       trx_mysql_thread_id, LEFT(trx_query, 100) AS sql_text
FROM information_schema.INNODB_TRX
ORDER BY trx_started LIMIT 20;
-- 重点:trx_state = LOCK WAIT 的是被阻塞者;trx_rows_locked 大的是阻塞源头

-- 2. MySQL 8.0 用 performance_schema 查等待关系
SELECT waiting_pid, waiting_query, blocking_pid, blocking_query,
       wait_age, sql_kill_blocking_query
FROM sys.innodb_lock_waits;
-- 这个视图直接给出"该杀掉谁",非常实用

-- 3. 5.7 及更早版本
SELECT * FROM information_schema.INNODB_LOCKS;
SELECT * FROM information_schema.INNODB_LOCK_WAITS;

-- 4. 看 InnoDB 内部状态,含最近的死锁信息
SHOW ENGINE INNODB STATUS\G
-- 关注 LATEST DETECTED DEADLOCK 段和 TRANSACTIONS 段

-- 5. 看锁等待超时设置(默认 50 秒)
SHOW VARIABLES LIKE 'innodb_lock_wait_timeout';

处理原则:找到最上游的那个长时间未提交的事务(阻塞源头),而不是杀掉所有等待者。等待者杀掉后新的请求还会继续被阻塞。用 sys.innodb_lock_waits 里的 blocking_pid 定位,然后用 KILL 或 KILL QUERY (后者只终止当前语句,保留连接)。

五、第五步:系统层面的其他来源

5.1 并发过高导致的上下文切换

-- 看连接数与线程数
SHOW STATUS LIKE 'Threads_%';
SHOW VARIABLES LIKE 'max_connections';        -- 默认 151
SHOW STATUS LIKE 'Max_used_connections';
SHOW STATUS LIKE 'Aborted_connects';          -- 连接失败数,突增说明应用有问题

-- 看每秒查询量,判断是不是真的"忙"
SHOW GLOBAL STATUS LIKE 'Questions';
SHOW GLOBAL STATUS LIKE 'Com_select';
-- 隔 10 秒取两次差值 / 10 = QPS

如果 QPS 并不高但 CPU 很高,要考虑并发线程数过多导致的调度开销。InnoDB 有一个并发限制参数 innodb_thread_concurrency,默认 0 表示不限制。在 CPU 核数较少(如 8 核)但并发连接上千的实例上,把它设为 CPU 核数的 2 倍(如 16)往往能显著降低 %sy 并提升整体吞吐——原理是限制同时进入 InnoDB 内核的线程数,减少自旋锁竞争和上下文切换。

5.2 自旋锁与热点竞争

InnoDB 的 mutex 在获取不到时会先自旋(spin)若干次再睡眠。高并发下大量自旋会让 %us 飙升但业务吞吐反而下降。perf top 里如果看到大量 ut_delay、mutex_spin_wait、rw_lock_s_lock_spin,就是典型的自旋竞争。

# 查看 InnoDB 的 mutex 等待概况
SHOW ENGINE INNODB MUTEX;

# 相关参数
SHOW VARIABLES LIKE 'innodb_spin_wait_delay';       -- 默认 6
SHOW VARIABLES LIKE 'innodb_sync_spin_loops';       -- 默认 30
SHOW VARIABLES LIKE 'innodb_thread_concurrency';    -- 默认 0(不限)

典型的自旋竞争场景:热点行更新(比如秒杀时大量请求更新同一件商品的库存行)、自增列(AUTO_INCREMENT)竞争、以及 MySQL 5.7 时代的 query cache 全局 mutex(这也是 5.7 之后 query cache 被彻底移除的原因之一)。对策分别是:把热点行拆分成多行(分段计数)、设 innodb_autoinc_lock_mode = 2(8.0 默认已是 2)、以及升级到 8.0。

5.3 大事务回滚

一个跑了两小时的大事务被 kill 或超时,回滚过程可能比执行还慢,并且会持续占用 CPU 和 IO。SHOW ENGINE INNODB STATUS 的 TRANSACTIONS 段里能看到 ROLLING BACK 状态和 undo log 条目数。此时只能等,或者通过 innodb_force_recovery 极端手段处理。预防方式是:永远不要在生产跑未分批处理的大事务。

六、标准化的排查清单

  1. 确认现象:top 看 us/sy/wa 分布,vmstat 看 r 和 cs,pidstat -w 看 mysqld 的上下文切换类型。先判断是计算型、并发型还是阻塞型。
  2. 看实时连接:SHOW FULL PROCESSLIST,按 Time 倒序,看 State 集中在什么值上。
  3. 判断是否为锁:若大量连接处于 Locked / waiting for lock 状态,转去查 sys.innodb_lock_waits,杀掉阻塞源头。这是能最快止血的一步。
  4. 抓慢 SQL:performance_schema.events_statements_summary_by_digest 按总耗时排序,或用 pt-query-digest 分析慢日志。优先处理"执行频次 × 单次扫描行数"最大的那几条。
  5. 看执行计划:对候选 SQL 做 EXPLAIN,重点看 type、key、rows 和 Extra 里的 filesort / temporary / join buffer。
  6. 验证索引有效性:SHOW INDEX 看基数,必要时 ANALYZE TABLE 重新采样;检查是否有隐式类型转换、函数包裹、前导 % 等失效写法。
  7. 检查配置:join_buffer_size、sort_buffer_size、tmp_table_size、read_rnd_buffer_size 是否过小导致频繁落盘;innodb_buffer_pool_size 是否足够(专用机建议物理内存的 60%-75%)。
  8. 止血:如果是某条新上线的 SQL 导致,最快的方式是先用 KILL 终止该类查询,或联系业务方临时下线功能,然后再做索引优化。
  9. 优化:加索引(大表用 pt-online-schema-change 或 8.0 的 instant DDL)、改写 SQL、或者对结果集加缓存。
  10. 复盘:把这次的慢 SQL 加入监控白名单,设置针对性的告警;把长事务、全表扫描纳入定期巡检。

七、避坑清单

  • 不要把 load average 当作 CPU 使用率。load 高而 CPU 空闲,问题在 IO 或锁。
  • long_query_time=10 抓不到真正的性能杀手。生产建议设 1 秒;高频 SQL 哪怕 200ms 也可能比一条 10 秒的报表 SQL 危害更大。
  • 不要只看总耗时排序。要综合看执行次数、平均耗时、扫描行数。用 Rows_examined / Rows_sent 的比值判断索引效率,比值几千倍的一定有问题。
  • 不要在生产随意执行 EXPLAIN ANALYZE 于写语句上,它会真实执行。
  • 杀连接要杀阻塞源头,不是杀等待者。杀等待者是治标,新的请求会继续堆积。
  • 加索引要注意大表的锁表风险。MySQL 5.6+ 支持 Online DDL,但仍建议用 pt-online-schema-change(触发器方式)或 gh-ost(binlog 方式),避免主从延迟和 MDL 锁。
  • 索引不是越多越好。每个索引都会增加写入成本、占用 buffer pool、并让优化器选择更困难。定期用 sys.schema_unused_indexes 清理从没被用过的索引。
  • 不要盲目调大 buffer 参数。sort_buffer_size、join_buffer_size 是每个连接分配的,设成 64MB 而并发 500 就是 32GB 内存,会直接 OOM。这些参数应该从小到大试。
  • 警惕统计信息过时。数据分布剧变(比如某状态值从 1% 变成 60%)后,优化器可能一直选错索引,ANALYZE TABLE 往往能立竿见影。
  • 保留现场。在优化之前先保存 SHOW ENGINE INNODB STATUS、SHOW FULL PROCESSLIST、慢日志片段,否则事后无法复盘,也无法证明优化是否真的有效。

小结

数据库服务器 CPU 占用过高,正确的处置路径是"先分类、再定位、后优化"。分类靠系统工具:top 的 us/sy/wa 分解、vmstat 的 r 与 cs、pidstat -w 的自愿/非自愿切换,30 秒内就能判断是慢 SQL 计算、并发调度还是 IO/锁阻塞。定位靠两条线索:实时线索是 SHOW FULL PROCESSLIST 的 State 和 sys.innodb_lock_waits,历史线索是慢查询日志配 pt-query-digest 或 events_statements_summary_by_digest。优化靠执行计划:EXPLAIN 里的 type 至少要到 range、rows 要远小于表行数、Extra 里不能有 filesort 和 temporary,同时排查隐式转换、函数包裹、前导模糊查询、违背最左前缀这四类索引失效写法。最后记住,优化之前先保存现场,止血优先于根治——先让业务恢复,再从容地改索引和 SQL。