MySQL性能调优实战：慢SQL定位、索引优化与参数调整全复盘

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 timestamp1700000000; SELECT ... FROM order_detail WHERE user_id 12345 AND status 1;这里Rows_sent是10Rows_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直接从这个索引上把数据返回。这种索引叫做覆盖索引。在报表类查询、统计类查询里覆盖索引的效果立竿见影。举个例子一条统计SQLSELECT 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 13800138000MySQL会尝试把字符串列转换为数字再比较索引自然就失效了。排查时可以看看执行计划里的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显示typerefkey也正常看起来没有任何问题。我深入查了数据分布发现问题出在索引的区分度上。status字段只有0和1两种取值而且99%的数据都是status1这种情况下走索引和全表扫描的区别很小优化器有时候甚至会在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这台机器平时不出问题的时候像个老实人但一旦出了问题如果排查手段和优化思路不到位就很容易陷入重启大法和加索引大法的死循环。我希望这篇总结能帮你少走一点弯路。