1. 这不是“看看就行”的工具课而是DBA日常救火的实操手册“MySQL性能分析工具的使用”——光看标题很多人会下意识划走又是一节讲EXPLAIN、SHOW PROFILE、slow log的理论课但如果你真在生产环境里扛过数据库告警凌晨三点被慢查询压得睡不着或者刚上线一个新功能用户反馈“点一下要等十秒”而你翻遍应用日志却找不到瓶颈在哪……那你就会明白这一章根本不是教你怎么“用工具”而是教你怎么在千头万绪的数据洪流里精准定位那根卡住系统的刺。我带过的几个团队里新人常犯的错是一出问题就开pt-query-digest扫慢日志结果导出2000行SQL挨个EXPLAIN两小时后发现95%都是无关紧要的后台统计任务老手则习惯性先看SHOW PROCESSLIST但往往只盯State列里的Sending data却忽略了Time列里那个已经挂了187秒的Copying to tmp table。这些都不是工具不会用而是没理解每个工具背后的数据采集逻辑、采样粒度和误差边界。比如Performance Schema默认只开启部分instrument你查不到file I/O wait不是它没能力是你没打开wait/io/file/*这组开关再比如sys schema里statement_analysis视图汇总的是归一化后的SQL模板SELECT * FROM users WHERE id 1和SELECT * FROM users WHERE id 1000会被合并成一条记录如果你只盯着“平均执行时间”就可能漏掉某条特定ID查询因索引失效导致的单次爆炸性延迟。这一章的核心从来不是罗列命令参数而是建立一套分层诊断思维从宏观资源水位CPU、IO、内存→ 中观线程状态连接数、锁等待、事务堆积→ 微观SQL行为执行计划、临时表、排序缓冲区。每层对应一组工具它们不是孤立的而是像手术刀组top和iostat是探针帮你快速判断是系统级瓶颈还是数据库内部争抢information_schema.PROCESSLIST和performance_schema.events_waits_current是内窥镜让你看清当前正在发生什么而EXPLAIN FORMATJSON和optimizer_trace才是显微镜能放大到索引选择、条件过滤、连接算法的每一个决策节点。真正高效的DBA不是把所有工具都背下来而是清楚在什么场景下该用哪一把刀切多深切到哪一层组织才停止。所以别把它当成第九章“高级特性”的收尾它其实是整套MySQL能力体系的“操作系统内核”——没有这套分析能力索引优化是蒙的参数调优是猜的高可用切换是赌的。接下来的内容我会完全按真实故障排查现场来组织不讲抽象概念只还原一次从告警响起、登录服务器、逐层缩小范围到最后定位到一行SQL、一个索引、甚至一个字符比如WHERE status active 末尾的空格导致索引失效的全过程。所有命令、配置、判断依据都来自某次电商大促前夜的真实压测复盘连my.cnf里那行被注释掉的innodb_print_all_deadlocks ON都是踩坑后加上的。2. 工具链全景图为什么必须分三层使用而不是一股脑全开2.1 第一层系统级资源监控——先确认“是不是MySQL的问题”很多DBA一接到“数据库慢”的告警第一反应就是登录MySQL执行SHOW FULL PROCESSLIST。这就像医生不量体温、不听心肺直接给病人开CT单。真正的第一步永远是看操作系统是否已亮红灯。因为MySQL再慢也可能是宿主机CPU被其他进程吃光或是磁盘IOPS被打满甚至只是网卡中断处理不过来。我见过最典型的案例某支付系统在每日00:00批量扣费时TPS断崖式下跌。运维同学查MySQLThreads_running只有3Innodb_row_lock_time_avg也正常百思不得其解。最后用vmstat 1发现waI/O wait持续在95%以上iostat -x 1显示%util为100%await飙升到200ms。进一步用pidstat -d 1定位到是备份脚本mysqldump在同时读取多个大表与业务查询争抢磁盘队列。解决方案不是调MySQL参数而是给备份加--single-transaction并错峰执行。这一层的关键工具组合是top/htop看整体CPU负载、MySQL进程mysqld的CPU占用率。注意区分us用户态和sy内核态如果sy高大概率是上下文切换或锁竞争vmstat 1重点关注r运行队列长度、b阻塞进程数、waI/O等待、si/soswap交换。r CPU核心数意味着CPU已饱和iostat -x 1核心看%util设备利用率、awaitI/O平均等待时间、svctm服务时间、%idle空闲率。await svctm说明I/O队列积压严重pidstat -d 1精确到进程级别的I/O读写量kB_rd/s, kB_wr/s快速识别“罪魁祸首”。提示iostat -x的%util不是“使用率”而是“设备忙于处理I/O请求的时间百分比”。即使%util100%只要await不高10ms说明设备响应及时只是持续工作。真正危险的是%util未满但await飙升这意味着请求在队列中排队等待。2.2 第二层MySQL内部状态监控——确认“是哪个模块在拖慢”当系统资源正常wa5%,rCPU核数,await10ms问题就一定在MySQL内部。这时不能跳进SQL细节而要先摸清“战场态势”有多少连接在排队哪些线程在等锁事务堆积在哪里这是决定后续深入方向的关键分水岭。information_schema.PROCESSLIST是入门级工具但它只能看到“快照”且State列含义模糊比如Sending data既可能是扫描索引也可能是网络传输慢。更可靠的是performance_schema它是MySQL 5.6内置的“飞行数据记录仪”默认开启但instrument有限。必须手动启用关键采集项-- 开启等待事件采集最常用 UPDATE performance_schema.setup_consumers SET ENABLED YES WHERE NAME LIKE events_waits%; UPDATE performance_schema.setup_instruments SET ENABLED YES, TIMED YES WHERE NAME IN (wait/io/file/innodb/innodb_data_file, wait/io/file/innodb/innodb_log_file, wait/lock/table/sql/handler, wait/synch/mutex/innodb/); -- 开启语句事件采集用于慢SQL分析 UPDATE performance_schema.setup_consumers SET ENABLED YES WHERE NAME events_statements_history_long; UPDATE performance_schema.setup_instruments SET ENABLED YES, TIMED YES WHERE NAME LIKE statement/sql/%; -- 查看当前最耗时的等待事件 SELECT EVENT_NAME, COUNT_STAR, SUM_TIMER_WAIT, AVG_TIMER_WAIT FROM performance_schema.events_waits_summary_global_by_event_name WHERE SUM_TIMER_WAIT 0 ORDER BY SUM_TIMER_WAIT DESC LIMIT 10;这段配置不是随便写的。wait/io/file/innodb/innodb_data_file对应数据文件读写wait/lock/table/sql/handler对应表级锁MyISAM或MDL锁MySQL 5.5wait/synch/mutex/innodb/对应InnoDB内部互斥锁。我曾在一个高并发订单库上通过此查询发现wait/synch/mutex/innodb/buf_pool_mutex的SUM_TIMER_WAIT异常高最终定位到是innodb_buffer_pool_size设置过大占物理内存80%导致Linux内核内存回收压力剧增频繁触发kswapd反而拖慢了Buffer Pool访问。注意performance_schema本身有内存开销setup_instruments全开会导致约5%-10%的性能损耗。生产环境建议只开启明确需要的instrument用完及时关闭。events_statements_history_long默认只存10000条大流量下可能被覆盖需结合events_statements_summary_by_digest做聚合分析。2.3 第三层SQL级深度剖析——找到“具体哪条SQL、哪个环节出了问题”当确认问题是SQL层面后就不能再泛泛而谈“慢查询”。必须精确到是解析慢优化慢执行慢还是返回结果慢每一层都有专属工具解析与优化阶段用EXPLAIN FORMATJSON。普通EXPLAIN只给概览FORMATJSON会输出完整的优化器决策树包括used_columns实际用到的列、range_analysis范围扫描分析过程、considered_execution_plans备选执行计划对比。我曾用它发现一个JOIN查询优化器因统计信息陈旧错误估计了右表行数预估100行实际100万行选择了嵌套循环而非哈希连接导致执行时间从200ms暴涨到12秒。执行阶段用optimizer_trace。它记录优化器每一步的代价计算是调试复杂查询的终极武器。开启方式SET optimizer_traceenabledon,one_lineoff; SET optimizer_trace_max_mem_size1048576; -- 1MB内存 SELECT /* MAX_EXECUTION_TIME(1000) */ * FROM orders o JOIN users u ON o.user_idu.id WHERE o.statuspaid; SELECT * FROM information_schema.OPTIMIZER_TRACE;输出中rows_estimation告诉你各表预估行数considered_execution_plans列出所有尝试过的计划及costattaching_conditions_to_tables展示条件如何下推。当看到cost: 12345.67远高于另一计划的cost: 89.23你就知道优化器为何选错路了。返回阶段用SHOW PROFILE已废弃但仍有参考价值或sys.schema_table_statistics_with_buffer。SHOW PROFILE能显示Sending data、Copying to tmp table等阶段耗时但仅对当前会话有效。更现代的方式是查sys库视图SELECT * FROM sys.schema_table_statistics_with_buffer WHERE table_schemaecommerce AND table_nameorders ORDER BY io_read_latency DESC LIMIT 5;它直接告诉你哪张表的I/O延迟最高结合io_read_requests可判断是随机读小块多次还是顺序读大块一次。这三层不是线性流程而是动态跳转。比如第二层发现大量waiting for table metadata lock就要立刻切回第一层查show processlist找持有MDL锁的长事务第三层看到Using temporary; Using filesort就要回到第二层查Sort_merge_passes状态变量是否激增。工具链的价值在于给你一张动态更新的“作战地图”而不是一份静态说明书。3. 实战拆解一次从告警到修复的完整闭环3.1 告警触发凌晨2:17监控平台弹出三条红色告警MySQL_QPS 50正常值300-800MySQL_Average_Response_Time 2500ms正常200msThreads_connected 200连接池上限250这不是偶发抖动而是持续5分钟的稳定低谷。我立刻登录跳板机执行第一层检查# 看CPU和内存 $ top -b -n1 | head -20 # 发现 mysqld 进程 %CPU 仅12%但 %MEM 达78%且 swap 有使用si/so 0 # 立刻怀疑内存不足触发swap # 看I/O $ iostat -x 1 3 | grep -E (avg-cpu|sda|nvme) # sda 的 %util 98% await 142ms svctm 8ms → 队列严重积压 # 看进程I/O $ pidstat -d 1 3 | grep mysqld # mysqld 的 kB_rd/s 稳定在120MB/s kB_wr/s 8MB/s → 读压力巨大结论系统级I/O瓶颈且MySQL在疯狂读磁盘。问题不在SQL逻辑而在数据无法常驻内存。下一步确认Buffer Pool命中率mysql SHOW ENGINE INNODB STATUS\G # 在 BUFFER POOL AND MEMORY 段看到 # Buffer pool hit rate 998 / 1000 → 表面看很高但注意这是1000次中的命中数 # 计算真实命中率(1 - Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests) * 100 mysql SHOW GLOBAL STATUS LIKE Innodb_buffer_pool_read%; # Innodb_buffer_pool_read_requests: 123456789 # Innodb_buffer_pool_reads: 1234567 → 命中率 (1 - 1234567/123456789)*100 ≈ 99.0% # 但绝对值123万次磁盘读对SSD也是压力3.2 锁定热点表用sys库快速定位“罪魁祸首”既然Buffer Pool不够用一定是某些大表或热数据没被缓存。sys库提供了现成视图-- 查I/O最重的表 SELECT * FROM sys.io_by_table_by_latency WHERE total_latency 1000000000000 -- 1秒以上 ORDER BY total_latency DESC LIMIT 5; -- 结果指向 ecommerce.orders 表total_latency 3.2秒read_latency 占92% -- 再查该表的索引使用 SELECT * FROM sys.schema_index_statistics WHERE table_schemaecommerce AND table_nameorders ORDER BY rows_selected DESC LIMIT 3;发现orders表的主键索引PRIMARY被选中次数最多但rows_selected高达2.3亿次而rows_inserted仅1200万。这意味着大量查询在扫描主键继续深挖-- 查最耗时的SQL模板归一化后 SELECT digest_text, count_star, sum_timer_wait, avg_timer_wait, sum_rows_examined, avg_rows_examined FROM performance_schema.events_statements_summary_by_digest WHERE schema_nameecommerce AND digest_text LIKE %orders% ORDER BY sum_timer_wait DESC LIMIT 5;结果第一条是SELECT * FROM orders WHERE user_id ? AND status ? ORDER BY created_at DESC LIMIT ? count_star: 14200, sum_timer_wait: 12345678900000 (12.3秒), avg_timer_wait: 869ms这就是元凶平均每条执行869ms每天执行1.4万次总耗时超4小时。但user_id和status都有索引为何这么慢3.3 深度剖析EXPLAIN FORMATJSON揭示索引失效真相对这条SQL执行EXPLAIN FORMATJSONEXPLAIN FORMATJSON SELECT * FROM orders WHERE user_id 12345 AND status shipped ORDER BY created_at DESC LIMIT 20;关键输出片段{ query_block: { select_id: 1, table: { table_name: orders, access_type: index, key: idx_user_status, key_length: 10, rows: 123456, filtered: 100, using_index: false, using_filesort: true, using_temporary: false, attached_condition: (ecommerce.orders.status shipped) } } }access_type: index不是ref说明没走索引查找而是全索引扫描idx_user_status是(user_id, status)联合索引。rows: 123456证明它扫描了12万行才找到匹配的。为什么因为ORDER BY created_at DESC无法利用(user_id, status)索引MySQL被迫先扫描整个索引找user_id12345 AND statusshipped再对结果集排序。验证SHOW INDEX FROM orders确认idx_user_status确实没有包含created_at。而created_at上有单独索引但优化器认为联合索引idx_user_status的ref查找比created_at索引的range扫描更优统计信息误导。3.4 终极修复三步落地效果立竿见影第一步创建覆盖索引Covering Index-- 新建索引将ORDER BY字段加入使查询能完全走索引 CREATE INDEX idx_user_status_created ON orders (user_id, status, created_at DESC); -- 注意MySQL 8.0 支持降序索引5.7需用函数索引或调整查询第二步强制使用新索引避免优化器误判-- 在应用代码中改写SQL添加USE INDEX SELECT * FROM orders USE INDEX (idx_user_status_created) WHERE user_id 12345 AND status shipped ORDER BY created_at DESC LIMIT 20;第三步刷新统计信息让优化器重新学习-- 分析表更新统计信息 ANALYZE TABLE orders; -- 或更激进删除旧索引统计5.7 OPTIMIZE TABLE orders;效果验证执行EXPLAINaccess_type变为rangerows从12万降至230平均执行时间从869ms降至18msInnodb_buffer_pool_reads24小时内下降67%QPS从50回升至620响应时间稳定在140ms。实操心得不要迷信ANALYZE TABLE。在大表上它采样率默认很低innodb_stats_sample_pages20可能导致统计失真。我们后来将innodb_stats_persistentON并手动设置innodb_stats_transient_sample_pages100确保采样更准。另外USE INDEX是临时方案长期应推动应用层移除提示靠索引设计和统计信息保证优化器自主选择最优计划。4. 避坑指南那些文档里不会写的血泪教训4.1slow_query_log的四大隐形陷阱slow_query_log是DBA的“案发现场”但它的日志内容充满误导性陷阱1long_query_time的单位是秒但精度是微秒设置long_query_time1不代表所有1秒的查询都会记录。MySQL 5.1默认开启log_queries_not_using_indexes此时哪怕查询0.1秒只要没走索引也会记。更隐蔽的是long_query_time在会话级可动态修改SET long_query_time0.5后该会话下所有500ms的查询都会记但全局配置仍是1秒。线上曾因此误判以为某个模块突然变慢实则是开发在测试环境改了会话参数。陷阱2microseconds精度导致“伪慢查询”日志里常见Query_time: 0.999876看起来快到1秒边缘。但这是微秒级四舍五入的结果。实际执行时间可能是0.999876秒也可能是0.9998755秒四舍五入后相同。我们曾用pt-query-digest分析发现这类“临界查询”占慢日志的37%但其中82%在压测中无法复现。解决方案日志中增加Lock_time和Rows_sent过滤掉Rows_sent0的管理类查询如SHOW VARIABLES。陷阱3SET timestamp导致时间戳错乱应用框架如Spring JDBC常在事务开始时执行SET timestamp1234567890以保证主从一致性。这会导致慢日志里的# Time显示为这个固定时间而非真实执行时间。排查时若只看# Time会误以为所有慢查询集中在某一秒爆发。正确做法结合# UserHost和# Query_time忽略# Time。陷阱4log_outputTABLE的性能黑洞将慢日志写入mysql.slow_log表看似方便查询但每次写入都是INSERT INTO mysql.slow_log在高并发下会成为新的性能瓶颈。某次大促slow_log表的INSERT等待时间占总innodb_row_lock_time的40%。改为log_outputFILE并用pt-query-digest定时解析问题立解。4.2EXPLAIN的五个致命误读误读1type: ALL一定最差不一定。对小表1000行全表扫描ALL可能比走索引再回表更快因为避免了随机I/O。EXPLAIN的rows列显示预估扫描行数但ALL的rows是表总行数range的rows是预估范围行数。需结合Handler_read_rnd_next状态变量看实际回表次数。误读2Extra: Using filesort等于性能灾难如果排序数据量小sort_buffer_sizeMySQL在内存中完成速度极快。只有当sort_merge_passes 0SHOW STATUS LIKE sort_merge_passes时才表示排序溢出到磁盘这才是问题。我们曾将sort_buffer_size从2M调至8M使sort_merge_passes从每秒12次降为0。误读3key_len越大越好key_len是索引使用的字节数。key_len10user_idint statusvarchar(10)比key_len4仅user_id更能过滤数据但前提是status字段在WHERE中被用到。如果查询只用user_idkey_len10说明索引没被充分利用应建单列索引。误读4filtered: 100.00代表100%准确filtered是优化器对WHERE条件过滤率的预估基于统计信息。当status字段分布极度不均如99%是pending1%是shipped而统计信息未更新filtered可能显示100.00实际只有1%数据匹配。此时ANALYZE TABLE比重建索引更有效。误读5possible_keys为空就无索引可用可能是索引列参与了函数运算WHERE YEAR(created_at)2023或存在隐式类型转换user_id是INT但传入字符串12345。此时possible_keys为空但key列也可能为空。用SHOW WARNINGS看优化器警告“Cannot use range access on index... due to type or collation conversion”。4.3performance_schema的三大配置雷区雷区1setup_instruments全开必死performance_schema默认只开基础instrument。全开UPDATE setup_instruments SET ENABLEDYES会使MySQL性能下降30%-50%尤其在高并发短连接场景。某次误操作后QPS从1200暴跌至300。正确姿势按需开启用完关闭。例如查锁问题只开wait/lock/...查I/O问题只开wait/io/file/...。雷区2events_statements_history_long的容量陷阱该表默认10000行存储最近10000条语句。但在QPS 1000的系统上10秒就填满。新语句会覆盖旧语句导致你想查的历史SQL已被冲掉。解决方案增大performance_schema_events_statements_history_long_size需重启或改用events_statements_summary_by_digest做聚合分析它只存摘要不易溢出。雷区3setup_actors的默认白名单performance_schema默认只监控rootlocalhost和%%用户。如果应用用app_user10.0.1.%连接它的语句事件不会被采集必须手动插入INSERT INTO performance_schema.setup_actors VALUES (%, app_user, %, YES, YES);这些不是犄角旮旯的冷知识而是我在三次重大故障复盘中亲手写在故障报告“根本原因”栏里的内容。它们不会出现在官方文档的“Usage”章节因为文档只告诉你“怎么用”而实战教会你“为什么不能那样用”。5. 工具之外构建可持续的性能分析能力5.1 建立“黄金指标”监控清单告别被动救火工具是手指标是眼。没有明确的指标阈值再好的工具也只是摆设。我们团队沉淀了一套“黄金五指标”每天晨会必看指标正常阈值危险阈值采集方式异常含义Innodb_buffer_pool_hit_rate99.5%95%SHOW GLOBAL STATUSBuffer Pool严重不足需扩容或优化查询Threads_running50100SHOW STATUS当前活跃线程过多可能有长事务或锁等待Innodb_row_lock_time_avg50ms200msSHOW STATUS行锁争抢严重检查热点行更新Slow_queries(per minute)550SHOW STATUS慢查询突增立即查slow.logCreated_tmp_disk_tables(per minute)10100SHOW STATUS排序/分组溢出到磁盘检查sort_buffer_size注意阈值不是绝对的需根据业务峰值动态调整。例如大促期间Threads_running正常值可设为80。关键是建立基线Baseline用pt-stalk自动抓取异常时的完整状态快照SHOW PROCESSLIST,INNODB STATUS,STATUS等形成可追溯的证据链。5.2 将分析流程固化为Checklist新人也能快速上手再资深的DBA半夜被叫醒时也会大脑空白。我们把上述三层分析法压缩成一页纸的《MySQL性能急救Checklist》贴在每位值班工程师的显示器边框上[ ] Step 1系统层3分钟 □ topmysqld %CPU 80% → 查应用层或SQL □ vmstat 1wa 10% → 查iostat □ iostat -x 1await 20ms → 查pidstat定位进程 [ ] Step 2MySQL层5分钟 □ SHOW PROCESSLIST有StateLocked或Time60s的线程 □ SHOW ENGINE INNODB STATUSSEMAPHORES段有等待TRANSACTIONS段有长事务 □ SELECT * FROM sys.wait_classes_global_by_avg_latency哪个等待事件最耗时 [ ] Step 3SQL层10分钟 □ 慢日志找Query_time最大的SQL用EXPLAIN FORMATJSON分析 □ 若无慢日志查sys.statement_analysis找avg_latency最高的digest □ 对目标SQLEXPLAIN → optimizer_trace → 检查索引覆盖、统计信息 [ ] Step 4验证与闭环2分钟 □ 修改后用pt-query-digest对比前后QPS、Latency □ 更新监控阈值将本次故障加入知识库这张表的价值不在于它多高深而在于它把混沌的“救火”变成了可复制的“流水线”。新同事入职第三天就能独立处理80%的常规告警。5.3 性能分析的终极心法少即是多最后分享一个反直觉的经验最好的性能分析是让分析工具尽量少工作。不要等slow_query_log满了才去查而要在long_query_time设为0.1秒时就定期用pt-query-digest跑出Top 10主动优化不要等Threads_connected接近上限才扩容而要根据Threads_created的增长速率SHOW STATUS LIKE Threads_created预测连接池需求不要等Innodb_buffer_pool_reads飙升才调innodb_buffer_pool_size而要监控Innodb_buffer_pool_read_requests与Innodb_buffer_pool_reads的比值当比值从1000:1降到500:1时就该行动了。工具是镜子照出问题但镜子本身不解决问题。真正的能力是在问题出现前就从镜子里看到它的影子。这一章教的所有命令、参数、步骤最终都要服务于一个目标让你的眼睛比监控告警更早地发现那根即将卡住系统的刺。当你不再需要深夜登录服务器而是白天从容地查看周报里“Buffer Pool命中率稳定在99.8%”时你就真正掌握了MySQL性能分析的精髓——它不是一门技术而是一种预见性的思维方式。