Leçon 9.6 · Temps de lecture : ~9 min

Une vue matérialisée est une vue dont le résultat est stocké sur disque comme une table : la requête s'exécute une fois, et les lectures renvoient les lignes enregistrées au lieu de les recalculer. Les rapports lourds deviennent rapides, au prix de données qui ne sont à jour qu'au moment du dernier rafraîchissement. Dans cette leçon, vous allez créer, interroger et rafraîchir des vues matérialisées dans PostgreSQL, voir quelles bases de données les prennent en charge et construire l'équivalent dans MySQL avec une table de synthèse.

Vues matérialisées en SQL

Dans la leçon précédente, vous avez créé des vues : une vue ne stocke que la requête, et chaque lecture l'exécute à nouveau. C'est pratique, mais un rapport qui joint cinq tables et agrège des milliers de lignes refait tout ce travail à chaque appel.

Une vue matérialisée résout ce problème en enregistrant le résultat. Les exemples utilisent le schéma Sakila avec la syntaxe de PostgreSQL ; le comportement présenté a été vérifié sur PostgreSQL 17, et la partie MySQL sur MySQL 8.0.

Qu'est-ce qu'une vue matérialisée ?

Une vue matérialisée est une requête nommée accompagnée d'une copie enregistrée de son résultat. Les lectures se font dans la copie, elles sont donc aussi rapides que la lecture d'une petite table. La copie ne se met pas à jour toute seule : quand les tables sources changent, la vue matérialisée garde les anciennes données jusqu'à ce que vous la rafraîchissiez.

Créer une vue matérialisée

La syntaxe est la même que pour une vue, avec le mot MATERIALIZED :

CREATE MATERIALIZED VIEW category_revenue AS
SELECT c.name AS category, COUNT(*) AS rentals, SUM(p.amount) AS revenue
FROM payment AS p
JOIN rental AS r ON r.rental_id = p.rental_id
JOIN inventory AS i ON i.inventory_id = r.inventory_id
JOIN film_category AS fc ON fc.film_id = i.film_id
JOIN category AS c ON c.category_id = fc.category_id
GROUP BY c.name;

Résultat : PostgreSQL exécute la requête une fois et enregistre 16 lignes, une par catégorie.

On l'interroge ensuite comme une table :

SELECT category, rentals, revenue
FROM category_revenue
ORDER BY revenue DESC
LIMIT 5;

Résultat : Sports (1179 locations, 5314.21), Sci-Fi (4756.98), Animation (4656.30), Drama (4587.39), Comedy (4383.58). Les cinq jointures ne sont pas réexécutées : les lignes viennent du résultat enregistré.

Pour créer l'objet sans exécuter la requête, ajoutez WITH NO DATA. Une telle vue ne peut pas être lue avant son premier rafraîchissement : un SELECT échoue avec « materialized view has not been populated ».

Une vue matérialisée est en lecture seule. INSERT INTO category_revenue … échoue avec « cannot change materialized view ». Ses données ne changent que lors d'un rafraîchissement.

Rafraîchir les données

Quand de nouveaux paiements arrivent, la vue matérialisée affiche toujours les anciens totaux. Pour la recalculer :

REFRESH MATERIALIZED VIEW category_revenue;

Résultat : la requête s'exécute à nouveau et remplace les lignes enregistrées. Avant cette commande, un nouveau paiement n'apparaît pas du tout dans la vue.

Un REFRESH simple verrouille la vue matérialisée : les requêtes qui la lisent attendent la fin du rafraîchissement. Pour une vue lue en permanence, utilisez CONCURRENTLY. Le nouveau résultat est alors construit à côté de l'ancien et seules les lignes modifiées sont remplacées, donc les lectures ne sont pas bloquées :

CREATE UNIQUE INDEX category_revenue_category_idx ON category_revenue (category);

REFRESH MATERIALIZED VIEW CONCURRENTLY category_revenue;

CONCURRENTLY exige un index unique sur la vue matérialisée ; sans lui, la commande échoue avec « cannot refresh materialized view concurrently ». Il est aussi plus lent qu'un rafraîchissement simple : réservez-le aux cas où le blocage des lectures pose problème.

La fréquence de rafraîchissement dépend de la tâche : une fois par nuit pour un rapport quotidien, toutes les quelques minutes pour un tableau de bord. Le rafraîchissement est lancé par un planificateur, par exemple cron ou l'extension pg_cron.

Index et suppression

Une vue matérialisée est stockée comme une table : on peut donc lui ajouter des index ordinaires, ce qui est impossible avec une vue classique. Les index sont étudiés en détail dans le module 10.

Pour la supprimer :

DROP MATERIALIZED VIEW category_revenue;

La liste des vues matérialisées de PostgreSQL se trouve dans pg_matviews ; la colonne ispopulated indique si une vue a été remplie.

Prise en charge selon les bases de données

Dans MySQL, CREATE MATERIALIZED VIEW est une erreur de syntaxe.

Une vue matérialisée dans MySQL : la table de synthèse

Dans MySQL, on stocke le même résultat dans une table ordinaire que l'on reconstruit soi-même :

