一、写在前面：慢查询的“冰山模型”
生产环境的性能问题像一座冰山，露出水面的只是“偶尔超时”的表象；隐藏在水下的，可能是索引缺失、数据倾斜、磁盘抖动、锁竞争、内存抖动，甚至业务洪峰。慢查询治理的第一步，是把整座冰山测绘出来，而非仅仅盯着冰尖。本文提出“三重定位”方法：
1. 慢日志：定位“哪条 SQL 慢”；
2. 审计表：定位“为什么慢”；
3. 火焰图：定位“系统资源到底耗在哪”。
三者层层递进，形成可复现、可量化、可落地的闭环。
二、第一重定位：慢日志——让每一次超时都有迹可循
1. 采集策略
- 阈值设定：平均耗时超过 100 ms 或扫描行数超过 1 万即落入慢日志。
- 采样比例：高并发场景下采用 1/100 采样，防止 I/O 放大。
- 持久化周期：按天转储，压缩归档 30 天，支持回溯。
2. 字段解释
- Query_time：端到端耗时，包含锁等待、网络往返。
- Rows_examined：扫描行数，用于判断索引有效性。
- Lock_time：锁等待时长，区分 CPU 与锁瓶颈。
- Thread_id：可关联审计表中的会话级指标。
3. 快速分类
通过正则或解析器，把慢日志按四类打标签：
- 索引缺失型：Rows_examined 远大于返回行数。
- 数据倾斜型：同一 SQL 不同参数耗时方差巨大。
- 锁竞争型：Lock_time 占比高。
- 资源抖动型：Query_time 突刺，伴随磁盘或 CPU 尖峰。
4. 可视化看板
把解析结果写入时序数据库，按小时聚合：
- 慢查询总量趋势图
- 平均耗时热力图
- Top 20 SQL 排行榜
三、第二重定位：审计表——把模糊感觉变成量化指标
1. 审计表结构
在业务库旁路部署一张会话级审计表，字段包括：
- sql_digest：SQL 指纹，去掉常量、统一大小写。
- exec_count：执行次数。
- sum_latency：总耗时。
- sum_rows_sent：返回行数总和。
- plan_hash：执行计划指纹，用于捕捉计划突变。
2. 采样策略
- 会话级采样：每 1000 次执行采样一次，避免高并发下的写入风暴。
- 计划突变采样：当 plan_hash 变化时强制记录。
3. 关联分析
把审计表与慢日志按 sql_digest 关联，可得出：
- 某条 SQL 在慢日志中出现 1 次，在审计表出现 10 万次 → 命中率低但影响面大。
- 同一指纹出现多个 plan_hash → 计划抖动导致偶发慢。
4. 自动基线
利用 30 天审计数据，计算每条 SQL 的平均耗时 μ 与标准差 σ，自动生成基线阈值 μ+3σ。触发阈值即产生告警，而非人工拍脑袋。
四、第三重定位：火焰图——在 CPU 的森林里寻找热点
1. 采集方式
- 用户态：对数据库进程进行 perf record，抓取 CPU 调用栈。
- 内核态：systemtap 或 eBPF，观测 I/O、调度、内存拷贝。
2. 火焰图阅读法
- 宽柱即热点：解析器、执行器、InnoDB 层、B+ 树查找、锁等待。
- 颜色无意义，高度代表调用深度，宽度代表 CPU 时间占比。
3. 与慢日志的交叉验证
- 慢日志显示“某 SQL 平均 300 ms”；
- 火焰图显示“B+ 树查找占 70%”；
- 二者结合即可断定：索引缺失导致全表/全索引扫描。
4. 周期对比
在慢查询高峰时段与低峰时段各采一张火焰图，做差分火焰图，可快速定位“新增热点”。
五、三重定位的协同流程：采集、清洗、关联、归因
1. 采集
慢日志 → 本地文件；审计表 → 业务库；火焰图 → 性能分析节点。
2. 清洗
统一时间戳、脱敏、压缩、去重。
3. 关联
以 sql_digest + 时间窗口为键，把三类数据 join 到同一条记录。
4. 归因
输出“根因标签”：索引缺失、数据倾斜、锁竞争、资源抖动、执行计划突变。
六、典型场景剖析：四类慢查询的完整治理案例
场景 A：索引缺失型
- 慢日志：Rows_examined 100 万，返回 10 行。
- 审计表：该 SQL 占全天总延迟 30%。
- 火焰图：B+ 树查找占 80%。
治理：添加复合索引，平均耗时从 300 ms 降至 3 ms。
场景 B：数据倾斜型
- 慢日志：同一指纹耗时 50 ms～5 s 波动。
- 审计表：参数分布极不均匀。
- 火焰图：CPU 使用平稳。
治理：把倾斜值单独分区，或改写 SQL 用覆盖索引。
场景 C：锁竞争型
- 慢日志：Lock_time 90%，Query_time 100 ms。
- 火焰图：mutex 等待占 60%。
治理：拆大事务、降低隔离级别、批量提交。
场景 D：执行计划突变
- 慢日志：某 SQL 突然全表扫描。
- 审计表：plan_hash 变化。
- 火焰图：全表扫描函数出现。
治理：强制指定索引或更新统计信息。
七、指标与基线：如何定义“真的慢”
1. 业务层 SLA
P99 < 100 ms，P95 < 50 ms。
2. 技术层基线
- CPU：单核利用率 < 70% 为健康。
- I/O：磁盘 util < 60%。
- 锁：InnoDB row lock wait < 10 ms。
3. 动态基线
利用滑动窗口算法，每周自动更新阈值，避免“刻舟求剑”。
八、治理闭环：从告警到复盘
1. 告警
慢日志 + 审计表双阈值触发，减少误报。
2. 工单
自动生成 JIRA 工单，字段包括：SQL 指纹、根因标签、影响面、建议方案。
3. 修复
DBA、开发、运维三方评审，灰度上线。
4. 复盘
7 天后回顾修复效果，更新知识库，沉淀到“慢查询百科”。
九、常见误区与最佳实践
误区 1：只盯着慢日志，不关联审计表 → 漏掉高频次、低延迟抖动。
误区 2：火焰图采样频率过低 → 错过短时尖峰。
误区 3：一次性加很多索引 → 写放大，适得其反。
最佳实践：
- 任何索引上线前先跑 24 小时影子测试；
- 每周跑一次全库统计信息更新；
- 火焰图采样周期 ≤ 10 ms，持续 30 秒即可。
十、工具链地图：从操作系统到数据库引擎
- 慢日志解析：pt-query-digest、内置解析器
- 审计插件：官方 Audit Plugin、社区版
- 火焰图：perf、systemtap、eBPF、FlameGraph 脚本
- 可视化：Grafana、时序数据库
- 告警：Alertmanager、短信、IM 机器人
十一、未来展望：自适应诊断与 AI 加持
1. 根因模型
用机器学习对历史慢日志打标签，训练出“根因分类器”，新慢查询 5 秒内给出预测。
2. 自愈系统
索引缺失 → 自动生成候选索引 → 影子验证 → 灰度上线。
3. 智能容量预测
基于审计表增长率，预测未来 30 天的慢查询风险，提前扩容或优化。
十二、结语：让慢查询从“火警”变成“天气预报”
慢查询治理从来不是一次性的“救火”，而是持续性的“气候观测”。慢日志告诉我们“哪里下雨了”，审计表告诉我们“雨有多大”，火焰图告诉我们“为什么下雨”。把这三重定位变成日常流程，就能把不可预测的“火警”变成可提前准备的“天气预报”，让数据库性能真正步入可控、可演进、可持续的轨道。
