Skip to content

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、索引、统计、参数、锁、架构和回归。

  • 分层要点:

    1. 治理闭环:采集(慢日志、performance_schema、Prometheus)→ 排序(总耗时 = 执行次数 × 平均耗时,同时看 P99、扫描行数)→ 定位(SQL/计划/锁/IO/CPU)→ 归因(SQL 写法、索引、统计、成本、锁、架构)→ 修复(改写/索引/参数/缓存/分库)→ 验证(压测/灰度/监控)→ 防回归(SQL 审核、慢日志告警、计划基线)。没有闭环的“优化”很容易按下葫芦浮起瓢。
    2. 慢日志: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 做聚合、排序和样本分析。
    3. 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 可看样本。它比慢日志更全,但有采集开销。
    4. 计划与实际:EXPLAIN 看 type/key/key_len/rows/filtered/Extra,EXPLAIN ANALYZE 看 actual rows/loops/time,optimizer_trace 看候选路径、成本和 why_not。估算和实际差几个数量级,通常是统计信息或成本模型问题,不要直接加索引。
    5. 统计信息: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 直方图改善倾斜列估算。统计是采样估算,不是精确值。
    6. 成本模型: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”。
    7. 归因矩阵:SQL(SELECT *、深分页、大 IN、OR、子查询、函数、隐式转换)、索引(缺失/冗余/列序/覆盖/统计)、锁(行锁/间隙锁/MDL/长事务)、资源(BP 命中、脏页、临时表、排序、连接数)、架构(读写分离、分库分表、缓存、ES 异构)、参数(buffer_pool、sort_buffer、tmp_table_size、join_buffer)。先归类,再选修复手段。
    8. 防回归:上线前 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 cost
text
归因树:
  慢
   ├─ 计划差? → 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:sys schema;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(统计/成本交互)。
  • 采集工具对比:

工具优点局限适用
慢日志简单、可落文件/表只记录执行完的 SQL;锁等待/网络不在 SQL 时间内入门、审计、Top SQL
performance_schema全量摘要、维度多有采集开销;需理解表结构常态化分析
sys.statement_analysis易读、聚合好依赖 performance_schema快速看 Top SQL
pt-query-digest聚合/样本/报告强需额外工具慢日志深度分析
Prometheus + exporter趋势/告警粒度到指标,不到单 SQL监控大盘
APM/链路追踪能看业务链路需埋点,SQL 维度可能弱端到端定位
  • 慢日志参数:
参数默认作用注意
slow_query_logOFF开关生产按需开启
long_query_time10s慢查询阈值可小数;0 记录全部
log_queries_not_using_indexesOFF记录未走索引 SQL容易刷爆日志
log_throttle_queries_not_using_indexes0限制每分钟条数配合上一项
min_examined_row_limit0扫描行数下限过滤小查询
log_slow_admin_statementsOFF记录管理语句DDL 排查
log_slow_replica_statementsOFF记录从库 SQL 线程主从延迟排查
log_outputFILE输出到文件/表TABLE 写 mysql.slow_log
  • performance_schema 关键字段:
字段含义用途
DIGEST_TEXTSQL 摘要聚合同类 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_persistentON统计持久化避免重启丢失
innodb_stats_persistent_sample_pages20持久化采样页越大越准、ANALYZE 越慢
innodb_stats_transient_sample_pages8非持久化采样仅相关场景
innodb_stats_auto_recalcON约 10% 行变化自动重算大事务后仍建议手动
innodb_stats_on_metadataOFF元数据查询触发统计开启可能拖慢 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_STATISTICS8.0直方图HISTOGRAM JSON
ANALYZE TABLE ... UPDATE HISTOGRAM8.0建直方图改善倾斜列估算
  • 成本模型:
成本项8.0 默认含义影响
io_block_read_cost1.0从磁盘读一个块磁盘 IO 贵 → 更愿意走索引
memory_block_read_cost0.25从 BP 读一个块内存读便宜
row_evaluate_cost0.1计算一行条件过滤成本
key_compare_cost0.05比较一次键排序/索引比较
disk_temptable_create_cost20.0建磁盘临时表避免磁盘临时表
disk_temptable_row_cost0.5磁盘临时表每行大临时表很贵
memory_temptable_create_cost2.0建内存临时表相对便宜
memory_temptable_row_cost0.1内存临时表每行仍有成本
  • 慢查询归因矩阵:
