DB2索引能否覆盖JOIN、WHERE及GROUP BY列？查询未用全索引求解

一、DB2索引能否覆盖JOIN、WHERE和GROUP BY列？ #

DB2支持创建覆盖索引，可以同时满足JOIN关联、WHERE过滤以及GROUP BY分组的需求，甚至可以包含聚合函数所需的列，从而避免回表访问和临时表排序操作。

二、你的查询及索引问题分析 #

查询语句 #

Select A.REF, A.MARKET, MAX(CREATE_TS) FROM ACTION A
INNER JOIN ACCESS AC ON A.ACC_ID = AC.ACC_ID AND AC.GROUP = 10 AND AC.PROF = 1
WHERE A.IND = 'MAND'
GROUP BY A.REF, A.MARKET

现有索引 #

ON ACTION (ACC_ID, IND, REF, MARKET)
ON ACCESS (ACC_ID, GROUP, PROF)

问题症结 #

• ACTION表的索引虽包含GROUP BY所需的REF和MARKET列，但缺少聚合函数依赖的CREATE_TS列，数据库无法直接通过索引计算MAX(CREATE_TS)，必须回表获取该字段数据，后续GROUP BY操作也无法利用索引的有序性，只能借助临时表完成排序。
• 虽然当前ACTION索引的列顺序能满足JOIN（ACC_ID）和WHERE（IND）的过滤需求，但因缺失关键的聚合列，无法实现完全覆盖。

三、优化方案 #

1. 调整ACTION表为覆盖索引 #

将ACTION的索引修改为包含CREATE_TS列，让索引覆盖查询所需的所有字段：

-- 适用于多数DB2版本（LUW/z/OS）
CREATE INDEX IDX_ACTION_COVER ON ACTION (ACC_ID, IND, REF, MARKET) INCLUDE (CREATE_TS);

若使用DB2 for z/OS且不支持INCLUDE语法，也可将CREATE_TS加入索引键（优先推荐INCLUDE，避免不必要的索引排序开销）：

CREATE INDEX IDX_ACTION_COVER ON ACTION (ACC_ID, IND, REF, MARKET, CREATE_TS);

该索引包含了JOIN关联列、WHERE过滤列、GROUP BY分组列以及聚合计算列，数据库可直接通过索引完成所有查询逻辑，无需回表或临时表排序。

2. 保留ACCESS表索引的有效性 #

ACCESS表的索引(ACC_ID, GROUP, PROF)列顺序合理：先关联列ACC_ID，再过滤列GROUP、PROF，能高效支持JOIN中的过滤条件，无需修改。

四、优化后效果 #

修改后的覆盖索引可让查询执行流程变为：

1. 通过ACCESS索引快速筛选出符合GROUP=10和PROF=1的ACC_ID集合
2. 关联ACTION索引，过滤出IND='MAND'的记录
3. 直接利用索引中REF, MARKET的有序性完成分组，同时计算MAX(CREATE_TS)
4. 全程无需访问表数据，也无需临时表排序，查询性能会显著提升

内容的提问来源于stack exchange，提问作者sivarajak