MySQL CPU飙升排查指南：从慢SQL到索引优化的完整方案

MySQL的CPU使用率飙高这应该是很多DBA和开发都遇到过的事。尤其是在业务高峰期一条慢SQL就能把整个实例的CPU打到80%以上所有请求跟着变慢数据库连接堆积最后应用服务全线告警。我这些年排查过不少类似问题有的确实是SQL写法太烂有的却是表结构设计或者MySQL内部机制埋下的雷。这篇文章就把我自己常用的排查思路和解决办法整理出来从现象确认、常见原因、定位手段到优化方案一条线讲清楚希望能让你下次再遇到CPU告警时少走点弯路。先说个基本判断MySQL的CPU使用率升高绝大多数时候是“逻辑读”太多造成的也就是MySQL需要扫描、判断、排序、回表的数据量太大CPU忙着做这些操作所以飙高。真正因为MySQL自身程序bug把CPU吃满的情况极少所以排查方向要优先放在“什么SQL在大量消耗CPU”上。1. MySQL CPU打满先搞清楚“高”是哪种“高”1.1 现象确认是瞬时飙升还是持续爬升接到CPU告警后第一件事不是急着去看慢SQL而是先确认CPU的走势。我一般会先在监控面板上把CPU曲线拉出来看它属于下面几种形态里的哪一种持续高位平稳CPU长时间维持在70%~90%以上说明有稳定的高消耗任务在跑大概率是某个慢查询持续被执行或者业务流量本身太高。周期性脉冲式升高每隔一段时间CPU就冲一个尖峰比如每分钟一次、每小时一次这种情况多半有定时任务在跑像报表统计、批处理脚本、定时刷数据的存储过程。突然飙升后不回落这种最危险通常意味着出现了非常差的执行计划或者某个大事务把资源占住后续请求全部排队CPU在重试风暴里越烧越高。偶发小幅抖动可以先观察不一定要立刻优化但需要记录现场避免问题扩大。确认现象形态的过程很重要因为不同形态对应的排查路径不一样。一段高峰持续了一小时和一分钟内冲上去就下不来背后完全是两种问题。前者可能是业务量上涨或者SQL恶化后者往往是索引失效或者出现了全表扫描的突发查询。1.2 先看监控别急着优化有句话我经常跟团队说没有监控数据就不要动手。很多人在看到CPU告警后第一反应是去修改数据库配置调一堆参数或者干脆重启数据库这非常危险。没有现场数据你根本不知道刚才是什么SQL把CPU打上去的重启之后问题大概率还会复现而且复现时你手里依然没有证据。所以接到告警的第一步我建议按顺序做这几件事保留告警时刻的监控快照至少覆盖CPU、内存、磁盘IO、网络带宽、活跃会话数五类指标。确认当前实例是不是主库如果是主从架构还要判断压力是不是来自主从切换后的流量重放。看活跃会话数如果会话数从几十突然涨到几百大问题可能在“并发”而不是某一条SQL。再查慢查询日志和processlist把当前正在执行的SQL捞出来。这个过程可能只需要两三分钟但能帮你把排查方向定下来。省掉这一步直接去优化SQL很容易犯“修好了一条SQL结果真正的问题还在”的错。2. MySQL CPU使用率高的几类常见原因2.1 慢SQL全表扫描CPU被无效劳动吃干慢SQL导致CPU高是最常见的情况。MySQL在执行一条查询时主要把CPU花在解析SQL、打开表、扫描记录、判断where条件、排序、分组、连接等操作上。其中开销最大的是扫描记录数如果一张表有几百万行数据又没有合适索引可用MySQL就不得不把这几百万行全部读出来逐行判断条件是否满足这个过程叫全表扫描。全表扫描的逻辑读次数会爆炸式增长。举个例子一张500万行的订单表如果查询条件是where status pending而status列上没有索引MySQL只能把500万行全扫一遍。即使最终只返回50行它判断条件的工作量也是500万次。CPU要做的事情包括读取记录、比较字段值、检查是否满足条件下推、生成临时结果这些工作全都要消耗CPU周期。我遇到过最夸张的一次是开发同事写了个关联查询三张大表join每张表几百万行没有索引结果就是三个全扫描在内存里做嵌套循环一个查询跑了40多秒CPU直接拉满。后来在关联字段上补了索引同样的查询降到了200毫秒。2.2 索引失效看似走了索引实际扫描行数依然巨大索引失效比没有索引更隐蔽。很多情况下SQL里确实写了索引字段作为条件但看完执行计划才发现MySQL选择的不是理想索引或者因为函数操作、隐式类型转换、字符集不一致导致索引完全没用上。最常见的索引失效场景有这几种在索引列上使用函数比如where DATE(create_time) 2024-01-01哪怕create_time上有索引也没用因为MySQL必须先对每一行调用DATE函数才能比较。改成where create_time 2024-01-01 and create_time 2024-01-02才能让索引生效。隐式类型转换字段类型是varchar查询条件却传了数字MySQL会先把字段转成数字再比较索引就失效了。where phone 13800138000和where phone 13800138000在特定字符集下性能差距很大。前导模糊查询where name like %张这种写法因为通配符在最前面索引的B树结构没法快速定位只能全表扫。联合索引不回全建了(a, b, c)联合索引查询条件如果跳过b直接用到c就会发生索引条件下推失效或者走错索引的问题。平时维护SQL规范或者在code review阶段就盯住这些点能避免大量线上CPU问题。2.3 连接数和并发过高CPU被上下文切换拖垮CPU使用率高有时候不是操作本身太重而是操作数量太多了。当系统并发量上涨时MySQL会不断创建和销毁线程来服务连接线程之间的上下文切换也要消耗CPU。大量短连接频繁建立、断开产生的开销尤其明显。我见过一个电商大促的案例平时QPS大概2000CPU占用40%大促当天QPS冲到8000CPU直接100%。当时业务方认为是SQL问题但抓出来的SQL一条条看执行计划都正常。后面查performance_schema发现threads状态一直在频繁创建和销毁才意识到是连接数配得太高、连接复用太差导致的。MySQL的最大连接数如果设置过大比如超过几千那么即使当前只有少量活跃SQL来自连接池的空闲连接也会周期性发送ping包这些都会占用CPU。更关键的是高并发下每一条SQL的等待和排队时间变长客户端会不断重试重试又带来更多请求形成恶性循环。2.4 排序、分组、临时表和filesort的隐藏开销很多查询看着不复杂实际上内部伴随着隐形的排序和临时表操作。比如order by没有走索引MySQL就得到sort buffer里做filesortgroup by如果没有可利用索引就得建临时表来分组distinct也有类似逻辑。filesort并不一定真的落盘到磁盘文件如果排序数据量小于sort_buffer_size就在内存里排。但内存排序同样吃CPU尤其是排序字段不是索引字段、数据集又很大时CPU会花大量时间在比较和交换上。临时表如果小在内存里用MEMORY引擎建如果大了就会转成磁盘临时表这时磁盘IO和CPU一起高。以前排查过一个报表查询单条SQL本身没啥大问题就是order by create_time desc limit 20但create_time上没有索引导致每次执行都要把所有记录先排一遍。加了索引后由于B树叶子节点本身是有序的排序这一步直接省略CPU使用率肉眼可见地降下来。2.5 MySQL配置不当缓冲池、排序区、线程池参数失衡有时候CPU高并不是当前业务导致的而是MySQL配置和你实际的负载模型不匹配。典型的例子包括innodb_buffer_pool_size设得太小导致大量数据页被频繁淘汰和重新读入每一次读盘和刷脏都会消耗CPUsort_buffer_size或者join_buffer_size设得太大每个连接都预分配大内存在高并发下内存暴涨操作系统被迫频繁换页CPU忙得不可开交。还有一个容易被忽略的点是table_open_cache和thread_cache_size设置不当。table_open_cache太小会导致表打开和关闭非常频繁thread_cache_size太小线程频繁创建销毁。这些操作都会体现在CPU使用率上而且从操作系统层面看表现为user CPU和sys CPU都偏高。MySQL 8.0以后默认的innodb_buffer_pool_size已经比较合理但很多从5.7迁移上来的老实例配置还是沿用老一套很容易出现问题。调参时要结合业务特性来做不能一刀切。2.6 批量更新、子查询和存储过程一次做太多事情生产上还有一个常见原因是开发写的批量更新脚本或者存储过程一次性处理的数据量太大。比如update语句用子查询更新好几百万行或者存储过程循环里逐条执行update每条update都单独提交一次事务。这类操作不仅会持有大量行锁还会导致redo log写入频繁、刷脏压力增大CPU和IO都会飙升。我这里说的“子查询”要特别提一句MySQL 5.7之前的版本对子查询的优化能力有限很多in (select ...)会被改写成相关子查询导致外层每一行都要执行一次子查询性能极差。MySQL 8.0对子查询的优化好了很多但如果生产环境还是5.7遇到in子查询的SQL一定要格外谨慎最好手动改成join或者临时表方式。3. 排查过程一套可复用的定位流程3.1 操作系统层面先看一眼top、vmstat、iostat登录到数据库服务器后我一般先跑几条命令快速判断CPU消耗的分布情况top看整体负载按P键按CPU排序看是mysqld进程占用的多还是其他进程占用的多。top -H -p mysql_pid看mysqld进程内部哪些线程在消耗CPU这些线程对应的通常是某条正在执行的SQL。vmstat 1 10看用户态CPU、内核态CPU、等待IO的占比。如果user CPU很高说明是SQL执行的计算量大如果sys CPU很高可能是并发切换、锁等待或IO系统调用频繁。iostat -x 1如果同时观察到磁盘util和CPU的wa都很高问题重点可能在磁盘IO这时候优化SQL的收益不一定大反而要考虑扩内存、加SSD或者从查询逻辑上减少扫描数量。top输出里可以看到mysqld的CPU占用但很难直接看出哪条SQL是元凶所以操作系统命令只是第一步拿到“确实在MySQL内部”这个结论后就该进数据库层面继续查。3.2 开慢查询日志和performance_schema定位MySQL内部问题慢查询日志是最直接的手段。检查一下当前是否开启了慢查询SHOW VARIABLES LIKE slow_query_log; SHOW VARIABLES LIKE long_query_time;如果没开可以临时开SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1; SET GLOBAL log_queries_not_using_indexes ON;long_query_time设置成1秒比较合适线上生产可以设成0.5秒别一开始就设成0那会瞬间生成海量日志。log_queries_not_using_indexes参数可以帮我们把那些没走索引的查询也记录下来这类SQL即使单条不慢积少成多也会吃CPU。MySQL 5.7及以上版本还提供了performance_schema它记录了SQL执行过程中的多种统计数据。查一下占用CPU高的SQL可以用这样的思路SELECT digest_text, count_star, avg_timer_wait/1000000000 AS avg_ms, sum_timer_wait/1000000000 AS total_ms FROM performance_schema.events_statements_summary_by_digest ORDER BY sum_timer_wait DESC LIMIT 20;这条语句能按总执行时间把SQL汇总排序执行时间最长的SQL就是头号嫌疑人排第一的基本没跑。3.3 用SHOW PROCESSLIST和EXPLAIN锁定问题SQL3.3.1 SHOW PROCESSLIST先看当前现场当CPU正在飙高时直接执行SHOW FULL PROCESSLIST;看state列和info列。state列描述当前正在做什么比如Sending data、Sorting result、Creating sort index、Copying to tmp table这些状态都代表CPU在干活。info列能看到正在执行的SQL全文。如果看到大量线程的state都是Sending data说明正在执行大量查询且扫描量很大如果都是Statistics或Sorting result指向排序和统计计算。把当前会话的SQL捞出来后再拿SQL去explain。3.3.2 EXPLAIN看执行计划EXPLAIN SELECT ...;重点看这些列type从好到差依次是system、const、eq_ref、ref、range、index、ALL。ALL是全表扫描index可能是在扫描索引树都要小心。key实际用到的索引如果为NULL说明没走索引。rows预估扫描行数这个值越大CPU计算量越大。Extra如果出现Using filesort、Using temporary、Using where都有优化空间。用explain确认问题SQL之后再对照表结构和索引分析为什么走不到合适的索引。3.4 从执行计划识别索引失效和扫描行数执行计划里最容易迷惑人的是rows列有时候预估行数和实际行数差很多那是因为统计信息过期。MySQL优化器是根据统计信息决定是否走索引的如果innodb_stats_info_seen或者统计信息长时间没更新优化器会选错索引导致原本应该走索引的查询走了全表。遇到执行计划不对劲可以手动触发一次统计信息更新ANALYZE TABLE your_table;再重新看explain有时候这个问题就解决了。如果ANALYZE之后依旧选错可以用FORCE INDEX或者USE INDEX临时指定索引验证一下是不是优化器选型问题但最终还是要从统计信息或者索引设计上解决。4. 对症下药针对不同原因的解决方法4.1 优化慢SQL改写查询与join策略找到那条吃CPU的慢SQL后优化策略要按“先改写、再索引、后调参”的顺序来。改写SQL时我常用的几个策略从“大表驱动小表”改成“小表驱动大表”。MySQL的join是嵌套循环驱动表越小越好被驱动表要有索引。拆分一个大查询为多个小查询。比如那张报表SQL要关联5张表可以把业务拆成两个查询在应用层做数据合并虽然多了一次网络请求但数据库压力大幅下降。把or条件改写为union all。当or两侧的字段不同时索引可能全部失效拆成两个查询合并结果反而能走索引。避免在where子句里做函数运算、避免负向查询!、not in、避免前导模糊匹配。大分页场景用延迟关联select * from table where id (select id from table order by id limit 100000, 1) order by id limit 20先用覆盖索引定位起始id再回表取数据。join策略方面还要注意字段字符集和排序规则必须一致否则关联时无法使用索引。我之前就踩过坑两张表的用户名字段一张是utf8mb4_general_ci另一张是utf8mb4_0900_ai_cijoin直接导致索引失效。4.2 索引优化怎么建、怎么删、怎么验证索引是解决CPU高的最核心手段但索引不是越多越好。建索引时我遵循的几个原则优先给where条件、order by、group by涉及到的字段建索引。联合索引遵循最左前缀原则字段顺序要把等值查询的字段放前面能用索引消除排序的字段一起考虑。不要为了一条SQL单独建一个只有一列的冗余索引能复用联合索引就复用。区分度低的字段比如性别、状态值建索引收益很低除非数据分布极度倾斜并且只查少数状态。覆盖索引是优化回表的利器。如果select的字段都在索引里可以直接从索引树取值不用回表CPU和IO都会大幅下降。建索引之后要用explain验证。之前见过有人建了个索引但查询条件因为隐式类型转换压根用不上建了等于白建。建完索引要注意观察一段时间别因为新索引影响了写入性能。删索引也要谨慎。线上有时候存在大量低质量索引比如重复的联合索引、长时间没被使用的单列索引它们会拖慢insert和update。可以开启userstat或者从performance_schema中找出从未使用过的索引再考虑删除。注意删除前要确认没有业务还在用可以先将索引设为invisible观察几天MySQL 8.0支持ALTER TABLE ... ALTER INDEX ... INVISIBLE。4.3 连接和并发参数调整别让CPU浪费在线程管理上如果是并发太高而非单条SQL拖慢参数调整的方向就不一样了。我优先会看这几个参数max_connections不要设置成无脑大的值。每个连接都需要内存和CPU资源连接数超过实际处理能力后大量连接在排队反而拖垮系统。合理的做法是把max_connections压到应用连接池最大需求稍微多一点让请求在应用侧排队而不是全堆到数据库。thread_cache_size调大一点可以减少线程频繁创建销毁的开销一般设置为16~64之间观察。innodb_thread_concurrency控制InnoDB内部并发执行线程数默认值通常是0表示不限制在CPU已经打满的情况下可以设置成64或者128限制同时进入内核的线程数避免过多线程互相争抢CPU。performance_schema如果完全用不上可以考虑关闭因为它在高压力下本身也会消耗一些CPU。但关闭会让以后排查问题缺少现场建议只在资源非常紧张时临时关闭。还有一点如果是应用侧大量使用短连接操作MySQL强烈建议改用连接池。像HikariCP、Druid这类成熟连接池能复用连接极大减少MySQL端线程创建销毁的开销。4.4 配置与硬件层面的取舍当SQL和索引优化到位之后如果CPU使用率仍然不理想就要考虑改变资源配置或者架构。升级CPU核数如果业务确实需要大量并发计算单机CPU成了瓶颈最简单粗暴的办法是升级CPU但注意MySQL在超过一定核心数后扩展性会下降一味加核不一定划算。增大innodb_buffer_pool_size把更多数据放进内存减少磁盘IO和重复读数据页的开销。注意太大也不行要留出足够内存给系统和其他缓冲。使用SSD替代机械盘对于需要频繁刷脏、读盘的场景SSD能明显降低IO等待也就间接降低了CPU的wa占比。读写分离把只读流量分发到从库降低主库CPU压力。引入Redis这类缓存热点数据在缓存层挡掉一部分数据库查询次数降下来CPU自然就降了。配置和硬件的调整最好有压测数据支撑。在测试环境模拟生产压力对比调整前后得CPU表现再决定上不上。生产环境直接改大参数风险很高。5. 实战复盘与避坑记录5.1 一次误删索引引发的CPU飙升有一次接手一个线上事故现象是某张核心表的CPU使用率突然从30%涨到90%但看慢查询日志并没有新增特别慢的SQL。后来查versioned DDL记录发现在故障时间点之前有人执行了ALTER TABLE ... DROP INDEX删掉了一个普通索引。那张表有唯一键也有普通索引。查询走的是普通索引删掉之后优化器只能走唯一键的索引而唯一键索引和查询条件的匹配度不高扫描行数暴增CPU就这么打满了。所以删索引前一定要先看有没有查询依赖它最好用invisible索引观察一段时间再删。这是吃一堑长一智的教训。5.2 批量更新导致的全表锁和CPU高还遇到过一个问题开发写了个给用户发积分的定时任务代码大致逻辑是查出这个月所有活跃用户然后循环更新积分表。一开始只有几千用户跑得快没发现问题。后面用户涨到几十万这个任务每次跑起来CPU使用率就飙到峰值而且主从延迟也跟着拉长。我当时去看processlist发现大量UPDATE语句在排队每一条都在等锁锁等待本身不直接消耗多少CPU但因为一条update占住大量行锁后续的查询和更新全部阻塞应用端不断重试重试的SQL又继续排队最终CPU被这些“无效劳动”堆满了。解决思路是把批量更新改成分批提交比如每次只更新500条中间sleep一下尽量缩短每个事务持锁的时间。另外在更新条件上加上索引避免每条update都做全表扫描。5.3 避免“缓存失效雪崩”放大CPU压力这个问题不一定在MySQL本身但会直接体现在MySQL CPU上。比如Redis里的热点数据key设置了同一时间过期大量请求在同一瞬间穿透到数据库数据库承接了远超平时的读流量CPU使用率瞬间冲高。从数据库角度看表现为大量重复查询同一批数据慢查询日志里全是相同的SQL。解决办法往往是错开缓存过期时间、加随机过期时间、热点key做成永不过期并主动更新以及必要时在数据库前面加一层本地缓存。总之MySQL的CPU排查有时候不能只盯着数据库整个调用链路的流量情况也要一起看。5.4 日常巡检建议提前发现问题总比事后救火好。我的日常巡检习惯是把下面这几样东西配上监控和告警CPU使用率超过70%就告警不要等到90%才通知。慢查询数量趋势慢查询突然增多往往意味着SQL或索引出了问题。活跃会话数变化活跃会话超过基线的两倍就要关注。临时表和filesort的频率这两个指标的上升代表出现了比较重的排序或分组操作。全表扫描次数这个可以从performance_schema或者监控工具里看全表扫描增多基本等同于CPU要涨。监控的目的是建立“基线”。你一定要知道自己这套系统在正常情况下的CPU大概是20%还是50%这样才能在刚出现异动时敏锐地发现。如果平时从不看曲线等CPU到100%再反应排查难度会大很多。这行干久了你会发现MySQL的CPU问题不是一个孤立的技术问题它背后往往藏着SQL编写习惯、索引设计规范、业务流量模型甚至发布流程的问题。真正有效的方法就是把线上每个CPU飙高案例都复盘透把同样类型的坑沉淀成团队的开发规范下一次同类问题出现时你甚至不用登录服务器看一眼监控曲线就能判断出大概原因。