PolarDB:Eliminação de JOIN

Cenário 1: LEFT JOINs com condição FALSE constante

Se a condição ON de um LEFT JOIN for sempre FALSE, a junção não produzirá correspondências. O otimizador remove a tabela interna e substitui todas as suas colunas por NULL.

-- Before optimization
SELECT ..., ti1.*, ti2.*, ... FROM ... LEFT JOIN (ti1, ti2, ...) ON FALSE;
-- After optimization
SELECT ..., NULL, NULL, ... FROM ...;

Condição para eliminação: A condição ON é uma expressão FALSE constante, como 1=0 ou FALSE.

Exemplo

1. Prepare o ambiente.

   DROP TABLE IF EXISTS orders;
   CREATE TABLE orders (
       order_id INT PRIMARY KEY,
       customer_id INT,
       order_date DATE
   );
   DROP TABLE IF EXISTS customers;
   CREATE TABLE customers (
       customer_id INT PRIMARY KEY,
       customer_name VARCHAR(100)
   );
   INSERT INTO orders VALUES (1, 101, '2023-10-01');

2. Desative a eliminação de JOIN e execute EXPLAIN para visualizar o plano não otimizado.

   SET SESSION join_elimination_mode = 'OFF';
   EXPLAIN SELECT o.*, c.* FROM orders o LEFT JOIN customers c ON FALSE;
   SHOW WARNINGS;

   Saída:

   +-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
   | Level | Code | Message                                                                                                                                                                                                                                                                                                                          |
   +-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
   | Note  | 1003 | /* select#1 */ select `testdb`.`o`.`order_id` AS `order_id`,`testdb`.`o`.`customer_id` AS `customer_id`,`testdb`.`o`.`order_date` AS `order_date`,`testdb`.`c`.`customer_id` AS `customer_id`,`testdb`.`c`.`customer_name` AS `customer_name` from `testdb`.`orders` `o` left join `testdb`.`customers` `c` on(false) where true |
   +-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

3. Ative a eliminação de JOIN e execute a mesma consulta.

   SET SESSION join_elimination_mode = 'ON';
   EXPLAIN SELECT o.*, c.* FROM orders o LEFT JOIN customers c ON FALSE;
   SHOW WARNINGS;

   Saída:

   +-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
   | Level | Code | Message                                                                                                                                                                                                                    |
   +-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
   | Note  | 1003 | /* select#1 */ select `testdb`.`o`.`order_id` AS `order_id`,`testdb`.`o`.`customer_id` AS `customer_id`,`testdb`.`o`.`order_date` AS `order_date`,NULL AS `customer_id`,NULL AS `customer_name` from `testdb`.`orders` `o` |
   +-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

   A tabela customers não aparece mais na consulta reescrita, pois a junção foi eliminada.

Cenário 2: LEFT JOINs em chaves únicas que não afetam o resultado

Se a tabela interna de um LEFT JOIN não for referenciada em outra parte da consulta e a junção não alterar o número de linhas retornadas da tabela externa, será seguro removê-la.

-- Before optimization
SELECT to1.*, to2.*, ..., tom.*
FROM to1, to2, ..., tom LEFT JOIN (ti1, ti2, ..., tin) ON cond_on
WHERE cond_where ...
-- After optimization
SELECT to1.*, to2.*, ..., tom.*
FROM to1, to2, ..., tom
WHERE cond_where ...

Condições para eliminação:

• Tabela interna não referenciada: Nenhuma coluna das tabelas internas (ti1, ti2, ..., tin) aparece fora do LEFT JOIN e de sua condição ON.

• A junção não afeta a cardinalidade da tabela externa: Uma das seguintes condições deve ser verdadeira:

  • A junção é única: cada linha na tabela externa corresponde a, no máximo, uma linha na tabela interna.

  • A junção pode produzir duplicatas, mas operações subsequentes da consulta cancelam seu efeito. Por exemplo, a junção está dentro de uma cláusula EXISTS ou IN (um SEMI JOIN), ou a subconsulta contém GROUP BY, LIMIT ou funções de janela.

  • Uso recursivo das duas condições anteriores.

Cenário 3: Autojunções

Quando uma tabela (ou sua tabela derivada) se une a si mesma usando um INNER JOIN e condições específicas são atendidas, o otimizador identifica uma cópia como redundante e a remove.

Tabela base unida a tabela base:

-- Before optimization
SELECT target.*, source.* FROM t1 AS target JOIN t1 AS source WHERE target.uk = source.uk;
-- After optimization
SELECT source.*, source.* FROM source WHERE source.uk = source.uk;

Tabela derivada unida a tabela derivada:

-- Before optimization
SELECT target.*, source.*
FROM (SELECT * FROM t1) target
JOIN (SELECT * FROM t1 WHERE t1.a > 1) source
WHERE target.uk = source.uk;
-- After optimization
SELECT source.*, source.*
FROM (SELECT * FROM t1 WHERE t1.a > 1) source
WHERE source.uk = source.uk;

