Como usar o recurso de subquery folding - PolarDB: Central de Documentação do Alibaba Cloud

Como funciona

Quando duas subconsultas acessam conjuntos sobrepostos, o otimizador pode eliminá-las ou combiná-las:

Remoção: o otimizador descarta totalmente uma subconsulta porque a outra já garante seu resultado.
Mesclagem: o otimizador combina as condições de duas subconsultas em uma única subconsulta.

O otimizador aplica regras de folding com base em duas propriedades do par de subconsultas: a relação de tipo (mesmo tipo ou mutuamente exclusivas) e a relação de inclusão entre seus conjuntos de resultados.

Conceitos principais

Tipos de subconsultas suportados

Tipo	Operador	Exemplo
EXISTS	`EXISTS`, `NOT EXISTS`	`WHERE EXISTS (SELECT 1 FROM t2)`
IN	`IN`, `NOT IN`	`WHERE a IN (SELECT a FROM t2)`
ANY	`= ANY`, `!= ANY`, `< ANY`, `<= ANY`, `> ANY`, `>= ANY`	`WHERE t.a > ANY (SELECT t2.a FROM t2)`
ALL	`= ALL`, `!= ALL`, `< ALL`, `<= ALL`, `> ALL`, `>= ALL`	`WHERE t.a > ANY (SELECT t2.a FROM t2)`

Subconsultas escalares de linha única (por exemplo, WHERE t.a < (SELECT MIN(t2.a) ...) ) não são suportadas.

Subconsultas do mesmo tipo e mutuamente exclusivas

Subconsultas do mesmo tipo compartilham o mesmo operador. Duas subconsultas EXISTS ou duas subconsultas > ANY são consideradas do mesmo tipo.
Subconsultas mutuamente exclusivas utilizam operadores opostos. A tabela a seguir apresenta todos os pares mutuamente exclusivos.

Subconsulta	Subconsulta mutuamente exclusiva
`EXISTS`	`NOT EXISTS`
`IN`	`NOT IN`
`= ANY`	`!= ALL`
`!= ANY`	`= ALL`
`< ANY`	`>= ALL` ou `> ALL`
`<= ANY`	`> ALL`
`> ANY`	`<= ALL` ou `< ALL`
`>= ANY`	`< ALL`

Essas equivalências refletem identidades lógicas subjacentes: IN equivale a = ANY, e NOT IN equivale a != ALL. Compreender essas relações facilita a internalização dos pares mutuamente exclusivos.

Relações de inclusão

O lado direito de uma subconsulta representa um conjunto. Dois conjuntos podem ter quatro tipos de relação:

Relação	Significado
Subconjunto à esquerda	O conjunto da esquerda é um subconjunto próprio do conjunto da direita
Subconjunto à direita	O conjunto da direita é um subconjunto próprio do conjunto da esquerda
Igualdade	Os dois conjuntos contêm os mesmos elementos
Incomparável	Nenhum dos conjuntos contém o outro

Exemplo de subconjunto à esquerda: na consulta abaixo, subq1 aplica a condição extra t2.a > 10, portanto seu resultado é sempre um subconjunto de subq2.

SELECT a FROM t
WHERE EXISTS (SELECT /*+ subq1 */ t2.a FROM t2 WHERE t2.a > 10)   -- subq1
  AND EXISTS (SELECT /*+ subq2 */ t2.a FROM t2);                   -- subq2

Pré-requisitos

Antes de começar, verifique se você possui:

Um cluster PolarDB for MySQL 8.0 na versão de revisão 8.0.2.2.23 ou posterior

Para verificar a versão do seu cluster, consulte Versões do mecanismo 5,6, 5,7 e 8,0.

Ativar o subquery folding

Dois parâmetros controlam o comportamento do folding:

Parâmetro	Escopo	Padrão	Descrição
`loose_polar_optimizer_switch`	Global	`coalesce_subquery=off`	Ativa ou desativa o subquery folding. Defina como `coalesce_subquery=on` para ativar.
`force_coalesce_subquery`	Global / sessão	`OFF`	Força operações de mesclagem marcadas como nem sempre ideais. O componente Cost Based Query Transformation (CBQT) do otimizador normalmente decide se a mesclagem melhora o desempenho; definir este parâmetro como `ON` ignora essa verificação.

Ative o subquery folding:

SET loose_polar_optimizer_switch = 'coalesce_subquery=on';

Force a mesclagem de subconsultas na sessão atual (use apenas após confirmar que a mesclagem é benéfica):

SET force_coalesce_subquery = ON;

Direcione subconsultas específicas com a sintaxe HINT: nomeie blocos de consulta com QB_NAME e especifique quais pares devem ser mesclados com SUBQUERY_COALESCE:

DESC SELECT /*+ SUBQUERY_COALESCE(qb1, qb2) SUBQUERY_COALESCE(qb3, qb4) */
  *
FROM t1
LEFT JOIN t2 ON t1.a = ANY  (SELECT /*+ QB_NAME(qb1) */ a FROM t2)
           AND t1.a != ALL  (SELECT /*+ QB_NAME(qb2) */ a FROM t2 WHERE a < 100)
