当前位置：网站首页>【StoneDB子查询优化】subquery子查询-优化exists场景的join查询优化器的处理-100GB数据量测试

【StoneDB子查询优化】subquery子查询-优化exists场景的join查询优化器的处理-100GB数据量测试

2022-08-05 19:52:00 【51CTO】

摘要:

说明如何优化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;
}
     1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.

选择join优化器问题分析:

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

【StoneDB子查询优化】subquery子查询-优化exists场景的join查询优化器的处理-100GB数据量测试_数据库

ChooseJoinAlgorithm函数优化:

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

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;
}
     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.

代码优化后exists场景分析:

如果未开启强制hash join查询, 且cond.Size() == 1，则进行JTYPE_MAP查询
需要强制开启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)

     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.
30.
31.

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

【StoneDB子查询优化】subquery子查询-优化exists场景的join查询优化器的处理-100GB数据量测试_数据库_02

JTYPE_MAP逻辑的火焰图

【StoneDB子查询优化】subquery子查询-优化exists场景的join查询优化器的处理-100GB数据量测试_数据库_03

强制走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)

     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.
30.
31.