含IN子句的Select SQL因b列过滤执行缓慢的调优咨询

问题背景 #

我有一张包含a、b、c、d、e列的表，执行以下SQL时，b in (b1, b2, b3,...)过滤条件导致查询异常缓慢：

select a, b, c, d, e 
from table 
where
    upper(a) in (a1)
    and b in (b1, b2, b3,..., b1000)
    and c in (c1, c2, c3,..., c10000)

a、b、c三列均已创建索引，但移除b列过滤后，SQL执行耗时<500ms；移除c列过滤时，返回数据量和保留b列时相近，但保留b列过滤的查询耗时超过2.5s。

b、c列均为字符串类型，核心区别：

• c列值长度固定为5
• b列值长度不固定（多数为5字符，但2%的字符长度超过20）

尝试调整inline size无效果，b列索引效率明显低于其他列，求性能提升方案。

表结构配置（Ignite代码） #

CacheConfiguration<AffinityKey<String>, Object> dataCacheCfg = new CacheConfiguration<>();
dataCacheCfg.setName(tableName);
QueryEntity queryEntity = new QueryEntity(AffinityKey.class, Object.class)
        .setTableName(tableName);

queryEntity.addQueryField(a, "String.class", null);
queryEntity.addQueryField(b, "String.class", null);
queryEntity.addQueryField(c, "String.class", null);
queryEntity.addQueryField(d, "String.class", null);
queryEntity.addQueryField(e, "String.class", null);
...

List<QueryIndex> queryIndices = new ArrayList<>();
queryIndices.add(new QueryIndex(a));
queryIndices.add(new QueryIndex(b));
queryIndices.add(new QueryIndex(c));

queryEntity.setIndexes(queryIndices);
dataCacheCfg.setQueryEntities(List.of(queryEntity));
ignite.getOrCreateCache(dataCacheCfg);

SQL执行计划（EXPLAIN结果） #

SELECT
    "__Z0"."b" AS "__C0_0",
    "__Z0"."d" AS "__C0_1",
    "__Z0"."e" AS "__C0_2",
    "__Z0"."f" AS "__C0_3",
    "__Z0"."c" AS "__C0_4",
    "__Z0"."g" AS "__C0_5",
    "__Z0"."a" AS "__C0_6"
FROM "my_table"."MY_TABLE" "__Z0"
    /* my_table.MY_TABLE_B_ASC_IDX: b IN('b1', 'b2', ..., 'b1000') */
WHERE ("__Z0"."c" IN('c1', 'c2', ..., 'c10000'))
    AND (("__Z0"."b" IN('b1', 'b2', ..., 'b1000'))
    AND (UPPER("__Z0"."a") = 'a1'))

优化方案 #

1. 单独设置b列索引的内联长度 #

之前调整inline size无效，可能是没针对b列单独配置。由于b列存在超长值，默认内联大小无法覆盖大部分条目，导致大量索引查询需要回表。显式为b列设置足够大的inline size：

// 根据b列最长值设定，比如64字节
queryIndices.add(new QueryIndex(b).setInlineSize(64));

2. 创建组合索引 #

优先将选择性高的列放在组合索引前列，比如创建(upper(a), c, b)或(a, c, b)组合索引，让查询直接通过索引完成过滤，减少回表次数。如果你的Ignite版本支持函数索引，建议给upper(a)单独建索引替代原a列索引：

queryIndices.add(new QueryIndex("UPPER(a)", QueryIndexType.SORTED));

3. 拆分IN子句或改用临时表关联 #

b列的1000个取值可以拆分成多个小批量IN子句，降低单条IN的匹配压力：

select a, b, c, d, e 
from table 
where
    upper(a) = 'a1'
    and (b in (b1,b2,...,b100) or b in (b101,...,b200) ...)
    and c in (c1, c2,...,c10000)

或者用临时表加载b的取值后关联查询，Ignite对关联查询的优化可能优于大IN子句：

CREATE TEMPORARY TABLE temp_b (val VARCHAR);
INSERT INTO temp_b VALUES ('b1'), ('b2'), ..., ('b1000');

select t.a, t.b, t.c, t.d, t.e 
from table t
join temp_b tb on t.b = tb.val
where upper(t.a) = 'a1' and t.c in (c1,...,c10000);

4. 切换b列索引类型 #

默认排序索引（SORTED）对超长字符串的匹配效率有限，IN子句本质是多等值查询，可以尝试改用哈希索引（HASH）：

queryIndices.add(new QueryIndex(b, QueryIndexType.HASH));

注意哈希索引仅支持等值查询，不适用范围查询，但你的场景刚好匹配。

5. 更新表统计信息 #

Ignite优化器依赖最新的统计信息选择执行计划，执行以下命令更新统计：

ANALYZE TABLE my_table.MY_TABLE;

6. 检查数据分布 #

由于使用了AffinityKey，确认b列值是否在节点上均匀分布。如果存在数据倾斜，会导致部分节点负载过高拖慢查询，可调整亲和键或分区策略平衡数据。

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