企业官网建设流程全解析

优化器选错执行计划，90% 是统计信息的锅。三种数据库都提供统计信息，但直方图类型、多列统计、自动采集策略完全不同。本文从「优化器的眼睛」角度，拆解三库统计信息机制的差异。

前言：优化器为什么需要「看」数据

CBO 的核心是代价估算，而代价估算的前提是知道数据长什么样。举个例子：

SELECT*FROMusersWHEREgender='M';

如果gender列 95% 是 ‘M’，全表扫描可能是正确的；如果只有 1% 是 ‘M’，那走索引才合理。优化器只能通过统计信息来做出这个判断。

统计信息回答的核心问题有三个：

1. 有多少数据？——表的行数、块数
2. 数据怎么分布？——列的唯一值数、分布直方图
3. 列之间有什么关联？——多列联合分布、函数依赖

这三种数据库都实现了统计信息，但精度和深度完全不同。今天就逐一拆解。

一、Oracle：统计信息体系的「豪华配置」

Oracle 的统计信息收集能力在三大数据库中最为成熟，几乎是「什么都能统计」。

1.1 基础统计信息

Oracle 通过DBMS_STATS包收集以下基础信息：

-- 收集表的统计信息BEGINDBMS_STATS.GATHER_TABLE_STATS(ownname=>'HR',tabname=>'EMPLOYEES',estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE,method_opt=>'FOR ALL COLUMNS SIZE AUTO',cascade=>TRUE-- 同时收集索引统计);END;/

基础统计信息存储在数据字典中，可以通过以下视图查询：

其中聚簇因子（Clustering Factor, CF）是 Oracle 特有的概念——它衡量索引顺序与表中物理行顺序的匹配程度。CF 越接近表的块数，说明数据越「有序」，索引范围扫描效率越高。如果 CF 接近表的总行数，说明数据和索引完全「无序」——这是优化器偏好全表扫描的一个重要信号。

-- 查看索引的聚簇因子（越低越好）SELECTindex_name,clustering_factor,num_rowsFROMdba_indexesWHEREtable_name='EMPLOYEES';

1.2 直方图：让优化器「看见」数据倾斜

如果列数据是均匀分布的，只需要NUM_DISTINCT（NDV）就能准确估算基数。但现实中的数据几乎都有倾斜——比如 80% 的订单来自 20% 的客户。这时候就需要直方图。

Oracle 支持 4 种直方图类型（12c+）：

其中HYBRID直方图（12c 默认）是 Oracle 的改进重点——它结合了频率直方图和等高直方图的优点。对于高频值，它像 FREQUENCY 一样精确记录；对于低频值，它按范围聚合，节省存储空间。

-- 查看某列的直方图信息SELECTcolumn_name,histogram,num_buckets,num_distinctFROMdba_tab_col_statisticsWHEREtable_name='EMPLOYEES'ANDcolumn_name='DEPARTMENT_ID';-- 查看直方图的桶详情SELECTendpoint_number,endpoint_value,endpoint_number-LAG(endpoint_number,1,0)OVER(ORDERBYendpoint_number)ASfrequencyFROMdba_tab_histogramsWHEREtable_name='EMPLOYEES'ANDcolumn_name='DEPARTMENT_ID'ORDERBYendpoint_number;

1.3 扩展统计：多列关联和表达式统计

单列直方图有一个致命缺陷：它假设列之间是独立的。比如city和state高度相关（知道了城市就知道了州），但优化器如果只看到两列各自的直方图，会把两列的过滤率简单相乘，导致基数估算严重偏低。

Oracle 的解决方案是扩展统计（Extended Statistics）：

-- 1. 多列统计（Column Group）SELECTDBMS_STATS.CREATE_EXTENDED_STATS(ownname=>'HR',tabname=>'EMPLOYEES',extension=>'(CITY, STATE)'-- 列组)FROMdual;-- 2. 表达式统计（Expression Statistics）SELECTDBMS_STATS.CREATE_EXTENDED_STATS(ownname=>'HR',tabname=>'EMPLOYEES',extension=>'(UPPER(LAST_NAME))'-- 函数表达式)FROMdual;-- 查看已创建的扩展统计SELECTextension_name,extensionFROMdba_stat_extensionsWHEREtable_name='EMPLOYEES';

创建扩展统计后，Oracle CBO 在估算WHERE city='Beijing' AND state='BJ'时会使用列组的联合分布直方图，而不是把两个独立列的选择率相乘——这意味着更准确的基数估算。

