mysql 原生left join_Left join优化改写为right join

示例

如下示例中，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：

/*+LEFT_TO_RIGHT_ENABLED=true,CASCADES_OPTIMIZER_ENABLED=false*/

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的值为889KB (3.31%)，从515MB下降到889KB，已经不是计算热点。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%