凌晨两点半被电话从被窝里薅起来的滋味做过数据库运维的朋友应该都不陌生。一接起来业务方那边火急火燎页面打不开、订单提交超时、接口全部卡死。登录服务器一看MySQL CPU 直接飙到 800%终端敲命令都带着延迟。这种场景我经历过太多次每次复盘都会想如果一开始就能用对方法定位瓶颈至少能把凌晨的救命电话减少一半。MySQL 性能瓶颈排查这件事最怕的就是“拍脑袋”。一看到 CPU 高就加内存一看到慢查询就加索引运气好的时候能蒙对运气不好反而把问题搞得更复杂。真正的靠谱路线应该是“先诊断、再定位、后根治”用数据说话拿证据开刀。这篇文章就把我这些年排查性能问题的完整套路整理出来从现象收集、慢查询分析、执行计划解读到索引优化、锁等待剖析、参数调优一条线走到底。不管是刚接触 MySQL 的开发者还是已经被线上问题毒打过的 DBA这篇都能给你一套可以直接复用的作战手册。1. 诊断的整体思路先分清“病在哪”再谈“怎么治”1.1 遇到性能问题先别急着调参数很多人一碰到数据库变慢第一反应是“把某个参数调大”。这个习惯非常危险因为 MySQL 性能瓶颈可能出现在硬件层、系统层、数据库层、SQL 层甚至业务架构层。不同层面的问题表现出的症状很像但药方完全不同。我把性能问题按发生概率排了个序大家可以照着这个顺序去查SQL 写法问题占了大概 60% 以上的情况比如缺索引、深分页、隐式类型转换、SELECT * 带回大量无用字段。索引失效占了 20% 左右比如在索引列上做函数运算、模糊查询前置通配符、联合索引不满足最左前缀。锁竞争与事务问题占了 10% 左右比如长事务持锁不释放、间隙锁导致并发插入被阻塞。数据库配置不合理占了 5% 左右比如 buffer pool 太小、redo log 刷盘策略太保守。硬件或系统瓶颈占了不到 5%比如磁盘 IOPS 跑满、swap 交换导致抖动。这个排序不是我拍脑袋统计的而是基于我多年排查的线上案例总结出来的经验规律。绝大多数性能问题根源都在 SQL 和索引这一层恰恰是这两层最容易被忽略。很多人一开始就去调 buffer pool、改刷盘策略方向完全错了。1.2 建立诊断基线先拿数据再动手我自己的习惯是接到任何性能问题先花 10 分钟把现场数据完整采集下来。不上服务器乱敲命令而是按固定流程抓取下面这几类信息当前系统负载top、sar -q看 CPU 核数是否跑满、负载是否超出正常水位。MySQL 全局状态SHOW GLOBAL STATUS重点看Threads_connected、Threads_running、QPS。当前活跃会话SHOW PROCESSLIST看哪些 SQL 卡着不动。InnoDB 引擎状态SHOW ENGINE INNODB STATUS看死锁、锁等待和事务状态。慢查询日志这是最重要的一手证据直接看压垮系统的 SQL 长什么样。采集完这些数据再开始判断方向。举个例子如果Threads_connected只有 20但Threads_running是 15说明有大量 SQL 同时在执行且互相争抢资源优先排查 SQL 效率和锁等待。如果Threads_connected已经 500 多接近最大连接数那首先要处理的是连接风暴问题而不是 SQL 效率。这种“先取证、后下结论”的思路能帮你避免被表面的 CPU 高占用带偏。CPU 高只是一个结果真正的原因可能是 SQL 低效、可能是锁等待造成了大量重试、也可能是连接数失控导致上下文切换频繁。只有拿到一手现场数据才能判断问题到底出在哪一层。2. 慢查询日志与性能指标把瓶颈从“感觉”变成“数字”2.1 慢查询日志的正确配置与使用慢查询日志是排查性能问题最基础也最重要的工具。它记录了执行时间超过阈值的 SQL 语句基本上就是性能问题的“嫌疑人名单”。但在实际工作中我发现很多人的慢查询日志配置不合理要么根本没开启要么long_query_time设得太大导致关键 SQL 没有进日志。我推荐一组稳妥的配置slow_query_log 1 slow_query_log_file /var/log/mysql/mysql-slow.log long_query_time 1 log_queries_not_using_indexes 1 log_slow_admin_statements 1其中long_query_time我一般会设成 1 秒也就是超过 1 秒的查询全部记录下来。初期排查阶段宁可日志多些也不要漏掉线索。等系统稳定之后再根据业务情况调整为 2 秒或 3 秒减少日志量。另外log_queries_not_using_indexes这个参数建议开着它能把没有走索引的查询也记录下来哪怕执行时间很短。很多隐性问题比如扫全表但是数据量暂时不大不会触发慢查询阈值但日后数据涨上来就会爆炸。通过这个参数提前发现潜在风险比事后救火舒服多了。慢查询日志默认是文本文件格式直接less看也行但内容多了之后效率太低。我习惯用mysqldumpslow工具做聚合分析mysqldumpslow -s at -t 10 /var/log/mysql/mysql-slow.log这个命令按平均执行时间排序显示最慢的 10 条 SQL适合从一堆日志里快速找出“首恶”。如果服务器上不方便装工具也可以用pt-query-digest分析它是 Percona Toolkit 里的神器能按指纹聚合 SQL统计出每条 SQL 的平均耗时、总耗时、扫描行数等一眼就能看出哪些 SQL 是真正的瓶颈。2.2 关键性能指标怎么看拿到慢查询日志只是第一步还需要结合整体性能指标判断问题的严重程度和影响范围。我日常关注的核心指标主要有这几个指标含义预警线Threads_connected当前连接数超过 max_connections 的 80%Threads_running正在执行的线程数持续大于 CPU 核心数QPS每秒查询数与业务峰值对比异常升高TPS每秒事务数与业务峰值对比异常降低Buffer pool 命中率InnoDB 缓存命中率低于 95% 需要关注临时表创建量Created_tmp_disk_tables增长明显说明 SQL 有问题锁等待次数Innodb_row_lock_waits持续增长说明有锁竞争这里特别说一下Threads_running它比Threads_connected更能反映系统真实负载。连接数高不代表有问题很多连接是空闲的。但Threads_running高说明真的有一堆 SQL 在同时执行CPU 和磁盘资源正在被消耗。如果这个值持续超过 CPU 核心数系统基本已经处于过载状态。我见过一个案例某系统的Threads_connected长期稳定在 100 左右看起来很正常但Threads_running动不动就飙到 40 多而服务器只有 16 核。一查慢日志发现某个报表查询每次要扫描 3000 万行数据执行时间 20 多秒。这个 SQL 一跑立刻占满所有 CPU其他会话全部排队。后来加了覆盖索引扫描行数降到几十万行Threads_running直接掉到个位数。2.3 用 sysbench 做一次保守的压力测试排查性能问题的时候经常需要回答一个问题当前这个硬件和配置理论上能撑住多少 QPS这个基准值很有价值能帮你判断业务请求量和系统容量之间是否匹配。我自己常用 sysbench 做基准测试。先准备一张测试表灌入一定量的数据然后模拟读写混合场景。命令大概长这样sysbench /usr/share/sysbench/oltp_read_write.lua \ --mysql-host127.0.0.1 \ --mysql-port3306 \ --mysql-userroot \ --mysql-passwordyourpass \ --tables4 \ --table-size1000000 \ --threads16 \ --time60 \ prepare sysbench /usr/share/sysbench/oltp_read_write.lua \ --mysql-host127.0.0.1 \ --mysql-port3306 \ --mysql-userroot \ --mysql-passwordyourpass \ --tables4 \ --table-size1000000 \ --threads16 \ --time60 \ run通过压测结果能看到当前机器的极限 TPS/QPS、延迟分布、95% 延迟等数据。这套基准值在后续做参数调优时非常有用——你改了某个配置再跑一遍同样的测试就能量化对比前后性能差异比凭感觉判断靠谱得多。需要注意基准测试最好在业务低峰期进行操作压测会占用大量硬件资源生产环境必须要慎重。我自己的做法是先在测试环境摸清基线生产环境只在半夜真正需要容量评估时才跑。3. SQL 分析与索引优化搞定八成以上的慢查询3.1 EXPLAIN 执行计划读懂 MySQL 的“内心戏”慢查询日志只是列出嫌疑人EXPLAIN 就是审问工具。每条有问题的 SQL我都会粘贴出来跑一遍EXPLAIN看 MySQL 实际是怎么执行的。EXPLAIN SELECT * FROM orders WHERE user_id 12345 AND status PAID ORDER BY created_at DESC LIMIT 10;执行计划里我最关注这几列type访问类型从好到差依次是 system const eq_ref ref range index ALL。看到 ALL 就要警惕那是全表扫描。key实际使用的索引如果为 NULL说明没有用索引。rows预估扫描行数这个数字越小越好。如果预估扫描几百万行却只返回 10 条说明索引选择有问题。Extra这里经常出现 “Using filesort” 或 “Using temporary”意味着 MySQL 需要额外排序或建临时表是性能杀手。看type列的时候我把ALL和index视为红色警报。index虽然字面意思是用了索引但实际上是遍历了整棵索引树和全表扫描的代价差不多只是比ALL稍微好一点。如果看到Using filesort且排序字段没有走索引在数据量大的时候会非常拖慢查询这个信号也要重视。3.2 索引失效的几大常见坑很多人的 SQL 明明建了索引查询还是慢就是因为踩了索引失效的坑。我整理了高频出现的几种情况在索引列上做函数运算或表达式计算WHERE YEAR(created_at) 2024会导致索引失效应该改成WHERE created_at 2024-01-01 AND created_at 2025-01-01。隐式类型转换WHERE phone 13800000000如果 phone 字段是 varchar 类型MySQL 会把字段隐式转成数字导致索引失效。这种 bug 最难查因为不加 EXPLAIN 根本看不出来。模糊查询前置通配符WHERE name LIKE %张无法使用索引但WHERE name LIKE 张%可以范围扫描。联合索引不满足最左前缀原则。联合索引 (a, b, c)查询条件只用到 b 或 c索引直接失效。还有一个特别隐蔽的坑对字段做“隐式字符集转换”。比如关联两个表的字符串字段一个表的字符集是 utf8mb4另一个是 utf8mb4_general_ci且排序规则不一致MySQL 会在关联时做隐式转换导致关联条件上的索引失效。这个问题在多数据库迁移合并的时候特别容易踩到排查起来非常头疼。我的经验是凡是外键关联字段或高频查询条件字段尽量统一字符集和排序规则。3.3 深分页问题与覆盖索引优化分页查询是业务开发里最普遍的需求也是慢查询的重灾区。尤其是深分页比如LIMIT 1000000, 20前一百万条数据全都被扫描一遍再丢弃索引再强也扛不住。我之前处理过一个案例一个后台列表页数据量大约 500 万行每次翻到第 50 页以后接口响应时间就从 200ms 暴涨到 5 秒。EXPLAIN 一看type 是 rangekey 是主键扫描行数 100 多万。问题就出在LIMIT的偏移量太大MySQL 必须先扫描足够多的行才能跳过前面的记录。这里给出两种标准优化方案方案一是延迟关联deferred join。先通过覆盖索引查出目标行的主键 ID再用主键回表取完整数据SELECT o.* FROM orders o INNER JOIN ( SELECT id FROM orders WHERE status PAID ORDER BY created_at DESC LIMIT 1000000, 20 ) t ON o.id t.id;核心思路是让内层查询只在索引上操作索引体积远小于全表扫描成本低得多。再加一个覆盖索引 (status, created_at, id) 支撑内层排序和筛选性能提升非常明显。这个案例优化后第 50 页的响应时间从 5 秒降到 200ms 出头。方案二是基于游标的分页也就是记住上一页最后一条记录的排序值SELECT * FROM orders WHERE status PAID AND (created_at, id) (2024-06-01 12:00:00, 100001) ORDER BY created_at DESC, id DESC LIMIT 20;这种方案适合移动端下拉加载天然支持深翻页性能非常稳定但前提是排序字段必须有唯一性约束所以我会在 order by 里加 id 作为第二排序键防止数据重复或漏掉。3.4 一个完整的优化案例复盘拿一个实际优化过的业务查询举例。业务需求是“查最近一个月内购买超过三次的用户”最初的 SQL 长这样SELECT user_id, COUNT(*) FROM orders WHERE created_at DATE_SUB(NOW(), INTERVAL 1 MONTH) GROUP BY user_id HAVING COUNT(*) 3;orders 表当时有 800 万行数据这个 SQL 执行一次要 12 秒严重拖垮数据库性能。EXPLAIN 显示 type 是 ALL全表扫描并且在 Using temporary 和 Using filesort。优化思路分两步第一步为created_at建索引让 WHERE 条件能走范围扫描减少参与 GROUP BY 的数据量。第二步反范式化处理在用户表加一个order_count_month字段每次完成订单时在事务里更新这个计数再用定时任务或应用侧逻辑做重置。这样报表查询直接查用户表不用再扫描订单表。优化之后查询响应时间从 12 秒降到 50ms 以内。这个案例说明一个道理SQL 写法上的优化能做到“省力”但如果业务场景频繁需要大数据量聚合统计就要考虑通过表结构设计或数据冗余来“省功”。两类手段配合使用才能真正根治问题。4. 锁与事务看不见摸不着的性能杀手4.1 锁等待的排查方法锁等待是 MySQL 性能问题里最隐蔽的一类。有时数据库明明没几条慢查询但业务还是卡得要命多半是锁在搞鬼。排查锁等待我习惯先看两条命令SHOW STATUS LIKE innodb_row_lock_waits; SELECT * FROM performance_schema.data_lock_waits;第一条命令告诉你等待的总次数第二条命令能查出具体的锁等待关系。另外SHOW ENGINE INNODB STATUS里有 LATEST DETECTED DEADLOCK 段落记录了最近一次死锁的详细信息包括涉及的 SQL、持有的锁、等待的锁是分析死锁的第一手资料。我处理过一个典型场景订单表频繁执行UPDATE orders SET status SHIPPED WHERE order_id ?按 order_id 更新单行逻辑上看起来没问题。但业务方反馈白天高峰期经常出现大量请求超时。排查发现有一个长事务在事务里先更新了某一行又去查其他表做了长时间的计算全程持锁超过 10 秒期间所有更新同一行的请求全部被阻塞。Threads_running飙高崩溃的却不是数据库本身而是业务方等不到响应。处理这种问题我的经验是两手抓一边在应用层强制设置事务超时时间避免事务无限期执行下去一边排查具体的长事务尽早提交或分拆。另外INNODB_TRX表可以查当前运行中的事务专门用它找“长时间不提交、又持有锁”的元凶。4.2 事务隔离级别与长事务的影响MySQL 默认的隔离级别是 REPEATABLE READ它对性能的影响主要体现在两个方面。第一间隙锁Gap Lock问题。在 REPEATABLE READ 下InnoDB 为了解决幻读问题会在索引记录间隙加锁。就是说你在一个事务里查了某个范围的数据另一个事务想往这个范围插入新记录会被阻塞。如果业务对幻读不敏感可以评估是否降级为 READ COMMITTED能减少很多无谓的锁竞争。第二长事务会撑大 undo log导致版本链变长。InnoDB 的 MVCC 依赖 undo log 构建历史版本如果事务长时间不提交它看到的旧版本数据就不能被清理版本链越来越长。查询时需要沿着版本链回溯性能自然会下降。更严重的是长事务可能会让被修改的行膨胀导致页空间不足触发页分裂操作进一步拖慢写性能。我见过的一个极端案例某同事在测试环境手动开了一个事务忘了提交结果第二天发现整个表的更新操作全部卡死。排查了半小时才发现那个残留的连接。后来我养成了一个习惯每次手动操作数据库一定最后检查SELECT * FROM information_schema.innodb_trx确保没有遗留事务。4.3 行锁升级与死锁的避坑清单InnoDB 的行锁是基于索引实现的。这意味着如果 UPDATE 或 DELETE 语句的 WHERE 条件没有走索引行锁就会退化为表锁导致所有对这张表的写操作全部串行化。所以排查写性能问题时除了看 SQL 本身还要确认 WHERE 条件用到的字段是否有索引。一个典型的反例是UPDATE orders SET status PAID WHERE order_no NO12345;如果 order_no 没有索引MySQL 必须全表扫描找到目标行而且在这个过程中所有扫描过的行都可能被锁上。数据量越大锁范围越大阻塞越严重。至于死锁InnoDB 会自动检测并回滚其中一个事务不会直接卡死系统。但频繁死锁会导致大量事务被回滚重试应用层需要不断重新执行表现为接口变慢、吞吐量下降。避免死锁最重要的原则是让事务按固定的顺序访问资源。比如同时更新用户表和订单表时所有事务都先更新用户表再更新订单表就不会出现互相等待的情况。5. 配置参数调优用合理的值做正确的取舍5.1 InnoDB Buffer Pool最重要的内存参数innodb_buffer_pool_size决定 InnoDB 能缓存多少数据和索引在内存中。这个参数设置得太小会导致磁盘 IO 频繁设置得太大又可能造成内存浪费或引发系统 OOM。对于纯 MySQL 实例官方建议设置为物理内存的 70%~80%。但实际部署中如果服务器上还跑着应用服务或其他中间件则需要适当下调。一个常用的评估方式是看Innodb_buffer_pool_reads的值它记录了从磁盘读取的次数如果这个值持续增长说明缓存命中率不高可以考虑调大 buffer pool。调参之后可以在 MySQL 侧直接修改、同时更新配置文件避免重启丢失SET GLOBAL innodb_buffer_pool_size 4294967296;当然这个参数在实例运行期间调整会有一些锁开销最好是在业务的低峰期进行操作。实际调参时还要配合监控 Buffer pool 命中率低于 95% 就要警觉。5.2 日志刷盘策略与安全取舍innodb_flush_log_at_trx_commit这个参数有三个取值0、1、2。默认值是 1表示每次事务提交都要把 redo log 刷到磁盘安全性最高但每次提交都有一次磁盘 fsync性能开销很大。设为 2表示每次提交只写入操作系统缓存每秒真正刷一次盘性能和安全性比较均衡。设为 0则由后台线程每秒刷盘速度最快但数据库崩溃时可能丢最后一秒的数据。很多互联网公司为了追求性能会把参数改成 2。这样做可以接受因为大部分业务能容忍极端情况下的秒级数据丢失。但如果你的业务对数据一致性要求极高比如金融支付系统就必须保持默认值 1或者通过组提交group commit缓解性能问题。5.3 连接数不是越大越好max_connections默认值一般是 151很多人在遇到连接数报警后第一反应是把它调大。但连接数越大线程上下文切换越频繁每个连接能分到的 CPU 时间片越少反而会拖慢整体性能。更好的思路是限制应用侧连接池的大小。比如 Java 的 HikariCP 连接池默认最大连接数是 10但很多业务团队为了“保险”动不动就配到 200。实际上对于单机 MySQL应用侧连接池总和最好控制在几百以内超出这个范围MySQL 的线程调度会成为瓶颈。关键是让应用等待排队还是让数据库直接拒绝——前者虽然延迟稍高但能避免服务雪崩。5.4 常用参数速查表参数建议值备注innodb_buffer_pool_size物理内存 70% 左右核心参数影响读写性能innodb_log_file_size1GB~4GB太小会导致频繁刷盘innodb_flush_log_at_trx_commit1 或 2按数据安全要求取舍max_connections200~500太大反而拖慢性能long_query_time1~2 秒配合慢查询日志使用tmp_table_size64MB~256MB太小会导致临时表落盘sort_buffer_size2MB~8MB每个线程独占不宜过大最后两个参数要特别说明它们都属于“每线程分配”的内存参数如果设置得太大在高并发下内存会瞬间翻倍。比如 300 个线程同时执行排序如果 sort_buffer_size 设成 256MB理论上可能吃掉 75GB 内存直接 OOM。所以这类参数宁小勿大没有特别需求不要轻易调高。6. 常见故障场景与避坑实录6.1 高频故障场景排查手册排查次数多了就发现性能故障往往是重复的模式。我自己整理了一个排查手册遇到问题直接按图索骥故障现象可能原因快速排查命令CPU 飙升慢 SQL、无索引扫描、大量排序SHOW PROCESSLIST、慢查询日志磁盘 IO 高Buffer pool 命中率低、redo log 刷盘频繁SHOW GLOBAL STATUS LIKE Innodb_buffer_pool_reads连接数满连接池配置过大、慢查询阻塞SHOW PROCESSLIST、SHOW VARIABLES LIKE max_connections大量锁等待长事务、索引失效导致锁范围扩大SHOW ENGINE INNODB STATUS主从延迟主库写入量大、从库单线程回放SHOW SLAVE STATUS、Seconds_Behind_Master偶发慢查询磁盘抖动、内存 swap、查询计划变化sar -d、dmesg、EXPLAIN排查主从延迟这个场景我额外说一句。如果从库用了并行复制并行度不够也会导致延迟。这往往不是单条 SQL 慢而是回放吞吐跟不上主库的写入速度。遇到这种情况要先看主库的写压力再看从库的复制线程配置必要时升级硬件或改并行复制策略。6.2 那些年踩过的配置坑第一个坑是sql_mode的变更。MySQL 5.7 之后默认启用ONLY_FULL_GROUP_BY如果业务的 SQL 写了不符合规范的 GROUP BY老版本会容忍新版本直接报错。升级 MySQL 版本时务必提前检查sql_mode对业务 SQL 的影响别等到上线才发现全部查询挂掉。第二个坑是字符集不一致引发的性能问题。关联表查询时如果两个表的字符串字段字符集不同MySQL 会做隐式转换导致索引失效。排查方法很简单用SHOW CREATE TABLE对比关联字段的字符集定义确保一致。第三个坑是int类型字段的显示宽度。很多人会纠结INT(5)和INT(11)的区别其实这个宽度不影响存储空间和取值只是控制显示时的位数对性能根本没有影响。真正影响存储的是类型本身INT固定占 4 字节BIGINT占 8 字节。在设计表时选对类型比花时间纠结显示宽度有意义得多。6.3 再分享几个实战中的独门技巧关于定位“瞬间飙高又恢复正常”的间歇性慢查询我有个独特的方法。这类问题在慢查询日志里可能看不到因为执行时间确实没超过阈值。我的处理方式是把performance_schema打开配合events_statements_summary_by_digest表按总延迟排序找出高消耗的 SQLSELECT DIGEST_TEXT, COUNT_STAR, SUM_TIMER_WAIT/1000000000 AS total_ms FROM performance_schema.events_statements_summary_by_digest ORDER BY SUM_TIMER_WAIT DESC LIMIT 20;这张表相当于一个语句级性能报表DIGEST_TEXT 是归一化之后的 SQL 文本SUM_TIMER_WAIT 是该类 SQL 的总耗时。通过它可以在不依赖慢查询日志的情况下找出整体消耗最大的 SQL 模式。还有一个技巧是用optimizer_trace查看优化器的具体决策过程。遇到那种“EXPLAIN 看起来很正常但实际执行很慢”的诡异情况可以用这个工具看看优化器到底是怎么计算成本的选索引的依据是什么。它能一步步打印出优化器的思考过程帮你看清楚执行计划为什么和你预期不一致。写在最后排查 MySQL 性能问题做久了我发现真正的高手不是会背多少参数而是有一套稳定的诊断流程和足够多的实战经验。慢查询日志、EXPLAIN、performance_schema 这些工具用熟了大部分问题一眼就能看出端倪。最难的不是技术本身而是在繁忙的业务压力下保持冷静系统地缩小排查范围最终定位到真正的问题根源。最后再分享一个小技巧每次处理完一个线上性能问题我都会写一份简短的复盘笔记包括现象、排查过程、根因和解决方案过段时间回看会发现很多问题本质上是同一类。积累几个月之后你自己也能总结出一套专属的排查手册处理问题会越来越快。希望这篇指南也能成为你排查路上的参考资料帮助你在下一次“数据库卡死”的警报声中多一分从容少一分慌乱。 MySQL性能优化实战：从慢查询定位到索引调优的完整排查指南
凌晨两点半被电话从被窝里薅起来的滋味做过数据库运维的朋友应该都不陌生。一接起来业务方那边火急火燎页面打不开、订单提交超时、接口全部卡死。登录服务器一看MySQL CPU 直接飙到 800%终端敲命令都带着延迟。这种场景我经历过太多次每次复盘都会想如果一开始就能用对方法定位瓶颈至少能把凌晨的救命电话减少一半。MySQL 性能瓶颈排查这件事最怕的就是“拍脑袋”。一看到 CPU 高就加内存一看到慢查询就加索引运气好的时候能蒙对运气不好反而把问题搞得更复杂。真正的靠谱路线应该是“先诊断、再定位、后根治”用数据说话拿证据开刀。这篇文章就把我这些年排查性能问题的完整套路整理出来从现象收集、慢查询分析、执行计划解读到索引优化、锁等待剖析、参数调优一条线走到底。不管是刚接触 MySQL 的开发者还是已经被线上问题毒打过的 DBA这篇都能给你一套可以直接复用的作战手册。1. 诊断的整体思路先分清“病在哪”再谈“怎么治”1.1 遇到性能问题先别急着调参数很多人一碰到数据库变慢第一反应是“把某个参数调大”。这个习惯非常危险因为 MySQL 性能瓶颈可能出现在硬件层、系统层、数据库层、SQL 层甚至业务架构层。不同层面的问题表现出的症状很像但药方完全不同。我把性能问题按发生概率排了个序大家可以照着这个顺序去查SQL 写法问题占了大概 60% 以上的情况比如缺索引、深分页、隐式类型转换、SELECT * 带回大量无用字段。索引失效占了 20% 左右比如在索引列上做函数运算、模糊查询前置通配符、联合索引不满足最左前缀。锁竞争与事务问题占了 10% 左右比如长事务持锁不释放、间隙锁导致并发插入被阻塞。数据库配置不合理占了 5% 左右比如 buffer pool 太小、redo log 刷盘策略太保守。硬件或系统瓶颈占了不到 5%比如磁盘 IOPS 跑满、swap 交换导致抖动。这个排序不是我拍脑袋统计的而是基于我多年排查的线上案例总结出来的经验规律。绝大多数性能问题根源都在 SQL 和索引这一层恰恰是这两层最容易被忽略。很多人一开始就去调 buffer pool、改刷盘策略方向完全错了。1.2 建立诊断基线先拿数据再动手我自己的习惯是接到任何性能问题先花 10 分钟把现场数据完整采集下来。不上服务器乱敲命令而是按固定流程抓取下面这几类信息当前系统负载top、sar -q看 CPU 核数是否跑满、负载是否超出正常水位。MySQL 全局状态SHOW GLOBAL STATUS重点看Threads_connected、Threads_running、QPS。当前活跃会话SHOW PROCESSLIST看哪些 SQL 卡着不动。InnoDB 引擎状态SHOW ENGINE INNODB STATUS看死锁、锁等待和事务状态。慢查询日志这是最重要的一手证据直接看压垮系统的 SQL 长什么样。采集完这些数据再开始判断方向。举个例子如果Threads_connected只有 20但Threads_running是 15说明有大量 SQL 同时在执行且互相争抢资源优先排查 SQL 效率和锁等待。如果Threads_connected已经 500 多接近最大连接数那首先要处理的是连接风暴问题而不是 SQL 效率。这种“先取证、后下结论”的思路能帮你避免被表面的 CPU 高占用带偏。CPU 高只是一个结果真正的原因可能是 SQL 低效、可能是锁等待造成了大量重试、也可能是连接数失控导致上下文切换频繁。只有拿到一手现场数据才能判断问题到底出在哪一层。2. 慢查询日志与性能指标把瓶颈从“感觉”变成“数字”2.1 慢查询日志的正确配置与使用慢查询日志是排查性能问题最基础也最重要的工具。它记录了执行时间超过阈值的 SQL 语句基本上就是性能问题的“嫌疑人名单”。但在实际工作中我发现很多人的慢查询日志配置不合理要么根本没开启要么long_query_time设得太大导致关键 SQL 没有进日志。我推荐一组稳妥的配置slow_query_log 1 slow_query_log_file /var/log/mysql/mysql-slow.log long_query_time 1 log_queries_not_using_indexes 1 log_slow_admin_statements 1其中long_query_time我一般会设成 1 秒也就是超过 1 秒的查询全部记录下来。初期排查阶段宁可日志多些也不要漏掉线索。等系统稳定之后再根据业务情况调整为 2 秒或 3 秒减少日志量。另外log_queries_not_using_indexes这个参数建议开着它能把没有走索引的查询也记录下来哪怕执行时间很短。很多隐性问题比如扫全表但是数据量暂时不大不会触发慢查询阈值但日后数据涨上来就会爆炸。通过这个参数提前发现潜在风险比事后救火舒服多了。慢查询日志默认是文本文件格式直接less看也行但内容多了之后效率太低。我习惯用mysqldumpslow工具做聚合分析mysqldumpslow -s at -t 10 /var/log/mysql/mysql-slow.log这个命令按平均执行时间排序显示最慢的 10 条 SQL适合从一堆日志里快速找出“首恶”。如果服务器上不方便装工具也可以用pt-query-digest分析它是 Percona Toolkit 里的神器能按指纹聚合 SQL统计出每条 SQL 的平均耗时、总耗时、扫描行数等一眼就能看出哪些 SQL 是真正的瓶颈。2.2 关键性能指标怎么看拿到慢查询日志只是第一步还需要结合整体性能指标判断问题的严重程度和影响范围。我日常关注的核心指标主要有这几个指标含义预警线Threads_connected当前连接数超过 max_connections 的 80%Threads_running正在执行的线程数持续大于 CPU 核心数QPS每秒查询数与业务峰值对比异常升高TPS每秒事务数与业务峰值对比异常降低Buffer pool 命中率InnoDB 缓存命中率低于 95% 需要关注临时表创建量Created_tmp_disk_tables增长明显说明 SQL 有问题锁等待次数Innodb_row_lock_waits持续增长说明有锁竞争这里特别说一下Threads_running它比Threads_connected更能反映系统真实负载。连接数高不代表有问题很多连接是空闲的。但Threads_running高说明真的有一堆 SQL 在同时执行CPU 和磁盘资源正在被消耗。如果这个值持续超过 CPU 核心数系统基本已经处于过载状态。我见过一个案例某系统的Threads_connected长期稳定在 100 左右看起来很正常但Threads_running动不动就飙到 40 多而服务器只有 16 核。一查慢日志发现某个报表查询每次要扫描 3000 万行数据执行时间 20 多秒。这个 SQL 一跑立刻占满所有 CPU其他会话全部排队。后来加了覆盖索引扫描行数降到几十万行Threads_running直接掉到个位数。2.3 用 sysbench 做一次保守的压力测试排查性能问题的时候经常需要回答一个问题当前这个硬件和配置理论上能撑住多少 QPS这个基准值很有价值能帮你判断业务请求量和系统容量之间是否匹配。我自己常用 sysbench 做基准测试。先准备一张测试表灌入一定量的数据然后模拟读写混合场景。命令大概长这样sysbench /usr/share/sysbench/oltp_read_write.lua \ --mysql-host127.0.0.1 \ --mysql-port3306 \ --mysql-userroot \ --mysql-passwordyourpass \ --tables4 \ --table-size1000000 \ --threads16 \ --time60 \ prepare sysbench /usr/share/sysbench/oltp_read_write.lua \ --mysql-host127.0.0.1 \ --mysql-port3306 \ --mysql-userroot \ --mysql-passwordyourpass \ --tables4 \ --table-size1000000 \ --threads16 \ --time60 \ run通过压测结果能看到当前机器的极限 TPS/QPS、延迟分布、95% 延迟等数据。这套基准值在后续做参数调优时非常有用——你改了某个配置再跑一遍同样的测试就能量化对比前后性能差异比凭感觉判断靠谱得多。需要注意基准测试最好在业务低峰期进行操作压测会占用大量硬件资源生产环境必须要慎重。我自己的做法是先在测试环境摸清基线生产环境只在半夜真正需要容量评估时才跑。3. SQL 分析与索引优化搞定八成以上的慢查询3.1 EXPLAIN 执行计划读懂 MySQL 的“内心戏”慢查询日志只是列出嫌疑人EXPLAIN 就是审问工具。每条有问题的 SQL我都会粘贴出来跑一遍EXPLAIN看 MySQL 实际是怎么执行的。EXPLAIN SELECT * FROM orders WHERE user_id 12345 AND status PAID ORDER BY created_at DESC LIMIT 10;执行计划里我最关注这几列type访问类型从好到差依次是 system const eq_ref ref range index ALL。看到 ALL 就要警惕那是全表扫描。key实际使用的索引如果为 NULL说明没有用索引。rows预估扫描行数这个数字越小越好。如果预估扫描几百万行却只返回 10 条说明索引选择有问题。Extra这里经常出现 “Using filesort” 或 “Using temporary”意味着 MySQL 需要额外排序或建临时表是性能杀手。看type列的时候我把ALL和index视为红色警报。index虽然字面意思是用了索引但实际上是遍历了整棵索引树和全表扫描的代价差不多只是比ALL稍微好一点。如果看到Using filesort且排序字段没有走索引在数据量大的时候会非常拖慢查询这个信号也要重视。3.2 索引失效的几大常见坑很多人的 SQL 明明建了索引查询还是慢就是因为踩了索引失效的坑。我整理了高频出现的几种情况在索引列上做函数运算或表达式计算WHERE YEAR(created_at) 2024会导致索引失效应该改成WHERE created_at 2024-01-01 AND created_at 2025-01-01。隐式类型转换WHERE phone 13800000000如果 phone 字段是 varchar 类型MySQL 会把字段隐式转成数字导致索引失效。这种 bug 最难查因为不加 EXPLAIN 根本看不出来。模糊查询前置通配符WHERE name LIKE %张无法使用索引但WHERE name LIKE 张%可以范围扫描。联合索引不满足最左前缀原则。联合索引 (a, b, c)查询条件只用到 b 或 c索引直接失效。还有一个特别隐蔽的坑对字段做“隐式字符集转换”。比如关联两个表的字符串字段一个表的字符集是 utf8mb4另一个是 utf8mb4_general_ci且排序规则不一致MySQL 会在关联时做隐式转换导致关联条件上的索引失效。这个问题在多数据库迁移合并的时候特别容易踩到排查起来非常头疼。我的经验是凡是外键关联字段或高频查询条件字段尽量统一字符集和排序规则。3.3 深分页问题与覆盖索引优化分页查询是业务开发里最普遍的需求也是慢查询的重灾区。尤其是深分页比如LIMIT 1000000, 20前一百万条数据全都被扫描一遍再丢弃索引再强也扛不住。我之前处理过一个案例一个后台列表页数据量大约 500 万行每次翻到第 50 页以后接口响应时间就从 200ms 暴涨到 5 秒。EXPLAIN 一看type 是 rangekey 是主键扫描行数 100 多万。问题就出在LIMIT的偏移量太大MySQL 必须先扫描足够多的行才能跳过前面的记录。这里给出两种标准优化方案方案一是延迟关联deferred join。先通过覆盖索引查出目标行的主键 ID再用主键回表取完整数据SELECT o.* FROM orders o INNER JOIN ( SELECT id FROM orders WHERE status PAID ORDER BY created_at DESC LIMIT 1000000, 20 ) t ON o.id t.id;核心思路是让内层查询只在索引上操作索引体积远小于全表扫描成本低得多。再加一个覆盖索引 (status, created_at, id) 支撑内层排序和筛选性能提升非常明显。这个案例优化后第 50 页的响应时间从 5 秒降到 200ms 出头。方案二是基于游标的分页也就是记住上一页最后一条记录的排序值SELECT * FROM orders WHERE status PAID AND (created_at, id) (2024-06-01 12:00:00, 100001) ORDER BY created_at DESC, id DESC LIMIT 20;这种方案适合移动端下拉加载天然支持深翻页性能非常稳定但前提是排序字段必须有唯一性约束所以我会在 order by 里加 id 作为第二排序键防止数据重复或漏掉。3.4 一个完整的优化案例复盘拿一个实际优化过的业务查询举例。业务需求是“查最近一个月内购买超过三次的用户”最初的 SQL 长这样SELECT user_id, COUNT(*) FROM orders WHERE created_at DATE_SUB(NOW(), INTERVAL 1 MONTH) GROUP BY user_id HAVING COUNT(*) 3;orders 表当时有 800 万行数据这个 SQL 执行一次要 12 秒严重拖垮数据库性能。EXPLAIN 显示 type 是 ALL全表扫描并且在 Using temporary 和 Using filesort。优化思路分两步第一步为created_at建索引让 WHERE 条件能走范围扫描减少参与 GROUP BY 的数据量。第二步反范式化处理在用户表加一个order_count_month字段每次完成订单时在事务里更新这个计数再用定时任务或应用侧逻辑做重置。这样报表查询直接查用户表不用再扫描订单表。优化之后查询响应时间从 12 秒降到 50ms 以内。这个案例说明一个道理SQL 写法上的优化能做到“省力”但如果业务场景频繁需要大数据量聚合统计就要考虑通过表结构设计或数据冗余来“省功”。两类手段配合使用才能真正根治问题。4. 锁与事务看不见摸不着的性能杀手4.1 锁等待的排查方法锁等待是 MySQL 性能问题里最隐蔽的一类。有时数据库明明没几条慢查询但业务还是卡得要命多半是锁在搞鬼。排查锁等待我习惯先看两条命令SHOW STATUS LIKE innodb_row_lock_waits; SELECT * FROM performance_schema.data_lock_waits;第一条命令告诉你等待的总次数第二条命令能查出具体的锁等待关系。另外SHOW ENGINE INNODB STATUS里有 LATEST DETECTED DEADLOCK 段落记录了最近一次死锁的详细信息包括涉及的 SQL、持有的锁、等待的锁是分析死锁的第一手资料。我处理过一个典型场景订单表频繁执行UPDATE orders SET status SHIPPED WHERE order_id ?按 order_id 更新单行逻辑上看起来没问题。但业务方反馈白天高峰期经常出现大量请求超时。排查发现有一个长事务在事务里先更新了某一行又去查其他表做了长时间的计算全程持锁超过 10 秒期间所有更新同一行的请求全部被阻塞。Threads_running飙高崩溃的却不是数据库本身而是业务方等不到响应。处理这种问题我的经验是两手抓一边在应用层强制设置事务超时时间避免事务无限期执行下去一边排查具体的长事务尽早提交或分拆。另外INNODB_TRX表可以查当前运行中的事务专门用它找“长时间不提交、又持有锁”的元凶。4.2 事务隔离级别与长事务的影响MySQL 默认的隔离级别是 REPEATABLE READ它对性能的影响主要体现在两个方面。第一间隙锁Gap Lock问题。在 REPEATABLE READ 下InnoDB 为了解决幻读问题会在索引记录间隙加锁。就是说你在一个事务里查了某个范围的数据另一个事务想往这个范围插入新记录会被阻塞。如果业务对幻读不敏感可以评估是否降级为 READ COMMITTED能减少很多无谓的锁竞争。第二长事务会撑大 undo log导致版本链变长。InnoDB 的 MVCC 依赖 undo log 构建历史版本如果事务长时间不提交它看到的旧版本数据就不能被清理版本链越来越长。查询时需要沿着版本链回溯性能自然会下降。更严重的是长事务可能会让被修改的行膨胀导致页空间不足触发页分裂操作进一步拖慢写性能。我见过的一个极端案例某同事在测试环境手动开了一个事务忘了提交结果第二天发现整个表的更新操作全部卡死。排查了半小时才发现那个残留的连接。后来我养成了一个习惯每次手动操作数据库一定最后检查SELECT * FROM information_schema.innodb_trx确保没有遗留事务。4.3 行锁升级与死锁的避坑清单InnoDB 的行锁是基于索引实现的。这意味着如果 UPDATE 或 DELETE 语句的 WHERE 条件没有走索引行锁就会退化为表锁导致所有对这张表的写操作全部串行化。所以排查写性能问题时除了看 SQL 本身还要确认 WHERE 条件用到的字段是否有索引。一个典型的反例是UPDATE orders SET status PAID WHERE order_no NO12345;如果 order_no 没有索引MySQL 必须全表扫描找到目标行而且在这个过程中所有扫描过的行都可能被锁上。数据量越大锁范围越大阻塞越严重。至于死锁InnoDB 会自动检测并回滚其中一个事务不会直接卡死系统。但频繁死锁会导致大量事务被回滚重试应用层需要不断重新执行表现为接口变慢、吞吐量下降。避免死锁最重要的原则是让事务按固定的顺序访问资源。比如同时更新用户表和订单表时所有事务都先更新用户表再更新订单表就不会出现互相等待的情况。5. 配置参数调优用合理的值做正确的取舍5.1 InnoDB Buffer Pool最重要的内存参数innodb_buffer_pool_size决定 InnoDB 能缓存多少数据和索引在内存中。这个参数设置得太小会导致磁盘 IO 频繁设置得太大又可能造成内存浪费或引发系统 OOM。对于纯 MySQL 实例官方建议设置为物理内存的 70%~80%。但实际部署中如果服务器上还跑着应用服务或其他中间件则需要适当下调。一个常用的评估方式是看Innodb_buffer_pool_reads的值它记录了从磁盘读取的次数如果这个值持续增长说明缓存命中率不高可以考虑调大 buffer pool。调参之后可以在 MySQL 侧直接修改、同时更新配置文件避免重启丢失SET GLOBAL innodb_buffer_pool_size 4294967296;当然这个参数在实例运行期间调整会有一些锁开销最好是在业务的低峰期进行操作。实际调参时还要配合监控 Buffer pool 命中率低于 95% 就要警觉。5.2 日志刷盘策略与安全取舍innodb_flush_log_at_trx_commit这个参数有三个取值0、1、2。默认值是 1表示每次事务提交都要把 redo log 刷到磁盘安全性最高但每次提交都有一次磁盘 fsync性能开销很大。设为 2表示每次提交只写入操作系统缓存每秒真正刷一次盘性能和安全性比较均衡。设为 0则由后台线程每秒刷盘速度最快但数据库崩溃时可能丢最后一秒的数据。很多互联网公司为了追求性能会把参数改成 2。这样做可以接受因为大部分业务能容忍极端情况下的秒级数据丢失。但如果你的业务对数据一致性要求极高比如金融支付系统就必须保持默认值 1或者通过组提交group commit缓解性能问题。5.3 连接数不是越大越好max_connections默认值一般是 151很多人在遇到连接数报警后第一反应是把它调大。但连接数越大线程上下文切换越频繁每个连接能分到的 CPU 时间片越少反而会拖慢整体性能。更好的思路是限制应用侧连接池的大小。比如 Java 的 HikariCP 连接池默认最大连接数是 10但很多业务团队为了“保险”动不动就配到 200。实际上对于单机 MySQL应用侧连接池总和最好控制在几百以内超出这个范围MySQL 的线程调度会成为瓶颈。关键是让应用等待排队还是让数据库直接拒绝——前者虽然延迟稍高但能避免服务雪崩。5.4 常用参数速查表参数建议值备注innodb_buffer_pool_size物理内存 70% 左右核心参数影响读写性能innodb_log_file_size1GB~4GB太小会导致频繁刷盘innodb_flush_log_at_trx_commit1 或 2按数据安全要求取舍max_connections200~500太大反而拖慢性能long_query_time1~2 秒配合慢查询日志使用tmp_table_size64MB~256MB太小会导致临时表落盘sort_buffer_size2MB~8MB每个线程独占不宜过大最后两个参数要特别说明它们都属于“每线程分配”的内存参数如果设置得太大在高并发下内存会瞬间翻倍。比如 300 个线程同时执行排序如果 sort_buffer_size 设成 256MB理论上可能吃掉 75GB 内存直接 OOM。所以这类参数宁小勿大没有特别需求不要轻易调高。6. 常见故障场景与避坑实录6.1 高频故障场景排查手册排查次数多了就发现性能故障往往是重复的模式。我自己整理了一个排查手册遇到问题直接按图索骥故障现象可能原因快速排查命令CPU 飙升慢 SQL、无索引扫描、大量排序SHOW PROCESSLIST、慢查询日志磁盘 IO 高Buffer pool 命中率低、redo log 刷盘频繁SHOW GLOBAL STATUS LIKE Innodb_buffer_pool_reads连接数满连接池配置过大、慢查询阻塞SHOW PROCESSLIST、SHOW VARIABLES LIKE max_connections大量锁等待长事务、索引失效导致锁范围扩大SHOW ENGINE INNODB STATUS主从延迟主库写入量大、从库单线程回放SHOW SLAVE STATUS、Seconds_Behind_Master偶发慢查询磁盘抖动、内存 swap、查询计划变化sar -d、dmesg、EXPLAIN排查主从延迟这个场景我额外说一句。如果从库用了并行复制并行度不够也会导致延迟。这往往不是单条 SQL 慢而是回放吞吐跟不上主库的写入速度。遇到这种情况要先看主库的写压力再看从库的复制线程配置必要时升级硬件或改并行复制策略。6.2 那些年踩过的配置坑第一个坑是sql_mode的变更。MySQL 5.7 之后默认启用ONLY_FULL_GROUP_BY如果业务的 SQL 写了不符合规范的 GROUP BY老版本会容忍新版本直接报错。升级 MySQL 版本时务必提前检查sql_mode对业务 SQL 的影响别等到上线才发现全部查询挂掉。第二个坑是字符集不一致引发的性能问题。关联表查询时如果两个表的字符串字段字符集不同MySQL 会做隐式转换导致索引失效。排查方法很简单用SHOW CREATE TABLE对比关联字段的字符集定义确保一致。第三个坑是int类型字段的显示宽度。很多人会纠结INT(5)和INT(11)的区别其实这个宽度不影响存储空间和取值只是控制显示时的位数对性能根本没有影响。真正影响存储的是类型本身INT固定占 4 字节BIGINT占 8 字节。在设计表时选对类型比花时间纠结显示宽度有意义得多。6.3 再分享几个实战中的独门技巧关于定位“瞬间飙高又恢复正常”的间歇性慢查询我有个独特的方法。这类问题在慢查询日志里可能看不到因为执行时间确实没超过阈值。我的处理方式是把performance_schema打开配合events_statements_summary_by_digest表按总延迟排序找出高消耗的 SQLSELECT DIGEST_TEXT, COUNT_STAR, SUM_TIMER_WAIT/1000000000 AS total_ms FROM performance_schema.events_statements_summary_by_digest ORDER BY SUM_TIMER_WAIT DESC LIMIT 20;这张表相当于一个语句级性能报表DIGEST_TEXT 是归一化之后的 SQL 文本SUM_TIMER_WAIT 是该类 SQL 的总耗时。通过它可以在不依赖慢查询日志的情况下找出整体消耗最大的 SQL 模式。还有一个技巧是用optimizer_trace查看优化器的具体决策过程。遇到那种“EXPLAIN 看起来很正常但实际执行很慢”的诡异情况可以用这个工具看看优化器到底是怎么计算成本的选索引的依据是什么。它能一步步打印出优化器的思考过程帮你看清楚执行计划为什么和你预期不一致。写在最后排查 MySQL 性能问题做久了我发现真正的高手不是会背多少参数而是有一套稳定的诊断流程和足够多的实战经验。慢查询日志、EXPLAIN、performance_schema 这些工具用熟了大部分问题一眼就能看出端倪。最难的不是技术本身而是在繁忙的业务压力下保持冷静系统地缩小排查范围最终定位到真正的问题根源。最后再分享一个小技巧每次处理完一个线上性能问题我都会写一份简短的复盘笔记包括现象、排查过程、根因和解决方案过段时间回看会发现很多问题本质上是同一类。积累几个月之后你自己也能总结出一套专属的排查手册处理问题会越来越快。希望这篇指南也能成为你排查路上的参考资料帮助你在下一次“数据库卡死”的警报声中多一分从容少一分慌乱。