MySQL

Le système de gestion de base de données relationnelle open-source le plus populaire au monde, combinant performance, fiabilité et facilité d'utilisation.

Retour aux compétences Site officiel

Pour les non-initiés

Qu'est-ce que MySQL ?

Imaginez MySQL comme une bibliothèque parfaitement organisée capable de stocker d'énormes quantités d'informations. Cette bibliothèque ne se contente pas de conserver les données : elle permet aussi de les retrouver instantanément, de les mettre à jour ou de les organiser selon vos besoins.

MySQL est un système de gestion de base de données qui joue un rôle crucial dans le fonctionnement de la plupart des applications et sites web que vous utilisez quotidiennement. Il est l'endroit où sont stockées toutes les informations - des produits d'un site e-commerce aux messages sur un réseau social.

Pourquoi MySQL est si populaire ?

Performance

Traitez des millions de requêtes par seconde avec une réactivité exceptionnelle, même pour les applications à fort trafic.

Fiabilité

Bénéficiez d'une solution éprouvée depuis plus de 25 ans, utilisée par Facebook, Twitter, YouTube et des millions d'autres sites.

Simplicité

Facile à apprendre et à utiliser, même pour les débutants, tout en offrant des fonctionnalités avancées pour les experts.

Compatibilité universelle

Compatible avec presque tous les langages de programmation et systèmes d'exploitation (Windows, macOS, Linux).

En résumé, MySQL est comme le coffre-fort numérique qui permet à la plupart des sites et applications modernes de fonctionner efficacement. Sans lui, nous n'aurions pas nos réseaux sociaux, nos sites e-commerce, nos applications bancaires en ligne et bien d'autres services que nous utilisons quotidiennement.

Pour les développeurs

Fonctionnement technique

MySQL est un système de gestion de base de données relationnelle (SGBDR) open-source qui implémente le langage SQL (Structured Query Language). Il utilise un modèle client-serveur où plusieurs clients peuvent se connecter au serveur MySQL pour exécuter des requêtes sur les bases de données.

Les concepts fondamentaux

Structure des données

Dans MySQL, les données sont organisées en bases de données, contenant des tables qui elles-mêmes stockent les données en lignes et colonnes. Cette structure hiérarchique permet une organisation logique des informations.

Création de base de données et tables

-- Création d'une base de données
CREATE DATABASE ecommerce_db
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;

-- Sélection de la base de données
USE ecommerce_db;

