MySQL性能调优实战：慢查询排查、索引设计与架构拆分全攻略

我刚接手一个内部系统的时候线上库表才几百万行随便一个列表页查询就要三五秒一到业务高峰CPU直接飙到 90% 以上。后来花了大概两个礼拜把慢查询一条条抠出来从索引、SQL 写法一路调到架构层面硬是把核心接口的响应时间压到了百毫秒级别。这篇文章就是我当时整套排查思路和调优手段的复盘适合正在被 MySQL 性能问题折磨的开发、DBA或者想系统梳理数据库调优知识的人。内容不绕弯子直接从排查方法、索引优化、事务与锁、再到架构拆分一层层往下讲每一步都有能直接拿去用的实操命令和判断标准。1. 性能问题从哪来先定位再动手1.1 我的慢SQL排查三板斧很多人一遇到数据库慢第一反应就是“加索引”“换机器”这其实是最容易翻车的方式。我自己的习惯是先回答三个问题哪些 SQL 慢慢在哪一步占了多少资源没有这三条信息任何优化都是猜。第一步是打开慢查询日志。很多生产库默认是关的我会用下面的方式临时开启并设置阈值注意不要直接改配置文件避免重启实例SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1; SET GLOBAL log_queries_not_using_indexes ON;阈值设为 1 秒是为了把这些“将来会出问题”的查询也抓进来。不过 log_queries_not_using_indexes 这个开关在生产环境要谨慎全表扫描一旦多了日志量会非常大磁盘撑不住。我一般只在内网压测环境开它。有了慢日志之后第二板斧是分析日志内容。直接用文本编辑器翻慢日志很不现实我习惯用 Percona Toolkit 里的 pt-query-digest 做聚合分析它会按累计执行时间、执行次数、平均耗时排出 Top SQL一眼就能看出哪些查询才是真正该优先处理的pt-query-digest /opt/mysql/log/slow.log slow_report.txt这里有个容易被忽略的点累计执行时间比单次平均时间更重要。一条查询一次只要 200ms但每秒被调用上千次它的资源消耗远远大于一条偶尔执行 10 秒的报表查询。动前者能救整个系统的吞吐量动后者只是让某个报表页面好看一点。第三板斧是看实时线程。慢日志只记录执行完的语句但有些查询卡住不返回慢日志里是看不到的。我会用下面这条命令看当前所有连接在干什么SELECT id, user, host, db, command, time, state, LEFT(info, 100) FROM information_schema.processlist WHERE command Sleep ORDER BY time DESC;重点关注 state 字段。如果大量连接卡在 Sending data通常意味着磁盘或内存排序压力大如果卡在 Waiting for table lock多半是 DDL 或表级锁没释放如果是 Waiting for lock to be granted那是行锁/间隙锁在打架。这一步能帮你快速确认问题发生在 SQL 执行阶段还是并发调度阶段。1.2 借助日志和性能视图锁定瓶颈慢日志能告诉我们哪条 SQL 慢但回答不了“它为什么慢”。这时候我会开 performance_schema 里的语句事件采集拿到更细粒度的执行拆解。注意这个开关本身有性能开销线上开启前先确认机器负载可承受。UPDATE performance_schema.setup_consumers SET ENABLED YES WHERE NAME LIKE events_statements%; UPDATE performance_schema.setup_instruments SET ENABLED YES, TIMED YES WHERE NAME LIKE statement/%;接着可以按 wait 事件排序找出真正等在哪类资源上。比如同样一条慢 SQL如果等的全是 innodb_row_lock_current_waits那是行锁竞争问题加索引反而没用如果是磁盘 io 等待要考虑缓冲池命中率或者是否需要把随机 IO 调整为顺序 IO。还有一个非常实用的指标是 InnoDB 的缓冲池命中率。我用一条查询就能看SELECT (1 - (innodb_buffer_pool_reads / NULLIF(innodb_buffer_pool_read_requests, 0))) * 100 AS buffer_hit_rate FROM performance_schema.global_status WHERE VARIABLE_NAME IN (innodb_buffer_pool_reads, innodb_buffer_pool_read_requests);命中率长期低于 95%说明热数据没有完全放进内存此时加索引、改 SQL 都是治标优先扩内存或者重新评估数据访问模式才是治本。我个人有个判断标准小于 90% 属于缓冲池配置严重不足90% 到 95% 之间要结合磁盘读延迟再判断超过 98% 基本不用操心 IO 层面的问题。其实这套排查流程就是一个漏斗慢日志找语句processlist 找堵点performance_schema 找等待根源。顺序不变效率最高。2. 单条SQL的优化索引设计才是重头戏2.1 为什么MySQL选了B树而不是别的结构在动手加索引之前我觉得有必要把 MySQL 索引用 B 树这件事讲透。索引的本质是用额外的存储空间换查询时间而 B 树的关键在于矮胖一个三层高度的 B 树大概能支撑千万级别的数据量每次查询最多三五次磁盘 IO。换成分支更少的二叉树层数要翻很多倍磁盘 IO 次数跟着翻倍换成哈希表等值查询快得离谱但范围查询直接失效。业务系统里 90% 的查询都有范围条件所以 B 树就成了默认选择。具体到 InnoDB主键索引就是聚簇索引表数据本身就挂在 B 树的叶子节点上二级索引的叶子节点存的是主键值所以走二级索引查到一条记录后大概率还要根据主键回表再查一次。理解这一点特别重要因为后面讲覆盖索引、索引下推的时候全都是围绕“少回表”在做文章。我的建议是每个表至少要有主键并且优先用自增或趋势递增的列做主键这能让新记录始终追加到 B 树的最右侧减少页分裂。不要用 UUID 或者随机字符串做主键它们在插入时会随机写不同页页分裂频繁写性能会很差。2.2 联合索引使用要点与几个反直觉的坑业务里最常见的是联合索引。很多人建联合索引就是把所有查询条件列排一排其实顺序非常关键。总原则是把等值条件放前面范围条件放后面然后考虑区分度。因为等值列用完后范围列还能继续走索引反过来如果范围列在前后面的条件基本就走不了索引了。举个实际例子某订单表有查询条件user_id ? AND status ? AND create_time BETWEEN ? AND ?。如果建(user_id, status, create_time)联合索引三个条件都能充分利用索引定位如果建(create_time, user_id, status)BETWEEN 一进来后面的 user_id 和 status 就只能做回表后的过滤索引的威力大打折扣。这里有几个坑我几乎每隔一段时间就会遇到一次一是最左前缀原则不等于从第一列开始。(user_id, status, create_time)这个索引查询条件只有status ? AND create_time BETWEEN ? AND ?因为没带 user_id索引基本用不上。很多人会把最左前缀理解为“查询条件里要有第一个字段”其实准确说法是“要从索引的第一个字段开始连续匹配”。这条特别容易记反。二是不要在索引列上做函数操作或类型转换。WHERE DATE(create_time) 2024-06-01会让索引失效WHERE create_time 2024-06-01 AND create_time 2024-06-02才能正常走索引。字符串列存的是数字查询时不加引号也容易让 MySQL 做隐式转换直接放弃索引。三是过度建索引本身就是灾难。我曾经接手过一个表业务上就十来个字段索引建了七个单条 INSERT 要同时维护七个 B 树写的速度肉眼可见地掉。后来删到三个索引写入耗时降了一半查询基本没受影响。业界比较稳妥的思路是让单表索引数量控制在五六个以内并且要让二级索引有足够区分度区分度太低的列单独建索引往往得不偿失。2.3 explain输出精读一眼看出SQL能不能用索引建好之后怎么验证它生效了答案是 EXPLAIN。我不建议只看一个 key 字段就下结论而是把 type、rows、Extra 三条结合起来判断。常用判断标准如下字段关键值代表含义typeALL全表扫描基本是性能黑洞typeindex扫了整个索引树比ALL好但仍然不理想typeref / eq_ref通过索引等值访问正常typerange索引范围扫描可接受typeconst主键或唯一键等值最优rows大于实际返回行数很多倍过滤性可能不足ExtraUsing filesort排序没走索引ExtraUsing temporary用了临时表大概率有分组或去重问题ExtraUsing index覆盖索引不回表最优之一ExtraUsing index condition触发了索引下推回表前先过滤了一部分我最关注的是 Using filesort 和 Using temporary。这两个一旦出现哪怕 rows 只有几百也意味着 MySQL 要把数据拉到临时区做排序或分组。一次两次无所谓QPS 一高就变成 CPU 和内存的隐形杀手。解决思路通常是把 ORDER BY 的字段融入到联合索引中让排序直接走索引顺序。举个例子分页查询常见写法是ORDER BY create_time LIMIT 10。如果过滤条件只有 user_id建(user_id, create_time)联合索引那么同一用户的记录在索引里天然就是按 create_time 排好序的MySQL 直接顺序取 10 条就行。否则它要把该用户的所有记录都先查出来排完序再丢 10 条性能差距在数据量大时非常明显。另外EXPLAIN 只能看到估算行数不能完全当作真实执行计划。碰到极端慢查询我会再加一个EXPLAIN ANALYZEMySQL 8.0它能把每个执行步骤的实际耗时和循环次数打印出来定位到具体是哪一步占用时间。有一个案例我记得特别清楚一条关联查询在 explain 里显示 rows 只有几百但实际执行要好几秒EXPLAIN ANALYZE 一看某个嵌套循环执行了百万次问题出在关联条件的隐式类型转换上。所以遇到真正棘手的查询不要满足于 explain 的估值去做真实执行计划才是最高效的。3. 数据操作层的优化事务、锁与写入模型3.1 隔离级别选型与长事务的代价单条 SQL 优化完之后下一个瓶颈往往出在事务上。MySQL 的默认隔离级别是 Repeatable Read这个级别下 InnoDB 除了行锁还会加间隙锁目的是防止幻读。对绝大多数业务系统来说真正需要的隔离强度其实是 Read Committed。只要把隔离级别调成 Read Committed间隙锁几乎消失并发插入时的锁冲突能下降一个量级。如果你对事务隔离级别没有概念可以想成几个一起去银行柜台办业务的人Read Uncommitted 相当于一个人还没办完就能看到他单子上的数字Read Committed 是办完提交后才能看到Repeatable Read 是业务开始那一刻就定格了所有人的账户数字Serializable 则是排队取号一个一个来。业务系统选 Read Committed 已经能满足绝大部分账目准确性的需求没必要为了一直不存在的场景付出锁和并发性能的代价。更隐蔽的问题是长事务。很多慢查询并不是因为 SQL 本身复杂而是事务迟迟不提交导致各种锁和 undo 日志越积越多。排查长事务我经常用这张表SELECT trx_id, trx_state, trx_started, trx_mysql_thread_id, TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS running_seconds FROM information_schema.innodb_trx ORDER BY running_seconds DESC;我见过最离谱的一个事务跑了四十分钟原因是业务代码里事务内嵌了远程 HTTP 调用。这种设计不仅把数据库连接占死了还让一批行锁长期不释放后面的请求全部排队。治理长事务没有捷径一是事务里只做数据库操作外部调用挪到事务外二是设置事务超时三是代码审查时重点盯事务边界。3.2 死锁处理先定位等待环再调整加锁顺序死锁是数据操作层最让人头疼的问题之一。虽然 MySQL 会自动检测死锁并回滚代价较小的事务但这只是事后补救线上频繁死锁会直接影响业务成功率。我处理死锁的思路是这样的先打开死锁日志InnoDB 默认会往错误日志里写最近一次死锁的详细信息。如果没有开启可以执行SHOW ENGINE INNODB STATUS\G查看最近一次死锁等待环。日志里会列出两个事务各自持有什么锁、等待什么锁仔细读一遍基本就能还原出冲突链条。大部分死锁的解决方法都可以归结为统一加锁顺序。举个例子转账操作里同时更新账户 A 和账户 B 的余额如果代码里先锁 A 再锁 B而另一条反向转账是先锁 B 再锁 A就很容易形成环。改成按账户 ID 排序后再逐个加锁就能破坏等待环。另外还有一个很多人忽略的细节减少大范围扫描产生过多行锁。UPDATE 语句的 WHERE 条件如果无法走索引InnoDB 会锁住全表的记录这时候并发事务在另一端等待即使没有逻辑上的死锁也会因为锁范围过大导致等待超时。给 UPDATE 和 DELETE 的 WHERE 条件匹配到索引既能提升执行速度也是在缩小锁的覆盖面。3.3 大事务拆分与批量写入的几个细节业务高峰写入慢很多时候不是磁盘不行而是事务太大。比如后台一次性批量导入十万条历史数据一个事务全包了不仅锁定的行多binlog 也会产生巨大的单条事务记录主从同步的压力非常大。我会把这么大的批处理拆成每 500 条或 1000 条一个批次提交后短暂休眠几毫秒让 InnoDB 的刷盘和主从的 IO 线程喘口气。在代码层面批量插入我通常用一条 INSERT 语句拼多组 VALUES而不是循环单条插入。拼接时要注意 SQL 长度限制避免超过 max_allowed_packet。同时建议用事务封装但每一批保持独立提交。这样即使中途失败也只需要重跑失败的批次不需要全部回滚。还有一个小技巧批量导入时临时关闭非唯一二级索引的唯一性检查或者先 drop 掉非核心索引导入结束再批量重建。这个操作的原理是让写入阶段只维护聚簇索引减少每行插入时要更新多个索引树的开销。重建索引的开销虽然存在但往往比逐行维护快得多。不过这个技巧只适合离线导入场景在线系统千万别这么干因为期间查询会失去索引支持。4. 架构调优从单机到分布式的关键决策4.1 读写分离的落地细节与真正瓶颈单库实例压到极限之后大多数人第一个想到的就是读写分离。思路本身没错但落地有不少细节容易踩坑。读写分离适合读多写少的业务形态核心思路是把只读流量打到从库上主库专心跳写入。我记得有个项目读占了 95%主库负载降不下来把几个大查询切到从库之后主库 CPU 立刻从 80% 掉到 30%。但这里有个他们一开始没想到的问题从库的数据延迟。业务方没留意到某些接口刚写完就立刻去读结果读到旧数据产生了好几起线上投诉。后来在代码里把“写完必读”的请求强制路由到主库其他查询再走从库这个问题才算解决。从库除了延迟还要关注自身的容量和索引是否复制了主库的索引结构。我见过一些团队只在主库建索引从库因为数据量不一样就漏建导致从库查询全表扫描CPU 被打满。另外从库是并行复制还是串行复制、从库硬件是否比主库低配这些都会影响整体表现。不要想当然地认为从库只是备份查询它的压力往往比主库更大。4.2 分库分表之前先想清楚三件事如果说读写分离是横向扩展的第一步分库分表就是更重的一步棋。我的立场是分库分表一定要有充分的理由和明确的边界条件。先想清楚三件事再动手。第一数据量和写入量是不是真的到了必须要拆的程度。单表千万行、索引合理的情况下很多查询依然可以跑得很好。我曾经帮一个系统排查也是千万量级最后发现纯粹是索引和 SQL 写法的问题根本不需要分表。为了未发生的膨胀提前拆表会带来取模路由、跨库 join、分布式事务等一系列连锁成本。第二拆分键到底怎么选。这是分库分表方案里最关键的决定。比如订单表按用户 ID 分那么按订单号查询时就必须先根据用户 ID 取模找到分片否则就要在所有分片上各查一遍。如果业务对订单号查询有刚性需求就得再维护一个路由关系表或者引入文档型存储做反向索引。选拆分键的唯一标准是核心业务查询能不能通过拆分键直接路由到目标分片。第三跨分片操作怎么降级。分库分表之后跨分片 join、全分片 count、批量 IN 查询都会变得尴尬。我见过一个团队为了支持运营后台的任意条件筛选做了个聚合查询层每次请求都发到所有分片耗时和资源消耗都不可控。后来给运营后台单独同步了一份宽表到分析库才把这个问题缓解。所以做分库分表之前必须把查询场景分类哪些是核心链路哪些是后台低频分析两者处理方式完全不同。4.3 缓存层引入的边界与一致性的妥协架构调优绕不开缓存但对缓存的态度应该非常谨慎。缓存不是银弹它解决的是热点读问题是为数据库挡流量的。如果一个查询 80% 的流量都集中在少数几条数据上缓存能带来数量级的收益如果查询条件千变万化命中率不到 20%缓存只会增加系统复杂度还带来一致性问题。引入缓存后大多数团队真正头疼的是缓存与数据库的一致性问题。业界没有完美的方案只有根据业务容忍度做的妥协。我常用的做法是 Cache Aside 模式读时先查缓存未命中则查库并回填写时先更新数据库再删除缓存。删除而不是更新缓存是因为缓存里的老数据可能被多个并发线程以不同的中间状态写坏删掉之后下次读时再填充反而更简单。删缓存本身也有坑。数据库更新成功、缓存删除失败的场景很常见我会用一个本地消息表记录待删除的缓存 key由后台任务重试删除保证最终一致。如果业务对一致性要求更高还得把写操作和删除操作放到同一个事务里但这样也会让写链路变长需要权衡。总之缓存的价值体现要量化上线前后对比响应时间、DB 负载、缓存命中率如果指标没有实质改善趁早下线别留着一个给你添堵的复杂度。5. 常见问题与排查技巧实录5.1 我遇到过的几个典型性能事故故障一某报表 SQL 在凌晨批量跑数把主库打挂。现象是所有业务请求变慢连接数飙升。排查后发现是报表任务直接查了主库一个大查询吃光了 CPU。解决方案并不复杂给报表任务单独分配一个只读账号强制指定从库或独立分析实例同时给大查询加执行超时。事后我把所有非核心异步任务都做了实例隔离这个教训的代价是十分钟的业务不可用。故障二某活动上线后出现大量锁等待。现象是插入操作全部卡住SHOW ENGINE INNODB STATUS显示大量 next-key lock。原因是一条按非索引条件更新的语句把某范围数据全部锁住了恰好该范围又是其他高频事务的活跃区域。处理方式是给更新条件补上了正确的联合索引让锁范围从全表缩小到单行。故障三慢日志持续出现某条查询改了索引也无效。后来用 EXPLAIN ANALYZE 发现卡点不在相关字段而是回表太多。二级索引命中大量主键每次回表都触发随机磁盘读。解决方法是把查询需要的列直接塞进二级索引做成覆盖索引彻底消灭回表。这三个案例有个共性最终解法都不是“加内存换机器”而是回到执行计划和锁范围的分析。硬件升级只是时间换空间甚至会把真正的问题掩盖更久。5.2 运维层面的日常体检清单最后分享一份我自己定期执行的 MySQL 体检脚本。它不复杂但覆盖了我觉得最值得看的维度-- 1. 当前连接数 vs 最大连接数 SHOW STATUS LIKE Threads_connected; SHOW VARIABLES LIKE max_connections; -- 2. 慢查询数量变化 SHOW GLOBAL STATUS LIKE Slow_queries; -- 3. 长事务大于120秒就要警惕 SELECT trx_id, trx_started, trx_mysql_thread_id FROM information_schema.innodb_trx WHERE TIMESTAMPDIFF(SECOND, trx_started, NOW()) 120; -- 4. 未使用索引的查询次数 SHOW GLOBAL STATUS LIKE Handler_read_rnd%; -- 5. 最近一分钟的锁等待情况 SHOW GLOBAL STATUS LIKE Innodb_row_lock_current_waits; SHOW GLOBAL STATUS LIKE Innodb_row_lock_waits;这些指标我会配合监控告警一起看。标准就是连接数峰值不超过 max_connections 的 80%慢查询数量持续增长就必须排查新上线功能长事务一旦出现立刻通知到负责人。平时再忙每周花十分钟跑一遍很多性能隐患都能在变成事故之前被发现。在我的实操经验里MySQL 调优从来不是某一招的比拼。慢 SQL 定位是起点索引优化是基本功事务和锁是并发场景的分水岭架构拆分是最后的扩容手段。这四层是一层一层往上走的每一次升级都要确认下层已经没问题。遇到性能问题不要急着堆硬件把执行计划、锁等待、缓冲池命中率这些关键指标拿出来对比再决定下一步往哪走。等你养成了这种排查习惯很多看似玄学的问题最后都会落到几条简单的记录和指标上。