慢查询优化不是一次性的“救火”行动，而是一个持续的闭环流程：监控采集 → 分析定位 → 优化执行 → 效果验证 → 回归监控。只有建立起这个闭环，才能将数据库性能维持在一个稳定的水位，而不是每次被业务高峰打穿后才手忙脚乱地翻日志。
22.3.1 开启并配置慢查询日志
慢查询日志是优化闭环的数据来源。MySQL 提供了几个核心参数来控制日志的记录行为：
| 参数 | 建议值 | 说明 |
|------|--------|------|
| slow_query_log | ON | 开启慢查询日志 |
| slow_query_log_file | /var/log/mysql/slow.log | 日志文件路径 |
| long_query_time | 0.1 ~ 1（秒） | 超过该时间的查询会被记录；OLTP 场景建议设 0.1~0.5 |
| log_queries_not_using_indexes | ON | 记录未使用索引的查询，有助于发现潜在风险 |
| log_slow_admin_statements | ON | 记录慢的管理语句，如 OPTIMIZE TABLE、ALTER TABLE |
| min_examined_row_limit | 0（或设置为较大值） | 扫描行数超过该值的语句才被记录，可过滤掉扫描极少行的查询 |
在生产环境，直接修改配置文件 my.cnf 后重启服务，或者通过 SET GLOBAL 动态调整。需要注意的是，long_query_time 设得过小会产生大量日志，对磁盘 I/O 有一定影响，可根据业务容忍度平衡。
22.3.2 使用工具分析慢查询日志
原始慢日志是一条条 SQL 和时间戳，直接阅读效率极低。需要借助工具汇总、排序、归类。
mysqldumpslow
MySQL 官方自带的轻量分析工具，可直接在服务器上运行：
# 按平均查询时间排序，展示前 10 条
mysqldumpslow -s at -t 10 /var/log/mysql/slow.log
# 统计各 SQL 出现次数（-s c），按次数排序
mysqldumpslow -s c -t 10 slow.log它会把 SQL 中的具体值替换为 N、字符串替换为 S，做模糊聚合。优点是简单，缺点是统计维度较少。
pt-query-digest
Percona Toolkit 中的重量级分析工具，功能强大得多：
# 分析慢日志，生成详细报告
pt-query-digest /var/log/mysql/slow.log > slow_report.txt
# 分析最近 10 分钟的慢查询（需 processlist 快照）
pt-query-digest --since '10m' /var/log/mysql/slow.log报告会按响应时间、执行次数、锁等待时间等维度列出最耗资源的 SQL，并给出每条 SQL 的执行计划示例。这是线上排查最常用的利器。
22.3.3 定位与深入分析
工具排出了“害群之马”后，下一步是对单条 SQL 做深度分析：
- 获取完整 SQL：注意慢日志中记录的只是当时执行的语句，如果带有变量绑定（Prepared Statement），可能只显示
?，需要结合应用日志或程序逻辑还原实际值。 - EXPLAIN 分析执行计划：将 SQL 加上
EXPLAIN在数据库中执行（或使用EXPLAIN FORMAT=JSON获取更详细信息），重点关注：
type：是否为 ALL（全表扫描）或 index（全索引扫描）？目标至少达到 range 或 ref。key：是否用到了预期的索引？实际使用的索引是什么？rows：预估扫描行数是否合理？Extra：出现Using filesort、Using temporary往往意味着排序或分组未利用索引。
- 检查统计信息：如果 EXPLAIN 显示优化器选择的索引不理想，很可能统计信息失效。对相关表执行
ANALYZE TABLE可刷新索引基数。 - 结合上下文：这条 SQL 是业务核心高频还是低频大扫表？是偶然慢还是一直慢？决定了优化的紧急程度和方向。
22.3.4 常见优化手段与执行
根据分析结果，选取合适的优化方式，一个真实案例往往只需其中一两招：
- 索引优化：最常见且效果最显著的手段。
- 创建缺失的索引，尤其是 WHERE、JOIN、ORDER BY 涉及的列。
- 建立覆盖索引，避免回表。
- 调整联合索引列顺序，使之符合最左前缀。
- 删除冗余索引和长期未使用的索引。
- SQL 改写：
- 消除
SELECT *，只取必要字段，有利于走覆盖索引。 - 拆分复杂子查询为 JOIN，或改写为 EXISTS。
- 深分页用延迟关联或游标分页替代
LIMIT 100000,10。 - 避免在索引列上使用函数或运算。
- 表结构调整：
- 将大字段拆分到独立表或附属表。
- 对超大表考虑分区或分库分表（更重的方案）。
- 参数与硬件调整：如增大
join_buffer_size、sort_buffer_size（需谨慎，每个会话一份），或加大内存提升缓冲池命中率。
22.3.5 优化效果验证
优化后必须验证，不能凭感觉。验证方法包括：
- EXPLAIN 对比：优化前后执行计划的变化，
rows是否骤降，Extra中是否不再有 filesort。 - 实际执行时间对比：在测试环境（或低峰期线上）用
SELECT SQL_NO_CACHE执行查询，测量响应时间。对于写操作，可以压测模拟。 - 慢查询日志观察：观察优化后的 SQL 是否还频繁出现在慢日志中。
- 监控指标验证：观察实例级别的 CPU、IO 利用率、QPS、平均响应时间等变化。
22.3.6 建立持续闭环与复盘机制
一次优化不是终点。线上业务和数据量不断变化，新的慢查询会持续产生。建立常态化流程才能长治久安：
- 定期巡检：每周或每天用 pt-query-digest 分析最近慢日志，识别新增的高消耗 SQL。
- 优化记录：为每条优化建立文档，记录问题 SQL、分析过程、优化措施、效果，便于后续复盘和知识沉淀。
- 预警联动：将慢查询数量纳入监控系统，设定阈值告警。当慢查询突然暴增时，能第一时间收到通知并介入。
- 发布前检查：在应用发版前，审查新上线的 SQL 是否合理，是否命中索引，可借助 SQL 审核工具（如 Inception、Yearning）自动化拦截。
- 闭环迭代：每次优化后回到监控，确认问题是否彻底收敛，再将新的慢查询纳入下一轮分析，形成螺旋上升的优化循环。
最终，慢查询优化闭环的目标是：让慢查询从一种“突发事件”变成一种可预期、可治理的日常运维任务，用系统化的方法替代救火式的疲于奔命。