HAVING     t1.b  = ANY      (SELECT /*+ QB_NAME(qb3) */ b FROM t2)
       AND t1.b != ALL      (SELECT /*+ QB_NAME(qb4) */ b FROM t2 WHERE b < 1);

Os objetos mesclados podem aparecer em qualquer posição nas condições WHERE , HAVING ou JOIN ON , inclusive sob os operadores AND e OR .

Regras de folding

Subconsultas do mesmo tipo

Operador AND

Tipos de subconsulta	Relação de inclusão	Resultado
Ambas: EXISTS, IN, ANY ou ALL	Subconjunto à esquerda ou igualdade	Remoção: subconsulta da direita removida, da esquerda mantida
Ambas: EXISTS, IN, ANY ou ALL	Subconjunto à direita	Remoção: subconsulta da esquerda removida, da direita mantida
Ambas: NOT EXISTS, NOT IN ou `!= ALL`	Incomparável	Mesclagem (nem sempre ideal): condições WHERE ou HAVING combinadas em uma única subconsulta. Requer subconsultas SPJ ou subconsultas apenas com condições SPJ e HAVING. Subconsultas apenas com condições WHERE ou condições HAVING inconsistentes também são suportadas.

Operador OR

Tipos de subconsulta	Relação de inclusão	Resultado
Ambas: EXISTS, IN, ANY ou ALL	Subconjunto à esquerda ou igualdade	Remoção: subconsulta da esquerda removida, da direita mantida
Ambas: EXISTS, IN, ANY ou ALL	Subconjunto à direita	Remoção: subconsulta da direita removida, da esquerda mantida
Ambas: EXISTS, IN ou ANY	Incomparável	Mesclagem (nem sempre ideal): condições WHERE ou HAVING combinadas em uma única subconsulta. Requer subconsultas SPJ ou subconsultas apenas com condições SPJ e HAVING. Subconsultas apenas com condições WHERE ou condições HAVING inconsistentes também são suportadas.

Subconsultas mutuamente exclusivas

Operador AND

Tipos de subconsulta	Relação de inclusão	Condições de mesclagem	Resultado
EXISTS + NOT EXISTS; IN + NOT IN	Subconjunto à esquerda ou igualdade	—	Remoção: condição AND reescrita para `FALSE`
EXISTS + NOT EXISTS	Subconjunto à direita	Bloco de consulta não pode ser UNION; apenas condições WHERE diferem; subconsultas aninhadas suportadas	Mesclagem (nem sempre ideal): conjuntos mesclados, adicionado `HAVING SUM(CASE WHEN extra_cond THEN 1 ELSE 0 END) ==0`
`!= ANY` + `= ALL`; `< ANY` + `>= ALL` ou `> ALL`; `<= ANY` + `> ALL`; `> ANY` + `<= ALL` ou `< ALL`; `>= ANY` + `< ALL`	Subconjunto à esquerda ou igualdade	—	Remoção: condição AND reescrita para `FALSE`
IN + NOT IN; `= ANY` + `!= ALL`	Subconjunto à direita	Bloco de consulta não pode ser UNION; apenas condições WHERE ou HAVING diferem; subconsultas aninhadas suportadas	Mesclagem (sempre ideal): conjuntos mesclados, operador LNNVL adicionado. Aplicado por padrão sem necessidade de `force_coalesce_subquery`.

Operador OR

Tipos de subconsulta	Relação de inclusão	Resultado
EXISTS + NOT EXISTS	Subconjunto à direita	Remove: condição OR reescrita para `TRUE`

Exemplos

Remover subconsultas do mesmo tipo

Condição AND

-- Before
SELECT * FROM t1
WHERE EXISTS (SELECT 1 FROM t2 WHERE c2 = 0)   -- Subquery 1
  AND EXISTS (SELECT 1 FROM t2);               -- Subquery 2

A subconsulta 1 é um subconjunto da subconsulta 2. Sob AND, a subconsulta 2 é redundante e, portanto, removida.

-- After
SELECT * FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE c2 = 0);

Condição OR

-- Before
SELECT * FROM t1
WHERE EXISTS (SELECT 1 FROM t2 WHERE c2 = 0)   -- Subquery 1
   OR EXISTS (SELECT 1 FROM t2);               -- Subquery 2

A subconsulta 1 é um subconjunto da subconsulta 2. Sob OR, o conjunto maior prevalece e a subconsulta 1 é removida.

-- After
SELECT * FROM t1 WHERE EXISTS (SELECT 1 FROM t2);

Mesclar subconsultas do mesmo tipo

Condição AND

-- Before
SELECT * FROM t1
WHERE NOT EXISTS (SELECT t1.a AS f FROM t1 WHERE a > 10 AND b < 10)
  AND NOT EXISTS (SELECT a           FROM t1 WHERE a > 10 AND c < 3);

