印刻数据库

外观

DB2 索引评估 ​

索引评估概述 ​

索引评估是数据库性能优化的重要环节，通过对现有索引的使用情况、效率和影响进行全面分析，识别低效或冗余索引，提出优化建议，从而提高数据库查询性能和降低系统资源消耗。

索引评估目标 ​

1. 识别低效索引 ​

  • 从未使用或很少使用的索引
  • 选择性差的索引
  • 重复或冗余的索引
  • 不适合查询模式的索引

2. 评估索引性能 ​

  • 索引的使用频率
  • 索引的选择性
  • 索引对查询性能的提升效果
  • 索引维护成本

3. 优化索引策略 ​

  • 删除或合并低效索引
  • 添加缺失的索引
  • 调整索引结构和类型
  • 优化索引维护策略

索引评估工具 ​

1. DB2自带工具 ​

db2pd ​

  • 提供数据库性能的实时快照
  • 包含索引使用情况统计
  • 支持多种过滤选项
bash
# 查看索引使用情况
db2pd -d <dbname> -indexusage

# 查看表空间和索引统计
db2pd -d <dbname> -tablespaces -indexes

db2get snapshot ​

  • 提供详细的数据库快照信息
  • 包含索引使用统计
  • 支持按数据库、表或索引过滤
bash
# 获取数据库级别的索引快照
db2 get snapshot for database on <dbname> | grep -i index

# 获取表级别的索引快照
db2 get snapshot for tables on <dbname> | grep -i index

RUNSTATS ​

  • 更新表和索引的统计信息
  • 为优化器提供准确的成本估算依据
  • 支持在线运行
bash
# 收集表和索引的统计信息
db2 runstats on table <schema>.<table> with distribution and indexes all

# 收集特定索引的统计信息
db2 runstats on table <schema>.<table> for indexes <index_name> with distribution

REORGCHK ​

  • 检查表和索引的重组需求
  • 评估索引的物理状态
  • 提供重组建议
bash
# 检查表和索引的重组需求
db2 reorgchk update statistics on table all

# 检查特定表的重组需求
db2 reorgchk update statistics on table <schema>.<table>

2. IBM Data Studio ​

  • 图形化界面，易于使用
  • 提供索引使用情况分析
  • 支持索引建议和优化
  • 集成多种DB2管理功能

3. 第三方工具 ​

  • IBM InfoSphere Optim Performance Manager
  • Quest Spotlight on DB2
  • BMC Performance Manager for DB2

索引评估指标 ​

1. 索引使用频率 ​

  • 索引扫描次数：索引被扫描的次数
  • 索引查找次数：通过索引查找数据的次数
  • 索引使用率：索引使用次数与表访问次数的比值

2. 索引选择性 ​

  • 唯一值数量：索引列包含的唯一值数量
  • 选择性：唯一值数量与表行数的比值
  • 高选择性：选择性接近1，适合创建索引
  • 低选择性：选择性接近0，不适合创建索引

3. 索引维护成本 ​

  • 索引更新次数：INSERT、UPDATE、DELETE操作导致的索引更新次数
  • 索引大小：索引占用的存储空间
  • 索引重组频率：索引需要重组的频率
  • 索引统计更新成本：收集索引统计信息的时间和资源消耗

4. 查询性能提升 ​

  • 查询响应时间：使用索引前后的查询响应时间对比
  • 逻辑读数量：使用索引前后的逻辑读数量对比
  • 物理读数量：使用索引前后的物理读数量对比
  • CPU使用率：使用索引前后的CPU使用率对比

索引评估步骤 ​

1. 收集索引统计信息 ​

  • 运行RUNSTATS更新表和索引的统计信息
  • 确保统计信息的准确性和完整性
  • 定期收集统计信息，建议在业务低峰期执行

2. 分析索引使用情况 ​

  • 使用db2pd或快照功能查看索引使用统计
  • 识别从未使用或很少使用的索引
  • 分析索引的使用模式和频率

3. 评估索引选择性 ​

  • 计算索引的选择性
  • 识别选择性差的索引
  • 分析索引列的分布情况

4. 分析查询执行计划 ​

  • 使用EXPLAIN或db2exfmt分析查询执行计划
  • 查看索引是否被优化器使用
  • 分析索引对查询性能的影响
