StoneDB 子查询优化
StoneDB 子查询优化
摘要:
说明如何优化 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 优化器问题分析:
- 仅判定 join simple 场景,未判断 exists 子句
- cond [0].IsType_JoinSimple () 如果走入了 else 分支,相当于被执行了两次
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; }
代码优化后 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)
MAP 子查询对比之前的子查询耗时:
JTYPE_MAP 逻辑的火焰图
强制走 JTYPE_HASH 查询测试:
博主都是部署在cnaaa服务器上的,强制开启 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)