文章目录每日一句正能量1. 背景与问题12亿行已经导完优化器却可能还“不认识”这张表2. 环境与数据统计信息重建不能所有表“一把梭”2.1 先把表分级S0 小表S1 中型表S2 大表S3 超大表2.2 统计信息不是越多越好2.3 迁移前需要保存旧系统“计划基线”3. 复现过程为什么ANALYZE一次以后计划还是可能不稳定3.1 场景一全量后完全没有统计信息3.2 场景二统计有但数据倾斜没采准3.3 场景三两个列强相关单列统计仍估错3.4 场景四统计更新后计划真的可能改变3.5 场景五热点参数和普通参数不应该只测一个3.6 场景六分区/继承结构要关注父级统计4. 方案实施迁移后统计信息重建的标准步骤4.1 第一步全量和大索引操作完成后再做最终统计基线4.2 第二步先采关键表4.3 第三步大表先采关键列4.4 第四步对倾斜列提高统计目标4.5 第五步不要全局提高default_statistics_target替代列级治理4.6 第六步建立扩展统计4.7 第七步必要时调整n_distinct但必须谨慎4.8 第八步计划验证统一使用同参数组4.9 第九步统计变化后重新检查缓存计划4.10 第十步计划稳定不等于永远固定同一个计划5. 结果对比统计信息重建到底要验收什么5.1 先看估算误差5.2 再看执行计划5.3 再看运行时间5.4 热点参数要单独验收5.5 示例计划对比5.6 采集成本也要记录5.7 数据一致性仍然要验证6. 风险与复盘统计信息越“精细”不代表计划越“稳定”6.1 风险一全库ANALYZE制造维护洪峰6.2 风险二采样本身有随机性6.3 风险三统计目标过高6.4 风险四自动ANALYZE不代表迁移后无需人工动作6.5 风险五统计正确但计划仍慢6.6 风险六参数敏感误判为统计不足6.7 风险七人工n_distinct长期失效回退方案统计信息变更也要可审计、可撤销如果提高统计目标后计划变差如果扩展统计造成不预期计划计划缓存回退性能回退门禁最终复盘附录 A大表关键列采集示例附录 B列级统计目标附录 C扩展统计附录 D计划对比附录 E最低统计信息验收清单每日一句正能量别人的光亮并不会将我们衬托的灰暗反而有可能把我们照亮。在黑暗中一盏灯点亮另一盏只会让世界更亮。靠近优秀的人不是为了模仿而是被他们的光芒照见自己的潜能。互为背景也互为光芒。一个优秀的朋友、一段高质量的对话都像一束光让我们看清自己未曾发现的潜力。被照亮不是被比下去而是被启迪、被唤醒。主题优化器统计信息 / 大表系统 / 迁移后性能稳定重点ANALYZE、统计目标、数据倾斜、扩展统计、多列相关性、分区/继承统计、计划缓存、执行计划对比与回退适用场景MySQL、SQL Server、Oracle 等大型系统迁移至 KingbaseES 后功能正确但执行计划波动、慢 SQL 增多、热点参数性能不稳定的场景。1. 背景与问题12亿行已经导完优化器却可能还“不认识”这张表数据库迁移完成后很容易出现一种误判12亿行数据已经全部导入 索引已经创建 COUNT一致 应用查询也能执行于是认为数据库已经准备好上线但对成本优化器来说数据存在和知道数据怎么分布是两回事。KingbaseES 使用基于成本的优化器。优化器选择Seq Scan Index Scan Nested Loop Hash Join Merge Join Sort Aggregate并不是凭规则硬编码而是依靠表行数 页面数 列基数 NULL比例 最常见值 直方图 多列关系估算每种执行路径的成本。如果全量刚导完统计信息缺失或者统计还是空表/小数据时的旧值优化器就可能认为实际300万行 ≈预计30行接着选Nested Loop再循环几百万次。因此迁移后一个非常关键但经常被忽略的步骤是重建目标库统计信息并证明新的统计信息确实让关键 SQL 的基数估算与执行计划稳定下来。KingbaseES 官方优化器统计文档明确指出良好查询性能的重要前提是正确统计信息陈旧统计可能导致低效执行计划官方ANALYZE文档也说明查询规划器会利用采集后的统计信息选择有效执行路径。所以数据迁移完成不能直接进入性能验收中间应该有Statistics Rebuild阶段。2. 环境与数据统计信息重建不能所有表“一把梭”示例环境目标 KingbaseES V9 trade_order 12亿行 820GB payment 8亿行 460GB customer_master 1.2亿行 120GB audit_log 90亿行 1.6TB 迁移 全量 KFS增量 Top SQL 约200条 核心参数化SQL 80条如果直接ANALYZE;意味着当前数据库所有有权限对象都被处理。KingbaseES 官方文档明确提醒不带参数执行ANALYZE可能处理整个数据库 运行时间可能很长 一般不建议无差别执行对大型表可以ANALYZEtable(column,...);只采集热点列。这对 TB 级系统非常重要。2.1 先把表分级推荐按大小 业务关键度 SQL调用频率 数据倾斜程度划分。S0 小表例如1GB可以ANALYZEapp.dict_table;完整采集。S1 中型表例如1GB~50GB一般完整 ANALYZE。热点列如果分布复杂提高statistics targetS2 大表例如50GB~1TB先识别WHERE JOIN GROUP BY ORDER BY涉及列。例如ANALYZEapp.trade_order(tenant_id,customer_id,order_status,created_at);先把 Top SQL 需要的信息补齐。S3 超大表例如audit_log 1.6TB如果只有created_at event_type参与核心检索就没有必要为了迁移验收对几十个低价值文本字段同步提高统计精度。统计采集也是成本。2.2 统计信息不是越多越好官方ANALYZE文档说明default_statistics_target决定默认统计目标。列级还可以ALTERTABLE...ALTERCOLUMN...SETSTATISTICSn;统计目标提高后最常见值列表更大 直方图桶更多 采样量更大精度一般提升。代价ANALYZE时间 CPU 统计目录空间都会增加。所以不要全库SET STATISTICS 10000这种粗放做法。2.3 迁移前需要保存旧系统“计划基线”源数据库统计结构和 KingbaseES 不一定一一对应。所以不能简单说把源统计信息复制过来更实用的是保存Top SQL 源P95/P99 源返回行数 源计划摘要 参数分布迁移后重新在 KingbaseES采统计 生成计划然后做行为级对比目标不是计划长得一模一样而是估算合理 性能达标 结果一致3. 复现过程为什么ANALYZE一次以后计划还是可能不稳定3.1 场景一全量后完全没有统计信息SQLSELECT*FROMtrade_orderWHEREcustomer_id?ORDERBYcreated_atDESCLIMIT20;实际customer_id10001 有18000行优化器估算15行于是Nested Loop SortP95165ms执行ANALYZEapp.trade_order;以后估算约17500计划Index Scan LimitP9538ms这就是最直接的统计修复。3.2 场景二统计有但数据倾斜没采准order_statusNORMAL 99.7% FAILED 0.2% MANUAL 0.1%默认采样如果对某些极端分布表达不足热点 SQL 可能估算偏差。可以ALTERTABLEapp.trade_orderALTERCOLUMNorder_statusSETSTATISTICS500;ANALYZEapp.trade_order(order_status);然后重新比较NORMAL FAILED MANUAL三个参数计划。注意提高统计目标不是因为“500比100高级”。而是因为这列真的需要更细的分布描述3.3 场景三两个列强相关单列统计仍估错客户province上海 city上海两者不是独立随机变量。如果优化器分别估计province选择率 × city选择率可能严重低估。KingbaseES 官方优化器统计文档明确提供扩展统计信息用于处理多列关系。支持的方向包括函数依赖 多元N-Distinct 多元MCV可以创建统计对象后再ANALYZE;实际统计数据才会被采集。3.4 场景四统计更新后计划真的可能改变官方ANALYZE文档明确提醒统计信息变化可能导致规划器选择不同计划这本身不是异常。统计更新的目的就是让优化器重新认识数据但这意味着ANALYZE不是“零风险维护操作”在核心系统里它也应该有前后计划验证。不能凌晨全库ANALYZE 早上发现20个SQL计划变了却完全没有基线。3.5 场景五热点参数和普通参数不应该只测一个同 SQLWHEREtenant_id?普通租户2万行超级租户320万行普通参数最优Index Scan超级租户可能更适合Bitmap 或 Seq Scan如果 prepared statement 使用缓存计划同一个计划未必适合所有参数KingbaseES 官方执行计划缓存文档说明可以使用plan_cache_mode控制auto force_generic_plan force_custom_plan当表定义、函数定义、统计信息等变化时相关缓存计划会自动失效也可以使用DISCARD PLANS等方式主动清理缓存计划。因此统计信息重建之后还要检查参数化SQL而不是只跑字面量 SQL。3.6 场景六分区/继承结构要关注父级统计官方ANALYZE文档说明如果表有子表会涉及父表自身 以及父子继承树两套统计视角。而自动分析触发时某些情况下只依据父表自身的变更来决定是否触发。如果父表自身几乎不变 子分区每天大量新增自动统计可能不能完全满足你的计划稳定需求。所以分区/继承类大表必须单独设计统计维护4. 方案实施迁移后统计信息重建的标准步骤4.1 第一步全量和大索引操作完成后再做最终统计基线KingbaseES 官方统计文档建议的主动采集时机包括大量数据装载后 CREATE INDEX后 大量INSERT/UPDATE/DELETE后 估算不准确时同时明确提醒不要在大量装载、DML、CREATE INDEX仍进行时同步做ANALYZE因为操作完成后数据状态还会继续变化。所以迁移步骤建议全量装载 ↓ 创建/重建关键索引 ↓ KFS增量追平到稳定状态 ↓ ANALYZE ↓ 计划回归而不是全量一边导 ANALYZE一边跑 索引又在建4.2 第二步先采关键表按照P0交易 P0支付 P0客户 P1重要业务 P2日志分批。不要让1.6TB audit_log阻塞order API性能验收。4.3 第三步大表先采关键列热点 SQLWHEREtenant_id?ANDorder_status?ANDcreated_at?ORDERBYcreated_atDESC关键列tenant_id order_status created_atJoincustomer_id这些优先。而一个从不参与过滤、排序和连接的大文本remark无需为了迁移验收花同等统计成本。4.4 第四步对倾斜列提高统计目标默认目标100是否足够用实际计划证明而不是凭经验。例如ALTERTABLEapp.trade_orderALTERCOLUMNorder_statusSETSTATISTICS500;再ANALYZEapp.trade_order(order_status);比较estimated rows actual rows是否明显收敛。4.5 第五步不要全局提高default_statistics_target替代列级治理全局调大所有列成本都增加而真正需要的是少数高倾斜热点列所以大系统通常优先列级SET STATISTICS全局参数要有充分依据。4.6 第六步建立扩展统计如果查询tenant_id business_type强相关。单列统计始终无法解释。使用CREATE STATISTICS建立多列统计对象。官方文档说明CREATE STATISTICS只创建目录对象 真正数据仍由ANALYZE采集所以不能创建完就立刻认为生效。正确CREATE STATISTICS ↓ ANALYZE ↓ EXPLAIN ANALYZE4.7 第七步必要时调整n_distinct但必须谨慎官方ANALYZE文档说明采样情况下distinct估算可能不精确如果这种误差持续造成不良计划可以手工为列配置n_distinct但这属于高级人工干预必须建立变更原因 实际基数证据 回退值 复核周期否则一年后数据分布变了人工值就成为新的陈旧统计。4.8 第八步计划验证统一使用同参数组每条关键 SQL 至少热点参数 普通参数 长尾参数 空结果参数分别保存估算行数 实际行数 计划 P50/P95/P99不要只测试一个幸运参数4.9 第九步统计变化后重新检查缓存计划统计信息更新后相关计划可能失效并重新生成这是正常的。但生产 prepared statement 还要验证generic plan custom plan差异。对明显参数敏感 SQL可以在测试环境比较plan_cache_modeauto force_generic_plan force_custom_plan不要直接生产全局强制。4.10 第十步计划稳定不等于永远固定同一个计划业务数据会成长100GB →500GB →2TB选择性也会变化。今天最优Index Scan未来可能Seq Scan更合理。所以“计划稳定”真正含义不是永远Plan A而是在相近数据分布和参数分组下优化器估算足够准确关键SQL不会因为统计噪声频繁跳到明显更差的计划。5. 结果对比统计信息重建到底要验收什么5.1 先看估算误差例如 Q001统计前estimated15 actual18000 误差1200倍统计后estimated17500 actual18000这个变化比cost从123变成98更值得关注。5.2 再看执行计划统计前Nested Loop Sort统计后Index Scan Limit说明更准确的基数改变了计划决策。5.3 再看运行时间Q001Before P95165ms After P9538msQ003Before920ms After430ms这才是真正业务效果。5.4 热点参数要单独验收Q001 普通38ms热点520ms不能因为热点比普通慢就 FAIL。应该比较源热点基线 目标热点基线以及SLA热点本来处理320万行500ms可能是合理结果。5.5 示例计划对比SQL参数统计前估算/实际统计后估算/实际P95变化Q001普通15 / 1800017500 / 18000165→38msQ001热点15 / 320万290万 / 320万2400→520msQ002普通1000 / 950980 / 95082→80msQ003倾斜500 / 38万36万 / 38万920→430ms这些为方法示例数据不是本文声称的生产实测。5.6 采集成本也要记录统计目标从100 →1000如果ANALYZE耗时3倍但关键 SQL 只提升1%就不一定值得。报告需要同时记录采集耗时 CPU IO 统计目录增长 计划收益5.7 数据一致性仍然要验证统计信息本身不改变业务数据但统计重建通常伴随着索引 SQL改写 参数一起优化。所以最终性能验收必须确认row_count PK集合 排序 金额 NULL 分页没有变化。6. 风险与复盘统计信息越“精细”不代表计划越“稳定”6.1 风险一全库ANALYZE制造维护洪峰TB级数据库一次ANALYZE;可能运行非常久。即使只持有读锁也会消耗CPU IO 缓存所以按关键度分批。6.2 风险二采样本身有随机性ANALYZE采样不是100%全表精确统计。官方文档甚至提示在少数情况下ANALYZE后计划可能非决定性改变提高统计目标可以减少这种风险但会增加成本。所以关键 SQL 要前后计划验证而不是假设“ANALYZE一定更好”。6.3 风险三统计目标过高高目标意味着更多采样 更大MCV/直方图 更慢ANALYZE 更多统计空间必须用于真正需要的列6.4 风险四自动ANALYZE不代表迁移后无需人工动作自动分析主要根据表修改量阈值触发。迁移全量、分区加载、特殊窗口的行为可能需要主动ANALYZE尤其性能验收前。6.5 风险五统计正确但计划仍慢如果estimated≈actual计划仍然慢。就不要继续疯狂加统计。下一步检查索引 SQL Join Sort IO 锁统计信息只是计划输入之一。6.6 风险六参数敏感误判为统计不足普通值Index Scan最好热点值Seq Scan最好这不是统计错误。而是数据分布真实不同需要参数分组 缓存计划策略 SQL路径治理。6.7 风险七人工n_distinct长期失效手工值是承诺不是自适应事实。必须定期复核否则它会反过来欺骗优化器。回退方案统计信息变更也要可审计、可撤销统计本身不是业务数据所以通常不需要数据回滚但围绕统计做的statistics target 扩展统计 缓存计划策略 SQL 索引都需要回退。建议每次调优记录query_id table column old_statistics_target new_statistics_target extended_statistics old_plan new_plan old_p95 new_p95 plan_cache_mode如果提高统计目标后计划变差流程1. 停止继续扩大变更 2. 保存新计划和参数 3. 恢复原SET STATISTICS 4. 重新ANALYZE相关列 5. 必要时清理缓存计划 6. 用相同参数组重测如果扩展统计造成不预期计划不要同时删扩展统计 改索引 改SQL 改work_mem一次改四件事。应单变量回退才能知道根因。计划缓存回退KingbaseES 官方文档说明统计信息变化会使相关缓存计划自动失效必要时也可使用产品支持的缓存计划清理命令。但清缓存属于运行态操作必须评估硬解析成本和连接范围。不要把DISCARD PLANS当成万能性能修复。性能回退门禁关键SQL P95 源端120% 关键SQL P99持续超SLA 估算误差显著扩大 错误率上升 CPU/IO进入危险区 写TPS明显下降则停止扩大统计/计划调整恢复上一稳定组合。最终复盘迁移后的统计信息治理不应该是全库ANALYZE一次 完成真正合理的流程是Top SQL驱动 ↓ 表/列分级 ↓ 基础ANALYZE ↓ 发现估算偏差 ↓ 倾斜列提高统计目标 ↓ 强相关列使用扩展统计 ↓ 参数分组验证 ↓ 计划缓存验证 ↓ P95/P99验收 ↓ 长期自动主动维护如果只记住一句话统计信息的目标不是“尽可能详细”而是让优化器在关键SQL、关键参数和当前数据分布下做出足够准确、足够稳定的代价判断。这也是为什么迁移后不能只问ANALYZE执行了吗而应该问关键SQL的estimated rows和actual rows接近了吗 计划选择稳定了吗 热点参数还会不会抖 性能达到源端基线了吗只有这些问题回答清楚大表系统的迁移性能验收才真正闭环。附录 A大表关键列采集示例ANALYZEapp.trade_order(customer_id,order_status,created_at,tenant_id);附录 B列级统计目标ALTERTABLEapp.trade_orderALTERCOLUMNorder_statusSETSTATISTICS500;ANALYZEapp.trade_order(order_status);实际目标值应通过估算精度和采集成本测试确定。附录 C扩展统计CREATESTATISTICSst_customer_regionONprovince,cityFROMapp.customer_master;ANALYZEapp.customer_master;具体语法和统计类型以实际 KingbaseES 版本为准。附录 D计划对比EXPLAIN(ANALYZE,BUFFERS,VERBOSE)SELECT...;重点保存estimated rows actual rows scan join sort buffers execution time附录 E最低统计信息验收清单[ ] 全量装载完成 [ ] 关键索引创建完成 [ ] P0/P1表统计已采集 [ ] Top SQL热点列已覆盖 [ ] 倾斜列统计目标已验证 [ ] 强相关列扩展统计已评估 [ ] 分区/继承统计已确认 [ ] 热点/普通/长尾参数均已测试 [ ] estimated vs actual误差已记录 [ ] P95/P99达到门禁 [ ] 计划缓存策略已验证 [ ] 回退值已记录转载自https://blog.csdn.net/u014727709/article/details/163781055欢迎 点赞✍评论⭐收藏欢迎指正