监控告警响了:数据库服务器 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 关键字段速查
| 字段 | 关键取值 | 判断标准 |
|---|---|---|
| type | system > 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%)说明索引过滤性差,可以考虑更好的联合索引 |
| Extra | Using 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 索引失效的六大常见原因
- 隐式类型转换:
WHERE phone = 13800138000而 phone 是 varchar 类型,MySQL 会对列做 CAST,索引失效。必须写成字符串。 - 对列使用函数:
WHERE DATE(created_at) = '2024-10-14'会让索引失效,应改写成范围查询created_at >= '2024-10-14 00:00:00' AND created_at < '2024-10-15 00:00:00'。 - 违背最左前缀:联合索引
(a, b, c),查询条件只有b和c则用不上。注意范围查询右侧的列会失效:WHERE a=1 AND b>10 AND c=5中c用不到索引过滤。 - 前导模糊查询:
LIKE '%keyword'无法使用 B+ 树索引,LIKE 'keyword%'可以。全文搜索需求应该用全文索引或 ES。 - OR 连接不同列:
WHERE a=1 OR b=2,如果 a、b 各自有索引,优化器可能做 index merge,也可能直接全表扫描。改写成两个查询用 UNION ALL 通常更可控。 - 统计信息不准: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 极端手段处理。预防方式是:永远不要在生产跑未分批处理的大事务。
六、标准化的排查清单
- 确认现象:
top看 us/sy/wa 分布,vmstat看 r 和 cs,pidstat -w看 mysqld 的上下文切换类型。先判断是计算型、并发型还是阻塞型。 - 看实时连接:
SHOW FULL PROCESSLIST,按 Time 倒序,看 State 集中在什么值上。 - 判断是否为锁:若大量连接处于 Locked / waiting for lock 状态,转去查
sys.innodb_lock_waits,杀掉阻塞源头。这是能最快止血的一步。 - 抓慢 SQL:
performance_schema.events_statements_summary_by_digest按总耗时排序,或用pt-query-digest分析慢日志。优先处理"执行频次 × 单次扫描行数"最大的那几条。 - 看执行计划:对候选 SQL 做
EXPLAIN,重点看 type、key、rows 和 Extra 里的 filesort / temporary / join buffer。 - 验证索引有效性:
SHOW INDEX看基数,必要时ANALYZE TABLE重新采样;检查是否有隐式类型转换、函数包裹、前导 % 等失效写法。 - 检查配置:
join_buffer_size、sort_buffer_size、tmp_table_size、read_rnd_buffer_size是否过小导致频繁落盘;innodb_buffer_pool_size是否足够(专用机建议物理内存的 60%-75%)。 - 止血:如果是某条新上线的 SQL 导致,最快的方式是先用
KILL终止该类查询,或联系业务方临时下线功能,然后再做索引优化。 - 优化:加索引(大表用
pt-online-schema-change或 8.0 的 instant DDL)、改写 SQL、或者对结果集加缓存。 - 复盘:把这次的慢 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。
企业QQ咨询




