印刻数据库

外观

DB2 索引维护 ​

索引维护概述 ​

索引维护是DB2数据库日常运维的重要组成部分，它直接影响数据库的查询性能和系统资源利用率。索引维护包括索引的重组、重建、统计信息更新等操作，这些操作可以确保索引保持高效状态，避免索引碎片化和性能下降。

索引维护的必要性 ​

1. 索引碎片化 ​

  • 随着数据的插入、更新和删除，索引页会产生碎片
  • 碎片化导致索引扫描效率降低，增加I/O开销
  • 严重碎片化时，索引可能比实际数据占用更多空间

2. 统计信息过时 ​

  • 索引统计信息是查询优化器生成执行计划的重要依据
  • 过时的统计信息可能导致优化器选择低效的执行计划
  • 数据分布发生显著变化时，必须更新统计信息

3. 索引结构损坏 ​

  • 硬件故障、软件bug等可能导致索引结构损坏
  • 损坏的索引会导致查询失败或返回错误结果
  • 定期检查和维护可以及早发现并修复损坏的索引

索引维护方法 ​

1. 索引重组（REORG INDEX） ​

适用场景 ​

  • 索引页碎片化严重，但索引结构完整
  • 索引统计信息基本准确，不需要重新计算
  • 希望以较低的系统开销维护索引

语法 ​

sql
REORG INDEX index_name ON table_name
  [ALLOW {YES | NO}]
  [USE {SYSDEFLTS | n BUFFERS}]
  [LONGLOBDATA]
  [RESETDICTIONARY]
  [KEEPDICTIONARY]
  [SCOPE {ALL | DATA | INDEXES}]
  [PARALLEL n]

示例 ​

sql
-- 重组单个索引
REORG INDEX idx_employee_id ON employee ALLOW YES;

-- 重组表上的所有索引
REORG INDEXES ALL FOR TABLE employee ALLOW YES;

2. 索引重建（RECREATE INDEX） ​

适用场景 ​

  • 索引结构损坏
  • 索引统计信息严重过时
  • 索引定义需要修改
  • 希望彻底优化索引结构

语法 ​

sql
-- 方法1：删除并重新创建索引
DROP INDEX index_name;
CREATE INDEX index_name ON table_name (column1, column2, ...);

-- 方法2：使用REORG INDEX RECREATE命令（DB2 10.5+）
REORG INDEX index_name ON table_name RECREATE
  [ALLOW {YES | NO}]
  [USE {SYSDEFLTS | n BUFFERS}]
  [PARALLEL n]

示例 ​

sql
-- 删除并重新创建索引
DROP INDEX idx_employee_department;
CREATE INDEX idx_employee_department ON employee (department_id) INCLUDE (salary);

-- 使用REORG INDEX RECREATE命令
REORG INDEX idx_employee_id ON employee RECREATE ALLOW YES;

3. 更新索引统计信息 ​

适用场景 ​

  • 数据分布发生显著变化
  • 查询性能下降
  • 执行计划不理想

语法 ​

sql
-- 更新单个表的统计信息（包括索引）
RUNSTATS ON TABLE table_name ON ALL COLUMNS AND INDEXES ALL;

-- 更新特定索引的统计信息
RUNSTATS ON TABLE table_name FOR INDEXES index_name;

-- 更新统计信息并收集分位数统计
RUNSTATS ON TABLE table_name ON ALL COLUMNS WITH DISTRIBUTION AND INDEXES ALL;

示例 ​

sql
-- 更新employee表的所有统计信息
RUNSTATS ON TABLE employee ON ALL COLUMNS WITH DISTRIBUTION AND INDEXES ALL;

-- 仅更新idx_employee_id索引的统计信息
RUNSTATS ON TABLE employee FOR INDEXES idx_employee_id;

索引维护策略 ​

1. 定期维护策略 ​

基于时间的维护 ​

  • 每日维护：更新高频访问表的统计信息
  • 每周维护：重组或重建碎片化严重的索引
  • 每月维护：全面检查和维护所有索引

基于阈值的维护 ​

  • 当索引碎片率超过30%时，执行重组操作
  • 当表数据变化超过20%时，更新统计信息
  • 当索引深度超过10层时，考虑重建索引

2. 在线维护与离线维护 ​

在线维护 ​

  • 允许用户在维护期间访问表和索引
  • 适用：生产环境，需要保证业务连续性
  • 特点：系统开销较大，维护时间较长

离线维护 ​

  • 维护期间锁定表，不允许用户访问
  • 适用：非生产环境或维护窗口
  • 特点：系统开销较小，维护时间较短

3. 自动化维护 ​

使用DB2自动维护功能 ​

sql
-- 启用自动RUNSTATS
CALL SYSPROC.ADMIN_CMD('AUTOMAINT_SET_POLICYFILE(''DB2MaintenancePolicy.xml'')');

