上周帮某商城系统排查一个查询接口超时的问题。页面上的订单列表转圈转到第 15 秒才出数据DBA 拿到慢日志一看一条 SQL 跑了 3.7 秒而且随着订单量增长这个数字只会越来越难看。这不是个别现象很多团队在业务初期对数据库的访问方式都很豪放等到数据量上来SQL 性能就成了最先暴露的短板。SQL 优化这件事说难不难说简单也绝对不简单。很多时候卡住大家的不是语法而是不知道优化器到底在想什么不知道索引到底有没有被用上不知道一条 SQL 从 2 秒到 20 毫秒之间到底差了哪几步。这篇文章就围绕我这次真实排查的顺序把索引策略、SQL 改写、执行计划解读、统计信息维护这一整套思路拆开讲。适合手里有线上库、被慢查询困扰过的开发同学也适合刚接触性能调优、想建立系统优化思路的后端工程师。1. 那一次线上接口超时优化前我到底在等什么先说现场。业务方的反馈是订单列表接口越来越慢我接到的慢日志记录大致长这样SELECT * FROM order_info o LEFT JOIN order_item i ON o.order_no i.order_no LEFT JOIN product p ON i.product_id p.id WHERE o.buyer_id 2756021 AND o.order_status IN (1, 2, 5) AND o.create_time 2025-01-01 00:00:00 ORDER BY o.create_time DESC LIMIT 20;这条 SQL 一眼看过去条件有 buyer_id、order_status、create_time还带着排序和分页关联了两张表。表面上看该有的条件都有了怎么会慢我习惯性先看执行计划结果问题立刻暴露出来左连接拿 order_item 和 product 时驱动表 order_info 全表扫描rows 估算十几万行然后对每一条都去关联另外两张表。也就是说这条 SQL 虽然在 WHERE 里写了 buyer_id但没有索引支撑优化器只能老老实实从全表里捞。这种写法在订单量只有几万条的时候完全没问题MySQL 可能几十毫秒就给你返回了。但订单量一旦到几十万上百万全表扫描的代价就被放大。你等的不是 SQL 在算你等的是它在硬盘上一条一条翻数据。这类问题的根源往往可以归纳为三点索引缺失或索引设计不合理条件列压根没有可用的索引路径即便有索引SQL 写法导致索引失效优化器有索引也没法用关联表之间的连接字段、排序字段没有配套索引排序和关联成为性能瓶颈。所以做 SQL 优化第一步永远不是上来就改 SQL而是要先把这条 SQL 怎么执行这件事看懂。执行计划里藏着优化器做过的所有决策它是我们判断问题的第一现场。2. 索引策略先行从单列索引到组合索引的设计逻辑2.1 为什么单列索引救不了这张表针对第一条 WHERE 条件常规做法是先加单列索引。有人可能会说buyer_id 不是很好的过滤字段吗每个买家订单不会太多加个索引让优化器直接按买家过滤不就行了我照着这个思路先试了一下ALTER TABLE order_info ADD INDEX idx_buyer_id (buyer_id);执行计划确实从全表扫描变成了索引范围扫描rows 降到几百行看起来有效。但把慢日志里的相关 SQL 都扒拉出来后我发现事情没那么简单。这条订单列表 SQL 还有 order_status 的 IN 条件、create_time 的范围条件以及 ORDER BY create_time DESC 的排序要求。单列索引 idx_buyer_id 解决了按买家找订单这一个维度可排序、状态过滤这些仍然要靠回表后二次处理。打个比方你有一本按姓氏编排的通讯录想找姓张并且住在朝阳区并且年龄大于30岁的人。按姓氏索引能找到一堆姓张的但后面两个条件就只能挨个翻页核对效率上不去。正确的做法是设计组合索引让 WHERE 和 ORDER BY 尽可能在同一棵索引树上完成。我最终给 order_info 建立的索引结构如下ALTER TABLE order_info DROP INDEX idx_buyer_id, ADD INDEX idx_buyer_status_time (buyer_id, order_status, create_time);组合索引 (buyer_id, order_status, create_time) 的思考路径是这样的buyer_id 作为等值条件放在最前面能最快圈定某个买家的数据范围order_status 是 IN 条件属于等值匹配的扩展放在第二列可以继续过滤create_time 是范围条件加排序字段放在第三列既能参与范围扫描也能利用索引的有序性避免 filesort。2.2 最左前缀原则组合索引为什么有列顺序组合索引设计里最核心的规则是最左前缀。优化器只能从索引的第一列开始按顺序匹配查询条件。如果查询条件里没有第一列这个索引基本就废了如果跳过中间某列直接用到第三列第三列也无法走索引。举例说明假设存在索引 (a, b, c)查询条件哪些列可用索引说明WHERE a 1 AND b 2 AND c 3a、b、c 全部命中等值条件下最完美的组合WHERE a 1 AND c 3a 命中、c 不命中b 被跳过c 只能作为普通过滤条件WHERE b 2 AND c 3全部不命中缺少最左列 a索引失效WHERE a 1 ORDER BY ca 命中c 可用于排序需要 b 在中间做等值衔接否则排序也无法走索引所以设计组合索引时要把等值条件列放在前面范围条件列放在后面。排序字段如果正好是范围条件之后能走索引排序就尽量走否则 filesort 的代价在数据量大时不容忽视。回到我们的案例idx_buyer_status_time 把 buyer_id等值、order_statusIN 等值群、create_time范围 排序组合起来。执行计划里的 type 从 ALL 变成了 rangeExtra 里不再出现 Using filesort这就是组合索引带来的可见收益。2.3 覆盖索引让查询在索引里原地解决问题说覆盖索引前先看一个很常见的反例。很多人为了优化大表查询习惯性 SELECT *哪怕只需要两三个字段。SELECT * 意味着查询结果需要拿索引定位到主键后再回表读取完整行数据。回表意味着随机 I/O数据量一大性能立刻掉下来。覆盖索引的思路是索引本身包含了这条查询需要的全部列优化器直接扫索引就能返回结果不需要回表。执行计划里 Extra 会显示 Using index这通常意味着这次查询的 I/O 消耗非常低。我处理过的一个真实场景里有一张日志表超过 800 万行业务侧只需要统计某天某类型日志的数量SELECT COUNT(*) FROM access_log WHERE access_date 2025-03-10 AND log_type 1;这张表原来的索引只有主键。后来加了组合索引 (access_date, log_type)COUNT(*) 这个查询直接从索引里统计行数执行耗时从 900 毫秒降到 5 毫秒。为什么因为索引 (access_date, log_type) 本身已经包含 WHERE 条件涉及的所有列优化器扫索引树的叶子节点就行连主键回表都不需要。覆盖索引设计的小技巧凡是高频查询涉及的条件列和返回列尽量塞进同一个索引。但是索引不是越多越好每一列都有维护成本且占用存储空间。常规建议是单个索引列数不超过 5 列区分度高的列优先。3. 一条 SQL 从 2 秒到 20 毫秒的实操记录3.1 索引加完了SQL 还是慢问题可能在写法上加完 idx_buyer_status_time 之后我重新跑了一遍那条订单列表 SQL结果有点出乎意料——耗时只降到了 1.2 秒左右并没有想象中那么快。执行计划显示 type 已经是 range但 Extra 里出现了 Using where; Using filesort。这说明 buyer_id 和 order_status 确实走了索引但 create_time 这个范围条件没有像设计时预想的那样参与索引过滤排序也没有用上索引。问题出在 SQL 的写法上ORDER BY o.create_time DESC 和 WHERE o.create_time 2025-01-01 都引用了 create_time 列。在 MySQL 的优化器逻辑里当一列同时用于范围查找和排序时索引只能服务其中一个目的它在执行计划里会优先保障范围过滤排序就只能 fallback 到 filesort。这种场景常见于列表类页面既有时间范围筛选又要按时间倒序展示。常见的解法有两类方案一去掉时间范围条件让排序完全走索引。如果业务上允许直接以下一次查询的游标时间点作为过滤条件既能走索引排序又能做深分页优化。典型的游标分页写法SELECT * FROM order_info WHERE buyer_id 2756021 AND order_status IN (1, 2, 5) AND create_time 2025-03-10 12:00:00 ORDER BY create_time DESC LIMIT 20;这种写法下create_time 只作为排序键和游标条件优化器可以一路沿着索引倒序扫描不需要额外排序。方案二接受 filesort但把参与排序的数据量降下来。如果必须支持任意时间范围的查询那就让 WHERE 条件尽量把集合缩到足够小。当最终参与排序的行数只有几百行时filesort 的开销完全可以接受。这个思路在 MySQL 8.0 里还有一个变体利用倒序索引 (create_time DESC) 配合其他等值条件能进一步改善排序性能。3.2 深分页问题LIMIT 100000, 20 为什么这么慢订单列表这类分页接口另一个高频性能杀手是深分页。LIMIT 100000, 20 这种写法MySQL 实际要做的是从索引里去数前 100020 条然后丢弃前 100000 条只返回最后 20 条。数过的这些行每一行都要回表取完整数据即使最终只展示 20 条。深分页的优化思路是不让数据库把前面那些无关数据取一遍。业界最常用的手段是延迟关联deferred join先在索引或覆盖索引上快速拿到目标行的主键 ID再回表查完整数据SELECT o.* FROM ( SELECT order_no FROM order_info WHERE buyer_id 2756021 AND order_status IN (1, 2, 5) ORDER BY create_time DESC LIMIT 100000, 20 ) t JOIN order_info o ON o.order_no t.order_no;子查询里只查 order_no 加上 WHERE 和 ORDER BY 涉及的列这一步已经在覆盖索引内完成速度极快。外面再按 ID 关联回原表取完整行20 行的回表消耗远小于 100020 行的回表消耗。我实测过一个线上改造案例同样的深分页 SQL延迟关联之前耗时 480 毫秒改造后降到 38 毫秒。后端分页接口如果发现页数越深响应越慢优先怀疑的就是这个问题。3.3 JOIN 查询的驱动顺序小表驱动大表回到原来的场景那条订单列表 SQL 还关联了 order_item 和 product。即使 order_info 的主过滤走对了索引关联环节也容易出现隐患。JOIN 优化的核心原则是小表驱动大表。MySQL 优化器会基于表行数和过滤条件估算驱动顺序通常选择过滤后行数更少的表作为驱动表被驱动表的关联字段必须有索引否则就会产生每次关联都全表扫描的悲剧。我在这次排查中还发现order_item 表在 order_no 上没有索引。看起来 order_no 是主键或唯一键其实并不是order_item 表的主键是自增 id业务上通过 order_no 关联但没建唯一索引。这意味着 order_info 每匹配到一条订单都要去 order_item 全表搜一次跟这个 order_no 相关的记录。订单越多这个关联成本就越离谱。补上索引后的效果立竿见影ALTER TABLE order_item ADD INDEX idx_order_no (order_no); ALTER TABLE product ADD INDEX idx_id (id);两条索引加完JOIN 环节的查询耗时直接从原来的秒级降到几十毫秒。关联字段必须有索引这句话写进任何团队的 SQL 规范里都不亏。4. 执行计划怎么看EXPLAIN 输出里的关键信号4.1 type 列从 ALL 到 ref/range 的优化路径EXPLAIN 输出里我最先看的是 type 字段。它描述的是 MySQL 如何访问表数据按照性能从好到差大致排列如下type含义出现场景system表中只有一行数据极少数情况const通过主键或唯一索引精确定位到一行等值查询命中唯一索引eq_ref关联查询时被驱动表通过主键或唯一索引匹配JOIN 常见的最优访问路径ref通过普通二级索引等值匹配组合索引的最左列等值查询range索引范围扫描IN、BETWEEN、、 等范围条件index遍历二级索引全树覆盖索引场景但没走条件过滤ALL全表扫描优化器认为扫描全部行比走索引代价更低看到 ALL 基本就先把它当作优化目标。这里提醒一句看到 type 是 ALL 不一定代表这条 SQL 有问题如果表只有几百行全表扫描反而比走索引更快。优化器不是傻子它也会做代价估算。4.2 key_len 的计算组合索引到底用了几列很多人看执行计划只关心 key 字段实际用到的索引名称但真正判断索引利用程度要看 key_len。key_len 表示 MySQL 在索引中使用的字节数通过它可以反推出组合索引实际生效了几列。计算规则与字段类型有关INT 类型4 字节加上 NOT NULL 的情况就是 4 字节允许 NULL 则加 1 字节VARCHAR(n)n 个字符按字符集算字节utf8mb4 下是 4n 字节加上表示长度的 2 字节允许 NULL 再加 1 字节索引列使用了前缀索引则按前缀长度计算。回到之前的组合索引 (buyer_id, order_status, create_time)如果字段类型分别是 INT、TINYINT、DATETIME那么完全命中三列时 key_len 大概是buyer_id4order_status1TINYINTNOT NULLcreate_time5DATETIME 在 MySQL 5.6 是 5 字节允许 NULL 1合计约 10 字节。如果执行计划里 key_len 只显示 4说明优化器只用到了索引第一列后面的列没有实际参与过滤。这是一个很有价值的排查信号。4.3 rows 和 Extra优化器的估算值只能信一半rows 列是优化器估算的扫描行数虽然有参考价值但它是基于统计信息算出来的没 ANALYZE 过表的话可能和真实情况差得很远。我在一次实际排查中遇到过 rows 显示 35 万、实际只命中 200 行的案例原因就是统计信息长期没有更新。Extra 列里的几个高频值要注意区分Using index覆盖索引性能好Using where存储引擎层返回数据后MySQL Server 层再过滤常见于索引无法完全覆盖条件Using filesort需要额外排序不代表真的用了磁盘文件但说明排序没走索引Using temporary使用了临时表多出现在 GROUP BY、DISTINCT、某些 JOIN 场景能避免就避免Using index condition索引下推ICPMySQL 5.6 的特性把部分 WHERE 条件下推到存储引擎层过滤是好事。看到一个执行计划建议按这个顺序快速过一遍type 是否 ALL → key 是否为 null → key_len 是否符合预期 → Extra 有没有 filesort/temporary。四步看下来这条 SQL 的优化空间基本心里有数了。5. 统计信息与优化器选择别让优化器睁眼瞎5.1 统计信息过期导致执行计划走偏MySQL 优化器选择执行计划依赖表的统计信息包括行数、索引基数、列分布等。如果一张表的统计信息长期不更新优化器对行数的估算就会出现严重偏差从而选错索引。我遇到过一个很典型的例子某表 800 万行索引 (status, create_time) 已经建好但执行计划就是不选它偏要全表扫描或走另一个区分度很差的索引。起初以为 SQL 写法有问题折腾半天后来执行了一次 ANALYZE TABLE执行计划马上正常了。这类问题的根源在于MySQL 的索引基数cardinality是通过采样计算出来的不是实时统计。大批量数据写入后索引基数如果没跟上优化器眼里这张索引还是没区分度的小透明。日常运维中建议对频繁大批量变更的表定期执行ANALYZE TABLE order_info;或者把 AUTO_ANALYZE 机制用起来。MySQL 8.0 中 InnoDB 默认在表数据变更超过一定比例时自动触发统计信息更新但显式 ANALYZE 仍然是线上维护的标配操作。5.2 直方图MySQL 8.0 里优化器的视力矫正器遇到列数据分布极不均匀的场景比如 status 字段 90% 都是同一个值普通索引统计信息很难准确表达这种倾斜。MySQL 8.0 引入了直方图Histogram它把列的数据分布用分桶的方式存起来优化器做代价估算时可以更精确地预估某个条件下会扫多少行。对一个区分度不高但分布倾斜明显的列建直方图ANALYZE TABLE order_info UPDATE HISTOGRAM ON order_status WITH 8 BUCKETS;实测一条 SQL 在加直方图前后优化器从以为全表只有 10000 行变成正确估算出只匹配 897 行执行计划从 ALL 切换到 range查询耗时下降明显。不过要注意直方图不是建完就不管了。数据分布变化后需要重新 ANALYZE 更新。它也不能替代索引直方图的作用是让优化器选择索引时更准确而不是加快查询本身。5.3 优化器提示什么时候需要人为干预大多数情况下相信优化器的选择。但确实存在一些极端场景统计信息被更新后优化器仍然执迷不悟这时可以通过优化器提示干预它选索引。SELECT * FROM order_info FORCE INDEX (idx_buyer_status_time) WHERE buyer_id 2756021 AND order_status IN (1, 2, 5) AND create_time 2025-01-01 ORDER BY create_time DESC;FORCE INDEX 属于最后手段。它的问题是一旦索引被改名字、删掉、或者数据分布彻底变化这条 SQL 可能直接崩。我通常只在应急场景用长期方案还是要回到索引设计、统计信息维护这条正道上来。选用 STRAIGHT_JOIN 强制驱动顺序、IGNORE INDEX 屏蔽某个索引道理类似。一切人为干预都要留下可追溯的说明并且要有后续观测计划。6. 索引之外的几招分区表、MRR 与业务降级6.1 分区表适合什么场景当单表数据量到了千万级而且业务查询明显围绕某个范围字段比如时间时分区表可以成为优化手段之一。分区不是把数据放在不同的表里而是把一张表的存储拆成多个独立的区段。比如 order_info 按 create_time 做 RANGE 分区ALTER TABLE order_info PARTITION BY RANGE (TO_DAYS(create_time)) ( PARTITION p2024 VALUES LESS THAN (TO_DAYS(2025-01-01)), PARTITION p2025 VALUES LESS THAN (TO_DAYS(2026-01-01)) );查询条件里如果带 create_time 范围优化器会做分区裁剪partition pruning只扫描需要的分区。这个方案我在某电商历史订单表上测试过全表扫描降为单分区扫描后耗时差不多降了一个量级。但要泼一盆冷水分区表不是万金油。如果查询条件不带分区键优化器还是会把所有分区都扫一遍性能比没分区时可能更差。同时分区表对 DDL 的限制也多比如主键必须包含分区键。使用前务必确认业务查询模式确实围绕分区键展开否则不建议为了看起来能优化而分区。6.2 MRR范围查询的随机 I/O 优化MRRMulti-Range Read是 MySQL 5.6 引入的优化机制解决二级索引范围扫描时频繁回表造成的随机 I/O 问题。优化器会先把命中的主键收集起来排序再按主键有序回表把随机 I/O 转成顺序 I/O。在 MySQL 8.0 里 MRR 默认开启。但有些场景下优化器未必选择 MRR本质原因是它评估 MRR 的收益不明显。当年某报表系统有一条百万行的范围查询 SQL每次要回表 8 万行开启 MRR 后耗时从 1.8 秒降到 0.9 秒。了解 MRR 的意义在于当你的 SQL 因为范围条件导致海量回表时可以试着通过 hint 或调整参数观察是否触发 MRR而不是立刻推翻索引重来。动手之前先执行 EXPLAIN看看 Extra 里是否出现Using MRR这类提示。6.3 业务侧降级查询扛不住时缓存和异步是兜底最后说一个不算数据库优化但线上系统必须认真考虑的方向业务侧降级。即使把所有索引、SQL 写法、统计信息都优化到位核心报表类的复杂查询在高并发下依旧可能成为瓶颈。这种时候数据库层面再怎么调也不如把请求从数据库前面拦住热点查询结果用缓存挡一层设置合理的过期时间比如 5 秒或 60 秒非实时的汇总统计走异步任务或离线数仓不在线上 OLTP 库跑分页接口对页深做限制超过 100 页直接返回空或引导使用其他查询方式大促、高峰期间对报表接口做熔断保障核心交易链路。我见过很多团队把性能问题全部压在 DBA 头上天天调 SQL但流量高峰一来还是被打穿。数据库的确还能再压榨出一点性能可真正的弹性应该设计在更靠近业务入口的位置。优化数据库和优化访问模式从来都是一件事的两面。7. 最后再分享一点排查思路这次订单列表的优化最终把接口 P95 从 4.5 秒降到了 120 毫秒左右手段无非是组合索引、补 JOIN 关联索引、改掉深分页写法外加对统计信息做了一次全面维护。全程没有用到任何黑科技每一步都是顺着执行计划找到的靶子。我自己做 SQL 优化的习惯是拿到一条慢 SQL 后先不急着改按这套顺序走一遍EXPLAIN 看执行计划确认 type、key_len、Extra 三个关键信号检查 WHERE 条件和排序字段能否匹配上组合索引的最左前缀看有没有可能用覆盖索引直接替代回表关联查询检查驱动顺序和连接字段的索引确认统计信息是否需要 ANALYZE 更新最后才考虑改 SQL 结构比如延迟关联、游标分页。给新手的一句建议是不要背索引失效十大场景这种口诀而是理解索引的本质——有序化的数据结构。理解了这一点你就自然会明白为什么 LIKE %xx 走不了索引为什么函数包裹列会失效为什么范围条件后的字段排序走不了索引。把原理消化掉任何变化场景都不会难住你。