本文介绍如何通过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的临时结果如下:
说明查看执行计划的步骤,请参见使用执行计划分析查询。
表
customer
Join表orders
,输出57069行数据至临时结果集tmp1
。临时结果集
tmp1
Join表lineitem
,输出114705行数据至另一个临时结果集tmp2
。临时结果集
tmp2
Join表nation
,输出114705行数据作为最终结果。
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的临时结果如下:
说明查看执行计划的步骤,请参见使用执行计划分析查询。
表
customer
Join表orders
,输出57069行数据至临时结果集tmp1
。临时结果集
tmp1
Join表nation
,输出57069行数据至临时结果集tmp2
。临时结果集
tmp2
Join表lineitem
,输出114705行数据作为最终结果。
3次Join累积输出结果行数为:57069 + 57069 + 114705 = 228843。
相较于Join顺序调整前(输出286479行数据),减少了20%的输出行数。从上述对比结果中可以看出,不同的Join顺序会影响中间临时结果集的大小,因此若发现AnalyticDB MySQL版的Join顺序不优,您可以手动调整Join顺序来提升SQL查询性能。