AnalyticDB:Reescrita de consultas de visualizações materializadas

Pré-requisitos

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

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

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

• As seguintes permissões nos bancos de dados ou tabelas relevantes:

  Permissão	Necessária para
  CREATE	Criar visualizações materializadas
  INSERT	Atualizar visualizações materializadas
  SELECT	Consultar as tabelas base e colunas referenciadas por uma visualização materializada
  Atualização via 127.0.0.1 ou '%'	Configurar atualização automática em visualizações materializadas de sua propriedade

Como funciona

O AnalyticDB for MySQL compara cada consulta recebida com todas as visualizações materializadas que têm a reescrita de consultas 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 usadas, 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 consultas: aplicada quando as estruturas diferem. O mecanismo usa 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 consultas 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 Configurar atualização completa para visualizações materializadas.

Ativar a reescrita de consultas

Ative a reescrita de consultas 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 Criar uma visualização materializada — Parâmetros.

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

  ALTER MATERIALIZED VIEW <mv_name> ENABLE QUERY REWRITE;

  Consulte Gerenciar visualizações materializadas.

Desativar a reescrita de consultas

Desative a reescrita de consultas 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 consultas está ativa

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

Exemplo

1. Crie uma visualização materializada com a reescrita de consultas 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. Com a reescrita de consultas ativada, 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 consultas está ativa, a linha TableScan mostra o nome da visualização materializada (adb_mv), e não a 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 consultas não foi ativada. Verifique a seção Solução de problemas.

Escopo da reescrita

Os exemplos a seguir demonstram cada regra de reescrita compatível com o método de reescrita avançada de consultas. 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 consultas não está sendo ativada após a criação de uma visualização materializada.

Comece pelas causas mais comuns:

1. A reescrita de consultas não está habilitada 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 Limitações e verifique se a definição da sua visualização materializada inclui alguma cláusula ou função não suportada.

3. Falta 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 Permissões necessárias do tópico Consultar dados de uma visualização materializada.

Próximos passos

• Criar uma visualização materializada

• Configurar atualização completa para visualizações materializadas

• Gerenciar visualizações materializadas

• Consultar dados de uma visualização materializada