mysql慢查询优化方案？｜阿里巴巴后端开发面经解析

60 秒回答模板

我会按“定位、分析、优化、验证、治理”的链路回答。首先开启或查看慢查询日志、监控平台、APM 链路和数据库指标，确认慢的是哪条 SQL、调用频率、平均耗时、P95/P99、扫描行数、返回行数、是否伴随 CPU、IO、锁等待或连接数异常。然后用 EXPLAIN 或 EXPLAIN ANALYZE 看执行计划，重点看访问类型、命中索引、扫描行数、过滤比例、是否出现临时表、文件排序、回表、索引失效等问题。优化时优先从索引和 SQL 入手，比如建立合适的联合索引、让条件符合最左前缀、避免函数或隐式转换导致索引失效、减少 SELECT *、拆分复杂查询、优化 JOIN 顺序、避免大偏移分页。若单条 SQL 已经合理，还要看表结构、数据分布、冷热数据、分库分表、缓存、锁竞争和业务调用方式。最后必须用执行计划、压测、慢日志变化和线上指标验证，避免只看单次执行时间就认为优化完成。

考点 先定位慢在哪里

主线 用执行计划判断根因

易错点 只回答“加索引、分库分表、加缓存”，没有说明如何定位根…

深入解析

先定位慢在哪里

慢查询优化的第一步不是直接加索引，而是确定问题边界。需要从慢查询日志、监控平台、APM 链路、数据库连接池指标和业务日志中定位具体 SQL，区分是单条 SQL 本身慢，还是流量突增、连接池耗尽、锁等待、网络抖动或下游服务阻塞导致的表象。关键指标包括执行次数、平均耗时、P95/P99、扫描行数、返回行数、影响行数、临时表数量、排序次数、锁等待时间和错误率。只有知道慢查询出现的时间段、调用入口和数据量背景，后续优化才不会变成盲目调参。

用执行计划判断根因

拿到 SQL 后要看 EXPLAIN 或 EXPLAIN ANALYZE，而不是只凭经验判断。重点关注访问类型是否从 ALL 全表扫描退化，possible_keys 与 key 是否符合预期，rows 是否远大于实际返回行数，filtered 是否过低，Extra 中是否出现 Using temporary、Using filesort、Using join buffer、Using where 等信号。对于 MySQL 8 的场景，EXPLAIN ANALYZE 还能看到实际执行耗时与行数，更适合发现优化器估算偏差。执行计划的目标不是背字段含义，而是回答三个问题：从哪里读、读了多少、为什么读这么多。

索引优化要匹配查询模式

索引优化要围绕真实查询条件、排序和分组设计。常见做法是为高频 WHERE 条件、JOIN 条件、ORDER BY、GROUP BY 建立合适索引，尤其是联合索引要按等值条件、范围条件、排序字段和选择性综合考虑。需要避免在索引列上使用函数、表达式、隐式类型转换、前置通配符模糊匹配等导致索引失效的写法。还要控制索引数量，因为索引会增加写入成本和存储成本。好的索引不是越多越好，而是能让扫描行数大幅下降，并尽量通过覆盖索引减少回表。

SQL 改写降低扫描和排序

如果执行计划显示扫描过多或排序代价过高，就要考虑 SQL 改写。常见方向包括只查询必要字段，减少 SELECT *；把复杂子查询改成 JOIN 或临时结果，前提是执行计划更优；把 OR 拆成 UNION ALL 以便分别命中索引；避免大 IN 列表导致优化器选择不稳定；对 ORDER BY 和 LIMIT 结合索引；把深分页从 limit offset 改为基于游标或主键范围的翻页。SQL 改写的核心是让数据库少读数据、少排序、少创建临时表、少做无效计算。

表结构和数据规模治理

当单条 SQL 已经较合理但仍然慢，就要看表结构与数据规模。字段类型是否过大，字符集和排序规则是否导致比较成本高，是否存在低选择性字段误建索引，是否有宽表导致回表和 IO 放大，是否历史数据长期堆积。对于明显冷热分离的业务，可以考虑归档历史表、分区表、冷热数据拆分或按业务维度分表。对于聚合统计类查询，可以引入汇总表或离线计算结果。表结构优化要谨慎，因为它影响面大，必须结合读写比例、数据增长速度和迁移成本评估。

排查锁等待和并发问题

