MySQL 面试题（四）：SQL 优化与线上排障

2026-07-24 MySQL 1648 字 · 约 5 分钟
MySQLSQL优化故障排查性能优化
所属系列：MySQL 面试题 · 第 4 / 4 篇

优化题最重要的是建立证据链。不要一看到慢查询就加索引，也不要一看到 CPU 高就重启数据库。

慢 SQL 的排查顺序

  1. 确认时间范围、SQL 指纹、调用来源和影响面；
  2. 查看请求量、并发数、连接池、CPU、I/O 和锁等待；
  3. 获取 SQL、参数类型、表结构、索引与数据量；
  4. 使用 EXPLAIN ANALYZE 对比估算行数和实际行数；
  5. 判断瓶颈属于扫描、排序、回表、锁、网络还是返回数据过多；
  6. 修改后在接近生产的数据分布下复测。

如何开启和查看慢查询

SHOW VARIABLES LIKE 'slow_query_log';
SHOW VARIABLES LIKE 'long_query_time';
SHOW VARIABLES LIKE 'log_queries_not_using_indexes';

生产环境是否启用 log_queries_not_using_indexes 要谨慎，它可能产生大量噪声。优先结合 Performance Schema、监控平台和慢日志聚合工具按 SQL 指纹分析。

一条 SQL 很慢，常见原因有哪些

  • 缺少合适索引或索引选择错误；
  • 扫描和返回行数过多；
  • 排序、分组或临时表成本高；
  • 统计信息与真实数据分布偏差；
  • 热点行锁等待或元数据锁；
  • 连接池排队、网络延迟或客户端读取慢；
  • Buffer Pool 命中率下降、磁盘 I/O 抖动；
  • 大事务、DDL、备份或复制任务争用资源。

如何优化查询

只查询需要的列

避免无条件 SELECT *，它会增加网络传输、内存和回表概率。

减少无界查询

管理后台和导出任务也应设置分页、时间范围与最大行数。

批量写入但控制批次

单条循环写入往返次数多，超大事务又会占用锁和日志。按业务压测结果分批提交，例如每批 500 或 1000 行。

避免在事务中调用外部服务

先开启事务、再调用 HTTP 服务会延长持锁时间。应先完成可外置的计算和远程调用，再进入尽可能短的数据库事务。

ORDER BY 为什么会出现文件排序

当索引顺序无法直接满足排序时，执行计划可能出现 filesort。它不一定真的写磁盘，只表示需要额外排序。

要让索引支持过滤和排序，需要关注：

  • 联合索引列顺序；
  • 等值条件之后的排序列；
  • 升降序是否兼容；
  • 返回数据量是否使优化器选择其他方案。

GROUP BY 和去重如何优化

  • 先过滤再聚合；
  • 为过滤和分组设计联合索引；
  • 避免对大结果集重复聚合；
  • 高频统计可以使用汇总表，但必须设计一致性策略；
  • 不要为了绕过 ONLY_FULL_GROUP_BY 随意关闭 SQL 模式。

为什么突然选错索引

可能是数据分布、参数、统计信息或缓存发生变化：

ANALYZE TABLE orders;
SHOW INDEX FROM orders;
EXPLAIN ANALYZE SELECT ...;

如果列分布高度倾斜，可以评估直方图。FORCE INDEX 只能在验证收益和数据变化风险后使用。

连接数打满怎么排查

先看连接状态：

SHOW GLOBAL STATUS LIKE 'Threads_connected';
SHOW GLOBAL STATUS LIKE 'Threads_running';
SHOW VARIABLES LIKE 'max_connections';
SHOW FULL PROCESSLIST;

重点判断：

  • 应用是否泄漏连接；
  • 连接池上限是否超过数据库承载能力；
  • SQL 是否因为锁或 I/O 长时间占用连接；
  • 是否出现突发流量；
  • 空闲连接回收策略是否合理。

直接增大 max_connections 可能把“连接拒绝”变成内存耗尽和更严重的抖动。

如何排查锁等待

SELECT * FROM performance_schema.data_lock_waits;
SELECT * FROM performance_schema.data_locks;
SHOW ENGINE INNODB STATUS\G

同时查找持锁事务的 SQL、开始时间、客户端和事务大小。不能只杀等待方，必须找到真正长期持锁的事务。

复制延迟常见原因

  • 主库产生大事务；
  • 从库回放线程受单线程或依赖限制；
  • 从库 I/O 或 CPU 不足；
  • 从库还承担大量查询；
  • DDL 或锁阻塞回放；
  • 网络抖动。

排查时要区分日志传输延迟和 SQL 回放延迟。读写分离系统还要明确业务是否允许读取旧数据。

大表变更如何降低风险

执行前确认：

  • MySQL 版本和 DDL 算法；
  • 是否支持 ALGORITHM=INSTANT 或 INPLACE；
  • 是否需要重建表；
  • 元数据锁等待；
  • 临时磁盘空间；
  • 主从延迟；
  • 超时、取消和回滚方案。

MySQL 8.0.18 以后可以使用：

EXPLAIN ALTER TABLE orders ADD COLUMN remark VARCHAR(255);

即使操作标记为 Online DDL，也可能在开始和结束阶段获取元数据锁。

删除大量历史数据怎么做

不要直接执行一个超大 DELETE。通常采用：

  1. 按主键或时间小批量删除；
  2. 每批提交并监控延迟；
  3. 控制执行速率；
  4. 清理相关归档和二级索引；
  5. 如果按时间淘汰是长期需求，评估分区表。

删除数据不代表表空间一定立即归还给操作系统，是否需要重建表要结合空间压力和维护窗口决定。

缓存能解决所有数据库性能问题吗

不能。缓存适合高频读取且允许一定时效性的场景，但会引入：

  • 缓存一致性；
  • 穿透、击穿和雪崩；
  • 热点 Key；
  • 失效策略；
  • 回源峰值。

先修复明显的全表扫描、无界查询和长事务，再决定是否加缓存。

线上优化的验收指标

优化前后至少对比：

  • P50、P95、P99 延迟；
  • 扫描行数和返回行数；
  • QPS 与并发连接；
  • CPU、I/O、Buffer Pool 命中；
  • 锁等待与死锁次数；
  • 错误率和复制延迟。

只有单次本地执行“快了”不能证明优化在生产负载下有效。

面试速答

CPU 100% 怎么处理

先限制影响面并获取现场证据，找高消耗 SQL、执行计划和请求来源；不能第一时间重启并丢失证据。

慢查询一定要加索引吗

不一定。也可能是返回数据过多、锁等待、排序聚合、网络或调用方式问题。

如何证明优化有效

使用相同数据规模、参数分布和并发模型，对比执行计划与关键指标，并观察上线后的长尾延迟和错误率。

相关文章