Left join是實踐中常用的一種表關聯方式,由於Hash Join實現會以右表做Build,且left Join不會做左右表的重新排序,在右表資料量很大時會造成執行慢、消耗過多記憶體資源等多個問題。本文以具體樣本介紹哪些情境下可以用right join替代left join。
背景資訊
AnalyticDB MySQL版預設使用Hash Join進行表關聯。Hash Join在實現時會用右表構建雜湊表,該過程會消耗大量資源,由於outer join(包括left join,right join)不同於inner join,從語義上不能交換左右表順序,因此在右表資料量大的情境下,會出現執行慢、記憶體資源消耗大的情況,在極端情境下(右表資料量很大)還會影響叢集的效能,或執行時直接報錯Out of Memory Pool size pre cal。此時,可以使用本章節提供的最佳化方法來減少資源消耗。
使用情境
通過修改SQL語句或者加Hint的方式,可以將left join調整為right join,原left join中的左表會變為右表來構建雜湊表。這時如果右表過大也會對效能有影響,因此,建議在left join左表較小,右表較大的情境下進行最佳化。
較小、很大的概念是相對的,和關聯列、叢集資源等都有關係。在實踐中,我們可以通過Explain analyze查看執行計畫的相關參數,通過關注PeakMemory、WallTime等參數的變化來判斷是否應該使用right join。
使用方法
通常有以下兩種方法可以把left join調整為right join:
直接修改SQL,例如將
a left join b on a.col1 = b.col2
改為b right join a on a.col1 = b.col2
。通過加hint指定最佳化器根據資源損耗把left join轉為right join。這種用法中,最佳化器會根據左右表的估算大小來決定是否把left join轉為right join。使用方法如下:
3.1.8及以上核心版本的叢集預設開啟該特性。如關閉了該特性,可在SQL最前面加上hint:
/*+O_CBO_RULE_SWAP_OUTER_JOIN=true*/
手動開啟該特性。3.1.8以下核心版本的叢集預設關閉該特性。可在SQL最前面加上hint:
/*+LEFT_TO_RIGHT_ENABLED=true*/
開啟該特性。
如何查看叢集核心版本,請參見如何查看執行個體版本資訊。如需升級核心版本,請聯絡支援人員。
樣本
如下樣本中,nation是一個25行的小表,customer是一個15000000行的大表,通過explain analyze
查看一條包含left join的SQL的執行計畫。
explain analyze
SELECT
COUNT(*)
FROM
nation t1
left JOIN customer t2 ON t1.n_nationkey = t2.c_nationkey
可以看到,進行join計算的stage2的計劃如下。其中,Left Join這個運算元中包含如下資訊:
PeakMemory: 515MB (93.68%), WallTime: 4.34s (43.05%)
:PeakMemory的佔比高達93.68%,可以判斷left join為整個SQL的效能瓶頸。Left (probe) Input avg.: 0.52 rows;Right (build) Input avg.: 312500.00 rows
:即右表為大表,左表為小表。
這種情境下,我們可以將left join轉為right join,來最佳化這條SQL語句。
Fragment 2 [HASH]
Output: 48 rows (432B), PeakMemory: 516MB, WallTime: 6.52us, Input: 15000025 rows (200.27MB); per task: avg.: 2500004.17 std.dev.: 2410891.74
Output layout: [count_0_2]
Output partitioning: SINGLE []
Aggregate(PARTIAL)
│ Outputs: [count_0_2:bigint]
│ Estimates: {rows: ? (?)}
│ Output: 96 rows (864B), PeakMemory: 96B (0.00%), WallTime: 88.21ms (0.88%)
│ count_2 := count(*)
└─ LEFT Join[(`n_nationkey` = `c_nationkey`)][$hashvalue, $hashvalue_0_4]
│ Outputs: []
│ Estimates: {rows: 15000000 (0B)}
│ Output: 30000000 rows (200.27MB), PeakMemory: 515MB (93.68%), WallTime: 4.34s (43.05%)
│ Left (probe) Input avg.: 0.52 rows, Input std.dev.: 379.96%
│ Right (build) Input avg.: 312500.00 rows, Input std.dev.: 380.00%
│ Distribution: PARTITIONED
├─ RemoteSource[3]
│ Outputs: [n_nationkey:integer, $hashvalue:bigint]
│ Estimates:
│ Output: 25 rows (350B), PeakMemory: 64KB (0.01%), WallTime: 63.63us (0.00%)
│ Input avg.: 0.52 rows, Input std.dev.: 379.96%
└─ LocalExchange[HASH][$hashvalue_0_4] ("c_nationkey")
│ Outputs: [c_nationkey:integer, $hashvalue_0_4:bigint]
│ Estimates: {rows: 15000000 (57.22MB)}
│ Output: 30000000 rows (400.54MB), PeakMemory: 10MB (1.84%), WallTime: 1.81s (17.93%)
└─ RemoteSource[4]
Outputs: [c_nationkey:integer, $hashvalue_0_5:bigint]
Estimates:
Output: 15000000 rows (200.27MB), PeakMemory: 3MB (0.67%), WallTime: 191.32ms (1.90%)
Input avg.: 312500.00 rows, Input std.dev.: 380.00%
通過修改SQL的方式實現left join to right join:
SELECT COUNT(*) FROM customer t2 right JOIN nation t1 ON t1.n_nationkey = t2.c_nationkey
通過加Hint的方式實現left join to right join:
3.1.8及以上核心版本的叢集執行以下語句開啟該特性:
/*+O_CBO_RULE_SWAP_OUTER_JOIN=true*/ SELECT COUNT(*) FROM nation t1 left JOIN customer t2 ON t1.n_nationkey = t2.c_nationkey
3.1.8以下核心版本的叢集執行以下語句開啟該特性:
/*+LEFT_TO_RIGHT_ENABLED=true*/ SELECT COUNT(*) FROM nation t1 left JOIN customer t2 ON t1.n_nationkey = t2.c_nationkey
上述任意一種SQL,執行explain analyze
後可以看到,在執行計畫中,left Join變為了right Join,可以判斷Hint是生效的。並且調整後PeakMemory的值為889 KB (3.31%),從515 MB下降到889 KB,已經不是計算熱點。
Fragment 2 [HASH]
Output: 96 rows (864B), PeakMemory: 12MB, WallTime: 4.27us, Input: 15000025 rows (200.27MB); per task: avg.: 2500004.17 std.dev.: 2410891.74
Output layout: [count_0_2]
Output partitioning: SINGLE []
Aggregate(PARTIAL)
│ Outputs: [count_0_2:bigint]
│ Estimates: {rows: ? (?)}
│ Output: 192 rows (1.69kB), PeakMemory: 456B (0.00%), WallTime: 5.31ms (0.08%)
│ count_2 := count(*)
└─ RIGHT Join[(`c_nationkey` = `n_nationkey`)][$hashvalue, $hashvalue_0_4]
│ Outputs: []
│ Estimates: {rows: 15000000 (0B)}
│ Output: 15000025 rows (350B), PeakMemory: 889KB (3.31%), WallTime: 3.15s (48.66%)
│ Left (probe) Input avg.: 312500.00 rows, Input std.dev.: 380.00%
│ Right (build) Input avg.: 0.52 rows, Input std.dev.: 379.96%
│ Distribution: PARTITIONED
├─ RemoteSource[3]
│ Outputs: [c_nationkey:integer, $hashvalue:bigint]
│ Estimates:
│ Output: 15000000 rows (200.27MB), PeakMemory: 3MB (15.07%), WallTime: 634.81ms (9.81%)
│ Input avg.: 312500.00 rows, Input std.dev.: 380.00%
└─ LocalExchange[HASH][$hashvalue_0_4] ("n_nationkey")
│ Outputs: [n_nationkey:integer, $hashvalue_0_4:bigint]
│ Estimates: {rows: 25 (100B)}
│ Output: 50 rows (700B), PeakMemory: 461KB (1.71%), WallTime: 942.37us (0.01%)
└─ RemoteSource[4]
Outputs: [n_nationkey:integer, $hashvalue_0_5:bigint]
Estimates:
Output: 25 rows (350B), PeakMemory: 64KB (0.24%), WallTime: 76.34us (0.00%)
Input avg.: 0.52 rows, Input std.dev.: 379.96%