PolarDB:Eliminar GROUP BY/ORDER BY redundante

Cenário 1: Agrupamento por chave primária ou única

Condições de acionamento:

• A cláusula GROUP BY inclui a chave primária de uma tabela.

• As demais colunas na lista do GROUP BY são determinadas funcionalmente por essa chave.

Cenário de negócio: calcular a contagem total de pedidos por usuário e exibir o nome de usuário correspondente.

SQL original:

-- user_id is the primary key of the user table and uniquely determines user_name.
SELECT
    u.user_id,
    u.user_name,
    COUNT(o.order_id)
FROM user u
JOIN orders o ON u.user_id = o.user_id
GROUP BY
    u.user_id,
    u.user_name;

Ação do otimizador: como user_id é a chave primária, a FD user_id -> user_name se aplica. O otimizador simplifica GROUP BY u.user_id, u.user_name para GROUP BY u.user_id, eliminando o agrupamento redundante em user_name.

SQL otimizado:

SELECT
    u.user_id,
    u.user_name,
    COUNT(o.order_id)
FROM user u
JOIN orders o ON u.user_id = o.user_id
GROUP BY u.user_id;

Verifique a otimização:

SHOW WARNINGS;

A coluna Message exibe a consulta reescrita apenas com GROUP BY u.user_id, confirmando a remoção de u.user_name da chave de agrupamento.

Cenário 2: Agrupamento por constantes

Condições de acionamento:

• Todas as colunas da cláusula GROUP BY aparecem como constantes de igualdade na cláusula WHERE.

Cenário de negócio: consultar uma linha específica com base em valores exatos de colunas.

SQL original:

SELECT a, b FROM t1 WHERE a = 1 AND b = 1 GROUP BY a, b;

Ação do otimizador: tanto a quanto b são constantes na cláusula WHERE, portanto o conjunto de resultados contém no máximo uma linha. O otimizador remove o GROUP BY e adiciona LIMIT 1.

SQL otimizado:

SELECT a, b FROM t1 WHERE a = 1 AND b = 1 LIMIT 1;

Verifique a otimização:

SHOW WARNINGS;

A coluna Message mostra a consulta reescrita com LIMIT 1 e sem a cláusula GROUP BY.

Cenário 3: Junção de múltiplas tabelas com agrupamento complexo (TPC-H Q10)

Condições de acionamento:

• Uma junção de múltiplas tabelas propaga a FD de uma chave primária por meio de condições de equi-join.

• Todas as colunas não chave na lista do GROUP BY são determinadas transitivamente pela chave primária.

SQL original (TPC-H Q10):

SELECT
    c_custkey, c_name, c_acctbal, c_phone, n_name, c_address, c_comment,
    SUM(l_extendedprice * (1 - l_discount)) AS revenue
FROM customer, orders, lineitem, nation
WHERE c_custkey = o_custkey
  AND l_orderkey = o_orderkey
  AND c_nationkey = n_nationkey
  -- Other filter conditions...
GROUP BY
    c_custkey, c_name, c_acctbal, c_phone, n_name, c_address, c_comment
ORDER BY revenue DESC
LIMIT 20;

Ação do otimizador:

1. Dependência de chave primária: c_custkey é a chave primária da tabela customer, logo determina c_name, c_acctbal, c_phone, c_address, c_comment e c_nationkey.

2. Propagação de dependência: a condição de junção c_nationkey = n_nationkey e o fato de n_nationkey ser a chave primária de nation significam que c_custkey também determina n_name.

3. Simplificação: todas as colunas não chave na lista do GROUP BY dependem funcionalmente de c_custkey. O otimizador simplifica a cláusula para GROUP BY c_custkey.

SQL otimizado:

SELECT
    c_custkey, c_name, c_acctbal, c_phone, n_name, c_address, c_comment,
    SUM(l_extendedprice * (1 - l_discount)) AS revenue
FROM customer, orders, lineitem, nation
WHERE c_custkey = o_custkey
  AND l_orderkey = o_orderkey
  AND c_nationkey = n_nationkey
  -- Other filter conditions...
GROUP BY c_custkey
ORDER BY revenue DESC
LIMIT 20;

Verifique a otimização:

SHOW WARNINGS;

A coluna Message apresenta a consulta reescrita apenas com GROUP BY c_custkey, confirmando a remoção de todas as outras colunas da chave de agrupamento.

Cenário 4: Simplificação de ORDER BY com coluna computada

Condições de acionamento:

• A cláusula ORDER BY inclui uma coluna que é uma função determinística de outras colunas presentes na mesma cláusula.

• Essa coluna está definida como UNIQUE NOT NULL.

Esquema da tabela:

CREATE TABLE t1 (
  a INT,
  b INT,
  c INT AS (a + b) UNIQUE NOT NULL
);

SQL original:

EXPLAIN SELECT a, b FROM t1 ORDER BY a, b, c;

Ação do otimizador: a coluna c é calculada a partir de a e b (c = a + b) e definida como UNIQUE NOT NULL, estabelecendo a FD (a, b) -> c. O otimizador simplifica ORDER BY a, b, c para ORDER BY a, b.

Verifique a otimização:

SHOW WARNINGS;

+-------+------+-------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                       |
+-------+------+-------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` order by `test`.`t1`.`a`,`test`.`t1`.`b` |
+-------+------+-------------------------------------------------------------------------------------------------------------------------------+

A consulta reescrita na coluna Message confirma a remoção de c da chave de ordenação.