Triggers et vues matérialisées (MySQL)

Formation

En Ligne

Prix sur demande

Appeler le centre

Avez-vous besoin d'un coach de formation?

Il vous aidera à comparer différents cours et à trouver la solution la plus abordable.

Description

  • Typologie

    Formation

  • Méthodologie

    En ligne

Grâce à cette formation vous pourrez acquérir les connaissances nécessaires qui vous permettrons d’ajouter des compétences à votre profil et obtenir de solides aptitude qui vous offriront de nombreuses opportunités professionnelles.

Questions / Réponses

Ajoutez votre question

Nos conseillers et autres utilisateurs pourront vous répondre

À qui souhaitez-vous addresser votre question?

Saisissez vos coordonnées pour recevoir une réponse

Nous ne publierons que votre nom et votre question

Les Avis

Les matières

  • MySQL

Le programme

Introduction du cours

Ce tutoriel a pour but de vous familiariser avec les différents concepts suivants :

  • les triggers ;

  • les vues ;

  • le maintien à jour de tables via des triggers ;

  • l'utilisation de ces tables comme un cache pour accélérer grandement certaines requêtes.

Nous utiliserons MySQL 5 ; bien sûr, tout cela fonctionne aussi sous PostgreSQL à condition de changer un peu la syntaxe des exemples.

Prérequis

  • Jointures.

  • Relations.

  • Transactions et différences MyISAM/InnoDB.

  • Savoir ce qu'est une procédure stockée.

Un exemple simple

Supposons que nous ayons une équipe de vendeurs, que nous rentrons dans la base de données sous la forme suivante :

CREATE TABLE vendeurs ( vd_id INTEGER PRIMARY KEY AUTO_INCREMENT, vd_name TEXT NOT NULL ) ENGINE=InnoDB;

Le patron veut que chaque vendeur entre dans la base de données, chaque jour, le total de ses recettes. Nous créons donc une table pour stocker ces informations :

CREATE TABLE recettes_vendeurs ( vd_id INTEGER NOT NULL REFERENCES vendeurs( vd_id ) ON DELETE RESTRICT, rc_date DATE NOT NULL, rc_montant NUMERIC( 12, 2 ), PRIMARY KEY( vd_id, rc_date ), KEY( rc_date, vd_id ) ) ENGINE=InnoDB;

Quelques explications s'imposent.

Nous créons une relation entre la table « recettes_vendeurs » et la table « vendeurs » par « vd_id REFERENCES vendeurs( vd_id ) ». Chaque ligne de « recettes_vendeurs » doit donc contenir l'id d'un vendeur existant. Comme nous n'avons par défini de contrainte d'unicité sur « recettes_vendeurs.vd_id », chaque vendeur peut avoir plusieurs lignes lui faisant référence dans « recettes_vendeurs ».

« ON DELETE RESTRICT » empêche la suppression d'un vendeur si des lignes de « recettes_vendeurs » lui font référence. En effet ici, on souhaite garder l'historique des recettes en comptabilité. Pour gérer le turnover, on ajoutera par exemple dans la table « vendeurs » des colonnes permettant de savoir si le vendeur est encore employé par la société, sa date d'embauche et éventuellement de départ, etc.

Pour d'autres applications où l'on souhaite supprimer automatiquement les lignes faisant référence au parent lorsque celui-ci est supprimé, on utilisera « ON DELETE CASCADE ».

Pour plus d'explications sur les relations : voir ce tutoriel.

La clé primaire naturelle de la table est (vd_id, rc_date) : elle est unique, une ligne par date et par vendeur. On aurait tout aussi bien pu utiliser (rc_date, vd_id). Il n'est pas nécessaire de créer une autre clé primaire. On crée aussi un index sur la date.

Pour la suite, nous allons remplir les tables avec ce petit script PHP très simple :

<?php // On place la connexion à MySQL dans un include pour éviter de // diffuser par inadvertance des mots de passe sur le Net // par copier-coller, ou dans Subversion/Git... require_once "dbconn.php"; // Si les triggers sont actifs lors du remplissage de la table et que le // but est de les tester, mieux vaut réduire ces valeurs. $max_vendeurs = 100; $max_jours = 5000; function query( $sql ) { $t = microtime( true ); $q = mysql_query( $sql ); if( $q ) { printf( "<p>%.02f ms : %s</p>", (microtime(true) - $t)*1000, $sql ); return $q; } echo "<pre>"; var_dump( debug_backtrace() ); echo "\n"; exit( mysql_error() ); } query( "BEGIN" ); query( "TRUNCATE recettes_vendeurs" ); query( "TRUNCATE vendeurs" ); query( "TRUNCATE recettes_jour_mat" ); query( "TRUNCATE recettes_mois_mat" ); query( "TRUNCATE recettes_vendeur_mois_mat" ); $values = array(); for( $vendeur_id=1; $vendeur_id<=$max_vendeurs; $vendeur_id++ ) $values[] = "($vendeur_id,'vendeur $vendeur_id')"; query( "INSERT INTO vendeurs (vd_id, vd_name) VALUES ".implode(',',$values) ); for( $jour=0; $jour<$max_jours; $jour++ ) query( "INSERT INTO recettes_vendeurs (vd_id, rc_date, rc_montant) SELECT vd_id, DATE_SUB( now(), INTERVAL $jour DAY ), pow(rand(),4) * 10000 FROM vendeurs" ); query( "COMMIT" );

Cela nous crée une base de données de test avec 500 000 lignes, ce qui est relativement petit, mais permettra de voir rapidement si une requête est optimisée ou non.

Création de vues (VIEW)

Nous voulons récupérer les informations suivantes :
1) total des recettes par jour ;
2) total des recettes par vendeur et par mois ;
2) total des recettes par mois.

Pour simplifier nos autres requêtes, nous allons utiliser des vues représentant ces trois requêtes.
Une vue (VIEW) est une requête SQL stockée sur le serveur que l'on peut appeler par son nom, et utiliser exactement comme une table.
Cela permet de stocker une tartine de SQL et de l'utiliser simplement.

CREATE VIEW recettes_jour AS SELECT rc_date, sum( rc_montant ) AS rc_montant FROM recettes_vendeurs GROUP BY rc_date; CREATE VIEW recettes_mois AS SELECT YEAR( rc_date ) AS rc_year, MONTH( rc_date ) AS rc_month, sum( rc_montant ) AS rc_montant FROM recettes_vendeurs GROUP BY rc_year, rc_month; CREATE VIEW recettes_vendeur_mois AS SELECT YEAR( rc_date ) AS rc_year, MONTH( rc_date ) AS rc_month, vd_id, sum( rc_montant ) AS rc_montant FROM recettes_vendeurs GROUP BY rc_year, rc_month, vd_id;

Une vue se comporte exactement comme une table. Par exemple, on peut la faire apparaître dans un SELECT :

SELECT * FROM recettes_vendeur_mois WHERE rc_year=2009 AND vd_id BETWEEN 1 AND 2; +---------+----------+-------+------------+ | rc_year | rc_month | vd_id | rc_montant | +---------+----------+-------+------------+ | 2009 | 1 | 1 | 56534.90 | | 2009 | 1 | 2 | 60471.68 | | 2009 | 2 | 1 | 74446.72 | | 2009 | 2 | 2 | 53958.03 | | 2009 | 3 | 1 | 46140.94 | | 2009 | 3 | 2 | 81320.11 | | 2009 | 4 | 1 | 34460.82 | | 2009 | 4 | 2 | 55439.18 | | 2009 | 5 | 1 | 52062.94 | | 2009 | 5 | 2 | 70186.89 | | 2009 | 6 | 1 | 79167.22 | | 2009 | 6 | 2 | 71725.05 | | 2009 | 7 | 1 | 75596.33 | | 2009 | 7 | 2 | 68066.56 | | 2009 | 8 | 1 | 49466.99 | | 2009 | 8 | 2 | 71283.26 | | 2009 | 9 | 1 | 41135.17 | | 2009 | 9 | 2 | 89311.28 | | 2009 | 10 | 1 | 63664.04 | | 2009 | 10 | 2 | 69149.33 | | 2009 | 11 | 1 | 48181.47 | | 2009 | 11 | 2 | 67718.38 | | 2009 | 12 | 1 | 75128.19 | | 2009 | 12 | 2 | 37845.85 | +---------+----------+-------+------------+

Quels ont été nos meilleurs vendeurs, sur un mois, en 2009 ?

SELECT * FROM recettes_vendeur_mois WHERE rc_year=2009 ORDER BY rc_montant DESC LIMIT 5; +---------+----------+-------+------------+ | rc_year | rc_month | vd_id | rc_montant | +---------+----------+-------+------------+ | 2009 | 7 | 39 | 110352.47 | | 2009 | 4 | 31 | 109560.18 | | 2009 | 7 | 56 | 106520.38 | | 2009 | 12 | 87 | 103366.81 | | 2009 | 7 | 66 | 100009.22 | +---------+----------+-------+------------+

Pas besoin de réécrire les clauses GROUP BY, etc., on utilise simplement la VIEW.

Faisons maintenant un test de performances. Pour mesurer de manière fiable la durée d'une requête avec MySQL, il faut l'exécuter par exemple en PHP, et mesurer le temps passé avec microtime() . Ou, plus simplement, exécuter la requête dans phpMyAdmin.

Cependant, si la même requête est exécutée plusieurs fois de suite, ce qui risque de se produire ici, MySQL va garder le résultat en cache et le chronométrage sera biaisé : à partir de la deuxième exécution, la requête semblera très rapide, en effet elle n'est pas traitée du tout.

Le mot-clé SQL_NO_CACHE, spécifique à MySQL, désactive le cache pour la requête qui le contient. Il doit être placé juste après SELECT. Il faut toujours l'utiliser pour chronométrer une requête.

Nous apercevons ici une limitation de MySQL, particulièrement sur la requête suivante :

SELECT SQL_NO_CACHE * FROM recettes_vendeur_mois WHERE rc_year=2009 AND vd_id BETWEEN 1 AND 2;

qui correspond en fait à :

SELECT SQL_NO_CACHE YEAR( rc_date ) AS rc_year, MONTH( rc_date ) AS rc_month, vd_id, sum( rc_montant ) AS rc_montant FROM recettes_vendeurs WHERE YEAR( rc_date )=2009 AND vd_id BETWEEN 1 AND 2 GROUP BY rc_year, rc_month, vd_id;

En écrivant la requête complète, l'index sur la table « recettes_vendeurs » est utilisé et la requête prend moins de 5 ms.
En utilisant la VIEW, l'index n'est pas utilisé, et par conséquent la requête prend plus de 0.58 seconde, ce qui est énorme.

Une base de données plus évoluée, comme PostgreSQL, est bien sûr capable de transformer la première requête sous la forme de la deuxième, pour utiliser tous les index possibles. Ce n'est pas toujours le cas de MySQL. Par conséquent, les VIEW sont d'une utilité limitée sous MySQL.

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

Les vues sont des outils très pratiques, mais leur utilisation peut causer certains problèmes de performances. En effet, on ne peut pas créer d'index sur une VIEW. Par conséquent, sur cette requête :

SELECT * FROM recettes_vendeur_mois WHERE rc_year=2009 ORDER BY rc_montant DESC LIMIT 5;

l'agrégat sum() doit être calculé pour toutes les lignes de l'année 2009, puis les résultats doivent être triés.

Souvent, nous aimerions avoir accès directement aux résultats du calcul, sans devoir effectuer ce calcul. Par exemple, dans un forum, il faut stocker dans la table « topics » ces informations :

  • nombre de posts dans le topic ;

  • date du dernier post dans le topic.

Cela nous permet de poser un index sur la date du dernier post, par exemple.

Reprenons notre exemple de comptabilité. Ici, nous n'avons pas de tables existantes (comme la table « topics ») où stocker les résultats de nos calculs ; nous allons donc en créer.

Nous allons matérialiser les vues créées plus haut :

CREATE TABLE recettes_jour_mat ( rc_date DATE NOT NULL, rc_montant NUMERIC( 12, 2 ), PRIMARY KEY ( rc_date ) ) ENGINE=InnoDB; CREATE TABLE recettes_mois_mat ( rc_year INTEGER NOT NULL, rc_month INTEGER NOT NULL, rc_montant NUMERIC( 12, 2 ), PRIMARY KEY( rc_year, rc_month ) ) ENGINE=InnoDB; CREATE TABLE recettes_vendeur_mois_mat ( rc_year INTEGER NOT NULL, rc_month INTEGER NOT NULL, vd_id INTEGER NOT NULL REFERENCES vendeurs( vd_id ) ON DELETE RESTRICT, rc_montant NUMERIC( 12, 2 ), PRIMARY KEY( rc_year, rc_month, vd_id ), KEY( vd_id ) ) ENGINE=InnoDB;

Après avoir créé ces tables, il va falloir les remplir, puis les tenir à jour en fonction des modifications de la table « recettes_vendeurs ».

Ces opérations peuvent se faire dans l'application, mais cela présente de nombreux inconvénients :

  • il faut penser à faire les requêtes de mise à jour à chaque fois qu'on touche à la table « recettes » ;

  • l'ajout d'une autre vue matérialisée oblige à ajouter des requêtes de mise à jour un peu partout dans l'application ;

  • les performances ne sont pas les meilleures possibles.

La solution correcte pour ce genre de problème est de laisser la base de données s'en occuper, en créant un TRIGGER.

Qu'est-ce qu'un trigger ?

Citation : Documentation PostgreSQL

