印刻数据库

外观

DB2 查询执行计划分析 ​

执行计划概述 ​

查询执行计划是DB2数据库优化器生成的详细执行步骤，用于执行SQL查询。它描述了数据库如何访问表、连接表、过滤数据和排序结果。分析执行计划是优化SQL查询性能的关键步骤。

执行计划的重要性 ​

  • 性能优化：识别查询中的瓶颈和低效操作
  • 问题诊断：定位慢查询的根本原因
  • 资源利用：了解查询对系统资源（CPU、内存、I/O）的影响
  • 索引设计：评估现有索引的有效性，指导新索引的创建
  • 成本估算：预测查询的执行成本和响应时间

执行计划生成流程 ​

  1. 语法解析：验证SQL语句的语法正确性
  2. 语义分析：检查对象存在性和权限
  3. 查询重写：优化器自动重写查询以提高效率
  4. 统计信息收集：获取表和索引的统计信息
  5. 计划生成：生成多个候选执行计划
  6. 成本估算：估算每个候选计划的执行成本
  7. 计划选择：选择成本最低的执行计划
  8. 计划缓存：将生成的执行计划缓存到包缓存中

执行计划分析工具 ​

1. db2expln 工具 ​

db2expln是DB2提供的命令行工具，用于生成查询执行计划的文本描述。

基本用法 ​

bash
# 从SQL文件生成执行计划
db2expln -d sample -f query.sql -t -z ""

# 直接输入SQL语句生成执行计划
db2expln -d sample -q "SELECT * FROM employees WHERE department_id = 10" -t

# 生成详细执行计划，包含成本信息
db2expln -d sample -q "SELECT * FROM employees WHERE department_id = 10" -t -v

参数说明 ​

  • -d：指定数据库名称
  • -f：指定包含SQL语句的文件
  • -q：直接指定SQL语句
  • -t：显示表访问信息
  • -v：显示详细的成本信息
  • -z：指定SQL语句分隔符

2. db2exfmt 工具 ​

db2exfmt是更强大的执行计划分析工具，生成更详细、结构化的执行计划报告。

基本用法 ​

bash
# 从包缓存中获取执行计划
db2exfmt -d sample -1 -o exfmt_output.txt

# 从动态SQL生成执行计划
db2 -x "EXPLAIN PLAN FOR SELECT * FROM employees WHERE department_id = 10"
db2exfmt -d sample -e username -o exfmt_output.txt

# 生成包含统计信息的执行计划
db2exfmt -d sample -e username -w -l -o exfmt_output.txt

参数说明 ​

  • -d：指定数据库名称
  • -1：从包缓存中获取最新的执行计划
  • -e：指定解释表的所有者
  • -o：指定输出文件
  • -w：显示表的统计信息
  • -l：显示操作的成本和行数估算

3. IBM Data Studio/DB2 Developer Tools ​

IBM Data Studio和DB2 Developer Tools提供了图形化的执行计划分析界面，使执行计划分析更加直观。

主要功能 ​

  • 图形化显示执行计划树
  • 颜色编码标识操作成本
  • 详细的操作属性和统计信息
  • 执行计划比较功能
  • 优化建议

4. 命令行处理器（CLP） ​

可以使用DB2命令行处理器直接查看执行计划。

bash
# 启用执行计划显示
db2set DB2OPTIONS="-z #"
db2

# 在CLP中显示执行计划
db2 => EXPLAIN PLAN FOR SELECT * FROM employees WHERE department_id = 10;
db2 => SELECT * FROM TABLE(EXPLAIN_FROM_SQLID('SQLID'));

执行计划结构与操作符 ​

执行计划结构 ​

执行计划以树状结构表示，每个节点代表一个操作符。操作符按照执行顺序从下到上或从右到左执行。

常见操作符类型 ​

1. 表访问操作符 ​

操作符描述
TBSCAN表扫描，逐行读取表中的所有数据
IXSCAN索引扫描，使用索引访问表数据
IXONLY仅索引扫描，不需要访问表数据
FETCH获取操作，根据索引键或行ID从表中获取数据

2. 连接操作符 ​

操作符描述
NLJOIN嵌套循环连接，适用于小结果集
HSJOIN哈希连接，适用于大结果集
MSJOIN合并连接，要求输入数据已排序
MJ-NL合并-嵌套循环连接，结合了合并和嵌套循环的优点

3. 过滤和排序操作符 ​

