PolarDB:Desaninhamento de subconsultas

Como funciona

A figura a seguir ilustra a estrutura de uma consulta que contém uma subconsulta.

T1, T2 e T3 representam, cada um, um conjunto de uma ou mais tabelas e visualizações. T2 (dentro da subconsulta) está aninhado com T3, conforme indicado pela linha pontilhada. T1 pertence à consulta externa e não está aninhado com T2.

A estratégia de função de janela se aplica quando todas as condições a seguir são atendidas:

• A subconsulta escalar não contém cláusula LIMIT ou DISTINCT, e sua saída é uma função de agregação.

• A tabela na subconsulta é um subconjunto das tabelas da consulta externa.

• A subconsulta conecta-se à consulta externa por meio de uma equi-junção. A consulta externa contém condições de junção com a mesma semântica e condições de filtro para tabelas comuns presentes na subconsulta.

• A coluna de junção na subconsulta é uma coluna de chave primária ou chave única.

• Nem a subconsulta nem a consulta externa contêm funções personalizadas ou funções aleatórias.

Após o desaninhamento, a função de janela calcula a agregação em grupos e anexa o resultado a cada linha, eliminando a necessidade de reexecutar a subconsulta.

Exemplo

O exemplo a seguir utiliza a consulta TPC-H Q2 (Minimum Cost Supplier Query), que identifica o fornecedor com o menor preço entre todos os fornecedores de uma região que vendem componentes de um tipo e tamanho específicos.

No MySQL Community Edition, a consulta externa recupera primeiro todos os fornecedores correspondentes. Em seguida, a subconsulta é executada em cada linha para encontrar o custo mínimo de fornecimento. Para grandes conjuntos de dados, isso significa que a subconsulta é executada uma vez para cada linha de fornecedor.

Consulta original:

SELECT s_acctbal, s_name, n_name, p_partkey, p_mfgr,
 s_address, s_phone, s_comment
FROM part, supplier, partsupp, nation, region
WHERE p_partkey = ps_partkey
   AND s_suppkey = ps_suppkey
   AND p_size = 30
   AND p_type LIKE '%STEEL'
   AND s_nationkey = n_nationkey
   AND n_regionkey = r_regionkey
   AND r_name = 'ASIA'
   AND ps_supplycost = (
       SELECT MIN(ps_supplycost)
       FROM partsupp, supplier, nation, region
       WHERE p_partkey = ps_partkey
           AND s_suppkey = ps_suppkey
           AND s_nationkey = n_nationkey
           AND n_regionkey = r_regionkey
           AND r_name = 'ASIA'
   )
ORDER BY s_acctbal DESC, n_name, s_name, p_partkey
LIMIT 100;

O PolarDB reescreve essa consulta utilizando MIN() OVER(PARTITION BY ps_partkey) para calcular o custo mínimo por peça em uma única passagem e, em seguida, filtra as linhas em que o custo de fornecimento corresponde ao mínimo do grupo.

Consulta reescrita:

SELECT s_acctbal, s_name, n_name, p_partkey, p_mfgr,
  s_address, s_phone, s_comment
FROM (
    SELECT MIN(ps_supplycost) OVER(PARTITION BY ps_partkey) as win_min,
      ps_partkey, ps_supplycost, s_acctbal, n_name, s_name, s_address,
      s_phone, s_comment
    FROM part, partsupp, supplier, nation, region
    WHERE p_partkey = ps_partkey
      AND s_suppkey = ps_suppkey
      AND s_nationkey = n_nationkey
      AND n_regionkey = r_regionkey
      AND p_size = 30
      AND p_type LIKE '%STEEL'
      AND r_name = 'ASIA') as derived
WHERE ps_supplycost = derived.win_min
ORDER BY s_acctbal DESC, n_name, s_name, p_partkey
LIMIT 100;

Melhoria de desempenho

Com fator de escala 10 do TPC-H:

• Q2: melhoria de 1,54x

• Q17: melhoria de 4,91x

Como funciona

A figura a seguir mostra a estrutura de uma consulta que contém uma subconsulta.

A estratégia GROUP BY é aplicável quando todas as condições abaixo são satisfeitas:

• A subconsulta escalar não possui cláusula GROUP BY ou LIMIT, e seu retorno é uma função de agregação.

• A subconsulta escalar aparece em uma condição JOIN, WHERE ou SELECT.

• A subconsulta conecta-se à consulta externa por uma equi-junção, com condições unidas por AND.

• Não há funções personalizadas ou aleatórias na subconsulta escalar.

Após o desaninhamento, a subconsulta torna-se uma tabela derivada que pré-agrega os resultados agrupados pela chave de junção. A consulta externa então se junta a essa tabela derivada uma única vez, substituindo a execução repetida da subconsulta.

Exemplo

Este exemplo recupera pedidos cuja quantidade excede 10% do valor total de compra do mesmo produto.

Consulta original:

SELECT *
FROM sale_lineitem sl
WHERE sl.sl_quantity >
    (SELECT 0.1 * SUM(pl.pl_quantity)
     FROM purchase_lineitem pl
     WHERE pl.pl_objectkey = sl.sl_objectkey);

Sem o desaninhamento, o banco de dados itera sobre cada linha em sale_lineitem, lê sl_objectkey e reexecuta a subconsulta para esse valor. A subconsulta roda tantas vezes quantas forem as linhas em sale_lineitem. Como sl_objectkey geralmente contém muitos valores duplicados, a mesma agregação em purchase_lineitem se repete diversas vezes — mesmo quando existe um índice em pl_objectkey.

O PolarDB reescreve essa consulta como um LEFT JOIN contra uma tabela derivada pré-agregada. A varredura na tabela purchase_lineitem ocorre apenas uma vez.

Consulta reescrita:

SELECT *
FROM sale_lineitem sl
LEFT JOIN
  (SELECT (0.1 * sum(pl.pl_quantity)) AS Name_exp_1,
          pl.pl_objectkey AS Name_exp_2
   FROM purchase_lineitem pl
   GROUP BY pl.pl_objectkey) derived ON derived.Name_exp_2 = sl.sl_objectkey
WHERE sl.sl_quantity > derived.name_exp_1;

O otimizador pode melhorar ainda mais a execução reordenando a junção com base em estimativas de custo.

UNNEST([@query_block_name] [strategy [, strategy] ...])
NO_UNNEST([@query_block_name] [strategy [, strategy] ...])

-- Force window function unnesting
SELECT ... FROM ... WHERE ... = (SELECT /*+UNNEST(WINDOW_FUNCTION)*/ agg FROM ...)
SELECT /*+UNNEST(@`select#2` WINDOW_FUNCTION)*/ ... FROM ... WHERE ... = (SELECT agg FROM ...)

-- Disable window function unnesting
SELECT ... FROM ... WHERE ... = (SELECT /*+NO_UNNEST(WINDOW_FUNCTION)*/ agg FROM ...)
SELECT /*+NO_UNNEST(@`select#2` WINDOW_FUNCTION)*/ ... FROM ... WHERE ... = (SELECT agg FROM ...)

-- Force GROUP BY unnesting
SELECT ... FROM ... WHERE ... = (SELECT /*+UNNEST(GROUP_BY)*/ agg FROM ...)
SELECT /*+UNNEST(@`select#2` GROUP_BY)*/ ... FROM ... WHERE ... = (SELECT agg FROM ...)

-- Disable GROUP BY unnesting
SELECT ... FROM ... WHERE ... = (SELECT /*+NO_UNNEST(GROUP_BY)*/ agg FROM ...)
SELECT /*+NO_UNNEST(@`select#2` GROUP_BY)*/ ... FROM ... WHERE ... = (SELECT agg FROM ...)