AnalyticDB:Reescrita de consulta de visualizações materializadas

Pré-requisitos

• Um cluster do AnalyticDB for MySQL deve executar a versão V3.1.4.0 ou posterior.

  Nota

  • Para verificar a versão secundária de um cluster Data Lakehouse Edition, execute SELECT adb_version();. Para atualizar a versão secundária, entre em contato com o suporte técnico.

  • Para visualizar e atualizar a versão secundária de um cluster Data Warehouse Edition, consulte Upgrade minor version.

• O uso de visualizações materializadas exige as seguintes permissões:

  • Permissão CREATE no banco de dados onde reside a visualização materializada.

  • Permissão SELECT nas colunas relevantes ou em todas as tabelas base da visualização materializada.

  • Para criar uma visualização materializada com atualização automática, também são necessárias estas duas permissões:

    • Permissão para conectar-se ao AnalyticDB for MySQL a partir de qualquer endereço IP (ou seja, '%').

    • Permissão INSERT na visualização materializada ou em todas as tabelas do banco de dados onde ela reside. Caso contrário, não será possível atualizar os dados da visualização materializada.

Como funciona

O AnalyticDB for MySQL compara cada consulta recebida com todas as visualizações materializadas que têm a reescrita de consulta ativada. Se houver correspondência, o mecanismo reescreve a consulta para ler da visualização materializada em vez das tabelas base. Duas estratégias de correspondência são utilizadas, aplicadas na seguinte ordem:

• Reescrita por correspondência exata: ocorre quando a estrutura da consulta é idêntica à definição da visualização materializada. Esta é a estratégia mais simples e com menos restrições.

• Reescrita avançada de consulta: aplicada quando as estruturas diferem. O mecanismo utiliza regras de reescrita (FILTER, JOIN, AGGREGATION, AGGREGATION ROLLUP, SUBQUERIES, QUERY PARTIAL, UNION) para determinar se a visualização materializada contém dados suficientes para responder à consulta total ou parcialmente. Subconsultas diferentes na mesma instrução podem corresponder a visualizações materializadas distintas.

Todas as reescritas de consulta operam no nível STALE_TOLERATED: o mecanismo reescreve a consulta mesmo que a visualização materializada contenha dados desatualizados ainda não sincronizados com as tabelas base. Isso maximiza a cobertura da reescrita, mas significa que os resultados podem não refletir as inserções ou atualizações mais recentes. Atualize suas visualizações materializadas antes de executar consultas sensíveis à latência. Para obter detalhes, consulte Configure full refresh for materialized views.

Ativar a reescrita de consulta

Ative a reescrita de consulta de uma das duas maneiras abaixo:

• No momento da criação, inclua a cláusula ENABLE QUERY REWRITE na instrução CREATE MATERIALIZED VIEW. Consulte Create a materialized view — Parameters.

• Após a criação, execute:

  ALTER MATERIALIZED VIEW <mv_name> ENABLE QUERY REWRITE;

  Consulte Gerencie visualizações materializadas.

Desativar a reescrita de consulta

Desative a reescrita de consulta de uma das duas maneiras abaixo:

• Para uma visualização materializada específica:

  ALTER MATERIALIZED VIEW <mv_name> DISABLE QUERY REWRITE;

• Para uma consulta específica, adicione uma dica antes da instrução SELECT:

  /*+MV_QUERY_REWRITE_ENABLED=false*/
  SELECT ...

Verificar se a reescrita de consulta está ativa

Após ativar a reescrita de consulta, use EXPLAIN para confirmar se o mecanismo está lendo da visualização materializada.

Exemplo

1. Crie uma visualização materializada com a reescrita de consulta ativada.

   CREATE MATERIALIZED VIEW adb_mv
   REFRESH START WITH now() + interval 1 day
   ENABLE QUERY REWRITE
   AS
   SELECT course_id, course_name, max(course_grade) AS max_grade FROM tb_courses;

2. Depois de ativar a reescrita de consulta, execute EXPLAIN na consulta.

   EXPLAIN SELECT course_id, course_name, max(course_grade) AS max_grade FROM tb_courses;

