数据库的查询执行计划中的代价模型与基数估计误差分析

描述
在数据库查询优化过程中，优化器需要为每个可能的执行计划计算"代价"，以选择最高效的方案。代价模型是量化资源消耗（如CPU、I/O、内存）的数学模型，而基数估计则预测每个操作符（如过滤、连接）输出的行数。基数估计的准确性直接决定代价计算的可靠性，但实际中常因数据分布不均、统计信息滞后等因素产生误差，导致优化器选择次优计划。本知识点将详解代价模型的构成、基数估计的原理、常见误差来源及缓解策略。

1. 代价模型的基本构成
代价模型将查询执行抽象为资源成本的加权和，通常包括：

• I/O代价：数据从磁盘读取到内存的消耗，与访问的数据页数正相关。
• CPU代价：处理数据（比较、计算、排序等）的消耗，与处理的行数正相关。
• 网络代价（分布式数据库）：节点间数据传输的消耗。
• 内存代价：临时存储中间结果的消耗。
  优化器为这些代价分配权重（如I/O权重 > CPU权重），最终计算总代价。例如，一个索引扫描可能CPU代价高（需处理索引项），但I/O代价低（少量磁盘访问），而全表扫描则相反。

2. 基数估计的关键技术
基数估计依赖统计信息，主要方法包括：

• 直方图：将数据按值域分桶，记录每个桶的频率和边界。例如，等宽直方图将值域均分，等深直方图保证每桶数据量相近。优化器通过直方图估算条件（如age BETWEEN 20 AND 30）的筛选率。
• 唯一值数（NDV）：统计列中不同值的数量，用于估算等值条件（如name = 'Alice'）的选择性（通常为1/NDV）。
• 相关性分析：若多列存在强相关性（如城市和邮编），单独估计会导致误差。例如，假设城市和邮编独立，估算城市=北京 AND 邮编=100001的选择性为(1/城市NDV) * (1/邮编NDV)，但实际可能接近1/城市NDV（因邮编与城市绑定）。
• 采样：对表数据随机采样，用小样本推断整体分布，适用于大数据表。

3. 基数估计误差的常见来源

• 数据偏斜：直方图桶内数据分布不均。例如，桶[0,100]包含90%的数据集中在10以内，估算value > 90时可能高估基数。
• 多列相关性：统计信息通常假设列独立，但实际中（如姓氏和国籍）可能强相关，导致连接基数估计偏差。
• 函数和表达式：对UPPER(name)或date + 7等表达式，统计信息缺失，优化器可能使用默认筛选率（如5%）。
• 数据更新滞后：统计信息未及时刷新，旧统计无法反映新插入或删除的数据分布。
• 复杂逻辑条件：对OR条件（如age < 18 OR salary > 10000）或否定条件（NOT status = 'active'），估算公式可能过于简化。

4. 误差对执行计划的影响示例
假设查询：

SELECT * FROM orders o JOIN customers c ON o.customer_id = c.id  
WHERE c.city = 'Beijing' AND o.amount > 1000;  

• 若低估city='Beijing'的基数：优化器可能高估嵌套循环连接的效率（误认为驱动表结果集小），实际因中间结果大而性能低下。
• 若高估amount>1000的基数：优化器可能选择对orders全表扫描而非索引扫描，增加I/O消耗。

5. 误差缓解策略

• 动态统计信息收集：使用ANALYZE TABLE更新统计信息，或启用自动收集（如Oracle的自动统计任务）。
• 多列统计信息：创建扩展统计项（如MySQL的STATS_EXTENDED），捕获列间相关性。
• 提示（Hints）：通过/*+ INDEX(...) */等提示强制优化器使用特定索引或连接顺序。
• 自适应查询优化：如Oracle的自适应计划，运行时根据实际基数动态调整执行路径。
• 自定义统计插件：针对特殊数据分布（如JSON字段），开发插件提供更精确的估计逻辑。

总结
代价模型与基数估计是查询优化的核心，但误差难以完全避免。理解误差来源并通过统计管理、监控工具（如执行计划中的EST_ROWS与ACTUAL_ROWS对比）主动调优，是提升数据库性能的关键。