数据库的统计信息收集与查询优化器决策机制

描述
统计信息是数据库优化器进行查询优化的核心依据，它描述了表中数据的分布特征，如行数、列的唯一值数量、数据分布直方图等。优化器基于这些统计信息估算不同执行计划的成本，从而选择最优方案。若统计信息不准确或过时，优化器可能选择低效的执行计划，导致查询性能下降。因此，统计信息的收集、维护和利用是数据库性能调优的关键环节。

一、统计信息的内容与作用

1. 核心统计指标：

   • 表级信息：总行数（num_rows）、数据块数量（blocks）。
   • 列级信息：
     • 唯一值数量（num_distinct），用于判断列的选择性。
     • 空值比例（num_nulls），影响连接和聚合操作。
     • 数据分布直方图（histogram），记录数据分布倾斜情况（如年龄列大量集中在20-30岁）。
   • 索引信息：索引的聚簇因子（clustering_factor，衡量数据有序程度）、索引层级（blevel）等。

2. 优化器如何利用统计信息：

   • 计算谓词条件的选择性（例如 WHERE age > 30，通过直方图估算满足条件的行数比例）。
   • 比较连接操作的代价（如哈希连接 vs. 嵌套循环连接）。
   • 决定索引是否有效（若索引列数据分布倾斜，可能优先全表扫描）。

二、统计信息的收集方法

1. 自动收集：

   • 数据库通常提供后台任务（如Oracle的GATHER_STATS_JOB）自动收集统计信息。触发条件包括：
     • 表数据变更超过一定比例（例如10%）。
     • 索引创建或重建后。
   • 优缺点：自动化程度高，但可能无法覆盖所有场景（如紧急批量数据加载后需立即更新）。

2. 手动收集：

   • 使用数据库内置命令，例如Oracle的DBMS_STATS.GATHER_TABLE_STATS：

     EXEC DBMS_STATS.GATHER_TABLE_STATS(  
         ownname => 'SCOTT',  
         tabname => 'EMP',  
         estimate_percent => 20,  -- 采样20%的数据  
         method_opt => 'FOR ALL COLUMNS SIZE AUTO'  -- 自动决定直方图桶数  
     );  
     

   • 关键参数说明：
     • estimate_percent：采样比例，平衡精度与性能。
     • cascade：同步收集索引统计信息。
     • degree：并行度，加速大表收集。

三、统计信息不准的优化器决策案例
假设表orders有1000万行，但统计信息未更新，仍显示为1000行。

1. 场景：执行查询 SELECT * FROM orders WHERE order_date > SYSDATE - 30。
2. 问题：
   • 优化器基于过时统计信息（1000行）误判结果集很小，可能选择嵌套循环连接或索引扫描。
   • 实际数据量庞大，本应使用全表扫描或哈希连接更高效。
3. 解决方案：
   • 手动更新统计信息：

     EXEC DBMS_STATS.GATHER_TABLE_STATS('SH', 'ORDERS', estimate_percent=>100);  
     

   • 验证更新效果：查询DBA_TAB_STATISTICS确认行数已更正。

四、统计信息的高级管理策略

1. 动态采样（Dynamic Sampling）：

   • 当统计信息缺失时，优化器临时采样少量数据（如1%）估算成本。
   • 适用场景：临时表、复杂谓词条件。但会增加解析时间。

2. 锁定统计信息：

   • 对数据稳定的表，手动锁定防止自动刷新：

     EXEC DBMS_STATS.LOCK_TABLE_STATS('SCOTT', 'DEPT');  
     

3. 增量统计信息收集：

   • 针对分区表，仅刷新变更的分区，减少全局收集开销。

五、实践建议

1. 定期监控统计信息健康度（如通过DBA_TAB_MODIFICATIONS查看数据变更量）。
2. 对ETL作业，在加载后显式调用统计信息收集。
3. 测试环境与生产环境统计信息需保持一致，避免执行计划差异。

通过以上步骤，统计信息不再是黑盒，而是可主动管理的优化工具，直接赋能查询优化器的智能决策。