3. Verifique o plano de execução. Quando a reescrita de consulta está ativa, a linha TableScan mostra o nome da visualização materializada (adb_mv), e não o da tabela base (tb_courses).

   +---------------+
   | Plan Summary  |
   +---------------+
    1- Output[ Query plan ] {Est rowCount: 1.0}
    2    -> Exchange[GATHER] {Est rowCount: 1.0}
    3        - TableScan {table: adb_mv, Est rowCount: 1.0}

   Se o plano ainda mostrar TableScan {table: tb_courses, ...}, a reescrita de consulta não foi ativada. Consulte a seção Solução de problemas.

Escopo da reescrita

Os exemplos a seguir demonstram cada regra de reescrita suportada pelo método de reescrita avançada de consulta. Todos os exemplos usam as mesmas quatro tabelas:

CREATE TABLE part (
  partkey INTEGER NOT NULL,
  name VARCHAR(55) NOT NULL,
  type VARCHAR(25) NOT NULL
);

CREATE TABLE lineitem (
  orderkey BIGINT,
  partkey BIGINT NOT NULL,
  suppkey BIGINT NOT NULL,
  extendedprice DOUBLE NOT NULL,
  discount DOUBLE NOT NULL,
  returnflag CHAR(1) NOT NULL,
  linestatus CHAR(1) NOT NULL,
  shipdate DATE NOT NULL,
  shipmode VARCHAR(25) NOT NULL,
  commitdate DATE NOT NULL,
  receiptdate DATE NOT NULL
);

CREATE TABLE orders (
  orderkey BIGINT PRIMARY KEY,
  custkey BIGINT NOT NULL,
  orderstatus VARCHAR(1) NOT NULL,
  totalprice DOUBLE NOT NULL,
  orderdate DATE NOT NULL
);

CREATE TABLE partsupp (
  partkey INTEGER NOT NULL PRIMARY KEY,
  suppkey INTEGER NOT NULL,
  availqty INTEGER NOT NULL,
  supplycost DECIMAL(15,2) NOT NULL
);

SELECT
  l.returnflag,
  l.linestatus,
  SUM(l.extendedprice * (1 - l.discount)),
  COUNT(*) AS count_order
FROM lineitem AS l
GROUP BY l.returnflag, l.linestatus;

CREATE MATERIALIZED VIEW mv0
REFRESH NEXT now() + interval 1 day
ENABLE QUERY REWRITE
AS
SELECT
  l.returnflag,
  l.linestatus,
  SUM(l.extendedprice * (1 - l.discount)) AS sum_disc_price,
  COUNT(*) AS count_order
FROM lineitem AS l
GROUP BY l.returnflag, l.linestatus;

SELECT returnflag, linestatus, sum_disc_price, count_order
FROM mv0;

SELECT
  l.shipmode,
  l.extendedprice * (1 - l.discount) AS disc_price
FROM orders AS o, lineitem AS l
WHERE o.orderkey = l.orderkey
  AND l.shipmode IN ('REG AIR', 'TRUCK')
  AND l.commitdate < l.receiptdate
  AND l.shipdate < l.commitdate;

CREATE MATERIALIZED VIEW mv1
REFRESH NEXT now() + interval 1 day
ENABLE QUERY REWRITE
AS
SELECT
  l.shipmode,
  l.extendedprice,
  l.discount
FROM orders AS o, lineitem AS l
WHERE o.orderkey = l.orderkey
  AND l.commitdate < l.receiptdate
  AND l.shipdate < l.commitdate;

SELECT
  shipmode,
  extendedprice * (1 - discount) AS disc_price,
  discount
FROM mv1
WHERE shipmode IN ('REG AIR', 'TRUCK');

SELECT
  p.type,
  p.partkey,
  ps.suppkey
FROM part AS p, partsupp AS ps
WHERE p.partkey = ps.partkey
  AND p.type NOT LIKE 'MEDIUM POLISHED%';

CREATE MATERIALIZED VIEW mv2
REFRESH NEXT now() + INTERVAL 1 day
ENABLE QUERY REWRITE
AS
SELECT
  p.type,
  p.partkey,
  ps.suppkey
FROM partsupp AS ps
INNER JOIN part AS p ON p.partkey = ps.partkey
WHERE p.type NOT LIKE 'MEDIUM POLISHED%';

SELECT type, partkey, suppkey
FROM mv2;

SELECT
  l.returnflag,
  l.linestatus,
  SUM(l.extendedprice * (1 - l.discount)) AS sum_disc_price
FROM lineitem AS l
GROUP BY l.returnflag, l.linestatus;

