Appearance
4.1-11 慢查询治理、统计信息、成本模型
所属:04 数据存储 / 4.1 MySQL 分级:B(按 A 类模板展开) 状态:◐ 初稿 关联:4.1-6、4.1-7、4.1-8、4.1-9、4.1-10、4.1-12、4.1-14
题型:排查 / 调优 / 体系
一句话结论:慢查询治理是“发现 → 定位 → 归因 → 修复 → 防回归”的闭环:慢日志和
performance_schema找 Top SQL,EXPLAIN/EXPLAIN ANALYZE/optimizer_trace看计划和实际,统计信息决定rows/filtered估算,成本模型决定选不选索引/连接顺序;治理不能只加索引,要同时管 SQL、索引、统计、参数、锁、架构和回归。分层要点:
- 治理闭环:采集(慢日志、
performance_schema、Prometheus)→ 排序(总耗时 = 执行次数 × 平均耗时,同时看 P99、扫描行数)→ 定位(SQL/计划/锁/IO/CPU)→ 归因(SQL 写法、索引、统计、成本、锁、架构)→ 修复(改写/索引/参数/缓存/分库)→ 验证(压测/灰度/监控)→ 防回归(SQL 审核、慢日志告警、计划基线)。没有闭环的“优化”很容易按下葫芦浮起瓢。 - 慢日志:
slow_query_log、long_query_time(默认 10s,生产常按 RT 目标调到 0.1–1s)、log_queries_not_using_indexes、min_examined_row_limit、log_slow_admin_statements、log_slow_replica_statements、log_output。用mysqldumpslow粗看,pt-query-digest做聚合、排序和样本分析。 performance_schema:events_statements_summary_by_digest按 SQL 摘要聚合,重点看COUNT_STAR、SUM_TIMER_WAIT、AVG_TIMER_WAIT、SUM_ROWS_EXAMINED、SUM_ROWS_SENT、SUM_NO_INDEX_USED、SUM_CREATED_TMP_DISK_TABLES、SUM_SORT_ROWS;sys.statement_analysis是更易读的视图;events_statements_history_long可看样本。它比慢日志更全,但有采集开销。- 计划与实际:
EXPLAIN看type/key/key_len/rows/filtered/Extra,EXPLAIN ANALYZE看 actual rows/loops/time,optimizer_trace看候选路径、成本和why_not。估算和实际差几个数量级,通常是统计信息或成本模型问题,不要直接加索引。 - 统计信息:InnoDB 持久化统计(
innodb_stats_persistent=ON)、采样页数(innodb_stats_persistent_sample_pages=20)、非持久化采样(innodb_stats_transient_sample_pages=8)、自动重算(innodb_stats_auto_recalc=ON,约 10% 行变化触发);ANALYZE TABLE手动更新;mysql.innodb_table_stats/innodb_index_stats看细节;8.0 直方图改善倾斜列估算。统计是采样估算,不是精确值。 - 成本模型:MySQL 8.0 用
server_cost/engine_cost的成本常数估算read_cost(读行)、eval_cost(计算条件)、prefix_cost(到当前表累计)、query_cost(总成本)。io_block_read_cost=1.0、memory_block_read_cost=0.25、row_evaluate_cost=0.1、key_compare_cost=0.05、临时表创建/行成本等共同决定“走索引还是全表、先连哪张表、要不要 filesort”。 - 归因矩阵:SQL(
SELECT *、深分页、大IN、OR、子查询、函数、隐式转换)、索引(缺失/冗余/列序/覆盖/统计)、锁(行锁/间隙锁/MDL/长事务)、资源(BP 命中、脏页、临时表、排序、连接数)、架构(读写分离、分库分表、缓存、ES 异构)、参数(buffer_pool、sort_buffer、tmp_table_size、join_buffer)。先归类,再选修复手段。 - 防回归:上线前 SQL 审核 +
EXPLAIN+ 生产量级压测;上线后慢日志/performance_schema告警;定期ANALYZE/直方图;索引治理(未使用/冗余);版本升级做计划回归;容量规划看 QPS、数据增长、BP 命中、IO 利用率。
- 治理闭环:采集(慢日志、
图/流程:
text
慢查询治理闭环:
采集:慢日志 / performance_schema / Prometheus
→ 排序:总耗时、P99、扫描行数、临时表/排序
→ 定位:EXPLAIN / EXPLAIN ANALYZE / optimizer_trace
→ 归因:SQL / 索引 / 统计 / 成本 / 锁 / 架构
→ 修复:改写 / 索引 / 参数 / 缓存 / 分库
→ 验证:压测 / 灰度 / 监控
→ 防回归:SQL 审核 / 告警 / 计划基线text
Top SQL 排序维度:
总耗时 = 执行次数 × 平均耗时
├─ 高频小 SQL:单次 5ms,但 QPS 高 → 总耗时最大
└─ 低频大 SQL:单次 2s,但一天几次 → 总耗时未必高
还要看:P99、扫描行数、返回行数、临时表、磁盘排序text
统计信息如何影响计划:
数据分布 → 采样 → rows / Cardinality / filtered
→ 成本估算 → 选索引 / 连接顺序 / 排序方式
统计过期/倾斜 → 估算错 → 计划错 → 慢查询text
成本模型(简化):
query_cost
├─ read_cost 读行/读块
├─ eval_cost 条件计算
├─ prefix_cost 到当前表的累计
└─ 临时表/排序/比较成本
成本常数:io_block_read_cost、memory_block_read_cost、
row_evaluate_cost、key_compare_cost、temptable costtext
归因树:
慢
├─ 计划差? → EXPLAIN/ANALYZE/trace → 统计/成本/索引
├─ 扫描多? → 索引/谓词/SQL 写法
├─ 等锁? → INNODB_TRX/data_lock_waits/MDL
├─ IO/CPU? → BP 命中/脏页/临时表/排序/连接数
└─ 架构? → 读写分离/分库分表/缓存/ES/异步版本/源码锚点:
- MySQL 5.6:
performance_schema语句摘要;EXPLAIN FORMAT=JSON;ICP/MRR。 - MySQL 5.7:
sysschema;innodb_stats_persistent默认 ON;optimizer cost tables;EXPLAIN FOR CONNECTION。 - MySQL 8.0:直方图;
EXPLAIN ANALYZE(8.0.18);hash join(8.0.18);prefer_ordering_index(8.0.21);derived_condition_pushdown(8.0.22);performance_schema默认 ON;data_locks/data_lock_waits。 - 关键默认值:
long_query_time=10s、slow_query_log=OFF、innodb_stats_persistent_sample_pages=20、innodb_stats_transient_sample_pages=8、innodb_stats_auto_recalc=ON、eq_range_index_dive_limit=200(8.0)、range_optimizer_max_mem_size=8MB。 - 成本表:
mysql.server_cost、mysql.engine_cost;8.0 默认io_block_read_cost=1.0、memory_block_read_cost=0.25、row_evaluate_cost=0.1、key_compare_cost=0.05。 - 源码/实现:
sql/sql_optimizer.cc(计划)、sql/opt_costmodel.cc(成本)、sql/opt_range.cc(range)、sql/opt_explain.cc(EXPLAIN)、sql/sql_lex.cc/sql/sql_parse.cc(慢日志判定)、storage/innobase/dict/dict0stats.cc(统计)、storage/innobase/handler/ha_innodb.cc(统计/成本交互)。
- MySQL 5.6:
采集工具对比:
| 工具 | 优点 | 局限 | 适用 |
|---|---|---|---|
| 慢日志 | 简单、可落文件/表 | 只记录执行完的 SQL;锁等待/网络不在 SQL 时间内 | 入门、审计、Top SQL |
performance_schema | 全量摘要、维度多 | 有采集开销;需理解表结构 | 常态化分析 |
sys.statement_analysis | 易读、聚合好 | 依赖 performance_schema | 快速看 Top SQL |
pt-query-digest | 聚合/样本/报告强 | 需额外工具 | 慢日志深度分析 |
| Prometheus + exporter | 趋势/告警 | 粒度到指标,不到单 SQL | 监控大盘 |
| APM/链路追踪 | 能看业务链路 | 需埋点,SQL 维度可能弱 | 端到端定位 |
- 慢日志参数:
| 参数 | 默认 | 作用 | 注意 |
|---|---|---|---|
slow_query_log | OFF | 开关 | 生产按需开启 |
long_query_time | 10s | 慢查询阈值 | 可小数;0 记录全部 |
log_queries_not_using_indexes | OFF | 记录未走索引 SQL | 容易刷爆日志 |
log_throttle_queries_not_using_indexes | 0 | 限制每分钟条数 | 配合上一项 |
min_examined_row_limit | 0 | 扫描行数下限 | 过滤小查询 |
log_slow_admin_statements | OFF | 记录管理语句 | DDL 排查 |
log_slow_replica_statements | OFF | 记录从库 SQL 线程 | 主从延迟排查 |
log_output | FILE | 输出到文件/表 | TABLE 写 mysql.slow_log |
performance_schema关键字段:
| 字段 | 含义 | 用途 |
|---|---|---|
DIGEST_TEXT | SQL 摘要 | 聚合同类 SQL |
COUNT_STAR | 执行次数 | 高频 SQL |
SUM_TIMER_WAIT | 总耗时(皮秒) | 总成本排序 |
AVG_TIMER_WAIT | 平均耗时 | 单次体验 |
SUM_ROWS_EXAMINED | 扫描行数 | 判断是否扫太多 |
SUM_ROWS_SENT | 返回行数 | 结果集大小 |
SUM_NO_INDEX_USED | 未用索引次数 | 找全表扫描 |
SUM_CREATED_TMP_DISK_TABLES | 磁盘临时表 | 临时表问题 |
SUM_SORT_ROWS | 排序行数 | 排序压力 |
SUM_SORT_MERGE_PASSES | 归并趟数 | 大排序 |
SUM_ERRORS/SUM_WARNINGS | 错误/告警 | 异常 SQL |
- 统计信息:
| 参数/视图 | 默认/用法 | 作用 | 注意 |
|---|---|---|---|
innodb_stats_persistent | ON | 统计持久化 | 避免重启丢失 |
innodb_stats_persistent_sample_pages | 20 | 持久化采样页 | 越大越准、ANALYZE 越慢 |
innodb_stats_transient_sample_pages | 8 | 非持久化采样 | 仅相关场景 |
innodb_stats_auto_recalc | ON | 约 10% 行变化自动重算 | 大事务后仍建议手动 |
innodb_stats_on_metadata | OFF | 元数据查询触发统计 | 开启可能拖慢 SHOW TABLE STATUS |
ANALYZE TABLE | 手动 | 重新采样 | 大表有 IO/短暂锁开销 |
mysql.innodb_table_stats | 视图 | 表级统计 | n_rows、clustered_index_size |
mysql.innodb_index_stats | 视图 | 索引统计 | n_diff_pfx01、n_leaf_pages |
SHOW INDEX | 命令 | Cardinality | 估算值,不是精确基数 |
information_schema.STATISTICS | 视图 | 索引元数据 | Cardinality 同源 |
COLUMN_STATISTICS | 8.0 | 直方图 | HISTOGRAM JSON |
ANALYZE TABLE ... UPDATE HISTOGRAM | 8.0 | 建直方图 | 改善倾斜列估算 |
- 成本模型:
| 成本项 | 8.0 默认 | 含义 | 影响 |
|---|---|---|---|
io_block_read_cost | 1.0 | 从磁盘读一个块 | 磁盘 IO 贵 → 更愿意走索引 |
memory_block_read_cost | 0.25 | 从 BP 读一个块 | 内存读便宜 |
row_evaluate_cost | 0.1 | 计算一行条件 | 过滤成本 |
key_compare_cost | 0.05 | 比较一次键 | 排序/索引比较 |
disk_temptable_create_cost | 20.0 | 建磁盘临时表 | 避免磁盘临时表 |
disk_temptable_row_cost | 0.5 | 磁盘临时表每行 | 大临时表很贵 |
memory_temptable_create_cost | 2.0 | 建内存临时表 | 相对便宜 |
memory_temptable_row_cost | 0.1 | 内存临时表每行 | 仍有成本 |
- 慢查询归因矩阵:
| 现象 | 可能原因 | 首选排查 |
|---|---|---|
type=ALL、key=NULL | 无索引/谓词不可用/成本选择 | EXPLAIN、SHOW WARNINGS |
rows 大、filtered 低 | 索引选择性差/条件过滤多 | EXPLAIN、统计、改写 |
Using filesort | 排序没走索引 | ORDER BY、索引列序 |
Using temporary | GROUP BY/DISTINCT/UNION | 临时表、索引、SQL 改写 |
| 扫描行数远大于返回行数 | 索引/谓词/深分页 | 覆盖索引、延迟关联、游标 |
| 等锁/超时 | 行锁/间隙锁/MDL/长事务 | INNODB_TRX、data_lock_waits |
| BP 命中低 | 热数据放不下/大查询 | buffer_pool、SQL、架构 |
| 磁盘临时表/排序 | 结果集大/参数小 | tmp_table_size、sort_buffer_size |
| QPS 高但单条不慢 | 高频小 SQL | 覆盖索引、缓存、批量 |
| 主从延迟 | 大事务/无主键/并行复制 | 4.1-14 |
深挖点(面试官常追的 6 个):
- 慢日志不等于慢 SQL:慢日志只记录执行完的 SQL,锁等待、网络、连接池排队、结果集传输可能不在
long_query_time里;要结合performance_schema、INNODB_TRX、data_lock_waits、APM 看端到端。 - Top SQL 要按“总耗时”排,不只看平均:高频 5ms SQL 的总耗时可能远大于低频 2s 报表;同时看 P99、扫描行数、返回行数、临时表和磁盘排序。只优化“平均最慢”的 SQL,可能收益很小。
- 统计信息是采样,不是真相:
rows、filtered、Cardinality 都来自采样;数据倾斜、批量增删、大事务、自动重算阈值未触发都会让估算失真。ANALYZE TABLE能缓解,但采样页数、直方图、数据分布决定上限。 - 成本模型是“估算的成本”,不是实际耗时:
read_cost/eval_cost/prefix_cost由成本常数和统计推算;io_block_read_cost与memory_block_read_cost的差异决定“磁盘 IO 贵不贵”,临时表成本决定“要不要避免磁盘临时表”。调成本常数能改计划,但风险高,必须压测。 - 索引治理要算总账:未使用/冗余索引增加写放大、DDL 时间、BP 占用和优化器选择空间;删除前要覆盖完整业务周期、从库/报表/灾备场景。索引不是越多越好,少量高复用联合索引 + 覆盖索引通常更优。
- 防回归比单次优化更重要:SQL 审核、生产量级压测、慢日志告警、计划基线、版本升级回归、容量规划,才能避免“今天优化、下个月数据涨了又慢”。优化要沉淀成规范,而不是一次性救火。
- 慢日志不等于慢 SQL:慢日志只记录执行完的 SQL,锁等待、网络、连接池排队、结果集传输可能不在
追问链:
- 慢查询治理流程? → 采集 → 排序 → 定位 → 归因 → 修复 → 验证 → 防回归;先证明瓶颈,再动手。
- 怎么发现慢 SQL? → 慢日志、
performance_schema、sys.statement_analysis、Prometheus、APM;Top SQL 按总耗时/扫描行数排序。 - 慢日志关键参数? →
slow_query_log、long_query_time、log_queries_not_using_indexes、min_examined_row_limit、log_output、log_slow_admin_statements。 long_query_time怎么设? → 按业务 RT 目标设,常见 0.1–1s;0 记录全部,但日志量大。- 慢日志有哪些坑? → 只记录执行完的 SQL、锁等待/网络不在内、未走索引日志刷爆、从库/管理语句默认不记。
pt-query-digest看什么? → 总耗时、执行次数、平均/P99、扫描行数、样本 SQL、临时表/排序。performance_schema看什么? →events_statements_summary_by_digest的执行次数、总/平均耗时、扫描/返回行数、未用索引、磁盘临时表、排序。events_statements_summary_by_digest关键字段? →DIGEST_TEXT、COUNT_STAR、SUM_TIMER_WAIT、AVG_TIMER_WAIT、SUM_ROWS_EXAMINED、SUM_ROWS_SENT、SUM_NO_INDEX_USED。- 总耗时和平均耗时怎么选? → 先看总耗时找收益最大的,再看平均/P99 找体验最差的;两者结合。
- P99 怎么用? → 看长尾体验;
performance_schema摘要不直接给 P99,需监控/采样估算。 - 扫描行数为什么重要? → 扫描多但返回少,说明索引/谓词/分页有问题;扫描行数是 IO/CPU 的直接来源。
EXPLAIN怎么配合? → 看type/key/key_len/rows/filtered/Extra,判断访问方式、索引列数、估算行数、排序/临时表。EXPLAIN ANALYZE怎么用? → 看 actual rows/loops/time,和rows估算对比,判断统计/成本是否失真;会执行 SQL。optimizer_trace看什么? →considered_access_paths、chosen、cost、why_not,判断为什么选/不选某个索引。- 统计信息是什么? → 表/索引的行数、基数、分布等估算,供优化器计算
rows/filtered和成本。 - InnoDB 统计怎么采? → 持久化统计采样索引页;
innodb_stats_persistent_sample_pages控制页数;ANALYZE TABLE触发。 innodb_stats_persistent是什么? → 统计持久化到磁盘,重启不丢;5.7+ 默认 ON。- 采样页数怎么调? → 默认 20;增大更准但 ANALYZE 更慢;倾斜严重时配合直方图。
ANALYZE TABLE什么时候执行? → 大批增删、统计过期、计划突变后;大表低峰执行,注意 IO/锁开销。- 直方图解决什么? → 改善非均匀/倾斜列的基数估算;不创建索引,不改变访问结构。
- 统计信息为什么会失真? → 采样误差、数据倾斜、批量变更、自动重算未触发、持久化统计未更新。
- Cardinality 是什么? → 索引列不同值的估算数量;
SHOW INDEX里是估算值,不是精确值。 - 成本模型是什么? → 优化器用统计和成本常数估算每个候选计划的
read_cost/eval_cost/prefix_cost,选总成本最低的。 read_cost/eval_cost/prefix_cost区别? → 读行成本、条件计算成本、到当前表的累计成本;总成本看query_cost。io_block_read_cost/memory_block_read_cost是什么? → 磁盘读块/内存读块的相对成本;默认 1.0/0.25,体现磁盘 IO 更贵。- 成本常数能调吗? → 能,
mysql.server_cost/engine_cost+FLUSH OPTIMIZER_COSTS;风险高,必须压测和回归。 - 为什么优化器选错? → 统计失真、成本估算偏差、
ORDER BY ... LIMIT、函数/隐式转换、大IN/range 内存、版本差异(4.1-9)。 - 索引治理怎么做? →
sys.schema_unused_indexes、sys.schema_redundant_indexes、pt-index-usage、pt-duplicate-key-checker;观察完整周期再删。 - 未使用索引能直接删吗? → 不能;统计窗口可能没覆盖月报/从库/灾备;先观察、灰度、保留回滚。
- 冗余索引怎么识别? → 前缀索引、同列唯一/普通索引、重复联合索引;用
sys.schema_redundant_indexes和pt-duplicate-key-checker。 - 深分页怎么优化? → 延迟关联、游标
WHERE id > ?、覆盖索引;避免大 offset 扫过再丢弃。 - 大
IN怎么优化? → 分批、临时表、JOIN;注意eq_range_index_dive_limit导致估算变粗。 - 临时表/排序怎么优化? → 让 GROUP BY/ORDER BY 走索引、减少结果集、调
tmp_table_size/sort_buffer_size、避免SELECT *。 - 锁等待怎么排查? →
INNODB_TRX找长事务、data_lock_waits/sys.innodb_lock_waits找等待关系、metadata_locks看 MDL(4.1-10)。 - BP 命中率怎么看? →
1 - Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests;低命中看热数据/大查询/扩容。 - 慢查询和架构有什么关系? → 读写分离、分库分表、缓存、ES 异构、异步化能从架构层消除慢查询,不只是 SQL 层。
- 读写分离/分库分表怎么选? → 读多写少先读写分离/缓存;数据量大/写入高再分库分表;注意一致性、跨分片查询、扩容。
- 缓存/ES 异构? → 热点读用 Redis,复杂检索/聚合用 ES;注意双写一致性、缓存穿透/击穿(4.2-8/4.2-10)。
- 怎么防回归? → SQL 审核、生产量级压测、慢日志告警、计划基线、版本升级回归、容量规划。
- 监控指标有哪些? → 慢查询数、P99、
Threads_running、QPS/TPS、BP 命中、磁盘 IO、临时表、排序、锁等待、主从延迟。 - 版本升级要注意什么? → 优化器/成本模型/统计/新特性变化;升级前跑 SQL 回归和压测,升级后对比计划。
- 容量规划怎么做? → 看数据增长、QPS、BP 命中、IO 利用率、连接数、复制延迟;提前扩容/归档/分片。
生产案例/取舍(示例,替换成你的项目):
- 慢日志 Top SQL 是高频小查询,单次 5ms 但 QPS 高;加覆盖索引/减少回表后总耗时明显下降。
- 低频报表单次 2s,但一天几次;评估后放离线/异步,不占用在线库。
- 批量删除后统计过期,计划从
range变ALL;ANALYZE TABLE+ 持久化统计恢复。 - 城市字段严重倾斜,平均基数失真;8.0 直方图后计划稳定。
- 深分页
LIMIT 1000000,20扫大量行;延迟关联/游标分页后 RT 下降。 - 大
IN列表超过eq_range_index_dive_limit,估算粗、计划抖动;分批/临时表后稳定。 Using temporary; Using filesort,临时表落盘;调整索引列序 + 减少结果集后消失。- 锁等待导致慢查询,慢日志只显示 SQL 执行时间;
data_lock_waits找到长事务,kill 后恢复。 - BP 命中率低,热数据放不下;扩容 BP/归档冷数据/优化大查询。
- 分库分表后跨分片聚合慢;本地索引 + ES 异构/离线汇总。
- 取舍:加索引换读性能,代价是写放大;调成本常数换计划,代价是回归风险;读写分离换读吞吐,代价是一致性;分库分表换容量,代价是跨分片复杂度。
易错点:
- 只看平均耗时,不看总耗时/执行次数。
- 只看慢日志,不看
performance_schema/锁等待/APM。 long_query_time设太大,漏掉大量慢 SQL。- 认为慢日志时间包含锁等待/网络。
- 认为加索引能解决所有慢查询。
- 认为
ANALYZE TABLE一定解决统计问题。 - 认为统计信息是精确值。
- 认为 Cardinality 是精确基数。
- 认为直方图是索引。
- 认为成本常数可以随便调。
- 认为
EXPLAIN的rows是实际行数。 - 忽略扫描行数/返回行数比例。
- 忽略临时表/磁盘排序。
- 忽略锁等待/MDL/长事务。
- 忽略 BP 命中/磁盘 IO/连接数。
- 未使用索引直接删除。
- 冗余索引不治理。
- 只优化 SQL,不看架构/缓存/分片。
- 不做压测/灰度/回归。
- 版本升级不做计划回归。
- 认为慢查询优化一次就永久有效。
30 秒口述稿:
慢查询治理是闭环:慢日志和
performance_schema找 Top SQL,按总耗时、P99、扫描行数排序;再用EXPLAIN、EXPLAIN ANALYZE、optimizer_trace看计划和实际;如果估算和实际差很多,先查统计信息和成本模型,而不是直接加索引。统计靠持久化采样、ANALYZE和 8.0 直方图;成本模型用read_cost/eval_cost/prefix_cost和成本常数决定计划。修复可以是改写 SQL、调索引、调参数、缓存、分库分表,最后用压测、监控和 SQL 审核防回归。
- 3 分钟展开顺序:结论(治理闭环)→ 慢日志与
performance_schema采集 → Top SQL 排序维度 →EXPLAIN/ANALYZE/trace→ 统计信息(采样/持久化/直方图)→ 成本模型(成本项/常数)→ 归因矩阵(SQL/索引/锁/资源/架构)→ 修复手段与取舍 → 防回归/监控/容量规划 → 生产案例。