bash
# 生成查询执行计划
db2 explain plan for <sql_query>

# 格式化并查看执行计划
db2exfmt -d <dbname> -1 -o explain_output.txt

5. 评估索引维护成本 ​

  • 分析索引更新次数与表更新次数的比值
  • 评估索引大小与表大小的比值
  • 考虑索引重组和统计更新的成本

6. 识别冗余索引 ​

  • 查找包含相同列的索引
  • 查找覆盖范围重叠的索引
  • 分析索引的前缀列是否重复

7. 提出优化建议 ​

  • 删除或禁用未使用的索引
  • 合并或重组冗余索引
  • 添加缺失的索引
  • 调整索引结构和类型
  • 优化索引维护策略

索引优化策略 ​

1. 删除或禁用低效索引 ​

  • 对于从未使用或很少使用的索引，考虑删除或禁用
  • 对于选择性差的索引，评估是否有必要保留
  • 对于冗余索引，保留最有效的一个

2. 添加缺失的索引 ​

  • 根据查询模式分析，识别需要索引的列
  • 考虑创建复合索引，覆盖多个查询条件
  • 评估索引的选择性和维护成本

3. 调整索引结构 ​

  • 优化索引列的顺序
  • 考虑包含列（INCLUDE）索引，减少回表操作
  • 选择合适的索引类型（B-tree、哈希、XML等）

4. 优化索引维护 ​

  • 调整RUNSTATS的运行频率和范围
  • 优化REORG的执行策略
  • 考虑使用增量RUNSTATS

5. 考虑分区索引 ​

  • 对于大型表，考虑使用分区索引
  • 提高查询性能和并行度
  • 便于索引维护和管理

索引评估最佳实践 ​

1. 定期进行索引评估 ​

  • 建议每月进行一次全面的索引评估
  • 在重大应用变更后进行索引评估
  • 在数据库性能下降时进行索引评估

2. 结合实际查询模式 ​

  • 分析应用程序的实际查询模式
  • 考虑不同时间段的查询分布
  • 优先优化高频查询

3. 评估索引维护成本 ​

  • 考虑索引对DML操作的影响
  • 评估索引存储空间需求
  • 考虑索引重组和统计更新的成本

4. 测试优化效果 ​

  • 在测试环境中验证索引优化建议
  • 比较优化前后的性能差异
  • 确保优化不会引入新的问题

5. 文档化索引策略 ​

  • 记录索引的创建原因和用途
  • 维护索引的元数据和文档
  • 定期更新索引策略文档

版本差异 ​

版本索引评估功能差异
DB2 9.7支持基本的索引统计和快照功能
DB2 10.1增强了索引使用情况跟踪，引入了更多索引统计指标
DB2 10.5引入了索引自适应功能，优化器可以自动选择最佳索引
DB2 11.1增强了索引维护功能，支持更灵活的RUNSTATS和REORG选项
DB2 11.5引入了更多索引评估工具和视图，支持实时索引使用监控

生产实践 ​

1. 索引评估自动化实践 ​

1.1 自动化脚本开发 ​

  • 需求背景：手动进行索引评估耗时耗力，难以定期执行
  • 解决方案：开发自动化脚本，实现索引评估的全流程自动化
  • 脚本功能：
    bash
    #!/bin/bash
# index_evaluation.sh - 自动化索引评估脚本

DB_NAME="mydb"
OUTPUT_DIR="/reports/index_evaluation"
CURRENT_DATE=$(date +%Y%m%d)

# 创建输出目录
mkdir -p $OUTPUT_DIR

# 1. 更新统计信息
echo "Step 1: Updating statistics..."
db2 connect to $DB_NAME
db2 runstats on table all with distribution and indexes all

# 2. 收集索引使用情况
echo "Step 2: Collecting index usage..."
db2pd -d $DB_NAME -indexusage > $OUTPUT_DIR/index_usage_$CURRENT_DATE.txt

# 3. 分析索引选择性
echo "Step 3: Analyzing index selectivity..."
db2 -x "SELECT t.tabname, i.indname, i.nunique/(SELECT COUNT(*) FROM $t) as selectivity \
FROM syscat.tables t JOIN syscat.indexes i ON t.tabname = i.tabname \
WHERE t.tabschema = 'SCHEMA_NAME'" > $OUTPUT_DIR/index_selectivity_$CURRENT_DATE.csv

