AnalyticDB:Réécriture de requête des vues matérialisées

Prérequis

• Un cluster AnalyticDB for MySQL exécute la version mineure 3.1.4.0 ou ultérieure.

  Remarque

  • Pour vérifier la version mineure d'un cluster Data Lakehouse Edition, exécutez SELECT adb_version();. Pour mettre à niveau la version mineure, contactez le support technique.

  • Pour afficher et mettre à niveau la version mineure d'un cluster Data Warehouse Edition, consultez la rubrique Mise à niveau de la version mineure.

• Pour utiliser les vues matérialisées, les autorisations suivantes sont requises :

  • Autorisation CREATE sur la base de données où réside la vue matérialisée.

  • Autorisation SELECT sur les colonnes concernées, ou sur l'intégralité des tables, de toutes les tables de base de la vue matérialisée.

  • Pour créer une vue matérialisée qui est actualisée automatiquement, les deux autorisations suivantes sont également requises :

    • Autorisation de se connecter à AnalyticDB for MySQL depuis n'importe quelle adresse IP (c'est-à-dire '%').

    • Autorisation INSERT sur la vue matérialisée ou sur toutes les tables de la base de données où réside la vue matérialisée. Sinon, les données de la vue matérialisée ne peuvent pas être actualisées.

Fonctionnement

AnalyticDB for MySQL compare chaque requête entrante à toutes les vues matérialisées pour lesquelles la réécriture de requête est activée. Si une correspondance est trouvée, le moteur réécrit la requête pour lire les données depuis la vue matérialisée plutôt que depuis les tables de base. Deux stratégies de mise en correspondance sont utilisées, appliquées dans l'ordre suivant :

• Réécriture par correspondance exacte — lorsque la structure de la requête est identique à la définition de la vue matérialisée. Il s'agit de la stratégie la plus simple, soumise à moins de restrictions.

• Réécriture de requête avancée — lorsque les structures diffèrent. Le moteur applique des règles de réécriture (FILTER, JOIN, AGGREGATION, AGGREGATION ROLLUP, SUBQUERIES, QUERY PARTIAL, UNION) pour déterminer si la vue matérialisée contient suffisamment de données pour répondre à la requête ou à une partie de celle-ci. Différentes sous-requêtes dans la même instruction peuvent correspondre à différentes vues matérialisées.

Toutes les réécritures de requête s'effectuent au niveau STALE_TOLERATED : le moteur réécrit une requête même si la vue matérialisée contient des données obsolètes qui n'ont pas été synchronisées depuis les tables de base. Cela maximise la couverture de la réécriture, mais signifie que les résultats peuvent ne pas refléter les dernières insertions ou mises à jour. Actualisez vos vues matérialisées avant d'exécuter des requêtes sensibles à la latence. Pour plus de détails, consultez la rubrique Configuration de l'actualisation complète des vues matérialisées.

Activation de la réécriture de requête

Activez la réécriture de requête de l'une des deux manières suivantes :

• Lors de la création, incluez la clause ENABLE QUERY REWRITE dans votre instruction CREATE MATERIALIZED VIEW. Consultez la section Création d'une vue matérialisée — Paramètres.

• Après la création, exécutez :

  ALTER MATERIALIZED VIEW <mv_name> ENABLE QUERY REWRITE;

  Consultez la rubrique Gestion des vues matérialisées.

Désactivation de la réécriture de requête

Désactivez la réécriture de requête de l'une des deux manières suivantes :

• Pour une vue matérialisée spécifique :

  ALTER MATERIALIZED VIEW <mv_name> DISABLE QUERY REWRITE;

• Pour une requête spécifique, ajoutez un indice avant l'instruction SELECT :

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

Vérification de l'activation de la réécriture de requête

Après avoir activé la réécriture de requête, utilisez EXPLAIN pour confirmer que le moteur lit les données depuis la vue matérialisée.

Exemple

1. Créez une vue matérialisée avec la réécriture de requête activée.

   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. Après avoir activé la réécriture de requête, exécutez EXPLAIN sur la requête.

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

3. Vérifiez le plan d'exécution. Lorsque la réécriture de requête est active, la ligne TableScan affiche le nom de la vue matérialisée (adb_mv), et non celui de la table de 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}

   Si le plan affiche toujours TableScan {table: tb_courses, ...}, la réécriture de requête ne s'est pas activée. Consultez la section Dépannage.

Portée de la réécriture

Les exemples suivants illustrent chaque règle de réécriture prise en charge par la méthode de réécriture de requête avancée. Tous les exemples utilisent les quatre mêmes tables :

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;

Limitations

Dépannage

La réécriture de requête ne s'active pas après la création d'une vue matérialisée.

Commencez par examiner les causes les plus fréquentes :

1. La réécriture de requête n'est pas activée sur la vue matérialisée. Exécutez ALTER MATERIALIZED VIEW <mv_name> ENABLE QUERY REWRITE; et réessayez.

2. La vue matérialisée atteint une limite. Consultez la section Limitations et vérifiez si la définition de votre vue matérialisée inclut une clause ou une fonction non prise en charge.

3. Autorisation SELECT manquante sur la vue matérialisée. Accordez l'autorisation requise au compte exécutant la requête :

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

   Pour plus de détails, consultez la section Autorisations requises de la rubrique Interrogation des données d'une vue matérialisée.

Étapes suivantes

• Création d'une vue matérialisée

• Configuration de l'actualisation complète des vues matérialisées

• Gestion des vues matérialisées

• Interrogation des données d'une vue matérialisée