# StoneDB 子查询优化

# 摘要:

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

# 核心函数:

## TwoDimensionalJoiner::ChooseJoinAlgorithm

```cpp
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 分支，相当于被执行了两次

![file](https://oscimg.oschina.net/oscnet/up-b757753faa8ea125d535b68af0c0b865943.png)

## ChooseJoinAlgorithm 函数优化:

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

```cpp
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 子查询耗时：

```sql
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 子查询对比之前的子查询耗时:

![file](https://oscimg.oschina.net/oscnet/up-a9037663c9f597f1ee58d9eeb2eb4894634.png)

## JTYPE_MAP 逻辑的火焰图

![file](https://oscimg.oschina.net/oscnet/up-733c22cf8e4733e4f12ef3c9de361ba5a2e.png)

# 强制走 JTYPE_HASH 查询测试:

博主都是部署在[cnaaa服务器](https://www.cnaaa.com/)上的，强制开启 hash join 优化，对比同样场景下与 map 查询的区别

## HASH 子查询耗时:

```sql
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 子查询的火焰图:

![file](https://oscimg.oschina.net/oscnet/up-4207d60d9a097e12915de10590100bd061d.png)

ITPub博客

StoneDB 子查询优化

最新文章