05.1.3.4 统计信息与优化器
所属:全栈开发面试题库 / 05 数据库与存储 / 05.1 关系型数据库通用能力 / 05.1.3 SQL 优化与执行计划
覆盖范围
- 统计信息内容:行数、基数、直方图、MCV、相关性、密度
- 选择率估算与行数估算偏差来源
- MySQL 与 PostgreSQL 统计机制、采样与更新时机
- 选错索引、参数嗅探、计划漂移与计划稳定性
- 扩展统计、计划提示与统计信息维护影响
高频题速览
| 编号 | 题目 | 难度 | 频率 |
|---|---|---|---|
| 05.1.3.4-001 | 统计信息存什么 | 基础 | ★★★★★ |
| 05.1.3.4-005 | 统计信息何时更新 | 进阶 | ★★★★☆ |
| 05.1.3.4-006 | 统计过期选错索引 | 进阶 | ★★★★★ |
| 05.1.3.4-008 | 参数嗅探与计划不稳定 | 高级 | ★★★★☆ |
| 05.1.3.4-009 | 优化器为什么放弃可用索引 | 进阶 | ★★★★☆ |
统计 → 估算 → 选型链路
表/列统计(行数、基数、直方图、MCV、相关性)
→ 谓词选择率估算 → 各路径/连接估算行数与代价
→ 优化器选最低代价计划
偏差来源:统计过期 / 采样不足 / 列间相关未建模 / 复杂条件 / 参数分布倾斜
05.1.3.4-001 优化器依赖哪些统计信息?
频率 ★★★★★ | 难度 基础
一句话结论
优化器主要依据表行数、列基数(不同值数量)、值分布(直方图/最常见值)、列相关性与索引基数估算每个条件的选择率和输出行数,再比较各计划代价;统计缺失或过期,估算就会失真。
概念解释
- 表行数:估算扫描与连接规模的基础。
- 列基数:列不同值数量,反映区分度。
- 直方图/MCV:值区间分布与最常见值,用于范围与等值条件选择率。
- 相关性(correlation):列物理顺序与值顺序的相关程度,影响索引扫描回表代价估算。
- 索引基数:索引键的区分度,用于判断索引过滤力度。
原理流程
- 优化器读取表与列统计。
- 对每个谓词估算选择率(命中比例)。
- 结合行数估算各路径输出规模与代价,选择计划。
代码 / 命令
-- PG:查看列统计摘要
SELECT attname, n_distinct, most_common_vals, histogram_bounds, correlation
FROM pg_stats WHERE tablename = 'orders';
-- MySQL:查看索引基数(估算值)
SHOW INDEX FROM orders;
常见误区
- 误区:统计信息就是表的精确行数。正解:统计是采样估算的分布摘要,含基数、直方图等;精确行数需实时 count。
- 误区:索引建好后统计信息自动永远准确。正解:数据持续变更后统计会过期,需按机制自动或手动更新。
追问
- 问:没有统计信息时优化器怎么估算?答:退化为默认假设(如固定选择率),估算偏差大;新表/未分析表尤其明显。
- 问:统计信息存在哪里?答:引擎内部系统表/目录中,随表与索引维护;具体存储见 05.2.1 / 05.3.1。
05.1.3.4-002 直方图、MCV 与密度怎么参与选择率估算?
频率 ★★★★☆ | 难度 进阶
一句话结论
**MCV(最常见值列表)**覆盖高频值的精确频率,直方图覆盖其余值的区间分布,密度/基数用于区分度与重复度估算;优化器按谓词类型在这些统计上查选择率,估算命中行数。
概念解释
- MCV:记录出现频率最高的若干值及其占比;等值条件直接查其频率。
- 直方图:把值域分桶,记录各桶行数;范围条件按桶位置估算比例。
- 密度:与基数相关,衡量值重复程度;用于连接与等值估算。
- 局限:单列统计建模不了列间关联;长尾与极端分布可能估计不准。
原理流程
- 等值条件:查 MCV 命中值频率;不在 MCV 则按密度/直方图估算。
- 范围条件:按直方图桶位置估算区间占比。
- 选择率 × 表行数 = 估算输出行数。
代码 / 命令
-- PG:查看 MCV 与直方图边界
SELECT attname, most_common_freqs, histogram_bounds
FROM pg_stats WHERE tablename = 'orders' AND attname = 'status';
常见误区
- 误区:直方图桶越多估算一定越准。正解:桶多增加统计存储与采样成本;长尾分布仍可能偏差,关键看采样代表性。
- 误区:MCV 能覆盖所有等值条件。正解:只记录最常见值;罕见值频率按密度/假设估算,可能偏差。
追问
- 问:范围条件选择率为什么容易估偏?答:数据在区间内不均匀(倾斜、空值集中),直方图桶粒度不足以反映局部密度。
- 问:连接估算用什么统计?答:结合两侧基数、密度与连接键匹配度;列间相关与数据倾斜会放大连接估算误差。
05.1.3.4-003 MySQL 的统计信息是怎么收集的?
频率 ★★★☆☆ | 难度 进阶
一句话结论
InnoDB 统计信息可基于采样索引页估算行数与基数,有持久化与非持久化两种模式;自动重算受变更比例阈值触发,也可手动 ANALYZE TABLE;具体参数与行为随版本,边界标「待确认」。
概念解释
- 采样页:不遍历全表,抽样若干索引页估算基数与分布。
- 持久化统计:统计存于系统表,重启不丢失;非持久化在内存,重启重建。
- 自动重算:数据变更达阈值后自动更新;参数控制采样页数与是否自动重算。
- 手动更新:
ANALYZE TABLE主动刷新。
原理流程
- 打开表/首次访问时读取统计;数据变更达阈值触发重算。
- 采样索引页估算行数、基数与分布。
- 优化器基于采样统计估算选择率。
代码 / 命令
-- 手动更新统计
ANALYZE TABLE orders;
-- 查看统计相关参数(版本相关,以当前版本文档为准)
SHOW VARIABLES LIKE 'innodb_stats_persistent';
SHOW VARIABLES LIKE 'innodb_stats_auto_recalc';
SHOW VARIABLES LIKE 'innodb_stats_persistent_sample_pages';
常见误区
- 误区:MySQL 统计是全表精确统计。正解:基于采样页估算,大表采样不足时基数与分布可能偏差。
- 误区:
ANALYZE TABLE会锁表很久。正解:其行为随版本与表类型不同;大表执行前应评估影响窗口,具体见 05.2.4。
追问
- 问:采样页数调大有什么影响?答:统计更接近真实分布,但收集成本与内存占用上升;需权衡。
- 问:非持久化统计重启后会怎样?答:重启后重新采样估算,期间计划可能波动;持久化模式更稳定。
05.1.3.4-004 PostgreSQL 的统计信息是怎么收集的?
频率 ★★★☆☆ | 难度 进阶
一句话结论
PG 由 ANALYZE(自动 autovacuum 触发或手动)采样收集列统计:基数、MCV、直方图、相关性,可配置统计目标;扩展统计支持多列相关性,信息存于系统目录供优化器使用。
概念解释
- 采样收集:
ANALYZE采样行构建统计摘要,不遍历全表。 - 统计目标:
default_statistics_target及列级ALTER COLUMN ... SET STATISTICS,控制直方图桶数。 - 扩展统计:多列相关性、表达式统计,弥补单列统计不足。
- 自动触发:autovacuum 在表变更达阈值时顺带
ANALYZE。
原理流程
- 表数据变更达阈值,autovacuum 触发
ANALYZE,或手动执行。 - 采样并写入 pg_statistic 等系统目录。
- 优化器查询时读取统计估算选择率。
代码 / 命令
-- 手动收集
ANALYZE orders;
-- 提高某列统计目标(直方图更细)
ALTER TABLE orders ALTER COLUMN created_at SET STATISTICS 1000;
-- 查看统计目标与摘要
SELECT attname, n_distinct, correlation FROM pg_stats WHERE tablename = 'orders';
常见误区
- 误区:PG 统计随每次写入实时更新。正解:统计在
ANALYZE时批量采样更新;两次分析之间数据分布变化不会即时反映。 - 误区:调大统计目标对所有列都有益。正解:高目标增加统计存储与分析成本;只对估算偏差大的列单独调高。
追问
- 问:autovacuum 为什么顺带做分析?答:回收死元组与更新统计是两类维护工作,合并触发减少扫描开销;机制详见 05.3.1。
- 问:扩展统计怎么建?答:对常组合过滤的列建多列扩展统计,记录列间相关性;语法见 PG 文档。
05.1.3.4-005 统计信息什么时候需要更新?怎么更新?
频率 ★★★★☆ | 难度 进阶
一句话结论
大批量导入/删除、数据分布明显变化、计划估算与实际偏差大时更新统计;MySQL 用 ANALYZE TABLE,PG 用 ANALYZE;更新后重跑计划对比估算是否收敛。
概念解释
- 触发时机:批量数据变更后、计划行数估算偏差大、索引选择异常、版本/结构变更后。
- 更新动作:手动命令触发采样重算。
- 验证:更新后对比计划
rows与实际行数是否接近。 - 自动机制:引擎按变更阈值自动重算,但大批量变更后常需手动补一次。
原理流程
- 识别统计过期迹象:估算偏差、选错路径。
- 低峰期执行统计更新命令。
- 重跑计划核对估算;偏差仍在则检查列相关/扩展统计。
代码 / 命令
-- MySQL
ANALYZE TABLE orders;
-- PostgreSQL
ANALYZE orders;
-- 全库分析(谨慎,评估窗口):ANALYZE;
常见误区
- 误区:统计更新后计划立即全部刷新。正解:计划缓存是否重建随引擎机制;必要时重跑语句观察新计划。
- 误区:每次小批量写入后都要手动分析。正解:自动机制覆盖常规变更;只在大批量变更或偏差明显时手动补分析。
追问
- 问:更新统计会阻塞业务吗?答:采集以采样为主,但大表仍有 CPU/IO 开销;应在低峰执行,具体锁与资源影响见引擎章节。
- 问:更新统计解决不了估算偏差怎么办?答:检查列间相关性、复杂表达式条件、NULL 分布;考虑扩展统计或 SQL 改写。
05.1.3.4-006 统计信息过期为什么会导致选错索引?
频率 ★★★★★ | 难度 进阶
一句话结论
统计过期后优化器对命中行数、区分度与回表代价估算失真:低估行数可能选嵌套循环差计划,高估行数可能放弃有效索引选全表扫描;更新统计后估算修正,索引选择往往恢复正常。
概念解释
- 估算偏少:以为命中很少行,选嵌套循环/索引;实际行数大,内层反复扫描膨胀。
- 估算偏多:以为回表行数多,放弃索引走全表;实际命中很少,索引更优。
- 基数失真:列区分度评估错误,索引价值被高估或低估。
原理流程
- 计划中对比估算与实际行数,定位偏差节点。
- 判断偏差方向:偏少/偏多。
- 更新统计后重跑计划;若仍偏差,检查列相关与条件复杂度。
代码 / 命令
-- 更新统计后重跑计划对比
ANALYZE TABLE orders; -- MySQL
ANALYZE orders; -- PG
EXPLAIN <SQL>;
EXPLAIN (ANALYZE, BUFFERS) <SQL>;
常见误区
- 误区:选错索引一定是索引设计问题。正解:先排除统计过期与估算偏差;统计修正后原索引可能被正常选中。
- 误区:更新统计就能让所有计划变快。正解:统计只修正估算;索引缺失、条件无法定位等问题更新统计解决不了。
追问
- 问:如何判断是统计问题还是索引问题?答:更新统计后计划恢复即为统计问题;估算准确仍走差路径,则检查索引设计与条件写法。
- 问:大批量插入后为什么容易出现统计偏差?答:新数据分布未被采样纳入,自动重算未达阈值前估算仍基于旧分布。
05.1.3.4-007 单列统计建模不了列间相关性,怎么办?
频率 ★★★☆☆ | 难度 高级
一句话结论
两列取值相关(如城市与省份、类别与子类别)时,独立估算各条件选择率再相乘会低估组合命中行数;可用扩展统计(多列相关性/依赖度)补充建模,或改写 SQL 降低对组合估算的依赖。
概念解释
- 独立假设:优化器默认各列条件独立,选择率相乘;列相关时该假设失真。
- 相关性偏差:相关列组合实际命中行数多于独立估算,中间结果集被低估。
- 扩展统计:记录多列函数依赖与多元 MCV,供优化器组合估算。
- 改写手段:拆分查询、增加强过滤条件、人工固定部分计划。
原理流程
- 识别常组合过滤且业务上相关的列。
- 建扩展统计收集列间依赖。
- 重跑计划核对组合估算行数;仍偏差则评估改写。
代码 / 命令
-- PG:建多列扩展统计(示例,语法以当前版本为准)
CREATE STATISTICS stat_city_prov (dependencies, mcv)
ON city, province FROM users;
ANALYZE users;
常见误区
- 误区:给每列都调高统计目标就能解决组合偏差。正解:单列目标改善不了列间相关;需要多列扩展统计。
- 误区:列相关只影响估算,不影响真实数据。正解:估算失真会导致连接方式与索引选错,进而影响真实执行代价。
追问
- 问:MySQL 有等价扩展统计吗?答:机制与支持程度随版本不同,边界行为待确认;以当前版本文档为准。
- 问:哪些列典型相关?答:省/市/区、类别/子类别、产品型号/品牌等层级从属列。
05.1.3.4-008 什么是参数嗅探?为什么会导致计划不稳定?
频率 ★★★★☆ | 难度 高级
一句话结论
参数嗅探指优化器用首次绑定的参数值估算分布并生成计划,后续不同参数复用该计划;当数据分布倾斜、参数对应行数差异大时,通用计划对部分参数更差,表现为同一 SQL 时快时慢。
概念解释
- 参数化计划:语句文本参数化后计划可复用。
- 嗅探值偏差:首次参数代表的分布不具代表性,生成的计划不适合其他参数。
- 倾斜放大:热点参数命中少量行、冷门参数命中大量行,最优计划本应不同。
- 表现:缓存计划在两类参数间复用,一批快一批慢。
- 引擎差异:参数嗅探是 SQL Server 的经典术语;MySQL 对预处理语句复用执行计划,PostgreSQL 顶层默认不缓存通用计划(PL/pgSQL 函数内才缓存),倾斜导致的计划次优在各引擎表现形式不同,具体实现见 05.2 / 05.3。
原理流程
- 首次执行用当前参数值估算行数与代价,生成计划入缓存。
- 后续参数复用计划。
- 倾斜数据下复用计划对新参数次优;统计/参数变化后计划重生成可能切换到另一极端。
代码 / 命令
-- 倾斜示例:status='PAID' 命中绝大多数行,'REFUND' 命中极少行
-- 参数化语句被两类参数复用同一计划,其中一类次优
SELECT * FROM orders WHERE status = :s AND user_id = :uid;
-- 排查:对比不同参数下的计划与实测行数;必要时按查询形态拆分或评估计划稳定性方案
常见误区
- 误区:参数嗅探是 bug,关掉参数化就好。正解:它是计划复用的常规机制;问题出在数据倾斜与通用计划不匹配,需结合统计与计划策略处理。
- 误区:慢的那批参数单独加索引就能解决。正解:先确认是否复用了错误计划;计划切换后再评估索引是否匹配。
追问
- 问:计划提示能否缓解参数嗅探?答:可在特定语句上固定路径做对照;长期依赖提示需评估数据变化后的僵化风险。
- 问:哪些场景容易出现参数嗅探问题?答:状态/类型等低区分度列、分布倾斜明显、参数化复用频繁的报表与列表查询。
05.1.3.4-009 优化器为什么会放弃一个看起来可用的索引?
频率 ★★★★☆ | 难度 进阶
一句话结论
优化器按代价判断:命中行数占比高、回表随机 IO 代价大、索引区分度低或统计偏差时,会认为全表扫描/其他路径更便宜而放弃该索引;"存在索引"不等于"优化器选择它"。
概念解释
- 选择率高:条件命中大部分行,回表次数多,顺序扫描更划算。
- 索引低区分度:单值命中行多,索引过滤收益不足。
- 统计偏差:估算行数失真导致代价误判。
- ** covering 缺失**:需大量回表放大索引路径代价。
原理流程
- 查看计划:
possible_keys有候选而key未选中。 - 核对命中行数与占比、回表代价、统计新鲜度。
- 判断放弃是否合理:合理则接受,不合理则更新统计或改写/调整索引。
代码 / 命令
EXPLAIN SELECT * FROM orders WHERE status = 'PAID';
-- possible_keys 含 idx_status,key 为 NULL 或 ALL:
-- 先查分布:SELECT status, count(*) FROM orders GROUP BY status;
常见误区
- 误区:优化器不用索引就是优化器有问题。正解:很多时候是数据分布下全表扫描代价更低;应先确认计划是否合理。
- 误区:强制用回放弃的索引就能提速。正解:若全表扫描本就更优,强制索引在数据增长后更慢;需先核对选择率与统计。
追问
- 问:命中比例到多少索引通常不划算?答:经验拐点随缓存、磁盘与行宽变化,约一成到两成;不能套固定阈值,以计划代价与实测为准。
- 问:索引被长期放弃要不要删掉?答:先确认该索引无其他查询使用;长期零收益且增加写入代价,可评估下线。
05.1.3.4-010 计划提示(强制索引等)什么时候用,有什么风险?
频率 ★★★☆☆ | 难度 进阶
一句话结论
计划提示用于临时验证或已知次优计划下固定访问路径;风险是数据分布变化后被锁定的路径可能变成最差选择,且提示会掩盖优化器能力问题,应优先用统计治理与 SQL 改写解决。
概念解释
- 提示类型:指定索引、连接顺序、连接算法、计划缓存策略等。
- 适用场景:排查对照、优化器明确误选、短期兜底。
- 僵化风险:数据增长后固定路径不再最优,且不易察觉。
- 维护成本:提示绑定具体索引/结构,Schema 变更后可能失效。
原理流程
- 确认优化器选型次优且统计/改写无法解决。
- 用提示做对照实验,对比两版计划与实测。
- 评估长期保留必要性;优先根因修复,提示作为兜底并记录。
代码 / 命令
-- MySQL:指定索引(示意语法)
SELECT * FROM orders FORCE INDEX (idx_user_id)
WHERE user_id = 1001;
-- PG:连接顺序/计划相关提示通过配置或语法指导,具体以版本文档为准
常见误区
- 误区:生产环境长期堆满强制索引提示很正常。正解:提示应是少数兜底;大量提示说明索引/统计/改写治理不到位。
- 误区:加了提示就不用再看计划。正解:提示锁定路径后仍需随数据增长复核,避免路径过时。
追问
- 问:提示与统计更新冲突时谁优先?答:提示通常强制固定路径,统计更新不会改变被提示锁定的部分。
- 问:如何评估一条提示是否该下线?答:观察对应 SQL 计划与实测;优化器已能稳定选优后,逐步移除提示并复测。
05.1.3.4-011 统计信息更新后,旧计划为什么还可能被复用?
频率 ★★★☆☆ | 难度 高级
一句话结论
计划缓存中的计划在统计更新后不一定立即失效;缓存命中时仍沿用旧计划,直到缓存淘汰、语句重优化或手动触发重生成。统计修正后若观察不到计划变化,需确认缓存是否刷新。
概念解释
- 计划缓存:语句签名对应已生成计划,复用避免重复优化。
- 失效时机:统计变更、结构变更、缓存淘汰等触发重优化;具体机制随引擎。
- 表现:更新统计后计划看起来"没变化"。
- 验证:重跑语句并观察计划;必要时清缓存/重连(评估影响)。
原理流程
- 更新统计信息。
- 检查缓存计划是否基于旧统计。
- 触发重优化后对比新计划;缓存行为边界随版本,标「待确认」以实测为准。
代码 / 命令
-- 重跑语句观察计划是否切换
EXPLAIN <SQL>;
-- 缓存清理/重优化手段随引擎不同,评估生产影响后再执行
常见误区
- 误区:执行
ANALYZE后所有缓存计划立刻重算。正解:缓存复用策略由引擎控制;旧计划可能继续命中一段时间。 - 误区:计划没变就是统计更新失败。正解:也可能缓存未刷新;先确认失效机制再判断统计是否生效。
追问
- 问:如何确认计划是否基于最新统计?答:对比更新前后估算行数;重优化后估算应向实际行数收敛。
- 问:手动清缓存有什么风险?答:缓存失效后首批语句重新优化,短期 CPU 与延迟上升;生产需低峰评估。
05.1.3.4-012 采样方式如何影响统计精度?
频率 ★★★☆☆ | 难度 高级
一句话结论
统计基于采样而非全表遍历:采样页数/行数不足、数据分布不均或新数据未覆盖时,基数与直方图偏差放大;增大采样目标、对关键列单独调高统计目标可改善,但要权衡采集成本。
概念解释
- 采样粒度:MySQL 采样索引页;PG 采样行构建直方图/MCV。
- 偏差场景:数据倾斜、冷热分区、批量新数据未被采样。
- 可调目标:采样页数、列级统计目标。
- 成本权衡:采样越大越准,但分析耗时与存储增加。
原理流程
- 定位估算偏差大的列与条件。
- 核对采样是否覆盖代表性数据分布。
- 适当调大采样/统计目标,重分析后对比估算。
代码 / 命令
-- PG:单列提高统计目标
ALTER TABLE orders ALTER COLUMN user_id SET STATISTICS 500;
ANALYZE orders;
-- MySQL:采样页数参数(版本相关,见 05.1.3.4-003)
常见误区
- 误区:采样统计与全表统计精度一样。正解:采样是分布摘要,极端分布与长尾必然存在误差。
- 误区:调大采样目标对所有表都该做。正解:只对估算反复偏差的关键列调整;全局调高会普遍增加分析成本。
追问
- 问:刚批量导入的表为什么统计最容易不准?答:新行尚未被采样纳入,自动分析未触发;导入后手动
ANALYZE补一次。 - 问:采样偏差与列间相关偏差如何区分?答:单列估算就偏差大属采样/分布问题;单列合理、组合行数低估属列间相关问题。
05.1.3.4-013 索引基数(cardinality)怎么看,低基数索引意味着什么?
频率 ★★★☆☆ | 难度 进阶
一句话结论
索引基数是索引键的不同值数量;基数越低区分度越差,单值命中行数越多,索引过滤价值越低。查看索引基数并与表行数对照,判断索引是否值得保留。
概念解释
- 基数:索引中 distinct 键值数量(统计估算)。
- 区分度:基数 / 总行数,比例越高越好。
- 低基数表现:命中行多、回表代价高,优化器倾向放弃。
- 动态变化:基数是统计值,需结合数据分布复核。
原理流程
- 查看索引基数与表行数。
- 计算区分度,判断过滤力度。
- 低基数单列索引评估下线或改为联合索引一部分。
代码 / 命令
-- MySQL
SHOW INDEX FROM orders; -- Cardinality 列为估算基数
-- PG
SELECT indexrelname, indisunique FROM pg_indexes;
-- 结合 pg_stats 的 n_distinct 查看列区分度
常见误区
- 误区:基数是精确实时值。正解:基数来自采样统计,可能与真实 distinct 数量有偏差。
- 误区:基数低的索引一律删除。正解:低基数列在联合索引、覆盖索引中仍可能有价值;先看服务的查询模式。
追问
- 问:基数高但从不被查询的索引要保留吗?答:不需要;索引价值 = 被使用的过滤收益,基数高只是必要条件。
- 问:基数统计过时会怎样?答:优化器误判区分度,可能错选或错弃索引;更新统计后复核。
05.1.3.4-014 哪些谓词最容易让选择率估算偏差?
频率 ★★★☆☆ | 难度 高级
一句话结论
范围条件、LIKE、函数/表达式条件、NULL 判断、多条件组合与连接键匹配最易估偏:分布不均匀、表达式不在统计覆盖范围、列间相关未建模,都会使选择率假设失真。
概念解释
- 范围条件:直方图桶粒度不足,局部密度不均。
- 模糊/函数:表达式值无对应统计分布。
- NULL 与罕见值:MCV 未覆盖,按默认假设估算。
- 组合与连接:独立假设相乘低估相关列组合;连接匹配度估算偏差。
原理流程
- 在计划中找估算与实际偏差最大的谓词。
- 归类偏差来源:分布、表达式、NULL、相关或连接。
- 对应更新统计、扩展统计、表达式索引或改写条件。
代码 / 命令
-- 复杂表达式条件:无普通统计覆盖
SELECT * FROM orders WHERE LOWER(remark) LIKE '%vip%';
-- 范围 + 多列组合:易低估组合行数
SELECT * FROM orders WHERE city = '昆明' AND level = 5;
常见误区
- 误区:等值条件选择率一定估得准。正解:罕见值、NULL、MCV 未覆盖时等值也可能偏差。
- 误区:估算偏差只影响计划,不影响真实结果。正解:计划选错会真实拉长执行时间;偏差是性能问题的前置信号。
追问
- 问:如何定位是哪个谓词导致偏差?答:逐层看计划节点行数变化,从表扫描到各过滤节点对比估算与实际。
- 问:表达式条件如何获得统计支持?答:建表达式索引/扩展统计,让优化器对表达式值有分布信息。
05.1.3.4-015 数据库版本升级后计划为什么会变?
频率 ★★★☆☆ | 难度 高级
一句话结论
版本升级可能带来优化器代价模型、统计机制、连接算法或默认参数变化,同一 SQL 生成不同计划;升级前后应在测试环境对比关键 SQL 计划与性能,评估回归并保留回退方案。
概念解释
- 模型变化:代价系数、选择率估算逻辑调整。
- 新算法:新增连接/半连接策略或索引类型支持。
- 默认参数:统计目标、成本常数、缓冲区等默认值变化。
- 回归风险:旧版本次优计划可能在新版本被选中,反之亦然。
原理流程
- 升级前采集关键 SQL 清单与基线计划/耗时。
- 测试环境升级后重跑对比计划与指标。
- 对回归语句核对统计、参数与新优化器行为,必要时调整提示/索引。
代码 / 命令
升级前:导出 Top SQL、计划、耗时基线
升级后:同数据规模重跑 → 计划/行数/耗时对比 → 回归语句单独分析
常见误区
- 误区:升级只兼容语法,执行计划不会变。正解:优化器行为随版本演进,计划可能切换;需做性能回归验证。
- 误区:新版本计划一定更优。正解:新模型对某些数据分布可能次优;以实测对比为准,不预设结论。
追问
- 问:升级后计划变差怎么临时兜底?答:评估版本相关的计划策略参数或提示;长期仍需修正索引与统计。
- 问:哪些 SQL 最该纳入回归对比?答:高频、高耗时、复杂连接与大表查询;低频小查询优先级低。
05.1.3.4-016 计划漂移(plan flip-flop)怎么治理?
频率 ★★★☆☆ | 难度 高级
一句话结论
计划漂移指同一 SQL 在两个(或多个)计划间反复切换,性能随之波动;常见诱因是统计在两个分布估计间摆动、参数分布倾斜与缓存复用。治理思路是稳定统计、明确数据分布、消除导致次优计划的条件,必要时固定计划。
概念解释
- 漂移表现:同一模板 SQL 间歇性快/慢,计划在索引扫描与全表扫描等之间切换。
- 诱因:统计波动、参数嗅探、数据增长改变拐点、缓存计划反复失效重建。
- 治理顺序:先稳定统计与数据分布,再评估计划固定手段。
- 观测:按 SQL 模板记录历史计划与耗时,识别切换点。
原理流程
- 按模板收集不同时间的计划与实测,确认是否在多计划间切换。
- 排查统计更新节奏、参数倾斜与缓存策略。
- 稳定估算与数据分布;必要时用提示固定已验证的优计划并记录复核周期。
代码 / 命令
观测:SQL 模板 × 时间 × 计划类型 × 实际行数 × 耗时
→ 定位切换触发条件(统计更新 / 参数取值 / 数据量变化)
→ 治理:稳定统计、补索引/改写、(兜底)固定计划
常见误区
- 误区:漂移靠重启数据库解决。正解:重启只清空缓存,统计与分布诱因仍在,计划会再次切换。
- 误区:发现漂移就立刻强制固定计划。正解:先确认哪个计划长期更优及其适用条件;固定计划可能在数据变化后僵化。
追问
- 问:如何判断两个计划哪个该保留?答:在代表性参数与数据规模下对比实测耗时、扫描行数与资源;不能只看单次结果。
- 问:统计更新节奏会诱发漂移吗?答:会;两次分析间分布变化使估算在拐点两侧摆动,计划随之切换。
05.1.3.4-017 收集统计信息会对业务造成什么影响?
频率 ★★★☆☆ | 难度 进阶
一句话结论
统计收集需要采样扫描与 CPU/IO 开销,大表分析期间占用资源、可能影响并发;应在低峰执行,控制分析范围与频率,避免全库同时批量分析。
概念解释
- 资源开销:采样扫描页/行、构建直方图与 MCV,消耗 IO 与 CPU。
- 锁与并发:通常不长期锁表,但大表分析仍有缓冲与资源压力;具体锁行为随引擎。
- 批量风险:多张大表同时分析会叠加资源峰值。
- 自动与手动:autovacuum/自动重算分散负载;手动分析需择期。
原理流程
- 评估待分析表规模与业务窗口。
- 低峰期分批分析,避免全库一次性跑。
- 观察资源指标;大表考虑延长自动分析间隔或分批策略。
代码 / 命令
-- 单表分析(低峰执行)
ANALYZE orders;
-- 避免:业务高峰期对所有大表同时 ANALYZE
常见误区
- 误区:统计收集完全无锁无影响。正解:虽不等同于锁表,但采样扫描与 CPU 开销真实存在,高负载时段执行会叠加压力。
- 误区:为了计划稳定把分析频率拉到最高。正解:过度分析增加持续开销;按变更量与偏差情况调整频率。
追问
- 问:大表分析耗时过长怎么办?答:评估采样目标、分批时段;结合引擎维护窗口与在线维护方案,见 05.1.4.5。
- 问:自动分析延迟会不会影响白天业务?答:自动触发时机与阈值可配置;关键表可在低峰手动补分析。
05.1.3.4-018 MySQL 与 PostgreSQL 统计机制有什么对照?
频率 ★★★★☆ | 难度 进阶
一句话结论
两者都基于采样构建分布统计供代价优化器使用,但统计粒度、可调目标与扩展能力不同:MySQL 偏重索引页采样与基数,PG 提供列级直方图/MCV/相关性与多列扩展统计;具体参数随版本,边界以官方文档为准。
概念解释
| 维度 | MySQL InnoDB | PostgreSQL |
|---|---|---|
| 采集方式 | 采样索引页估算 | 采样行构建统计摘要 |
| 统计内容 | 行数、索引基数、分布摘要 | 基数、MCV、直方图、相关性 |
| 可调目标 | 采样页数、持久化/自动重算参数 | 列级 STATISTICS、扩展统计 |
| 更新触发 | 自动重算阈值 / ANALYZE TABLE |
autovacuum 触发 / ANALYZE |
| 多列建模 | 支持程度随版本,待确认 | 扩展统计(依赖、MCV) |
原理流程
- 通用主线:统计 → 选择率 → 行数估算 → 代价选型;机制差异不影响这条主线。
代码 / 命令
-- MySQL
ANALYZE TABLE orders;
SHOW INDEX FROM orders;
-- PostgreSQL
ANALYZE orders;
SELECT attname, n_distinct, correlation FROM pg_stats WHERE tablename = 'orders';
常见误区
- 误区:两库统计命令语义完全一致。正解:
ANALYZE TABLE与ANALYZE都更新统计,但内部采样、存储与可调项不同。 - 误区:PG 有扩展统计就不需要索引设计了。正解:扩展统计改善估算,访问路径仍依赖合理索引与查询写法。
追问
- 问:跨库迁移 SQL 计划会一致吗?答:统计口径与代价模型不同,计划可能不同;迁移后需在目标库重新验证计划。
- 问:统计相关问题在哪个分册深入?答:MySQL 细节见 05.2.2,PostgreSQL 细节见 05.3.2。
参考来源
- MySQL 8.0 Reference Manual: Optimizer Statistics:https://dev.mysql.com/doc/refman/8.0/en/optimizer-statistics.html
- MySQL 8.0 Reference Manual: InnoDB Persistent Statistics:https://dev.mysql.com/doc/refman/8.0/en/innodb-persistent-stats.html
- PostgreSQL Documentation: Statistics Used by the Planner:https://www.postgresql.org/docs/current/planner-stats.html
- PostgreSQL Documentation: Extended Statistics:https://www.postgresql.org/docs/current/ddl-extended-statistics.html