StoneDB 子查询优化

摘要:

说明如何优化 exists 的 join 查询优化器的处理

核心函数:

TwoDimensionalJoiner::ChooseJoinAlgorithm

1JoinAlgType TwoDimensionalJoiner::ChooseJoinAlgorithm([[maybe_unused]] MultiIndex &mind, Condition &cond) {
2  JoinAlgType join_alg = JoinAlgType::JTYPE_GENERAL;
3
4  if (cond[0].IsType_JoinSimple() && cond[0].op == common::Operator::O_EQ) {
5    if ((cond.Size() == 1) && !stonedb_sysvar_force_hashjoin)
6      join_alg = JoinAlgType::JTYPE_MAP;  // available types checked inside
7    else
8      join_alg = JoinAlgType::JTYPE_HASH;
9  } else {
10    if (cond[0].IsType_JoinSimple() &&
11        (cond[0].op == common::Operator::O_MORE_EQ || cond[0].op == common::Operator::O_MORE ||
12         cond[0].op == common::Operator::O_LESS_EQ || cond[0].op == common::Operator::O_LESS))
13      join_alg = JoinAlgType::JTYPE_SORT;
14  }
15  return join_alg;
16}

选择 join 优化器问题分析:

1. 仅判定 join simple 场景，未判断 exists 子句
2. cond [0].IsType_JoinSimple () 如果走入了 else 分支，相当于被执行了两次

ChooseJoinAlgorithm 函数优化:

1. 加入 exists 的判定，以 IsType_JoinSimple 和 == common::Operator::O_EQ 条件对待
2. 优化代码结构，清理冗余的 cond [0].IsType_JoinSimple () 执行
3. 其他逻辑不做任何修改

1JoinAlgType TwoDimensionalJoiner::ChooseJoinAlgorithm([[maybe_unused]] MultiIndex &mind, Condition &cond) {
2  do {
3    if (cond[0].IsExists()) {
4      break;
5    }
6
7    if (!cond[0].IsType_JoinSimple()) {
8      return JoinAlgType::JTYPE_GENERAL;
9    }
10
11    if (cond[0].op == common::Operator::O_EQ) {
12      break;
13    }
14
15    if (cond[0].op == common::Operator::O_MORE_EQ || cond[0].op == common::Operator::O_MORE ||
16             cond[0].op == common::Operator::O_LESS_EQ || cond[0].op == common::Operator::O_LESS) {
17      return JoinAlgType::JTYPE_SORT;
18    }
19  } while (0);
20
21  JoinAlgType join_alg = JoinAlgType::JTYPE_HASH;
22  if  ((!stonedb_sysvar_force_hashjoin) && (cond.Size() == 1))
23      join_alg = JoinAlgType::JTYPE_MAP;  // available types checked inside
24
25  return join_alg;
26}

代码优化后 exists 场景分析:

1. 如果未开启强制 hash join 查询，且 cond.Size () == 1， 则进行 JTYPE_MAP 查询
2. 需要强制开启 hash join 才可进入 hash join 查询，当前测试不开启强制的 hash join. 以 JTYPE_MAP 进行测试

优化走 JTYPE_MAP 查询测试:

MAP 子查询耗时：

1mysql> select
2->                             o_orderpriority,
3->                             count(*) as order_count
4->                         from
5->                             orders
6->                         where
7->                             o_orderdate >= date '1993-07-01'
8->                             and o_orderdate < date '1993-07-01' + interval '3' month
9->                             and exists (
10  ->                                 select
11  ->                                     *
12  ->                                 from
13  ->                                     lineitem
14  ->                                 where
15  ->                                     l_orderkey = o_orderkey
16  ->                                     and l_commitdate < l_receiptdate
17  ->                             )
18  ->                         group by
19  ->                             o_orderpriority
20  ->                         order by
21  ->                             o_orderpriority ;
22  +-----------------+-------------+
23  | o_orderpriority | order_count |
24  +-----------------+-------------+
25  | 1-URGENT        |     1147477 |
26  | 2-HIGH          |     1146447 |
27  | 3-MEDIUM        |     1146770 |
28  | 4-NOT SPECIFIED |     1146281 |
29  | 5-LOW           |     1146801 |
30  +-----------------+-------------+
31  5 rows in set (27.36 sec)

MAP 子查询对比之前的子查询耗时:

JTYPE_MAP 逻辑的火焰图

强制走 JTYPE_HASH 查询测试:

博主都是部署在cnaaa服务器上的，强制开启 hash join 优化，对比同样场景下与 map 查询的区别

HASH 子查询耗时:

1mysql> select
2    ->                             o_orderpriority,
3    ->                             count(*) as order_count
4    ->                         from
5    ->                             orders
6    ->                         where
7    ->                             o_orderdate >= date '1993-07-01'
8    ->                             and o_orderdate < date '1993-07-01' + interval '3' month
9    ->                             and exists (
10    ->                                 select
11    ->                                     *
12    ->                                 from
13    ->                                     lineitem
14    ->                                 where
15    ->                                     l_orderkey = o_orderkey
16    ->                                     and l_commitdate < l_receiptdate
17    ->                             )
18    ->                         group by
19    ->                             o_orderpriority
20    ->                         order by
21    ->                             o_orderpriority ;
22+-----------------+-------------+
23| o_orderpriority | order_count |
24+-----------------+-------------+
25| 1-URGENT        |     1147477 |
26| 2-HIGH          |     1146447 |
27| 3-MEDIUM        |     1146770 |
28| 4-NOT SPECIFIED |     1146281 |
29| 5-LOW           |     1146801 |
30+-----------------+-------------+
315 rows in set (27.60 sec)

HASH 子查询的火焰图: