1. 为什么我的MySQL突然变慢了：一次线上故障的复盘
大概半年前，我负责维护的一个业务系统突然出现了接口响应时间暴涨的情况。监控面板显示数据库连接数飙升，CPU打满，应用层的慢请求日志里一片飘红。我一开始还以为是代码问题，顺手把几个接口的日志翻了个底朝天，结果发现业务代码逻辑根本没变，改动记录也干干净净。
后来登录到数据库服务器上看了一眼，才发现问题全在MySQL这一侧：几条原本走索引的SQL执行计划变成了全表扫描，InnoDB的buffer pool命中率掉到了90%以下，redo log的写入频率明显异常。也就是从那天起，我决定把MySQL的调优和排障当成一个系统性工程来做，而不是等到线上炸了才去救火。
这篇文章想分享的就是我在这个过程中沉淀下来的实操经验，包括慢SQL定位、索引设计、执行计划解读、参数调优和大表处理这几个方向。适合刚接手MySQL维护工作的开发或DBA同学参考，也适合那些已经被慢查询折磨过、想系统补一补排查思路的人。文中的案例都做了脱敏处理，但排查思路和命令是可以直接抄作业的。
2. 慢SQL定位：不能只靠慢查询日志
2.1 慢查询日志的正确开启方式
很多同学知道有个慢查询日志，但实际配置得比较随意。我见过不少服务器上长期开着慢查询日志，long_query_time却还是默认的10秒——在这种配置下，绝大多数业务SQL根本不会进日志，等于这个功能白开了。
我的建议是至少把阈值降到1秒，关键业务表甚至可以临时降到0.5秒来观察。通过如下方式确认当前配置：
SHOW VARIABLES LIKE 'slow_query_log%'; SHOW VARIABLES LIKE 'long_query_time';如果没开启，可以动态打开：
SET GLOBAL slow_query_log = ON; SET GLOBAL long_query_time = 1; SET GLOBAL log_queries_not_using_indexes = ON;这里说两点经验。
第一，long_query_time的修改只对后续新连接生效，对已存在的连接不生效。如果你是在客户端工具里直接执行SET GLOBAL，那么当前这个会话还要重新连接才能真正产生慢查询日志。这个细节特别容易让新手误以为配置没生效。
第二，log_queries_not_using_indexes这个参数建议在测试环境打开，但线上要谨慎。生产库一旦开了它，日志量可能爆炸式增长，因为很多写操作即使只更新几条数据，也可能因为没命中索引而被记录下来。我一般只在排查期间临时开启，拿到数据后就关掉。
2.2 日志内容的解读要点
慢查询日志的每一行记录都包含几个关键信息：SQL执行时间、锁等待时间、返回行数、扫描行数、以及SQL语句本身。举个例子：
# Query_time: 2.345678 Lock_time: 0.000123 # Rows_sent: 10 Rows_examined: 200000 SET timestamp=1700000000; SELECT ... FROM order_detail WHERE user_id = 12345 AND status = 1;这里Rows_sent是10，Rows_examined却是20万，扫描行数和返回行数差了四个数量级，一眼就能断定这条SQL存在严重的索引使用问题。
为了快速分析大量慢SQL，我习惯用mysqldumpslow来汇总：
mysqldumpslow -s at -t 20 /var/log/mysql/slow.log-s at表示按平均查询时间排序，-t 20表示取前20条。这个工具会把SQL语句中的具体参数值抽象掉，自动归类相似的语句，省去手工翻几千行日志的折磨。不过要注意，生产环境日志文件可能会很大，建议做一个日志切割任务，避免单文件占用过多磁盘。
2.3 线上临时抓取慢SQL的轻量方案
有时候慢查询日志还没来得及刷盘，我们就想抓当前正在执行的SQL，这种情况我会用information_schema.processlist来临时救急：
SELECT id, user, host, db, command, time, state, LEFT(info, 200) FROM information_schema.processlist WHERE command != 'Sleep' ORDER BY time DESC LIMIT 20;time字段就是这个连接当前已经执行了多久。如果发现某个查询执行时间特别长，且state是Sending data或Copying to tmp table，基本可以判断它在大量扫描数据或者在做临时表排序。此时可以用EXPLAIN去验证执行计划，或者直接和业务方确认语句是否可以终止。
我还用过一种更轻的办法：在业务侧给数据库访问层加一个慢请求拦截的钩子，把超过阈值的SQL自动打印出来。虽然这需要改代码，但对定位"偶尔慢一下"的毛刺问题特别有效，因为日志往往会漏掉一些刚好没达到慢查询阈值的微妙异常。
3. 索引设计：从"加了索引"到"加对索引"
3.1 B+树结构决定了你的查询成本
索引优化之前，先想清楚一个基础问题：MySQL的InnoDB引擎使用B+树组织索引，叶子节点存放的是完整的主键值和索引列值。每次查询从根节点向下走，走多少层取决于数据量。一个三层的B+树轻轻松松支持千万级数据量，这也是为什么单表几百万行数据只要索引用得对，性能通常不会有问题。
真正拖慢查询的，往往是回表。所谓回表，就是通过二级索引找到主键之后，再回到聚簇索引上去取完整数据行。每回一次表就相当于多做一次随机IO访问，数据量大时这个成本会被放大到可怕的程度。所以索引设计的核心不是"有没有索引"，而是"能不能减少回表、能不能用上覆盖索引"。
3.2 联合索引的列顺序不能拍脑袋
联合索引是最容易被用错的地方。很多人建索引时想当然地把区分度高的列放在前面，可实际上最左前缀原则要求查询条件必须从联合索引的最左列开始连续匹配。一个(a, b, c)联合索引，实际上可以支持(a)、(a,b)、(a,b,c)三组查询，但无法支持跳过a直接查b。
我常用的设计方法是：先看业务查询的等值条件有哪些，把等值条件列放在联合索引前面；再看范围条件和排序字段，放在后面。举一个实际例子，订单查询场景经常用user_id + status + create_time组合，我会建议把索引建成(user_id, status, create_time)，这样既能等值过滤用户和状态，又能用上时间字段做排序，避免额外的filesort。
3.3 覆盖索引是性价比最高的优化
如果一个二级索引包含了查询需要的所有列，那么查询过程就不需要回表，InnoDB直接从这个索引上把数据返回。这种索引叫做覆盖索引。在报表类查询、统计类查询里，覆盖索引的效果立竿见影。
举个例子，一条统计SQL：
SELECT COUNT(*) FROM order_detail WHERE status = 1;如果status列上只有普通索引，InnoDB依然会扫描整个索引树，但因为索引树比数据行小很多，扫描成本已经比全表扫描低了不少。如果进一步把查询改成SELECT COUNT(*) FROM order_detail WHERE status = 1 AND pay_time > '2024-01-01'，那就需要考虑让复合索引能覆盖统计条件。
我发现很多人对覆盖索引有个误解，以为把所有查询列都塞进一个索引就是覆盖索引，结果索引列膨胀得比数据还宽，写入变慢、索引体积变大。覆盖索引的正确使用方式是只包含高频查询需要的少量列，而不是把全表字段都塞进去。
3.4 索引失效的常见场景与规避
索引失效的问题我在线上踩过多次。最常见的几个：对索引列做了函数运算、隐式类型转换、LIKE的前导通配符、OR条件中有一个字段没有索引。
-- 索引失效 SELECT * FROM user WHERE DATE(create_time) = '2024-01-01'; -- 改写后可走索引 SELECT * FROM user WHERE create_time >= '2024-01-01' AND create_time < '2024-01-02';隐式类型转换特别坑。如果phone字段是varchar类型，查询条件里却写成了WHERE phone = 13800138000，MySQL会尝试把字符串列转换为数字再比较，索引自然就失效了。排查时可以看看执行计划里的type字段是不是变成了ALL。关于这一点，后面讲EXPLAIN时会再展开。
4. EXPLAIN解读：执行计划里被忽略的信息
4.1 type、rows、Extra到底怎么看
执行计划是判断SQL性能的关键依据，但光会用EXPLAIN还不够，关键是要看懂几个核心字段的组合含义。
type：从高到低常见的有const、eq_ref、ref、range、index、ALL。看到ALL基本意味着全表扫描，这时候就应该警惕了。index虽然是全索引扫描，比ALL好，但依然不理想。rows：预估扫描行数，这个值只是一个估算，但能反映优化器的判断。Extra：常见的有Using index（覆盖索引）、Using where、Using filesort、Using temporary。看到后两个就要下意识警觉，它们通常意味着排序或去重操作需要额外的临时文件，消耗很大。
我见过一个经典案例：某条SQL的type已经是ref，也走了索引，但线上还是慢。后来发现Extra里出现了Using filesort，排序字段没有包含在索引里。优化器选择先按索引取数据，再在内存或磁盘上排序。数据量一大，这个排序就变成了性能瓶颈。
4.2 临时表与filesort的隐藏开销
说到Using temporary，这是分组、去重、子查询场景里比较容易出现的性能杀手。MySQL会在内存中创建临时表，数据量大时还会自动落盘到临时文件。临时表的创建和清理都需要代价，而且它不受innodb_buffer_pool_size的直接管理。
有一次排查一个列表接口，SQL中用了DISTINCT加ORDER BY，执行计划里Extra同时出现了Using temporary和Using filesort。如果业务确实只需要去重，可以在应用层完成，而不是让数据库扛；如果必须在SQL里做，那就要考虑把排序和去重字段放进合适的索引中，让索引天然有序，从而消除这两个额外操作。
4.3 一个案例：为什么明明走了索引还是慢
某一次我收到一个反馈，说某个查询加了索引之后还是慢，EXPLAIN显示type=ref，key也正常，看起来没有任何问题。我深入查了数据分布，发现问题出在索引的区分度上。status字段只有0和1两种取值，而且99%的数据都是status=1，这种情况下走索引和全表扫描的区别很小，优化器有时候甚至会在rows估算过大时放弃索引。
所以看EXPLAIN不能只看type，还要结合rows和实际的数据分布来综合判断。如果索引列的值重复度太高，走索引反而意味着大量回表和随机IO，这时候合理的方案可能是放弃这个索引，或者考虑对数据做预处理，比如按天分表、增加汇总表。索引不是越多越好，有时候一个没用的索引还会拖慢写操作和增加存储成本。
5. MySQL参数调优：不是抄个配置文件就完事
5.1 innodb_buffer_pool_size才是核心中的核心
InnoDB的缓冲池决定了MySQL能缓存多少数据和索引页，它直接决定了随机读请求是走内存还是落磁盘。很多线上系统的innodb_buffer_pool_size都设置得偏小，默认的128M在稍微有点规模的生产环境里完全不够用。
一个合理的设置经验是：在专门的MySQL服务器上，缓冲池可以设置为物理内存的60%到70%。如果机器同时还跑着应用服务，那就要酌情下调，为操作系统和应用进程留够空间。修改之后可以通过以下指标确认效果：
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read_requests'; SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_reads';用read_requests减去reads再除以read_requests，可以算出一个大致的命中率。如果命中率长期低于95%，说明缓冲池可能偏小，或者访问模式里存在大量低频的一次性扫描查询。
这里要提醒一句：调大缓冲池不是重启后立刻生效那么简单的，MySQL需要在启动时预分配内存。配置了过大的缓冲池在低内存机器上可能导致无法启动或者频繁内存交换。修改前先评估机器实际可用内存。
5.2 连接数不是越大越好
max_connections这个参数很多人一看就知道是控制最大连接数的，但调参时很容易犯一个错误：盲目加大连接数。连接本身不占多少资源，但每个连接都需要线程栈和内存，连接数过大时MySQL会频繁切换线程上下文，甚至触发Too many connections的报错，不是因为连接数不够，而是因为单连接执行时间太长，把连接池占满了。
所以在调max_connections之前，应该先检查是否有慢查询占用了大量连接。如果一个查询执行几秒钟，期间它的连接资源一直被占用，1000个连接可能很快就被打满。这时候该做的是优化SQL，而不是无限加连接数。常规项目的max_connections设置在300到500之间比较常见，具体看实例规格和业务并发量。
5.3 redo log与刷盘策略：数据安全与性能的平衡
redo log（重做日志）是用来保证崩溃恢复能力的，它的刷盘策略由innodb_flush_log_at_trx_commit控制。这个参数有三个值：0表示每秒刷一次盘，性能最好，但MySQL崩溃可能丢最近一秒的数据；1表示每次事务提交都刷盘，最安全，也是默认值；2表示每次提交只写到操作系统缓存，每秒再刷一次盘，折中方案。
如果是非金融类业务，允许极端情况下丢几笔业务数据，可以考虑设为2，能明显减少磁盘IO压力。但我个人的习惯是，凡是核心交易链路相关的实例，一律保持1。这个选择没有绝对的对错，关键是业务方要清楚数据丢失的边界在哪里。
5.4 binlog和主从复制的相关参数
sync_binlog控制binlog刷盘频率，设置为1时每次事务提交都会将binlog刷入磁盘，安全性最高，但也会有性能损耗。很多主从架构下的MySQL出现主从延迟，不一定是网络问题，而是从库的relay log回放效率不够，或者有大事务在主库上执行了太久。
大事务是主从延迟的头号杀手。我曾经处理过一个案例，某个定时任务一次性更新了上百万行数据，整个事务跑了十几分钟，主库虽然执行完了，但从库在回放这个事务时花费了更长的时间，导致读写分离场景下读库看到的数据延迟非常明显。这种问题靠调参数救不了，只能从业务侧拆分事务，把大批量更新切分成小批量循环提交。
6. 大表困境：归档、分区与分库分表的边界判断
6.1 数据量大了之后，硬件优化也会失灵
单个MySQL实例的处理能力是有限的。当单表数据量从百万增长到千万甚至上亿级别时，即使索引设计得很合理，查询性能也会出现不可忽视的退化。原因很简单：索引层级增加、缓冲池无法容纳全部热点数据、磁盘IO访问变多、统计信息更新导致执行计划不稳定。
我最先推荐的方案不是分库分表，而是数据归档。很多业务表里存了大量历史数据，这些数据在业务上已经没有访问价值，只是法律或统计上需要保留。把它们从主表中迁走，往往能把表的体积降一半甚至更多，查询性能立刻回升。
6.2 归档方案的实操思路
归档不复杂，关键在于设计好归档的周期和迁移的策略。我常用的方式是定期任务扫描一张表，按时间字段分批取出历史数据写入归档表，然后从主表删除已迁移的部分。
-- 分批迁移示例 DELETE FROM order_detail WHERE create_time < '2023-01-01' AND id IN ( SELECT id FROM ( SELECT id FROM order_detail WHERE create_time < '2023-01-01' LIMIT 5000 ) tmp );这里要注意，删除大量数据时一定要分批加LIMIT，一次删除几十万行会触发大事务，不仅阻塞其他写操作，还会带来严重的主从延迟。每次删除的批次大小可以根据实际负载调整，我一般控制在5000到10000行左右，休眠几百毫秒再进行下一批。
归档类表建议和主表建立在同一个实例里但不同的库中，这样后续如果需要做跨表统计，还可以用union或者应用层合并。如果归档数据量特别大，也可以考虑直接导出到其他存储系统，这里不展开，但原则是一样的：把主表瘦身。
6.3 分区表的使用边界
分区表在处理特定场景时确实有用，比如按时间范围分区，查询条件如果带上分区键，优化器可以直接裁剪到对应分区，扫描数据量大幅减少。但分区表不是什么银弹，我见过太多人把分区表当成了分库分表的廉价替代品，结果踩了坑。
一个坑是：不是所有查询都能用上分区裁剪，如果查询条件没有包含分区键，MySQL依然要扫描所有分区，性能比普通表还差。另一个坑是：分区数量过多时，表的元数据管理开销很大，包括插入时的分区路由、全局统计信息收集等。建议分区数量控制在几十以内，不要为了"看起来清晰"而拆出几百个分区。
如果一个表已经严重膨胀，但访问模式还是围绕某个主键做点查和有限范围查询，分区的价值并不大，此时归档或分库分表可能更合适。
6.4 分库分表前的最后检查清单
分库分表是成本最高、运维最复杂的方案，很多团队低估了它的代价。一旦做了分库分表，跨库查询、全局唯一ID、分布式事务、数据迁移都会变成长期负担。所以我建议大家在决定分库分表之前，先检查一遍这个清单：
- 是否已经尝试过归档历史数据？
- 是否已经使用覆盖索引消除了大部分回表？
- 是否已经优化过慢查询和热点数据？
- 是否已经评估过读写分离方案能缓解的压力？
- 业务增长的速度是否真的需要分片？
如果以上方案都已经用尽，且业务数据量确实到了千万甚至亿级，那么再考虑分库分表也不迟。分片键的选择特别重要，应该尽量让大部分业务查询能带上这个键。如果业务查询五花八门，没有明确的分片维度，那分库分表就不是一个合适的阶段，反而应该考虑改造成缓存加异步落库的架构。
7. 我踩过的坑和几条保命经验
7.1 坑一：备份恢复时锁表
有一次我在一个相对空闲的实例上手动执行逻辑备份，用的是mysqldump的默认参数。结果备份过程中业务方反馈写入变慢，我查了进程列表才发现备份语句对相关表加了全局读锁，写操作全被阻塞了。
后来我改用--single-transaction参数，利用InnoDB的MVCC机制在备份开始时建立一致性快照，既不锁表，也能保证备份数据的一致性。虽然这个参数下不能同时使用ALTER TABLE等DDL，但对大多数业务场景来说，安全性反而更高。这个坑提醒我：任何涉及锁的操作，上线前都要思考它对线上写请求的影响。
7.2 坑二：字符集不一致导致索引失效
还有一个容易被忽视的问题：两张表关联查询时，如果关联字段的字符集或排序规则不一致，MySQL就无法直接使用索引，只能做额外的转换操作。比如一张表的user_id是utf8mb4，另一张表却是utf8，执行计划里关联字段的type会骤降，甚至变成全表扫描。
我的排查思路是：先查看关联字段的CHARACTER SET和COLLATE，确保两边一致；如果确实不一致，就通过ALTER TABLE统一。这个过程同样要注意锁表和耗时，建议在低峰期分批执行。
7.3 坑三：大事务引发主从延迟
前面提到过大事务对主从延迟的影响，这里详细说一个案例。某个业务同学写了一个定时任务，每天凌晨批量更新所有用户的状态字段，单条更新语句没有加LIMIT，直接更新全表。执行期间从库的Seconds_Behind_Master数值飙升，甚至导致读库上的报表服务查询超时。
解决思路分两步：第一步，把全量大更新改成循环分批提交，一次只更新几千行，每批之间留出间歇；第二步，在监控上增加主从延迟告警，一旦延迟超过阈值就通知值班人员。很多主从问题不是一夜之间出现的，而是小问题长期积累，等到量变引起质变才暴露。
7.4 几条保命经验
最后分享几条我用真金白银换来的经验：
第一，任何一次ALTER TABLE操作，即使只是加一个索引，都有可能因为表体积过大而长时间锁表。线上大表加索引尽量用pt-online-schema-change这类工具，在后台以增量方式完成，减少阻塞时间。
第二，开启MySQL的审计日志或者操作记录，哪怕是简单的schema变更记录，都对事后追溯有巨大帮助。很多线上事故查不到原因，就是因为没有任何历史变更记录。
第三，核心实例一定提前做好容量规划和压测。我发现很多团队只在业务上线前做过功能测试，完全没做过数据量增长后的压测，等到线上数据涨上来之后，各种问题集中爆发，那时候再优化成本就高了。
第四，监控指标不要只看CPU和内存。真正有效的MySQL监控应该包含慢查询数量、锁等待时间、临时表数量、buffer pool命中率、主从延迟、连接数占用率这些更细的指标，否则很多性能劣化问题根本发现不了。
MySQL这台机器，平时不出问题的时候像个老实人，但一旦出了问题，如果排查手段和优化思路不到位，就很容易陷入"重启大法"和"加索引大法"的死循环。我希望这篇总结能帮你少走一点弯路。
