🏗️ ALTER TABLE — Modifier la structure

Ajouter / Modifier / Supprimer des colonnes

-- Ajouter une colonne
ALTER TABLE clients ADD COLUMN telephone VARCHAR(20) NULL;
ALTER TABLE clients ADD COLUMN deleted_at DATETIME NULL AFTER email;

-- Modifier le type ou les options d'une colonne
ALTER TABLE clients MODIFY COLUMN ville VARCHAR(150) NOT NULL DEFAULT 'non_renseigne';

-- Renommer une colonne (MySQL 8.0+)
ALTER TABLE produits RENAME COLUMN poids_kg TO poids;

-- Supprimer une colonne
ALTER TABLE clients DROP COLUMN telephone;

-- Plusieurs modifications en une seule requête (atomique)
ALTER TABLE produits
  ADD COLUMN description TEXT NULL,
  MODIFY COLUMN prix DECIMAL(12, 2) NOT NULL,
  ADD INDEX idx_nom (nom);

Renommer une table

RENAME TABLE produits TO articles;
-- ou
ALTER TABLE produits RENAME TO articles;

🚀 Index — Accélérer les requêtes

Un index est une structure de données (B-Tree par défaut) qui permet à MySQL de trouver des lignes sans scanner toute la table.

-- Créer un index simple
CREATE INDEX idx_produits_prix ON produits(prix);

-- Index UNIQUE (interdit les doublons)
CREATE UNIQUE INDEX idx_clients_email ON clients(email);

-- Index composite (multi-colonnes)
CREATE INDEX idx_commandes_client_statut ON commandes(client_id, statut);

-- Index FULLTEXT (pour la recherche textuelle)
CREATE FULLTEXT INDEX idx_produits_nom ON produits(nom, description);

-- Supprimer un index
DROP INDEX idx_produits_prix ON produits;

-- Voir les index d'une table
SHOW INDEX FROM produits;

📋 Types d'index

Règle du préfixe gauche

-- Index composite : (client_id, statut)
CREATE INDEX idx ON commandes(client_id, statut);

-- UTILISE l'index (préfixe gauche respecté)
SELECT * FROM commandes WHERE client_id = 1;
SELECT * FROM commandes WHERE client_id = 1 AND statut = 'livree';

-- N'UTILISE PAS l'index (col gauche absente)
SELECT * FROM commandes WHERE statut = 'livree';

🔬 EXPLAIN — Analyser les requêtes

EXPLAIN affiche le plan d'exécution sans exécuter la requête.

EXPLAIN SELECT * FROM produits WHERE prix > 500;
EXPLAIN SELECT p.nom, c.nom FROM produits p JOIN categories c ON p.categorie_id = c.id;

-- EXPLAIN ANALYZE (MySQL 8.0.18+) : exécute ET mesure
EXPLAIN ANALYZE SELECT * FROM produits WHERE categorie_id = 1;

⚡ Règles d'optimisation

• Indexez les colonnes de WHERE, JOIN ON et ORDER BY fréquemment utilisées
• Évitez les fonctions sur les colonnes indexées dans WHERE : WHERE YEAR(created_at) = 2024 n'utilise pas l'index sur created_at. Utilisez WHERE created_at BETWEEN '2024-01-01' AND '2024-12-31'
• Limitez le nombre d'index : chaque index ralentit les INSERT/UPDATE/DELETE
• Utilisez DECIMAL pour les montants, pas FLOAT
• Évitez SELECT * : sélectionnez seulement les colonnes nécessaires

-- Mauvais : empêche l'utilisation de l'index
SELECT * FROM commandes WHERE YEAR(date_commande) = 2024;

-- Bon : l'index sur date_commande peut être utilisé
SELECT * FROM commandes
WHERE date_commande >= '2024-01-01'
  AND date_commande < '2025-01-01';

🔍 Introspection de la base

-- Structure d'une table
DESCRIBE produits;
SHOW COLUMNS FROM produits;

-- DDL complet (CREATE TABLE avec tous les index et FK)
SHOW CREATE TABLE commandes;

-- Tous les index d'une table
SHOW INDEX FROM produits;

-- Tables et leur statut (taille, moteur, lignes...)
SHOW TABLE STATUS FROM formation_mysql;

-- Variables de configuration
SHOW VARIABLES LIKE '%innodb%';
SHOW VARIABLES LIKE 'max_connections';

-- Processus en cours
SHOW PROCESSLIST;