# 4. 识别未使用的索引
echo "Step 4: Identifying unused indexes..."
db2 -x "SELECT tabname, indname FROM syscat.indexes \
WHERE tabschema = 'SCHEMA_NAME' AND (stats_time IS NULL OR stats_time < CURRENT_TIMESTAMP - 30 DAYS)" > $OUTPUT_DIR/unused_indexes_$CURRENT_DATE.txt

# 5. 生成评估报告
echo "Step 5: Generating evaluation report..."
echo "# DB2 索引评估报告 - $CURRENT_DATE" > $OUTPUT_DIR/index_report_$CURRENT_DATE.md
echo "## 索引使用情况" >> $OUTPUT_DIR/index_report_$CURRENT_DATE.md
echo "\`\`\`" >> $OUTPUT_DIR/index_report_$CURRENT_DATE.md
cat $OUTPUT_DIR/index_usage_$CURRENT_DATE.txt >> $OUTPUT_DIR/index_report_$CURRENT_DATE.md
echo "\`\`\`" >> $OUTPUT_DIR/index_report_$CURRENT_DATE.md

echo "## 低选择性索引" >> $OUTPUT_DIR/index_report_$CURRENT_DATE.md
echo "\`\`\`csv" >> $OUTPUT_DIR/index_report_$CURRENT_DATE.md
grep -E "(0\.[0-4]|^0,)" $OUTPUT_DIR/index_selectivity_$CURRENT_DATE.csv >> $OUTPUT_DIR/index_report_$CURRENT_DATE.md
echo "\`\`\`" >> $OUTPUT_DIR/index_report_$CURRENT_DATE.md

echo "## 未使用的索引" >> $OUTPUT_DIR/index_report_$CURRENT_DATE.md
echo "\`\`\`" >> $OUTPUT_DIR/index_report_$CURRENT_DATE.md
cat $OUTPUT_DIR/unused_indexes_$CURRENT_DATE.txt >> $OUTPUT_DIR/index_report_$CURRENT_DATE.md
echo "\`\`\`" >> $OUTPUT_DIR/index_report_$CURRENT_DATE.md

db2 disconnect all
echo "Index evaluation completed successfully! Report saved to $OUTPUT_DIR/index_report_$CURRENT_DATE.md"

1.2 自动化调度与监控 ​

  • 使用cron调度定期执行：
    bash
    # 每月1号凌晨3点执行索引评估
0 3 1 * * /path/to/index_evaluation.sh >> /var/log/index_eval.log 2>&1
  • 监控与告警：
    • 集成监控系统，实时监控脚本执行情况
    • 当发现大量未使用索引或低选择性索引时发送告警
    • 生成可视化报告，便于管理层查看

2. 大型表索引评估实践 ​

2.1 分区表索引评估 ​

  • 案例背景：企业有一个TB级分区表，索引数量众多，评估难度大
  • 实施策略：
    1. 分区级评估：
      sql
      -- 分析每个分区的索引使用情况
SELECT data_partition_name, indname, iuse_active FROM sysibmadm.indexuse 
WHERE tabname = 'LARGE_TABLE' AND tabschema = 'SCHEMA_NAME';
    2. 增量评估：每次评估一个分区，避免系统负载过高
    3. 并行评估：利用DB2的并行处理能力，提高评估效率
    4. 分区索引优化：针对不同分区的查询模式，调整索引策略

2.2 索引评估性能优化 ​

  • 问题：大型表索引评估耗时过长，影响生产系统性能
  • 解决方案：
    bash
    # 使用增量RUNSTATS减少评估时间
db2 runstats on table SCHEMA_NAME.LARGE_TABLE with distribution and indexes all allow write access incremental

# 使用采样方式收集统计信息
db2 runstats on table SCHEMA_NAME.LARGE_TABLE with sample 10 percent and indexes all

# 在业务低峰期执行评估

3. 混合工作负载索引策略实践 ​