CREATE TABLE category_revenue AS
SELECT c.name AS category, COUNT(*) AS rentals, SUM(p.amount) AS revenue
FROM payment AS p
JOIN rental AS r ON r.rental_id = p.rental_id
JOIN inventory AS i ON i.inventory_id = r.inventory_id
JOIN film_category AS fc ON fc.film_id = i.film_id
JOIN category AS c ON c.category_id = fc.category_id
GROUP BY c.name;

Pour la rafraîchir sans laisser les lecteurs face à une table vide, remplissez une nouvelle copie et échangez les deux tables en une seule instruction :

CREATE TABLE category_revenue_new LIKE category_revenue;

INSERT INTO category_revenue_new
SELECT c.name, COUNT(*), SUM(p.amount)
FROM payment AS p
JOIN rental AS r ON r.rental_id = p.rental_id
JOIN inventory AS i ON i.inventory_id = r.inventory_id
JOIN film_category AS fc ON fc.film_id = i.film_id
JOIN category AS c ON c.category_id = fc.category_id
GROUP BY c.name;

RENAME TABLE category_revenue TO category_revenue_old,
             category_revenue_new TO category_revenue;

DROP TABLE category_revenue_old;

Résultat : RENAME TABLE échange les deux tables d'un coup, donc les requêtes voient soit les anciennes, soit les nouvelles données, jamais une table vide.

Pour une petite table, DELETE suivi de INSERT … SELECT dans une même transaction convient aussi. N'utilisez pas TRUNCATE ici : dans MySQL, il valide implicitement la transaction, et les lecteurs verraient la table vide (voir la leçon 8.4).

Le rafraîchissement se planifie avec le planificateur d'événements de MySQL (CREATE EVENT … ON SCHEDULE EVERY 1 HOUR) ou une tâche cron externe.

Quand utiliser une vue matérialisée

Bons cas d'usage :

• les rapports et tableaux de bord lourds qui joignent de nombreuses tables et agrègent beaucoup de lignes ;
• les données qui peuvent avoir quelques minutes ou heures de retard : ventes du jour, statistiques mensuelles, classements ;
• les résultats lus par beaucoup d'utilisateurs et rarement modifiés.

Mauvais cas d'usage :

• les données qui doivent toujours être à jour, comme un solde ou un stock disponible ;
• des tables sources qui changent en permanence : la vue devrait être rafraîchie sans arrêt ;
• les requêtes simples et rapides : une vue classique ou la requête elle-même suffit.

Points clés :

• Une vue matérialisée stocke le résultat de sa requête ; les lectures renvoient les lignes enregistrées sans exécuter la requête.
• Les données datent du dernier REFRESH MATERIALIZED VIEW ; dans PostgreSQL, rien n'est mis à jour automatiquement.
• REFRESH … CONCURRENTLY ne bloque pas les lectures mais exige un index unique sur la vue matérialisée.
• Une vue matérialisée est en lecture seule et peut avoir ses propres index.
• MySQL et MariaDB n'ont pas de vues matérialisées : utilisez une table de synthèse et échangez-la avec RENAME TABLE.
• Utilisez-les pour des rapports lourds qui tolèrent des données légèrement en retard, pas pour des valeurs qui doivent être à jour.

Questions fréquentes

Quelle est la différence entre une vue et une vue matérialisée ?

Une vue ne stocke que la requête et l'exécute à chaque lecture, ses données sont donc toujours à jour. Une vue matérialisée stocke le résultat de la requête : les lectures sont rapides, mais les données datent du dernier rafraîchissement.

Une vue matérialisée se met-elle à jour automatiquement ?

Pas dans PostgreSQL : vous lancez REFRESH MATERIALIZED VIEW vous-même ou de façon planifiée. Oracle peut rafraîchir à chaque commit, et les vues indexées de SQL Server sont maintenues automatiquement à chaque modification.

MySQL prend-il en charge les vues matérialisées ?

Non. MySQL et MariaDB n'ont que des vues classiques. Le remplacement habituel est une table de synthèse remplie avec INSERT … SELECT et reconstruite de façon planifiée.

Peut-on ajouter un index à une vue matérialisée ?

Oui. Une vue matérialisée est stockée comme une table, on peut donc l'indexer, et REFRESH … CONCURRENTLY exige un index unique.

Questions d'entretien

Quand utiliseriez-vous une vue matérialisée plutôt qu'une vue classique ?

Quand la requête est coûteuse, que son résultat est lu souvent et que les données peuvent être un peu en retard : rapports, tableaux de bord, statistiques agrégées. Une vue classique est préférable quand les données doivent toujours être à jour ou que la requête est peu coûteuse.

Que fait REFRESH MATERIALIZED VIEW CONCURRENTLY et que nécessite-t-il ?

Il recalcule le résultat sans bloquer les lecteurs : le nouveau résultat est construit à côté de l'ancien et seules les différences sont appliquées. Il exige un index unique sur la vue matérialisée et est plus lent qu'un rafraîchissement simple.

Comment implémenter une vue matérialisée dans MySQL ?

Créer une table de synthèse avec CREATE TABLE … AS SELECT. Pour la rafraîchir, remplir une nouvelle copie et échanger les tables avec RENAME TABLE, et lancer le rafraîchissement avec le planificateur d'événements ou cron.