MySQL 慢查询治理工程:从慢日志采集到 SQL 评审门禁
单条慢查询的优化是技术活,慢查询的长期治理是工程活。多数团队的现状是:慢日志开了但没人看、索引加了但没人删、同样的烂 SQL 换个写法又进来。本文给一套把"治慢"变成流水线的做法:采集分层、归因自动化、评审门禁、回归防护。
一、慢日志的正确打开方式
slow_query_log = ON
long_query_time = 0.1 # OLTP 系统 100ms;别用默认 10s,会漏掉量大面广的"中速查询"
log_queries_not_using_indexes = OFF # 小表全扫是合理的,开了只会淹没有效信号
min_examined_row_limit = 100 # 配合上条:扫得少就不记
log_slow_admin_statements = ON # DDL 也要进画像
阈值哲学:long_query_time 按 P95 目标设(接口 SLO 300ms → 慢日志 100ms),而不是"能抓多少算多少"。全量记录不可行,但只记极端值会漏掉"不慢但巨多"的 CPU 杀手——这部分靠 performance_schema 补。
二、归因分层:digest 聚合代替逐条看
-- pt-query-digest 仍是慢日志分析主力
pt-query-digest --since '2026-11-16 00:00:00' /var/log/mysql/slow.log > report.txt
-- 实时画像(不依赖慢日志):按 digest 排总耗时
SELECT DIGEST_TEXT, COUNT_STAR,
SUM_TIMER_WAIT/1e12 AS total_s,
AVG_TIMER_WAIT/1e9 AS avg_ms,
SUM_ROWS_EXAMINED/COUNT_STAR AS avg_rows_examined
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC LIMIT 15;
归因三问按顺序执行:总耗时最大(不是单条最慢!)、平均扫描行数/返回行数比最高(过滤效率)、调用次数最多(哪怕每条 5ms)。一个健康的画像报告应该直接给出"前 3 个 digest + 建议动作",而不是 200 行列表。
自动化:把 digest top-N 每天定时导出对比昨日——新面孔 digest 出现在 top10 = 有人上了新 SQL,这就是归因的起点。
三、SQL 评审门禁:让烂查询进不来
治理的上游是准入。三条可自动化的硬规则(lint/CI 或 DBA 审核 checklist):
-- 规则一:WHERE 无索引可用 → 拒(小表白名单除外)
-- 检查法:EXPLAIN 的 type=ALL 且 rows > 1000
-- 规则二:函数包裹索引列 / 隐式转换 → 拒
WHERE DATE(created_at) = '2026-11-17' -- 拒:改范围条件
WHERE varchar_col = 12345 -- 拒:隐式类型转换
-- 规则三:SELECT * 进代码(ORM 默认行为要显式关)
-- 规则四:无 LIMIT 的排序查询、深分页 OFFSET > 1000 → 改游标分页
WHERE id < ? ORDER BY id DESC LIMIT 20 -- 游标式,代替 OFFSET 50000
落地形态:ORM 层开"SQL 日志 + EXPLAIN 抽样审计"(本项目 GORM 侧可用 gorm.io/plugin/dbresolver+ 自研中间件把执行 SQL 采样送审),加 PR 模板里"涉及 SQL 必须附 EXPLAIN"。工具化示例:
# CI 里的 SQL lint:对 migration/查询文件跑
sqlfluff lint --dialect mysql sql/ && \
mysqlsh -e "EXPLAIN FORMAT=JSON $(cat sql/new_query.sql)" | jq '.cost_info' | tee -a review.log
四、索引治理闭环:加之前先证明值得
慢查询的解法七成是索引,但索引是有息负债。加索引前的三问:写入频率多高?现有索引能否复用(看 sys.schema_unused_indexes)?预期能把扫描行数降几个数量级?
删索引的安全流程(invisible 三步法)已在索引篇展开,这里补自动化侧:每周自动报告"冗余索引 + 90 天未使用索引 + 疑似缺失索引"(缺失检测用 performance_schema.table_io_waits_summary_by_index_usage 里 PRIMARY/全表计数异常高的表交叉慢日志 digest)。
五、回归防护:核心查询钉计划
优化完的 SQL 会在三周后被统计信息变化或代码改动打回原形。两层防护:
-- 1) 核心查询集固化成测试资产:每次发版对预发库 EXPLAIN diff
SELECT digest_text FROM golden_queries -- 人工挑选的 30~50 条关键 SQL
-- CI: for q in $(cat golden.sql); do explain 前后计划 hash 对比; done
-- 2) 计划稳定性:8.0.30+ SPM / hint 钉选(谨慎,最后手段)
SELECT /*+ JOIN_ORDER(a, b) NO_BNL(c) */ ...
golden queries 的来源就是 digest top-N 里"值得永远快"的那部分。把它们变成 CI 资产后,"性能回归"就有了客观判定,而不是上线后被监控告警发现。
六、组织节奏:周会与配额
技术机制之外,两条组织约定决定成败:每周 30 分钟慢查询例会(看 digest 变化趋势 + 认领 top 问题);每迭代预留 15~20% 性能债配额。没有配额的团队,慢查询治理永远是"下次一定"。
度量用三个数追踪趋势:慢日志条数/天、top10 digest 总耗时占比、P99 接口延迟。目标是曲线向下且稳定,而不是某周清零(清零往往是阈值调松了)。
小结
慢查询治理的工程化 = 采集分层(慢日志按 P95 设阈 + P_S digest 补"快而多"的盲区)、归因自动化(digest top-N 每日 diff,新面孔即嫌疑人)、准入门禁(四条硬规则进 CI/评审模板)、索引负债管理(加前三问、删用 invisible 三步)、golden queries 回归钉计划。单条 SQL 优化靠经验,这套流水线靠机制——机制让团队换人后曲线仍然向下。
评论(0)
登录 后参与评论。