-- 配置自动维护窗口
UPDATE SYSCAT.DBAUTOMAINT SET AUTO_RUNSTATS = 'ON' WHERE DBNAME = CURRENT DATABASE;

使用脚本自动化维护 ​

bash
#!/bin/bash
# 索引维护自动化脚本

DB_NAME="sample"
SCHEMA="db2inst1"

# 更新统计信息
db2 "CONNECT TO $DB_NAME"
db2 "RUNSTATS ON TABLE $SCHEMA.employee ON ALL COLUMNS WITH DISTRIBUTION AND INDEXES ALL"
db2 "RUNSTATS ON TABLE $SCHEMA.department ON ALL COLUMNS WITH DISTRIBUTION AND INDEXES ALL"

# 重组索引
db2 "REORG INDEXES ALL FOR TABLE $SCHEMA.employee ALLOW YES"
db2 "REORG INDEXES ALL FOR TABLE $SCHEMA.department ALLOW YES"

db2 "CONNECT RESET"
db2 "TERMINATE"

索引维护工具 ​

1. DB2自带工具 ​

db2dart ​

  • 用于检查索引结构完整性
  • 语法：db2dart sample /DI /TS employee /I idx_employee_id

db2pd ​

  • 用于监控索引状态和碎片化程度
  • 语法：db2pd -d sample -indexes

reorgchk ​

  • 用于检查表和索引的重组需求
  • 语法：reorgchk -i UPDATE STATISTICS ON TABLE sample.employee

2. 第三方工具 ​

IBM Data Studio ​

  • 提供图形化界面进行索引维护
  • 支持自动生成维护脚本
  • 提供索引性能分析功能

IBM InfoSphere Optim ​

  • 用于数据库性能优化和维护
  • 提供高级索引分析和优化建议
  • 支持自动化维护任务调度

版本差异 ​

DB2 9.7及以下 ​

  • 索引重组只能离线进行
  • 不支持REORG INDEX RECREATE命令
  • 统计信息更新功能相对简单

DB2 10.1 ​

  • 引入在线索引重组功能
  • 增强了统计信息收集能力
  • 支持并行索引维护操作

DB2 10.5 ​

  • 引入REORG INDEX RECREATE命令
  • 优化了索引重组算法
  • 支持更细粒度的维护控制

DB2 11.1及以上 ​

  • 增强了自动维护功能
  • 引入索引压缩增强
  • 优化了大索引的维护性能

生产实践 ​

1. 制定详细的维护计划 ​

  • 根据业务特点和数据变化频率制定维护计划
  • 维护窗口应避开业务高峰期
  • 建立维护任务的监控和告警机制

2. 维护前的准备工作 ​

  • 备份相关表和索引
  • 检查系统资源状况（CPU、内存、I/O）
  • 通知相关业务部门

3. 维护后的验证 ​

  • 检查索引结构完整性
  • 验证查询性能是否改善
  • 更新维护记录和文档

4. 大型表的索引维护 ​

  • 对于超大型表，考虑分区索引维护
  • 使用并行处理提高维护效率
  • 采用渐进式维护策略，避免长时间锁定表

5. 监控和告警 ​

  • 建立索引碎片化监控机制
  • 设置统计信息过时告警
  • 监控索引维护任务的执行情况

常见问题（FAQ） ​

Q1: 如何判断索引是否需要重组？ ​

A1: 可以通过以下方法判断：

  • 使用reorgchk工具检查索引的重组需求
  • 监控索引的碎片率，当碎片率超过30%时考虑重组
  • 观察查询性能变化，若性能下降明显可能需要重组索引

Q2: 索引重组和重建有什么区别？ ​

A2: 主要区别在于：

  • 重组（REORG）：在原有索引结构基础上整理碎片，开销较小，可在线进行
  • 重建（RECREATE）：删除并重新创建索引，开销较大，但能彻底优化索引结构
  • 选择依据：根据索引碎片化程度、系统资源和业务需求决定

Q3: 如何优化索引维护的性能？ ​

A3: 优化建议：

  • 在维护窗口进行，避开业务高峰期
  • 使用并行处理提高维护效率
  • 合理配置缓冲池大小
  • 对于大型表，考虑分区维护策略

Q4: 自动维护和手动维护哪个更好？ ​

A4: 各有优缺点：

  • 自动维护：节省人力，适合常规维护任务
  • 手动维护：更灵活，适合复杂场景和特殊需求
  • 建议：结合使用，常规任务自动执行，复杂任务手动处理

Q5: 索引维护会影响数据库性能吗？ ​

A5: 是的，索引维护会消耗系统资源：

  • 在线维护：影响较小，但会增加系统负载
  • 离线维护：会锁定表，影响用户访问
  • 建议：在维护窗口进行，合理规划维护顺序，监控系统资源使用