统计信息过期如何影响优化器判断——增长型大表的采集前后执行计划与参数实验实战

文章目录每日一句正能量1. 背景与问题优化器不是看真实数据做计划而是看“统计摘要”做判断2. 环境与数据为什么增长型大表最容易出现统计滞后2.1 为什么表越大“20%变化”越可怕2.2 表总行数增长只是第一层问题2.3 MCV为什么重要2.4 n_distinct为什么会影响GROUP BY和JOIN2.5 日期直方图特别容易在追加型表里过期3. 复现过程ANALYZE前后同一SQL从21秒降到3秒3.1 基线SQL3.2 采集前计划3.3 先确认统计是否陈旧3.4 单变量实验只执行ANALYZE3.5 ANALYZE后计划3.6 为什么ANALYZE后计划会变4. 方案实施普通ANALYZE还不够时怎么继续提高判断精度4.1 第一层建立“统计新鲜度”指标4.2 第二层超大表单独调整auto analyze阈值4.3 不要全库统一把scale factor调很低4.4 第三层提高关键列statistics target4.5 为什么不建议全局statistics target直接10004.6 第四层多列相关条件使用扩展统计4.7 创建扩展统计4.8 扩展统计解决什么问题4.9 第五层采集后做计划回归4.10 统计采集后的验证指标4.11 什么时候不要第一时间加Hint4.12 统计信息不是越“实时”越好4.13 超大追加表要结合业务波次4.14 分区表应关注“新分区”统计4.15 统计信息采样存在天然误差5. 结果对比统计采集本身就能让SQL从21.6秒降到3.4秒E0旧统计E1普通ANALYZEE2提高关键列statistics targetE3扩展统计5.1 汇总5.2 优化收益不只是Execution Time5.3 统计信息还会影响Hash内存5.4 统计信息也会影响GROUP BY5.5 P99更能暴露热点估算错误6. 风险与复盘统计信息采集不是“越多越好”而是要让优化器获得足够准确的世界模型6.1 风险一全库ANALYZE造成资源冲击6.2 风险二statistics target过高6.3 风险三只看last_analyze时间6.4 风险四自动ANALYZE阈值太低6.5 风险五扩展统计对象太多6.6 风险六统计更新后计划变化6.7 风险七出现回归就删除新统计推荐统计治理模型推荐诊断顺序回退方案最终复盘附录 A最小统计采集附录 B提高列统计目标附录 C扩展统计附录 D最低验收门禁每日一句正能量珍惜是柴米油盐里小心翼翼的呵护。珍惜不是空话是做饭时记得对方的口味是疲惫时递上的一杯热水。浪漫的真谛就藏在日复一日的琐碎与平淡中被一双珍视的眼睛看见被一双用心的手打捞起来。主题统计信息 / 增长型大表 / 优化器判断重点ANALYZE、estimated rows、actual rows、MCV、直方图、n_distinct、default_statistics_target、扩展统计、auto analyze、执行计划前后对比适用场景KingbaseES 中订单、流水、日志、交易明细、审计记录等持续快速增长的大表尤其适用于“SQL 没变、索引没变但某天开始突然变慢”的生产问题。1. 背景与问题优化器不是看真实数据做计划而是看“统计摘要”做判断数据库优化器在生成计划时并不会真的先执行SELECT COUNT(*)去确认每个条件会返回多少行。它必须在 SQL 真正运行之前根据表行数 页面数 NULL比例 n_distinct 最常见值 MCV 直方图 列相关性估算这个条件会返回多少行 这个 Join 两边有多大 应该走索引还是全表扫描 应该 Nested Loop 还是 Hash Join Hash 要准备多少内存 Sort 会处理多少行KingbaseES 官方优化器统计信息文档明确说明数据库采用基于成本的优化器 CBO而统计信息就是代价估算的基础。统计信息是通过采样收集的数据概览不是查询执行时的实时真相。因此统计信息一旦过期真正发生的事情不是数据库不知道表有多少行这么简单。而是优化器的整个成本模型开始建立在一个已经过时的数据世界里。例如统计采集时 表 1亿行 租户A占5% 两个月后 表 3亿行 新增数据大量属于租户A 租户A实际占30% 统计仍然认为 租户A≈5%那么查询WHEREtenant_idA优化器可能估150万实际却9000万这会直接影响Index Scan vs Bitmap/Seq Scan以及Nested Loop vs Hash Join的选择。KingbaseES 官方 SQL 调优资料也明确指出陈旧统计信息可能使优化器产生低效执行计划。本文的核心观点是统计信息过期不是一个“维护动作没做”的小问题而是优化器用于判断 Scan、Join、Aggregate、Sort 和内存成本的输入数据已经失真。2. 环境与数据为什么增长型大表最容易出现统计滞后示例系统数据库 KingbaseES V9 表 growing_order 初始 1亿行 两个月后 3亿行 每日新增 800万~1200万 热点租户 过去5% 现在30% 热点状态 status1 比例从20%增长到75% 最近日期 新增数据高度集中表结构CREATETABLEgrowing_order(order_idBIGINTPRIMARYKEY,tenant_idBIGINTNOTNULL,customer_idBIGINTNOTNULL,statusINTNOTNULL,biz_dateDATENOTNULL,amountNUMERIC(18,2));索引CREATEINDEXidx_growing_order_tenant_dateONgrowing_order(tenant_id,biz_date);CREATEINDEXidx_growing_order_tenant_status_dateONgrowing_order(tenant_id,status,biz_date);2.1 为什么表越大“20%变化”越可怕KingbaseES 官方自动清理参数文档说明自动 ANALYZE 的触发与autovacuum_analyze_threshold autovacuum_analyze_scale_factor × 表规模有关。默认参数中autovacuum_analyze_threshold 50 autovacuum_analyze_scale_factor 0.2意味着随着表越来越大按比例触发所需的变化行数也越来越大。如果表已经5亿行20% 就是1亿行即使业务每天新增1000万统计信息也可能在一段时间内无法完全反映最新分布。注意“新增1000万”并不等于一定立刻触发一次你期望的统计刷新。实际还受自动维护调度 数据库负载 表级设置 版本参数影响。因此增长型大表应该有自己的统计新鲜度策略而不是全部依赖默认阈值。2.2 表总行数增长只是第一层问题更危险的是分布发生改变例如过去tenant_id999999 只有10万订单某次业务迁入以后突然增加800万表总行数只增加4%看起来变化不大。但这个租户的选择性已经从极高选择性变成低选择性这会直接改变索引扫描是否还划算所以统计新鲜度不能只看“整表变了多少”还要看关键业务值的分布是否发生跃迁。2.3 MCV为什么重要假设tenant_id存在大量普通租户每个只有1万~5万行但一个超级租户800万如果超级租户进入Most Common Values优化器能对它使用更准确的频率。如果采样没有捕捉到或者旧统计里的频率已经过期优化器就可能用平均分布估算这对热点参数非常危险。2.4 n_distinct为什么会影响GROUP BY和JOINn_distinct描述某列大约有多少不同值它会影响GROUP BY组数 Join结果规模 选择率例如customer_id从1000万个distinct增长到6000万个但旧统计仍停留在1000万优化器对HashAggregate Hash Join内存和结果规模的估算都会受到影响。2.5 日期直方图特别容易在追加型表里过期订单、流水、日志这类表数据不断向时间轴右侧追加旧统计采集时最大日期2026-05-01现在已经到2026-08-01如果查询WHEREbiz_date2026-07-01统计信息没有及时更新就可能很难准确判断最近一个月到底有多少数据这就是典型Ascending Column / Append-only Distribution问题。3. 复现过程ANALYZE前后同一SQL从21秒降到3秒3.1 基线SQLSELECTo.order_id,o.customer_id,o.amountFROMgrowing_order oWHEREo.tenant_id:tenant_idANDo.status1ANDo.biz_date:start_dateANDo.biz_date:end_date;执行EXPLAIN(ANALYZE,BUFFERS,VERBOSE)SELECT...;热点参数tenant_id999999 status1 最近90天3.2 采集前计划示例Index Scan estimated rows18,000 actual rows8,200,000误差455倍如果后面继续 Joincustomer优化器可能认为只有1.8万行于是选择Nested Loop结果Inner loops 数百万P9521.6sBuffers Read980万这时很多 DBA 第一反应索引不行 Join算法不行但真正的第一个问题应该是为什么18000变成了820万3.3 先确认统计是否陈旧需要记录上次ANALYZE时间 表当前大概行数 上次统计时行数 近期INSERT/UPDATE/DELETE 热点参数增长不要只看SQL慢就直接执行CREATE INDEX3.4 单变量实验只执行ANALYZE这是整篇最重要的实验方法。不要同时改索引 改SQL 调work_mem 改Join开关只执行ANALYZEgrowing_order;然后同一SQL 同一参数 同一测试环境再次运行。3.5 ANALYZE后计划示例estimated rows6,900,000 actual rows8,200,000估算误差从455倍降到约1.19倍计划Index Scan Nested Loop变成Bitmap Scan Hash JoinP9521.6s →3.4sBuffers980万 →190万这里已经可以证明主要根因不是 SQL 突然写坏而是旧统计让优化器严重低估热点参数。3.6 为什么ANALYZE后计划会变因为优化器重新获得了表规模 热点频率 直方图 distinct NULL比例等数据概览。然后重新计算Index Scan成本 Bitmap Scan成本 Seq Scan成本 Join成本原来820万行在优化器世界里只有1.8万行现在终于接近真实值所以计划自然会改变。4. 方案实施普通ANALYZE还不够时怎么继续提高判断精度4.1 第一层建立“统计新鲜度”指标不能只监控CPU 连接数 锁还要监控last_analyze last_autoanalyze 表增长量 修改行数 reltuples对于关键大表建议定义Freshness SLA例如热点交易表 统计延迟不超过6小时 历史归档表 不超过7天不同表不能一刀切。4.2 第二层超大表单独调整auto analyze阈值示例ALTERTABLEgrowing_orderSET(autovacuum_analyze_threshold50000,autovacuum_analyze_scale_factor0.02);这是方法示例不是通用推荐值。如果表5亿行scale factor0.2对应比例变化量非常大。调整到0.02可以更早触发。但采集频率也会增加。所以必须平衡统计新鲜度 vs ANALYZE资源开销4.3 不要全库统一把scale factor调很低如果10000张表全部设置0.01可能导致后台ANALYZE频繁执行 CPU/IO增加正确方式按增长速度分层例如G0 静态维表 G1 普通OLTP表 G2 增长型大表 G3 热点超大事实表只对G2/G3做特殊策略。4.4 第三层提高关键列statistics targetKingbaseES 官方 ANALYZE 文档说明default_statistics_target控制默认统计信息量。也可以ALTERTABLE...ALTERCOLUMN...SETSTATISTICS...单独提高某列目标。例如ALTERTABLEgrowing_orderALTERCOLUMNtenant_idSETSTATISTICS500;然后ANALYZEgrowing_order;目的更多MCV 更多直方图桶 更大采样提高热点和长尾估算质量。4.5 为什么不建议全局statistics target直接1000因为统计目标越大采样更多 ANALYZE更慢 sys_statistic更大 规划时间也可能略增KingbaseES 官方文档明确指出提高统计目标是在估算精度 ANALYZE时间 统计目录空间之间做权衡。所以应该只提高关键列例如tenant_id biz_date status region_id4.6 第四层多列相关条件使用扩展统计查询WHEREtenant_id:tANDstatus1ANDbiz_date:d单列统计通常会把tenant_id status biz_date选择率近似组合。如果三个条件高度相关估算可能仍然错误KingbaseES 官方优化器统计文档明确指出单列统计无法表达多列之间的相关性因此提供dependencies ndistinct MCV扩展统计。4.7 创建扩展统计CREATESTATISTICSst_order_tenant_status_date(dependencies,ndistinct,mcv)ONtenant_id,status,biz_dateFROMgrowing_order;注意CREATE STATISTICS只是建立统计对象。官方文档明确说明真正的数据采集还必须执行 ANALYZE。所以接着ANALYZEgrowing_order;4.8 扩展统计解决什么问题例如超级租户的status1比例95% 普通租户20%单独看tenant_id和status都无法完整表达两者的关联关系扩展统计可以帮助优化器更好判断tenant status组合选择率。4.9 第五层采集后做计划回归ANALYZE不是做完就结束因为官方文档也提醒统计变化可能使规划器选择发生变化大多数情况下是改善。但对边界SQL也可能从一个计划切到另一个计划。所以统计采集要有计划回归至少检查Top SQL 核心接口 长查询 批处理4.10 统计采集后的验证指标每条关键 SQL 保存Plan Hash / Plan Shape estimated rows actual rows Buffers P95/P99 CPU Temp目标不是计划必须不变而是新计划是否更符合真实数据4.11 什么时候不要第一时间加HintKingbaseES SQL 调优资料提到当统计信息误差仍无法解决时可以考虑使用 Hint 控制执行计划。但顺序应该是统计新鲜度 ↓ statistics target ↓ 扩展统计 ↓ SQL/索引 ↓ 最后才考虑Hint如果一开始强制Nested Loop 强制Index Scan你只是把错误统计造成的症状固定住。数据再增长一次Hint很可能再次变成坏计划4.12 统计信息不是越“实时”越好每执行一批1000行INSERT就 ANALYZE 一次没有必要采样本身有成本。合理目标应该是统计变化足以影响计划时 及时更新而不是每秒和真实数据完全同步4.13 超大追加表要结合业务波次例如每天凌晨批量导入3000万最适合导入完成 ↓ 建/维护索引 ↓ ANALYZE ↓ 报表放量而不是等自动ANALYZE什么时候碰巧触发这和迁移后大批量导入完成需要手工 ANALYZE 的工程原则是一致的。4.14 分区表应关注“新分区”统计增长型事实表经常按月/日分区新分区刚创建 刚装载统计信息可能为空或很弱。应在批量加载完成后明确执行ANALYZE新分区/相关对象不要假设旧分区统计可以代表新分区。4.15 统计信息采样存在天然误差官方调优指南明确指出统计是采样得到的因此即使刚刚ANALYZE也不代表estimatedactual完全一致。我们真正关注的是误差是否足以改变计划例如estimated700万 actual820万通常可以接受。而estimated1.8万 actual820万就是灾难级偏差。5. 结果对比统计采集本身就能让SQL从21.6秒降到3.4秒E0旧统计estimated 18,000 actual 8,200,000 Plan Index Scan Nested Loop Buffers 980万 P95 21.6sE1普通ANALYZEestimated 6,900,000 actual 8,200,000 Plan Bitmap Scan Hash Join Buffers 190万 P95 3.4s已经6倍改善。E2提高关键列statistics targettenant_id: 500 biz_date: 500 status: 300重新 ANALYZE。示例estimated 7,900,000 actual 8,200,000 P95 2.8s热点值估算进一步改善。E3扩展统计建立tenant_id status biz_date相关性统计。重新 ANALYZE。示例estimated 8,100,000 actual 8,200,000 P95 2.3sPlan稳定5.1 汇总实验统计状态EstimatedActual计划P95E0过期1.8万820万IndexNested Loop21.6sE1ANALYZE690万820万BitmapHash3.4sE2高统计目标790万820万BitmapHash2.8sE3扩展统计810万820万稳定Hash路径2.3s以上均为方法演示数据不是生产实测。5.2 优化收益不只是Execution TimeBuffers980万 →150万说明数据库真实读取工作量下降Nested Loop数百万Inner loops消失。CPU下降因此这是计划质量真正改善而不是缓存刚好变热5.3 统计信息还会影响Hash内存如果优化器估Hash Build1万行实际500万那么内存预算 Batches 临时文件都会比预想糟糕。所以统计过期也会间接制造Hash Join落盘问题。这和前面的 Hash Join 文章并不是两个独立主题。根因链可能是统计过期 →低估Build →选Hash →内存不足 →多Batch →Temp爆炸5.4 统计信息也会影响GROUP BYestimated groups1000actual groups100万那么HashAggregate需要维护的状态量完全不同。所以统计治理应该覆盖Scan Join Aggregate Sort而不是只看索引扫描。5.5 P99更能暴露热点估算错误普通参数都估得准热点参数估错几百倍总体平均可能看起来不错真正出问题的是少量VIP/大租户所以要按参数组看 P95/P99。6. 风险与复盘统计信息采集不是“越多越好”而是要让优化器获得足够准确的世界模型6.1 风险一全库ANALYZE造成资源冲击大型数据库几TB 上万张表高峰期直接ANALYZE;可能带来明显 IO/CPU。应该按关键表 按波次 按维护窗口执行。6.2 风险二statistics target过高全局1000会增加采样 ANALYZE时间 统计目录 规划成本优先列级提高6.3 风险三只看last_analyze时间昨天刚 ANALYZE不代表今天就一定准确如果昨晚导入5000万热点数据统计已经再次失真。所以应该同时看时间 变化量6.4 风险四自动ANALYZE阈值太低scale factor太低会让超高频写表频繁 ANALYZE。资源成本可能超过收益。所以阈值要根据增长速度 表大小 查询重要性设计。6.5 风险五扩展统计对象太多所有列组合都建立不现实官方文档也指出列组合数量可能非常庞大因此扩展统计需要人工针对关键相关条件创建只服务真正影响计划的列组合6.6 风险六统计更新后计划变化ANALYZE 后计划改变不是异常。但需要Top SQL回归因为某些临界 SQL 可能改变 Join 顺序或访问路径。6.7 风险七出现回归就删除新统计这也是错误做法。新统计通常更接近真实数据如果某 SQL 因此变差应先检查成本模型 索引 SQL结构 参数敏感而不是第一时间恢复错误世界模型推荐统计治理模型对增长型大表建立表规模 DML增长 最近ANALYZE 关键列分布 Top SQL估算误差五维监控。可以定义S0 静态/低变化 S1 普通业务表 S2 高增长大表 S3 高增长高倾斜核心表S3更低auto analyze比例 关键列高statistics target 扩展统计 采集后计划回归这比全库统一参数更合理。推荐诊断顺序1. 保存慢SQL计划 2. 对比estimated/actual 3. 看表增长和最近ANALYZE 4. 检查热点参数/日期分布 5. 单变量执行ANALYZE 6. 对比计划和P95 7. 必要时提高列statistics target 8. 多列相关则CREATE STATISTICS 9. 再次ANALYZE 10. 固化auto analyze表级策略回退方案统计治理的回退和普通 SQL 回退不完全一样。如果调整statistics target auto analyze参数 扩展统计后出现回归1. 保存新旧计划和参数 2. 不要立即清除所有新统计 3. 若列statistics target过高恢复原值后重新ANALYZE 4. 若表级autovacuum参数不合适恢复原storage parameter 5. 若扩展统计被证明有问题记录证据后DROP并重新ANALYZE 6. 重测普通/热点/长尾参数最重要的是统计回退也必须保持单变量不能同时改SQL、索引、Hint。否则无法知道真正原因。最终复盘统计信息本质上是优化器对数据世界的压缩模型它不需要100%精确但必须足够准确到不会选错成本数量级对于增长型大表最危险的不是表从1亿变3亿本身。而是热点分布 日期分布 distinct 列相关性都已经变化优化器仍然使用过去的概率模型。如果只记住一句话统计信息过期真正破坏的不是“行数显示”而是优化器判断索引、扫描、Join、Hash、Sort 和 Aggregate 的整个成本基础当 estimated 与 actual 跨越几个数量级时应该先修正统计世界模型再去改 SQL 和索引。这也是为什么生产调优时EXPLAIN ANALYZE里的estimated rows vs actual rows永远是最值得先看的数据之一。附录 A最小统计采集ANALYZEgrowing_order;附录 B提高列统计目标ALTERTABLEgrowing_orderALTERCOLUMNtenant_idSETSTATISTICS500;ANALYZEgrowing_order;附录 C扩展统计CREATESTATISTICSst_order_tenant_status_date(dependencies,ndistinct,mcv)ONtenant_id,status,biz_dateFROMgrowing_order;ANALYZEgrowing_order;附录 D最低验收门禁[ ] 上次ANALYZE时间已记录 [ ] 表增长量已记录 [ ] 热点参数已覆盖 [ ] estimated/actual已比较 [ ] ANALYZE单变量实验已执行 [ ] statistics target有依据 [ ] 扩展统计仅用于关键相关列 [ ] 采集后Top SQL已回归 [ ] P95/P99达到SLA [ ] Buffer/CPU无异常回归 [ ] 自动采集阈值已文档化 [ ] 回退参数已保存转载自https://blog.csdn.net/u014727709/article/details/163950186欢迎 点赞✍评论⭐收藏欢迎指正