-- Création d'une table pour les catégories
CREATE TABLE categories (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    slug VARCHAR(100) NOT NULL UNIQUE,
    description TEXT,
    parent_id INT DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (parent_id) REFERENCES categories(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- Création d'une table pour les produits
CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    category_id INT NOT NULL,
    name VARCHAR(255) NOT NULL,
    slug VARCHAR(255) NOT NULL UNIQUE,
    sku VARCHAR(50) NOT NULL UNIQUE,
    description TEXT,
    price DECIMAL(10, 2) NOT NULL,
    stock_quantity INT NOT NULL DEFAULT 0,
    status ENUM('draft', 'published', 'out_of_stock') DEFAULT 'draft',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE CASCADE,
    INDEX idx_product_status (status),
    INDEX idx_product_price (price)
) ENGINE=InnoDB;

-- Ajout d'un index composé
ALTER TABLE products
ADD INDEX idx_cat_status (category_id, status);

Requêtes SQL

MySQL utilise le langage SQL pour interagir avec les données. SQL permet d'effectuer des opérations CRUD (Create, Read, Update, Delete) ainsi que des requêtes plus complexes avec des jointures, agrégations et sous-requêtes.

Exemples de requêtes SQL

-- Insertion de données
INSERT INTO categories (name, slug, description)
VALUES 
    ('Électronique', 'electronique', 'Produits électroniques et gadgets'),
    ('Vêtements', 'vetements', 'Vêtements et accessoires de mode'),
    ('Maison', 'maison', 'Articles pour la maison et le jardin');

-- Insertion avec valeurs multiples
INSERT INTO products (category_id, name, slug, sku, description, price, stock_quantity, status)
VALUES 
    (1, 'Smartphone XYZ', 'smartphone-xyz', 'SP-001', 'Un smartphone puissant avec les dernières technologies.', 499.99, 50, 'published'),
    (1, 'Casque sans fil', 'casque-sans-fil', 'CS-002', 'Casque Bluetooth avec réduction de bruit active.', 129.99, 75, 'published'),
    (2, 'T-shirt coton bio', 't-shirt-coton-bio', 'TS-003', 'T-shirt en coton bio, coupe classique.', 29.99, 200, 'published');

-- Requête SELECT basique
SELECT id, name, price, stock_quantity
FROM products
WHERE status = 'published'
ORDER BY price DESC;

-- Requête avec jointure
SELECT p.id, p.name, p.price, c.name AS category_name
FROM products p
JOIN categories c ON p.category_id = c.id
WHERE p.price < 200
ORDER BY p.price ASC;

-- Requête avec agrégation
SELECT c.name AS category_name, 
       COUNT(p.id) AS product_count, 
       AVG(p.price) AS avg_price,
       MIN(p.price) AS min_price,
       MAX(p.price) AS max_price,
       SUM(p.stock_quantity) AS total_stock
FROM categories c
LEFT JOIN products p ON c.id = p.category_id
GROUP BY c.id
HAVING product_count > 0
ORDER BY product_count DESC;

-- Mise à jour de données
UPDATE products
SET price = price * 0.9, status = 'published'
WHERE category_id = 2 AND price > 50;

-- Suppression avec sous-requête
DELETE FROM products
WHERE category_id IN (
    SELECT id
    FROM categories
    WHERE name = 'Catégorie obsolète'
);

Moteurs de stockage

MySQL propose plusieurs moteurs de stockage, chacun avec ses propres caractéristiques :

InnoDB (par défaut) - Supporte les transactions ACID, les clés étrangères et le verrouillage au niveau ligne
MyISAM - Optimisé pour les environnements à forte lecture mais sans transactions
MEMORY - Stocke toutes les données en mémoire pour des accès ultra-rapides
ARCHIVE - Idéal pour stocker de grandes quantités de données historiques rarement consultées
CSV - Stocke les données dans des fichiers texte au format CSV

Fonctionnalités avancées

MySQL propose de nombreuses fonctionnalités avancées comme les transactions, les procédures stockées, les déclencheurs, les vues et les événements programmés.

Fonctionnalités avancées de MySQL

-- Création d'une table pour les commandes
CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    total_amount DECIMAL(10, 2) NOT NULL,
    status ENUM('pending', 'paid', 'shipped', 'delivered', 'canceled') DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- Création d'une table pour les éléments de commande
CREATE TABLE order_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    unit_price DECIMAL(10, 2) NOT NULL,
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE RESTRICT
) ENGINE=InnoDB;

-- Transactions
START TRANSACTION;

-- Insertion d'une nouvelle commande
INSERT INTO orders (user_id, total_amount, status)
VALUES (123, 659.97, 'paid');

SET @last_order_id = LAST_INSERT_ID();

-- Insertion des éléments de la commande
INSERT INTO order_items (order_id, product_id, quantity, unit_price)
VALUES
  (@last_order_id, 1, 1, 499.99),
  (@last_order_id, 2, 1, 129.99),
  (@last_order_id, 3, 1, 29.99);

-- Mise à jour du stock
UPDATE products
SET stock_quantity = stock_quantity - 1
WHERE id IN (1, 2, 3);

-- Si tout s'est bien passé, on valide la transaction
COMMIT;
-- En cas d'erreur: ROLLBACK;

-- Utilisation de vues
CREATE VIEW product_sales_view AS
SELECT 
    p.id,
    p.name,
    p.sku,
    SUM(oi.quantity) AS total_units_sold,
    SUM(oi.quantity * oi.unit_price) AS total_revenue
FROM products p
LEFT JOIN order_items oi ON p.id = oi.product_id
LEFT JOIN orders o ON oi.order_id = o.id
WHERE o.status IN ('paid', 'shipped', 'delivered')
GROUP BY p.id;

-- Requête utilisant la vue
SELECT * FROM product_sales_view
WHERE total_units_sold > 10
ORDER BY total_revenue DESC;

-- Procédure stockée
DELIMITER //
CREATE PROCEDURE GetProductStats(
    IN category_id_param INT,
    IN min_price_param DECIMAL(10, 2),
    IN max_price_param DECIMAL(10, 2)
)
BEGIN
    SELECT 
        p.id,
        p.name,
        p.price,
        p.stock_quantity,
        c.name AS category_name,
        IFNULL(pv.total_units_sold, 0) AS units_sold,
        IFNULL(pv.total_revenue, 0) AS revenue
    FROM products p
    JOIN categories c ON p.category_id = c.id
    LEFT JOIN product_sales_view pv ON p.id = pv.id
    WHERE 
        (category_id_param IS NULL OR p.category_id = category_id_param)
        AND p.price BETWEEN IFNULL(min_price_param, 0) AND IFNULL(max_price_param, 999999)
    ORDER BY p.price ASC;
