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.
Partitionnement manuel : il est possible de partitionner manuellement une table MyISAM en utilisant le moteur MERGE. Cependant ce moteur n’est compatible qu’avec le moteur MyISAM, et n’est pas aussi transparent que le partitionnement et nécessite dans tous les cas de parcourir l’ensemble des tables pour lire des enregistrements. Ce type de partitionnement a quasiment disparu aujourd’hui depuis qu’InnoDB est devenu le moteur de stockage le plus courant.
a. Gestion de la montée en charge
Plus une table est sollicitée, plus le risque qu’elle pose des problèmes de performances augmente. Le partitionnement peut permettre de réduire la contention d’une table en la répartissant sur l’ensemble des partitions, grâce au « partition pruning ». Par exemple, lors de l’exécution d’une requête SELECT sur une table ayant pour moteur de stockage MyISAM, seules les partitions nécessaires sont verrouillées en lecture au lieu de la table entière.
Le partitionnement peut aussi permettre d’isoler les enregistrements qui sont atteints. Cela permet de diminuer la taille des index qui vont être le plus souvent accédés et, par incidence, de diminuer la place mémoire nécessaire pour leur stockage dans le cache. Par exemple, pour un index de type B-Tree, cela permet de diminuer le nombre de feuilles, ainsi que la profondeur, ce qui induit une accélération des opérations de recherche, insertion et suppression. De plus, les données liées à l’index devraient avoir une plus grande probabilité de se trouver en mémoire, ce qui a pour conséquence de réduire...
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 MariaDB. 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 doit simplement faire une simple référence à la routine.
Les routines stockées présentent plusieurs avantages :
-
Améliorer la sécurité : les programmes clients n’accèdent plus directement aux tables. Telle une API (Application Programming Interface), 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.
-
Augmenter les performances : les commandes n’ont pas à être analysées plusieurs fois et bien moins d’informations transitent sur le réseau, ce qui permet de limiter le trafic et de ne solliciter que le serveur MariaDB pour certains traitements.
Le langage permettant de programmer une routine est assez rudimentaire, mais il est 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.
Lors de la création d’un programme stocké (procédure, fonction, trigger...), il est impératif de modifier le délimiteur, c’est-à-dire le symbole qui indique à...
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 et après modification :
-
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...É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 MariaDB. Ce planificateur de tâches interne (CRON-like) 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é, MariaDB 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: localhost
db: NULL
Command: Daemon ...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, MariaDB permet la création de vues modifiables sous certaines conditions :
-
La requête qui génère la vue doit permettre à MariaDB 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....
Colonnes générées
1. Introduction
Les colonnes générées sont apparues avec MariaDB 5.2, soit très longtemps avant MySQL. L’idée est très simple : il s’agit de colonnes dont les valeurs sont dérivées d’une ou plusieurs autres colonnes de la même table. Les colonnes générées peuvent être indexées, ce qui permet d’ajouter des index fonctionnels pour, par exemple :
-
Indexer des chaînes de caractères de droite à gauche plutôt que de gauche à droite.
-
Indexer seulement la date pour une colonne contenant à la fois la date et l’heure.
-
Indexer la somme de deux colonnes.
MariaDB propose deux types de colonnes générées : les colonnes virtuelles, dont les valeurs sont calculées à la volée et jamais stockées sur disque, et les colonnes persistantes, qui sont des colonnes classiques stockées sur disque mais dont la valeur est calculée à partir d’autres colonnes.
2. Colonnes persistentes
Imaginez que vous avez une table t avec une colonne stockant un nom de famille. Pour ajouter à votre table une colonne persistente contenant le nom de famille à l’envers, vous utiliserez la syntaxe suivante :
mysql> ALTER TABLE t ADD nom_inverse VARCHAR(50) GENERATED ALWAYS AS (REVERSE(nom)) PERSISTENT;...