Cet article explore plusieurs fonctionnalités avancées du système de gestion de bases de données MySQL, y compris les vues, les déclencheurs, les transactions, les procédures stockées, les fonctions, les structures de contrôle de flux, les index et l'optimisation des requêtes lentes.

Les Vues

Une vue est un objet de base de données virtuel dont le contenu est défini par une requête. Elle permet de stocker le résultat d'une requête pour une consultation réutilisable.

CREATE VIEW vue_cours_enseignant AS
SELECT c.nom_cours, e.nom_enseignant
FROM cours c
INNER JOIN enseignants e ON c.id_enseignant = e.id;

Points importants concernant les vues :

• Les données d'une vue proviennent des tables sous-jacentes, donc les vues sont généralement en lecture seule pour les opérations directes d'insertion, de mise à jour ou de suppression.
• En raison de leur comportement similaire aux tables, une convention de nommage claire (ex : préfixe v_) est recommandée pour éviter toute confusion avec les tables physiques.

Modification du Délimiteur

Le mot-clé DELIMITER permet de changer le caractère de fin d'instruction (par défaut ;) pour un autre, ce qui est utile pour définir des blocs de code complexes.

DELIMITER //
SELECT * FROM ma_table //
DELIMITER ;

Procédures Stockées

Une procédure stockée est un ensemble d'instructions SQL précompilé et stocké dans la base de données, similaire à une fonction dans un langage de programmation.

-- Procédure sans paramètre
DELIMITER //
CREATE PROCEDURE afficher_toutes_commandes()
BEGIN
    SELECT * FROM commandes;
END //
DELIMITER ;
CALL afficher_toutes_commandes();

-- Procédure avec paramètres
DELIMITER //
CREATE PROCEDURE rechercher_commandes_par_plage(
    IN debut_id INT,
    IN fin_id INT,
    OUT nombre_resultats INT
)
BEGIN
    SELECT * FROM commandes WHERE id BETWEEN debut_id AND fin_id;
    SELECT COUNT(*) INTO nombre_resultats FROM commandes WHERE id BETWEEN debut_id AND fin_id;
END //
DELIMITER ;

Pour gérer les paramètres de sortie (OUT), il faut utiliser des variables utilisateur dans la session :

SET @total = 0;
CALL rechercher_commandes_par_plage(10, 50, @total);
SELECT @total;

Commandes utiles pour la gestion des procédures :

SHOW PROCEDURE STATUS WHERE Db = 'nom_base';
SHOW CREATE PROCEDURE nom_proc;
DROP PROCEDURE IF EXISTS nom_proc;

Fonctions Intégrées

MySQL fournit de nombreuses fonctions intégrées pour traiter les données. Voici quelques exemples :

-- Manipulation de chaînes
SELECT TRIM('  espace   '), UPPER('hello'), LEFT('MySQL', 2);
-- Recherche phonétique (anglais)
SELECT * FROM clients WHERE SOUNDEX(nom) = SOUNDEX('Smith');
-- Formatage de dates
SELECT DATE_FORMAT(date_commande, '%Y-%m') AS mois, COUNT(*)
FROM commandes
GROUP BY DATE_FORMAT(date_commande, '%Y-%m');

Déclencheurs (Triggers)

Un déclencheur est un programme associé à une table qui s'exécute automatiquement en réponse à certaines événements (INSERT, UPDATE, DELETE).

CREATE TABLE journal_erreurs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    description VARCHAR(255),
    horodatage DATETIME
);

DELIMITER //
CREATE TRIGGER verifier_commande_apres_insertion
AFTER INSERT ON commandes
FOR EACH ROW
BEGIN
    IF NEW.statut = 'annulee' THEN
        INSERT INTO journal_erreurs (description, horodatage)
        VALUES (CONCAT('Commande annulée: ', NEW.id), NOW());
    END IF;
END //
DELIMITER ;

Commandes de gestion :

SHOW TRIGGERS FROM nom_base;
DROP TRIGGER IF EXISTS verifier_commande_apres_insertion;

Transactions et Propriétés ACID

Une transaction est une unité de travail logique qui doit respecter quatre propriétés (ACID) pour garantir l'intégrité des données.

START TRANSACTION;
UPDATE comptes SET solde = solde - 100 WHERE id_titulaire = 'A';
UPDATE comptes SET solde = solde + 100 WHERE id_titulaire = 'B';
COMMIT; -- Ou ROLLBACK pour annuler

Isolation des Transactions et MVCC

MySQL (InnoDB) utilise le Contrôle de Concurrence Multi-Version (MVCC) pour gérer l'accès concurrent aux données. Chaque transaction "voit" une image cohérente de la base de données à un instant donné.

MVCC fonctionne en maintenant deux colonnes système par ligne : l'identifiant de la transaction qui a créé la version de la ligne et l'identifiant de la trensaction qui l'a supprimée (ou une valeur vide). Une requête ne verra que les versions des lignes dont l'identifiant de création est inférieur ou égal à l'identifiant de la requête, et dont l'identifiant de suppression est supérieur ou nul.

Contrôle de Flux

Les procédures stockées peuvent utiliser des structures de contrôle classiques.

-- Structure conditionnelle
IF score > 90 THEN
    SET mention = 'Excellent';
ELSEIF score > 70 THEN
    SET mention = 'Bien';
ELSE
    SET mention = 'Passable';
END IF;

-- Boucle WHILE
DECLARE compteur INT DEFAULT 0;
WHILE compteur < 5 DO
    INSERT INTO logs (message) VALUES (CONCAT('Itération ', compteur));
    SET compteur = compteur + 1;
END WHILE;

Index et Structures de Données

Les index sont des structures de données qui améliorent la vitesse des requêtes. MySQL utilise principalement des index basés sur l'arbre B+.

Un arbre B+ est une extension de l'arbre B. Les données sont stockées uniquement dans les feuilles, tandis que les nœuds internes (racine et branches) contiennent seulement des clés de tri. Les feuilles sont reliées entre elles par des pointeurs, ce qui facilite les requêtes de plage.

Index clés :

• Index clusterisé : La table physique est triée selon la clé primaire. Les feuilles de l'arbre B+ contiennent toutes les données de la ligne.
• Index secondaire (non clusterisé) : Les feuilles contiennent la valeur de la clé indexée et la valeur de la clé primaire correspondante. Une recherche via un index secondaire nécessite une recherche supplémentaire dans l'index clusterisé (sauf si toutes les colonnes requises sont dans l'index secondaire).

Optimisation des Requêtes Lentes

L'outil EXPLAIN permet d'analyser le plan d'exécution d'une requête. La colonne type fournit une indication sur l'efficacité de l'indexation, allant de system (meilleur) à ALL (pire, scan complet de la table). L'objectif est d'éviter les opérations de type ALL ou index (scan complet d'un index).

EXPLAIN SELECT * FROM commandes WHERE client_id = 100 AND date BETWEEN '2023-01-01' AND '2023-12-31';