Ambas as subconsultas acessam a mesma tabela com a mesma condição base (a > 10). As condições extras são mescladas com OR.

-- After
SELECT * FROM t1
WHERE NOT EXISTS (SELECT t1.a AS f FROM t1 WHERE a > 10 AND (b < 10 OR c < 3));

Condição OR

-- Before
SELECT * FROM t1
WHERE EXISTS (SELECT t1.a AS f FROM t1 WHERE a > 10 AND b < 10)
   OR EXISTS (SELECT a           FROM t1 WHERE a > 10 AND c < 3);

Ambas as subconsultas acessam a mesma tabela com a mesma condição base. As condições extras são mescladas com OR.

-- After
SELECT * FROM t1
WHERE EXISTS (SELECT t1.a AS f FROM t1 WHERE a > 10 AND (b < 10 OR c < 3));

Remover subconsultas mutuamente exclusivas

EXISTS e NOT EXISTS — Condição AND

-- Before
SELECT * FROM t1
WHERE     EXISTS (SELECT 1 FROM t2 WHERE c1 = 0)   -- Subquery 1
  AND NOT EXISTS (SELECT 1 FROM t2);               -- Subquery 2

A subconsulta 1 é um subconjunto da subconsulta 2. Uma linha não pode satisfazer simultaneamente EXISTS (subset) e NOT EXISTS (superset), logo a condição AND é sempre falsa.

-- After
SELECT * FROM t1 WHERE false;

Conflito entre ANY e ALL — Condição AND

Aplica-se a: > ANY + < ALL ou <= ALL; < ANY + > ALL ou >= ALL.

-- Before
SELECT * FROM t1
WHERE t1.c1 > ANY (SELECT c1 FROM t2 WHERE c1 > 10 AND c2 > 1)   -- ANY set
  AND t1.c1 < ALL (SELECT c1 FROM t2 WHERE c1 > 10);              -- ALL set

O conjunto ANY é um subconjunto do conjunto ALL. Nenhum valor pode ser simultaneamente maior que algum elemento do subconjunto e menor que todos os elementos do superconjunto, logo a condição AND é sempre falsa.

-- After
SELECT * FROM t1 WHERE false; //The ANY set is a subset of the ALL set.

EXISTS e NOT EXISTS — Condição OR

-- Before
SELECT * FROM t1
WHERE     EXISTS (SELECT 1 FROM t2)                    -- Subquery 1
   OR NOT EXISTS (SELECT 1 FROM t2 WHERE c1 = 0);     -- Subquery 2

A subconsulta 2 é um subconjunto da subconsulta 1. Sob OR, se o superconjunto não for vazio, a condição é verdadeira; se for vazio, o complemento do subconjunto é sempre verdadeiro. Portanto, a condição OR é sempre verdadeira.

-- After
SELECT * FROM t1 WHERE true; // Subquery 2 is a subset of subquery 1.

Mesclar subconsultas mutuamente exclusivas

Mesclar EXISTS e NOT EXISTS

-- Before
SELECT * FROM t1
WHERE     EXIST (SELECT 1 FROM t2)                -- Subquery 1
  AND NOT EXIST (SELECT 1 FROM t2 WHERE c2 = 0); -- Subquery 2

O conjunto NOT EXISTS é um subconjunto à direita do conjunto EXISTS. O otimizador mescla ambos em uma única varredura e adiciona uma condição HAVING para excluir as linhas correspondentes ao predicado NOT EXISTS.

-- After
SELECT * FROM t1
WHERE EXIST (
  SELECT 1 FROM t2
  HAVING SUM(CASE WHEN extra_cond THEN 1 ELSE 0 END) ==0
);

Esta mesclagem nem sempre é ideal. Por padrão, o componente CBQT decide se deve aplicá-la. Para forçar a mesclagem, defina force_coalesce_subquery = ON .

O gráfico a seguir mostra a duração da consulta TPCH Q21 antes e depois de ativar o subquery folding. Uma barra mais curta indica melhor desempenho.

Mesclar IN e NOT IN (ou = ANY e != ALL)

Aplica-se a: IN + NOT IN (o conjunto NOT IN é o subconjunto à esquerda); = ANY + != ALL (o conjunto ALL é o subconjunto à esquerda).

-- Before
SELECT * FROM t1
WHERE  t1.c1  = ANY (SELECT c1 FROM t2 WHERE c1 > 10)     -- = ANY set
  AND t1.c1 != ALL  (SELECT c1 FROM t2 WHERE c1 > 100);   -- != ALL set (left subset)

O conjunto != ALL (c1 > 100) é um subconjunto do conjunto = ANY (c1 > 10). O otimizador aplica o folding adicionando a condição LNNVL à subconsulta maior, excluindo as linhas que também satisfazem a subconsulta menor.

-- After
SELECT * FROM t1
WHERE t1.c1 = ANY (SELECT c1 FROM t2 WHERE c1 > 10 AND LNNVL(c1 > 100));

Esta mesclagem é sempre ideal e aplicada por padrão, sem necessidade de configurar force_coalesce_subquery.