🔍 登录 注册

MySQL 慢查询治理工程:从慢日志采集到 SQL 评审门禁

南 南风知我意 2026-11-17 👁 2 ⏱ 4min

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 优化靠经验,这套流水线靠机制——机制让团队换人后曲线仍然向下。

读完了?给作者一点支持

本文采用 CC BY 4.0 协议转载 · © 南风知我意

评论(0)

登录 后参与评论。

  • 还没有评论,来抢沙发。

🔗 相关文章

审计人的职业规划地图:事务所、企业内审、监管与自由执业的四条路线

"做了三年审计接下来去哪"是行业论坛上的永恒问题。2026 年的就业市场给出了新的答案坐标:AI 压缩了基础核算与核对类岗位,同时抬高了风险判断、内控体系、数据审计类岗位的身价。本文把审计人的出路整理成四条主路线——事务所纵深、企业内审/风控、监管与准则机构、自由执业/专家证人——每条给进入条件、典型轨迹、5 年收入区间感与适配画像,帮你做一次结构化的自我定位。

1 天前👍 1

AI 正在怎样改变审计工作:一份可落地的工具地图与边界清单

"AI 取代审计师"是标题党,"AI 不改审计流程"是天真的。2026 年的真实图景是:审计的每个环节都出现了成熟度不一的 AI 应用——有的已进四大方法论并受监管审视(分析程序、抽凭),有的仍处试点(风险评价辅助),有的干脆不该用(对管理层意图的判断)。本文按审计流程逐环节给出一份工具地图:能做什么、成熟度如何、边界在哪,以及从业者该怎么上手。

1 天前👍 1

内部审计的转型:从合规检查到持续审计,2026 年的方法论与落地路径

企业内部审计正经历十年来最大的一次定位调整:监管趋严(上市公司内控规范迭代、"十五五"会计规划对行业数字化基建的明确指向)加上 AI 工具成熟,让传统"年度计划 + 现场抽查"模式的价值密度持续下降。领先实践已经给出替代方案:风险导向的动态审计计划 + 持续审计(continuous auditing)+ 咨询角色。本文给企业内审负责人与准备进入内审的事务所人士一套转型方法论:架构怎么搭、数据管道怎么建、异常规则库怎么运营、以及如何向审计委员会证明价值。

1 天前👍 1