数据库一旦慢下来业务侧最先感受到的就是接口超时、页面转圈、报表出不来。很多人第一反应是“加索引”“换硬件”“上缓存”但真正动手做MySQL性能分析时才发现连从哪儿下手都不知道。我这些年处理过的线上故障绝大多数根因并不复杂复杂的是在几十个干扰项里快速定位到那一个关键点。这篇内容不绕弯子直接把我日常做MySQL性能分析的方法、工具、指标和踩坑记录整理出来希望能给正在被慢查询折磨的同行一些参考。1. 性能分析到底在分析什么1.1 性能问题的本质是资源与需求的矛盾MySQL性能分析说白了就是回答三个问题慢在哪里、为什么慢、怎么让它不慢。这三个问题听起来简单但每一个背后都牵扯到一整套观察维度。很多人一遇到数据库变慢就急着看慢查询日志这没错但属于“只盯着结果不看过程”。真正专业的分析路径应该是先确认瓶颈类型再锁定具体对象最后才轮到优化动作。瓶颈类型无非这么几类——CPU计算密集、内存不够用导致频繁刷盘、磁盘IO吞吐到顶、锁等待严重、网络传输耗时。不同类型的瓶颈处理思路完全不一样用错方向反而会越优化越糟。我见过最典型的反面案例某系统查询慢DBA一口气加了三个索引结果写入性能暴跌因为每个索引都要额外维护B树。这就是典型的没搞清楚“慢”到底是因为全表扫描还是因为索引失效就盲目动手。所以性能分析的第一步永远是观察而不是改。1.2 建立可量化的性能基线做性能分析之前建议先给数据库建立一套基线数据。没有基线你无法判断当前的状态算不算异常。基线的核心指标包括QPS每秒查询数、TPS每秒事务数、并发连接数、InnoDB缓冲池命中率、临时表创建频率、慢查询数量趋势、主从延迟时间。这些数据不需要多精确但要能反映业务高峰和低峰的差异。我习惯用mysqld_exporter配合Prometheus持续采集保留至少两周的数据这样出了性能问题可以直接拿当前数据和历史同期对比很快能判断是突发问题还是逐步恶化。如果没有监控系统也可以用一条SQL快速看当前状态SHOW GLOBAL STATUS LIKE Questions; SHOW GLOBAL STATUS LIKE Uptime;用Questions除以Uptime就是自启动以来的平均QPS。虽然粗糙但应急的时候够用。1.3 分析前必须搞清楚的三个前提做性能分析前先确认三件事否则分析半天可能白干。第一问题是不是真的在数据库。我接过不少工单排查到最后发现是应用层代码死循环狂发请求或者是Redis缓存穿透把压力全打到了MySQL。最简单的验证方式在业务低峰期手动执行同样的SQL如果响应时间正常那问题多半不在数据库本身。第二问题是偶发还是持续。偶发的性能抖动重点排查锁竞争、连接风暴、临时大表持续的性能低下重点排查SQL写法、索引设计、硬件瓶颈。这两个方向的分析路径几乎是反的。第三影响范围有多大。是单条SQL慢还是整个实例所有操作都慢是单库慢还是所有分片都慢范围决定了排查的切入点。全部慢优先看硬件和配置单条慢优先看执行计划这些经验判断能帮你少走很多弯路。2. 慢查询日志性能分析的起点2.1 如何正确开启和配置慢查询日志慢查询日志是MySQL性能分析最基础、最有效的抓手。它记录的是执行时间超过阈值的SQL语句通过分析这些SQL你能直接定位到最需要优化的目标。确认慢查询日志是否开启SHOW VARIABLES LIKE slow_query_log; SHOW VARIABLES LIKE long_query_time;线上环境我一般建议这样配置slow_query_log ON slow_query_log_file /var/log/mysql/slow-query.log long_query_time 1 log_queries_not_using_indexes ON min_examined_row_limit 100long_query_time 1表示超过1秒的SQL就算慢查询。不要设成0否则日志量会爆炸。log_queries_not_using_indexes会记录所有没走索引的SQL这个开关很有价值但也要配合min_examined_row_limit一起用否则一些扫描行数极少的查询也会被记录干扰判断。有一点要特别注意从MySQL 8.0开始慢查询日志默认是关闭的需要你手动开启。配置修改后要重启MySQL服务才能生效如果是生产环境可以用SET GLOBAL在线调整但重启后会失效最终还是需要写进配置文件。2.2 分析慢日志的实用技巧拿到慢查询日志后不要一条一条去看那样效率太低。用mysqldumpslow工具做汇总mysqldumpslow -s at -t 10 /var/log/mysql/slow-query.log-s at表示按平均查询时间排序-t 10表示只显示前10条。这样能快速找出最值得优化的TOP SQL。更精细的分析我推荐用pt-query-digest它是Percona Toolkit套件里的明星工具pt-query-digest /var/log/mysql/slow-query.log这个工具会把SQL按照指纹聚合计算出每条SQL的执行次数、总耗时、平均耗时、最大耗时、响应时间占比等还会自动识别出哪些SQL是“最值得优化的”。输出结果里重点看Response time占比最高的那几条这几条SQL就是性能瓶颈的核心。2.3 慢查询分析的经典误区这里提醒几个我踩过的坑。第一个坑只看执行时间不看执行次数。一条SQL执行时间2秒但一天只跑一次另一条SQL执行时间0.2秒但每秒跑100次。从总耗时来看后者的影响远大于前者。所以分析慢日志一定要结合执行次数看总量。第二个坑忽略了锁等待时间。慢查询日志记录的是“从开始执行到返回结果”的总时长这个时间包含了锁等待时间。有时候SQL本身只需要0.1秒但等锁等了1.9秒。如果你只盯着SQL本身的执行计划优化永远解决不了问题。遇到这种情况要结合SHOW ENGINE INNODB STATUS查看锁等待的具体原因。第三个坑生产环境的慢查询日志长期不清理。日志文件越来越大磁盘空间被占满导致数据库写入失败这个故障我见过不止一次。建议做日志切割用logrotate或者crontab定期处理。3. EXPLAIN执行计划读懂SQL的“体检报告”3.1 EXPLAIN关键字段逐项拆解当慢查询日志帮你锁定了问题SQL下一步就是用EXPLAIN查看它的执行计划搞清楚MySQL到底是怎么执行这条SQL的。EXPLAIN SELECT * FROM orders o LEFT JOIN users u ON o.user_id u.id WHERE o.status 1 ORDER BY o.create_time DESC LIMIT 20;执行结果会返回一长串字段每个字段都有含义但日常分析重点关注这几个type字段这是最重要的字段之一表示访问类型。性能从好到差依次是systemconsteq_refrefrangeindexALL。看到ALL就说明是全表扫描这是最危险的情况数据量大时性能必然拉胯。看到index也不要掉以轻心它虽然遍历了整棵索引树但本质上还是全索引扫描一样可能慢。key字段实际使用的索引名。如果为NULL说明这条SQL没用到任何索引需要检查为什么没走索引。rows字段MySQL预估需要扫描的行数。这个值越大查询越慢。优化目标就是尽量让这个值变小比如从几百万降到几千。Extra字段包含了很多额外信息需要特别注意的几种Using filesort在内存或磁盘上做了排序数据量大时很慢通常需要优化ORDER BY和索引的配合。Using temporary用了临时表常见于GROUP BY、DISTINCT等操作同样不健康。Using index覆盖索引不需要回表这是最理想的状态。Using where在存储引擎层返回记录后进行了过滤。3.2 用实际案例演示EXPLAIN分析思路拿一个真实场景举例。订单表orders有三百万数据业务侧反映按用户查订单特别慢。EXPLAIN SELECT * FROM orders WHERE user_id 12345 AND status 1 ORDER BY create_time DESC;执行计划显示type为ALLrows为300万Extra为Using where; Using filesort。这明显是一个全表扫描加文件排序的糟糕计划。优化思路分两步先给user_id建索引缩小扫描范围然后考虑要不要把status也加进联合索引最后解决排序问题。如果改成联合索引(user_id, status, create_time)执行计划就变成了typerefrows几十条Extra里Using filesort消失了因为索引本身已经按照create_time排好序了。这就是一次典型的索引设计优化。3.3 用EXPLAIN ANALYZE验证真实执行成本MySQL 8.0.18之后的版本提供了EXPLAIN ANALYZE它能真正执行SQL并返回每个步骤的实际耗时和扫描行数比传统EXPLAIN的估算值可靠得多。EXPLAIN ANALYZE SELECT * FROM orders WHERE status 1;结果里会显示类似这样的信息- Filter: (orders.status 1) (actual time0.234..125.456 rows150000 loops1) - Table scan on orders (actual time0.123..98.567 rows3000000 loops1)看到actual time的差距你就能直观感受到SQL的真实开销在哪里。这个工具特别适合用来验证“优化是否真的有效”改完索引后跑一次EXPLAIN ANALYZE对比优化前后的实际执行时间和扫描行数一目了然。4. 性能分析核心指标体系4.1 连接数最先崩溃的往往是它数据库连接数被打满是所有性能故障里最常见的一种也是最先让人感知到的——应用直接报“Too many connections”。查看当前连接情况SHOW STATUS LIKE Threads_connected; SHOW STATUS LIKE Max_used_connections; SHOW VARIABLES LIKE max_connections;Threads_connected是当前连接数Max_used_connections是历史最大连接数。如果后者经常接近max_connections的上限值说明连接资源已经非常紧张了。处理连接数打满的问题单纯调大max_connections不是好办法因为每个连接都要占用内存和线程资源调太大反而会让MySQL整体变慢。正确思路是排查应用侧是否存在连接泄漏连接池的最大连接数是否设置过高。看是否有大量Sleep状态的连接长期占用不释放。对短连接风暴做限流或者引入Proxy中间层。MySQL连接数就像一个餐厅的座位数客人来了得有位置坐但座位太多也要有足够的服务员线程和厨房CPU/IO来接单否则一样出餐慢。4.2 InnoDB缓冲池命中率内存够不够用InnoDB缓冲池Buffer Pool是MySQL最重要的内存区域用来缓存数据页和索引页。命中率高意味着大部分读操作直接在内存完成不用走磁盘速度自然快。计算命中率SHOW GLOBAL STATUS LIKE Innodb_buffer_pool_read_requests; SHOW GLOBAL STATUS LIKE Innodb_buffer_pool_reads;命中率 Innodb_buffer_pool_read_requests/ (Innodb_buffer_pool_read_requestsInnodb_buffer_pool_reads)。理论上缓冲池命中率应该在99%以上如果低于95%优先检查innodb_buffer_pool_size是否设置得太小。这个参数的推荐值是物理内存的60%~70%但不要贪心要给操作系统和其他进程留够空间。还有一个容易被忽略的细节innodb_buffer_pool_instances参数。在MySQL 5.7及以上版本如果Buffer Pool设置得很大比如超过8G建议把innodb_buffer_pool_instances设置为8或者16让多个缓冲池实例分担并发访问压力减少内部锁竞争。4.3 临时表和排序隐性的性能杀手平时做性能分析很多人容易忽略临时表和排序相关的状态变量。我习惯关注下面这几个指标SHOW GLOBAL STATUS LIKE Created_tmp_tables; SHOW GLOBAL STATUS LIKE Created_tmp_disk_tables; SHOW GLOBAL STATUS LIKE Sort_merge_passes;Created_tmp_disk_tables与Created_tmp_tables的比值如果长期偏高说明有大量临时表被创建到了磁盘上通常是因为GROUP BY、DISTINCT、UNION这类操作涉及的数据量太大超过了tmp_table_size和max_heap_table_size限制。Sort_merge_passes表示排序过程中不得不使用磁盘临时文件的次数数值越大说明排序越吃力通常和sort_buffer_size设置不合理或者没有合理索引有关。这些指标是“隐形的性能杀手”因为单条SQL执行时你不会立刻感知到问题但累积起来会拖垮整个数据库的IO。4.4 主从延迟读写分离架构下的定时炸弹只要用了主从复制就必须监控主从延迟。延迟过高时从库读到的就是过期的旧数据业务上可能出现刚提交的数据查不到的情况。查看从库状态SHOW SLAVE STATUS\G;重点看Seconds_Behind_Master这个字段正常情况下应该接近0如果持续增长或者在几百以上就要排查原因了。主从延迟的常见原因很多从库硬件性能不如主库大事务在主库执行完同步到从库需要更久从库上有分析报表类的重查询抢占了资源单线程复制跟不上主库的并发写入速率等等。解决思路分别对应升级从库硬件、拆分大事务、开启并行复制MTS。5. 系统层性能分析从数据库背后看问题5.1 用SHOW ENGINE INNODB STATUS看内部状态当数据库层面各项指标看着都正常但就是慢这时候要往InnoDB引擎内部深挖。SHOW ENGINE INNODB STATUS;这条命令会输出一长串InnoDB的运行状态信息重点是LATEST DETECTED DEADLOCK最近一次死锁详情和TRANSACTIONS当前事务列表部分。通过事务列表你能看到当前正在运行的事务、每笔事务持有哪些锁、正在等待哪把锁。这在处理锁等待和死锁问题时是关键的诊断依据。举个例子某个更新操作的SQL迟迟不返回用这个命令查看后发现是有另一个事务一直持有该行数据的排他锁没有提交导致当前事务一直在等锁。找到源头事务后就让业务侧确认是否可以中止那个事务问题迎刃而解。5.2 从iostat和top中读出数据库的IO和CPU压力很多DBA处理MySQL性能问题时只看数据库内部指标忽略了系统层的信息这是不完整的。MySQL运行在操作系统之上操作系统的资源状况直接决定了MySQL的上限。我用top命令确认CPU是否有大量sys占用用iostat -x 1看磁盘的%util、await、svctm等IO指标。%util接近100%说明磁盘已经处于饱和状态这时候再怎么优化SQL瓶颈都还在磁盘。另外别忘了看swap使用情况。如果操作系统发生了swap交换MySQL性能会断崖式下跌因为InnoDB缓冲池里的数据页被换到磁盘上了。检查free -h里swap的used值如果不是0要认真排查内存压力来源了。5.3 Performance Schema和sys库现代MySQL的探照灯从MySQL 5.7开始sys schema库是一个非常实用的性能分析入口。它把Performance Schema采集的原始数据整理成了视图让开发者用普通SQL就能看出系统性能问题。查看最耗时的SQLSELECT * FROM sys.statement_analysis ORDER BY total_latency DESC LIMIT 10;查看当前正在执行的SQLSELECT * FROM sys.session WHERE command ! Sleep;查看哪些索引没用过、哪些索引冗余SELECT * FROM sys.schema_unused_indexes;sys库可以说是性能分析的情报中心信息量大且直观。如果你还没用过建议抽时间在测试环境把这些视图一个个过一遍能极大提升诊断效率。Performance Schema本身会占用少量额外性能开销但在MySQL 5.7版本中这个开销已经控制得比较好了日常开启影响不大。6. 常见性能瓶颈与排查实录6.1 隐式类型转换导致的索引失效这是我处理过最多的一类问题。举例user表的id字段是varchar类型但应用传入了数字类型的参数。EXPLAIN SELECT * FROM users WHERE id 12345;虽然id字段上有主键索引但由于发生了隐式类型转换MySQL无法使用索引完成等值匹配只能全表扫描。优化方式很简单应用侧传参时保证类型一致或者把字段类型改成和业务实际匹配的类型。这个问题的隐蔽性在于小数据量时全表扫描也就几十毫秒根本不会引起注意一旦数据量涨到千万级SQL直接变慢上百倍。6.2 深度分页的噩梦LIMIT 1000000, 20后台管理系统的列表页经常出现“翻到第几页就特别慢”的情况。核心原因是LIMIT offset, size的offset过大时MySQL会扫描并丢弃前面所有符合条件的数据行越往后翻页越慢。SELECT * FROM orders ORDER BY id LIMIT 1000000, 20;这条SQL会扫描100万行数据然后丢弃前999980行只返回最后20行。毫无效率可言。优化手段有几种使用“延迟关联”先只查出主键再用主键关联回原表取完整行。用“书签”方式替代offset记住上一页最后一条的id下一页直接WHERE id 上一页最大id ORDER BY id LIMIT 20。如果场景允许限制最大翻页深度。6.3 连接风暴导致数据库整体卡顿有一个线上事故印象很深某个活动上线瞬间流量激增应用实例扩容后每个实例都创建了大量的数据库连接连接数瞬间被打满后续所有请求都在排队等待获取连接最终表现为整个系统瘫痪。事后复盘问题出在两层应用侧连接池的最小连接数设置过大、初始化策略是饥饿加载数据库侧max_connections设置得又偏低。解决方案是双管齐下应用侧控制连接池上限、调整预热策略数据库侧适当提高max_connections并增加skip-name-resolve减少DNS解析开销。还有一个容易忽略的点max_connections调大之后要同步调大操作系统的最大文件句柄数ulimit -n否则连接数到一定程度MySQL会发生文件句柄不足的报错。6.4 锁等待 vs 死锁两种完全不同的性能场景锁等待和死锁经常被混为一谈但处理思路完全不同。锁等待是指一个事务在等另一把锁等锁时间超过innodb_lock_wait_timeout默认50秒后MySQL会主动报错并回滚当前SQL。这类问题大多是持锁事务迟迟不提交导致的解决思路是优化事务逻辑缩短事务时间让锁尽快释放。死锁则是指两个事务互相持有对方需要的锁双方都在等待对方释放形成循环。InnoDB检测到死锁后会自动回滚其中一个事务让另一个继续执行。如果你在监控里看到死锁报错不应该只是重试而是要去分析死锁触发的事务逻辑调整加锁顺序。查看死锁信息依然是用SHOW ENGINE INNODB STATUS在LATEST DETECTED DEADLOCK段落里能看到两个事务各自持有哪些锁、等待哪些锁、涉及哪些SQL。根据这些信息就能还原出死锁链路的完整面貌。6.5 常见问题速查表故障现象优先检查项常用处理方式CPU使用率100%慢查询日志、processlist定位高CPU消耗SQL优化执行计划磁盘IO%util持续100%iostat、InnoDB日志刷盘策略优化SQL减少IO次数调整innodb_flush_log_at_trx_commit连接数打满Threads_connected检查连接池配置排查Sleep连接限流查询突然变慢EXPLAIN执行计划检查索引是否失效、是否发生隐式转换更新操作长时间不返回锁等待情况找到持锁事务优化事务执行时长主从延迟严重且有大量写操作大事务、从库负载拆分大事务开启并行复制内存持续增长且内存交换Buffer Pool设置调大innodb_buffer_pool_size排查其他内存占用7. 性能分析工具链与实操建议7.1 常用的第三方性能分析工具日常分析工作中除了MySQL自带的工具我还常用两套第三方工具。Percona Toolkit是我首选的工具集其中pt-query-digest用于分析慢查询日志pt-index-usage分析索引使用情况pt-online-schema-change在在线变更表结构时避免锁表pt-kill用于批量终止异常会话。这些都是生产环境验证过的“老兵”久经考验。另一个是mysqld_exporter配合Prometheus和Grafana的组合。这套方案能让我随时看到数据库的历史趋势图而不是只能看到问题发生那一刻的瞬间状态。比如当业务方说“系统今天下午三点开始变慢”我可以直接看下午三点前后的各项监控曲线快速缩小时段和方向。7.2 一套可复用的性能分析流程把以上所有内容串起来我现在拿到一个“MySQL性能问题”的工单时执行流程是固定的先看监控仪表盘确认瓶颈范围CPU、IO、连接数、锁等待、主从延迟。根据瓶颈方向查看慢查询日志找出耗时最高的SQL。用EXPLAIN/EXPLAIN ANALYZE分析执行计划确认SQL具体慢在哪一步。用SHOW ENGINE INNODB STATUS检查是否有锁竞争或者死锁。结合系统层的top、iostat、free信息确认硬件资源是否存在瓶颈。制定优化方案优先做成本和风险最低的调整比如加索引、改SQL写法。上线后持续观察指标变化确认优化真实有效避免优化了个寂寞。这套流程看起来简单但每一步都踩过数不清的坑才沉淀下来的。关键是不要去跳步比如直接跳过监控数据去改配置往往是治标不治本过几天问题换个方式又回来了。7.3 优化动作的先后顺序与风险评估最后说一个比较重要的经验优化动作一定要分级不要一股脑全上。最安全的优化是第一优先级——SQL改写和索引优化。这属于物理层面的小手术不动原有系统架构出现问题可以快速回退。其次是配置参数的调整比如innodb_buffer_pool_size、innodb_flush_log_at_trx_commit需要做A/B对比测试后再上线。风险最高的是架构层面的调整比如分库分表、引入缓存中间件、升级实例规格这种一定要经过完整的评审和压测。我个人见过太多“调了一个参数后引发连锁反应”的案例。比如为了加快写入速度把innodb_flush_log_at_trx_commit从1改成0确实写入快了很多但一旦数据库崩溃最多可能丢失最近1秒的事务数据。对于金融、订单这类业务这是完全不可接受的。所以每个优化动作都要想清楚它在解决什么它可能会牺牲什么我能不能接受这个代价。写在最后的一点体会做MySQL性能分析这几年最大的感受是大多数性能问题不是“技术难度”问题而是“观察维度”问题。你观察得越全面盲区就越少定位就越准。不要迷信任何一个单一指标慢查询日志会骗人、EXPLAIN也会骗人只有多个维度相互印证时你才会接近那个真实的瓶颈点。还有个小建议日常在做业务开发时就养成看执行计划的习惯而不是等到线上告警才去看。每写一条涉及多表关联或者大数据量的SQL跑一次EXPLAIN几秒钟的事但能帮你提前规避掉90%的线上性能隐患。这个习惯比任何工具都值钱。如果你们团队目前连最基本的慢查询日志和监控都还没搭建起来那今天就可以做这件事先跑起来再慢慢完善。性能分析不是一门玄学它是一套方法论加一个习惯坚持做下去你会发现数据库的“脾气”其实很好摸透。