Como ajustar manualmente a ordem de junção usando hints - AnalyticDB: Central de Documentação do Alibaba Cloud

Este tópico descreve como ajustar manualmente a ordem de junção usando hints.

Introdução ao recurso

AnalyticDB for MySQL oferece suporte a consultas complexas com junções e ajusta automaticamente a ordem de junção por padrão. No entanto, instruções de consulta e condições de filtro das tabelas podem mudar. Se as características dos dados forem complexas, o ajuste automático poderá não selecionar a ordem de junção ideal. Uma ordem subótima gera grandes conjuntos de resultados intermediários e alto consumo de memória, o que prejudica o desempenho da consulta.

Para resolver esses problemas, o AnalyticDB for MySQL permite usar o hint /*+ reorder_joins*/ para ativar ou desativar o ajuste da ordem de junção:

/*+ reorder_joins=true*/: Ativa o ajuste automático da ordem de junção. O AnalyticDB for MySQL ativa esse recurso por padrão; portanto, não é necessário especificar esse hint ao executar consultas SQL.
/*+ reorder_joins=false*/: Desativa o ajuste automático da ordem de junção. Essa opção permite ajustar manualmente a ordem com base nas características dos dados da consulta. A execução segue a ordem de junção definida na instrução SQL.

Nota

O hint /*+ reorder_joins*/ opera no nível de sessão e afeta apenas a instrução SQL em que foi especificado.

Método de ajuste

Antes do ajuste
- Instrução de consulta
  A instrução Query10 original é:
  Nota
  
  Este tópico usa a Query10 do TPC-H como exemplo para demonstrar o método e o efeito do ajuste manual da ordem de junção. Para obter mais informações sobre o TPC-H, consulte TPC-H.
  
  O ajuste automático da ordem de junção do AnalyticDB for MySQL vem ativado por padrão. O hint /*+ reorder_joins=false*/ aparece na instrução de consulta abaixo para simular um cenário com ordem de junção subótima.
```
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;
```
- Ordem de junção
  Com base na instrução SQL anterior, a ordem de junção é:
```
customer JOIN orders JOIN lineitem JOIN nation;
```
- Resultados da consulta
  Os resultados intermediários de cada junção no plano de execução são:
  
  Nota
  Para obter mais informações sobre como visualizar um plano de execução, consulte Analisar uma consulta usando um plano de execução.
  1. A tabela customer se junta à tabela orders, produzindo 57.069 linhas no conjunto de resultados intermediários tmp1.
  2. O conjunto de resultados intermediários tmp1 se junta à tabela lineitem via INNER (Hash) Join sob a condição o_orderkey = l_orderkey. Essa operação leva 4,88 ms (21,67%) e gera 114.705 linhas no novo conjunto de resultados intermediários tmp2.
  3. O conjunto de resultados intermediários tmp2 se junta à tabela nation, gerando 114.705 linhas como resultado final. O nó de junção é do tipo INNER (Hash) e a condição é left.c_nationkey = right.n_nationkey. A operação leva 5,71 ms (12,07%).
  O total acumulado de linhas geradas pelas três junções é: 57.069 + 114.705 + 114.705 = 286.479.
Após o ajuste
- Instrução de consulta
  Adicione o hint /*+ reorder_joins=false*/ à instrução SQL para desativar o ajuste automático da ordem de junção do AnalyticDB for MySQL e ajustar a ordem manualmente. A instrução SQL com a ordem ajustada fica assim:
```
/*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;
```
- Ordem de junção
  Com base na instrução SQL anterior, a ordem de junção é:
```
customer JOIN orders JOIN nation JOIN lineitem
```
- Resultados da consulta
  Os resultados intermediários de cada junção no plano de execução são:
  
  Nota
  Para obter mais informações sobre como visualizar um plano de execução, consulte Analisar uma consulta usando um plano de execução.
  1. A tabela customer se junta à tabela orders, produzindo 57.069 linhas no conjunto de resultados intermediários tmp1. O nó de junção é do tipo INNER (Hash) e a condição é c_custkey = o_custkey. A operação leva 4,75 ms (36,7%).
  2. O conjunto de resultados intermediários tmp1 se junta à tabela nation, gerando 57.069 linhas no conjunto de resultados intermediários tmp2. O nó de junção é do tipo INNER (Hash) e a condição é left.c_nationkey = right.n_nationkey. A operação leva 5,63 ms (25,61%).
  3. O conjunto de resultados intermediários tmp2 se junta à tabela lineitem via INNER (Hash) Join sob a condição left.o_orderkey = right.l_orderkey. A junção produz 114.705 linhas como resultado final e leva 5,29 ms (9,81% do tempo total).
  O total acumulado de linhas geradas pelas três junções é: 57.069 + 57.069 + 114.705 = 228.843.
  
  Isso representa uma redução de 20% no número de linhas de saída em comparação com as 286.479 linhas obtidas antes do ajuste. Tal comparação mostra que diferentes ordens de junção afetam o tamanho dos conjuntos de resultados intermediários. Caso a ordem de junção no AnalyticDB for MySQL não seja ideal, ajuste-a manualmente para melhorar o desempenho da consulta.