数据库查询优化中的统计信息与成本估算原理解析

题目描述

在数据库查询优化过程中，优化器需要从多个可能的执行计划中选择最优方案。这一选择依赖于统计信息（Statistics）和成本估算（Cost Estimation）。本题将详解统计信息如何收集与存储、优化器如何利用统计信息估算查询成本，以及常见估算模型（如选择率、基数估算）的原理。

1. 统计信息的核心作用

统计信息是数据库对表中数据分布的摘要，例如：

• 表级信息：行数、数据页数量；
• 列级信息：不同值的数量（NDV）、空值比例、数据分布（直方图）；
• 索引信息：索引层级、叶子节点数等。

优化器通过统计信息估算查询的“成本”（如CPU、I/O、内存开销），从而比较不同执行计划的效率。

2. 统计信息的收集与存储

（1）收集方式

• 自动收集：数据库定期触发（如MySQL的auto_increment统计、PostgreSQL的autovacuum）；
• 手动命令：如ANALYZE TABLE（MySQL）或DBMS_STATS.GATHER_TABLE_STATS（Oracle）。

（2）关键统计指标

• 基数（Cardinality）：某列不同值的数量（NDV）。例如，性别列NDV通常为2；
• 直方图（Histogram）：描述数据分布，尤其对非均匀数据（如年龄、收入）至关重要：
  • 等宽直方图：按值范围分桶，记录每个桶的频率；
  • 等高直方图：按频率分桶，使每个桶包含近似行数。
• 相关性：多列联合条件的选择率估算（如WHERE city=北京 AND salary>10000）。

3. 成本估算的基本原理

（1）选择率（Selectivity）

选择率指条件过滤后剩余行数占总行数的比例。例如：

• WHERE id=10：若id唯一，选择率 ≈ 1/NDV；
• WHERE age>30：通过直方图确定值在30以上的比例；
• 多条件选择率：假设条件独立，联合选择率 = 各条件选择率的乘积（实际可能因相关性产生偏差）。

（2）基数估算（Cardinality Estimation）

查询结果的预估行数 = 表总行数 × 选择率。例如：

• 表users有10000行，status列NDV=4（值均匀分布），则WHERE status=1的基数 ≈ 10000 × 1/4 = 2500行。

（3）成本模型

优化器将操作（如索引扫描、全表扫描）转化为成本单位：

• I/O成本：读取数据页的代价（与页数相关）；
• CPU成本：处理行数的代价（如比较、排序）。
  总成本 = I/O成本 + CPU成本。

4. 实际示例分析

假设表employees有10000行，统计信息如下：

• department列NDV=10，直方图显示分布均匀；
• salary列最小值3000，最大值80000，直方图分5桶。

查询：

SELECT * FROM employees  
WHERE department = 'HR' AND salary > 50000;  

步骤1：计算单条件选择率

• department='HR'：选择率 ≈ 1/10 = 0.1；
• salary>50000：通过直方图发现50000以上数据占20%，选择率=0.2。

步骤2：联合选择率
假设条件独立，联合选择率 ≈ 0.1 × 0.2 = 0.02。

步骤3：基数估算
预估行数 = 10000 × 0.02 = 200行。

步骤4：比较执行计划成本

• 全表扫描：成本 ≈ 读取10000行的I/O + 过滤10000行的CPU；
• 索引扫描（假设有(department, salary)索引）：成本 ≈ 索引范围扫描的I/O + 回表查询200行的I/O。
  优化器会选择成本更低的方案。

5. 统计信息的常见问题与优化

• 统计信息过期：数据频繁更新后，统计信息未及时刷新，导致估算偏差（如实际行数远高于估算）；
• 数据相关性偏差：例如country='中国' AND city='北京'具有强相关性，独立假设会低估选择率；
• 解决方案：
  • 定期更新统计信息（尤其在大批量数据变更后）；
  • 使用动态采样（如Oracle的动态统计）实时补充统计信息；
  • 创建扩展统计信息（如Oracle的列组）捕获多列相关性。

总结

统计信息与成本估算是查询优化器的“眼睛”，其准确性直接决定执行计划的优劣。理解直方图、选择率、基数估算等原理，有助于通过优化统计信息收集策略或提示（Hint）干预优化器决策，提升查询性能。