Un déclencheur (TRIGGER) spécifie que la base de données doit exécuter automatiquement une fonction donnée chaque fois qu'un certain type d'opération est exécuté. Les fonctions déclencheurs peuvent être définies pour s'exécuter avant ou après une commande INSERT, UPDATE ou DELETE, soit une fois par ligne modifiée, soit une fois par expression SQL.

Nous voulons maintenir à jour nos vues matérialisées en fonction des modifications sur la table « recettes_vendeurs ». Par conséquent, nous allons créer trois triggers sur cette table.

La documentation complète se trouve ici.

La commande « delimiter » permet au client MySQL en ligne de commande de ne pas couper les lignes sur les « ; » qui sont dans le code des triggers. Si les commandes sont collées dans un phpMyAdmin, il faut l'inclure.

delimiter // CREATE TRIGGER recettes_vendeurs_insert_t BEFORE INSERT ON recettes_vendeurs FOR EACH ROW BEGIN INSERT INTO recettes_jour_mat (rc_date, rc_montant) VALUES (NEW.rc_date, NEW.rc_montant) ON DUPLICATE KEY UPDATE rc_montant = rc_montant + NEW.rc_montant; INSERT INTO recettes_mois_mat (rc_year, rc_month, rc_montant) VALUES (YEAR( NEW.rc_date ), MONTH( NEW.rc_date ), NEW.rc_montant) ON DUPLICATE KEY UPDATE rc_montant = rc_montant + NEW.rc_montant; INSERT INTO recettes_vendeur_mois_mat (rc_year, rc_month, vd_id, rc_montant) VALUES (YEAR( NEW.rc_date ), MONTH( NEW.rc_date ), NEW.vd_id, NEW.rc_montant) ON DUPLICATE KEY UPDATE rc_montant = rc_montant + NEW.rc_montant; END// CREATE TRIGGER recettes_vendeurs_update_t BEFORE UPDATE ON recettes_vendeurs FOR EACH ROW BEGIN UPDATE recettes_jour_mat SET rc_montant = rc_montant - OLD.rc_montant WHERE rc_date = OLD.rc_date; UPDATE recettes_jour_mat SET rc_montant = rc_montant + NEW.rc_montant WHERE rc_date = NEW.rc_date; UPDATE recettes_mois_mat SET rc_montant = rc_montant - OLD.rc_montant WHERE rc_year = YEAR( OLD.rc_date ) AND rc_month = MONTH( OLD.rc_date ); UPDATE recettes_mois_mat SET rc_montant = rc_montant + NEW.rc_montant WHERE rc_year = YEAR( NEW.rc_date ) AND rc_month = MONTH( NEW.rc_date ); UPDATE recettes_vendeur_mois_mat SET rc_montant = rc_montant - OLD.rc_montant WHERE rc_year = YEAR( OLD.rc_date ) AND rc_month = MONTH( OLD.rc_date ) AND vd_id = OLD.vd_id; UPDATE recettes_vendeur_mois_mat SET rc_montant = rc_montant + NEW.rc_montant WHERE rc_year = YEAR( NEW.rc_date ) AND rc_month = MONTH( NEW.rc_date ) AND vd_id = NEW.vd_id; END// CREATE TRIGGER recettes_vendeurs_delete_t BEFORE DELETE ON recettes_vendeurs FOR EACH ROW BEGIN UPDATE recettes_jour_mat SET rc_montant = rc_montant - OLD.rc_montant WHERE rc_date = OLD.rc_date; UPDATE recettes_mois_mat SET rc_montant = rc_montant - OLD.rc_montant WHERE rc_year = YEAR( OLD.rc_date ) AND rc_month = MONTH( OLD.rc_date ); UPDATE recettes_vendeur_mois_mat SET rc_montant = rc_montant - OLD.rc_montant WHERE rc_year = YEAR( OLD.rc_date ) AND rc_month = MONTH( OLD.rc_date ) AND vd_id = OLD.vd_id; END// delimiter ;

Voilà, nos triggers sont créés.

Lors de l'insertion, on utilise INSERT ... ON DUPLICATE KEY UPDATE pour tenir à jour les tables. Cela fonctionne, car nous avons utilisé des clés primaires (uniques) qui conviennent : par exemple, dans « recettes_mois_mat », la clé primaire est (rc_year, rc_month) ; il ne peut y avoir qu'une seule ligne pour un mois d'une année en particulier. C'est ce que nous voulons.

Appeler le centre

Avez-vous besoin d'un coach de formation?

Il vous aidera à comparer différents cours et à trouver la solution la plus abordable.

Triggers et vues matérialisées (MySQL)

Prix sur demande