数据库的查询优化器参数调优与执行计划固定

一、知识点描述
查询优化器是数据库核心组件，负责将SQL查询转换为高效执行计划。优化器参数调优是通过调整内部配置参数，改变优化器生成执行计划的策略；执行计划固定则是通过技术手段确保特定查询始终使用最优执行计划，避免因统计信息变化、参数调整等因素导致计划退化。本知识点涵盖优化器工作原理、关键参数作用、执行计划不稳定的成因及固定方法。

二、知识脉络详解

1. 查询优化器的工作基础

• 输入处理：接收SQL语句后，优化器先进行语法解析、语义检查，生成初始查询树。
• 代价估算：结合数据字典中的表/索引统计信息（如行数、唯一值数量、数据分布直方图），计算不同执行路径的代价（CPU、I/O、内存消耗）。
• 计划生成：通过动态规划或遗传算法等搜索策略，枚举可能的连接顺序、索引使用方式，选择代价最低的执行计划。

关键点：优化器依赖统计信息的准确性，若统计信息过时（如数据大幅更新后未重新收集），代价估算会失真，导致选择次优计划。

2. 影响优化器行为的核心参数
以下以Oracle/MySQL/PostgreSQL等常见数据库为例说明参数类型：

• 优化目标参数：如optimizer_mode（Oracle）或optimizer_switch（MySQL），定义优化器优先考虑响应时间还是吞吐量。例如：
  • FIRST_ROWS_n：适合OLTP场景，优先返回前n行。
  • ALL_ROWS：适合OLAP场景，最小化整体执行时间。
• 代价模型参数：如cpu_index_cost（PostgreSQL）调整CPU计算代价权重，random_page_cost（PostgreSQL）调整随机I/O代价。
• 查询变换参数：控制是否启用子查询展开、谓词下推等重写规则。例如关闭optimizer_switch中的materialization=off（MySQL）可禁用物化优化。

操作示例（以MySQL调优为例）：

-- 查看当前优化器开关状态
SELECT @@optimizer_switch;
-- 禁用索引合并优化，避免多个索引合并操作带来的开销
SET SESSION optimizer_switch = 'index_merge=off';

3. 执行计划不稳定的常见原因

• 统计信息更新：自动统计信息收集后，数据分布估算变化可能导致计划变更。
• 参数绑定窥探（Bind Peeking）：首次执行时优化器根据传入参数值生成计划，后续若参数值分布差异大（如从高频值变为低频值），原计划可能低效。
• 环境变化：内存压力、并发竞争等资源变化影响代价计算。

4. 执行计划固定技术

• 执行计划提示：通过SQL注释强制指定索引、连接方式等。例如：

  SELECT /*+ INDEX(t idx_name) */ * FROM table t WHERE t.name = 'test';
  

• 计划基线：首次捕获高效计划后，将其加入基线库，后续执行时优化器优先匹配基线计划（Oracle的SQL Plan Baseline、MySQL的Optimizer Hints）。
• SQL配置文件：使用工具（如Oracle的SQL Tuning Advisor）生成配置文件，直接注入优化器决策。

实施步骤（以Oracle计划基线为例）：

1. 捕获高效计划：在系统负载稳定时执行目标SQL，确保生成理想计划。
2. 创建基线：

   -- 从共享池加载计划到基线
   DECLARE
     v_plan PLS_INTEGER;
   BEGIN
     v_plan := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(
       sql_id => 'xxx123456789');
   END;
   

3. 验证与固定：标记基线计划为ACCEPTED，并设置FIXED属性防止其他计划干扰。

5. 调优实践流程

• 监控异常：通过慢查询日志或动态性能视图（如V$SQL）识别执行时间突变的SQL。
• 分析计划变更：对比历史计划与当前计划，检查统计信息更新时间、参数差异。
• 渐进调优：优先通过提示微调，验证效果后再考虑固定方案，避免过度干预优化器。

三、核心要点总结

• 优化器参数调优需结合业务负载特征，通过A/B测试验证参数修改效果。
• 执行计划固定是“兜底”手段，应优先保障统计信息准确性和参数合理性。
• 固定计划后仍需定期复审，确保计划仍适应数据变化。