Vues matérialisées

• Tables temporaires
• Sommaire
• Gestion des utilisateurs et configuration du serveur

Pour le dernier chapitre de cette partie, nous allons parler des vues matérialisées. Comme leur nom l'indique, les vues matérialisées sont des vues, dont les données sont matérialisées, c'est-à-dire stockées.

Les vues matérialisées sont des objets assez utiles, permettant un gain de performance relativement important lorsqu'ils sont bien utilisés. Malheureusement, MySQL n'implémente pas les vues matérialisées en tant que telles. Cependant, en utilisant les moyens du bord (c'est-à-dire les objets et concepts disponibles avec MySQL), il est tout à fait possible de construire des vues matérialisées soi-même.

Voyons donc quel est le principe de ces vues matérialisées, ce qu'on peut gagner à les utiliser, et comment les construire et les mettre à jour.

• Principe
• Mise à jour des vues matérialisées
• Gain de performance

Principe

Vues - rappels et performance

Une vue, c'est tout simplement une requête SELECT à laquelle on donne un nom. Lorsque l'on sélectionne des données à partir d'une vue, on exécute en réalité la requête SQL de la vue. Par conséquent, les vues ne permettent pas de gagner en performance.

En fait, dans certains cas, les requêtes sur des vues peuvent même être moins rapides que si l'on fait directement la requête sur la (ou les) table(s).

Les vues peuvent en effet utiliser deux algorithmes différents :

• MERGE : les clauses de la requête sur la vue (WHERE, ORDER BY,…) sont fusionnées à la requête définissant la vue ;
• TEMPTABLE : une table temporaire est créée avec les résultats de la requête définissant la vue, et la requête de sélection sur la vue est ensuite exécutée sur cette table temporaire.

Avec l'algorithme MERGE, tout se passe comme si l'on exécutait la requête directement sur les tables contenant les données. On perd un petit peu de temps à fusionner les clauses des deux requêtes, mais c'est négligeable.

Par contre, avec TEMPTABLE, non seulement on exécute deux requêtes (une pour créer la vue temporaire, l'autre sur cette dernière), mais en plus la table temporaire ne possède aucun index, contrairement aux tables normales. Une recherche dans la table temporaire peut donc prendre plus de temps que la même recherche sur une table normale, pour peu que cette dernière possède des index.

Enfin, n'oublions pas que MySQL nécessite l'utilisation de l'algorithme TEMPTABLE pour les vues contenant au moins un des éléments suivants :

• DISTINCT ;
• LIMIT ;
• une fonction d'agrégation (SUM(), COUNT(), MAX(), etc.) ;
• GROUP BY ;
• HAVING ;
• UNION ;
• une sous-requête dans la clause SELECT.

Vues matérialisées

Il fallait donc quelque chose qui combine les avantages des vues et les avantages des tables normales : c'est ainsi que le concept des vues matérialisées est né !

Une vue matérialisée est un objet qui permet de stocker le résultat d'une requête SELECT. Là où une vue se contente de stocker la requête, la vue matérialisée va stocker directement les résultats (elle va donc les matérialiser), plutôt que la requête. Lorsque l'on fait une requête sur une vue matérialisée, on va donc chercher directement des données dans celle-ci, sans passer par les tables d'origine et/ou une table temporaire intermédiaire.

"Vraies" vues matérialisées

Certains SGBD (Oracle par exemple) permettent de créer directement des vues matérialisées, avec des outils et des options dédiés. Il suffit alors de créer les vues matérialisées que l'on désire et de les paramétrer correctement, et tout se fait automatiquement.

Malheureusement, MySQL ne propose pas encore ce type d'objet. Il faut donc se débrouiller avec les outils disponibles.

"Fausses" vues matérialisées pour MySQL

On veut donc une structure qui permet de stocker des données, provenant d'une requête SELECT.

La réponse semble évidente : une table !

Pour créer une vue matérialisée avec MySQL, on utilise donc tout simplement une table, dans laquelle on stocke les résultats d'une requête SELECT.

Exemple : on veut matérialiser V_Revenus_annee_espece.

1
2
3
4
5
6
7

CREATE TABLE VM_Revenus_annee_espece
ENGINE = InnoDB
SELECT YEAR(date_reservation) AS annee, Espece.id AS espece_id, SUM(Adoption.prix) AS somme, COUNT(Adoption.animal_id) AS nb
FROM Adoption
INNER JOIN Animal ON Animal.id = Adoption.animal_id
INNER JOIN Espece ON Animal.espece_id = Espece.id
GROUP BY annee, Espece.id;

On a maintenant matérialisé la vue V_Revenus_annee_espece, avec VM_Revenus_annee_espece.

Non bien sûr. C'est bien beau d'avoir créé une table, mais il va falloir aussi tenir cette vue matérialisée à jour.

Mise à jour des vues matérialisées

On l'a dit, il ne suffit pas de créer une vue matérialisée à partir des données, il faut aussi la tenir à jour lorsque les données des tables d'origine changent.

Deux possibilités :

• une mise à jour sur demande ;
• une mise à jour automatique chaque fois qu'un changement est fait.

Mise à jour sur demande

Si l'on veut pouvoir mettre la vue matérialisée à jour ponctuellement, on peut utiliser une procédure stockée. Le plus simple sera de supprimer les données de la vue matérialisée, puis d'insérer les données à jour grâce à la même requête de sélection ayant servi à créer/initialiser la vue matérialisée.

Exemple : procédure stockée permettant de mettre à jour la vue VM_Revenus_annee_especes.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

DELIMITER |
CREATE PROCEDURE maj_vm_revenus()
BEGIN
    TRUNCATE VM_Revenus_annee_espece;

    INSERT INTO VM_Revenus_annee_espece
    SELECT YEAR(date_reservation) AS annee, Espece.id AS espece_id, SUM(Adoption.prix) AS somme, COUNT(Adoption.animal_id) AS nb
    FROM Adoption
    INNER JOIN Animal ON Animal.id = Adoption.animal_id
    INNER JOIN Espece ON Animal.espece_id = Espece.id
    GROUP BY annee, Espece.id;
END |
DELIMITER ;

La commande TRUNCATE nom_table; a le même effet que DELETE FROM nom_table; (sans clause WHERE) ; elle supprime toutes les lignes de la table. Cependant, TRUNCATE est un peu différent de DELETE FROM.

• TRUNCATE ne supprime pas les lignes une à une : TRUNCATE supprime la table, puis la recrée (sans les données).
• Par conséquent, TRUNCATE ne traite pas les clés étrangères : on ne peut pas faire un TRUNCATE sur une table dont une colonne est référencée par une clé étrangère, sauf si celle-ci est dans la même table ; en outre les options ON DELETE ne sont pas traitées lorsque des données sont supprimées avec cette commande.
• TRUNCATE valide implicitement les transactions, et ne peut pas être annulé par un rollback.
• Pour toutes ces raisons, TRUNCATE est beaucoup plus rapide que DELETE FROM (en particulier si le nombre de ligne à supprimer est important).

Dans le cas de VM_Revenus_annee_espece, on peut sans problème utiliser TRUNCATE.

Pour mettre à jour la vue matérialisée, il suffit donc d'exécuter la procédure stockée.

1

CALL maj_vm_revenus();

Ce type de mise à jour est généralement utilisé pour des données qu'il n'est pas impératif d'avoir à jour en permanence. La plupart du temps, la procédure est appelée par un script qui tourne périodiquement (une fois par jour ou une fois par semaine par exemple).

Mise à jour automatique

Si à l'inverse, on veut que les données soient toujours à jour par rapport aux derniers changements de la base de données, on utilisera plutôt les triggers pour mettre à jour la vue matérialisée.

Dans le cas de VM_Revenus_annee_espece, la vue matérialisée doit être mise à jour en cas de modification de la table Adoption.

• Une insertion provoquera la mise à jour de la ligne correspondant à l'année et à l'espèce de l'adoption insérée (majoration du total des prix et du nombre d'adoptions), ou insérera une nouvelle ligne si elle n'existe pas encore.
• Une suppression provoquera la mise à jour de la ligne correspondant à l'année et à l'espèce de l'adoption supprimée, ou la suppression de celle-ci s'il s'agissait de la seule adoption correspondante.
• Une modification sera un mix de la suppression et de l'insertion.

En ce qui concerne la colonne espece_id de la vue matérialisée, il vaut mieux lui ajouter une clé étrangère, avec l'option ON DELETE CASCADE. En principe, Espece.id ne devrait jamais être modifiée, mais en mettant cette clé étrangère, on s'assure que la correspondance entre les tables existera toujours.

On va également ajouter une clé primaire : (annee, espece_id), afin de simplifier nos triggers. Ça permettra d'utiliser la commande INSERT INTO ... ON DUPLICATE KEY UPDATE.

1
2
3

ALTER TABLE VM_Revenus_annee_espece
    ADD CONSTRAINT fk_vm_revenu_espece_id FOREIGN KEY (espece_id) REFERENCES Espece (id) ON DELETE CASCADE,
    ADD PRIMARY KEY (annee, espece_id);

Et voici donc les triggers nécessaires à la mise à jour de la vue matérialisée :

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64

DELIMITER |

DROP TRIGGER after_insert_adoption |
CREATE TRIGGER after_insert_adoption AFTER INSERT
ON Adoption FOR EACH ROW
BEGIN
    UPDATE Animal
    SET disponible = FALSE
    WHERE id = NEW.animal_id;

    INSERT INTO VM_Revenus_annee_espece (espece_id, annee, somme, nb)
    SELECT espece_id, YEAR(NEW.date_reservation), NEW.prix, 1
    FROM Animal
    WHERE id = NEW.animal_id
    ON DUPLICATE KEY UPDATE somme = somme + NEW.prix, nb = nb + 1;
END |

DROP TRIGGER after_update_adoption |
CREATE TRIGGER after_update_adoption AFTER UPDATE
ON Adoption FOR EACH ROW
BEGIN
    IF OLD.animal_id <> NEW.animal_id THEN
        UPDATE Animal
        SET disponible = TRUE
        WHERE id = OLD.animal_id;

        UPDATE Animal
        SET disponible = FALSE
        WHERE id = NEW.animal_id;
    END IF;

    INSERT INTO VM_Revenus_annee_espece (espece_id, annee, somme, nb)
    SELECT espece_id, YEAR(NEW.date_reservation), NEW.prix, 1
    FROM Animal
    WHERE id = NEW.animal_id
    ON DUPLICATE KEY UPDATE somme = somme + NEW.prix, nb = nb + 1;

    UPDATE VM_Revenus_annee_espece
    SET somme = somme - OLD.prix, nb = nb - 1
    WHERE annee = YEAR(OLD.date_reservation)
    AND espece_id = (SELECT espece_id FROM Animal WHERE id = OLD.animal_id);

    DELETE FROM VM_Revenus_annee_espece
    WHERE nb = 0;
END |

DROP TRIGGER after_delete_adoption |
CREATE TRIGGER after_delete_adoption AFTER DELETE
ON Adoption FOR EACH ROW
BEGIN
    UPDATE Animal
    SET disponible = TRUE
    WHERE id = OLD.animal_id;

    UPDATE VM_Revenus_annee_espece
    SET somme = somme - OLD.prix, nb = nb - 1
    WHERE annee = YEAR(OLD.date_reservation)
    AND espece_id = (SELECT espece_id FROM Animal WHERE id = OLD.animal_id);

    DELETE FROM VM_Revenus_annee_espece
    WHERE nb = 0;
END |

DELIMITER ;

Gain de performance

Tables vs vue vs vue matérialisée

On va tester les performances des vues et des vues matérialisées en créant trois procédures stockées différentes, répondant à la même question : quelle est l'année ayant rapporté le plus en termes d'adoption de chats ? Les trois procédures utiliseront des objets différents :

• l'une fera la requête directement sur les tables ;
• l'autre fera la requête sur la vue ;
• la dernière utilisera la vue matérialisée.

Nos tables contenant très peu de données, la requête sera répétée un millier de fois, afin que les temps d'exécution soient utilisables.

Empêcher MySQL d'utiliser le cache

Lorsqu'on exécute la même requête plusieurs fois de suite, MySQL ne l'exécute en réalité qu'une seule fois, et stocke le résultat en cache. Toutes les fois suivantes, MySQL ressort simplement ce qu'il a en cache, ce qui ne prend quasiment rien comme temps. Pour faire des tests de performance, cela pose évidemment problème, puisque cela fausse le temps d'exécution. Heureusement, il existe une clause spéciale, qui permet d'empêcher MySQL de stocker le résultat de la requête en cache : SQL_NO_CACHE.

Les trois procédures

Sur les tables

On utilise SELECT... INTO afin de stocker les résultats dans des variables, plutôt que de les afficher mille fois.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

DELIMITER |
CREATE PROCEDURE test_perf_table()
BEGIN
    DECLARE v_max INT DEFAULT 1000;                     
    DECLARE v_i INT DEFAULT 0;
    DECLARE v_nb INT;
    DECLARE v_somme DECIMAL(15,2);
    DECLARE v_annee CHAR(4);

    boucle: LOOP
        IF v_i = v_max THEN LEAVE boucle; END IF;         -- Condition d'arrêt de la boucle

        SELECT SQL_NO_CACHE YEAR(date_reservation) AS annee, 
                            SUM(Adoption.prix) AS somme,
                            COUNT(Adoption.animal_id) AS nb
               INTO v_annee, v_somme, v_nb
        FROM Adoption
        INNER JOIN Animal ON Animal.id = Adoption.animal_id
        INNER JOIN Espece ON Animal.espece_id = Espece.id
        WHERE Espece.id = 2
        GROUP BY annee
        ORDER BY somme DESC
        LIMIT 1;

        SET v_i = v_i + 1;
    END LOOP;

END |
DELIMITER ;

Sur la vue

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

DELIMITER |
CREATE PROCEDURE test_perf_vue()
BEGIN
    DECLARE v_max INT DEFAULT 1000;                   
    DECLARE v_i INT DEFAULT 0;
    DECLARE v_nb INT;
    DECLARE v_somme DECIMAL(15,2);
    DECLARE v_annee CHAR(4);

    boucle: LOOP
        IF v_i = v_max THEN LEAVE boucle; END IF;    

        SELECT SQL_NO_CACHE annee, somme, nb         
               INTO v_annee, v_somme, v_nb                                          
        FROM V_Revenus_annee_espece
        WHERE espece_id = 2
        ORDER BY somme DESC
        LIMIT 1;

        SET v_i = v_i + 1;
    END LOOP;

END |
DELIMITER ;

Sur la vue matérialisée

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

DELIMITER |
CREATE PROCEDURE test_perf_vm()
BEGIN
    DECLARE v_max INT DEFAULT 1000;                   
    DECLARE v_i INT DEFAULT 0;
    DECLARE v_nb INT;
    DECLARE v_somme DECIMAL(15,2);
    DECLARE v_annee CHAR(4);

    boucle: LOOP
        IF v_i = v_max THEN LEAVE boucle; END IF;    

        SELECT SQL_NO_CACHE annee, somme, nb         
               INTO v_annee, v_somme, v_nb                                          
        FROM VM_Revenus_annee_espece
        WHERE espece_id = 2
        ORDER BY somme DESC
        LIMIT 1;

        SET v_i = v_i + 1;
    END LOOP;

END |
DELIMITER ;

Le test

Il n'y a plus maintenant qu'à exécuter les trois procédures pour voir leur temps d'exécution.

1
2
3

CALL test_perf_table();
CALL test_perf_vue();
CALL test_perf_vm();

1
2
3
4
5
6
7
8

mysql> CALL test_perf_table();
Query OK, 1 row affected (0.27 sec)

mysql> CALL test_perf_vue();
Query OK, 1 row affected (0.29 sec)

mysql> CALL test_perf_vm();
Query OK, 1 row affected (0.06 sec)

Le temps d'exécution semble équivalent pour la requête faite sur les tables directement, et sur la vue. Par contre, on a un gros gain de performance avec la requête sur la vue matérialisée.

Voici les moyennes et écarts-types des temps d'exécution des trois procédures, pour 20 exécutions :

La requête sur la vue est donc bien légèrement plus lente que la requête faite directement sur les tables. Mais le résultat le plus intéressant est bien celui obtenu avec la vue matérialisée : 5 fois plus rapide que la requête sur la vue !

Par ailleurs, on peut maintenant ajouter des clés et des index à VM_Revenus_annee_especes pour accélérer encore les requêtes. Ici, le nombre réduit de lignes fait que l'effet ne sera pas perceptible, mais sur des tables plus fournies, ça vaut la peine !

1

ALTER TABLE VM_Revenus_annee_espece ADD INDEX (somme);

Conclusion

Nos tables contiennent très peu de données, par rapport à la réalité. Les applications moyennes classiques exploitent des bases de données dont les tables peuvent compter des centaines de milliers, voire des millions de lignes. Le gain de performance permis par les vues matérialisées, et les index, sera encore plus visible sur de telles bases.

Cependant, toutes les vues ne sont pas intéressantes à matérialiser. De manière générale, une vue contenant des fonctions d'agrégation, des regroupements, et qui donc devra utiliser l'algorithme TEMPTABLE, est une bonne candidate.

Mais il faut également prendre en compte le coût de la mise à jour de la vue matérialisée. S'il s'agit d'une vue qui doit être constamment à jour, et qui utiliserait donc des triggers, il ne sera sans doute pas intéressant de la matérialiser si les données sur laquelle elle s'appuie sont souvent modifiées, car le gain de performance pour les requêtes de sélection sur la vue ne compenserait pas le coût des mises à jour de celle-ci.

Par ailleurs, certaines vues ne nécessitent pas qu'on leur consacre une table entière. Ainsi, la vue V_Nombre_espece peut être matérialisée simplement en ajoutant une colonne à la table Espece. Cette colonne peut alors être tenue à jour avec des triggers sur Animal (comme on le fait avec la colonne Animal.disponible).

Comme souvent, il faut donc bien choisir au cas par cas, peser le pour et le contre et réfléchir à toutes les implications quant au choix des vues à matérialiser.

En résumé

• Une vue matérialisée est un objet qui stocke le résultat d'une requête SELECT, c'est donc la matérialisation d'une vue.
• MySQL n'implémente pas les vues matérialisées, contrairement à certains SGBD.
• On utilise de simples tables comme vues matérialisées, que l'on met à jour grâce à une procédure stockée, ou à des triggers.
• Les vues matérialisées peuvent permettre de substantiels gains de performance.

• Tables temporaires
• Sommaire
• Gestion des utilisateurs et configuration du serveur