SQL & MySQL

Quand MySQL cherche une ligne sans index, il parcourt TOUTES les lignes de la table séquentiellement — c'est un "full table scan". Sur 100 lignes, c'est invisible (0.001 seconde). Sur 100 000 lignes, c'est 0.5 seconde. Sur 10 millions de lignes, c'est 5 secondes — inacceptable pour un site web où chaque page doit se charger en moins de 200 millisecondes. Les index résolvent ce problème.

Comment fonctionne un index — l'analogie du livre

Imaginez un annuaire téléphonique de 1 000 pages. Pour trouver "Diop", sans index vous parcourez chaque page du début à la fin — 1 000 opérations. Avec l'index alphabétique, vous ouvrez directement à la lettre D, puis vous affinez — 10 opérations. C'est 100 fois plus rapide.

MySQL crée une structure de données appelée B-tree (balanced tree — arbre équilibré) sur les valeurs indexées. C'est un arbre trié qui permet de trouver n'importe quelle valeur en un nombre logarithmique de comparaisons. Sur 1 million de lignes : ~20 comparaisons au lieu de 1 million. Sur 100 millions : ~27 comparaisons. La différence de performance est spectaculaire.

Types d'index en MySQL

B-tree (par défaut) — le plus courant. Fonctionne avec =, <, >, <=, >=, BETWEEN, LIKE 'prefix%' (mais PAS LIKE '%suffix'). C'est le type créé par CREATE INDEX.

Hash — uniquement pour les comparaisons d'égalité (=). Plus rapide que B-tree pour l'égalité, mais ne supporte pas les comparaisons de plage (<, >, BETWEEN). Disponible uniquement pour les tables MEMORY.

Full-text — pour la recherche en texte libre. CREATE FULLTEXT INDEX idx_ft ON products(name, description); puis SELECT * FROM products WHERE MATCH(name, description) AGAINST('wax coton' IN BOOLEAN MODE);. Bien plus performant que LIKE '%wax%' sur de grosses tables.

Les clés primaires (PRIMARY KEY) et les contraintes UNIQUE créent automatiquement un index B-tree. C'est pourquoi WHERE id = 5 est toujours instantané.

Créer un index

Index simple : CREATE INDEX idx_products_price ON products(price);

Index composite (multi-colonnes) : CREATE INDEX idx_products_active_cat ON products(is_active, category_id, price);

L'index composite est le plus puissant mais a une règle cruciale : la leftmost prefix rule. L'index (is_active, category_id, price) accélère les requêtes qui filtrent par :

• is_active seul ✓
• is_active + category_id ✓
• is_active + category_id + price ✓
• category_id seul ✗ (la première colonne doit être dans le WHERE)
• price seul ✗

L'ordre des colonnes dans l'index est donc stratégique : mettez en premier la colonne avec la plus forte sélectivité (celle qui filtre le plus de lignes).

Index unique : CREATE UNIQUE INDEX idx_email ON customers(email);

Supprimer : DROP INDEX idx_products_price ON products;

EXPLAIN — radiographier une requête

EXPLAIN est votre outil de diagnostic. Il montre comment MySQL PLANIFIE l'exécution d'une requête sans l'exécuter réellement.

EXPLAIN SELECT * FROM products WHERE category_id = 1 AND is_active = TRUE;

Les colonnes clés du résultat EXPLAIN :

• type — comment MySQL accède aux données :

  • const : recherche par clé primaire d'une seule ligne (WHERE id = 5) → le plus rapide
  • eq_ref : jointure par clé primaire (chaque ligne de la table gauche correspond à exactement 1 ligne de la droite) → très rapide
  • ref : recherche par index non-unique (WHERE category_id = 1) → rapide
  • range : scan partiel d'un index (WHERE price BETWEEN 5000 AND 20000) → correct
  • index : scan complet de l'index (pas de la table, mais tout l'index) → moyen
  • ALL : full table scan (parcours de TOUTE la table) → LENT, à corriger

• key — quel index MySQL utilise. NULL = aucun index utilisé.

• rows — nombre ESTIMÉ de lignes à examiner. Moins c'est mieux. Si rows = le nombre total de lignes de la table, c'est un full scan.

• Extra — informations complémentaires :

  • "Using where" : MySQL filtre après avoir lu les lignes
  • "Using index" : les données sont lues directement depuis l'index sans toucher la table (index couvrant) → optimal
  • "Using filesort" : MySQL doit trier les résultats en mémoire (ORDER BY sur une colonne non indexée) → peut être lent
  • "Using temporary" : MySQL crée une table temporaire (GROUP BY complexe) → peut être lent

ANALYZE TABLE — mettre à jour les statistiques

MySQL utilise des statistiques internes pour décider quel index utiliser. Après beaucoup d'insertions/suppressions, ces statistiques peuvent être obsolètes. ANALYZE TABLE products; les recalcule.

Quand indexer — et quand NE PAS indexer

Indexez les colonnes utilisées dans :

• WHERE (filtres fréquents) : category_id, status, is_active
• JOIN ON (conditions de jointure) : customer_id, order_id, product_id
• ORDER BY (tri) : created_at, price
• GROUP BY (regroupement) : status, YEAR(created_at)

N'indexez PAS :

• Les très petites tables (<1000 lignes) — le full scan est déjà instantané
• Les colonnes avec très peu de valeurs distinctes — un BOOLEAN (TRUE/FALSE) n'a que 2 valeurs, l'index pointe vers la moitié de la table et n'apporte rien
• Les colonnes massivement modifiées — chaque INSERT/UPDATE reconstruit l'index, sur une table de logs avec des millions d'insertions par jour, les index ralentissent les écritures significativement
• Les colonnes utilisées avec LIKE '%...%' — un LIKE qui commence par % ne peut PAS utiliser un index B-tree (il faudrait un index FULLTEXT)

Le compromis : chaque index occupe de l'espace disque et ralentit les INSERT/UPDATE/DELETE (MySQL doit maintenir l'index à jour). C'est un échange : lecture rapide contre écriture ralentie. Idéal pour les tables lues souvent et modifiées rarement (catalogue produits). Mauvais pour les tables massivement écrites (logs, métriques temps réel).