3.1 OLTP与OLAP混合环境 ​

  • 案例背景：同一数据库同时承载OLTP和OLAP工作负载，索引策略难以平衡
  • 实施策略：
    1. 分类评估：
      • 对OLTP查询：优化点查询和短事务，使用B-tree索引
      • 对OLAP查询：优化复杂查询和分析，使用列组织表和多维索引
    2. 时间分片评估：
      sql
      -- 分析不同时间段的查询模式
SELECT hour(timestamp), query_text, count(*) 
FROM sysibmadm.long_running_queries 
GROUP BY hour(timestamp), query_text 
ORDER BY count(*) DESC;
    3. 动态索引调整：根据不同时间段的工作负载，动态调整索引策略

3.2 索引与物化查询表(MQT)结合 ​

  • 应用场景：频繁执行的聚合查询，单独使用索引效果不佳
  • 实施方法：
    sql
    -- 创建物化查询表
CREATE TABLE sales_summary_mqt AS 
(SELECT product_id, region, SUM(sales_amount) as total_sales 
 FROM sales GROUP BY product_id, region)
DATA INITIALLY DEFERRED REFRESH DEFERRED;

-- 同时维护聚合列上的索引
CREATE INDEX idx_sales_summary ON sales(product_id, region);

-- 定期刷新MQT
REFRESH TABLE sales_summary_mqt;

4. 索引评估与业务周期结合实践 ​

4.1 业务高峰期前的索引优化 ​

  • 案例背景：企业即将迎来业务高峰期，需要确保索引策略能够应对高并发查询
  • 实施步骤：
    1. 历史数据分析：分析去年同期的查询模式和索引使用情况
    2. 峰值负载测试：在测试环境模拟峰值负载，评估当前索引策略
    3. 预优化调整：
      sql
      -- 为预测的高频查询添加索引
CREATE INDEX idx_orders_peak ON orders(order_date, customer_id);

-- 优化现有索引的统计信息
RUNSTATS ON TABLE orders FOR INDEX idx_orders_peak WITH DISTRIBUTION;
    4. 实时监控：高峰期实时监控索引使用情况，及时调整

4.2 业务变革后的索引评估 ​

  • 应用场景：业务系统升级或新功能上线后，查询模式发生变化
  • 实施策略：
    1. 变更前后对比：对比业务变更前后的索引使用情况
    2. 新查询模式分析：识别新引入的查询模式，评估现有索引是否支持
    3. 快速调整：根据分析结果，快速添加或调整索引
    4. 持续监控：在变更后的一段时间内，持续监控索引使用情况

5. 索引评估的持续监控实践 ​

5.1 实时监控系统集成 ​

  • 需求背景：需要实时了解索引使用情况，及时发现问题
  • 解决方案：集成监控系统，实时监控索引相关指标
  • 监控指标：
    • 索引使用率
    • 索引选择性变化
    • 索引维护成本
    • 索引对查询性能的影响
  • 监控工具集成：
    bash
    # 将索引使用数据发送到监控系统
db2pd -d $DB_NAME -indexusage | grep -v "Index" | while read line; do
  # 解析并发送到监控系统（如Prometheus、Grafana等）
  echo "index_usage{db='$DB_NAME',table='$table',index='$index'} $usage" | curl --data-binary @- http://monitoring-server:9091/metrics/job/db2_index_metrics
done

5.2 异常检测与告警 ​

  • 实施方法：
    1. 设置索引使用率阈值（如低于5%持续30天）
    2. 设置索引维护成本阈值（如索引更新次数超过表更新次数的50%）
    3. 当指标超过阈值时，触发告警
    4. 告警处理流程：
      • 自动生成索引评估任务
      • 通知DBA团队
      • 提供初步优化建议

常见问题（FAQ） ​

Q1: 如何识别从未使用的索引？ ​

A1: 可以使用以下方法识别从未使用的索引：

  • 使用db2pd的-indexusage选项查看索引使用情况
  • 查看DB2快照中的索引统计信息
  • 分析查询执行计划，确认索引是否被使用
  • 启用索引使用跟踪功能

Q2: 选择性多少的索引才是有效的？ ​

A2: 索引选择性的判断标准：

  • 选择性>0.8：高选择性，适合创建索引
  • 0.5<选择性≤0.8：中等选择性，可以考虑创建索引
  • 选择性≤0.5：低选择性，不适合创建索引