操作符描述
SORT排序操作，对数据进行排序
FILTER过滤操作，根据条件过滤数据
AGGREGATE聚合操作，执行SUM、COUNT、AVG等聚合函数
GROUP BY分组操作，根据指定列对数据分组
DISTINCT去重操作，去除重复数据

4. 其他操作符 ​

操作符描述
UNION合并操作，合并多个结果集
SUBQUERY子查询操作
VIEW视图引用
INSERT/UPDATE/DELETEDML操作

执行计划分析方法 ​

1. 查看执行顺序 ​

执行计划的执行顺序通常是从下到上或从右到左。了解执行顺序有助于理解查询的执行流程。

2. 识别瓶颈操作 ​

  • 高成本操作：关注成本估算最高的操作符
  • 全表扫描：不必要的TBSCAN通常是性能瓶颈
  • 排序操作：SORT操作消耗大量CPU和内存
  • 大量数据移动：数据在操作符之间的大量移动

3. 分析访问路径 ​

  • 表访问方式：评估是否使用了合适的索引
  • 索引选择：检查优化器是否选择了正确的索引
  • 索引效率：评估索引过滤的有效性（过滤因子）

4. 连接策略分析 ​

  • 连接顺序：检查表的连接顺序是否合理
  • 连接类型：评估是否使用了合适的连接类型
  • 连接效率：分析连接操作的成本和数据量

5. 成本估算验证 ​

  • 行数估算：比较优化器估算的行数与实际行数
  • 成本估算：评估成本估算的准确性
  • 统计信息：检查统计信息是否过时或不准确

执行计划优化建议 ​

1. 优化表访问 ​

  • 添加合适的索引：根据查询条件创建索引
  • 优化索引设计：选择合适的索引列顺序
  • 避免全表扫描：确保查询使用索引过滤
  • 使用覆盖索引：减少FETCH操作

2. 优化连接操作 ​

  • 调整连接顺序：将过滤性强的表放在前面
  • 选择合适的连接类型：根据数据量选择连接类型
  • 优化连接条件：确保连接条件使用索引列

3. 优化过滤操作 ​

  • 简化WHERE条件：减少复杂的逻辑表达式
  • 避免函数操作：不要在索引列上使用函数
  • 使用绑定变量：提高计划重用率
  • 优化谓词顺序：将过滤性强的谓词放在前面

4. 优化排序和聚合 ​

  • 避免不必要的排序：使用索引排序
  • 优化GROUP BY：确保分组列有索引
  • 使用物化查询表：加速复杂聚合查询

5. 优化子查询 ​

  • 重写子查询：将相关子查询转换为连接
  • 使用EXISTS代替IN：对于大结果集
  • 优化嵌套子查询：减少嵌套层级

实际案例分析 ​

案例1：全表扫描优化 ​

问题SQL：

sql
SELECT * FROM employees WHERE department_id = 10;

执行计划：

TBSCAN
  Table: EMPLOYEES
    Cost: 1000
    Rows: 10000

问题：使用了全表扫描，成本高

优化方案：

sql
-- 创建索引
CREATE INDEX idx_emp_dept ON employees(department_id);

优化后执行计划：

FETCH
  IXSCAN
    Index: IDX_EMP_DEPT
      Cost: 100
      Rows: 100

效果：成本从1000降低到100，行数从10000减少到100

案例2：连接顺序优化 ​

问题SQL：

sql
SELECT e.name, d.department_name 
FROM employees e, departments d 
WHERE e.department_id = d.department_id 
AND e.salary > 5000;

执行计划：

NLJOIN
  TBSCAN
    Table: DEPARTMENTS
      Cost: 500
      Rows: 100
  TBSCAN
    Table: EMPLOYEES
      Filter: SALARY > 5000
      Cost: 1000
      Rows: 10000

问题：先扫描departments表，再扫描employees表，连接顺序不合理

优化方案：

sql
-- 收集统计信息
RUNSTATS ON TABLE employees AND INDEXES ALL;
RUNSTATS ON TABLE departments AND INDEXES ALL;

优化后执行计划：

HSJOIN
  IXSCAN
    Index: IDX_EMP_SALARY
      Filter: SALARY > 5000
      Cost: 200
      Rows: 2000
  IXSCAN
    Index: PK_DEPARTMENTS
      Cost: 50
      Rows: 100

效果：连接类型从NLJOIN变为HSJOIN，成本显著降低

版本差异 ​

DB2 9.x 执行计划 ​

  • 引入了新的优化器特性
  • 支持更多连接类型
  • 改进了成本估算模型