END //
DELIMITER ;

-- Appel de la procédure stockée
CALL GetProductStats(1, 100.00, 500.00);

Optimisation des performances

MySQL offre de nombreuses possibilités d'optimisation pour garantir des performances élevées même avec de grandes quantités de données ou un nombre important d'utilisateurs simultanés.

Techniques d'optimisation MySQL

-- 1. Indexation appropriée
-- Ajout d'un index pour les recherches fréquentes
ALTER TABLE products
ADD INDEX idx_name_price (name, price);

-- 2. Optimisation des requêtes
-- Avant optimisation
SELECT p.*, c.name AS category_name
FROM products p
JOIN categories c ON p.category_id = c.id
WHERE p.price > 100;

-- Après optimisation (sélection spécifique des colonnes)
SELECT p.id, p.name, p.price, p.status, c.name AS category_name
FROM products p
JOIN categories c ON p.category_id = c.id
WHERE p.price > 100;

-- 3. EXPLAIN pour analyser les requêtes
EXPLAIN SELECT p.id, p.name, p.price
FROM products p
WHERE p.price > 100 AND p.status = 'published';

-- 4. Partitionnement de tables
-- Partitionnement par plage de dates pour une table d'historique
CREATE TABLE order_history (
    id INT NOT NULL,
    order_id INT NOT NULL,
    status VARCHAR(50) NOT NULL,
    updated_at TIMESTAMP NOT NULL,
    PRIMARY KEY (id, updated_at)
)
PARTITION BY RANGE (UNIX_TIMESTAMP(updated_at)) (
    PARTITION p_2022_q1 VALUES LESS THAN (UNIX_TIMESTAMP('2022-04-01')),
    PARTITION p_2022_q2 VALUES LESS THAN (UNIX_TIMESTAMP('2022-07-01')),
    PARTITION p_2022_q3 VALUES LESS THAN (UNIX_TIMESTAMP('2022-10-01')),
    PARTITION p_2022_q4 VALUES LESS THAN (UNIX_TIMESTAMP('2023-01-01')),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

-- 5. Configuration du serveur MySQL (my.cnf ou my.ini)
/*
[mysqld]
# Cache et mémoire
innodb_buffer_pool_size = 1G
innodb_log_file_size = 256M
query_cache_size = 64M

# Paramètres de connexion
max_connections = 200
thread_cache_size = 16

# Optimisation des écritures
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT

# Réglage de performance des requêtes
sort_buffer_size = 4M
join_buffer_size = 2M

# Paramètres d'optimisation pour les applications web
max_allowed_packet = 64M
*/

Intégration avec PHP

MySQL est particulièrement bien intégré avec PHP, formant le socle de nombreuses applications web. L'utilisation de PDO (PHP Data Objects) est recommandée pour une interaction sécurisée et efficace, souvent combinée avec Doctrine dans les projets Symfony.

Interaction MySQL avec PHP via PDO

<?php
// Connexion à MySQL en PDO
function connectToDatabase() {
    try {
        $host = 'localhost';
        $dbname = 'ecommerce_db';
        $username = 'db_user';
        $password = 'db_password';
        
        $dsn = "mysql:host={$host};dbname={$dbname};charset=utf8mb4";
        
        $options = [
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
            PDO::ATTR_EMULATE_PREPARES => false,
        ];
        
        $pdo = new PDO($dsn, $username, $password, $options);
        
        return $pdo;
    } catch (PDOException $e) {
        // Log l'erreur sans exposer les détails sensibles
        error_log('Erreur de connexion à la base de données: ' . $e->getMessage());
        throw new Exception('Erreur de connexion à la base de données');
    }
}

// Exemple d'utilisation: récupération des produits d'une catégorie
function getProductsByCategory($categoryId, $limit = 10, $offset = 0) {
    try {
        $pdo = connectToDatabase();
        
        $query = "
            SELECT p.id, p.name, p.price, p.description, p.slug
            FROM products p
            WHERE p.category_id = :category_id AND p.status = 'published'
            ORDER BY p.created_at DESC
            LIMIT :limit OFFSET :offset
        ";
        
        $stmt = $pdo->prepare($query);
        $stmt->bindParam(':category_id', $categoryId, PDO::PARAM_INT);
        $stmt->bindParam(':limit', $limit, PDO::PARAM_INT);
        $stmt->bindParam(':offset', $offset, PDO::PARAM_INT);
        $stmt->execute();
        
        return $stmt->fetchAll();
    } catch (PDOException $e) {
        error_log('Erreur lors de la récupération des produits: ' . $e->getMessage());
        throw new Exception('Erreur lors de la récupération des produits');
    }
}

// Exemple d'insertion sécurisée
function addProduct($data) {
    try {
        $pdo = connectToDatabase();
        
        $query = "
            INSERT INTO products 
            (category_id, name, slug, sku, description, price, stock_quantity, status)
            VALUES 
            (:category_id, :name, :slug, :sku, :description, :price, :stock_quantity, :status)
        ";
        
        $stmt = $pdo->prepare($query);
        $stmt->execute([
            ':category_id' => $data['category_id'],
            ':name' => $data['name'],
            ':slug' => $data['slug'],
            ':sku' => $data['sku'],
            ':description' => $data['description'],
            ':price' => $data['price'],
            ':stock_quantity' => $data['stock_quantity'],
            ':status' => $data['status'] ?? 'draft'
        ]);
        
        return $pdo->lastInsertId();
    } catch (PDOException $e) {
        error_log('Erreur lors de l'ajout du produit: ' . $e->getMessage());
        throw new Exception('Erreur lors de l'ajout du produit');
    }
}

// Exemple de transaction
function createOrder($userId, $items) {
    try {
        $pdo = connectToDatabase();
        $pdo->beginTransaction();
        
        // Calcul du montant total
        $totalAmount = 0;
        foreach ($items as $item) {
            // Récupération du prix actuel du produit
            $stmt = $pdo->prepare("SELECT price FROM products WHERE id = ? AND status = 'published'");
            $stmt->execute([$item['product_id']]);
            $product = $stmt->fetch();
            
            if (!$product) {
                throw new Exception("Produit non disponible");
            }
            
            $totalAmount += $product['price'] * $item['quantity'];
        }
        
        // Insertion de la commande
        $stmt = $pdo->prepare("INSERT INTO orders (user_id, total_amount, status) VALUES (?, ?, 'pending')");
        $stmt->execute([$userId, $totalAmount]);
        $orderId = $pdo->lastInsertId();
        
        // Insertion des éléments de la commande
        $stmt = $pdo->prepare("INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES (?, ?, ?, ?)");
        
        foreach ($items as $item) {
            $stmt->execute([
                $orderId,
                $item['product_id'],
                $item['quantity'],
                $product['price']
            ]);
            
            // Mise à jour du stock
            $updateStmt = $pdo->prepare("UPDATE products SET stock_quantity = stock_quantity - ? WHERE id = ?");
            $updateStmt->execute([$item['quantity'], $item['product_id']]);
        }
        
        // Si tout s'est bien passé, on valide la transaction
        $pdo->commit();
        
        return $orderId;
    } catch (Exception $e) {
        // En cas d'erreur, on annule toutes les modifications
        $pdo->rollBack();
        error_log('Erreur lors de la création de la commande: ' . $e->getMessage());
        throw new Exception('Erreur lors de la création de la commande');
    }
}

Administration et sécurité

Contrôle d'accès - Système de privilèges granulaire avec utilisateurs, rôles et permissions
Encryption - Support du chiffrement des données au repos et en transit (SSL/TLS)
Auditing - Journalisation des activités et requêtes pour analyse de sécurité
Sauvegardes - Outils natifs pour les sauvegardes logiques (mysqldump) et physiques, souvent automatisés avec AWS Lambda
Réplication - Possibilité de créer des copies synchronisées de vos données pour la haute disponibilité avec AWS EC2
Clustering - MySQL Cluster pour les déploiements nécessitant une disponibilité maximale, généralement avec Docker

Applications concrètes

Cas d'usage

Applications Web

MySQL est le socle de millions de sites et applications web, des blogs personnels aux réseaux sociaux géants comme Facebook, en passant par des CMS comme WordPress et des frameworks comme Laravel ou Symfony.

E-commerce

Les plateformes de commerce électronique comme Magento, WooCommerce ou Shopify utilisent MySQL pour gérer leurs catalogues de produits, commandes, inventaires et transactions.

Analytique et business intelligence

MySQL sert de base à de nombreux systèmes d'analyse de données, tableaux de bord et outils de reporting pour les entreprises, grâce à ses performances en lecture et ses capacités analytiques.

Applications SaaS

De nombreux logiciels en tant que service (SaaS) comme Slack, Zendesk ou Mailchimp utilisent MySQL pour stocker les données de leurs clients de manière sécurisée et performante.