Editions ENI Livres | Vidéos | e-Formations
Autres fonctionnalités
Partitionnement
1. Intérêt et limitations
Le partitionnement horizontal permet de diviser une table en fonction des données qu’elle contient, de manière transparente pour l’utilisateur. Ce processus peut permettre une gestion plus efficace des données stockées en les regroupant dans différents emplacements (partitions) selon certains critères. Le partitionnement d’une table se définit à sa création, en indiquant le type et la clé de partitionnement, ainsi que d’autres paramètres tels que le nombre de partitions à générer.
a. Gestion des gros volumes
Avec certains systèmes de fichiers, la limite de la taille maximale d’un fichier peut poser problème. Le partitionnement peut permettre de contourner cette limite, en répartissant le contenu d’une table dans plusieurs fichiers.
b. Partition pruning
Le « partition pruning » permet à l’optimiseur de ne pas analyser les partitions qui ne contiennent pas de données nécessaires à l’exécution de la requête. Il est utilisé dans les cas suivants :
-
colonne_partitionnée = constante,
-
colonne_partitionnée IN (constante1, constante2, constante3...),
-
colonne_partitionnée BETWEEN constante1 AND constante2,
-
colonne_partitionnée [op] constante avec op qui est un opérateur d’inégalité (<, >, <>, <=, >=),
-
avec les fonctions YEAR(), TO_DAYS(), TO_SECONDS() sur les colonnes de type DATE et DATETIME.
c. Suppression rapide d’un gros volume de données
Effacer un gros volume de données d’une table avec un ordre SQL DELETE est parfois une opération très coûteuse. Le partitionnement peut permettre dans ce cas de réduire l’ensemble de ces opérations à la suppression de fichiers et à une modification de structure, en d’autres termes, d’avoir à utiliser une commande DDL (ALTER) en lieu et place d’une commande DML (DELETE).
Dans l’exemple suivant, la table t1 contient quatre partitions :
mysql> SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
Table: t1
Create Table:
CREATE TABLE `t1` (
`valeur` tinyint(3) unsigned NOT NULL, ...Routines stockées
1. Rôle
Les routines stockées sont des programmes (des procédures ou des fonctions) créés par l’utilisateur, précompilés et stockés dans le serveur MySQL. Elles permettent de déplacer une partie de la logique métier d’une application du client vers le serveur. Le client n’a alors plus besoin de soumettre à nouveau toute la commande, mais a juste à faire une simple référence à la routine.
Les routines stockées peuvent avoir plusieurs utilités :
-
Améliorer la sécurité : les programmes clients n’accèdent plus directement aux tables. Telle une API, toutes les opérations de gestion des données sont effectuées via des routines stockées, ce qui limite les privilèges à leur exécution, sans pour autant donner accès aux tables qui hébergent l’information.
-
Centraliser les requêtes : différentes applications (qui peuvent utiliser des langages de programmation différents) peuvent accéder aux mêmes données et avoir les mêmes fonctionnalités, ce qui permet de factoriser le code SQL commun et implique une diminution de la redondance et une facilité de maintenance du code.
La principale limitation des routines stockées est le langage disponible : il est rudimentaire et difficile à déboguer. Il est néammoins possible d’utiliser :
-
Des requêtes SQL (INSERT, UPDATE, CREATE...).
-
Des variables définies en utilisant les mots-clés DECLARE et SET.
-
Des opérateurs (=, AND, LIKE...) et des fonctions natives tout comme dans le SQL (CEIL, CONCAT, DAYOFWEEK...).
-
Des fonctions de contrôle (IF, CASE, REPEAT, LOOP...).
-
Des curseurs qui permettent de parcourir les lignes en sortie d’une requête SQL pour effectuer des boucles de traitement.
Concernant les performances, il ne faut en général pas attendre des miracles des procédures stockées, principalement car il est difficile de profiler l’exécution des routines stockées. Un bon outil est la procédure ps_trace_thread() disponible avec performance_schema (voir le chapitre Outils de surveillance pour plus de détails sur performance_schema). La documentation de cette procédure...
Déclencheurs (triggers)
1. Rôle
Les déclencheurs ou triggers sont des objets dont le but est d’exécuter du code en réaction à un événement qui survient sur une table. Les événements peuvent être de trois types : INSERT, UPDATE ou DELETE (ou assimilés, par exemple, l’instruction REPLACE qui vaut soit INSERT, soit INSERT et DELETE). L’ordre de déclenchement est défini avant (BEFORE) ou après (AFTER) l’événement. Par exemple, le DBA choisira BEFORE pour un test de vérification sur des données et AFTER pour de la journalisation.
2. Syntaxe
La syntaxe à observer lors de la création d’un déclencheur est :
CREATE
[DEFINER = { user | CURRENT_USER }]
TRIGGER nom_du_trigger moment_du_déclenchement
action_qui_déclenche
ON nom_de_table FOR EACH ROW corps_du_déclencheur avec moment_du_déclenchement qui vaut BEFORE ou AFTER et action_qui_déclenche qui prend les valeurs INSERT, UPDATE ou DELETE.
Dans le corps du déclencheur, les alias OLD et NEW sont disponibles en fonction du contexte (INSERT/UPATE/DELETE). Ils permettent d’accéder respectivement à la valeur d’une colonne de la table qui contient le déclencheur, avant modification et après :
-
Lors d’un UPDATE, OLD.colonne référence la valeur de la colonne avant la modification, alors que NEW.colonne, sa valeur après qu’elle a été modifiée.
-
Lors d’un INSERT, seule NEW.colonne existe.
-
Lors d’un DELETE, seule OLD.colonne existe.
Exemple d’un trigger qui sauvegarde les données effacées de la table City :
CREATE TABLE City (
id int(11)...Événements
1. Rôle
Le planificateur d’événements ou « event scheduler » offre la possibilité à l’administrateur de bases de données de déclencher l’exécution de programmes stockés directement sur le serveur MySQL. Ce planificateur de tâches interne (similaire dans la logique au planificateur de tâches de Windows ou au service cron sous Linux) permet donc d’automatiser très simplement des tâches à des intervalles réguliers, ou à heure fixe, sans avoir besoin de configurer le système d’exploitation qui héberge la base de données. Pour pouvoir l’utiliser, il faut tout d’abord l’activer, car ce n’est pas le cas par défaut :
mysql> SHOW VARIABLES LIKE 'event_scheduler';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| event_scheduler | OFF |
+-----------------+-------+
mysql> SET GLOBAL event_scheduler = 1;
mysql> SHOW VARIABLES LIKE 'event_scheduler';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| event_scheduler | ON |
+-----------------+-------+ Une fois activé, MySQL démarre un processus léger (thread) en tâche de fond. Ce processus est chargé d’exécuter les événements lorsque le moment est venu. Vous pouvez le constater en utilisant la commande SHOW PROCESSLIST, et pour cela vous aurez besoin du privilège SUPER, sinon vous ne verrez que les processus qui vous sont associés :
mysql> SHOW PROCESSLIST\G
*************************** 1. row ***************************
Id: 12
User: event_scheduler
Host:...Vues
1. Rôle
Les vues sont des tables virtuelles créées à partir d’une requête SELECT. Elles ne stockent pas les données qu’elles génèrent mais seulement la requête permettant de les créer. La requête SELECT qui génère la vue référence une ou plusieurs tables. La vue peut donc être par exemple une jointure entre différentes tables, l’agrégation ou l’extraction de certaines colonnes d’une table. Elle peut également être créée à partir d’une autre vue.
Les vues sont souvent en lecture seule et ne permettent donc que de lire des données. Cependant, MySQL permet la création de vues modifiables sous certaines conditions :
-
La requête qui génère la vue doit permettre à MySQL de retrouver la trace de l’enregistrement à modifier dans la ou les tables sous-jacentes ainsi que celle de toutes les valeurs de chaque colonne. La requête SELECT créant la vue ne doit donc pas contenir de clause DISTINCT, GROUP BY, HAVING et autres fonctions d’agrégation.
-
La clause ALGORITHM ne doit pas être de valeur TEMPTABLE. Nous reviendrons sur ce point par la suite.
-
La requête ne doit pas accéder à des vues sous-jacentes non modifiables.
Les vues peuvent être utilisées pour différentes raisons, elles permettent de :
-
Contrôler l’intégrité en restreignant l’accès aux données pour améliorer la confidentialité avec un partitionnement vertical et/ou horizontal pour cacher des champs aux utilisateurs. Ceci permet de personnaliser l’affichage des informations suivant le type d’utilisateur.
-
Masquer la complexité du schéma. L’indépendance logique des données est utile pour donner aux utilisateurs l’accès à un ensemble de relations représentées sous la forme d’une table. Les données de la vue sont alors des champs de différentes tables regroupées, ou des résultats d’opérations sur ces champs.
-
Modifier automatiquement des données sélectionnées (sum(), avg(), max()...). Cela permet de manipuler des valeurs calculées à partir d’autres valeurs du schéma....
Nouvelles fonctionnalités de MySQL 8.0
1. Fonctions de fenêtrage
Les fonctions de fenêtrage font enfin leur apparition dans MySQL, après de très longues années d’attente. Ces fonctions permettent de répondre à un certain nombre de requêtes qu’il était auparavant très difficile d’écrire d’une manière simple. Qu’est-ce qu’une fonction de fenêtrage ? Il s’agit d’une fonction d’agrégation au même titre que les célèbres SUM(), COUNT() ou MAX(), mais avec une différence essentielle : au lieu de fournir un résultat par critère de regroupement, les lignes individuelles sont préservées. Pas clair ? Un exemple va rendre ces explications plus concrètes.
Dans la base exemple employees, considérons la table salaries qui donne le salaire des employés à différentes dates. Prenons par exemple l’employé numéro 34330 :
mysql> SELECT * FROM salaries WHERE emp_no = 34330 ORDER BY from_date;
+--------+--------+------------+------------+
| emp_no | salary | from_date | to_date |
+--------+--------+------------+------------+
| 34330 | 55286 | 1988-10-07 | 1989-10-07 |
| 34330 | 57201 | 1989-10-07 | 1990-10-07 |
| 34330 | 59129 | 1990-10-07 | 1991-10-07 |
| 34330 | 61662 | 1991-10-07 | 1992-10-06 |
| 34330 | 63546 | 1992-10-06 | 1993-10-06 |
| 34330 | 63417 | 1993-10-06 | 1994-05-20 |
+--------+--------+------------+------------+ Si on souhaite calculer le salaire total de cet employé au cours de sa carrière, on peut utiliser la fonction SUM() :
mysql> SELECT emp_no, SUM(salary) AS total FROM salaries
WHERE emp_no = 34330;
+--------+--------+
| emp_no | total |
+--------+--------+
| 34330 | 360241 |
+--------+--------+ La fonction SUM() a pour effet d’agréger les valeurs des différents salaires, MySQL renvoie donc un résultat avec une seule ligne. Si, au lieu d’un seul employé, on appliquait la requête à plusieurs employés, il faudrait ajouter une clause GROUP BY pour spécifier à la fonction SUM() qu’il...
