数据库的统计信息与查询优化

描述

统计信息是数据库优化器的核心依据，用于估算查询成本、选择高效执行计划。它包含表的行数、列的基数（不同值的数量）、数据分布直方图等。若统计信息不准确，优化器可能选择全表扫描而非索引扫描，导致性能下降。

解题过程

1. 统计信息的类型与作用

• 表的统计信息：
  • 行数（n_rows）：影响连接顺序、访问路径选择。
  • 数据块数量：估算I/O成本。
• 列的统计信息：
  • 基数（n_distinct）：不同值的数量，用于估算选择率。
  • 空值比例：影响条件过滤的准确性。
  • 直方图（Histogram）：描述数据分布，解决数据倾斜问题（如年龄列集中在20-30岁）。
• 索引的统计信息：
  • 索引层级、叶子块数量：决定索引扫描成本。

示例：
若WHERE age > 30，优化器通过直方图发现超过30的数据占比10%，则可能选择索引扫描。

2. 统计信息的收集方式

• 自动收集：
  数据库定期触发（如Oracle的gather_stats_job、MySQL的innodb_stats_auto_recalc）。
• 手动收集：
  使用命令显式更新，例如：

  -- MySQL  
  ANALYZE TABLE users;  
  -- PostgreSQL  
  ANALYZE users;  
  -- Oracle  
  EXEC DBMS_STATS.GATHER_TABLE_STATS('SCHEMA','USERS');  
  

• 关键参数：
  • 采样比例：高比例更准确但耗资源（如ESTIMATE_PERCENT=>100）。
  • 直方图桶数（Buckets）：越多则分布越精细。

3. 统计信息如何影响查询优化

优化器通过统计信息估算选择率（Selectivity），即条件过滤后剩余数据的比例：

• 等值查询：WHERE id=100
  选择率 ≈ 1 / n_distinct（假设数据分布均匀）。
• 范围查询：WHERE age > 30
  通过直方图计算比例，若30以上占比20%，则选择率=0.2。
• 多条件组合：
  使用独立性假设，选择率 = 选择率1 × 选择率2。

成本计算示例：

• 全表扫描成本 = 表块数 × 单块I/O成本
• 索引扫描成本 = 索引层级遍历 + 叶子块读取 + 回表成本
  若选择率低（如0.1），索引扫描可能更优。

4. 统计信息不准的常见场景与解决

• 场景1：数据剧烈变化后未更新统计信息
  • 现象：表新增100万行，但统计信息显示行数=1万，优化器错误选择索引扫描。
  • 解决：定期或实时更新统计信息（如MySQL设置innodb_stats_persistent_sample_pages增加采样页数）。
• 场景2：数据倾斜导致直方图失效
  • 现象：90%订单状态为“已完成”，WHERE status='进行中'本应走索引，但优化器误判选择全表扫描。
  • 解决：
    1. 增加直方图桶数，细化分布。
    2. 使用提示（Hint）强制索引（如/*+ INDEX(table_name index_name) */）。
• 场景3：多列关联时独立性假设错误
  • 现象：WHERE country='中国' AND city='北京'，若城市与国家强关联，独立计算选择率会低估结果集。
  • 解决：
    1. 创建扩展统计信息（如Oracle的列组统计）。
    2. 使用动态采样（如Oracle的OPTIMIZER_DYNAMIC_SAMPLING）。

5. 实践建议

• 高频更新表：设置自动统计信息收集，并调整采样率平衡效率与准确性。
• 复杂查询：检查执行计划，对比实际行数与估算行数，偏差大时需更新统计信息。
• 数据倾斜列：优先创建直方图，避免均匀分布假设导致的误判。

通过以上步骤，统计信息成为优化器的“眼睛”，确保查询计划贴近真实数据特征，从而提升性能。