1. 为什么慢查询日志不够用诊断工具的真正价值定位很多人一提到MySQL性能诊断第一反应就是打开慢查询日志把long_query_time调低然后盯着日志文件里那些超过阈值的SQL发呆。这个做法本身没错但如果你只依赖慢查询日志排查效率会非常低甚至经常出现“日志里明明没有慢SQL但系统就是卡”的尴尬局面。我经历过好几次这样的情况业务方反馈接口响应时间从200ms涨到了2s但慢查询日志里干干净净。后来排查发现问题出在大量执行时间只有几十毫秒、但每秒执行上千次的SQL上。单条SQL不慢但累积起来把CPU打满了。慢查询日志只记录“单次执行超过阈值”的语句对这种“蚂蚁搬家式”的性能消耗完全无感。这就是为什么我们需要一套完整的数据库性能诊断工具链。慢查询日志只是最基础的一环它解决的是“哪些SQL单次执行慢”的问题但数据库性能问题至少还包括以下几类高频短耗时SQL导致的资源争抢单条不慢但并发高CPU或IO被吃满。锁等待与死锁SQL本身执行很快但被其他事务阻塞等待时间长。连接数暴涨导致的线程调度开销连接池配置不当数据库侧大量线程上下文切换。Buffer Pool命中率下降导致的磁盘IO飙升SQL没变但数据量增长后缓存不够用了。临时表与文件排序引发的磁盘写入Using temporary和Using filesort在数据量大时非常致命。这些场景慢查询日志要么记录不到要么记录的信息不足以定位根因。所以一个合格的MySQL性能诊断体系应该是“慢查询日志 实时状态监控 执行计划分析 锁与事务诊断 系统资源关联”的组合拳。这篇文章我会从实战角度出发把我在实际项目中反复使用的诊断工具和排查思路完整梳理一遍。不管你是刚接触MySQL运维的开发人员还是已经有一定经验的DBA都能从中找到可以直接复用的命令、参数配置和排查路径。文章会涉及具体的SQL语句、系统命令和参数调优但不会停留在“背命令”的层面每个工具我都会解释它解决什么问题、在什么场景下用、以及我踩过哪些坑。2. 实时状态监控用SHOW命令和Performance Schema快速定位瓶颈2.1 SHOW GLOBAL STATUS最被低估的实时诊断入口很多人觉得SHOW GLOBAL STATUS输出太多、太杂不如直接看监控图表。但在我实际排查问题时这个命令往往是第一个要跑的。原因很简单它不需要额外安装任何东西任何MySQL实例都能直接用而且能快速给出几个关键指标的当前值。我通常会重点关注以下几组指标SHOW GLOBAL STATUS WHERE Variable_name IN ( Threads_connected, Threads_running, Queries, Com_select, Com_insert, Com_update, Com_delete, Innodb_buffer_pool_read_requests, Innodb_buffer_pool_reads, Innodb_row_lock_waits, Innodb_row_lock_time_avg, Created_tmp_disk_tables, Created_tmp_tables, Select_full_join, Select_scan );这里面有几个指标需要特别解释Threads_running当前正在执行的线程数不包括等待连接的线程。这个值如果持续超过CPU核数的2倍说明数据库侧有严重的并发争抢。我一般会把它和Threads_connected对比看如果连接数很高但running很低说明大量连接处于空闲或等待状态问题可能在应用侧连接池配置。Innodb_buffer_pool_read_requests vs Innodb_buffer_pool_reads前者是逻辑读请求次数后者是物理读次数。命中率计算公式是(1 - reads/read_requests) * 100%。如果命中率低于99%说明Buffer Pool不够大或者有大量冷数据被频繁访问。我曾经遇到一个案例命中率从99.9%掉到97%原因是某个报表查询全表扫描了一张5000万行的表把热数据全挤出去了。Created_tmp_disk_tables / Created_tmp_tables这个比值如果超过10%说明有大量临时表被写到磁盘。常见原因是GROUP BY、ORDER BY、DISTINCT操作的数据量超过了tmp_table_size和max_heap_table_size的限制。解决办法要么调大这两个参数要么优化SQL减少排序数据量。Innodb_row_lock_time_avg平均行锁等待时间单位毫秒。如果这个值超过100ms说明有严重的锁竞争。需要结合Innodb_row_lock_waits一起看等待次数多且平均时间长基本可以确定是锁冲突问题。注意SHOW GLOBAL STATUS给出的是自实例启动以来的累计值不是瞬时值。要计算某个时间段内的增量需要间隔一段时间采样两次然后做差值。我一般用mysqladmin extended-status -r -i 2来实时刷新每2秒输出一次增量。2.2 Performance Schema细粒度诊断的利器Performance Schema是MySQL 5.7之后越来越完善的一个内置诊断引擎。它通过插桩的方式采集数据库内部各种事件包括SQL执行、锁等待、文件IO、网络IO等。虽然开启它会带来大约5%-10%的性能开销但在排查复杂问题时这点开销完全值得。我常用的几个Performance Schema表events_statements_summary_by_digest按SQL指纹聚合的执行统计。这个表能告诉你哪类SQL执行次数最多、总耗时最长、平均延迟最高。和慢查询日志不同它记录所有SQL不管快慢。SELECT DIGEST_TEXT, COUNT_STAR AS exec_count, ROUND(SUM_TIMER_WAIT/1000000000, 2) AS total_ms, ROUND(AVG_TIMER_WAIT/1000000000, 2) AS avg_ms, ROUND(SUM_ROWS_EXAMINED/COUNT_STAR, 0) AS avg_rows_examined, ROUND(SUM_ROWS_SENT/COUNT_STAR, 0) AS avg_rows_sent FROM performance_schema.events_statements_summary_by_digest ORDER BY SUM_TIMER_WAIT DESC LIMIT 20;这个查询能快速找出“总耗时最高”的20类SQL。注意看avg_rows_examined和avg_rows_sent的比值如果扫描行数远大于返回行数说明索引效率很低。events_waits_summary_global_by_event_name按等待事件类型聚合。这个表能告诉你数据库时间花在哪里了是IO等待、锁等待还是CPU计算。SELECT EVENT_NAME, COUNT_STAR AS waits, ROUND(SUM_TIMER_WAIT/1000000000, 2) AS total_ms, ROUND(AVG_TIMER_WAIT/1000000, 2) AS avg_us FROM performance_schema.events_waits_summary_global_by_event_name WHERE COUNT_STAR 0 ORDER BY SUM_TIMER_WAIT DESC LIMIT 15;如果wait/io/file/innodb/innodb_data_file排在前列说明磁盘IO是瓶颈如果wait/synch/mutex/innodb/开头的排在前列说明有内部锁竞争。data_locks和data_lock_waits这两个表在MySQL 8.0中用来替代INNODB_LOCKS和INNODB_LOCK_WAITS。当出现锁等待时可以直接查询当前谁在等谁SELECT r.ENGINE_TRANSACTION_ID AS waiting_trx, r.THREAD_ID AS waiting_thread, r.OBJECT_NAME AS waiting_table, b.ENGINE_TRANSACTION_ID AS blocking_trx, b.THREAD_ID AS blocking_thread, b.OBJECT_NAME AS blocking_table FROM performance_schema.data_lock_waits w JOIN performance_schema.data_locks r ON w.REQUESTING_ENGINE_LOCK_ID r.ENGINE_LOCK_ID JOIN performance_schema.data_locks b ON w.BLOCKING_ENGINE_LOCK_ID b.ENGINE_LOCK_ID;这个查询在排查死锁和长时间锁等待时非常有用能直接定位到阻塞的源头事务。2.3 监控指标采集的实战配置建议Performance Schema默认只开启部分消费者consumer很多诊断表是空的。我一般会在my.cnf里做如下配置[mysqld] performance_schema ON performance_schema_consumer_events_statements_current ON performance_schema_consumer_events_statements_history ON performance_schema_consumer_events_statements_history_long ON performance_schema_consumer_events_waits_current ON performance_schema_consumer_events_waits_history ON performance_schema_consumer_events_waits_history_long ON performance_schema_consumer_global_instrumentation ON performance_schema_consumer_thread_instrumentation ON提示history_long表会保留更多历史事件但内存开销也更大。如果实例内存紧张可以只开current和history。另外Performance Schema的内存是预分配的修改配置后需要重启实例。3. 执行计划深度解读EXPLAIN之外你还需要看什么3.1 EXPLAIN的局限性与EXPLAIN ANALYZE的补充EXPLAIN是每个MySQL使用者最熟悉的工具但它有一个根本性局限它展示的是优化器“计划怎么做”而不是“实际怎么做”。优化器的估算可能因为统计信息过期、数据倾斜、索引选择性问题而严重偏离实际。MySQL 8.0.18引入了EXPLAIN ANALYZE它会真正执行SQL并输出实际执行统计。这个功能在排查“优化器选错索引”的问题时特别有用。EXPLAIN ANALYZE SELECT o.order_id, o.total_amount, c.customer_name FROM orders o JOIN customers c ON o.customer_id c.customer_id WHERE o.created_at 2024-01-01 AND o.status completed;输出结果中会包含actual time、rows、loops等实际执行信息。如果某个节点的actual rows远大于estimated rows说明统计信息不准需要执行ANALYZE TABLE更新统计信息。我遇到过一个典型案例一张订单表有2亿行数据status字段只有三个值pending、completed、cancelled优化器估算statuscompleted会返回30%的行于是选择了全表扫描。但实际上由于业务特点completed状态的订单只占5%。执行ANALYZE TABLE orders之后优化器修正了估算改用了索引。3.2 执行计划中的危险信号看EXPLAIN结果时以下几个信号需要高度警惕信号含义常见原因处理方向typeALL全表扫描无可用索引或索引选择性差添加合适索引或优化SQLtypeindex全索引扫描索引覆盖但需扫描整个索引树检查是否缺少更优索引ExtraUsing filesort文件排序ORDER BY字段无索引添加排序字段索引ExtraUsing temporary临时表GROUP BY/DISTINCT无索引支持优化索引或减少数据量rows值极大预估扫描行数多索引选择性差或统计信息过期ANALYZE TABLE或换索引filtered值极低过滤后剩余行少索引过滤效果差考虑复合索引调整顺序其中Using filesort和Using temporary是最常见的两个性能杀手。很多人看到这两个词就紧张但需要区分情况如果rows很小比如几十行filesort的开销可以忽略如果rows达到几万甚至几十万那就必须优化了。3.3 索引选择性与直方图统计MySQL 8.0引入了直方图统计Histogram可以针对非索引列收集数据分布信息帮助优化器做出更准确的估算。-- 为status列创建直方图分10个桶 ANALYZE TABLE orders UPDATE HISTOGRAM ON status WITH 10 BUCKETS; -- 查看直方图信息 SELECT * FROM information_schema.COLUMN_STATISTICS WHERE TABLE_NAME orders AND COLUMN_NAME status;直方图特别适合那些“数据分布不均匀但又不适合建索引”的列。比如一个is_deleted字段99%的值是01%是1查询WHERE is_deleted 1时优化器如果不知道分布情况可能会选择全表扫描。有了直方图优化器就知道只返回1%的行走索引更划算。注意直方图不是万能的它只影响优化器的估算不改变实际执行。而且直方图需要定期更新否则数据分布变化后反而会误导优化器。我一般建议在业务低峰期通过定时任务每周更新一次关键列的直方图。4. 锁与事务诊断从“卡住了”到“谁卡了谁”的完整排查链路4.1 锁等待的快速定位方法当业务反馈“某个操作一直转圈”时第一步是确认是不是锁等待。最直接的方法是查询information_schema.INNODB_TRX和performance_schema.data_lock_waits。-- 查看当前运行中的事务 SELECT trx_id, trx_state, trx_started, TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS duration_sec, trx_mysql_thread_id, trx_query FROM information_schema.INNODB_TRX ORDER BY trx_started ASC;如果看到某个事务的trx_state是LOCK WAIT并且duration_sec很大说明它被阻塞了。接下来用前面的data_lock_waits查询找到阻塞它的事务。我通常会把这两个查询组合成一个“锁等待全景图”SELECT waiting_trx.trx_id AS waiting_trx_id, waiting_trx.trx_mysql_thread_id AS waiting_thread, waiting_trx.trx_query AS waiting_query, TIMESTAMPDIFF(SECOND, waiting_trx.trx_started, NOW()) AS waiting_sec, blocking_trx.trx_id AS blocking_trx_id, blocking_trx.trx_mysql_thread_id AS blocking_thread, blocking_trx.trx_query AS blocking_query, TIMESTAMPDIFF(SECOND, blocking_trx.trx_started, NOW()) AS blocking_sec FROM performance_schema.data_lock_waits w JOIN information_schema.INNODB_TRX waiting_trx ON w.REQUESTING_ENGINE_TRANSACTION_ID waiting_trx.trx_id JOIN information_schema.INNODB_TRX blocking_trx ON w.BLOCKING_ENGINE_TRANSACTION_ID blocking_trx.trx_id;这个查询能直接告诉你谁在等、等了多久、谁在阻塞、阻塞了多久、各自的SQL是什么。有了这些信息就可以决定是kill掉阻塞事务还是联系应用侧修复逻辑。4.2 死锁日志的解读与预防死锁是锁等待的特例MySQL会自动检测并回滚其中一个事务。死锁信息可以通过SHOW ENGINE INNODB STATUS查看但输出格式比较原始。MySQL 8.0的Performance Schema提供了更结构化的死锁信息SELECT * FROM performance_schema.events_errors_summary_global_by_error WHERE ERROR_NAME ER_LOCK_DEADLOCK;不过更实用的还是直接看SHOW ENGINE INNODB STATUS中的LATEST DETECTED DEADLOCK部分。解读死锁日志的关键是看两个事务各自持有什么锁、等待什么锁。我总结的死锁预防原则固定访问顺序如果多个事务需要访问相同的表和行约定按相同顺序访问。比如都先更新A表再更新B表不要一个先A后B另一个先B后A。缩短事务长度事务越长持有锁的时间越久冲突概率越大。尽量把不必要的查询放到事务外。使用低隔离级别如果业务允许把隔离级别从REPEATABLE READ降到READ COMMITTED可以减少间隙锁带来的冲突。批量操作拆分大批量更新时拆成多个小批次每批提交一次减少单事务锁范围。4.3 长事务的发现与处理长事务是很多性能问题的根源。它不仅持有锁时间长还会导致undo log膨胀、purge操作延迟。发现长事务的方法SELECT trx_id, trx_state, trx_started, TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS duration_sec, trx_mysql_thread_id, trx_rows_locked, trx_rows_modified, trx_query FROM information_schema.INNODB_TRX WHERE TIMESTAMPDIFF(SECOND, trx_started, NOW()) 60 ORDER BY trx_started ASC;我一般把超过60秒的事务定义为长事务。处理方式取决于业务场景如果是报表查询导致的考虑把报表放到只读副本上执行如果是应用忘记提交事务需要修复代码逻辑如果是大批量DML考虑拆批处理。提示trx_rows_locked和trx_rows_modified在MySQL 5.7中可能不准确8.0中改善了很多。如果发现某个事务锁了大量行但修改很少可能是间隙锁导致的需要检查索引设计和隔离级别。5. 系统层诊断把数据库问题和OS资源关联起来5.1 从数据库指标到系统指标的映射数据库性能问题最终都会反映到系统资源上。我常用的系统诊断命令组合# 查看MySQL进程的CPU和内存占用 top -p $(pgrep -d, mysqld) # 查看磁盘IO情况重点关注%util和await iostat -x 1 5 # 查看网络连接状态 ss -s ss -tnp | grep mysqld | awk {print $1} | sort | uniq -c # 查看系统负载 uptime vmstat 1 5关键映射关系Threads_running高 CPU使用率高 → SQL计算密集需要优化SQL或加CPUInnodb_buffer_pool_reads高 磁盘%util高 → Buffer Pool不足或全表扫描多Threads_connected高 网络连接数多 → 连接池配置问题Innodb_row_lock_waits高 CPU不高 → 锁竞争需要优化事务逻辑5.2 磁盘IO瓶颈的确认与缓解磁盘IO是数据库最常见的瓶颈。确认方法# 查看MySQL进程的IO情况 pidstat -d -p $(pgrep mysqld) 1 5 # 查看具体是哪些文件在读写 iotop -o -P -p $(pgrep mysqld)如果确认是IO瓶颈缓解手段包括增大Buffer Poolinnodb_buffer_pool_size设置为物理内存的50%-70%让更多数据缓存在内存中。优化SQL减少扫描全表扫描是IO杀手通过索引优化把扫描行数降下来。使用SSD如果还在用机械硬盘换SSD是最直接的提升。调整IO容量innodb_io_capacity和innodb_io_capacity_max根据磁盘类型调整。SSD可以设置更高比如2000-4000。分离redo log和data文件如果条件允许把redo log放到独立磁盘减少IO争抢。5.3 连接数暴涨的排查思路连接数暴涨通常表现为Threads_connected飙升应用侧报“too many connections”。排查步骤-- 查看当前连接来源分布 SELECT SUBSTRING_INDEX(HOST, :, 1) AS client_ip, COUNT(*) AS conn_count, SUM(COMMAND Sleep) AS sleep_count, SUM(COMMAND Query) AS query_count FROM information_schema.PROCESSLIST GROUP BY client_ip ORDER BY conn_count DESC;如果某个IP的连接数特别多且大部分是Sleep状态说明应用侧连接池配置有问题——可能是最大连接数设太大或者连接泄漏没有归还。另一种情况是Query状态的连接多说明数据库处理不过来连接堆积。这时候需要看Threads_running如果running不高但connected很高可能是网络延迟或客户端处理慢。注意information_schema.PROCESSLIST在连接数很多时查询会很慢因为它需要遍历所有线程。MySQL 8.0的performance_schema.threads表性能更好但字段含义略有不同。6. 工具链组合与实战排查路径6.1 我常用的诊断工具组合经过多个项目的积累我形成了一套固定的诊断工具组合工具用途使用频率备注SHOW GLOBAL STATUS快速体检每次排查必用看增量而非累计值Performance Schema细粒度分析复杂问题必用需提前开启consumerEXPLAIN ANALYZE执行计划验证SQL优化必用8.0.18支持INNODB_TRX data_locks锁诊断卡顿问题必用组合查询效率高pt-query-digest慢日志聚合定期分析Percona Toolkit组件pt-index-usage索引使用分析索引优化需配合慢日志sys schema可读性封装日常监控基于Performance Schema其中sysschema是MySQL 5.7自带的一套视图把Performance Schema的原始数据封装成了更易读的格式。比如sys.statement_analysis视图直接给出了按总延迟排序的SQL列表比手写查询方便很多。SELECT * FROM sys.statement_analysis ORDER BY total_latency DESC LIMIT 10;6.2 一个完整的排查案例复盘某次线上反馈订单查询接口在下午高峰期响应时间从300ms涨到3s。排查过程如下第一步确认数据库侧是否有异常SHOW GLOBAL STATUS WHERE Variable_name IN ( Threads_running, Threads_connected, Innodb_row_lock_waits, Innodb_row_lock_time_avg );发现Threads_running从平时的5涨到了40Innodb_row_lock_time_avg从10ms涨到了800ms。初步判断是锁竞争。第二步定位锁等待用前面的锁等待全景图查询发现大量事务在等待orders表的同一批行。阻塞源头是一个批量更新事务已经运行了120秒。第三步分析阻塞事务查看阻塞事务的SQL发现是一个没有走索引的UPDATE orders SET statusprocessed WHERE created_at 2024-01-01。由于created_at没有索引这个UPDATE扫描了全表并锁定了大量行。第四步紧急处理与长期修复紧急处理kill掉阻塞事务业务恢复。长期修复为created_at添加索引并把批量更新拆成小批次执行。这个案例的教训是没有索引的UPDATE/DELETE比SELECT更危险因为它不仅慢还会锁定大量行引发连锁反应。6.3 日常巡检清单最后分享一份我日常巡检用的清单按优先级排列Buffer Pool命中率低于99%需要关注低于95%必须处理。Threads_running持续超过CPU核数2倍需要关注。Innodb_row_lock_time_avg超过100ms需要关注。Created_tmp_disk_tables比率超过10%需要关注。慢查询数量趋势突然上涨需要排查。连接数使用率超过max_connections的80%需要关注。磁盘空间低于20%需要清理或扩容。主从延迟超过10秒需要排查。这份清单我一般做成定时脚本每天早中晚各跑一次输出到监控平台。大部分问题在爆发前都能通过趋势变化提前发现。提示巡检脚本不要只记录当前值要记录历史趋势。很多问题不是突然出现的而是慢慢恶化的。有了趋势数据就能在问题还小的时候介入处理。7. 几个容易踩的坑和反直觉经验7.1 不要迷信“调大参数就能解决问题”我见过太多人一遇到性能问题就调大innodb_buffer_pool_size、max_connections、tmp_table_size。调参数确实能缓解症状但如果不找到根因只是把问题推迟了。比如连接数不够调大max_connections只是让更多连接堆积最终还是会崩。正确的做法是先看Threads_running如果running不高说明连接都在等待调大连接数上限没用。7.2 慢查询日志的long_query_time不要设太小有人为了“抓所有问题SQL”把long_query_time设成0.1秒甚至0.01秒。结果日志文件暴涨磁盘IO被日志写入吃掉反而加剧了性能问题。我的建议是平时设1秒排查问题时临时调到0.1秒排查完调回去。另外一定要开启log_queries_not_using_indexes但要注意这个选项在数据量大的实例上会产生大量日志建议配合min_examined_row_limit使用。7.3 EXPLAIN的rows是估算值不是实际值很多人看EXPLAIN时把rows当成实际扫描行数这是不对的。rows是优化器基于统计信息的估算值可能偏差很大。要获取实际行数需要用EXPLAIN ANALYZE或者开启optimizer_trace。我一般先用EXPLAIN快速筛选可疑SQL再用EXPLAIN ANALYZE确认。7.4 索引不是越多越好每个索引都会增加写入开销和存储空间。我见过一张表建了15个索引写入性能极差。索引优化不仅是“加索引”还包括“删无用索引”。用sys.schema_unused_indexes可以找出从未被使用的索引SELECT * FROM sys.schema_unused_indexes WHERE object_schema NOT IN (mysql, sys, performance_schema);7.5 主从延迟不一定是数据库的问题主从延迟的排查思路和单实例性能问题不同。先看SHOW SLAVE STATUS中的Seconds_Behind_Master如果这个值在波动说明从库在追赶。然后看Relay_Log_Space和Exec_Master_Log_Pos的变化速度。如果从库SQL线程执行慢可能是从库上有大查询在跑或者从库硬件配置比主库差。我遇到过一次主从延迟最后发现是从库的innodb_flush_log_at_trx_commit设成了1而主库设的是2从库写入慢导致延迟。这些经验都是我在实际项目中踩坑后总结的不一定适用于所有场景但至少能帮你少走一些弯路。数据库性能诊断没有银弹关键是建立一套系统的排查思路然后根据具体问题灵活组合工具。工具是死的思路是活的。