有些慢查询并不是因为执行计划差，而是被锁等待拖慢。需要检查事务是否过长，是否存在大事务批量更新，是否有范围更新扩大锁范围，是否因为索引缺失导致更新扫描大量记录并加锁，是否存在死锁重试。InnoDB 下还要关注隔离级别、间隙锁、行锁退化感知以及热点行更新。处理这类问题时，除了优化 SQL 和索引，还可能需要缩短事务、调整更新顺序、拆小批量任务、降低热点竞争或把强一致写路径改成异步化。

从业务链路补充优化

数据库优化不能只盯着 SQL。高频读、结果相对稳定的查询可以考虑缓存，但要设计好过期策略、一致性要求、击穿和雪崩保护。重复查询可以在业务层合并请求或批量查询，避免 N+1 查询。报表类、搜索类、排行榜类需求不一定适合直接压在主库上，可以走只读库、搜索引擎、异步任务或预计算。还要评估是否存在接口一次请求触发多条慢 SQL 的情况，因为用户感知的慢往往来自整条链路，而不是单个数据库调用。

优化后必须验证闭环

慢查询优化不能以“SQL 跑快了一次”作为结束。需要对比优化前后的执行计划、扫描行数、返回行数、耗时分位数、CPU、IO、锁等待、临时表和排序指标，最好在接近真实数据量的环境中验证。上线后继续观察慢查询日志和监控，确认没有因为新增索引拖慢写入，也没有让其他查询执行计划变差。对于核心链路，还要补充压测或回归测试，并记录优化原因、影响范围和回滚方案，这样才能防止同类慢查询反复出现。

易错点

只回答“加索引、分库分表、加缓存”，没有说明如何定位根因和验证效果。
把 EXPLAIN 字段逐个背诵，却不能解释扫描行数、排序、临时表和回表的实际影响。
认为索引越多越好，忽略索引会增加写入成本、存储成本和优化器选择复杂度。
忽视深分页、SELECT *、隐式类型转换、函数操作索引列等常见 SQL 问题。
只关注单条 SQL 执行时间，不看调用频率、P95/P99、锁等待和完整业务链路。
优化后不做压测和线上监控对比，无法证明方案真的降低了慢查询风险。

面试官追问

如何判断一条慢 SQL 是否命中了索引？

可以先看 EXPLAIN 中 possible_keys、key、type、rows 和 Extra。key 表示实际使用的索引，type 越接近 const、ref、range 通常越好，rows 越小说明扫描量越可控。但不能只看 key 非空，还要看是否扫描行数仍然很大，是否出现 Using filesort 或 Using temporary，以及索引是否真正过滤了数据。

联合索引字段顺序应该怎么设计？

通常要结合查询频率、等值条件、范围条件、排序分组和字段选择性综合设计。高频等值过滤字段适合放在前面，范围字段后面的索引列往往不能继续充分用于过滤，排序字段能否利用索引也要看前面的条件是否连续匹配。不能机械地只按选择性排序，要以真实 SQL 模式为准。

为什么 SELECT * 可能导致慢查询？

SELECT * 会读取不必要的列，增加网络传输、内存占用和回表成本。如果原本可以通过覆盖索引直接返回结果，查询了额外字段后就可能必须回表读取整行。对于宽表或高频接口，这种成本会被调用次数放大，所以生产中应尽量只查业务真正需要的字段。

大分页为什么慢，怎么优化？

大分页的核心问题是 offset 很大时，数据库通常需要先扫描并跳过前面大量记录，再返回目标页数据。优化方式包括使用基于主键、自增 id、创建时间的游标翻页，或者先通过索引查询目标页主键，再回表获取完整数据。对于必须随机跳页的后台场景，也可以限制最大页数或使用搜索服务。

慢查询和锁等待如何区分？

如果执行计划看起来合理、扫描行数不大，但线上耗时突然升高，就要怀疑锁等待或资源竞争。可以查看事务列表、锁等待信息、死锁日志和对应时间段的更新语句。锁问题常伴随长事务、热点更新、批量写入或缺索引更新，处理重点是缩短事务、补索引、拆批和降低热点冲突。

什么情况下应该引入缓存？

缓存适合读多写少、结果相对稳定、允许一定一致性延迟或可设计主动失效的场景。不能用缓存掩盖所有慢 SQL，因为缓存未命中、击穿或批量失效时数据库仍会承压。引入缓存前要明确过期策略、更新策略、穿透防护、热点 key 保护和降级方案。