O LEFT JOIN é um método comum de junção de tabelas. Durante um hash join, a tabela da direita constrói a tabela hash, e a ordem das tabelas à esquerda e à direita em um LEFT JOIN não pode ser alterada. Se a tabela da direita for grande, a execução da consulta torna-se lenta e consome muita memória. Este tópico apresenta exemplos específicos de cenários em que é possível alterar um LEFT JOIN para um RIGHT JOIN.
Informações básicas
Por padrão, o AnalyticDB for MySQL utiliza hash joins para unir tabelas. Nesse tipo de junção, a tabela da direita constrói a tabela hash, o que demanda muitos recursos. Diferentemente das junções internas, as junções externas (incluindo LEFT JOIN e RIGHT JOIN) não permitem trocar a ordem das tabelas da esquerda e da direita sem alterar a semântica da consulta. Por isso, quando a tabela da direita é grande, a execução da consulta fica lenta e consome uma quantidade significativa de memória. Em casos extremos, nos quais a tabela da direita é muito grande, o desempenho do cluster é afetado ou o erro Out of Memory Pool size pre cal é reportado durante a execução. Nessas situações, utilize os métodos de otimização descritos neste tópico para reduzir o consumo de recursos.
Cenários
É possível alterar um LEFT JOIN para um RIGHT JOIN modificando a instrução SQL ou adicionando uma dica. No LEFT JOIN original, a tabela da esquerda passa a ser a tabela da direita para construir a tabela hash. Se essa nova tabela da direita for muito grande, o desempenho ainda será impactado. Portanto, recomenda-se aplicar essa otimização quando a tabela à esquerda do LEFT JOIN for pequena e a tabela à direita for grande.
A classificação de uma tabela como pequena ou grande é relativa e depende de fatores como colunas de junção e recursos do cluster. Na prática, use EXPLAIN ANALYZE para visualizar os parâmetros do plano de execução. Para decidir se deve usar um RIGHT JOIN, observe as alterações em parâmetros como PeakMemory e WallTime.
Uso
Altere um LEFT JOIN para um RIGHT JOIN de duas formas:
Modifique diretamente a instrução SQL. Por exemplo, altere
a left join b on a.col1 = b.col2parab right join a on a.col1 = b.col2.-
Adicione uma dica para instruir o otimizador a converter um LEFT JOIN em um RIGHT JOIN com base no consumo de recursos. Assim, o otimizador decide se deve fazer a alteração conforme os tamanhos estimados das tabelas da esquerda e da direita. Utilize um dos métodos abaixo:
Em clusters V3.1.8 ou posteriores, esse recurso está ativado por padrão. Caso esteja desativado, adicione a seguinte dica no início da instrução SQL para ativá-lo manualmente:
/*+O_CBO_RULE_SWAP_OUTER_JOIN=true*/Em clusters com versão anterior à V3.1.8, esse recurso vem desativado por padrão. Adicione a seguinte dica no início da instrução SQL para ativá-lo:
/*+LEFT_TO_RIGHT_ENABLED=true*/
Para verificar a versão secundária de um cluster Data Lakehouse Edition, execute SELECT adb_version();. Para atualizar a versão secundária, consulte Atualize a versão secundária de um cluster.
Exemplos
No exemplo a seguir, nation é uma tabela pequena com 25 linhas e customer é uma tabela grande com 15.000.000 de linhas. Use EXPLAIN ANALYZE para visualizar o plano de execução de uma consulta que contém um LEFT JOIN.
explain analyze
SELECT
COUNT(*)
FROM
nation t1
left JOIN customer t2 ON t1.n_nationkey = t2.c_nationkeyO plano abaixo mostra o estágio 2, responsável pelo cálculo da junção. O operador Left Join contém as seguintes informações:
PeakMemory: 515MB (93.68%), WallTime: 4.34s (43.05%): o valor de PeakMemory representa até 93,68% do total, indicando que o LEFT JOIN é o gargalo de desempenho da consulta.Left (probe) Input avg.: 0.52 rows; Right (build) Input avg.: 312500.00 rows: a tabela da direita é a tabela grande e a tabela da esquerda é a tabela pequena.
Nesse cenário, altere o LEFT JOIN para um RIGHT JOIN a fim de otimizar a consulta.
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%-
Alteração do LEFT JOIN para RIGHT JOIN mediante modificação da instrução SQL:
SELECT COUNT(*) FROM customer t2 right JOIN nation t1 ON t1.n_nationkey = t2.c_nationkey -
Alteração do LEFT JOIN para RIGHT JOIN mediante adição de uma dica:
-
Para clusters V3.1.8 ou posteriores, execute a seguinte instrução para ativar esse recurso:
/*+O_CBO_RULE_SWAP_OUTER_JOIN=true*/ SELECT COUNT(*) FROM nation t1 left JOIN customer t2 ON t1.n_nationkey = t2.c_nationkey -
Para clusters com versão anterior à V3.1.8, execute a seguinte instrução para ativar esse recurso:
/*+LEFT_TO_RIGHT_ENABLED=true*/ SELECT COUNT(*) FROM nation t1 left JOIN customer t2 ON t1.n_nationkey = t2.c_nationkey
-
Ao executar EXPLAIN ANALYZE em qualquer uma das consultas anteriores, o plano de execução exibirá um RIGHT Join em vez de um LEFT Join, confirmando que a dica teve efeito. Após o ajuste, o valor de PeakMemory passa a ser 889 KB (3,31%), caindo de 515 MB para 889 KB e deixando de ser um ponto crítico de desempenho.
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%