SQL查询优化器工作原理解析（进阶篇）

今天我们来深入探讨SQL查询优化器的内部工作机制，特别是基于成本的优化（CBO）如何选择最佳执行计划。

一、优化器的核心任务
当数据库收到SQL查询时，优化器需要将声明式的SQL语句转化为可执行的物理操作序列。其核心目标是：从数百万种可能的执行计划中，找到在合理时间内返回正确结果且资源消耗最低的计划。

二、优化过程的三个阶段

阶段1：逻辑转换（查询重写）

• 输入：原始SQL语句的语法树
• 过程：基于关系代数规则进行等价变换
• 关键操作：
  1. 视图合并：将视图定义展开合并到主查询

  -- 原始：CREATE VIEW v1 AS SELECT * FROM t1 WHERE c1>10;
  --        SELECT * FROM v1 JOIN t2 ON v1.id=t2.id;
  -- 重写后：SELECT * FROM t1 JOIN t2 ON t1.id=t2.id WHERE t1.c1>10;
  

  2. 谓词下推：将过滤条件尽可能推到靠近数据源的位置
  3. 子查询展开：将相关子查询转换为更高效的JOIN操作

阶段2：生成候选执行计划

• 搜索空间构建：考虑所有可能的连接顺序、访问方法和连接算法
• 示例：三表连接优化

  SELECT * FROM t1, t2, t3 
  WHERE t1.id=t2.id AND t2.id=t3.id;
  

  可能计划包括：
  • 连接顺序：((t1⨝t2)⨝t3), ((t1⨝t3)⨝t2), ...（共6种）
  • 连接算法：嵌套循环连接、哈希连接、排序合并连接
  • 访问路径：全表扫描、索引扫描、索引快速全扫描

阶段3：成本估算与计划选择
这是优化器最核心的复杂决策过程：

1. 基数估计（Cardinality Estimation）

• 目标：预测每个操作符输出的行数
• 方法：
  • 使用统计信息（直方图、不同值数量、数据分布）
  • 对于谓词条件：应用选择率估算
    • c1 = 100：选择率 ≈ 1/NDV(c1)（NDV为不同值数量）
    • c1 > 100：使用直方图估算比例

2. 成本模型计算

• CPU成本：处理每条记录的开销
• I/O成本：物理读/逻辑读的代价
• 内存成本：排序、哈希操作的内存使用
• 公式示例：索引扫描成本 = 索引高度 + 选择率 × 叶子块数

3. 动态规划搜索
优化器使用自底向上的动态规划方法：

Level 0: 估算每个表的访问成本
  - t1: 全表扫描成本=100，索引扫描成本=30
  - t2: 全表扫描成本=200，索引扫描成本=50

Level 1: 估算两表连接的最佳成本
  - (t1⨝t2): 
    * 嵌套循环: 30 + 1000×50 = 50030
    * 哈希连接: 30 + 50 + 哈希构建成本 = 150
  - (t2⨝t3): ...（类似计算）

Level 2: 估算三表连接的最佳成本
  - 比较((t1⨝t2)⨝t3) vs ((t1⨝t3)⨝t2)的成本

三、实际案例演示

查询示例：

SELECT e.name, d.dname 
FROM employees e 
JOIN departments d ON e.dept_id = d.id 
WHERE e.salary > 50000 AND e.hire_date > '2020-01-01';

优化器决策过程：

1. 访问路径选择：

   • employees表：有salary索引和hire_date索引
   • 计算选择率：salary>50000选择20%，hire_date>2020选择30%
   • 组合选择率：0.2×0.3=6%，预计返回6000条记录
   • 比较索引扫描vs全表扫描成本

2. 连接方法选择：

   • 嵌套循环：适合驱动表结果集小的情况
   • 哈希连接：适合两表都较大的等值连接
   • 排序合并：适合数据已排序或需要排序的输出

3. 最终计划选择：

   • 方案1：employees索引扫描 → 哈希连接departments（成本：350）
   • 方案2：employees全表扫描 → 嵌套循环连接（成本：420）
   • 选择成本更低的方案1

四、优化器局限性及应对策略

常见估算错误原因：

1. 数据相关性：如city='北京'和country='中国'不是独立的
2. 统计信息过时：数据分布变化后未更新统计信息
3. 复杂表达式：用户自定义函数难以估算选择率

优化建议：

• 定期更新统计信息：ANALYZE TABLE或数据库自动任务
• 使用查询提示（hint）引导优化器：/*+ INDEX(e salary_idx) */
• 避免在WHERE条件中使用复杂表达式和函数

通过理解优化器的工作原理，DBA和开发者可以编写出更优化的SQL语句，并在性能问题时准确诊断根本原因。