代价模型探秘：优化器凭什么选择这个执行计划？——直方图、基数估计与动态采样

代价模型探秘优化器凭什么选择这个执行计划——直方图、基数估计与动态采样同一个SQL同一张表昨天跑50ms今天跑5秒——执行计划变了。你认为加索引能解决但加了也没用。问题出在哪答案藏在优化器的代价模型里。这篇文章将带你穿透EXPLAIN的执行计划表象深入到优化器内部的决策过程用代价公式、基数估计和直方图这三个核心概念彻底搞懂“优化器到底在算什么”。引言那个“灵异”的执行计划跳变周二下午业务反馈一个订单统计报表从平时的200ms变成了4.8秒。SQL很简单SELECT*FROMordersWHEREorder_dateBETWEEN2026-07-01AND2026-07-31ANDstatus1ORDERBYorder_dateDESC;开发人员检查了索引——idx_status_datestatus, order_date明明存在。执行计划却显示走了全表扫描。“昨天晚上我们刚analyze过表啊”DBA困惑地说。问题出在统计信息的过时——不是表没分析而是数据的分布发生了剧烈变化但直方图没有捕捉到这种变化。优化器基于错误的统计信息估算出“全表扫描更便宜”于是选择了错误的执行计划。这篇文章将带你进入优化器的“黑盒”回答三个核心问题代价是什么怎么算——优化器的代价模型到底是什么怎么知道有多少行——基数估计如何依赖统计信息和直方图如何让优化器选对——何时需要手动干预如何干预读完本文你将从“经验主义加索引”升级到“量化分析调执行计划”。一、前置知识优化器是“会计”不是“先知”1.1 优化器的本质是成本核算很多DBA误以为优化器能“预知”最优执行计划。实际上优化器是一个基于模型的决策者——它在SQL编译阶段根据统计信息估算每个候选执行计划的代价然后选择估算代价最小的那一个。这个决策过程遵循一个简化公式总代价 IO代价 CPU代价 IO代价 ≈ (扫描的页数 × 页读取成本) CPU代价 ≈ (处理的元组数 × 行处理成本)关键问题是这些估算值是从哪来的答案统计信息Statistics。统计信息记录了表的行数n_live_tup/table_rows每列的不同值数量n_distinct/cardinalityNULL值比例数据分布直方图1.2 一个核心概念基数估计Cardinality Estimation基数估计是优化器最核心的职责——对于某个条件如status 1估算能返回多少行。这个估算直接决定了执行计划的选择估算行数少 → 走索引快速定位估算行数多 → 走全表扫描避免随机IO关键公式简单版估算行数 表总行数 × 选择率(条件)选择率的计算依赖于统计信息。例如对于status 1如果没有直方图优化器假设均匀分布选择率 1 / ndv(status) 1 / 3 ≈ 33% 估算行数 100万 × 33% ≈ 33万行但如果实际数据分布是status1占95%的数据那优化器就严重误判了。这就是数据倾斜导致优化器选错执行计划的根源。本节小结优化器通过代价模型比较候选执行计划而代价模型依赖统计信息中的基数估计。如果统计信息不准或数据分布不均衡优化器就可能选出糟糕的执行计划。理解这个链条是读懂优化器行为的前提。二、核心剖析代价模型的三大支柱2.1 支柱一统计信息Statistics——优化器的“眼睛”统计信息是优化器唯一的输入来源。如果统计信息是错的执行计划一定是次优的。MySQL的统计信息-- 查看表的统计信息SELECTtable_name,table_rows,avg_row_length,data_length,index_lengthFROMinformation_schema.TABLESWHEREtable_nameorders;-- 查看列的统计信息MySQL 8.0SELECT*FROMinformation_schema.COLUMN_STATISTICSWHEREschema_nametestANDtable_nameorders;统计信息的更新时机ANALYZE TABLE手动更新innodb_stats_auto_recalc自动触发当表变化超过10%时PostgreSQL的统计信息-- 查看表的统计信息SELECTrelname,reltuples,relpages,relallvisibleFROMpg_classWHERErelnameorders;-- 查看列的统计信息SELECTtablename,attname,n_distinct,null_fracFROMpg_statsWHEREtablenameorders;统计项含义对优化器的影响reltuples/table_rows表的行数估算扫描行数的基数n_distinct/cardinality列的不同值数量计算选择率null_fracNULL值比例影响IS NULL查询的估算avg_width平均列宽度影响IO代价的估算2.2 支柱二基数估计Cardinality Estimation——优化器的“算盘”基数估计是优化器最复杂的环节。在不同数据库中算法截然不同MySQL的基数估计算法MySQL的基数估算基于采样innodb_stats_persistent_sample_pages默认20页。估算行数 采样页中的匹配行数 / 采样页数 × 总页数 × (1 - null_frac)核心问题采样只有20页如果这20页恰好是数据分布的代表结果就准如果恰好是特殊情况如样本中全是status1结果就偏差极大。PostgreSQL的基数估计算法PG使用多变量统计和更复杂的采样策略。对于等值条件-- 查看PG的估算结果EXPLAINANALYZESELECT*FROMordersWHEREstatus1;-- 实际行数 950,000估算行数 950,000准确-- 或者实际行数 950,000估算行数 50,000偏差严重——数据倾斜PG vs MySQL的关键差异维度MySQLPostgreSQL采样策略固定20页采样基于列统计的自动采样直方图支持✅等深直方图MySQL 8.0✅等深直方图多列相关性❌ 不支持✅ 支持多变量统计动态采样❌ 不支持✅ 支持default_statistics_target2.3 支柱三直方图Histogram——对付数据倾斜的“秘密武器”当数据分布严重不均时简单的1/ndv估算会完全失效。直方图记录了列值的分布情况让优化器能更精确地估算选择率。2.3.1 等深直方图 vs 等宽直方图直方图类型原理优点缺点等深直方图每个桶包含相同数量的行能准确反映数据密度变化桶边界值可能重复等宽直方图每个桶覆盖相同的数值范围实现简单对数据倾斜敏感可能空桶或超载桶-- MySQL 8.0 创建直方图ANALYZETABLEordersUPDATEHISTOGRAMONstatus,order_dateWITH100BUCKETS;-- 查看直方图信息SELECT*FROMinformation_schema.HISTOGRAM_STATISTICSWHEREschema_nametestANDtable_nameorders;2.3.2 直方图是如何影响执行计划的场景status列有1、2、3三个值但分布极度不均——95%是13%是22%是3。没有直方图status 1: 估算行数 100万 × 1/3 ≈ 33万 → 走索引 status 2: 估算行数 100万 × 1/3 ≈ 33万 → 走索引 status 3: 估算行数 100万 × 1/3 ≈ 33万 → 走索引有直方图status 1: 估算行数 95万 → 全表扫描正确走索引反而慢 status 2: 估算行数 3万 → 走索引正确 status 3: 估算行数 2万 → 走索引正确什么时候需要创建直方图场景是否需要直方图原因列值均匀分布❌ 不需要简单估算已经足够列值严重倾斜✅必须不均匀分布会严重误导优化器列值大量重复但查询特定值✅ 需要例如订单状态、性别连续值范围查询如日期、年龄✅ 建议帮助优化器评估BETWEEN的范围选择率2.4 优化器的“运行日志”optimizer_traceMySQL和EXPLAIN (ANALYZE, BUFFERS)PGMySQLoptimizer_trace——优化器的“心路历程”-- 开启trace当前会话SEToptimizer_traceenabledon;SEToptimizer_trace_max_mem_size1000000;-- 执行要分析的SQLSELECT*FROMordersWHEREorder_dateBETWEEN2026-07-01AND2026-07-31ANDstatus1;-- 获取优化器决策过程SELECT*FROMinformation_schema.OPTIMIZER_TRACE\G关键JSON节点解读rows_estimation:{table_scan:{rows:1000000,// 全表扫描估算行数cost:202431// 全表扫描代价},potential_range_indexes:[{index:idx_status_date,chosen:true}],analyzing_range_alternatives:{range_scan_alternatives:[{index:idx_status_date,ranges:[status1 AND order_date BETWEEN ...],rows:52341,// 估算行数cost:10469// 索引扫描代价}]}},considered_execution_plans:[{plan_prefix:[],table:orders,best_access_path:{access_type:range,index:idx_status_date,cost:10469,chosen:true},cost_for_plan:10469,chosen:true}]诊断方法比较table_scan.cost和range_scan_alternatives[0].cost就能知道优化器为什么选择了某个计划。PostgreSQLEXPLAIN (ANALYZE, BUFFERS)——执行计划的“回放录像”EXPLAIN(ANALYZE,BUFFERS,VERBOSE)SELECT*FROMordersWHEREorder_dateBETWEEN2026-07-01AND2026-07-31ANDstatus1;输出示例Index Scan using idx_status_date on orders (cost0.56..10469.12 rows52341 width128) (actual time0.045..12.345 rows52341 loops1) Index Cond: ((order_date 2026-07-01::date) AND (order_date 2026-07-31::date) AND (status 1)) Buffers: shared hit1024 read256 Planning Time: 0.456 ms Execution Time: 12.567 ms关键信息解读cost0.56..10469.12启动代价0.56总代价10469.12rows52341估算行数actual time12.345 rows52341实际行数Buffers: hit1024 read256缓存命中/未命中情况如果rows和actual rows差异巨大如估算52341实际950000说明基数估计严重偏差需要更新统计信息或直方图。本节小结优化器的决策依赖于统计信息、基数估计和直方图三个支柱。optimizer_traceMySQL和EXPLAIN (ANALYZE, BUFFERS)PG是窥视优化器决策过程的窗口。如果rows和实际行数差异超过10倍执行计划大概率是错的需要手动干预。三、手把手实操从慢SQL到量化分析3.1 环境准备环境依赖MySQL 8.0本实操基于8.0.32测试表至少100万行数据分布不均拥有SUPER和PROCESS权限-- 创建测试表并灌入倾斜数据CREATETABLEorders(idINTPRIMARYKEYAUTO_INCREMENT,statusINT,order_dateDATE,amountDECIMAL(10,2),customer_idINT)ENGINEInnoDB;-- 灌入100万行数据95%的status1模拟数据倾斜INSERTINTOorders(status,order_date,amount,customer_id)SELECTCASEWHENRAND()0.95THEN1ELSEFLOOR(RAND()*3)1END,DATE_SUB(2026-08-01,INTERVALFLOOR(RAND()*365)DAY),ROUND(RAND()*1000,2),FLOOR(RAND()*10000)FROM(SELECT1UNIONSELECT2UNIONSELECT3UNIONSELECT4UNIONSELECT5)a,(SELECT1UNIONSELECT2UNIONSELECT3UNIONSELECT4UNIONSELECT5)b,(SELECT1UNIONSELECT2UNIONSELECT3UNIONSELECT4)c;-- 100行基数-- 重复执行直到100万行实际可用存储过程-- 创建索引CREATEINDEXidx_status_dateONorders(status,order_date);ANALYZETABLEorders;3.2 实操1观测统计信息过时导致的劣化执行计划-- Step 1: 查看当前执行计划EXPLAINSELECT*FROMordersWHEREstatus1ANDorder_dateBETWEEN2026-06-01AND2026-06-30;-- Step 2: 大量更新status1为status4模拟数据分布变化UPDATEordersSETstatus4WHEREstatus1ANDorder_date2026-01-01;-- 假设更新了约30万行-- Step 3: 不更新统计信息再次查看执行计划EXPLAINSELECT*FROMordersWHEREstatus1ANDorder_dateBETWEEN2026-06-01AND2026-06-30;-- 优化器可能仍然按照旧统计信息估算选择错误计划-- Step 4: 更新统计信息后对比ANALYZETABLEorders;EXPLAINSELECT*FROMordersWHEREstatus1ANDorder_dateBETWEEN2026-06-01AND2026-06-30;诊断方法-- 查看优化器估算的行数 vs 实际行数EXPLAINSELECT...;-- rows列是估算值SELECTCOUNT(*)FROMordersWHEREstatus1ANDorder_dateBETWEEN2026-06-01AND2026-06-30;-- 实际值3.3 实操2直方图对执行计划的影响-- Step 1: 没有直方图时的执行计划EXPLAINSELECT*FROMordersWHEREstatus1;-- 假设rows估算约为 33万1/3总行数实际为95万-- Step 2: 创建直方图MySQL 8.0ANALYZETABLEordersUPDATEHISTOGRAMONstatusWITH10BUCKETS;-- 查看直方图SELECTJSON_PRETTY(histogram)FROMinformation_schema.COLUMN_STATISTICSWHEREschema_nametestANDtable_nameordersANDcolumn_namestatus;-- Step 3: 创建直方图后再次查看执行计划EXPLAINSELECT*FROMordersWHEREstatus1;-- rows估算应接近95万走全表扫描EXPLAINSELECT*FROMordersWHEREstatus2;-- rows估算应接近3万走索引扫描输出对比场景status1的估算行数执行计划无直方图33万走索引错误有直方图95万全表扫描正确3.4 实操3optimizer_trace捕获决策全过程MySQL-- 开启traceSEToptimizer_traceenabledon;-- 执行SQLSELECT*FROMordersWHEREstatus1ANDorder_dateBETWEEN2026-06-01AND2026-06-30;-- 获取traceSELECTJSON_PRETTY(trace)FROMinformation_schema.OPTIMIZER_TRACE\G-- 关键搜索节点-- 1. rows_estimation → 看优化器估算的行数-- 2. range_scan_alternatives → 看每个索引方案的代价-- 3. considered_execution_plans → 看最终选择的执行计划常见诊断模式-- 比较row_estimation中的cost和range_scan_alternatives中的cost-- 如果range scan cost table scan cost优化器会放弃索引走全表扫描-- 如果rows估算偏差5倍说明统计信息或直方图需要更新3.5 排错常见统计信息问题及解决方案问题现象可能原因诊断方法解决方案执行计划突然变差统计信息过期对比rows和实际行数ANALYZE TABLE/VACUUM ANALYZEstatus1永远走全表扫描数据倾斜但无直方图查看HISTOGRAM_STATISTICSUPDATE HISTOGRAM ... WITH 100 BUCKETS持续变差autovacuum未触发SELECT * FROM pg_stat_all_tables调低autovacuum_scale_factor估算行数永远不准采样策略不合适检查innodb_stats_persistent_sample_pages增大采样页数JOIN估算偏差大多列相关性未考虑PG中的n_distinct统计不准确PG中创建CREATE STATISTICS多变量统计本节小结通过实操可以验证统计信息、直方图对执行计划的影响。核心诊断方法是对比估算行数和实际行数——偏差超过5-10倍时执行计划大概率错误。optimizer_traceMySQL能显示优化器做出决策的完整过程是调优的终极武器。四、进阶思考代价模型的“对手”——参数调优与动态采样4.1 统计信息更新策略InnoDB vs PostgreSQL数据库自动更新触发条件手动更新命令调优建议MySQL表变化超过innodb_stats_auto_recale阈值ANALYZE TABLE大表关闭自动更新用定时任务手动执行PostgreSQLautovacuum触发变化量 autovacuum_vacuum_thresholdscale_factor*reltuplesVACUUM ANALYZE高频更新表单独调低autovacuum_vacuum_scale_factorPG的典型配置调整-- 针对频繁更新的表降低autovacuum触发阈值ALTERTABLEordersSET(autovacuum_vacuum_scale_factor0.01);ALTERTABLEordersSET(autovacuum_vacuum_threshold1000);-- 查看下次autovacuum触发时机SELECTrelname,n_live_tup,n_dead_tup,n_dead_tup(autovacuum_vacuum_thresholdautovacuum_vacuum_scale_factor*n_live_tup)AStrigger_soonFROMpg_stat_all_tables;4.2 动态采样与手动HINT——当优化器“犯浑”时的最后手段当优化器持续选择错误计划时可以强制指定执行计划MySQL-- 强制使用索引SELECT*FROMordersFORCEINDEX(idx_status_date)WHEREstatus1ANDorder_dateBETWEEN2026-06-01AND2026-06-30;-- 或使用STRAIGHT_JOIN强制表顺序SELECTSTRAIGHT_JOIN...;PostgreSQL-- 强制使用索引扫描SETenable_seqscanOFF;-- 会话级禁用全表扫描SELECT*FROMordersWHEREstatus1ANDorder_dateBETWEEN2026-06-01AND2026-06-30;SETenable_seqscanON;-- 恢复-- 使用pg_hint_plan扩展CREATEEXTENSION pg_hint_plan;/* IndexScan(orders idx_status_date) */SELECT*FROMordersWHEREstatus1ANDorder_dateBETWEEN2026-06-01AND2026-06-30;4.3 优化器代价模型的演进发展阶段代表版本特征规则优化RBOMySQL 5.0-5.6基于启发式规则不考虑数据分布代价优化CBOMySQL 5.7、所有现代PG基于统计信息估算代价自适应优化MySQL 8.0、PG 12自适应哈希、动态采样、实时反馈本节小结统计信息的更新策略直接影响优化器决策质量。当自动更新不及时时需要手动干预或调整参数。当优化器持续选错时可以使用FORCE INDEX或HINT强制指定执行计划——但这应该是“最后手段”优先考虑更新统计信息或创建直方图。五、总结从“经验派”到“量化派”回到开篇的问题同一条SQL同样的表昨天快今天慢——原因不是索引变了而是统计信息变了。优化的目标不是“加索引”。优化的目标是让优化器做出正确的选择。要做到这一点你需要掌握读懂统计信息能查看information_schema.TABLES和pg_stats知道n_distinct、null_frac、cardinality意味着什么理解基数估计知道优化器是如何估算行数的以及数据倾斜为什么会导致估算偏差善用直方图知道何时需要创建直方图以及等深直方图如何解决数据倾斜问题使用诊断工具能用optimizer_traceMySQL和EXPLAIN (ANALYZE, BUFFERS)PG捕获优化器的决策过程量化分析当看到执行计划时能在几秒钟内判断“rows估算是否合理”、“cost与实际情况是否匹配”三个核心认知优化器不是万能的——它依赖于统计信息而统计信息是过时的快照。数据分布变化越快统计信息越可能不准确。直方图是应对数据倾斜的关键——在没有直方图的情况下优化器假设均匀分布这在高倾斜场景下会导致灾难性的错误估算。性能调优是科学不是玄学——每一次执行计划选择都有量化依据cost估算、rows估算。找到这个依据就找到了调优的入口。下次你遇到执行计划跳变时不要急着加索引。先问自己三个问题统计信息是最新的吗→ 检查ANALYZE/VACUUM ANALYZE是否执行数据分布是否有严重倾斜→ 检查是否需要直方图优化器的rows估算和实际rows差异有多大→ 用EXPLAIN ANALYZE验证真正懂优化的人不是知道“加哪个索引”而是知道“为什么优化器选了这个计划以及如何让优化器选对计划”。he_mode以及如何清空缓存重新生成计划。