数据库的查询执行计划中的自适应查询优化技术

一、技术描述
自适应查询优化是数据库管理系统在查询执行过程中动态调整执行计划的技术。传统优化器在查询编译阶段基于统计信息生成固定执行计划，而自适应优化通过在运行时收集实际执行数据，对初始计划进行实时调整，解决因统计信息过时、数据偏斜或代价估计不准导致的性能问题。

二、核心原理

1. 执行时反馈机制：在查询执行过程中收集实际处理的行数、选择率等运行时统计信息
2. 中间结果监测：对已执行的操作符进行实际代价与预估代价的对比分析
3. 动态重优化：根据运行时信息决定是否调整后续执行计划

三、技术实现流程

步骤1：初始执行计划生成

• 优化器基于数据字典中的统计信息生成初始执行计划
• 在计划中标记关键检查点（如连接操作前、分组操作前）
• 示例：对查询SELECT * FROM t1 JOIN t2 ON t1.id=t2.id WHERE t1.val>100
  • 初始计划可能采用"嵌套循环连接"假设t1过滤后数据量很少
  • 在t1表扫描后设置检查点，实际评估过滤后的行数

步骤2：运行时统计信息收集

• 在执行过程中，对每个操作符收集实际处理数据：
  • 实际处理行数（实际基数）
  • 实际选择率（过滤条件的效果）
  • 实际数据分布特征
• 技术实现：

  -- 在操作符间插入统计计数器
  Operator: Seq Scan on t1
    Actual Rows: 50,000    -- 实际扫描行数
    Estimated Rows: 5,000  -- 预估行数
    Discrepancy: 10x       -- 差异倍数
  

步骤3：代价差异检测与评估

• 比较实际值与预估值的差异：
  • 设定阈值（如5倍差异触发重优化）
  • 计算代价估计误差率
• 评估影响程度：
  • 如果误差影响连接顺序选择，触发重优化
  • 如果只影响非关键路径，保持原计划

步骤4：执行计划调整决策

• 连接方法调整：
  • 初始使用嵌套循环连接，实际发现外表数据量大
  • 动态切换为哈希连接或归并连接
• 连接顺序重排：
  • 基于实际中间结果大小重新排序连接顺序
  • 优先处理结果集较小的表
• 并行度调整：
  • 根据实际数据量调整并行工作进程数

步骤5：平滑执行切换

• 保存已执行部分的结果
• 为未执行部分生成新的子计划
• 确保状态一致性：
  • 维护临时结果集
  • 处理并行执行间的依赖关系

四、具体技术实现方式

1. 多次优化（Re-optimization）

• 在预定义检查点暂停执行
• 重新调用优化器生成剩余查询的新计划
• 适用场景：多表连接查询中存在显著估计误差

2. 中间结果物化（Mid-Query Materialization）

• 将已执行的部分结果物化到临时表
• 基于物化结果的统计信息优化后续查询
• 示例：

  -- 初始计划不佳时，将中间结果保存
  CREATE TEMPORARY TABLE temp_results AS 
  SELECT * FROM t1 WHERE val > 100;  -- 物化过滤结果
  
  -- 基于实际物化结果重新优化后续连接
  ANALYZE temp_results;  -- 收集临时表统计信息
  SELECT * FROM temp_results JOIN t2 ON ...;
  

3. 自适应连接方法（Adaptive Join）

• 动态选择连接算法：
  • 开始时使用嵌套循环连接（适合小数据集）
  • 当发现内表数据量大时，切换为哈希连接
• 实现机制：
  • 构建哈希表的同时监测构建时间
  • 超过阈值时切换到替代算法

五、实际应用案例

案例：数据偏斜时的自适应优化

-- 查询语句
SELECT o.customer_id, SUM(oi.amount) 
FROM orders o JOIN order_items oi ON o.id = oi.order_id
WHERE o.order_date >= '2023-01-01'
GROUP BY o.customer_id;

-- 传统优化问题：
-- 基于统计信息假设订单日期分布均匀
-- 实际数据：2023年后数据量是预估的10倍

-- 自适应优化过程：
1. 扫描orders表时发现实际符合条件行数远超预估
2. 在连接操作前暂停执行，重新评估连接策略
4. 检测到哈希连接内存可能不足，切换到归并连接
5. 调整分组操作的聚合策略

六、优势与局限性

优势：

• 解决统计信息过时问题
• 处理数据分布偏斜场景
• 适应动态工作负载变化
• 减少手动调优需求

局限性：

• 运行时优化带来额外开销
• 复杂查询中检查点设置困难
• 对短查询收益有限
• 实现复杂度较高

七、最佳实践建议

1. 启用自适应优化：在OLAP场景中开启自适应查询功能
2. 设置合理阈值：根据工作负载特性配置重优化触发条件
3. 监控优化效果：跟踪自适应优化的成功率和性能提升
4. 结合统计信息更新：定期更新统计信息减少自适应优化触发频率

自适应查询优化代表了查询优化技术的重要发展方向，通过运行时反馈机制弥补了传统静态优化的不足，在复杂查询和大数据场景中显著提升性能稳定性。