Journal
Données & Infra MySQL

MySQL 8 : index composites, EXPLAIN ANALYZE et pièges de transactions

Journal du lundi 24 août 2026, généré le 24 août à 09:55

L'ordre des colonnes dans un index composite change tout

Un index sur (a, b, c) ne sert pas les requêtes qui filtrent uniquement sur b ou c : MySQL utilise l'index de gauche à droite, en s'arrêtant à la première colonne absente du filtre. La règle pratique reste égalité avant plage : place les colonnes filtrées avec = en premier, puis celle utilisée avec >, < ou BETWEEN en dernier.

-- Index utile pour : WHERE tenant_id = ? AND status = ? AND created_at > ?
CREATE INDEX idx_orders_lookup
  ON orders (tenant_id, status, created_at);

Ajouter created_at avant status casserait l'égalité sur status, forçant un scan partiel de l'index.

Vérifier avec EXPLAIN ANALYZE, pas seulement EXPLAIN

EXPLAIN donne un plan théorique basé sur des statistiques ; EXPLAIN ANALYZE (disponible depuis MySQL 8.0.18) exécute réellement la requête et affiche les coûts et le nombre de lignes mesurés à chaque étape. C'est l'outil à privilégier quand un index existe mais que l'optimiseur choisit quand même un full table scan — souvent un signe de statistiques périmées (ANALYZE TABLE) ou de faible sélectivité.

Le piège classique : REPEATABLE READ et les lectures fantômes évitées, mais pas tout

MySQL/InnoDB utilise REPEATABLE READ par défaut, plus strict que le standard SQL grâce aux next-key locks qui bloquent les insertions dans les plages verrouillées. Mais un SELECT simple lit un instantané cohérent (MVCC), alors qu'un SELECT ... FOR UPDATE lit les données les plus récentes et pose des verrous. Mélanger les deux dans la même transaction est une source classique de bugs difficiles à reproduire :

START TRANSACTION;
SELECT stock FROM products WHERE id = 42;          -- snapshot, peut être obsolète
SELECT stock FROM products WHERE id = 42 FOR UPDATE; -- valeur fraîche, verrouillée
UPDATE products SET stock = stock - 1 WHERE id = 42;
COMMIT;

Pour une décrémentation, éviter le read-then-write et préférer une opération atomique : UPDATE products SET stock = stock - 1 WHERE id = 42 AND stock > 0;, puis vérifier ROW_COUNT().

Colonnes JSON : indexer via une colonne générée

MySQL ne permet pas d'indexer directement un chemin JSON, mais une colonne générée virtuelle le rend possible :

ALTER TABLE events
  ADD COLUMN user_id INT
    GENERATED ALWAYS AS (payload->>'$.user_id') STORED,
  ADD INDEX idx_events_user (user_id);

Cela évite de scanner et parser du JSON à chaque requête, tout en gardant un schéma flexible pour le reste du document.

À vérifier régulièrement

  • SHOW ENGINE INNODB STATUS pour repérer les deadlocks récents.
  • performance_schema pour identifier les requêtes les plus coûteuses en production.
  • La taille du innodb_buffer_pool_size par rapport au volume de données réellement actif.