现象可能原因首选排查
type=ALL、key=NULL无索引/谓词不可用/成本选择EXPLAIN、SHOW WARNINGS
rows 大、filtered 低索引选择性差/条件过滤多EXPLAIN、统计、改写
Using filesort排序没走索引ORDER BY、索引列序
Using temporaryGROUP 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 个):

    1. 慢日志不等于慢 SQL:慢日志只记录执行完的 SQL,锁等待、网络、连接池排队、结果集传输可能不在 long_query_time 里;要结合 performance_schema、INNODB_TRX、data_lock_waits、APM 看端到端。
    2. Top SQL 要按“总耗时”排,不只看平均:高频 5ms SQL 的总耗时可能远大于低频 2s 报表;同时看 P99、扫描行数、返回行数、临时表和磁盘排序。只优化“平均最慢”的 SQL,可能收益很小。
    3. 统计信息是采样,不是真相:rows、filtered、Cardinality 都来自采样;数据倾斜、批量增删、大事务、自动重算阈值未触发都会让估算失真。ANALYZE TABLE 能缓解,但采样页数、直方图、数据分布决定上限。
    4. 成本模型是“估算的成本”,不是实际耗时:read_cost/eval_cost/prefix_cost 由成本常数和统计推算;io_block_read_cost 与 memory_block_read_cost 的差异决定“磁盘 IO 贵不贵”,临时表成本决定“要不要避免磁盘临时表”。调成本常数能改计划,但风险高,必须压测。
    5. 索引治理要算总账:未使用/冗余索引增加写放大、DDL 时间、BP 占用和优化器选择空间;删除前要覆盖完整业务周期、从库/报表/灾备场景。索引不是越多越好,少量高复用联合索引 + 覆盖索引通常更优。
    6. 防回归比单次优化更重要:SQL 审核、生产量级压测、慢日志告警、计划基线、版本升级回归、容量规划,才能避免“今天优化、下个月数据涨了又慢”。优化要沉淀成规范,而不是一次性救火。
  • 追问链:

    1. 慢查询治理流程? → 采集 → 排序 → 定位 → 归因 → 修复 → 验证 → 防回归;先证明瓶颈,再动手。
    2. 怎么发现慢 SQL? → 慢日志、performance_schema、sys.statement_analysis、Prometheus、APM;Top SQL 按总耗时/扫描行数排序。
    3. 慢日志关键参数? → slow_query_log、long_query_time、log_queries_not_using_indexes、min_examined_row_limit、log_output、log_slow_admin_statements。
    4. long_query_time 怎么设? → 按业务 RT 目标设,常见 0.1–1s;0 记录全部,但日志量大。
    5. 慢日志有哪些坑? → 只记录执行完的 SQL、锁等待/网络不在内、未走索引日志刷爆、从库/管理语句默认不记。
    6. pt-query-digest 看什么? → 总耗时、执行次数、平均/P99、扫描行数、样本 SQL、临时表/排序。
    7. performance_schema 看什么? → events_statements_summary_by_digest 的执行次数、总/平均耗时、扫描/返回行数、未用索引、磁盘临时表、排序。
    8. 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。
    9. 总耗时和平均耗时怎么选? → 先看总耗时找收益最大的,再看平均/P99 找体验最差的;两者结合。
    10. P99 怎么用? → 看长尾体验;performance_schema 摘要不直接给 P99,需监控/采样估算。
    11. 扫描行数为什么重要? → 扫描多但返回少,说明索引/谓词/分页有问题;扫描行数是 IO/CPU 的直接来源。
    12. EXPLAIN 怎么配合? → 看 type/key/key_len/rows/filtered/Extra,判断访问方式、索引列数、估算行数、排序/临时表。
    13. EXPLAIN ANALYZE 怎么用? → 看 actual rows/loops/time,和 rows 估算对比,判断统计/成本是否失真;会执行 SQL。
    14. optimizer_trace 看什么? → considered_access_paths、chosen、cost、why_not,判断为什么选/不选某个索引。
    15. 统计信息是什么? → 表/索引的行数、基数、分布等估算,供优化器计算 rows/filtered 和成本。
    16. InnoDB 统计怎么采? → 持久化统计采样索引页;innodb_stats_persistent_sample_pages 控制页数;ANALYZE TABLE 触发。
    17. innodb_stats_persistent 是什么? → 统计持久化到磁盘,重启不丢;5.7+ 默认 ON。
    18. 采样页数怎么调? → 默认 20;增大更准但 ANALYZE 更慢;倾斜严重时配合直方图。
    19. ANALYZE TABLE 什么时候执行? → 大批增删、统计过期、计划突变后;大表低峰执行,注意 IO/锁开销。
    20. 直方图解决什么? → 改善非均匀/倾斜列的基数估算;不创建索引,不改变访问结构。
    21. 统计信息为什么会失真? → 采样误差、数据倾斜、批量变更、自动重算未触发、持久化统计未更新。
    22. Cardinality 是什么? → 索引列不同值的估算数量;SHOW INDEX 里是估算值,不是精确值。
    23. 成本模型是什么? → 优化器用统计和成本常数估算每个候选计划的 read_cost/eval_cost/prefix_cost,选总成本最低的。
    24. read_cost/eval_cost/prefix_cost 区别? → 读行成本、条件计算成本、到当前表的累计成本;总成本看 query_cost。
    25. io_block_read_cost/memory_block_read_cost 是什么? → 磁盘读块/内存读块的相对成本;默认 1.0/0.25,体现磁盘 IO 更贵。
    26. 成本常数能调吗? → 能,mysql.server_cost/engine_cost + FLUSH OPTIMIZER_COSTS;风险高,必须压测和回归。
    27. 为什么优化器选错? → 统计失真、成本估算偏差、ORDER BY ... LIMIT、函数/隐式转换、大 IN/range 内存、版本差异(4.1-9)。
    28. 索引治理怎么做? → sys.schema_unused_indexes、sys.schema_redundant_indexes、pt-index-usage、pt-duplicate-key-checker;观察完整周期再删。
    29. 未使用索引能直接删吗? → 不能;统计窗口可能没覆盖月报/从库/灾备;先观察、灰度、保留回滚。
    30. 冗余索引怎么识别? → 前缀索引、同列唯一/普通索引、重复联合索引;用 sys.schema_redundant_indexes 和 pt-duplicate-key-checker。
    31. 深分页怎么优化? → 延迟关联、游标 WHERE id > ?、覆盖索引;避免大 offset 扫过再丢弃。
    32. 大 IN 怎么优化? → 分批、临时表、JOIN;注意 eq_range_index_dive_limit 导致估算变粗。
    33. 临时表/排序怎么优化? → 让 GROUP BY/ORDER BY 走索引、减少结果集、调 tmp_table_size/sort_buffer_size、避免 SELECT *。
    34. 锁等待怎么排查? → INNODB_TRX 找长事务、data_lock_waits/sys.innodb_lock_waits 找等待关系、metadata_locks 看 MDL(4.1-10)。
    35. BP 命中率怎么看? → 1 - Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests;低命中看热数据/大查询/扩容。
    36. 慢查询和架构有什么关系? → 读写分离、分库分表、缓存、ES 异构、异步化能从架构层消除慢查询,不只是 SQL 层。
    37. 读写分离/分库分表怎么选? → 读多写少先读写分离/缓存;数据量大/写入高再分库分表;注意一致性、跨分片查询、扩容。
    38. 缓存/ES 异构? → 热点读用 Redis,复杂检索/聚合用 ES;注意双写一致性、缓存穿透/击穿(4.2-8/4.2-10)。
    39. 怎么防回归? → SQL 审核、生产量级压测、慢日志告警、计划基线、版本升级回归、容量规划。
    40. 监控指标有哪些? → 慢查询数、P99、Threads_running、QPS/TPS、BP 命中、磁盘 IO、临时表、排序、锁等待、主从延迟。
    41. 版本升级要注意什么? → 优化器/成本模型/统计/新特性变化;升级前跑 SQL 回归和压测,升级后对比计划。
    42. 容量规划怎么做? → 看数据增长、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/索引/锁/资源/架构)→ 修复手段与取舍 → 防回归/监控/容量规划 → 生产案例。