但实际情况需要结合查询模式和表大小综合考虑。

Q3: 如何处理冗余索引？ ​

A3: 处理冗余索引的步骤：

  1. 识别冗余索引（包含相同列或覆盖范围重叠的索引）
  2. 分析每个索引的使用情况和性能影响
  3. 保留最有效的索引，删除或禁用其他冗余索引
  4. 在测试环境中验证删除后的性能影响
  5. 逐步在生产环境中实施

Q4: 复合索引的列顺序如何优化？ ​

A4: 复合索引列顺序的优化原则：

  • 将选择性高的列放在前面
  • 将查询中频繁使用的列放在前面
  • 考虑查询的排序和分组需求
  • 考虑索引覆盖查询的可能性

Q5: 如何评估索引维护成本？ ​

A5: 评估索引维护成本的方法：

  • 分析索引更新次数与表更新次数的比值
  • 计算索引大小与表大小的比例
  • 测量索引重组和统计更新的时间和资源消耗
  • 考虑索引对DML操作性能的影响

Q6: 什么时候应该考虑使用包含列（INCLUDE）索引？ ​

A6: 适合使用包含列索引的场景：

  • 查询需要返回的列较多，但过滤条件列较少
  • 希望避免回表操作，提高查询性能
  • 索引的选择性较高
  • 查询频率较高

Q7: 如何监控索引使用情况？ ​

A7: 监控索引使用情况的方法：

  • 使用db2pd -indexusage定期收集索引使用统计
  • 启用索引使用跟踪功能
  • 分析查询执行计划
  • 使用IBM Data Studio或其他监控工具
  • 定期查看数据库快照中的索引统计信息

Q8: 索引越多越好吗？ ​

A8: 不是，索引过多会带来以下问题：

  • 增加存储空间需求
  • 增加DML操作的开销
  • 增加索引维护成本
  • 可能导致优化器选择错误的索引
  • 增加缓冲区池竞争

因此，需要平衡索引的数量和质量，只保留必要的有效索引。

Q9: 如何处理大型表的索引评估？ ​

A9: 处理大型表索引评估的建议：

  • 分批次进行评估，避免影响生产系统性能
  • 使用增量RUNSTATS收集统计信息
  • 考虑使用分区索引，便于管理和维护
  • 优先评估高频查询使用的索引
  • 在测试环境中验证优化建议

Q10: 索引评估需要考虑哪些业务因素？ ​

A10: 索引评估需要考虑的业务因素：

  • 业务高峰期的查询模式
  • 业务增长对索引的影响
  • 不同业务部门的查询需求
  • 业务变更对查询模式的影响
  • 业务对响应时间的要求

索引评估案例分析 ​

案例：识别和删除未使用的索引 ​

问题描述：某数据库存在大量索引，但性能不佳，需要识别和删除未使用的索引。

评估步骤：

  1. 运行RUNSTATS更新统计信息
  2. 使用db2pd -indexusage收集索引使用情况
  3. 分析索引使用频率，识别从未使用的索引
  4. 评估索引维护成本和存储空间占用
  5. 在测试环境中验证删除后的性能影响
  6. 逐步在生产环境中删除未使用的索引

优化结果：

  • 删除了30%的未使用索引
  • 存储空间减少了25%
  • DML操作性能提升了20%
  • 查询性能保持稳定

案例：优化复合索引列顺序 ​

问题描述：某查询使用了复合索引，但性能不佳。

评估步骤：

  1. 分析查询执行计划
  2. 检查索引列的选择性
  3. 发现索引列顺序不合理，选择性低的列放在了前面
  4. 调整索引列顺序，将选择性高的列放在前面
  5. 重新收集统计信息
  6. 验证查询性能提升

优化结果：

  • 查询响应时间从5秒减少到0.5秒
  • 索引选择性从0.3提高到0.9
  • 逻辑读数量减少了90%

结论 ​

索引评估是数据库性能优化的重要组成部分，通过定期评估和优化索引策略，可以显著提高数据库查询性能，降低系统资源消耗，提高应用程序的响应速度和吞吐量。索引评估需要结合实际查询模式、业务需求和系统资源情况，采取综合的优化策略，持续监控和调整，以适应不断变化的业务环境。