CREATE MATERIALIZED VIEW mv3
REFRESH NEXT now() + INTERVAL 1 day
ENABLE QUERY REWRITE
AS
SELECT
  l.returnflag,
  l.linestatus,
  SUM(l.extendedprice * (1 - l.discount)) AS sum_disc_price,
  COUNT(*) AS count_order
FROM lineitem AS l
GROUP BY l.returnflag, l.linestatus;

SELECT returnflag, linestatus, sum_disc_price, count_order
FROM mv3;

SELECT
  l.returnflag,
  SUM(l.extendedprice * (1 - l.discount)) AS sum_disc_price,
  COUNT(*) AS count_order
FROM lineitem AS l
WHERE l.returnflag = 'R'
GROUP BY l.returnflag;

CREATE MATERIALIZED VIEW mv4
REFRESH NEXT now() + INTERVAL 1 day
ENABLE QUERY REWRITE
AS
SELECT
  l.returnflag,
  l.linestatus,
  SUM(l.extendedprice * (1 - l.discount)) AS sum_disc_price,
  COUNT(*) AS count_order
FROM lineitem AS l
GROUP BY l.returnflag, l.linestatus;

SELECT
  returnflag,
  linestatus,
  sum_disc_price,
  count_order
FROM mv4
WHERE returnflag = 'R'
GROUP BY returnflag;

SELECT
  p.type,
  p.partkey,
  ps.suppkey
FROM part AS p,
  (SELECT * FROM partsupp WHERE suppkey > 10) ps
WHERE p.partkey = ps.partkey;

CREATE MATERIALIZED VIEW mv5
REFRESH NEXT now() + INTERVAL 1 day
ENABLE QUERY REWRITE
AS
SELECT
  p.type,
  p.partkey,
  ps.suppkey
FROM part AS p, partsupp AS ps
WHERE p.partkey = ps.partkey;

SELECT type, partkey, suppkey
FROM mv5
WHERE suppkey > 10;

SELECT
  p.type,
  p.partkey,
  ps.suppkey
FROM part AS p, partsupp AS ps
WHERE p.partkey = ps.partkey
  AND p.type NOT LIKE 'MEDIUM POLISHED%';

CREATE MATERIALIZED VIEW mv6
REFRESH NEXT now() + INTERVAL 1 day
ENABLE QUERY REWRITE
AS
SELECT
  p.type,
  p.partkey
FROM part AS p
WHERE p.type NOT LIKE 'MEDIUM POLISHED%';

SELECT
  mv6.type,
  mv6.partkey,
  ps.suppkey
FROM mv6, partsupp AS ps
WHERE mv6.partkey = ps.partkey;

SELECT
  l.linestatus,
  COUNT(*) AS count_order
FROM lineitem AS l
WHERE l.shipdate >= DATE '1998-01-01'
GROUP BY l.linestatus;

CREATE MATERIALIZED VIEW mv7
REFRESH NEXT now() + interval 1 day
ENABLE QUERY REWRITE
AS
SELECT
  l.linestatus,
  COUNT(*) AS count_order
FROM lineitem AS l
WHERE l.shipdate >= DATE '2000-01-01'
GROUP BY l.linestatus;

SELECT linestatus, count_order
FROM (
    SELECT linestatus, count_order
    FROM mv7
  UNION ALL
    SELECT
      l.linestatus,
      COUNT(*) AS count_order
    FROM lineitem AS l
    WHERE l.shipdate >= DATE '1998-01-01' AND l.shipdate < DATE '1998-01-01'
    GROUP BY l.linestatus
)
GROUP BY linestatus;

Limitações

Solução de problemas

A reescrita de consulta não está sendo ativada após a criação de uma visualização materializada.

Comece pelas causas mais comuns:

1. A reescrita de consulta não está ativada na visualização materializada. Execute ALTER MATERIALIZED VIEW <mv_name> ENABLE QUERY REWRITE; e tente novamente.

2. A visualização materializada atingiu uma limitação. Revise a seção Limitations e verifique se a definição da sua visualização materializada inclui alguma cláusula ou função não suportada.

3. Falta a permissão SELECT na visualização materializada. Conceda a permissão necessária à conta que executa a consulta:

   GRANT SELECT ON <database>.<mv_name> TO '<account>';

   Para obter detalhes, consulte a seção Required permissions do tópico Consultar dados de uma visualização materializada.

Próximos passos

• Criar uma visualização materializada

• Configure full refresh for materialized views

• Gerenciar visualizações materializadas

• Consultar dados de uma visualização materializada