Tabela base unida a tabela derivada:

-- Before optimization
SELECT target.*, source.*
FROM t1 target
JOIN (SELECT * FROM t1 WHERE t1.a > 1) source
WHERE target.uk = source.uk;
-- After optimization
SELECT source.*, source.*
FROM (SELECT * FROM t1 WHERE t1.a > 1) source
WHERE source.uk = source.uk;

Condições para eliminação:

• Relação de subconjunto: O conjunto de resultados de source deve ser um subconjunto do conjunto de resultados de target.

• Disponibilidade de colunas: Todas as colunas referenciadas de target também devem existir em source.

• Junção por chave única: A condição de JOIN deve usar uma comparação de igualdade em uma chave única ou chave primária na tabela target. Isso garante que cada linha de source corresponda a, no máximo, uma linha de target. Assim, remover target não altera a contagem de linhas ou os resultados.

  • Caso especial: Se não houver condição de igualdade de chave única, a eliminação se aplica apenas quando o conjunto de resultados de target contém zero ou uma linha.

Cenário 4: Auto-semi-joins

Quando uma tabela passa por uma semi-join consigo mesma (expressa como uma cláusula IN ou EXISTS), o otimizador pode eliminar a tabela interna e mesclar suas condições na consulta externa.

Com condição de igualdade de coluna única:

-- Before optimization
SELECT source.*
FROM t1 AS source
WHERE EXISTS (
    SELECT * FROM t1 AS target
    WHERE source.uk = target.uk AND target.a > 1
);
-- After optimization
SELECT source.* FROM t1 AS source WHERE source.uk = source.uk AND source.a > 1;

Sem condição de igualdade de coluna única:

-- Before optimization
SELECT source.*
FROM t1 AS source
WHERE EXISTS (SELECT * FROM t1 AS target WHERE source.a = target.a);
-- After optimization
SELECT source.* FROM t1 AS source WHERE source.a = source.a;

Condições para eliminação:

• Relação de subconjunto: O conjunto de resultados de source deve ser um subconjunto do conjunto de resultados de target.

• Disponibilidade de colunas: Todas as colunas referenciadas de target também devem existir em source.

• Uma das seguintes condições também deve ser verdadeira:

  • Junção por chave única: A condição de JOIN usa uma comparação de igualdade em uma chave única ou chave primária em target. Isso assegura que cada linha de source corresponda a, no máximo, uma linha de target.

    • Caso especial: Sem igualdade de chave única, a eliminação se aplica apenas quando o conjunto de resultados de target contém zero ou uma linha.

  • Todas as condições de junção são comparações de igualdade (target.col = source.col) conectadas por AND.

Cenário 5: Junções por chave estrangeira

Quando existe uma restrição de chave estrangeira (FK) entre duas tabelas e a consulta referencia apenas colunas da tabela filha (a tabela com a FK), a junção com a tabela pai é redundante. A restrição de FK já garante que cada linha filha tenha uma linha pai correspondente.

CREATE TABLE target (a INT PRIMARY KEY);
CREATE TABLE source (a INT, FOREIGN KEY (a) REFERENCES target(a));

-- Before optimization
SELECT target.a, source.a FROM target, source WHERE target.a = source.a;
-- After optimization
SELECT source.a, source.a FROM source WHERE source.a = source.a;

Condições para eliminação:

• Referências substituíveis à tabela pai: É possível substituir todas as colunas da tabela pai pelas colunas de FK correspondentes na tabela filha. Na prática, isso significa que apenas a chave de junção da tabela pai é referenciada.

• Condição de junção baseada na chave estrangeira: A condição de JOIN é uma igualdade entre a coluna de FK da tabela filha e a coluna de chave primária ou chave única da tabela pai.

• Chave estrangeira NOT NULL: A coluna de FK na tabela filha é definida como NOT NULL, o que garante que cada linha filha tenha uma linha pai correspondente.

Cenário 6: Semi-joins por chave estrangeira

Quando uma cláusula EXISTS ou IN verifica a existência de um registro correspondente na tabela pai com base em um relacionamento de FK, a verificação é redundante. A restrição de FK já garante que a correspondência exista. O otimizador elimina a subconsulta SEMI JOIN.

CREATE TABLE target (a INT PRIMARY KEY);
CREATE TABLE source (a INT, FOREIGN KEY (a) REFERENCES target(a));

-- Before optimization
SELECT source.a FROM source WHERE EXISTS (SELECT * FROM target WHERE target.a = source.a);
-- After optimization
SELECT source.a FROM source WHERE source.a = source.a;

Condições para eliminação: As mesmas três condições do Cenário 5 — referências ao pai substituíveis, condição de junção baseada em FK e coluna de FK NOT NULL.