上周夜班接到一条线上告警某接口的 95 分位响应时间从 80ms 直接跳到 1.2s。看了一圈链路缓存命中正常服务端逻辑也没有明显阻塞最后定位到数据库层——一条看起来平平无奇的订单查询 SQL单次执行要 600ms把这个 SQL 拿下来做 MySQL 性能调优时才发现问题根本不是它写法多烂而是它压根没走索引。MySQL 里的 SQL 调优是个老生常谈的话题但真正遇到慢 SQL 时很多人还是习惯性二选一要么使劲加索引要么把 SQL 拆成九九八十一个子查询。这两条路我都走过踩过的坑比写过的代码还多。这篇文章就用我的实际排查经验把 MySQL 中 SQL 调优从定位、分析、改写、参数协同到线上避坑完整捋一遍。不管你是后端开发、专职 DBA还是正在准备 MySQL 面试的候选人照着这个思路走至少能少走一半弯路。1. 先认清目标调优到底在调什么1.1 调优的第一原则是少干活很多朋友拿到慢 SQL 的第一反应是“这 SQL 太复杂了拆开写”。我早先也这样干过把一条四表 JOIN 的查询拆成四条单表查询然后在业务代码里拼装。结果是应用层多了一堆循环数据库的查询总数翻了四倍响应时间从 400ms 涨到 700ms机房运维大哥看我的眼神都不对劲了。MySQL 的 SQL 调优核心目标始终只有一个让数据库用最少的代价拿到需要的数据。代价是什么是扫描的行数、参与排序的数据量、创建的临时表大小、以及锁竞争的时间。SQL 写得好不好最终都落到这几项上。与其迷信“拆 SQL”或者“加缓存”不如先回答一个问题这条 SQL 到底扫描了多少行优化后的目标扫描多少行扫描行数差一个数量级执行时间就差一个数量级这比什么技巧都实在。所以我的调优流程向来是先定位最耗时的 SQL再 EXPLAIN 看它的执行计划和扫描行数然后针对“扫描行数多”或“排序量大”的根因做索引或写法调整最后验证结果建立新的基线。1.2 定位慢 SQL 的三件套慢查询日志、performance_schema、sys 库调优的第一步是找到那条“罪犯 SQL”而不是拿着全量 SQL 列表瞎猜。我常用的定位手段有三个按使用频率排序。慢查询日志是我最先开的东西。默认它是不开的线上实例一般也不建议长时间全量开启但临时开一下做诊断没任何问题。动态开启执行SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 0.1; SET GLOBAL slow_query_log_file /var/log/mysql/slow.log;long_query_time 我习惯临时调成 0.1 秒这样任何超过 100ms 的 SQL 都会被记录下来。平时生产环境设 1 秒甚至 2 秒足够诊断窗口期设 0.1 秒才能把“亚健康”的 SQL 也揪出来。慢日志里每一条记录都包含执行时间、锁等待时间、扫描行数、返回行数这些字段非常有价值。我见过不少人打开慢日志后只看执行时间忽略了 Rows_examined结果漏掉了真正的索引问题。performance_schema 和 sys 库是另一组利器。sys 库只是 performance_schema 的视图封装查起来更友好。我排查问题时经常用这么两条查询# 按平均耗时倒排看当前实例上哪些 SQL 最值得优化 SELECT schema_name, digest_text, count_star, avg_timer_wait/1000000000 AS avg_ms FROM sys.statements_with_avg_timer_wait ORDER BY avg_timer_wait DESC LIMIT 20; # 看哪些 SQL 扫描行数多但返回行数少典型的“吃力不讨好” SELECT schema_name, digest_text, rows_examined, rows_sent, rows_examined/rows_sent AS exam_per_sent FROM sys.statements_with_full_table_scans ORDER BY exam_per_sent DESC LIMIT 20;第一类查询帮我找到“频率高且单次慢”的热点 SQL第二类查询帮我找到“扫描了 100 万行只返回 50 行”的冤大头。这两条综合起来基本能锁定 80% 的调优目标。1.3 建立基线怎么判断 SQL 是真慢还是假慢定位到一条 SQL 之后别急着优化。先问一句它在正常负载下的表现到底是什么我吃过这个亏——有一次“优化”了一条 SQL把执行时间从 200ms 压到 50ms结果一周后业务高峰一到数据库 CPU 反而涨了 20%。原因是之前那条 SQL 虽然单次执行 200ms但只在特定低频接口里出现改成新写法后虽然快了但被业务方拿去复用查询频率直接翻了十倍。所以我现在的习惯是对每条要做优化的 SQL先记录三个数——平均执行时间、扫描行数、以及在当前 QPS 下的资源占比。如果一条 SQL 平均 30ms但每秒要执行 200 次那它就是优化优先级最高的如果一条 SQL 平均耗时 500ms但每天只有几次并且跑在凌晨的批处理任务里那它压根不需要动。调优的价值等于“执行频率 × 单次优化收益”这个公式我建议你贴在工位上。2. 读懂 EXPLAIN调优的基本功2.1 执行计划里的关键字段逐个拆EXPLAIN 是 MySQL 给 SQL 优化器的一份“驾驶舱仪表盘”但很多同学看到一堆字段就晕。我一般只看四个type、key_len、rows、Extra。把这四个字段读明白比背二十个字段都管用。先看 type它表示 MySQL 找到目标行所用的访问方式。从好到差大致是system const eq_ref ref range index ALL。ALL 显然是全表扫描index 也不算好它表示在遍历一颗完整的索引树range 表示只扫描索引的一部分区间常见于 BETWEEN、IN、、 等条件ref 和 eq_ref 是 JOIN 场景里值得追求的级别前者是普通索引等值匹配后者是被驱动表用主键或唯一索引匹配效率极高。我自己的判断线很简单主查询里出现 ALL 或 index先停下来看原因。再看 key_len这个字段很多人只扫一眼 key 走了哪个索引其实 key_len 才代表着索引真正被用到的字节数。同一个复合索引 idx_a_b(a,b)在某些 SQL 里 key_len 可能只包含 a 列的字节这说明 b 列没有参与到索引过滤中。比如一个 VARCHAR(50) 的字段utf8mb4 字符集下每个字符占 4 字节如果 key_len 是 202说明这个字段的完整 50 字符都被用上了如果只有 6那说明 SQL 里肯定用了别的更短的列。rows 是优化器估算的需要扫描的行数这个数字不是精确值但数量级基本可信。我优化前后对比时最关心的就是这个数字有没有降一个数量级。Extra 里的内容更精彩常见的有 Using where、Using index、Using temporary、Using filesort。其中 Using filesort 意味着 MySQL 不得不额外在内存或磁盘上做排序这是性能杀手后面专门拿一节来说。2.2 索引为什么快以及索引失效的四个常见场景索引快的原因本质上是 B 树把“顺序查找 O(n)”变成了“树查找 O(log n)”并且在非叶子节点上只存键值不存数据一个 16KB 的页能塞下很多条索引记录三层树就能支撑上千万行数据的快速定位。这个原理大家都知道但实践里真正的问题往往是索引明明建了SQL 却用不上。我把索引失效的场景总结为四个踩过其中任何一条的可以举手**第一对索引列做了函数运算或表达式处理。**比如WHERE DATE(create_time) 2024-01-01只要 create_time 上有索引这个函数就会让优化器放弃索引因为函数的计算结果无法直接与 B 树里的键值比对。正确写法是写成范围条件WHERE create_time 2024-01-01 AND create_time 2024-01-02。**第二隐式类型转换。**如果 user_id 在表里是 VARCHAR 类型SQL 里却写WHERE user_id 123456MySQL 会把字符串列转换成数字再比较索引照样失效。排查方法很简单看表结构字段类型再对比 SQL 里的字面量类型不一致就改 SQL。我见过因为手机号字段是 varchar前端传了个整型导致全表扫描的真实事故。**第三复合索引不满足最左前缀原则。**索引 (a, b, c) 可以加速 a、ab、abc 这三种查询条件但直接用 b 条件或者 c 条件索引就只能成为摆设。最左前缀原则不是八股文它是 B 树里索引键按顺序排列的天然结果。第四LIKE 通配符开头。WHERE name LIKE %字符串%无法利用索引因为 B 树只支持前缀匹配字符串%这种前缀匹配才可以走索引。业务上非要模糊匹配老老实实考虑全文索引或者搜索引擎别在 MySQL 里硬扛。2.3 覆盖索引让索引直接出结果不回表覆盖索引是 SQL 调优里性价比最高的一招它指查询所需的全部列都包含在索引树里MySQL 可以直接遍历索引返回结果连回表查数据页这步都省了。用大白话讲B 树索引像一个“集合作业本”如果这个本子上已经把你要的名字和成绩都记了那就不需要再去翻每个人的原始档案了。我举个例子。订单表很大有一条统计 SQL 是查某天某个渠道的订单数SELECT COUNT(*) FROM orders WHERE channel_id 10 AND create_time 2024-06-01 AND create_time 2024-06-02;如果没有合适的索引MySQL 需要扫整个表。如果建立一个复合索引 (channel_id, create_time)这个 COUNT(*) 所需的 channel_id 和 create_time 全在索引里优化器可以直接扫索引树统计数量扫描的数据量从几百万行降到几千行。执行计划里 Extra 字段会出现 Using index说明覆盖索引生效了。设计覆盖索引时要考虑“过滤条件列在前查询列补充到尾部”。比如查询经常带上 user_id 和 status且需要返回 amount就可以考虑建 (user_id, status, amount) 这样的索引让查询一路走到索引叶子节点就把数据拿全。当然索引不是越多越好每多一个索引写操作和存储成本都会增加这个账要算清楚。3. 实战拆解一条慢 SQL 的完整优化过程3.1 业务场景与建表语句光讲理论不过瘾拿一条真实线上 SQL 完整走一遍调优流程。场景是一个商品订单查询接口前端需要展示“店铺的已发货订单列表按下单时间倒序分页”同时支持按订单状态筛选。核心表 orders 的结构如下CREATE TABLE orders ( id bigint(20) NOT NULL AUTO_INCREMENT, order_no varchar(32) NOT NULL COMMENT 订单号, shop_id bigint(20) NOT NULL COMMENT 店铺ID, user_id bigint(20) NOT NULL COMMENT 用户ID, status tinyint(4) NOT NULL COMMENT 订单状态, total_amount decimal(10,2) DEFAULT 0.00, create_time datetime NOT NULL COMMENT 下单时间, PRIMARY KEY (id), KEY idx_shop_id (shop_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;表里有大约 500 万行数据接口每页显示 20 条对应的 SQL 是这样的SELECT id, order_no, total_amount, status, create_time FROM orders WHERE shop_id 1024 AND status 2 ORDER BY create_time DESC LIMIT 20 OFFSET 0;这个查询单次执行实测下来 900ms完全不可接受。让我来拆这起“案件”。3.2 优化前执行计划说明了什么拿到 SQL 先别猜直接 EXPLAINEXPLAIN SELECT id, order_no, total_amount, status, create_time FROM orders WHERE shop_id 1024 AND status 2 ORDER BY create_time DESC LIMIT 20 OFFSET 0;关键输出如下id: 1 select_type: SIMPLE table: orders type: ref possible_keys: idx_shop_id key: idx_shop_id key_len: 8 rows: 125000 Extra: Using where; Using filesort逐项分析type 是 ref说明 shop_id 等值匹配走得不错但因为 idx_shop_id 只建立在 shop_id 一个列上等值匹配之后还要回表去过滤 status 字段优化器估算还要扫 12.5 万行。更致命的是 Extra 里的 Using filesort——因为 ORDER BY create_time 没有符合任何索引顺序MySQL 必须在内存或者磁盘上对这 12.5 万行做一次完整排序然后再取 20 行。这其实就是典型的“数据库帮你干了很多活但大部分活都是白干的”。我常说这类 SQL 的时间大头不是查数据而是排序。12.5 万行的排序加上磁盘临时表可能参与其中耗时能不高吗3.3 优化方案先改索引再考虑改 SQL我首选的方案不是改写 SQL而是补一个复合索引。为什么因为这条 SQL 的 WHERE 条件是 shop_id status 两个等值条件ORDER BY 是 create_time查询列是 id、order_no、total_amount、status、create_time。从 B 树索引的组织方式看一个设计得当的复合索引可以让这三个部分都“顺路”。建索引ALTER TABLE orders ADD INDEX idx_shop_status_time (shop_id, status, create_time);这个索引设计有三个细节值得展开说。第一等值条件在前。shop_id 和 status 都是等值过滤它们放在索引最左边没有任何争议等值条件谁先谁后影响不大我习惯把区分度更高的放前面。第二排序字段紧跟等值条件。create_time 放在第三个位置这样满足 shop_id1024 AND status2 的索引记录天然就是按 create_time 排序的Using filesort 直接消失。第三如果查询只需要返回 id、status、create_time 这几列那索引本身可以覆盖查询连回表都省了但这条 SQL 还要 select order_no 和 total_amount所以覆盖不了只能做到避免排序回表是无法避免的。SQL 本身我暂时不改。建完索引再 EXPLAINtype: ref possible_keys: idx_shop_id, idx_shop_status_time key: idx_shop_status_time key_len: 9 rows: 3200 Extra: Using where对比一下rows 从 12.5 万降到了 3200Extra 里的 Using filesort 消失了。这就是一个数量级的进步。3.4 优化后效果与对比复盘实跑一遍优化前平均 920ms优化后平均 48ms差不多提速 19 倍。这里我需要泼一盆冷水98% 提升听起来开心但不要高兴得太早。为什么这个优化方案还有后遗症。idx_shop_status_time 这个复合索引把 create_time 作为排序键导致 insert 和 update 时的索引维护成本上升且占用额外存储空间。在大多数业务场景里这点代价可以接受但你必须清楚这笔账。另一个隐患是分页问题如果用户看完第 1 页、第 2 页、第 3 页……一直往后翻OFFSET 会越来越大到第 10000 页时 OFFSET 就接近 20 万MySQL 仍然要把前 20 万行扫完再丢弃这就是深分页优化要解决的问题下一节详细展开。这个案例还可以提炼一个方法论优先看 Extra再看 rows最后才看 SQL 写法。Extra 里的 Using filesort、Using temporary 是明确信号rows 是数据量大小的指示器。SQL 写法很多时候是背锅的——真正的问题是索引设计不匹配。4. 进阶与踩坑深分页、排序、并发场景的调优实战4.1 深分页之痛OFFSET 越大越慢的本质上面那条订单查询用户不可能只看前几页一翻就到很后面。深分页的典型 SQL 长这样SELECT id, order_no, total_amount, status, create_time FROM orders WHERE shop_id 1024 AND status 2 ORDER BY create_time DESC LIMIT 20 OFFSET 400000;即使索引建得漂亮这个 OFFSET 400000 也会让 MySQL 扫描并丢弃前 40 万条索引记录然后才取 20 条返回。时间全浪费在“拿到又不要”上。我排查过的几个线上慢 SQL根因都是 OFFSET 太大。解法有两个看业务场景选。**方案一延迟关联。**核心思路是先用覆盖索引快速定位主键再用主键回表取完整数据。改写后SELECT o.id, o.order_no, o.total_amount, o.status, o.create_time FROM ( SELECT id FROM orders WHERE shop_id 1024 AND status 2 ORDER BY create_time DESC LIMIT 20 OFFSET 400000 ) t JOIN orders o ON o.id t.id ORDER BY o.create_time DESC;子查询里走 idx_shop_status_time 索引同时查询列只有 id索引完全覆盖Using indexMySQL 只需要在索引树上完成排序和分页虽然还是扫了 40 万条索引记录但不需要回表每一条回表都是一次随机 IO省掉大量随机 IO 后耗时能从 900ms 降到 100ms 出头。这个方案对被分页的列表查询非常管用。**方案二游标 / 键集分页。**如果业务能接受“加载更多”而非“跳页”就用 WHERE create_time 上一页最后一条记录的时间来取数据。比如上一页最后一条 create_time 是 2024-06-01 14:30:00下一页就查SELECT id, order_no, total_amount, status, create_time FROM orders WHERE shop_id 1024 AND status 2 AND create_time 2024-06-01 14:30:00 ORDER BY create_time DESC LIMIT 20;这个写法在数据量再大也稳定因为扫描行数始终受 LIMIT 和 WHERE 限制不会越翻越慢。代价是业务逻辑要改成“下一页”而不是“第 N 页”很多产品经理不一定接受要根据实际情况权衡。4.2 Using filesort 的应对让排序走上索引Filesort 听着像磁盘排序其实 MySQL 5.7 之后大部分排序在 sort_buffer 内存里完成超过 sort_buffer_size 才会落盘。不管在哪它都要把结果集完整排一遍O(n log n) 的时间逃不掉。上一节的复合索引方案已经演示了怎么让 ORDER BY 字段走在索引顺序上这里补充一个容易被忽视的场景ORDER BY 两个字段方向必须一致。复合索引的存储顺序是严格的升序如果 ORDER BY 是 create_time DESC, id ASC索引是 create_time ASC, id ASC优化器就没法按索引顺序直接出结果只能 filesort。如果业务上一定要一升一降常规做法是保留一个字段的排序另一个字段的重量用代码排。还有一类排序慢是 JOIN 导致的。A JOIN B 之后再 ORDER BY B.idMySQL 可能为了排序把结果集先放进临时表。遇到这种我优先看能不能调整驱动顺序或者把 ORDER BY 字段也纳入 JOIN 的索引范围让被驱动表通过索引顺序直接产出。4.3 并发连接池场景下的 SQL 调优慢 SQL 还叠加排队问题SQL 调优不能只看单条执行时间放到高并发场景里慢 SQL 的杀伤力会被放大。数据库连接池的线程是有限的如果 10 个连接里 8 个都在执行一条耗时 1 秒的慢 SQL另外 20 个正常请求就全部排队。你通过 MySQL 的 processlist 看可能会看到一堆“Waiting for table level lock”或者“Sleep”真实原因却是慢查询占满了连接。所以并发场景下的调优顺序我建议是先解决慢 SQL 的单条性能再看连接池大小与 max_connections 的匹配度。连接池上限设得再大如果数据库侧 max_connections 是 200应用侧 300 个线程同时请求多出来的 100 个就会在应用侧堆积。连接池大小和数据库 max_connections 之间要留出 buffer我一般习惯给 DB 侧的连接数留出 20%-30% 余量防止慢 SQL 突发时连接被打满。另外升级版的慢查询日志 sys 库可以帮你看到“排队最严重的 SQL”是不是同一个 digest。我有一次线上故障最终定位到罪魁祸首不是最慢的 SQL而是每分钟调用 3 万次、单次只要 20ms 的“高频小查询”——它本身不慢但把连接池线程全占满了导致后面的真慢查询连执行机会都没有。这也是为什么要反复强调“执行频率 × 单次耗时”而不是只看单条执行时间。4.4 常见问题速查表我把这几年线上排查经常遇到的 SQL 问题整理成一个速查表方便看文章的朋友直接对号入座。症状可能原因优先处理方向EXPLAIN 显示 ALL条件列无索引或索引失效建合适索引检查函数运算、隐式类型转换rows 偏大但 type 是 index用了前缀索引却未能走完整范围调复合索引列顺序用覆盖索引Extra 出现 Using filesort排序字段不在索引顺序里调整索引顺序或改写排序逻辑LIMIT 深分页很慢OFFSET 太大导致大量丢弃延迟关联或游标分页高频小 SQL 打满连接池单条不慢但调用频率极高前端限流或合并查询JOIN 慢且被驱动表全表扫被驱动表关联列无索引为 JOIN 关联列建索引驱动表取小结果集这张表不能解决所有问题但能让你在第一眼看到慢日志时有个切入方向。5. 参数调优协同作战别忘了和 SQL 调优打配合5.1 参数调优三件套先别碰除非你有基线SQL 调优解决的是“单条 SQL 怎么跑得快”MySQL 参数调优解决的是“整个实例怎么稳定地跑得快”。网上流传的“参数调优三件套”主要指innodb_buffer_pool_size、sort_buffer_size、max_connections 这三项再加上一个慢查询相关配置。很多新手上线就照着网上的“推荐值”一顿改这是大忌。我先说说三个参数的真正含义再给合理的调整方法。innodb_buffer_pool_size是 InnoDB 的缓存池大小它决定了很多数据页能常驻内存。如果你的业务是读多写少这个值调整的收益是最大的。常见推荐值是物理内存的 60%-70%但必须在服务器只剩 MySQL 一个主要进程、且没有其他大量吃内存的应用时才能这么激进。我维护过一台 8G 内存的机器上面跑了 MySQL、Nginx、Java 应用buffer_pool 调到 6G 的结果就是内存被 OOM killer 盯上直接把 MySQL 杀了。所以先量力而行看SHOW VARIABLES LIKE innodb_buffer_pool_size和系统监控的实际内存占用分步调整。sort_buffer_size是每个连接在排序时分配的缓冲区大小每个连接都会有一份它同时变大对内存的消耗是连接数倍的放大。sort_buffer_size 设为 2M 不是问题但如果 200 个连接里有一半在做大排序光排序缓冲就可能吃掉 200M 内存。这个参数我通常不动除非明确看到 MySQL 状态变量Sort_merge_passes很高——那说明排序频繁落盘才值得调大。max_connections控制的是 MySQL 最多接受多少个连接。这个值不宜过小过小会导致应用连接池排队也不宜过大过大只是把压力往后推迟真正连接全部建立后反而把内存和 CPU 打爆。设置依据是实际需要的并发连接数我一般取业务高峰期连接数的 1.5-2 倍并观察 Threads_connected 指标。5.2 慢查询配置与 QPS 基线参数调优的依据参数调优的正确姿势是先有基线再动刀。我把慢查询阈值临时调到 0.2 秒跑一到两天的业务流量收集慢日志把这个时段的高频 SQL 都优化到位后再看 MySQL 的全局状态指标。能确认的参数优化依据包括Threads_connected、Max_used_connections、Sort_merge_passes、Innodb_buffer_pool_reads。Innodb_buffer_pool_reads 很高意味着大量数据要回磁盘读buffer_pool 可能偏小Threads_running 持续高于 CPU 核数说明 SQL 并发度太高可能需要限流或者改索引。参数调整一定要小步走。比如 buffer_pool 从 4G 调到 6G观察三天max_connections 从 200 调到 300观察两天。一次只改一个参数否则出了问题根本分不清是哪一项导致的。5.3 AI 辅助 SQL 调优能用的工具和不能犯的懒现在很多团队在尝试让 AI 辅助做 SQL 调优。我的看法是AI 非常擅长总结 EXPLAIN 输出和翻官方文档它可以帮你快速生成“基础建议版”的索引方案但最终拍板必须是人。原因很简单AI 不知道你的表有多大写入频率有多高不知道哪条业务路径是真正的热点更不知道为了一个排序快一点多建一个索引在写入坏境里会造成什么代价。我见过一些同事让 AI 给 SQL 优化建议AI 直接建议“给 order_no 加唯一的全表索引”。单看这条 SQL 确实可行但 order_no 列上有唯一约束的写入性能、插入时的索引维护成本以及这个索引占用的磁盘空间AI 一概不考虑。所以在调优这件事上把工具当成陪练可以把它当军师就危险了。写在最后我个人的一套完整调优 SOP说了这么多最后分享一套我自己一直在用的完整流程你完全可以照着来。第一步打开慢查询日志设置阈值到 0.2 秒收集当天慢 SQL第二步从 sys 库里跑高频 SQL 排行圈定“频率高 × 单次慢”的优化目标第三步用 EXPLAIN 拆解每条 SQL重点看 type、rows、Extra 三个字段定位扫描行为和排序行为第四步根据定位结果设计索引方案复合索引列顺序按“等值条件在前、排序字段次之、查询列补充到尾部”来排能用覆盖索引尽量用第五步看完 SQL 优化效果后回到参数层面检查 buffer_pool、线程和内存有需要再小步调参数并观察。这一套流程走下来80% 的慢 SQL 都能在几天内解决。剩下的 20% 往往不是 SQL 本身的问题而是业务逻辑确实需要那么大的数据量或那么复杂的计算这时候要想的是架构层面的改造比如冗余字段、汇总表、分库分表这些都是另一个话题了。我第一次独立调优线上慢 SQL 时最深的体会是调优不是炫技是让数据库别做无用功。每次看到 Extra 里的 Using filesort 消失、rows 降一个数量级的时候那种踏实感比任何简历上写的“精通 SQL 调优”都值钱。