DB2 10.x 执行计划 ​

  • 增强了索引扫描算法
  • 改进了连接顺序优化
  • 引入了自适应查询优化
  • 增强了执行计划诊断功能

DB2 11.x 执行计划 ​

  • 引入了列式存储支持
  • 增强了内存数据库优化
  • 改进了并行查询执行
  • 增强了执行计划可视化

DB2 12.x 执行计划 ​

  • 引入了AI驱动的优化器
  • 增强了自适应查询优化
  • 改进了执行计划缓存机制
  • 增强了查询性能监控

生产环境最佳实践 ​

1. 定期分析执行计划 ​

  • 定期检查关键查询的执行计划
  • 监控执行计划的变化
  • 建立执行计划基线

2. 维护统计信息 ​

  • 定期运行RUNSTATS更新统计信息
  • 监控统计信息的准确性
  • 使用自动统计信息收集

3. 使用绑定变量 ​

  • 提高计划重用率
  • 减少优化器开销
  • 避免硬解析

4. 监控包缓存 ​

  • 监控包缓存使用情况
  • 定期清理无效的执行计划
  • 调整包缓存大小

5. 使用性能监控工具 ​

  • 结合db2top、db2pd等工具监控查询性能
  • 使用IBM Data Server Manager进行全面监控
  • 设置性能阈值告警

6. 建立优化流程 ​

  • 建立SQL开发规范
  • 实施查询性能测试
  • 建立优化反馈机制

常见问题（FAQ） ​

Q1: 为什么执行计划会变化？ ​

A1: 执行计划变化的原因包括：

  • 统计信息变化
  • 数据量变化
  • 索引创建或删除
  • 配置参数调整
  • 数据库版本升级
  • 优化器级别调整

Q2: 如何固定执行计划？ ​

A2: 可以使用以下方法固定执行计划：

  • 使用SQL语句注释提示（如/*+ INDEX(emp idx_emp_dept) */）
  • 使用优化配置文件（OProfile）
  • 使用绑定选项（如KEEPDYNAMIC(YES)）
  • 调整优化器级别

Q3: 执行计划中的成本是什么意思？ ​

A3: 执行计划中的成本是优化器估算的相对成本，基于CPU成本、I/O成本和内存成本的综合计算。成本越低，执行效率越高。

Q4: 如何判断执行计划是否最优？ ​

A4: 判断执行计划是否最优的方法：

  • 检查是否使用了合适的索引
  • 检查连接顺序和连接类型是否合理
  • 检查是否避免了不必要的全表扫描和排序
  • 比较实际执行时间与预期
  • 监控资源使用情况

Q5: 为什么索引没有被使用？ ​

A5: 索引没有被使用的原因包括：

  • 统计信息过时
  • 索引过滤性差
  • 查询条件使用了函数或表达式
  • 优化器认为全表扫描成本更低
  • 索引列顺序不合理

Q6: 如何优化复杂查询的执行计划？ ​

A6: 优化复杂查询的执行计划可以：

  • 分解复杂查询为多个简单查询
  • 使用临时表存储中间结果
  • 重写子查询为连接
  • 添加合适的索引
  • 调整优化器级别
  • 使用物化查询表

Q7: 如何使用db2exfmt生成详细的执行计划？ ​

A7: 使用以下步骤生成详细的执行计划：

  1. 执行EXPLAIN PLAN命令：
    sql
    EXPLAIN PLAN FOR SELECT * FROM employees WHERE department_id = 10;
  2. 使用db2exfmt生成报告：
    bash
    db2exfmt -d sample -e username -w -l -o exfmt_output.txt

Q8: 如何比较不同执行计划？ ​

A8: 比较不同执行计划的方法：

  • 使用db2exfmt的比较功能
  • 使用IBM Data Studio的执行计划比较
  • 手动比较成本、行数和操作符
  • 测试不同计划的实际执行时间

总结 ​

查询执行计划分析是DB2数据库性能优化的核心技能。通过深入理解执行计划的结构、操作符和生成原理，结合适当的工具和方法，可以有效地识别和解决SQL查询性能问题。

在实际工作中，DBA和开发人员应该：

  1. 掌握多种执行计划分析工具
  2. 定期分析关键查询的执行计划
  3. 维护准确的统计信息
  4. 建立良好的SQL开发规范
  5. 持续监控和优化查询性能

通过不断学习和实践，提高执行计划分析能力，将有助于提升数据库整体性能，确保业务系统的高效运行。