MySQL / 系统课时
建立查询性能基线
建立查询性能基线
优化前先把“快或慢”变成可比较的数据:固定观察窗口,记录响应时间、扫描行数、返回行数和执行次数,再用同一口径复测。这样才能区分真实收益、偶发抖动和流量变化。
学习目标
- 建立按时间窗口和 SQL 指纹统计的查询基线
- 理解响应时间、Rows examined、返回行数与执行次数分别说明什么
- 用扫描效率和总耗时判断优化优先级,而不是只看一次执行耗时
先固定比较窗口
单次执行时间会受缓存命中、并发、磁盘 I/O、参数值和连接状态影响。基线应至少固定三个条件:同一 SQL 指纹、相近业务负载、明确的统计窗口。生产环境可以分别保留高峰和低峰窗口,避免拿完全不同的流量阶段做前后对比。
四个核心指标各自回答不同问题:
| 指标 | 主要回答的问题 |
|---|---|
| 响应时间 | 一次执行通常需要多久,是否出现明显长尾 |
| Rows examined | 为得到结果扫描了多少行,访问路径是否过宽 |
| 返回行数 | 业务真正需要多少行,可用于衡量扫描放大 |
| 执行次数 | 语句出现得有多频繁,单次小问题是否累积成大成本 |
除了平均响应时间,还应关注最大值或监控系统里的 P95、P99。平均值下降但长尾恶化,仍可能让接口体验变差。
从 Performance Schema 找到基线候选
MySQL 的 performance_schema.events_statements_summary_by_digest 会按语句摘要累计执行统计,适合先找到“总成本高、执行频繁、扫描放大明显”的语句。下面查询假设 Performance Schema 已启用,并把计时器从皮秒换算为毫秒。
SELECT
SCHEMA_NAME,
LEFT(DIGEST_TEXT, 120) AS sample_sql,
COUNT_STAR AS exec_count,
ROUND(AVG_TIMER_WAIT / 1000000000, 2) AS avg_ms,
ROUND(MAX_TIMER_WAIT / 1000000000, 2) AS max_ms,
ROUND(SUM_ROWS_EXAMINED / NULLIF(COUNT_STAR, 0), 1) AS rows_examined_per_exec,
ROUND(SUM_ROWS_SENT / NULLIF(COUNT_STAR, 0), 1) AS rows_sent_per_exec
FROM performance_schema.events_statements_summary_by_digest
WHERE SCHEMA_NAME = 'app'
AND COUNT_STAR >= 10
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;预期结果(数值会随实例负载变化):
SCHEMA_NAME sample_sql exec_count avg_ms max_ms rows_examined_per_exec rows_sent_per_exec
app SELECT ... FROM orders WHERE ... <VALUE> <VALUE> <VALUE> <VALUE> <VALUE>这里的累计值不是天然的“最近 10 分钟”。生产基线应在窗口起点和终点分别采集快照,对 COUNT_STAR、SUM_TIMER_WAIT、SUM_ROWS_EXAMINED、SUM_ROWS_SENT 做差分;不要把服务器启动以来的累计值直接当作某个短时间窗口。
用扫描放大和总成本排优先级
Rows examined / 返回行数 可以作为扫描放大的直观信号。返回 20 行却平均扫描几十万行,通常值得继续检查索引、过滤条件和访问类型;但返回 0 行时这个比值没有意义,应直接看扫描行数和执行计划。
优化优先级还要乘上执行频率。一个平均 8 ms、每秒执行几千次的查询,可能比偶尔出现一次的 800 ms 查询消耗更多 CPU。实际排查时可优先关注“执行次数高 × 平均耗时高”或“执行次数高 × 扫描行数高”的语句。
用同一口径验证优化
复测时只改变一个主要变量,例如新增索引、改写条件或调整分页方式,然后在相近流量和相同观察窗口下重新采集。至少比较平均或分位响应时间、每次扫描行数、每次返回行数、执行次数和总耗时。
如果 Rows examined 明显下降而响应时间几乎不变,说明瓶颈可能已经转移到排序、临时表、网络返回、锁等待或并发资源竞争。真正可靠的优化结论,是访问成本和用户可见延迟都按预期变化,而不是某一次手工执行恰好更快。