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 boutique_avancee
CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE boutique_avancee;
-- Categories avec hierarchie
CREATE TABLE categories (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
parent_id INT UNSIGNED NULL,
nom VARCHAR(100) NOT NULL,
description TEXT,
actif BOOLEAN DEFAULT TRUE,
INDEX idx_parent (parent_id),
FOREIGN KEY (parent_id) REFERENCES categories(id) ON DELETE SET NULL
) ENGINE=InnoDB;
-- Fournisseurs
CREATE TABLE fournisseurs (
id INT UNSIGNED AUTO_INCREMENT 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 AUTO_INCREMENT PRIMARY KEY,
categorie_id INT UNSIGNED,
fournisseur_id 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,
stock_minimum INT UNSIGNED DEFAULT 10,
actif BOOLEAN DEFAULT TRUE,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
INDEX idx_categorie (categorie_id),
INDEX idx_fournisseur (fournisseur_id),
INDEX idx_prix (prix),
INDEX idx_stock (stock),
FOREIGN KEY (categorie_id) REFERENCES categories(id) ON DELETE SET NULL,
FOREIGN KEY (fournisseur_id) REFERENCES fournisseurs(id) ON DELETE SET NULL
) ENGINE=InnoDB;
-- Clients
CREATE TABLE clients (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
email VARCHAR(255) NOT NULL UNIQUE,
nom VARCHAR(100) NOT NULL,
prenom VARCHAR(100) NOT NULL,
telephone VARCHAR(20),
date_inscription DATE DEFAULT (CURRENT_DATE),
segment ENUM('standard', 'premium', 'vip') DEFAULT 'standard',
INDEX idx_segment (segment)
) ENGINE=InnoDB;
-- Commandes
CREATE TABLE commandes (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
client_id 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',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
INDEX idx_client (client_id),
INDEX idx_statut (statut),
INDEX idx_date (created_at),
FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE RESTRICT
) ENGINE=InnoDB;
-- Lignes de commande
CREATE TABLE commande_lignes (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
commande_id INT UNSIGNED NOT NULL,
produit_id INT UNSIGNED NOT NULL,
quantite INT UNSIGNED NOT NULL DEFAULT 1,
prix_unitaire DECIMAL(10, 2) NOT NULL,
INDEX idx_commande (commande_id),
INDEX idx_produit (produit_id),
FOREIGN KEY (commande_id) REFERENCES commandes(id) ON DELETE CASCADE,
FOREIGN KEY (produit_id) REFERENCES produits(id) ON DELETE RESTRICT
) ENGINE=InnoDB;
-- Avis produits
CREATE TABLE avis (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
produit_id INT UNSIGNED NOT NULL,
client_id INT UNSIGNED NOT NULL,
note TINYINT UNSIGNED NOT NULL CHECK (note BETWEEN 1 AND 5),
commentaire TEXT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
INDEX idx_produit (produit_id),
INDEX idx_client (client_id),
UNIQUE KEY uk_client_produit (client_id, produit_id),
FOREIGN KEY (produit_id) REFERENCES produits(id) ON DELETE CASCADE,
FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE
) ENGINE=InnoDB;
-- Donnees de test
INSERT INTO categories (id, parent_id, 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 (categorie_id, fournisseur_id, 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 (client_id, numero, total, statut, created_at) 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 commande_lignes (commande_id, produit_id, quantite, prix_unitaire) 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 (produit_id, client_id, 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.categorie_id = 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.client_id = 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.prix_unitaire,
(cl.quantite * cl.prix_unitaire) AS sous_total
FROM commande_lignes cl
INNER JOIN commandes cmd ON cl.commande_id = cmd.id
INNER JOIN produits p ON cl.produit_id = 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.categorie_id = 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 nombre_commandes,
COALESCE(SUM(cmd.total), 0) AS total_achats
FROM clients cl
LEFT JOIN commandes cmd ON cl.id = cmd.client_id
AND cmd.statut != 'annulee'
GROUP BY cl.id, cl.prenom, cl.nom, cl.segment
ORDER BY total_achats DESC;
-- Produits sans commande (jamais vendus)
SELECT
p.id,
p.sku,
p.nom,
p.stock
FROM produits p
LEFT JOIN commande_lignes cl ON p.id = cl.produit_id
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.categorie_id = c.id;
SELECT * FROM categories c LEFT JOIN produits p ON p.categorie_id = c.id;
-- Categories avec ou sans produits
SELECT
c.nom AS categorie,
COUNT(p.id) AS nombre_produits
FROM produits p
RIGHT JOIN categories c ON p.categorie_id = c.id
GROUP BY c.id, c.nom
ORDER BY nombre_produits 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 categorie_parent,
enfant.nom AS sous_categorie
FROM categories enfant
INNER JOIN categories parent ON enfant.parent_id = parent.id
ORDER BY parent.nom, enfant.nom;
-- Categories racines avec leurs sous-categories
SELECT
COALESCE(parent.nom, 'Racine') AS niveau_1,
enfant.nom AS niveau_2
FROM categories enfant
LEFT JOIN categories parent ON enfant.parent_id = parent.id
ORDER BY niveau_1, niveau_2;
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.fournisseur_id = 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.client_id
AND cmd.created_at 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.categorie_id = c.id
WHERE p.prix > 100;
-- Optimisation : s'assurer que les colonnes de jointure sont indexees
SHOW INDEX FROM produits WHERE Column_name = 'categorie_id';
SHOW INDEX FROM commandes WHERE Column_name = 'client_id';
-- Jointure optimisee avec index covering
EXPLAIN SELECT
p.id, p.nom, p.prix
FROM produits p
INNER JOIN categories c ON p.categorie_id = 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 prix_moyen,
prix - (SELECT AVG(prix) FROM produits) AS ecart_moyenne
FROM produits
ORDER BY ecart_moyenne 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 client_id = 1
AND created_at = (
SELECT MAX(created_at)
FROM commandes
WHERE client_id = 1
);
Sous-requetes de liste (retournent une colonne)
-- Produits dans les categories actives
SELECT nom, prix
FROM produits
WHERE categorie_id IN (
SELECT id FROM categories WHERE actif = TRUE
);
-- Clients ayant commande au moins une fois
SELECT *
FROM clients
WHERE id IN (
SELECT DISTINCT client_id FROM commandes
);
-- Produits jamais commandes
SELECT *
FROM produits
WHERE id NOT IN (
SELECT DISTINCT produit_id FROM commande_lignes
);
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,
ROW_NUMBER() OVER (PARTITION BY c.id ORDER BY p.prix DESC) AS rang
FROM produits p
INNER JOIN categories c ON p.categorie_id = c.id
) AS ranked
WHERE rang <= 3;
-- Statistiques par client
SELECT
stats.client_id,
stats.nom_client,
stats.nombre_commandes,
stats.total_achats,
stats.panier_moyen
FROM (
SELECT
cl.id AS client_id,
CONCAT(cl.prenom, ' ', cl.nom) AS nom_client,
COUNT(cmd.id) AS nombre_commandes,
SUM(cmd.total) AS total_achats,
AVG(cmd.total) AS panier_moyen
FROM clients cl
LEFT JOIN commandes cmd ON cl.id = cmd.client_id
AND cmd.statut != 'annulee'
GROUP BY cl.id
) AS stats
ORDER BY stats.total_achats 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.categorie_id = c.id
WHERE p.prix > (
SELECT AVG(p2.prix)
FROM produits p2
WHERE p2.categorie_id = p.categorie_id
);
-- Derniere commande de chaque client
SELECT *
FROM commandes cmd1
WHERE cmd1.created_at = (
SELECT MAX(cmd2.created_at)
FROM commandes cmd2
WHERE cmd2.client_id = cmd1.client_id
);
-- Clients avec plus de commandes que la moyenne
SELECT
cl.id,
CONCAT(cl.prenom, ' ', cl.nom) AS client,
(SELECT COUNT(*) FROM commandes WHERE client_id = cl.id) AS nb_commandes
FROM clients cl
WHERE (
SELECT COUNT(*) FROM commandes WHERE client_id = cl.id
) > (
SELECT AVG(cnt) FROM (
SELECT COUNT(*) AS cnt
FROM commandes
GROUP BY client_id
) 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.client_id = cl.id
);
-- Produits jamais commandes (NOT EXISTS)
SELECT *
FROM produits p
WHERE NOT EXISTS (
SELECT 1
FROM commande_lignes cl
WHERE cl.produit_id = p.id
);
-- Fournisseurs avec des produits en rupture de stock
SELECT f.*
FROM fournisseurs f
WHERE EXISTS (
SELECT 1
FROM produits p
WHERE p.fournisseur_id = 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 ventes_categorie AS (
SELECT
c.id AS categorie_id,
c.nom AS categorie,
SUM(cl.quantite * cl.prix_unitaire) AS chiffre_affaires
FROM categories c
INNER JOIN produits p ON c.id = p.categorie_id
INNER JOIN commande_lignes cl ON p.id = cl.produit_id
INNER JOIN commandes cmd ON cl.commande_id = cmd.id
WHERE cmd.statut NOT IN ('annulee', 'brouillon')
GROUP BY c.id, c.nom
)
SELECT
categorie,
chiffre_affaires,
ROUND(chiffre_affaires * 100.0 / SUM(chiffre_affaires) OVER (), 2) AS pourcentage
FROM ventes_categorie
ORDER BY chiffre_affaires DESC;
CTEs multiples
-- Analyse RFM (Recence, Frequence, Montant)
WITH
-- Calcul des metriques par client
metriques_client AS (
SELECT
cl.id AS client_id,
CONCAT(cl.prenom, ' ', cl.nom) AS client,
DATEDIFF(CURRENT_DATE, MAX(cmd.created_at)) AS recence_jours,
COUNT(DISTINCT cmd.id) AS frequence,
SUM(cmd.total) AS montant_total
FROM clients cl
INNER JOIN commandes cmd ON cl.id = cmd.client_id
WHERE cmd.statut NOT IN ('annulee', 'brouillon')
GROUP BY cl.id
),
-- Calcul des scores RFM (1 a 5)
scores_rfm AS (
SELECT
client_id,
client,
recence_jours,
frequence,
montant_total,
NTILE(5) OVER (ORDER BY recence_jours DESC) AS score_recence,
NTILE(5) OVER (ORDER BY frequence ASC) AS score_frequence,
NTILE(5) OVER (ORDER BY montant_total ASC) AS score_montant
FROM metriques_client
)
SELECT
client_id,
client,
recence_jours,
frequence,
montant_total,
score_recence,
score_frequence,
score_montant,
(score_recence + score_frequence + score_montant) AS score_total,
CASE
WHEN score_recence >= 4 AND score_frequence >= 4 AND score_montant >= 4 THEN 'Champion'
WHEN score_recence >= 3 AND score_frequence >= 3 THEN 'Fidele'
WHEN score_recence >= 4 THEN 'Nouveau prometteur'
WHEN score_recence <= 2 AND score_frequence >= 3 THEN 'A risque'
WHEN score_recence <= 2 THEN 'Perdu'
ELSE 'Standard'
END AS segment_rfm
FROM scores_rfm
ORDER BY score_total DESC;
CTE recursive
Pour parcourir des structures hierarchiques.
-- Hierarchie complete des categories
WITH RECURSIVE hierarchie_categories AS (
-- Ancre : categories racines (sans parent)
SELECT
id,
nom,
parent_id,
0 AS niveau,
CAST(nom AS CHAR(500)) AS chemin
FROM categories
WHERE parent_id IS NULL
UNION ALL
-- Partie recursive
SELECT
c.id,
c.nom,
c.parent_id,
h.niveau + 1,
CONCAT(h.chemin, ' > ', c.nom)
FROM categories c
INNER JOIN hierarchie_categories h ON c.parent_id = h.id
)
SELECT
id,
REPEAT(' ', niveau) || nom AS categorie_indentee,
niveau,
chemin
FROM hierarchie_categories
ORDER BY chemin;
-- Calculer le nombre de produits par branche complete
WITH RECURSIVE arbre_categories AS (
SELECT id, nom, parent_id, id AS racine_id
FROM categories
WHERE parent_id IS NULL
UNION ALL
SELECT c.id, c.nom, c.parent_id, a.racine_id
FROM categories c
INNER JOIN arbre_categories a ON c.parent_id = a.id
)
SELECT
r.nom AS categorie_racine,
COUNT(DISTINCT p.id) AS total_produits
FROM arbre_categories a
INNER JOIN categories r ON a.racine_id = r.id
LEFT JOIN produits p ON a.id = p.categorie_id
GROUP BY r.id, r.nom
ORDER BY total_produits 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 creer_commande(
IN p_client_id INT,
IN p_produits JSON, -- [{"produit_id": 1, "quantite": 2}, ...]
OUT p_commande_id INT,
OUT p_erreur VARCHAR(255)
)
BEGIN
DECLARE v_produit_id INT;
DECLARE v_quantite INT;
DECLARE v_prix DECIMAL(10,2);
DECLARE v_stock INT;
DECLARE v_total DECIMAL(10,2) DEFAULT 0;
DECLARE v_numero VARCHAR(20);
DECLARE v_index INT DEFAULT 0;
DECLARE v_count INT;
-- Gestionnaire d'erreur
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
ROLLBACK;
SET p_erreur = 'Erreur lors de la creation de la commande';
SET p_commande_id = NULL;
END;
-- Demarrer la transaction
START TRANSACTION;
-- Generer le numero de commande
SET v_numero = CONCAT('CMD-', DATE_FORMAT(NOW(), '%Y%m%d'), '-',
LPAD(FLOOR(RAND() * 10000), 4, '0'));
-- Creer la commande
INSERT INTO commandes (client_id, numero, total, statut)
VALUES (p_client_id, v_numero, 0, 'brouillon');
SET p_commande_id = LAST_INSERT_ID();
-- Nombre de produits a traiter
SET v_count = JSON_LENGTH(p_produits);
-- Traiter chaque produit
WHILE v_index < v_count DO
SET v_produit_id = JSON_EXTRACT(p_produits, CONCAT('$[', v_index, '].produit_id'));
SET v_quantite = JSON_EXTRACT(p_produits, CONCAT('$[', v_index, '].quantite'));
-- Verifier le stock avec verrouillage
SELECT prix, stock INTO v_prix, v_stock
FROM produits
WHERE id = v_produit_id
FOR UPDATE; -- Verrouille la ligne
IF v_stock < v_quantite THEN
ROLLBACK;
SET p_erreur = CONCAT('Stock insuffisant pour le produit ', v_produit_id);
SET p_commande_id = NULL;
LEAVE;
END IF;
-- Ajouter la ligne de commande
INSERT INTO commande_lignes (commande_id, produit_id, quantite, prix_unitaire)
VALUES (p_commande_id, v_produit_id, v_quantite, v_prix);
-- Mettre a jour le stock
UPDATE produits
SET stock = stock - v_quantite
WHERE id = v_produit_id;
-- Calculer le sous-total
SET v_total = v_total + (v_prix * v_quantite);
SET v_index = v_index + 1;
END WHILE;
-- Mettre a jour le total de la commande
UPDATE commandes
SET total = v_total, statut = 'validee'
WHERE id = p_commande_id;
-- Valider la transaction
COMMIT;
SET p_erreur = NULL;
END //
DELIMITER ;
-- Utilisation
SET @commande_id = NULL;
SET @erreur = NULL;
CALL creer_commande(
1,
'[{"produit_id": 1, "quantite": 1}, {"produit_id": 3, "quantite": 2}]',
@commande_id,
@erreur
);
SELECT @commande_id AS commande_id, @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 apres_client;
-- Deuxieme operation
INSERT INTO commandes (client_id, numero, total)
VALUES (LAST_INSERT_ID(), 'CMD-TEST', 0);
SAVEPOINT apres_commande;
-- Troisieme operation qui echoue potentiellement
-- Si probleme, revenir au savepoint
ROLLBACK TO SAVEPOINT apres_commande;
-- Continuer avec d'autres operations
-- ...
COMMIT;
Niveaux d'isolation
-- Voir le niveau d'isolation actuel
SELECT @@transaction_isolation;
-- ou
SELECT @@tx_isolation; -- versions anciennes
-- Modifier le niveau d'isolation pour la session
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
| Niveau | Description | Problemes evites | Problemes possibles |
|---|---|---|---|
| READ UNCOMMITTED | Lit les donnees non validees | Aucun | Dirty reads, Non-repeatable reads, Phantom reads |
| READ COMMITTED | Lit uniquement les donnees validees | Dirty reads | Non-repeatable reads, Phantom reads |
| REPEATABLE READ | Lectures coherentes dans la transaction (defaut MySQL) | Dirty reads, Non-repeatable reads | Phantom reads |
| SERIALIZABLE | Isolation maximale | Tous | Performances reduites, deadlocks |
-- 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 (categorie_id, 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 performance_schema.data_locks;
-- Voir les attentes de verrous
SELECT * FROM performance_schema.data_lock_waits;
-- Informations sur InnoDB
SHOW ENGINE INNODB STATUSG
-- Requetes en attente
SELECT
r.trx_id AS waiting_trx_id,
r.trx_mysql_thread_id AS waiting_thread,
r.trx_query AS waiting_query,
b.trx_id AS blocking_trx_id,
b.trx_mysql_thread_id AS blocking_thread,
b.trx_query AS blocking_query
FROM information_schema.innodb_lock_waits w
INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id
INNER JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id;
Optimisation des requetes complexes
Strategies d'optimisation
-- 1. Utiliser EXPLAIN pour analyser
EXPLAIN FORMAT=JSON
SELECT
c.nom AS categorie,
COUNT(p.id) AS nb_produits,
AVG(p.prix) AS prix_moyen
FROM categories c
LEFT JOIN produits p ON c.id = p.categorie_id
GROUP BY c.id;
-- 2. Preferer EXISTS a IN pour les sous-requetes correlees
-- Moins performant
SELECT * FROM clients WHERE id IN (
SELECT client_id FROM commandes WHERE total > 500
);
-- Plus performant
SELECT * FROM clients cl WHERE EXISTS (
SELECT 1 FROM commandes cmd WHERE cmd.client_id = 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 prix_moyen FROM produits
)
SELECT
p.nom,
p.prix,
p.prix - s.prix_moyen AS ecart,
p.prix / s.prix_moyen AS ratio
FROM produits p, stats s;
-- 4. Limiter les colonnes selectionnees
-- Eviter SELECT * en production
SELECT id, nom, prix FROM produits WHERE categorie_id = 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.categorie_id
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
boutique_avancee > backup_transactionnel.sql
# Restauration
mysql -u root -p boutique_avancee < backup_transactionnel.sql
# Export d'une requete complexe
mysql -u root -p boutique_avancee -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.categorie_id
WHERE p.actif = TRUE
" > export_produits.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.