关于数据库:StoneDB子查询优化subquery子查询优化exists场景的join查询优化器的处理100GB数据量测试

摘要:

阐明如何优化 exists 的 join 查问优化器的解决

外围函数:

TwoDimensionalJoiner::ChooseJoinAlgorithm

JoinAlgType TwoDimensionalJoiner::ChooseJoinAlgorithm([[maybe_unused]] MultiIndex &mind, Condition &cond) {
  JoinAlgType join_alg = JoinAlgType::JTYPE_GENERAL;

  if (cond[0].IsType_JoinSimple() && cond[0].op == common::Operator::O_EQ) {if ((cond.Size() == 1) && !stonedb_sysvar_force_hashjoin)
      join_alg = JoinAlgType::JTYPE_MAP;  // available types checked inside
    else
      join_alg = JoinAlgType::JTYPE_HASH;
  } else {if (cond[0].IsType_JoinSimple() &&
        (cond[0].op == common::Operator::O_MORE_EQ || cond[0].op == common::Operator::O_MORE ||
         cond[0].op == common::Operator::O_LESS_EQ || cond[0].op == common::Operator::O_LESS))
      join_alg = JoinAlgType::JTYPE_SORT;
  }
  return join_alg;
}

抉择 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. 其余逻辑不做任何批改

JoinAlgType TwoDimensionalJoiner::ChooseJoinAlgorithm([[maybe_unused]] MultiIndex &mind, Condition &cond) {
  do {if (cond[0].IsExists()) {break;}

    if (!cond[0].IsType_JoinSimple()) {return JoinAlgType::JTYPE_GENERAL;}

    if (cond[0].op == common::Operator::O_EQ) {break;}

    if (cond[0].op == common::Operator::O_MORE_EQ || cond[0].op == common::Operator::O_MORE ||
             cond[0].op == common::Operator::O_LESS_EQ || cond[0].op == common::Operator::O_LESS) {return JoinAlgType::JTYPE_SORT;}
  } while (0);

  JoinAlgType join_alg = JoinAlgType::JTYPE_HASH;
  if  ((!stonedb_sysvar_force_hashjoin) && (cond.Size() == 1))
      join_alg = JoinAlgType::JTYPE_MAP;  // available types checked inside

  return join_alg;
}

代码优化后 exists 场景剖析:

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

优化走 JTYPE_MAP 查问测试:

MAP 子查问耗时：

mysql> select
->                             o_orderpriority,
->                             count(*) as order_count
->                         from
->                             orders
->                         where
->                             o_orderdate >= date '1993-07-01'
->                             and o_orderdate < date '1993-07-01' + interval '3' month
->                             and exists (
  ->                                 select
  ->                                     *
  ->                                 from
  ->                                     lineitem
  ->                                 where
  ->                                     l_orderkey = o_orderkey
  ->                                     and l_commitdate < l_receiptdate
  ->                             )
  ->                         group by
  ->                             o_orderpriority
  ->                         order by
  ->                             o_orderpriority ;
  +-----------------+-------------+
  | o_orderpriority | order_count |
  +-----------------+-------------+
  | 1-URGENT        |     1147477 |
  | 2-HIGH          |     1146447 |
  | 3-MEDIUM        |     1146770 |
  | 4-NOT SPECIFIED |     1146281 |
  | 5-LOW           |     1146801 |
  +-----------------+-------------+
  5 rows in set (27.36 sec)

MAP 子查问比照之前的子查问耗时:

JTYPE_MAP 逻辑的火焰图

强制走 JTYPE_HASH 查问测试:

强制开启 hash join 优化, 比照同样场景下与 map 查问的区别

HASH 子查问耗时:

mysql> select
    ->                             o_orderpriority,
    ->                             count(*) as order_count
    ->                         from
    ->                             orders
    ->                         where
    ->                             o_orderdate >= date '1993-07-01'
    ->                             and o_orderdate < date '1993-07-01' + interval '3' month
    ->                             and exists (
    ->                                 select
    ->                                     *
    ->                                 from
    ->                                     lineitem
    ->                                 where
    ->                                     l_orderkey = o_orderkey
    ->                                     and l_commitdate < l_receiptdate
    ->                             )
    ->                         group by
    ->                             o_orderpriority
    ->                         order by
    ->                             o_orderpriority ;
+-----------------+-------------+
| o_orderpriority | order_count |
+-----------------+-------------+
| 1-URGENT        |     1147477 |
| 2-HIGH          |     1146447 |
| 3-MEDIUM        |     1146770 |
| 4-NOT SPECIFIED |     1146281 |
| 5-LOW           |     1146801 |
+-----------------+-------------+
5 rows in set (27.60 sec)

HASH 子查问的火焰图: