问题背景

最近，用户在测试 ShardingSphere 联邦查询功能时，反馈了 1 条 2w6k 行的超长 SQL，执行时出现了 Communications link failure 异常，SQL 在 Proxy 接入端执行了 20 多分钟后最终超时断开。

笔者刚看到这个异常，怀疑是超长 SQL 过于复杂，导致在 SQL 解析、SQL 绑定或者生成执行计划的过程中出现问题，但是看到这条 SQL 的执行计划时，大概了解了问题的原因——Calcite 对于 IN 查询，会通过 SubQueryRemoveRule 规则，将 IN 查询转换为 JOIN。由于超长 SQL 中包含了大量的 IN 查询过滤条件，转换为 JOIN 方式执行，会导致下推的 SQL 缺失 IN 过滤条件，查询数据量相比预期要多很多，从而触发多轮磁盘文件交换，最终导致执行超时异常。

为了彻底解决 IN 查询的问题，笔者对 Calcite IN 查询内部处理逻辑进行了深入探究，通过对 SubQueryRemoveRule 规则进行优化，以及联邦查询谓词下推规则 PushFilterIntoScanRule 的适配，同时还重写了 Calcite RelToSqlConverter 中 IN 语法树转换 SQL 逻辑，最终将 2w6k 行超长 SQL 的查询耗时缩减到 1s 左右，实现了百倍以上的性能提升。

问题分析

为了深入分析这个问题，我们通过 EXPLAIN 语句来观察联邦查询的执行计划。如下展示了 2w6k 行超长 SQL 的执行计划，可以看到除了最外层的 DBPlusEngineLimitSort 算子，内部全部是由 UNION ALL 组合的多个查询语句，每个查询语句中包含了单列 IN 和多列 IN 查询条件。

原始 SQL 中的单列 IN 被转换为红色部分的 DBPlusEngineSortMergeJoin，通过关联 DBPlusEngineValues 中的常量实现 IN 过滤，而多列 IN 则被转换为蓝色部分的 DBPlusEngineHashJoin，同样通过关联 DBPlusEngineValues 中的常量实现多列 IN 过滤。这样的转换逻辑影响了 DBPlusEngineScan 中下推 SQL 的过滤条件，导致部分 IN 过滤条件没有下推下去，下推 SQL 查询的数据量过大。

为了方便问题分析，我们使用如下精简 SQL 进行探究，这条 SQL 和执行计划中的 SQL 结构一致，只是少了一些 UNION ALL 子查询，以及 IN 批量查询条件。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SELECT *
FROM (
  SELECT *
  FROM t_order
  WHERE creation_date = '2017-08-08'
    AND order_id IN (1000, 1001, 1100, 1101, 1200, 1201, 1300, 1301, 1400, 1401, 1500, 1501, 1600, 1601, 1700, 1701, 1800, 1801, 1900, 1901, 2000, 2001, 2100, 2101, 2200)
    AND (order_id, user_id, status, merchant_id) IN ((1000, 10, 'init', 1), (1001, 10, 'init', 2), (1100, 11, 'init', 5), (1101, 11, 'init', 6), (1200, 12, 'init', 9), (1201, 12, 'init', 10), (1300, 13, 'init', 13), (1301, 13, 'init', 14), (1400, 14, 'init', 17), (1401, 14, 'init', 18), (1500, 15, 'init', 1), (1501, 15, 'init', 2), (1600, 15, 'init', 5), (1601, 15, 'init', 6), (1700, 17, 'init', 9), (1701, 17, 'init', 10), (1800, 18, 'init', 13), (1801, 18, 'init', 14), (1900, 19, 'init', 17), (1901, 19, 'init', 18), (2000, 20, 'init', 3), (2001, 20, 'init', 4), (2100, 21, 'init', 7), (2101, 21, 'init', 8))
  UNION ALL
  SELECT *
  FROM t_order
  WHERE creation_date = '2017-08-08'
    AND order_id IN (1000, 1001, 1100, 1101, 1200, 1201, 1300, 1301, 1400, 1401, 1500, 1501, 1600, 1601, 1700, 1701, 1800, 1801, 1900, 1901, 2000, 2001, 2100, 2101, 2200)
    AND (order_id, user_id, status, merchant_id) IN ((1000, 10, 'init', 1), (1001, 10, 'init', 2), (1100, 11, 'init', 5), (1101, 11, 'init', 6), (1200, 12, 'init', 9), (1201, 12, 'init', 10), (1300, 13, 'init', 13), (1301, 13, 'init', 14), (1400, 14, 'init', 17), (1401, 14, 'init', 18), (1500, 15, 'init', 1), (1501, 15, 'init', 2), (1600, 15, 'init', 5), (1601, 15, 'init', 6), (1700, 17, 'init', 9), (1701, 17, 'init', 10), (1800, 18, 'init', 13), (1801, 18, 'init', 14), (1900, 19, 'init', 17), (1901, 19, 'init', 18), (2000, 20, 'init', 3), (2001, 20, 'init', 4), (2100, 21, 'init', 7), (2101, 21, 'init', 8))
) a
ORDER BY order_id ASC
LIMIT 0, 10;

我们通过 EXPLAIN 语句观察这条 SQL 的执行计划，可以看到和用户反馈 SQL 的执行计划类似，2 个 IN 过滤条件依次被转换为 DBPlusEngineLookupJoin 和 DBPlusEngineSortMergeJoin，这些 JOIN 通过关联 DBPlusEngineValues 中的常量列表来实现数据过滤。此外，DBPlusEngineScan 算子中的下推 SQL，只包含了单列 IN 过滤条件，没有包含多列 IN 过滤条件，这会导致查询到内存的数据量比预期要大，通过内存过滤多列 IN，查询的性能要差很多。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| PLAN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| DBPlusEngineLimitSort(sort0=[$0], dir0=[ASC-nulls-first], offset=[0], fetch=[10])                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                |
|   DBPlusEngineUnion(all=[true])                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  |
|     DBPlusEngineCalc(expr#0..10=[{inputs}], expr#11=['2017-08-08':CHAR(10)], expr#12=[=($t5, $t11)], proj#0..5=[{exprs}], $condition=[$t12])                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     |
|       DBPlusEngineSortMergeJoin(condition=[AND(=($0, $7), =($1, $8), =($2, $9), =($3, $10))], joinType=[inner])                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  |
|         DBPlusEngineSort(sort0=[$0], sort1=[$1], sort2=[$2], sort3=[$3], dir0=[ASC-nulls-first], dir1=[ASC-nulls-first], dir2=[ASC-nulls-first], dir3=[ASC-nulls-first])                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         |
|           DBPlusEngineCalc(expr#0..6=[{inputs}], expr#7=[Sarg[1000L:BIGINT, 1001L:BIGINT, 1100L:BIGINT, 1101L:BIGINT, 1200L:BIGINT, 1201L:BIGINT, 1300L:BIGINT, 1301L:BIGINT, 1400L:BIGINT, 1401L:BIGINT, 1500L:BIGINT, 1501L:BIGINT, 1600L:BIGINT, 1601L:BIGINT, 1700L:BIGINT, 1701L:BIGINT, 1800L:BIGINT, 1801L:BIGINT, 1900L:BIGINT, 1901L:BIGINT, 2000L:BIGINT, 2001L:BIGINT, 2100L:BIGINT, 2101L:BIGINT]:BIGINT], expr#8=[SEARCH($t0, $t7)], expr#9=[Sarg[10, 11, 12, 13, 14, 15, 17, 18, 19, 20, 21]], expr#10=[SEARCH($t1, $t9)], expr#11=['init':VARCHAR(50)], expr#12=[=($t2, $t11)], expr#13=[Sarg[1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 13, 14, 17, 18]], expr#14=[SEARCH($t3, $t13)], expr#15=[AND($t8, $t10, $t12, $t14)], proj#0..6=[{exprs}], $condition=[$t15])                                                                                                                                                                                                                                                                                         |
|             DBPlusEngineCalc(expr#0..6=[{inputs}], order_id=[$t1], user_id=[$t2], status=[$t3], merchant_id=[$t4], remark=[$t5], creation_date=[$t6], EXPR$0=[$t0])                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              |
|               DBPlusEngineLookupJoin(condition=[=($0, $1)], joinType=[inner])                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    |
|                 DBPlusEngineValues(values=[[[1000:BIGINT], [1001:BIGINT], [1100:BIGINT], [1101:BIGINT], [1200:BIGINT], [1201:BIGINT], [1300:BIGINT], [1301:BIGINT], [1400:BIGINT], [1401:BIGINT], [1500:BIGINT], [1501:BIGINT], [1600:BIGINT], [1601:BIGINT], [1700:BIGINT], [1701:BIGINT], [1800:BIGINT], [1801:BIGINT], [1900:BIGINT], [1901:BIGINT], [2000:BIGINT], [2001:BIGINT], [2100:BIGINT], [2101:BIGINT], [2200:BIGINT]]])                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             |
|                 DBPlusEngineScan(sql=[SELECT * FROM `sharding_db`.`t_order` WHERE `order_id` IN (1000, 1001, 1100, 1101, 1200, 1201, 1300, 1301, 1400, 1401, 1500, 1501, 1600, 1601, 1700, 1701, 1800, 1801, 1900, 1901, 2000, 2001, 2100, 2101, 2200) AND `order_id` IN ('...')])                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               |
|         DBPlusEngineSort(sort0=[$0], sort1=[$1], sort2=[$2], sort3=[$3], dir0=[ASC-nulls-first], dir1=[ASC-nulls-first], dir2=[ASC-nulls-first], dir3=[ASC-nulls-first])                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         |
|           DBPlusEngineCalc(expr#0..3=[{inputs}], expr#4=[Sarg[1000L:BIGINT, 1001L:BIGINT, 1100L:BIGINT, 1101L:BIGINT, 1200L:BIGINT, 1201L:BIGINT, 1300L:BIGINT, 1301L:BIGINT, 1400L:BIGINT, 1401L:BIGINT, 1500L:BIGINT, 1501L:BIGINT, 1600L:BIGINT, 1601L:BIGINT, 1700L:BIGINT, 1701L:BIGINT, 1800L:BIGINT, 1801L:BIGINT, 1900L:BIGINT, 1901L:BIGINT, 2000L:BIGINT, 2001L:BIGINT, 2100L:BIGINT, 2101L:BIGINT, 2200L:BIGINT]:BIGINT], expr#5=[SEARCH($t0, $t4)], proj#0..3=[{exprs}], $condition=[$t5])                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           |
|             DBPlusEngineValues(values=[[[1000:BIGINT, 10, 'init':VARCHAR(50), 1], [1001:BIGINT, 10, 'init':VARCHAR(50), 2], [1100:BIGINT, 11, 'init':VARCHAR(50), 5], [1101:BIGINT, 11, 'init':VARCHAR(50), 6], [1200:BIGINT, 12, 'init':VARCHAR(50), 9], [1201:BIGINT, 12, 'init':VARCHAR(50), 10], [1300:BIGINT, 13, 'init':VARCHAR(50), 13], [1301:BIGINT, 13, 'init':VARCHAR(50), 14], [1400:BIGINT, 14, 'init':VARCHAR(50), 17], [1401:BIGINT, 14, 'init':VARCHAR(50), 18], [1500:BIGINT, 15, 'init':VARCHAR(50), 1], [1501:BIGINT, 15, 'init':VARCHAR(50), 2], [1600:BIGINT, 15, 'init':VARCHAR(50), 5], [1601:BIGINT, 15, 'init':VARCHAR(50), 6], [1700:BIGINT, 17, 'init':VARCHAR(50), 9], [1701:BIGINT, 17, 'init':VARCHAR(50), 10], [1800:BIGINT, 18, 'init':VARCHAR(50), 13], [1801:BIGINT, 18, 'init':VARCHAR(50), 14], [1900:BIGINT, 19, 'init':VARCHAR(50), 17], [1901:BIGINT, 19, 'init':VARCHAR(50), 18], [2000:BIGINT, 20, 'init':VARCHAR(50), 3], [2001:BIGINT, 20, 'init':VARCHAR(50), 4], [2100:BIGINT, 21, 'init':VARCHAR(50), 7], [2101:BIGINT, 21, 'init':VARCHAR(50), 8]]]) |
|     DBPlusEngineCalc(expr#0..10=[{inputs}], expr#11=['2017-08-08':CHAR(10)], expr#12=[=($t5, $t11)], proj#0..5=[{exprs}], $condition=[$t12])                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     |
|       DBPlusEngineCalc(expr#0..10=[{inputs}], order_id=[$t5], user_id=[$t6], status=[$t7], merchant_id=[$t8], remark=[$t9], creation_date=[$t10], EXPR$0=[$t0], EXPR$00=[$t1], EXPR$1=[$t2], EXPR$2=[$t3], EXPR$3=[$t4])                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         |
|         DBPlusEngineSortMergeJoin(condition=[=($0, $5)], joinType=[inner])                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       |
|           DBPlusEngineSort(sort0=[$0], dir0=[ASC-nulls-first])                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   |
|             DBPlusEngineCalc(expr#0=[{inputs}], expr#1=[Sarg[1000L:BIGINT, 1001L:BIGINT, 1100L:BIGINT, 1101L:BIGINT, 1200L:BIGINT, 1201L:BIGINT, 1300L:BIGINT, 1301L:BIGINT, 1400L:BIGINT, 1401L:BIGINT, 1500L:BIGINT, 1501L:BIGINT, 1600L:BIGINT, 1601L:BIGINT, 1700L:BIGINT, 1701L:BIGINT, 1800L:BIGINT, 1801L:BIGINT, 1900L:BIGINT, 1901L:BIGINT, 2000L:BIGINT, 2001L:BIGINT, 2100L:BIGINT, 2101L:BIGINT]:BIGINT], expr#2=[SEARCH($t0, $t1)], EXPR$0=[$t0], $condition=[$t2])                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 |
|               DBPlusEngineValues(values=[[[1000:BIGINT], [1001:BIGINT], [1100:BIGINT], [1101:BIGINT], [1200:BIGINT], [1201:BIGINT], [1300:BIGINT], [1301:BIGINT], [1400:BIGINT], [1401:BIGINT], [1500:BIGINT], [1501:BIGINT], [1600:BIGINT], [1601:BIGINT], [1700:BIGINT], [1701:BIGINT], [1800:BIGINT], [1801:BIGINT], [1900:BIGINT], [1901:BIGINT], [2000:BIGINT], [2001:BIGINT], [2100:BIGINT], [2101:BIGINT], [2200:BIGINT]]])                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               |
|           DBPlusEngineSort(sort0=[$4], dir0=[ASC-nulls-first])                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   |
|             DBPlusEngineLookupJoin(condition=[AND(=($0, $4), =($1, $5), =($2, $6), =($3, $7))], joinType=[inner])                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                |
|               DBPlusEngineCalc(expr#0..3=[{inputs}], expr#4=[Sarg[1000L:BIGINT, 1001L:BIGINT, 1100L:BIGINT, 1101L:BIGINT, 1200L:BIGINT, 1201L:BIGINT, 1300L:BIGINT, 1301L:BIGINT, 1400L:BIGINT, 1401L:BIGINT, 1500L:BIGINT, 1501L:BIGINT, 1600L:BIGINT, 1601L:BIGINT, 1700L:BIGINT, 1701L:BIGINT, 1800L:BIGINT, 1801L:BIGINT, 1900L:BIGINT, 1901L:BIGINT, 2000L:BIGINT, 2001L:BIGINT, 2100L:BIGINT, 2101L:BIGINT, 2200L:BIGINT]:BIGINT], expr#5=[SEARCH($t0, $t4)], proj#0..3=[{exprs}], $condition=[$t5])                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       |
|                 DBPlusEngineValues(values=[[[1000:BIGINT, 10, 'init':VARCHAR(50), 1], [1001:BIGINT, 10, 'init':VARCHAR(50), 2], [1100:BIGINT, 11, 'init':VARCHAR(50), 5], [1101:BIGINT, 11, 'init':VARCHAR(50), 6], [1200:BIGINT, 12, 'init':VARCHAR(50), 9], [1201:BIGINT, 12, 'init':VARCHAR(50), 10], [1300:BIGINT, 13, 'init':VARCHAR(50), 13], [1301:BIGINT, 13, 'init':VARCHAR(50), 14], [1400:BIGINT, 14, 'init':VARCHAR(50), 17], [1401:BIGINT, 14, 'init':VARCHAR(50), 18], [1500:BIGINT, 15, 'init':VARCHAR(50), 1], [1501:BIGINT, 15, 'init':VARCHAR(50), 2], [1600:BIGINT, 15, 'init':VARCHAR(50), 5], [1601:BIGINT, 15, 'init':VARCHAR(50), 6], [1700:BIGINT, 17, 'init':VARCHAR(50), 9], [1701:BIGINT, 17, 'init':VARCHAR(50), 10], [1800:BIGINT, 18, 'init':VARCHAR(50), 13], [1801:BIGINT, 18, 'init':VARCHAR(50), 14], [1900:BIGINT, 19, 'init':VARCHAR(50), 17], [1901:BIGINT, 19, 'init':VARCHAR(50), 18], [2000:BIGINT, 20, 'init':VARCHAR(50), 3], [2001:BIGINT, 20, 'init':VARCHAR(50), 4], [2100:BIGINT, 21, 'init':VARCHAR(50), 7], [2101:BIGINT, 21, 'init':VARCHAR(50), 8]]]) |
|               DBPlusEngineScan(sql=[SELECT * FROM `sharding_db`.`t_order` WHERE `order_id` IN (1000, 1001, 1100, 1101, 1200, 1201, 1300, 1301, 1400, 1401, 1500, 1501, 1600, 1601, 1700, 1701, 1800, 1801, 1900, 1901, 2000, 2001, 2100, 2101) AND `user_id` IN (10, 11, 12, 13, 14, 15, 17, 18, 19, 20, 21) AND `status` = 'init' AND `merchant_id` IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 13, 14, 17, 18) AND (`order_id`, `user_id`, `status`, `merchant_id`) IN ('...', '...', '...', '...')])                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
24 rows in set (0.97 sec)

为了找到解决问题的方案，我们来跟踪下这条 SQL 的优化过程，观察从最开始的逻辑执行计划，依次经过 RBO 和 CBO 优化后，执行计划的变化情况。下图展示了 Calcite 生成的原始执行计划，可以看到原始 SQL 中的 2 个 IN 过滤条件，都包含在 LogicalFilter 算子的 condition 中。

经过 RBO 优化的执行计划如下图所示，此时 LogicalFilter 算子已经被优化， condition 中的 2 个 IN 过滤条件被转换为 JOIN，因此可以将问题的范围缩小到 RBO 优化过程。

由于 RBO 优化已经将 IN 转换为 JOIN，后续 CBO 继续处理时，只能基于 JOIN 的运算方式，选择代价最小的物理算子，无法再对执行方式进行修改。

现在我们已经将问题的范围缩小到 RBO 优化过程，但是 RBO 优化使用的优化规则很多，具体是哪一个优化规则导致的呢？我们首先来 Debug，观察原始逻辑执行计划生成的过程，了解 IN 算子在 RelNode 中是如何表示的，再根据 RelNode 去排查优化规则。

Calcite 在转换逻辑执行计划时，会从 WHERE 中首先提取出 IN 子查询，然后尝试将 IN 子查询进行替换，如下是 replaceSubQueries 实现逻辑：

1
2
3
4
5
6
7
8
protected void replaceSubQueries(final Blackboard bb, final SqlNode expr, RelOptUtil.Logic logic) {
    // 查找 expr 中的子查询，本案例中是查找 where 条件的 in 子查询
    findSubQueries(bb, expr, logic, false);
    for (SubQuery node : bb.subQueryList) {
        // 替换 in 子查询
        substituteSubQuery(bb, node);
    }
}

在 substituteSubQuery 替换逻辑中，会判断当前 IN 运算值的个数，如果个数小于 20（或者值列表中引用了列），则会将 IN 转换为 OR 拼接的条件。由于我们的查询 Case 模拟业务场景，IN 个数都超过了 20，感兴趣的朋友可以自行尝试，生成对应的执行计划。

由于我们的 IN 值个数不小于 20，逻辑会继续向下执行，将 IN 转换为 RexSubQuery 子查询。从源码注释中，我们也可以看到，后续这个对象会通过 SubQueryRemoveRule 规则进行优化。

如下图所示，在 Calcite 最终生成的逻辑执行计划中，IN 被表示为 RexSubQuery 对象。而在 RBO 优化中，和子查询转 JOIN 相关的规则是 CoreRules.FILTER_SUB_QUERY_TO_CORRELATE，内部实现对应的是 SubQueryRemoveRule 规则，负责将谓词中的子查询转换为其他结构。

我们来具体看下 CoreRules.FILTER_SUB_QUERY_TO_CORRELATE 的配置，这个优化规则匹配 Filter 中包含子查询的场景，通过 RexUtil.SubQueryFinder::containsSubQuery 来判断谓词中是否包含子查询。

1
2
3
4
5
6
7
Config FILTER = ImmutableSubQueryRemoveRule.Config.builder()
    .withMatchHandler(SubQueryRemoveRule::matchFilter)
    .build()
    .withOperandSupplier(b ->
        b.operand(Filter.class)
            .predicate(RexUtil.SubQueryFinder::containsSubQuery).anyInputs())
    .withDescription("SubQueryRemoveRule:Filter");

当规则匹配到谓词中的 IN 子查询时，会继续调用 SubQueryRemoveRule::matchFilter 对关系代数进行变换，方法内部会判断子查询的类型，本案例 Case 是 IN 子查询，最终会调用到 rewriteIn 方法。

如上图所示，IN 子查询会被改写为 JOIN 关联查询。这就是本案例查询慢的根本原因，对于 IN 常量集合，无需进行改写，只需要将 IN 条件完整地下推到底层数据库，就可以提前过滤掉大部分数据，从而提升查询性能。

问题解决

研究清楚问题的根本原因后，我们的目标就很明确了——重写 SubQueryRemoveRule 规则，IN 常量集合子查询不改写为 JOIN，保留原始的 Filter，并将 Filter 下推到 DBPlusEngineScan 中。

如下图所示，我们先尝试重写 SubQueryRemoveRule 规则，并修改规则匹配条件，当 Filter 条件中完全是 LogicalValues 时，则不使用 SubQueryRemoveRule 将 IN 改写为 JOIN。确保执行计划中仍然包含 Filter 算子，能够实现 IN 谓词下推。

然后我们再修改 PushFilterIntoScanRule 谓词下推规则，当 Filter 过滤条件是 IN 常量值子查询时，支持将这类 Filter 下推到 Scan 中。

修改完成后，我们再次执行 EXPLAIN 语句，可以看到 IN 子查询已经被下推到 DBPlusEngineScan 中，但是 RelNode 转 SQL 时，将 LogicalValues 转换为了多个 UNION ALL 查询。在 MySQL 上测试了下，执行效率没有 IN 子查询高，需要再调整下 RelToSqlConverter#visit(Values e) 方法，转换为 MySQL 原始的 IN 语句。

1
2
3
4
5
6
7
8
9
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| PLAN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| DBPlusEngineLimitSort(sort0=[$0], dir0=[ASC-nulls-first], offset=[0], fetch=[10])                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                |
|   DBPlusEngineUnion(all=[true])                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  |
|     DBPlusEngineScan(sql=[SELECT * FROM `sharding_db`.`t_order` WHERE `creation_date` = '2017-08-08' AND `order_id` IN (SELECT 1000 UNION ALL SELECT 1001 UNION ALL SELECT 1100 UNION ALL SELECT 1101 UNION ALL SELECT 1200 UNION ALL SELECT 1201 UNION ALL SELECT 1300 UNION ALL SELECT 1301 UNION ALL SELECT 1400 UNION ALL SELECT 1401 UNION ALL SELECT 1500 UNION ALL SELECT 1501 UNION ALL SELECT 1600 UNION ALL SELECT 1601 UNION ALL SELECT 1700 UNION ALL SELECT 1701 UNION ALL SELECT 1800 UNION ALL SELECT 1801 UNION ALL SELECT 1900 UNION ALL SELECT 1901 UNION ALL SELECT 2000 UNION ALL SELECT 2001 UNION ALL SELECT 2100 UNION ALL SELECT 2101 UNION ALL SELECT 2200) AND (`order_id`, `user_id`, `status`, `merchant_id`) IN (SELECT 1000, 10, 'init', 1 UNION ALL SELECT 1001, 10, 'init', 2 UNION ALL SELECT 1100, 11, 'init', 5 UNION ALL SELECT 1101, 11, 'init', 6 UNION ALL SELECT 1200, 12, 'init', 9 UNION ALL SELECT 1201, 12, 'init', 10 UNION ALL SELECT 1300, 13, 'init', 13 UNION ALL SELECT 1301, 13, 'init', 14 UNION ALL SELECT 1400, 14, 'init', 17 UNION ALL SELECT 1401, 14, 'init', 18 UNION ALL SELECT 1500, 15, 'init', 1 UNION ALL SELECT 1501, 15, 'init', 2 UNION ALL SELECT 1600, 15, 'init', 5 UNION ALL SELECT 1601, 15, 'init', 6 UNION ALL SELECT 1700, 17, 'init', 9 UNION ALL SELECT 1701, 17, 'init', 10 UNION ALL SELECT 1800, 18, 'init', 13 UNION ALL SELECT 1801, 18, 'init', 14 UNION ALL SELECT 1900, 19, 'init', 17 UNION ALL SELECT 1901, 19, 'init', 18 UNION ALL SELECT 2000, 20, 'init', 3 UNION ALL SELECT 2001, 20, 'init', 4 UNION ALL SELECT 2100, 21, 'init', 7 UNION ALL SELECT 2101, 21, 'init', 8)]) |
|     DBPlusEngineScan(sql=[SELECT * FROM `sharding_db`.`t_order` WHERE `creation_date` = '2017-08-08' AND `order_id` IN (SELECT 1000 UNION ALL SELECT 1001 UNION ALL SELECT 1100 UNION ALL SELECT 1101 UNION ALL SELECT 1200 UNION ALL SELECT 1201 UNION ALL SELECT 1300 UNION ALL SELECT 1301 UNION ALL SELECT 1400 UNION ALL SELECT 1401 UNION ALL SELECT 1500 UNION ALL SELECT 1501 UNION ALL SELECT 1600 UNION ALL SELECT 1601 UNION ALL SELECT 1700 UNION ALL SELECT 1701 UNION ALL SELECT 1800 UNION ALL SELECT 1801 UNION ALL SELECT 1900 UNION ALL SELECT 1901 UNION ALL SELECT 2000 UNION ALL SELECT 2001 UNION ALL SELECT 2100 UNION ALL SELECT 2101 UNION ALL SELECT 2200) AND (`order_id`, `user_id`, `status`, `merchant_id`) IN (SELECT 1000, 10, 'init', 1 UNION ALL SELECT 1001, 10, 'init', 2 UNION ALL SELECT 1100, 11, 'init', 5 UNION ALL SELECT 1101, 11, 'init', 6 UNION ALL SELECT 1200, 12, 'init', 9 UNION ALL SELECT 1201, 12, 'init', 10 UNION ALL SELECT 1300, 13, 'init', 13 UNION ALL SELECT 1301, 13, 'init', 14 UNION ALL SELECT 1400, 14, 'init', 17 UNION ALL SELECT 1401, 14, 'init', 18 UNION ALL SELECT 1500, 15, 'init', 1 UNION ALL SELECT 1501, 15, 'init', 2 UNION ALL SELECT 1600, 15, 'init', 5 UNION ALL SELECT 1601, 15, 'init', 6 UNION ALL SELECT 1700, 17, 'init', 9 UNION ALL SELECT 1701, 17, 'init', 10 UNION ALL SELECT 1800, 18, 'init', 13 UNION ALL SELECT 1801, 18, 'init', 14 UNION ALL SELECT 1900, 19, 'init', 17 UNION ALL SELECT 1901, 19, 'init', 18 UNION ALL SELECT 2000, 20, 'init', 3 UNION ALL SELECT 2001, 20, 'init', 4 UNION ALL SELECT 2100, 21, 'init', 7 UNION ALL SELECT 2101, 21, 'init', 8)]) |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
4 rows in set (3.87 sec)

为了将 LogicalValues 转换为下推 SQL 中 MySQL 原生的 IN 语法，需要增加 DBPlusEngineRelToSQLConverter，并重写 visit(final Values values) 方法，当判断是 IN 常量子查询时，采用 ROW 语法生成 SqlNode。

调整完成后，再次执行 EXPLAIN 观察执行计划，可以看到在 IN 过滤条件下推 SQL 中，使用了 MySQL 原生支持的 (order_id, user_id, status, merchant_id) IN 语法，通过这种方式执行，SQL 可以快速查询出结果。

1
2
3
4
5
6
7
8
9
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| PLAN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| DBPlusEngineLimitSort(sort0=[$0], dir0=[ASC-nulls-first], offset=[0], fetch=[10])                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            |
|   DBPlusEngineUnion(all=[true])                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              |
|     DBPlusEngineScan(sql=[SELECT * FROM `sharding_db`.`t_order` WHERE `creation_date` = '2017-08-08' AND `order_id` IN ((1000), (1001), (1100), (1101), (1200), (1201), (1300), (1301), (1400), (1401), (1500), (1501), (1600), (1601), (1700), (1701), (1800), (1801), (1900), (1901), (2000), (2001), (2100), (2101), (2200)) AND (`order_id`, `user_id`, `status`, `merchant_id`) IN ((1000, 10, 'init', 1), (1001, 10, 'init', 2), (1100, 11, 'init', 5), (1101, 11, 'init', 6), (1200, 12, 'init', 9), (1201, 12, 'init', 10), (1300, 13, 'init', 13), (1301, 13, 'init', 14), (1400, 14, 'init', 17), (1401, 14, 'init', 18), (1500, 15, 'init', 1), (1501, 15, 'init', 2), (1600, 15, 'init', 5), (1601, 15, 'init', 6), (1700, 17, 'init', 9), (1701, 17, 'init', 10), (1800, 18, 'init', 13), (1801, 18, 'init', 14), (1900, 19, 'init', 17), (1901, 19, 'init', 18), (2000, 20, 'init', 3), (2001, 20, 'init', 4), (2100, 21, 'init', 7), (2101, 21, 'init', 8))]) |
|     DBPlusEngineScan(sql=[SELECT * FROM `sharding_db`.`t_order` WHERE `creation_date` = '2017-08-08' AND `order_id` IN ((1000), (1001), (1100), (1101), (1200), (1201), (1300), (1301), (1400), (1401), (1500), (1501), (1600), (1601), (1700), (1701), (1800), (1801), (1900), (1901), (2000), (2001), (2100), (2101), (2200)) AND (`order_id`, `user_id`, `status`, `merchant_id`) IN ((1000, 10, 'init', 1), (1001, 10, 'init', 2), (1100, 11, 'init', 5), (1101, 11, 'init', 6), (1200, 12, 'init', 9), (1201, 12, 'init', 10), (1300, 13, 'init', 13), (1301, 13, 'init', 14), (1400, 14, 'init', 17), (1401, 14, 'init', 18), (1500, 15, 'init', 1), (1501, 15, 'init', 2), (1600, 15, 'init', 5), (1601, 15, 'init', 6), (1700, 17, 'init', 9), (1701, 17, 'init', 10), (1800, 18, 'init', 13), (1801, 18, 'init', 14), (1900, 19, 'init', 17), (1901, 19, 'init', 18), (2000, 20, 'init', 3), (2001, 20, 'init', 4), (2100, 21, 'init', 7), (2101, 21, 'init', 8))]) |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
4 rows in set (0.80 sec)

测试 SQL 执行计划符合预期后，我们再用复杂的业务 SQL 对比测试，观察下优化前后的查询性能。

在优化之前，由于下推 SQL 过滤条件较少，部分 IN 常量集合子查询被转换为 JOIN 处理，导致 Scan 查询的数据量非常大，联邦查询引擎为了避免占用过多内存，会使用多次磁盘交换来完成 SQL 执行。本地 Mac 测试这条 SQL 需要 28s，用户环境受限于磁盘的读写性能，执行时间甚至超过了 20m。

经过本文优化之后，所有原始 SQL 中的 IN 过滤条件都能下推到 Scan 中，因此无需再进行磁盘交换，联邦查询引擎通过流式方式获取 Scan 中的数据，仅需要再对多个 UNION ALL 的查询进行数据合并，查询性能得到了极大的提升，本地 Mac 执行仅需 0.295s，相比原来的 28s，性能提升了 100 倍。

结语

本文介绍了复杂 SQL 包含 IN 常量子查询性能优化的过程，通过重写 SubQueryRemoveRule 和 DBPlusEngineRelToSQLConverter 逻辑，我们在联邦查询引擎中支持了 IN 常量子查询下推，最终将原始 SQL 的性能提升了 100 倍。

在优化的过程中，笔者也发现了很多联邦查询引擎和底层 Calcite 框架的不足，对于一些复杂的业务场景，还需要进行扩展和增强，才能完美地适配业务需求。后续规划中，联邦查询引擎将继续加强测试，尤其是结合业务场景和数据进行测试，尽可能多地发现问题，并进行深度优化，保证业务 SQL 准确、高性能地查询出结果。

本文整理的优化方案，对于其他使用 Calcite 框架的项目同样有借鉴意义，大家如有需要，可以参考进行优化。由于笔者水平经验有限，如果文章中有错误或者不足之处，欢迎大家留言指正。

写在最后

笔者因为工作原因接触到 Calcite，前期学习过程中，深感 Calcite 学习资料之匮乏，因此创建了 Calcite 从入门到精通知识星球，希望能够将学习过程中的资料和经验沉淀下来，为更多想要学习 Calcite 的朋友提供一些帮助。

Calcite 从入门到精通

欢迎关注

欢迎关注「端小强的博客」微信公众号，会不定期分享日常学习和工作经验，欢迎大家关注交流。

微信公众号

参考资料
许可协议

本文采用 署名-非商业性使用-禁止演绎 4.0 国际 许可协议，未经授权请勿转载。

分享文章

快来参与讨论吧~