MySQL avance : Jointures, sous-requetes et transactions

Introduction

Maitriser les jointures, les sous-requetes et les transactions est essentiel pour exploiter pleinement la puissance de MySQL. Ces fonctionnalites vous permettent d’interroger des donnees complexes, de garantir l’integrite de vos operations et d’optimiser vos requetes.

Dans cet article, vous apprendrez :

• Les differents types de jointures et quand les utiliser
• Les sous-requetes scalaires, de table et correlees
• Les Common Table Expressions (CTEs) pour des requetes lisibles
• Les transactions ACID et les niveaux d’isolation
• Les verrous et la gestion de la concurrence
• Les bonnes pratiques et l’optimisation

Base de donnees d’exemple

Utilisez ce schema pour suivre les exemples :

CREATE DATABASE IF NOT EXISTS boutiqueavancee
CHARACTER SET utf8mb4 COLLATE utf8mb4unicodeci;

USE boutiqueavancee;

-- Categories avec hierarchie
CREATE TABLE categories (
    id INT UNSIGNED AUTOINCREMENT PRIMARY KEY,
    parentid INT UNSIGNED NULL,
    nom VARCHAR(100) NOT NULL,
    description TEXT,
    actif BOOLEAN DEFAULT TRUE,
    INDEX idxparent (parentid),
    FOREIGN KEY (parentid) REFERENCES categories(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- Fournisseurs
CREATE TABLE fournisseurs (
    id INT UNSIGNED AUTOINCREMENT PRIMARY KEY,
    nom VARCHAR(100) NOT NULL,
    email VARCHAR(255),
    telephone VARCHAR(20),
    pays VARCHAR(50) DEFAULT 'France',
    actif BOOLEAN DEFAULT TRUE
) ENGINE=InnoDB;

-- Produits
CREATE TABLE produits (
    id INT UNSIGNED AUTOINCREMENT PRIMARY KEY,
    categorieid INT UNSIGNED,
    fournisseurid INT UNSIGNED,
    sku VARCHAR(50) NOT NULL UNIQUE,
    nom VARCHAR(255) NOT NULL,
    description TEXT,
    prix DECIMAL(10, 2) NOT NULL,
    cout DECIMAL(10, 2),
    stock INT UNSIGNED DEFAULT 0,
    stockminimum INT UNSIGNED DEFAULT 10,
    actif BOOLEAN DEFAULT TRUE,
    createdat TIMESTAMP DEFAULT CURRENTTIMESTAMP,
    updatedat TIMESTAMP DEFAULT CURRENTTIMESTAMP ON UPDATE CURRENTTIMESTAMP,
    INDEX idxcategorie (categorieid),
    INDEX idxfournisseur (fournisseurid),
    INDEX idxprix (prix),
    INDEX idxstock (stock),
    FOREIGN KEY (categorieid) REFERENCES categories(id) ON DELETE SET NULL,
    FOREIGN KEY (fournisseurid) REFERENCES fournisseurs(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- Clients
CREATE TABLE clients (
    id INT UNSIGNED AUTOINCREMENT PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE,
    nom VARCHAR(100) NOT NULL,
    prenom VARCHAR(100) NOT NULL,
    telephone VARCHAR(20),
    dateinscription DATE DEFAULT (CURRENTDATE),
    segment ENUM('standard', 'premium', 'vip') DEFAULT 'standard',
    INDEX idxsegment (segment)
) ENGINE=InnoDB;

-- Commandes
CREATE TABLE commandes (
    id INT UNSIGNED AUTOINCREMENT PRIMARY KEY,
    clientid INT UNSIGNED NOT NULL,
    numero VARCHAR(20) NOT NULL UNIQUE,
    total DECIMAL(10, 2) NOT NULL DEFAULT 0,
    statut ENUM('brouillon', 'validee', 'payee', 'expediee', 'livree', 'annulee') DEFAULT 'brouillon',
    createdat TIMESTAMP DEFAULT CURRENTTIMESTAMP,
    updatedat TIMESTAMP DEFAULT CURRENTTIMESTAMP ON UPDATE CURRENTTIMESTAMP,
    INDEX idxclient (clientid),
    INDEX idxstatut (statut),
    INDEX idxdate (createdat),
    FOREIGN KEY (clientid) REFERENCES clients(id) ON DELETE RESTRICT
) ENGINE=InnoDB;

-- Lignes de commande
CREATE TABLE commandelignes (
    id INT UNSIGNED AUTOINCREMENT PRIMARY KEY,
    commandeid INT UNSIGNED NOT NULL,
    produitid INT UNSIGNED NOT NULL,
    quantite INT UNSIGNED NOT NULL DEFAULT 1,
    prixunitaire DECIMAL(10, 2) NOT NULL,
    INDEX idxcommande (commandeid),
    INDEX idxproduit (produitid),
    FOREIGN KEY (commandeid) REFERENCES commandes(id) ON DELETE CASCADE,
    FOREIGN KEY (produitid) REFERENCES produits(id) ON DELETE RESTRICT
) ENGINE=InnoDB;

-- Avis produits
CREATE TABLE avis (
    id INT UNSIGNED AUTOINCREMENT PRIMARY KEY,
    produitid INT UNSIGNED NOT NULL,
    clientid INT UNSIGNED NOT NULL,
    note TINYINT UNSIGNED NOT NULL CHECK (note BETWEEN 1 AND 5),
    commentaire TEXT,
    createdat TIMESTAMP DEFAULT CURRENTTIMESTAMP,
    INDEX idxproduit (produitid),
    INDEX idxclient (clientid),
    UNIQUE KEY ukclientproduit (clientid, produitid),
    FOREIGN KEY (produitid) REFERENCES produits(id) ON DELETE CASCADE,
    FOREIGN KEY (clientid) REFERENCES clients(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Donnees de test
INSERT INTO categories (id, parentid, nom) VALUES
(1, NULL, 'Electronique'),
(2, NULL, 'Vetements'),
(3, NULL, 'Livres'),
(4, 1, 'Smartphones'),
(5, 1, 'Audio'),
(6, 2, 'Homme'),
(7, 2, 'Femme'),
(8, 3, 'Informatique'),
(9, 3, 'Litterature');

INSERT INTO fournisseurs (id, nom, email, pays) VALUES
(1, 'TechDistrib', 'contact@techdistrib.fr', 'France'),
(2, 'AsiaImport', 'sales@asiaimport.com', 'Chine'),
(3, 'EuroBooks', 'info@eurobooks.eu', 'Allemagne'),
(4, 'FashionWholesale', 'orders@fashionwholesale.fr', 'France');

INSERT INTO produits (categorieid, fournisseurid, sku, nom, prix, cout, stock) VALUES
(4, 1, 'PHONE-001', 'Smartphone Galaxy S24', 899.99, 650.00, 45),
(4, 2, 'PHONE-002', 'iPhone 15 Pro', 1199.00, 850.00, 30),
(5, 1, 'AUDIO-001', 'Casque Bluetooth Sony', 249.99, 120.00, 100),
(5, 2, 'AUDIO-002', 'Ecouteurs AirPods Pro', 279.00, 150.00, 75),
(6, 4, 'VET-H-001', 'Jean slim homme', 59.99, 25.00, 200),
(6, 4, 'VET-H-002', 'Chemise classique', 49.99, 18.00, 150),
(7, 4, 'VET-F-001', 'Robe ete', 79.99, 30.00, 80),
(8, 3, 'BOOK-001', 'Clean Code', 34.90, 15.00, 60),
(8, 3, 'BOOK-002', 'Design Patterns', 45.00, 20.00, 40),
(9, 3, 'BOOK-003', 'Les Miserables', 12.90, 5.00, 100),
(4, 1, 'PHONE-003', 'Pixel 8', 799.00, 550.00, 0);  -- Stock epuise

INSERT INTO clients (email, nom, prenom, segment) VALUES
('jean.dupont@email.com', 'Dupont', 'Jean', 'premium'),
('marie.martin@email.com', 'Martin', 'Marie', 'vip'),
('paul.bernard@email.com', 'Bernard', 'Paul', 'standard'),
('sophie.petit@email.com', 'Petit', 'Sophie', 'standard'),
('lucas.moreau@email.com', 'Moreau', 'Lucas', 'premium');

INSERT INTO commandes (clientid, numero, total, statut, createdat) VALUES
(1, 'CMD-2024-0001', 959.98, 'livree', '2024-01-15 10:30:00'),
(1, 'CMD-2024-0002', 84.98, 'expediee', '2024-02-20 14:15:00'),
(2, 'CMD-2024-0003', 1199.00, 'livree', '2024-01-22 09:00:00'),
(2, 'CMD-2024-0004', 324.99, 'payee', '2024-03-10 16:45:00'),
(3, 'CMD-2024-0005', 79.89, 'livree', '2024-02-05 11:20:00'),
(4, 'CMD-2024-0006', 249.99, 'annulee', '2024-03-15 13:30:00'),
(5, 'CMD-2024-0007', 149.98, 'validee', '2024-03-18 10:00:00');

INSERT INTO commandelignes (commandeid, produitid, quantite, prixunitaire) VALUES
(1, 1, 1, 899.99), (1, 5, 1, 59.99),
(2, 5, 1, 59.99), (2, 8, 1, 34.90),
(3, 2, 1, 1199.00),
(4, 3, 1, 249.99), (4, 5, 1, 59.99), (4, 8, 1, 34.90),
(5, 8, 1, 34.90), (5, 9, 1, 45.00),
(6, 3, 1, 249.99),
(7, 5, 1, 59.99), (7, 6, 1, 49.99), (7, 10, 1, 12.90);

INSERT INTO avis (produitid, clientid, note, commentaire) VALUES
(1, 1, 5, 'Excellent smartphone, tres satisfait'),
(1, 2, 4, 'Bon produit, batterie moyenne'),
(2, 2, 5, 'Le meilleur iPhone'),
(3, 1, 4, 'Bonne qualite audio'),
(5, 3, 3, 'Correct pour le prix'),
(8, 1, 5, 'Lecture indispensable pour tout developpeur');

Les jointures SQL

Les jointures permettent de combiner des donnees provenant de plusieurs tables.

INNER JOIN : Intersection des tables

Retourne uniquement les lignes qui ont une correspondance dans les deux tables.

-- Produits avec leur categorie
SELECT
    p.id,
    p.nom AS produit,
    p.prix,
    c.nom AS categorie
FROM produits p
INNER JOIN categories c ON p.categorieid = c.id;

-- Commandes avec informations client
SELECT
    cmd.numero,
    cmd.total,
    cmd.statut,
    CONCAT(cl.prenom, ' ', cl.nom) AS client,
    cl.email
FROM commandes cmd
INNER JOIN clients cl ON cmd.clientid = cl.id
WHERE cmd.statut = 'livree';

-- Jointure multiple : Lignes de commande avec details
SELECT
    cmd.numero AS commande,
    p.nom AS produit,
    cl.quantite,
    cl.prixunitaire,
    (cl.quantite  cl.prixunitaire) AS soustotal
FROM commandelignes cl
INNER JOIN commandes cmd ON cl.commandeid = cmd.id
INNER JOIN produits p ON cl.produitid = p.id
ORDER BY cmd.numero, p.nom;

LEFT JOIN : Tous les enregistrements de gauche

Retourne toutes les lignes de la table de gauche, avec les correspondances de droite (NULL si pas de correspondance).

-- Tous les produits, meme sans categorie
SELECT
    p.id,
    p.nom AS produit,
    p.prix,
    COALESCE(c.nom, 'Sans categorie') AS categorie
FROM produits p
LEFT JOIN categories c ON p.categorieid = c.id;

-- Clients avec nombre de commandes (inclut ceux sans commande)
SELECT
    cl.id,
    CONCAT(cl.prenom, ' ', cl.nom) AS client,
    cl.segment,
    COUNT(cmd.id) AS nombrecommandes,
    COALESCE(SUM(cmd.total), 0) AS totalachats
FROM clients cl
LEFT JOIN commandes cmd ON cl.id = cmd.clientid
    AND cmd.statut != 'annulee'
GROUP BY cl.id, cl.prenom, cl.nom, cl.segment
ORDER BY totalachats DESC;

-- Produits sans commande (jamais vendus)
SELECT
    p.id,
    p.sku,
    p.nom,
    p.stock
FROM produits p
LEFT JOIN commandelignes cl ON p.id = cl.produitid
WHERE cl.id IS NULL;

RIGHT JOIN : Tous les enregistrements de droite

Retourne toutes les lignes de la table de droite. Peu utilise car equivalent a LEFT JOIN inverse.

-- Equivalent des deux requetes suivantes :
SELECT  FROM produits p RIGHT JOIN categories c ON p.categorieid = c.id;
SELECT  FROM categories c LEFT JOIN produits p ON p.categorieid = c.id;

-- Categories avec ou sans produits
SELECT
    c.nom AS categorie,
    COUNT(p.id) AS nombreproduits
FROM produits p
RIGHT JOIN categories c ON p.categorieid = c.id
GROUP BY c.id, c.nom
ORDER BY nombreproduits DESC;

CROSS JOIN : Produit cartesien

Combine chaque ligne de la premiere table avec chaque ligne de la seconde.

-- Toutes les combinaisons taille/couleur
CREATE TEMPORARY TABLE tailles (taille VARCHAR(5));
CREATE TEMPORARY TABLE couleurs (couleur VARCHAR(20));

INSERT INTO tailles VALUES ('XS'), ('S'), ('M'), ('L'), ('XL');
INSERT INTO couleurs VALUES ('Noir'), ('Blanc'), ('Bleu'), ('Rouge');

SELECT
    t.taille,
    c.couleur,
    CONCAT(t.taille, '-', c.couleur) AS reference
FROM tailles t
CROSS JOIN couleurs c
ORDER BY t.taille, c.couleur;

-- Resultat : 20 combinaisons (5 tailles x 4 couleurs)

Self JOIN : Jointure reflexive

Une table jointe a elle-meme.

-- Hierarchie des categories (parent/enfant)
SELECT
    parent.nom AS categorieparent,
    enfant.nom AS souscategorie
FROM categories enfant
INNER JOIN categories parent ON enfant.parentid = parent.id
ORDER BY parent.nom, enfant.nom;

-- Categories racines avec leurs sous-categories
SELECT
    COALESCE(parent.nom, 'Racine') AS niveau1,
    enfant.nom AS niveau2
FROM categories enfant
LEFT JOIN categories parent ON enfant.parentid = parent.id
ORDER BY niveau1, niveau2;

Jointures avec conditions multiples

-- Jointure avec plusieurs conditions
SELECT
    p.nom AS produit,
    f.nom AS fournisseur,
    p.stock
FROM produits p
INNER JOIN fournisseurs f ON p.fournisseurid = f.id
    AND f.actif = TRUE
    AND f.pays = 'France';

-- Jointure avec plage de dates
SELECT
    c.nom AS client,
    cmd.numero,
    cmd.total
FROM clients cl
INNER JOIN commandes cmd ON cl.id = cmd.clientid
    AND cmd.createdat BETWEEN '2024-01-01' AND '2024-03-31'
    AND cmd.statut IN ('livree', 'expediee');

Performances des jointures

-- Analyser une jointure avec EXPLAIN
EXPLAIN SELECT
    p.nom, c.nom AS categorie
FROM produits p
INNER JOIN categories c ON p.categorieid = c.id
WHERE p.prix > 100;

-- Optimisation : s'assurer que les colonnes de jointure sont indexees
SHOW INDEX FROM produits WHERE Columnname = 'categorieid';
SHOW INDEX FROM commandes WHERE Columnname = 'clientid';

-- Jointure optimisee avec index covering
EXPLAIN SELECT
    p.id, p.nom, p.prix
FROM produits p
INNER JOIN categories c ON p.categorieid = c.id
WHERE c.nom = 'Electronique';

Sous-requetes

Une sous-requete est une requete imbriquee dans une autre requete.

Sous-requetes scalaires (retournent une seule valeur)

-- Prix moyen pour comparaison
SELECT
    nom,
    prix,
    (SELECT AVG(prix) FROM produits) AS prixmoyen,
    prix - (SELECT AVG(prix) FROM produits) AS ecartmoyenne
FROM produits
ORDER BY ecartmoyenne DESC;

-- Produits avec prix superieur a la moyenne
SELECT nom, prix
FROM produits
WHERE prix > (SELECT AVG(prix) FROM produits);

-- Derniere commande d'un client
SELECT 
FROM commandes
WHERE clientid = 1
AND createdat = (
    SELECT MAX(createdat)
    FROM commandes
    WHERE clientid = 1
);

Sous-requetes de liste (retournent une colonne)

-- Produits dans les categories actives
SELECT nom, prix
FROM produits
WHERE categorieid IN (
    SELECT id FROM categories WHERE actif = TRUE
);

-- Clients ayant commande au moins une fois
SELECT 
FROM clients
WHERE id IN (
    SELECT DISTINCT clientid FROM commandes
);

-- Produits jamais commandes
SELECT 
FROM produits
WHERE id NOT IN (
    SELECT DISTINCT produitid FROM commandelignes
);

Sous-requetes de table (retournent plusieurs colonnes)

-- Top 3 produits par categorie (utilisation de sous-requete derivee)
SELECT
    categorie,
    produit,
    prix,
    rang
FROM (
    SELECT
        c.nom AS categorie,
        p.nom AS produit,
        p.prix,
        ROWNUMBER() OVER (PARTITION BY c.id ORDER BY p.prix DESC) AS rang
    FROM produits p
    INNER JOIN categories c ON p.categorieid = c.id
) AS ranked
WHERE rang <= 3;

-- Statistiques par client
SELECT
    stats.clientid,
    stats.nomclient,
    stats.nombrecommandes,
    stats.totalachats,
    stats.paniermoyen
FROM (
    SELECT
        cl.id AS clientid,
        CONCAT(cl.prenom, ' ', cl.nom) AS nomclient,
        COUNT(cmd.id) AS nombrecommandes,
        SUM(cmd.total) AS totalachats,
        AVG(cmd.total) AS paniermoyen
    FROM clients cl
    LEFT JOIN commandes cmd ON cl.id = cmd.clientid
        AND cmd.statut != 'annulee'
    GROUP BY cl.id
) AS stats
ORDER BY stats.totalachats DESC;

Sous-requetes correlees

Une sous-requete correlee reference la requete externe. Elle est executee pour chaque ligne de la requete principale.

-- Produits avec prix superieur a la moyenne de leur categorie
SELECT
    p.nom,
    p.prix,
    c.nom AS categorie
FROM produits p
INNER JOIN categories c ON p.categorieid = c.id
WHERE p.prix > (
    SELECT AVG(p2.prix)
    FROM produits p2
    WHERE p2.categorieid = p.categorieid
);

-- Derniere commande de chaque client
SELECT 
FROM commandes cmd1
WHERE cmd1.createdat = (
    SELECT MAX(cmd2.createdat)
    FROM commandes cmd2
    WHERE cmd2.clientid = cmd1.clientid
);

-- Clients avec plus de commandes que la moyenne
SELECT
    cl.id,
    CONCAT(cl.prenom, ' ', cl.nom) AS client,
    (SELECT COUNT() FROM commandes WHERE clientid = cl.id) AS nbcommandes
FROM clients cl
WHERE (
    SELECT COUNT() FROM commandes WHERE clientid = cl.id
) > (
    SELECT AVG(cnt) FROM (
        SELECT COUNT() AS cnt
        FROM commandes
        GROUP BY clientid
    ) AS moyennes
);

EXISTS et NOT EXISTS

Plus performant que IN pour les grandes tables.

-- Clients ayant passe au moins une commande (EXISTS)
SELECT 
FROM clients cl
WHERE EXISTS (
    SELECT 1
    FROM commandes cmd
    WHERE cmd.clientid = cl.id
);

-- Produits jamais commandes (NOT EXISTS)
SELECT 
FROM produits p
WHERE NOT EXISTS (
    SELECT 1
    FROM commandelignes cl
    WHERE cl.produitid = p.id
);

-- Fournisseurs avec des produits en rupture de stock
SELECT f.
FROM fournisseurs f
WHERE EXISTS (
    SELECT 1
    FROM produits p
    WHERE p.fournisseurid = f.id
    AND p.stock = 0
    AND p.actif = TRUE
);

Common Table Expressions (CTEs)

Les CTEs (WITH clause) rendent les requetes complexes plus lisibles et maintenables.

CTE simple

-- Calcul du chiffre d'affaires par categorie
WITH ventescategorie AS (
    SELECT
        c.id AS categorieid,
        c.nom AS categorie,
        SUM(cl.quantite  cl.prixunitaire) AS chiffreaffaires
    FROM categories c
    INNER JOIN produits p ON c.id = p.categorieid
    INNER JOIN commandelignes cl ON p.id = cl.produitid
    INNER JOIN commandes cmd ON cl.commandeid = cmd.id
    WHERE cmd.statut NOT IN ('annulee', 'brouillon')
    GROUP BY c.id, c.nom
)
SELECT
    categorie,
    chiffreaffaires,
    ROUND(chiffreaffaires  100.0 / SUM(chiffreaffaires) OVER (), 2) AS pourcentage
FROM ventescategorie
ORDER BY chiffreaffaires DESC;

CTEs multiples

-- Analyse RFM (Recence, Frequence, Montant)
WITH
-- Calcul des metriques par client
metriquesclient AS (
    SELECT
        cl.id AS clientid,
        CONCAT(cl.prenom, ' ', cl.nom) AS client,
        DATEDIFF(CURRENTDATE, MAX(cmd.createdat)) AS recencejours,
        COUNT(DISTINCT cmd.id) AS frequence,
        SUM(cmd.total) AS montanttotal
    FROM clients cl
    INNER JOIN commandes cmd ON cl.id = cmd.clientid
    WHERE cmd.statut NOT IN ('annulee', 'brouillon')
    GROUP BY cl.id
),
-- Calcul des scores RFM (1 a 5)
scoresrfm AS (
    SELECT
        clientid,
        client,
        recencejours,
        frequence,
        montanttotal,
        NTILE(5) OVER (ORDER BY recencejours DESC) AS scorerecence,
        NTILE(5) OVER (ORDER BY frequence ASC) AS scorefrequence,
        NTILE(5) OVER (ORDER BY montanttotal ASC) AS scoremontant
    FROM metriquesclient
)
SELECT
    clientid,
    client,
    recencejours,
    frequence,
    montanttotal,
    scorerecence,
    scorefrequence,
    scoremontant,
    (scorerecence + scorefrequence + scoremontant) AS scoretotal,
    CASE
        WHEN scorerecence >= 4 AND scorefrequence >= 4 AND scoremontant >= 4 THEN 'Champion'
        WHEN scorerecence >= 3 AND scorefrequence >= 3 THEN 'Fidele'
        WHEN scorerecence >= 4 THEN 'Nouveau prometteur'
        WHEN scorerecence <= 2 AND scorefrequence >= 3 THEN 'A risque'
        WHEN scorerecence <= 2 THEN 'Perdu'
        ELSE 'Standard'
    END AS segmentrfm
FROM scoresrfm
ORDER BY scoretotal DESC;

CTE recursive

Pour parcourir des structures hierarchiques.

-- Hierarchie complete des categories
WITH RECURSIVE hierarchiecategories AS (
    -- Ancre : categories racines (sans parent)
    SELECT
        id,
        nom,
        parentid,
        0 AS niveau,
        CAST(nom AS CHAR(500)) AS chemin
    FROM categories
    WHERE parentid IS NULL

    UNION ALL

    -- Partie recursive
    SELECT
        c.id,
        c.nom,
        c.parentid,
        h.niveau + 1,
        CONCAT(h.chemin, ' > ', c.nom)
    FROM categories c
    INNER JOIN hierarchiecategories h ON c.parentid = h.id
)
SELECT
    id,
    REPEAT('  ', niveau) || nom AS categorieindentee,
    niveau,
    chemin
FROM hierarchiecategories
ORDER BY chemin;

-- Calculer le nombre de produits par branche complete
WITH RECURSIVE arbrecategories AS (
    SELECT id, nom, parentid, id AS racineid
    FROM categories
    WHERE parentid IS NULL

    UNION ALL

    SELECT c.id, c.nom, c.parentid, a.racineid
    FROM categories c
    INNER JOIN arbrecategories a ON c.parentid = a.id
)
SELECT
    r.nom AS categorieracine,
    COUNT(DISTINCT p.id) AS totalproduits
FROM arbrecategories a
INNER JOIN categories r ON a.racineid = r.id
LEFT JOIN produits p ON a.id = p.categorieid
GROUP BY r.id, r.nom
ORDER BY totalproduits DESC;

Transactions ACID

Les transactions garantissent l’integrite des donnees en regroupant plusieurs operations en une unite atomique.

Proprietes ACID

Syntaxe de base

-- Demarrer une transaction
START TRANSACTION;
-- ou
BEGIN;

-- Valider les modifications
COMMIT;

-- Annuler les modifications
ROLLBACK;

Exemple pratique : Creation d’une commande

DELIMITER //

CREATE PROCEDURE creercommande(
    IN pclientid INT,
    IN pproduits JSON,  -- [{"produitid": 1, "quantite": 2}, ...]
    OUT pcommandeid INT,
    OUT perreur VARCHAR(255)
)
BEGIN
    DECLARE vproduitid INT;
    DECLARE vquantite INT;
    DECLARE vprix DECIMAL(10,2);
    DECLARE vstock INT;
    DECLARE vtotal DECIMAL(10,2) DEFAULT 0;
    DECLARE vnumero VARCHAR(20);
    DECLARE vindex INT DEFAULT 0;
    DECLARE vcount INT;

    -- Gestionnaire d'erreur
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SET perreur = 'Erreur lors de la creation de la commande';
        SET pcommandeid = NULL;
    END;

    -- Demarrer la transaction
    START TRANSACTION;

    -- Generer le numero de commande
    SET vnumero = CONCAT('CMD-', DATEFORMAT(NOW(), '%Y%m%d'), '-',
                          LPAD(FLOOR(RAND()  10000), 4, '0'));

    -- Creer la commande
    INSERT INTO commandes (clientid, numero, total, statut)
    VALUES (pclientid, vnumero, 0, 'brouillon');

    SET pcommandeid = LASTINSERTID();

    -- Nombre de produits a traiter
    SET vcount = JSONLENGTH(pproduits);

    -- Traiter chaque produit
    WHILE vindex < vcount DO
        SET vproduitid = JSONEXTRACT(pproduits, CONCAT('$[', vindex, '].produitid'));
        SET vquantite = JSONEXTRACT(pproduits, CONCAT('$[', vindex, '].quantite'));

        -- Verifier le stock avec verrouillage
        SELECT prix, stock INTO vprix, vstock
        FROM produits
        WHERE id = vproduitid
        FOR UPDATE;  -- Verrouille la ligne

        IF vstock < vquantite THEN
            ROLLBACK;
            SET perreur = CONCAT('Stock insuffisant pour le produit ', vproduitid);
            SET pcommandeid = NULL;
            LEAVE;
        END IF;

        -- Ajouter la ligne de commande
        INSERT INTO commandelignes (commandeid, produitid, quantite, prixunitaire)
        VALUES (pcommandeid, vproduitid, vquantite, vprix);

        -- Mettre a jour le stock
        UPDATE produits
        SET stock = stock - vquantite
        WHERE id = vproduitid;

        -- Calculer le sous-total
        SET vtotal = vtotal + (vprix  vquantite);

        SET vindex = vindex + 1;
    END WHILE;

    -- Mettre a jour le total de la commande
    UPDATE commandes
    SET total = vtotal, statut = 'validee'
    WHERE id = pcommandeid;

    -- Valider la transaction
    COMMIT;

    SET perreur = NULL;
END //

DELIMITER ;

-- Utilisation
SET @commandeid = NULL;
SET @erreur = NULL;

CALL creercommande(
    1,
    '[{"produitid": 1, "quantite": 1}, {"produitid": 3, "quantite": 2}]',
    @commandeid,
    @erreur
);

SELECT @commandeid AS commandeid, @erreur AS erreur;

Savepoints

Les savepoints permettent des rollbacks partiels.

START TRANSACTION;

-- Premiere operation
INSERT INTO clients (email, nom, prenom)
VALUES ('nouveau@email.com', 'Nouveau', 'Client');

SAVEPOINT apresclient;

-- Deuxieme operation
INSERT INTO commandes (clientid, numero, total)
VALUES (LASTINSERTID(), 'CMD-TEST', 0);

SAVEPOINT aprescommande;

-- Troisieme operation qui echoue potentiellement
-- Si probleme, revenir au savepoint
ROLLBACK TO SAVEPOINT aprescommande;

-- Continuer avec d'autres operations
-- ...

COMMIT;

Niveaux d’isolation

-- Voir le niveau d'isolation actuel
SELECT @@transactionisolation;
-- ou
SELECT @@txisolation;  -- versions anciennes

-- Modifier le niveau d'isolation pour la session
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- Exemple de probleme Phantom Read
-- Session 1
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
SELECT COUNT() FROM produits WHERE prix > 100;  -- Retourne 6

-- Session 2 (en parallele)
INSERT INTO produits (categorieid, sku, nom, prix, stock)
VALUES (1, 'NEW-001', 'Nouveau produit', 150, 10);
COMMIT;

-- Session 1
SELECT COUNT() FROM produits WHERE prix > 100;  -- Toujours 6 grace a REPEATABLE READ
COMMIT;

Verrous et concurrence

Types de verrous

-- Verrou partage (lecture) - SELECT ... FOR SHARE
START TRANSACTION;
SELECT  FROM produits WHERE id = 1 FOR SHARE;
-- D'autres sessions peuvent lire mais pas modifier
COMMIT;

-- Verrou exclusif (ecriture) - SELECT ... FOR UPDATE
START TRANSACTION;
SELECT  FROM produits WHERE id = 1 FOR UPDATE;
-- La ligne est verrouillee, autres sessions bloquees
UPDATE produits SET stock = stock - 1 WHERE id = 1;
COMMIT;

-- Verrou avec NOWAIT (MySQL 8.0+)
SELECT  FROM produits WHERE id = 1 FOR UPDATE NOWAIT;
-- Echoue immediatement si la ligne est verrouillee

-- Verrou avec SKIP LOCKED (MySQL 8.0+)
SELECT  FROM produits WHERE stock > 0 FOR UPDATE SKIP LOCKED LIMIT 1;
-- Ignore les lignes verrouilees et prend la suivante

Prevention des deadlocks

-- Mauvaise pratique : ordre d'acces inconsistant
-- Session 1                    -- Session 2
-- UPDATE produits SET ... WHERE id = 1;
--                              UPDATE produits SET ... WHERE id = 2;
-- UPDATE produits SET ... WHERE id = 2;  -- Bloque
--                              UPDATE produits SET ... WHERE id = 1;  -- Deadlock!

-- Bonne pratique : toujours acceder aux ressources dans le meme ordre
START TRANSACTION;
-- Verrouiller dans l'ordre croissant des IDs
SELECT  FROM produits WHERE id IN (1, 2) ORDER BY id FOR UPDATE;
-- Puis effectuer les modifications
UPDATE produits SET stock = stock - 1 WHERE id = 1;
UPDATE produits SET stock = stock - 1 WHERE id = 2;
COMMIT;

Monitoring des verrous

-- Voir les verrous en cours (MySQL 8.0+)
SELECT  FROM performanceschema.datalocks;

-- Voir les attentes de verrous
SELECT  FROM performanceschema.datalockwaits;

-- Informations sur InnoDB
SHOW ENGINE INNODB STATUSG

-- Requetes en attente
SELECT
    r.trxid AS waitingtrxid,
    r.trxmysqlthreadid AS waitingthread,
    r.trxquery AS waitingquery,
    b.trxid AS blockingtrxid,
    b.trxmysqlthreadid AS blockingthread,
    b.trxquery AS blockingquery
FROM informationschema.innodblockwaits w
INNER JOIN informationschema.innodbtrx b ON b.trxid = w.blockingtrxid
INNER JOIN informationschema.innodbtrx r ON r.trxid = w.requestingtrxid;

Optimisation des requetes complexes

Strategies d’optimisation

-- 1. Utiliser EXPLAIN pour analyser
EXPLAIN FORMAT=JSON
SELECT
    c.nom AS categorie,
    COUNT(p.id) AS nbproduits,
    AVG(p.prix) AS prixmoyen
FROM categories c
LEFT JOIN produits p ON c.id = p.categorieid
GROUP BY c.id;

-- 2. Preferer EXISTS a IN pour les sous-requetes correlees
-- Moins performant
SELECT  FROM clients WHERE id IN (
    SELECT clientid FROM commandes WHERE total > 500
);

-- Plus performant
SELECT  FROM clients cl WHERE EXISTS (
    SELECT 1 FROM commandes cmd WHERE cmd.clientid = cl.id AND cmd.total > 500
);

-- 3. Utiliser des CTEs plutot que des sous-requetes repetees
-- Moins performant (sous-requete executee plusieurs fois)
SELECT
    nom,
    prix,
    prix - (SELECT AVG(prix) FROM produits) AS ecart,
    prix / (SELECT AVG(prix) FROM produits) AS ratio
FROM produits;

-- Plus performant (CTE calculee une fois)
WITH stats AS (
    SELECT AVG(prix) AS prixmoyen FROM produits
)
SELECT
    p.nom,
    p.prix,
    p.prix - s.prixmoyen AS ecart,
    p.prix / s.prixmoyen AS ratio
FROM produits p, stats s;

-- 4. Limiter les colonnes selectionnees
-- Eviter SELECT * en production
SELECT id, nom, prix FROM produits WHERE categorieid = 1;

Profiling des requetes

-- Activer le profiling
SET profiling = 1;

-- Executer la requete
SELECT
    c.nom,
    COUNT(p.id)
FROM categories c
LEFT JOIN produits p ON c.id = p.categorieid
GROUP BY c.id;

-- Voir le profil
SHOW PROFILES;
SHOW PROFILE FOR QUERY 1;
SHOW PROFILE CPU, BLOCK IO FOR QUERY 1;

-- Desactiver le profiling
SET profiling = 0;

Sauvegarde et restauration transactionnelle

# Sauvegarde coherente avec transactions
mysqldump -u root -p 
  --single-transaction 
  --routines 
  --triggers 
  --events 
  --master-data=2 
  boutiqueavancee > backuptransactionnel.sql

# Restauration
mysql -u root -p boutiqueavancee < backuptransactionnel.sql

# Export d'une requete complexe
mysql -u root -p boutiqueavancee -e "
SELECT
    c.nom AS categorie,
    p.nom AS produit,
    p.prix,
    p.stock
FROM categories c
INNER JOIN produits p ON c.id = p.categorieid
WHERE p.actif = TRUE
" > exportproduits.tsv

Conclusion

Vous maitrisez maintenant les concepts avances de MySQL :

Jointures

Sous-requetes

CTEs

Transactions

Ces competences sont essentielles pour developper des applications robustes et performantes. Dans les articles suivants, vous apprendrez l’optimisation avancee avec les index et les techniques de partitioning pour les grandes echelles.