DB2 - 索引

Db2 - 提升性能的索引

本章将探讨 Db2 索引，它是加速查询性能的关键工具。我们将涵盖索引的类型、创建和设计最佳实践。

什么是索引？

索引是一种数据结构，它提供了快速查找表中数据的路径，就像书后的索引允许您快速找到某个主题而无需阅读每一页一样。如果没有索引，Db2 将不得不执行“全表扫描”（full table scan），读取每一行以查找与查询条件匹配的数据，这对于大型表来说非常缓慢。

索引类型

唯一索引与非唯一索引

唯一索引（Unique Index）强制表中任意两行不能在索引列中具有相同的值。当您定义主键（PRIMARY KEY）或唯一约束（UNIQUE constraint）时，它会自动创建。非唯一索引（Non-Unique Index）允许重复值，纯粹用于提升性能。

聚簇索引

聚簇索引（Clustering Index）决定了表中数据的物理顺序。由于数据是物理排序的，它对于范围查询（例如，WHERE order_date BETWEEN '2023-01-01' AND '2023-01-31'）非常高效。一个表只能有一个聚簇索引。

INCLUDE 索引

带有 INCLUDE 子句的索引在其叶节点中直接包含额外的列数据。这允许“仅通过索引访问”（index-only access），即 Db2 可以完全从索引中回答查询，而无需访问表数据，从而显著提高性能。

其他高级索引

• 表达式索引：在函数或表达式的结果上创建索引，当查询频繁根据计算值（例如，UPPER(last_name)）进行过滤时非常有用。
• 随机键索引：有助于缓解在高并发插入量环境中的页面争用（“热点”）。
• 块索引：与多维聚簇（MDC）表自动配合使用，提供高效的多维数据访问。

索引管理

创建索引

使用 CREATE INDEX 语句。语法很简单，但选择正确的列和选项是关键。

-- 语法
CREATE [UNIQUE] INDEX <index_name>
ON <table_name> ( <column_name> [ASC|DESC], ... )
[INCLUDE ( <include_column>, ... )]
[CLUSTER];

-- 示例 1：在 email 列上创建唯一索引
CREATE UNIQUE INDEX UQ_CUST_EMAIL
ON CUSTOMERS (EMAIL);

-- 示例 2：为 Orders 表创建复合聚簇索引
-- 这对于查找特定订单中的所有项目非常有用
CREATE INDEX IDX_ORDER_ITEMS
ON ORDER_ITEMS (ORDER_ID ASC, ITEM_ID ASC)
CLUSTER;

-- 示例 3：带有 INCLUDE 子句的索引，用于仅索引访问
-- 此查询仅通过索引即可回答：
-- SELECT last_name, first_name FROM employees WHERE department_id = 100;
CREATE INDEX IDX_EMP_DEPT
ON EMPLOYEES (DEPARTMENT_ID)
INCLUDE (LAST_NAME, FIRST_NAME);

删除索引

如果索引不再需要或正在影响写入性能，您可以使用 DROP INDEX 删除它。

-- 语法
DROP INDEX <index_name>;

-- 示例
DROP INDEX IDX_EMP_DEPT;

索引设计最佳实践

• 选择性地索引：对经常用于 WHERE、JOIN、ORDER BY 和 GROUP BY 子句的列进行索引。
• 使用复合索引：如果您经常在多个列上进行过滤，请创建一个单一的复合索引（例如，在 (last_name, first_name) 上），而不是两个单独的索引。列的顺序很重要！
• 不要过度索引：索引并非没有成本。它们会占用磁盘空间并降低写入操作（INSERT、UPDATE、DELETE）的速度，因为索引也必须更新。找到正确的平衡点。
• 监控索引使用情况：使用 Db2 的监控工具查找未使用的索引，这些索引是删除的候选对象。
• 保持统计信息最新：Db2 优化器依赖于您的数据统计信息来决定是否使用索引。定期运行 RUNSTATS 命令，尤其是在数据大量更改之后。

故障排除：我的索引是否正在使用？

db2exfmt 工具会生成一个查询优化器访问计划的详细报告。这是查看您的索引是否有效使用的明确方法。

-- 1. 解释查询而不执行它
EXPLAIN PLAN FOR SELECT * FROM EMPLOYEES WHERE LAST_NAME = 'Smith';

-- 2. 将解释数据格式化为可读报告
db2exfmt -d <database_name> -g -1 -o smith_query_plan.txt

-- 现在，检查 smith_query_plan.txt。查找 'IXSCAN'（索引扫描）
-- 操作。如果您看到 'TBSCAN'（表扫描），则表示未使用索引。