# 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)

StoneDB 子查詢最佳化

相關文章