如何通过Hint实现手动调整Join顺序-云原生数据仓库AnalyticDB(AnalyticDB)-阿里云帮助中心

本文介绍如何通过Hint实现手动调整Join顺序。

功能介绍

AnalyticDB for MySQL支持复杂的联表（Join）查询且默认提供了自动调整Join顺序的功能。但查询语句和表的过滤条件随时可能发生变化，而且如果数据特征复杂，自动调整Join顺序功能不一定在所有场景下都能很好地预估查询特征，并选择出最优的Join顺序。而不优的Join顺序可能造成中间结果集膨胀中间结果集数据量较大、内存消耗大等问题，进而影响查询性能。

为解决上述问题，AnalyticDB for MySQL支持通过Hint/*+ reorder_joins*/来开启或关闭调整Join顺序功能，其中：

/*+ reorder_joins=true*/：开启自动调整Join顺序功能。开启后，系统会自动调整Join顺序。AnalyticDB for MySQL默认开启调整Join顺序功能，因此在执行SQL查询时无需使用该Hint也会自动调整Join顺序。
/*+ reorder_joins=false*/：关闭自动调整Join顺序功能。关闭后，您可以根据查询的数据特征手动调整Join顺序，让查询直接根据SQL书写方式中的Join顺序来执行。

说明

/*+ reorder_joins*/为会话级别的Hint，仅对目标SQL查询语句生效。

调整方法

调整前
- 查询语句
  原始的Query10语句如下：
  说明
  
  本文以TPC-H中的Query10为例介绍手动调整Join顺序的方法及查询效果。更多关于TPC-H的详细说明，请参见TPC-H。
  
  由于AnalyticDB for MySQL的自动调整Join顺序功能默认开启，在执行如下查询语句时使用了Hint/*+ reorder_joins=false*/来模拟Join顺序不优的场景。
```
SELECT   c_custkey,
         c_name,
         Sum(l_extendedprice * (1 - l_discount)) AS revenue,
         c_acctbal,
         n_name,
         c_address,
         c_phone,
         c_comment
FROM     customer c,
         orders o,
         lineitem l,
         nation n
WHERE    c_custkey = o_custkey
AND      l_orderkey = o_orderkey
AND      o_orderdate >= date '1993-10-01'
AND      o_orderdate <  date '1993-10-01' + INTERVAL '3' month
AND      l_returnflag = 'R'
AND      c_nationkey = n_nationkey
GROUP BY c_custkey,
         c_name,
         c_acctbal,
         c_phone,
         n_name,
         c_address,
         c_comment
ORDER BY revenue DESC
LIMIT    20;
```
- Join顺序
  如果按照如上SQL的书写方式，Join的顺序应该是：
```
customer JOIN orders JOIN lineitem JOIN nation;
```
- 查询结果
  此时，执行计划中各个JOIN的临时结果如下：
  
  说明
  查看执行计划的步骤，请参见使用执行计划分析查询。
  1. 表customerJoin表orders，输出57069行数据至临时结果集tmp1。
  2. 临时结果集tmp1Join表lineitem，输出114705行数据至另一个临时结果集tmp2。临时结果集 tmp1 与表 lineitem 通过 INNER(Hash) Join 连接，连接条件为 o_orderkey = l_orderkey，耗时 4.88 ms（占比 21.67%），输出 114705 行数据至临时结果集 tmp2。
  3. 临时结果集tmp2Join表nation，输出114705行数据作为最终结果。该 Join 节点类型为 INNER(Hash)，连接条件为 left.c_nationkey = right.n_nationkey，耗时 5.71 ms（占比 12.07%）。
  3次Join累积输出结果行数为：57069 + 114705 + 114705 = 286479。
调整后
- 查询语句
  在SQL语句中添加/*+ reorder_joins=false*/的Hint来关闭AnalyticDB for MySQL的自动调整Join顺序功能，并手动调整Join的顺序，调整顺序后的SQL语句如下：
```
/*reorder_joins=false*/
SELECT   c_custkey,
         c_name,
         Sum(l_extendedprice * (1 - l_discount)) AS revenue,
         c_acctbal,
         n_name,
         c_address,
         c_phone,
         c_comment
FROM     customer c,
         orders o,
         nation n,
         lineitem l
WHERE    c_custkey = o_custkey
AND      c_nationkey = n_nationkey
AND      l_orderkey = o_orderkey
AND      o_orderdate >= date '1993-10-01'
AND      o_orderdate <  date '1993-10-01' + INTERVAL '3' month
AND      l_returnflag = 'R'
GROUP BY c_custkey,
         c_name,
         c_acctbal,
         c_phone,
         n_name,
         c_address,
         c_comment
ORDER BY revenue DESC
LIMIT    20;
```
- Join顺序
  如果按照如上SQL的书写方式，Join的顺序应该是：
```
customer JOIN orders JOIN nation JOIN lineitem
```
- 查询结果
  此时，执行计划中各个JOIN的临时结果如下：
  
  说明
  查看执行计划的步骤，请参见使用执行计划分析查询。
  1. 表customerJoin表orders，输出57069行数据至临时结果集tmp1。该 Join 节点类型为 INNER(Hash)，连接条件为 c_custkey = o_custkey，耗时 4.75 ms（占比 36.7%）。
  2. 临时结果集tmp1Join表nation，输出57069行数据至临时结果集tmp2。该 Join 节点采用 INNER(Hash) 连接方式，连接条件为 left.c_nationkey = right.n_nationkey，耗时 5.63 ms（占比 25.61%）。
  3. 临时结果集tmp2Join表lineitem，输出114705行数据作为最终结果。表 orders Join 表 lineitem，连接类型为 INNER(Hash)，连接条件为 left.o_orderkey = right.l_orderkey，输出 114,705 行数据，耗时 5.29 ms（占总耗时 9.81%）。
  3次Join累积输出结果行数为：57069 + 57069 + 114705 = 228843。
  
  相较于Join顺序调整前（输出286479行数据），减少了20%的输出行数。从上述对比结果中可以看出，不同的Join顺序会影响中间临时结果集的大小，因此若发现AnalyticDB for MySQL的Join顺序不优，您可以手动调整Join顺序来提升SQL查询性能。