H2数据库使用IN运算符查询缓慢，UNION等效查询却正常

问题本质 #

在H2 2.1.214版本中，当表的复合主键为(type, name)时，执行WHERE name = 'xxx' AND type IN ('a', 'b')这类查询时，优化器无法正确利用复合主键索引，导致全索引扫描（scanCount高达200001）；但改用UNION或CTE JOIN的等效查询时，却能精准命中索引（scanCount仅为2）。

原因分析 #

H2的查询优化器对复合索引的非前缀列搭配IN条件的场景支持存在局限：

• 复合主键(type, name)的索引排序逻辑是先按type分组，再在每个组内按name排序。
• 当查询条件为name = 常量 AND type IN (...)时，优化器没有将其拆解为多个name=常量 AND type=单个值的精准查询（如UNION的分支），而是选择通过name字段做范围扫描（name是索引的非前缀列，无法精准定位），再过滤type，导致扫描大量索引条目。
• 额外创建idx_type单字段索引也无效，因为单字段索引无法同时匹配name和type的联合条件，优化器仍会选择低效的扫描方式。

可行解决方案 #

1. 手动改写为UNION ALL（推荐） #

将IN查询拆分为多个精准匹配的子查询，用UNION ALL组合（无需去重，比UNION更高效）：

SELECT * FROM test WHERE name = 'NAME9999' AND type = 'TYPE1' 
UNION ALL
SELECT * FROM test WHERE name = 'NAME9999' AND type = 'TYPE2';

每个子查询都能精准匹配复合主键的完整键，触发索引的精准查找，保持极低的扫描量。

2. 使用JOIN替代IN #

将IN的取值集合转为临时表，通过JOIN关联查询：

WITH types AS (SELECT * FROM ( VALUES ('TYPE1'), ('TYPE2') ) AS t(type) )
SELECT * FROM test 
  JOIN types t ON test.type = t.type
 WHERE name = 'NAME9999';

这种方式会让优化器以嵌套循环的方式，对每个type值执行一次精准索引查找，同样高效。

3. 升级H2版本 #

H2后续版本可能修复了该优化器缺陷，升级后可能无需改写查询即可自动利用索引，可查看官方发布说明确认。

4. 调整索引结构（备选） #

若能控制表结构，可创建针对查询条件的复合索引(name, type)，但此方案仅针对当前查询有效，无法解决其他字段的类似问题。

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