上个月线上一个订单统计接口突然从200ms涨到8秒业务方连续催了三次运维把慢查询日志丢给我的时候第一反应就是先别急着加索引——你连问题到底出在哪一步都不清楚加什么索引都是碰运气。MySQL性能分析命令的价值就在这种时候体现出来它不是让你拍脑袋猜而是把“到底慢在哪、为什么慢、从哪里下刀”用最直观的方式铺在你面前。这篇是我这几年排查线上问题反复用到的命令沉淀覆盖EXPLAIN、慢查询日志、SHOW PROFILE、SHOW PROCESSLIST和performance_schema适合后端开发、DBA和运维同学收藏起来比每次线上出事临时翻官方文档高效得多。1. 排查逻辑先行性能分析到底在看哪四块1.1 慢SQL的本质逃不出四个维度我处理过的绝大多数MySQL性能问题归根到底都落在四类原因上扫描行数太多、等待耗时太长、锁阻塞、连接资源耗尽。这四个维度基本决定了你该掏出哪条命令。扫描行数对应的是执行计划和索引问题最典型的场景是一条SQL全表扫了500万行实际只返回20条这种问题用EXPLAIN一眼就能定位。等待耗时说的是CPU计算、磁盘IO、网络传输这些环节一条SQL看着没扫多少行但排序、分组、临时表操作把时间吃掉大半这种情况要用慢日志加SHOW PROFILE这类工具看耗时分布。锁阻塞属于并发场景两个事务互相等对方释放行锁或者一个DDL在等一个长事务提交这就要靠SHOW PROCESSLIST和information_schema里的锁视图。连接资源耗尽通常是前两者引发的连锁反应——慢SQL占满连接新请求全部排队表现为数据库连接数打满、接口大面积超时。1.2 命令与场景速查表为了方便你收藏后快速查阅我把常用命令和它们对应的排查场景整理成一张对照表命令核心作用最擅长解决的问题EXPLAIN / EXPLAIN ANALYZE查看执行计划是否走索引、扫描多少行、排序与临时表SHOW PROCESSLIST查看实时会话当前正在跑什么SQL、锁等待、长事务慢查询日志 mysqldumpslow事后统计慢SQL找出耗时最长、出现最频繁的SQLSHOW PROFILE5.7SQL各阶段耗时定位时间花在排序、发送还是拷贝临时表performance_schema / sys schema历史性能统计TOP SQL排行、锁等待血缘、用户负载1.3 先做好状态采集准备这里想多说一句别等线上出事了才想起来开开关。性能分析命令里有相当一部分是“当下执行才有效”但慢日志和performance_schema必须提前开启否则故障发生时你手里没有任何历史数据只能干等下一次复现。我最常用的动态配置是-- 慢查询日志建议在配置文件里固化这里演示动态开启 SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 2; SET GLOBAL log_queries_not_using_indexes ON; -- performance_schema 是全局开关通常在my.cnf中配置 SET GLOBAL performance_schema ON;注意两个细节long_query_time的单位是秒可以设成小数比如0.5代表500毫秒线上业务敏感的话建议从1秒起步没必要一开始就抓所有SQL否则慢日志文件会膨胀得非常快。还有一个坑是SET GLOBAL long_query_time只对新连接生效已存在的连接仍然沿用旧值所以改完参数后最好观察一段时间必要时重启应用池。log_queries_not_using_indexes这个开关能把“没走索引的SQL”也记进慢日志即使它执行时间很短这是发现隐式类型转换、函数包裹索引列等问题的利器。提示修改配置文件时slow_query_log_file的值建议带实例名比如slow-3306.log多实例部署时别让日志写串了。2. EXPLAIN执行计划绝大多数慢SQL的破案现场2.1 一条EXPLAIN输出到底怎么读EXPLAIN是我使用频率最高的命令没有之一。它不真正执行SQL而是让优化器告诉你“我打算怎么执行”。看一个典型例子EXPLAIN SELECT o.order_id, u.name FROM orders o JOIN users u ON o.user_id u.id WHERE o.status 0 AND o.created_at 2024-01-01;输出里最关键的五列是type、key、rows、Extra、key_len。type是访问类型从好到差大致是systemconsteq_refrefrangeindexALL。ALL就是全表扫描这是性能问题的头号嫌疑人index虽然也是全索引扫描但至少比全表好range是索引范围扫描通常意味着WHERE条件命中了范围查询ref是非唯一索引等值匹配const和eq_ref是主键或唯一索引查找属于最优路径。rows是优化器估算的需要扫描的行数这个数字是估算值不一定准但数量级很有参考价值。如果估算的行数是几百万哪怕最终结果只返回几十行这条SQL也基本没救了。key列显示实际选中的索引key_len表示索引使用的字节数从长度可以反推联合索引实际用了几个字段。2.2 type和key_len判断索引有没有走对很多新手只看key不为空就以为走了索引这是误区。索引走到一半和完全没走性能差别也很大。举个例子SELECT * FROM orders WHERE customer_id 100;如果customer_id有单列索引EXPLAIN的type通常是refkey是idx_customer_idrows可能只有几十行这是正常状态。但如果有人在查询里写了SELECT * FROM orders WHERE customer_id 1 101;MySQL的优化器不会对索引列做函数或表达式转换索引列直接参与运算会导致索引失效EXPLAIN会看到typeALLrows直接变成全表行数。这是一个非常经典的“索引明明存在却不生效”案例。再看key_len怎么算以utf8mb4字符集为例一个VARCHAR(50)的字段索引最大字节数是50*42202字节其中2字节是变长字段的长度前缀。如果联合索引是(status, created_at)而你的SQL只用了status一个等值条件那key_len就只有status字段的长度不会包含created_at。看到key_len明显比联合索引总长度短说明后面的字段没有用上联合索引的顺序可能设计不合理。2.3 Extra列里的三个红灯警告Extra列是我第二关注的点这里出现了几个关键词要格外小心。Using filesort表示MySQL需要额外做一次排序操作而不是直接利用索引顺序。注意这个“文件”不一定指磁盘文件也可能在内存里但总之意味着一次额外开销。常见触发场景是ORDER BY的字段没有索引或者排序字段与WHERE条件用的索引不一致。优化方向通常是把排序字段加进联合索引比如(status, created_at)让WHERE status0 ORDER BY created_at直接在索引上完成排序。Using temporary表示执行过程中需要创建临时表常见触发场景是GROUP BY、DISTINCT、某些子查询或UNION。临时表会带来额外的内存或磁盘IO开销如果分组字段没有索引扫描几百行可能无所谓但扫描几百万行再分组数据库基本要被拖垮。Using index这个是绿灯代表覆盖索引查询所需字段都在索引树上不需要回表。我在做查询优化时会尽量把SELECT的字段调整成和索引匹配让这个绿灯多亮起来。2.4 8.0里的EXPLAIN ANALYZE把估算变成实测MySQL 8.0.18开始提供了EXPLAIN ANALYZE它能真实执行SQL并输出每一步的实际耗时、实际行数。用法和EXPLAIN一样只是把关键字换成EXPLAIN ANALYZEEXPLAIN ANALYZE SELECT o.order_id, u.name FROM orders o JOIN users u ON o.user_id u.id WHERE o.status 0 AND o.created_at 2024-01-01;输出里会出现actual time0.123..2.456 rows520000 loops1之类的内容。actual time的第一个值是取第一行数据的耗时第二个值是所有行处理完的耗时rows是实际行数。把actual rows和EXPLAIN估算的rows放在一起对比可以判断优化器的估算是否靠谱。比如索引统计信息过期时估算扫描100行实际却扫了10万行这时候执行计划可能看起来还行实际性能很差。注意EXPLAIN ANALYZE会真实执行SQL线上大查询慎用建议先加个LIMIT或者在备库上跑否则本来一条慢SQL就被你亲手再放大一次。3. 慢查询日志与SHOW PROFILE把时间花在哪挖出来3.1 慢日志的最小可用配置模板EXPLAIN解决的是“SQL执行计划长什么样”但有些SQL执行计划看起来不错实际还是很慢这时候需要知道时间到底消耗在哪个阶段。慢查询日志是抓住嫌疑人的第一步。一个我实际用着很舒服的配置模板[mysqld] slow_query_log 1 slow_query_log_file /data/mysql/log/slow-3306.log long_query_time 1 log_queries_not_using_indexes 1 log_throttle_queries_not_using_indexes 10log_throttle_queries_not_using_indexes这个参数容易被忽略它的作用是限制每分钟记录“未用索引SQL”的数量防止大量无索引查询瞬间刷爆慢日志。没有这个限制的时候我见过一个没走索引的查询把慢日志文件在半小时内撑到20GB直接把磁盘打满。另外long_query_time是支持小数秒的一些核心接口要求毫秒级响应可以把阈值设到0.5甚至0.2但要确保日志轮转和容量监控跟得上。3.2 mysqldumpslow快速汇总慢SQL慢日志是文本文件直接打开看容易被刷屏尤其是高峰期。MySQL自带的mysqldumpslow工具可以把同结构的SQL聚合统计用法非常实用# 按平均查询时间排序取前10条 mysqldumpslow -s at -t 10 /data/mysql/log/slow-3306.log # 按出现次数排序 mysqldumpslow -s c -t 10 /data/mysql/log/slow-3306.log # 按总执行时间排序 mysqldumpslow -s t -t 10 /data/mysql/log/slow-3306.log它会自动把SQL里的具体数字和字符串替换成N和S从而把“同一模板”的SQL合并成一条统计出执行次数、平均耗时、总耗时。我看这个输出的习惯是先按总时间看Top10再按平均时间看Top10前者告诉你哪类SQL最消耗数据库资源后者告诉你哪类SQL单次执行就离谱。如果你装的是Percona分支或者有Perl环境也可以用pt-query-digest分析维度更丰富但mysqldumpslow胜在开箱即用。3.3 SHOW PROFILE的使用姿势与8.0退化问题慢日志告诉你哪条SQL慢SHOW PROFILE告诉你慢在哪一步。在MySQL 5.7时代这是我分析SQL内部各阶段耗时的主要武器SET profiling 1; -- 执行你的问题SQL SELECT * FROM orders WHERE status 0 ORDER BY created_at DESC; -- 查看所有被profile的SQL列表 SHOW PROFILES; -- 查看具体某一条的耗时明细 SHOW PROFILE FOR QUERY 1;输出里会有一系列阶段状态比如Starting、Executing hook on table、Sending data、sorting result、Copying to tmp table。我通常会关注三个点如果Sending data耗时占比极高说明查询在读取和返回数据阶段消耗大大概率是扫描行数过多或IO饱和如果sorting result很长说明排序操作是瓶颈该调整索引或减少排序字段如果Copying to tmp table明显说明临时表操作太重要想想怎么避免GROUP BY或DISTINCT带来的临时表。但是从MySQL 8.0开始PROFILING功能已经被官方移除。实测执行SET profiling1会直接报ERROR 1193 (HY000): Unknown system variable profiling。很多从5.7迁移到8.0的团队会发现以前的监控脚本失效了这其实不是配置问题而是官方用performance_schema替代了它。3.4 8.0下用performance_schema定位耗时阶段MySQL 8.0推荐的做法是通过performance_schema.events_stages_history_long来还原SQL执行的阶段耗时。先把消费者打开UPDATE performance_schema.setup_consumers SET enabled YES WHERE name IN (events_stages_history_long, events_statements_history_long);然后执行你的问题SQL再从历史表里抓数据SELECT EVENT_ID, SQL_TEXT, TIMER_WAIT/1000000000 AS duration_ms FROM performance_schema.events_statements_history_long ORDER BY EVENT_ID DESC LIMIT 5;拿到疑似慢SQL的EVENT_ID后去看它的阶段明细SELECT EVENT_NAME, TIMER_WAIT/1000000000 AS stage_ms FROM performance_schema.events_stages_history_long WHERE NESTING_EVENT_ID 此处替换为你的EVENT_ID ORDER BY TIMER_WAIT DESC;TIMER_WAIT的单位是皮秒所以除以1000000000得到毫秒。这套查询虽然不如SHOW PROFILE一条命令来得直观但能实现同样的目标而且已经把MySQL 8.0的标准分析路径打通了。建议新项目直接上手这种方式别在旧习惯里留恋。4. SHOW PROCESSLIST连接、锁与阻塞一眼看穿4.1 快速定位“现在正在执行什么”慢日志是历史记录PROCESSLIST是实时现场。数据库突然卡顿的时候第一件事永远是执行SHOW FULL PROCESSLIST;它会把当前所有会话列出来核心字段是Command、Time、State、Info。Command表示当前会话在干什么常见取值有Query、Sleep、KilledTime表示当前状态持续了多少秒State说明正在执行的阶段Info是正在执行的SQL语句。我通常直接按Time降序看前几行秒数最大的往往就是元凶。如果这个会话的Command是QueryState是Sending dataInfo是一条聚合查询而Time已经超过几十秒基本可以断定就是这条SQL把数据库拖慢了。这时候先用EXPLAIN分析它不要急着KILL除非CPU和IO已经打满。4.2 高频State状态解读与处理PROCESSLIST里几个高频State值得背下来Waiting for table metadata lock出现频率极高代表当前会话在等待元数据锁。这种状态最常见的诱因是一个长事务还没提交同时有人对这个表执行了ALTER TABLE或者反过来DDL语句在等一个长时间运行的查询结束。排查思路是去information_schema.innodb_trx里找未提交的长事务和业务方确认后结束掉它。Sending data名字有误导性它并不只是在发送数据实际包含读取、过滤、排序等多个阶段慢SQL最常出现在这个状态。Waiting for handler commit表示等待存储引擎提交或者刷盘通常和刷盘频率、IO延迟相关。如果频繁出现建议关注innodb_flush_log_at_trx_commit参数和磁盘IO能力。还有一个容易被忽略的是大量Sleep状态的空闲连接。它们本身不消耗CPU但会占用连接数。当连接数打满时新请求进不来表现为系统“假活”。这类问题的原因是连接池没设置闲置回收不是SQL性能问题。4.3 会话级排查SQL与KILL操作直接执行SHOW FULL PROCESSLIST有时候字段会被截断我更推荐用下面这条SQL来查SELECT id, user, host, db, command, time, state, LEFT(info, 200) AS sql_text FROM information_schema.processlist WHERE command Sleep ORDER BY time DESC LIMIT 20;它可以过滤掉空闲连接直接看到活跃请求。需要处理某个会话时用KILL 12345;不过KILL要谨慎尤其是长事务。KILL一个正在执行写操作的事务会导致回滚回滚本身也可能消耗大量CPU和IO越大的事务回滚越慢。我见过有人KILL一个跑了半小时的批量事务结果回滚又跑了20分钟。所以KILL之前先确认事务大小和业务容忍度。5. sys schema与information_schema把分析变成固定报表5.1 performance_schema和sys schema的关系MySQL 5.7开始默认提供sysschema它本身不存数据而是基于performance_schema提供了一组经过加工的视图把一堆枯燥的事件计数转换成可读性很强的报表。8.0里performance_schema默认开启但你最好还是确认一下SHOW VARIABLES LIKE performance_schema;如果值是OFF需要改配置文件重启实例动态开启不一定在所有版本都能生效。sysschema相关的表如果缺失可以执行mysql_sys安装脚本但8.0安装版通常自带。5.2 用statement_analysis找出代价最高的SQL我最常查的视图是sys.statement_analysis它按SQL语句模板聚合了执行次数、总延迟、平均延迟、扫描行数、返回行数等信息。下面这条SQL基本是我每周巡检的保留项目SELECT query, exec_count, total_latency, avg_latency, rows_examined_avg, rows_sent_avg, rows_examined_avg / rows_sent_avg AS exam_per_sent FROM sys.statement_analysis ORDER BY total_latency DESC LIMIT 10;total_latency的单位是皮秒看的时候自己换算。rows_examined_avg / rows_sent_avg这个比值我特别关注它代表每返回一行数据要扫描多少行。如果这个比值超过几千甚至几万说明这条SQL的过滤性极差典型的“大海捞针”EXPLAIN大概率能看到全表扫描或者选错索引。反过来如果比值很低但total_latency依然很高那瓶颈可能不在扫描行数而在排序、临时表或者锁等待。5.3 锁等待视图查清楚谁在等谁锁问题是并发场景下的老大难sysschema提供了两个视图非常有价值。sys.innodb_lock_waits可以直接看到等待锁的事务和被阻塞的事务以及它们各自执行的SQLSELECT * FROM sys.innodb_lock_waits\G它会输出等待的事务ID、阻塞的事务ID、等待的锁类型以及两个事务对应的查询语句。我遇到死锁或者大量行锁等待时会先用它确认阻塞源头再决定是回滚等待事务、结束长事务还是调整SQL顺序。sys.schema_table_lock_waits则专门看元数据锁等待比如DDL语句卡在等一个查询SELECT * FROM sys.schema_table_lock_waits\G它能列出哪个表、哪个SQL在等待哪个SQL持有锁定位这类问题比翻SHOW PROCESSLIST快得多。顺便说一句MySQL 8.0里information_schema的锁信息也做了调整innodb_locks和innodb_lock_waits被performance_schema.data_locks和data_lock_waits取代如果你在8.0里沿用老查询很可能会发现表不存在。5.4 把分析语句固化成SQL模板sysschema的查询语句普遍比较长我的做法是把高频查询存成.sql文件放到服务器固定目录起名top_sql.sql、lock_waits.sql、processlist.sql。出问题时直接mysql -uroot -p -e source /data/scripts/top_sql.sql省去临时一个字一个字敲命令也避免紧张时拼错。如果公司内有多套MySQL环境我会在每套环境里跑一遍这些模板对比不同实例的TOP SQL差异经常能发现某个环境特有的隐患比如测试环境的SQL不规范问题提前暴露出来。6. 一个慢SQL的完整排查案例命令串起来用6.1 从PROCESSLIST捕获现场前面的命令单独拆开讲都清楚但实际排查是一个串联过程。我举个例子场景是订单统计报表接口每天早上10点变慢数据库CPU从20%飙到60%接口耗时2秒以上。第一步登上数据库执行SHOW FULL PROCESSLIST;一眼看到一条聚合查询SELECT status, COUNT(*), SUM(amount) FROM orders WHERE created_at 2024-11-01 00:00:00 GROUP BY status;这条SQL的Time已经跑了30秒State是Sending dataInfo显示完整SQL。它没有出现在慢日志里因为它是在我执行PROCESSLIST的瞬间正好在跑但这就是现场。6.2 EXPLAIN定案全表扫描加临时表趁它还活着立刻对该SQL做EXPLAINEXPLAIN SELECT status, COUNT(*), SUM(amount) FROM orders WHERE created_at 2024-11-01 00:00:00 GROUP BY status;输出里最扎眼的三列是typeALL、rows5200000、ExtraUsing temporary。原因很清楚orders表有500多万行created_at上没有索引这条SQL被迫全表扫描同时因为GROUP BY status需要聚合又额外产生了临时表。这里有个细节值得单独说查询条件是created_at 2024-11-01 00:00:00字段类型是datetime传入的是字符串。虽然在MySQL里能正常比较但如果字段类型和参数类型不一致会触发隐式类型转换导致索引失效。我查了一下表结构created_at确实是datetime类型参数也是标准日期字符串所以排除了这个因素问题就是单纯缺索引。6.3 建立联合索引后的前后对比考虑到这个报表查询的模式是“按时间范围过滤后按状态分组”我添加了一个联合索引ALTER TABLE orders ADD INDEX idx_created_at_status (created_at, status);为什么这样设计WHERE条件里的created_at是范围查询应该放在联合索引前面status用于GROUP BY放在后面之后MySQL可以尽量通过索引来避免临时表和文件排序。建立索引后再次EXPLAINEXPLAIN SELECT status, COUNT(*), SUM(amount) FROM orders WHERE created_at 2024-11-01 00:00:00 GROUP BY status;这次type变成了rangerows从520万降到了几万Extra里的Using temporary消失了。实际接口耗时从2秒多降到80毫秒CPU在高峰时段也回到了正常水位。这个案例没什么高大上的技巧但它代表了性能分析命令的标准用法先通过PROCESSLIST抓现场再用EXPLAIN看执行计划最后用索引解决扫描和临时表问题。整个过程不到10分钟前提是你对这些命令足够熟悉。我现在的习惯是每周固定跑一遍statement_analysis把TOP SQL过一遍像这种“当时没出事但迟早出事”的语句趁早处理掉比等到业务方来催要体面得多。你把这篇文章收藏了真到下次线上变慢的时候顺着这条链路走一遍大概率就能自己解决问题。