1.4 动态采样与自动统计收集

Oracle 还有一个「兜底」机制——动态采样（Dynamic Sampling）：

-- 查看动态采样级别（默认 2，即 OPTIMIZER_DYNAMIC_SAMPLING=2）SELECTname,valueFROMv$parameterWHEREname='optimizer_dynamic_sampling';

动态采样的逻辑是：如果优化器发现某个表没有统计信息（比如刚创建的表、临时表），在生成计划时会实时扫描少量数据块来估算统计信息。虽然有额外开销，但总比瞎猜强。

日常统计维护方面，Oracle 默认启用了自动统计收集任务，每天夜间窗口运行：

-- 查看自动统计收集任务的运行历史SELECTjob_name,last_start_date,statusFROMdba_scheduler_jobsWHEREjob_nameLIKE'%GATHER_STATS%'ORjob_nameLIKE'%STATS%';-- 查看哪些表的统计信息过期了（变化超过 10%）SELECTtable_name,num_rows,stale_statsFROMdba_tab_statisticsWHEREstale_stats='YES';

经验之谈：生产系统中，不要等到自动任务发现统计过期了才收集。核心业务表（日增百万行以上）建议在 ETL 流程结束后立即执行DBMS_STATS.GATHER_TABLE_STATS，避免白天高峰期触发自动统计收集导致性能抖动。

二、PostgreSQL：透明但够用的统计体系

PostgreSQL 的统计信息存储在一张关键系统表pg_statistic中，通过pg_stats视图对外暴露。

2.1 基础统计信息

-- 查看一张表的列统计信息SELECTattname,null_frac,avg_width,n_distinct,most_common_vals,most_common_freqs,histogram_bounds,correlationFROMpg_statsWHEREtablename='employees';

关键字段解读：

PG 统计信息的一个重要特征是correlation值——它相当于 Oracle 的聚簇因子，但表达方式不同。correlation接近 1 或 -1 时，索引范围扫描效率高；接近 0 时，优化器更倾向位图扫描或全表扫描。

2.2 直方图与 MCV

PG 使用两种机制来统计列的数据分布：

• MCV（Most Common Values）：记录出现频率最高的值及精确频率。由default_statistics_target控制存储多少个最常见值（默认 100）；
• 等深直方图（Equi-Depth Histogram）：将除去 NULL 和 MCV 之后的剩余值，按出现次数分成 N 个等深桶，只记录每桶的边界值。桶数也由default_statistics_target控制。

-- 查看默认的统计目标（桶数）SHOWdefault_statistics_target;-- 默认 100-- 针对特定列提高统计精度ALTERTABLEemployeesALTERCOLUMNdepartment_idSETSTATISTICS1000;

default_statistics_target是一个重要的调优参数。默认值 100 对于大多数 OLTP 系统够用，但对于数据倾斜严重的列（比如 1000 万行中最常见的值占了 800 万行），提高这个值可以让优化器更精确地估算低频值的选择率。

2.3 扩展统计：解决列关联问题

PG 10+ 引入了与 Oracle 扩展统计类似的能力：

-- 创建多列依赖统计CREATESTATISTICSs_city_state(dependencies)ONcity,stateFROMemployees;-- 创建多列 NDV 统计（用于 GROUP BY 多列场景）CREATESTATISTICSs_dept_title(ndistinct)ONdepartment_id,job_titleFROMemployees;-- 创建 MCV 扩展统计（用于多列联合过滤场景）CREATESTATISTICSs_multi(mcv)ONcity,state,ageFROMemployees;-- 查看已创建的扩展统计SELECTstxname,stxkind,stxddependencies,stxdndistinctFROMpg_statistic_ext;

三种扩展统计类型：

创建扩展统计后，需要重新收集统计信息才会生效：

ANALYZEemployees;

2.4 自动收集：autovacuum 的另一面

PostgreSQL 的自动统计收集由autovacuum子系统负责（它的角色不仅仅是清理死元组）：

-- 查看 autovacuum 的统计收集配置SELECTname,settingFROMpg_settingsWHEREnameLIKE'autovacuum_analyze%';

关键参数：

规则：当修改行数 > threshold + 表总行数 × scale_factor时自动 ANALYZE。对于大表（1 亿行），需要变化 1000 万行才会触发——这可能在数据倾斜场景下不够及时。建议对频繁更新的核心大表手动调低autovacuum_analyze_scale_factor（比如 0.05）。

