DB2 LUW中关联查询未使用VARCHAR列索引的原因排查

问题现象 #

1. 单表查询正常使用索引
   当对RELATIONS表执行单表点查时，WHERE条件匹配ACCOUNT_1='TEST'，执行计划显示使用IX_RELATIONS索引的IXSCAN操作：

SELECT RELATION_ID
  FROM RELATIONS
 WHERE ACCOUNT_1 = 'TEST'

Explain Plan
ID | Operation                   |                     Rows | Cost
 1 | RETURN                      |                          |   39
 2 |  FETCH RELATIONS            |         2 of 2 (100.00%) |   39
 3 |   IXSCAN IX_RELATIONS       | 2 of 243069485 (   .00%) |   30

2. 关联查询未使用索引
   当RELATIONS与ACCOUNTS通过ACCOUNT_1和ACCOUNT列（均为VARCHAR(255)类型）关联查询时，RELATIONS表执行全表扫描（TBSCAN），未使用IX_RELATIONS索引，执行计划如下：

SELECT R.RELATION_ID
  FROM RELATIONS as R
  JOIN ACCOUNTS  as A
    ON A.ACCOUNT = R.ACCOUNT_1

Explain Plan
 ID | Operation                      |                             Rows |     Cost
  1 | RETURN                         |                                  | 16745806
  2 |  MSJOIN                        |                   273182208 of 1 | 16745806
  3 |   TBSCAN                       | 243069488 of 243069488 (100.00%) |  3706243
  4 |    SORT                        | 243069488 of 243069488 (100.00%) |  3706243
  5 |     TBSCAN RELATIONS           |           243069488 of 243069485 |  3253808
  6 |   FILTER                       |         1 of 560701760 (   .00%) | 12961335
  7 |    IXSCAN IX_ACCOUNTS          |           560701760 of 560701737 | 12961335

表与索引信息 #

• RELATIONS表：ACCOUNT_1列类型为VARCHAR(255)、NOT NULL，对应索引IX_RELATIONS为ACCOUNT_1 ASC VARCHAR(255)
• ACCOUNTS表：ACCOUNT列类型为VARCHAR(255)、NOT NULL

测试观察 #

• 当添加ACCOUNTS表的CREATE_DT条件，仅查询5小时数据（约232k条）时，关联查询可正常使用IX_RELATIONS索引
• 当查询范围扩大到4天数据（约190万条）时，仍不使用索引

原因分析 #

DB2优化器基于成本选择执行计划，核心原因是连接数据量的变化导致成本计算结果不同：

1. 当ACCOUNTS返回数据量较小时（如232k条），优化器认为采用嵌套循环连接更高效：即对ACCOUNTS的每一行，通过IX_RELATIONS索引查询RELATIONS表，总查询成本低于全表扫描。
2. 当ACCOUNTS返回数据量较大时（如190万条），优化器计算后认为：全表扫描RELATIONS并排序，再与排序后的ACCOUNTS执行合并连接（MSJOIN）的总成本更低。因为此时嵌套循环需要执行190万次索引查找，累计成本远高于全表扫描+排序的成本。
3. 单表点查时，仅需匹配少量行，索引扫描的成本远低于全表扫描，因此优化器选择索引。

解决方法 #

1. 更新统计信息 #

确保表和索引的统计信息准确，让优化器能正确计算执行成本：

-- 更新RELATIONS表及索引统计信息
RUNSTATS ON TABLE RELATIONS AND INDEXES ALL;
-- 更新ACCOUNTS表及索引统计信息
RUNSTATS ON TABLE ACCOUNTS AND INDEXES ALL;

2. 使用优化器提示强制索引或连接方式 #

通过提示引导优化器选择索引或特定连接类型，但需注意：仅在确认该方式长期适用时使用，避免数据量变化后性能下降。

• 强制使用IX_RELATIONS索引：

SELECT /*+ USE_INDEX(R IX_RELATIONS) */ R.RELATION_ID
  FROM RELATIONS as R
  JOIN ACCOUNTS  as A
    ON A.ACCOUNT = R.ACCOUNT_1

• 强制使用嵌套循环连接（适合ACCOUNTS数据量较大但仍想使用索引的场景）：

SELECT /*+ NLJOIN(R A) */ R.RELATION_ID
  FROM RELATIONS as R
  JOIN ACCOUNTS  as A
    ON A.ACCOUNT = R.ACCOUNT_1

3. 创建覆盖索引 #

当前IX_RELATIONS仅包含ACCOUNT_1列，查询需要回表FETCHRELATION_ID。创建包含RELATION_ID的覆盖索引，可消除回表操作，降低索引扫描的成本，让优化器更倾向于选择索引：

CREATE INDEX IX_RELATIONS_COVER ON RELATIONS(ACCOUNT_1) INCLUDE(RELATION_ID);

4. 调整优化器参数（谨慎使用） #

若上述方法无效，可考虑调整优化器的成本计算参数，如修改DB2_OPTIMIZATION_LEVEL（例如从默认的5调整为3，降低优化器对合并连接的偏好），但该操作会影响全局查询，需充分测试后再执行。

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