手动调整Join顺序

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

功能介绍

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

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

  • /*+ reorder_joins=true*/:开启自动调整Join顺序功能。开启后,系统会自动调整Join顺序。AnalyticDB 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 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行数据至临时结果集tmp11

      2. 临时结果集tmp1Join表lineitem,输出114705行数据至另一个临时结果集tmp22

      3. 临时结果集tmp2Join表nation,输出114705行数据作为最终结果。3

      3次Join累积输出结果行数为:57069 + 114705 + 114705 = 286479。

  • 调整后

    • 查询语句

      在SQL语句中添加/*+ reorder_joins=false*/的Hint来关闭AnalyticDB 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行数据至临时结果集tmp11

      2. 临时结果集tmp1Join表nation,输出57069行数据至临时结果集tmp22

      3. 临时结果集tmp2Join表lineitem,输出114705行数据作为最终结果。3

      3次Join累积输出结果行数为:57069 + 57069 + 114705 = 228843。

      相较于Join顺序调整前(输出286479行数据),减少了20%的输出行数。从上述对比结果中可以看出,不同的Join顺序会影响中间临时结果集的大小,因此若发现AnalyticDB MySQL版的Join顺序不优,您可以手动调整Join顺序来提升SQL查询性能。