三、MySQL：从 5.7 到 8.0，统计信息的追赶之路

3.1 InnoDB 统计信息

MySQL 的统计信息由存储引擎层负责，InnoDB 的统计信息有两种模式：

-- 查看统计信息模式SHOWVARIABLESLIKE'innodb_stats%';

持久化统计信息存储在mysql.innodb_table_stats和mysql.innodb_index_stats表中：

-- 查看 InnoDB 持久化统计SELECT*FROMmysql.innodb_table_statsWHEREtable_name='employees';SELECT*FROMmysql.innodb_index_statsWHEREtable_name='employees';

一个重要陷阱：innodb_stats_persistent_sample_pages默认只有 20 页。对于几十 GB 的大表，20 页采样可能严重失准——优化器可能会「认为表很小」而选择全表扫描。大表建议调整为 200-500 页。

3.2 MySQL 8.0 直方图：终于来了

MySQL 8.0 是第一个支持直方图的版本（此前只能靠索引统计来间接估算）：

-- 为某列创建直方图ANALYZETABLEemployeesUPDATEHISTOGRAMONdepartment_idWITH100BUCKETS;-- 查看直方图信息SELECT*FROMinformation_schema.COLUMN_STATISTICSWHERESCHEMA_NAME='my_db'ANDTABLE_NAME='employees'ANDCOLUMN_NAME='department_id'\G

MySQL 支持两种直方图类型：

-- 查看直方图 JSON 内容（简化显示）SELECTJSON_PRETTY(HISTOGRAM)FROMinformation_schema.COLUMN_STATISTICSWHERECOLUMN_NAME='department_id'\G

直方图在创建后持久化到数据字典（重启不丢失），但统计信息不会自动更新。你需要定期手动执行ANALYZE TABLE ... UPDATE HISTOGRAM。

3.3 MySQL 统计的限制

相比 Oracle 和 PG，MySQL 的统计信息体系仍有显著差距：

1. 没有多列统计：无法处理列关联问题。WHERE city='X' AND state='Y'的基数估算仍然简单相乘；
2. 没有表达式统计：WHERE UPPER(name)='JOHN'这种场景优化器只能靠猜测；
3. 采样精度受限于页数：innodb_stats_persistent_sample_pages=20的默认值对 100GB+ 的表精度不足；
4. 没有自动过期检测机制：统计是否过期需要 DBA 自己判断和管理。

四、三库统计信息对比

五、生产环境统计信息最佳实践

Oracle

-- ETL 结束后立即收集（而非等自动任务）BEGINDBMS_STATS.GATHER_TABLE_STATS(ownname=>'HR',tabname=>'EMPLOYEES',estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE,method_opt=>'FOR ALL COLUMNS SIZE SKEWONLY',degree=>4,cascade=>TRUE);END;/

• AUTO_SAMPLE_SIZE：让 Oracle 自适应决定采样比例
• SIZE SKEWONLY：只对倾斜列建直方图，节省资源
• degree => 4：并行收集，适合大表

PostgreSQL

-- 核心表调高统计精度 + 降低自动分析阈值ALTERTABLEemployeesALTERCOLUMNdepartment_idSETSTATISTICS1000;ALTERTABLEemployeesSET(autovacuum_analyze_scale_factor=0.05);-- 创建多列扩展统计CREATESTATISTICSs_emp_multi(mcv)ONcity,state,department_idFROMemployees;ANALYZEemployees;

MySQL

-- 大表调高采样页数ALTERTABLEemployees STATS_PERSISTENT=1,STATS_SAMPLE_PAGES=200;-- 创建直方图并定期更新ANALYZETABLEemployeesUPDATEHISTOGRAMONdepartment_id,statusWITH100BUCKETS;

六、总结

三种数据库的统计信息体系差距明显：

• Oracle：最成熟。四种直方图 + 扩展统计 + 表达式统计 + 自动采样 + 过期标记。是「豪华版」统计体系；
• PostgreSQL：实用主义。MCV + 等深直方图 + 扩展统计（10+），暴露关键参数供 DBA 调节，清晰透明；
• MySQL：正在追。8.0 加入直方图是重要里程碑，但缺少多列统计和表达式统计，仍有差距。

一句话：统计信息是优化器的眼睛。如果数据倾斜严重（大部分现实场景都这样），直方图是必须的。在多列关联很强的表上，Oracle 和 PG 的扩展统计能显著改善 JOIN 顺序选择——MySQL 目前还只能靠手工 SQL 改写来弥补。