LEFT JOIN lent sous MySQL : index, plan d'exécution et réécritures

Optimiser une requête MySQL avec LEFT OUTER JOIN commence par indexer la clé de jointure de la table de droite. Le résultat d'un LEFT JOIN garde chaque ligne de la table de gauche, avec la valeur NULL quand aucune donnée ne correspond ; sans index, la base relit la même table pour chaque ligne.

Ce que fait réellement une jointure externe gauche

Une jointure externe gauche (LEFT JOIN ou LEFT OUTER JOIN, le mot clé OUTER étant facultatif dans MySQL) renvoie toutes les lignes de la table de gauche, qu'elles aient ou non une correspondance dans la table de droite. Quand aucune correspondance n'existe, chaque colonne issue de la table de droite reçoit la valeur NULL. C'est la différence avec la jointure interne (INNER JOIN), qui ne garde que les couples de lignes satisfaisant la condition de jointure.

Exemple classique sur une base de vente : lister tous les clients avec leurs commandes, y compris ceux qui n'ont jamais commandé.

SELECT c.id, c.nom, co.date_commande
FROM clients c
LEFT OUTER JOIN commandes co ON co.client_id = c.id
WHERE c.actif = 1;

Deux conséquences pratiques comptent pour la performance. D'abord, la table de gauche est la table « pilote » : MySQL la parcourt et, pour chaque ligne, va chercher les lignes de droite. L'optimiseur peut réordonner librement les tables d'un INNER JOIN, mais une jointure externe lui impose en principe de lire la table de gauche avant celle de droite. Ensuite, une relation un-à-plusieurs multiplie les lignes : un client qui a passé quarante commandes apparaît quarante fois dans le résultat. Une requête qui « ralentit avec le temps » souffre souvent de ce seul phénomène, la volumétrie de la table de droite grossissant plus vite que celle de gauche.

Les types de jointures en SQL et leur comportement dans MySQL

Les types de jointures en SQL se distinguent par les lignes qu'ils conservent quand la condition échoue. MySQL n'implémente pas tous ceux de la norme : il ne connaît pas FULL OUTER JOIN, qu'on émule par l'union d'un LEFT JOIN et d'un RIGHT JOIN. Il convertit par ailleurs chaque RIGHT JOIN en LEFT JOIN équivalent avant de l'optimiser, si bien que le choix entre les deux relève de la lisibilité, pas de la vitesse.

L'émulation du full outer join se écrit avec UNION, qui élimine les doublons, ou UNION ALL complété d'un filtre IS NULL sur la seconde branche, plus rapide parce qu'il évite le dédoublonnage dans une table temporaire :

SELECT a.id, b.id FROM a LEFT JOIN b ON b.a_id = a.id
UNION ALL
SELECT a.id, b.id FROM a RIGHT JOIN b ON b.a_id = a.id
WHERE a.id IS NULL;

Le self join sert aux hiérarchies (un employé et son responsable dans la même table) ; il se traite comme une jointure entre deux tables distinctes, avec un index sur la colonne qui porte la référence.

Lire le plan d'exécution avant de modifier quoi que ce soit

Le plan d'exécution d'une requête MySQL s'obtient en plaçant EXPLAIN devant le SELECT. Chaque table occupe une ligne du résultat, dans l'ordre où le serveur la lit, et quatre colonnes suffisent pour un premier diagnostic : type, key, rows et Extra. Une ligne de la table de droite en type ALL avec key à NULL révèle le problème le plus courant : aucun index n'est utilisé pour la jointure.

La colonne rows donne une estimation du nombre de lignes examinées pour chaque ligne de la table précédente : il faut multiplier les valeurs entre elles pour approcher le coût réel. Dans Extra, la mention « Using join buffer (hash join) » signale que MySQL a construit une table de hachage en mémoire faute d'index exploitable.

Depuis MySQL 8.0.18, EXPLAIN ANALYZE exécute vraiment la requête et affiche, pour chaque étape, le temps passé et le nombre de lignes réellement produites. L'écart entre lignes estimées et lignes réelles désigne des statistiques périmées, que ANALYZE TABLE recalcule. Sur MariaDB, l'équivalent s'appelle ANALYZE suivi de la requête, avec une sortie différente.

Indexer la colonne de jointure de la table de droite

L'index sur la colonne de jointure de la table de droite est la correction qui rapporte le plus, parce qu'il remplace un parcours de table par une lecture ciblée pour chaque ligne de gauche. Dans l'exemple des clients, c'est commandes.client_id qu'il faut indexer, pas clients.id, déjà couvert par la clé primaire :

ALTER TABLE commandes ADD INDEX idx_client (client_id);

Sur InnoDB, l'ajout d'un index secondaire se fait en ligne (ALGORITHM=INPLACE) : les écritures continuent pendant la construction, mais l'opération consomme des entrées-sorties et de l'espace disque. Une clé étrangère déclarée crée d'ailleurs cet index automatiquement ; son absence trahit souvent une base migrée depuis MyISAM, moteur qui ignorait les contraintes. Les différences entre les moteurs MyISAM, InnoDB et MEMORY pèsent aussi sur le verrouillage pendant ces opérations.

Quand la requête filtre ou trie aussi sur une autre colonne de la table de droite, un index composite sert les deux à la fois. Pour joindre les commandes d'un client et ne garder que la dernière par date, (client_id, date_commande) permet à MySQL de lire les lignes déjà triées. L'ordre des colonnes compte : la colonne de jointure vient en tête, la colonne de tri ou de plage ensuite.

Dernier cran, l'index couvrant : s'il contient toutes les colonnes de la table de droite citées dans la requête, le serveur répond depuis l'index seul sans toucher aux données de la table, ce qu'EXPLAIN signale par « Using index ». Avec InnoDB, la clé primaire figure implicitement dans chaque index secondaire et n'a pas besoin d'y être répétée.

Aligner les types, les interclassements et éviter les fonctions

Un index sur la colonne de jointure ne sert à rien si MySQL doit convertir chaque valeur avant de comparer. Trois situations produisent cet effet :

• Types différents : une jointure entre un INT et un VARCHAR force une conversion implicite ; l'index du côté converti devient inutilisable.
• Interclassements différents : deux colonnes texte en utf8mb4_0900_ai_ci d'un côté et utf8mb4_unicode_ci ou latin1 de l'autre obligent le serveur à convertir, quand elles ne déclenchent pas l'erreur « Illegal mix of collations ».
• Fonction autour de la colonne : une condition comme DATE(co.cree_le) = c.date_inscription ou LOWER(co.email) = c.email masque la valeur brute à l'index.

Le correctif consiste à harmoniser les définitions de colonnes (ALTER TABLE … MODIFY), ou à réécrire la condition en plage : co.cree_le >= c.date_inscription AND co.cree_le < c.date_inscription + INTERVAL 1 DAY. Depuis MySQL 8.0.13, un index fonctionnel sur l'expression elle-même reste une option quand la réécriture est impossible. Ces écarts apparaissent souvent après une migration ou une importation de données, quand des tables créées à des époques différentes portent des jeux de caractères par défaut différents.

Placer chaque condition au bon endroit, entre ON et WHERE

La position d'une condition sur la table de droite change le sens d'une requête LEFT JOIN, et donc son plan. Le manuel de MySQL décrit la « simplification des jointures externes » : une condition du WHERE qui rejette les lignes complétées par NULL, comme co.statut = 'payee', transforme la jointure externe en jointure interne. Les clients sans commande disparaissent du résultat, ce qui ressemble à un bogue mais correspond exactement à ce qui a été écrit.

Pour garder tous les clients et ne joindre que les commandes payées, la condition va dans la clause ON :

SELECT c.id, c.nom, co.id AS commande
FROM clients c
LEFT JOIN commandes co
  ON co.client_id = c.id AND co.statut = 'payee';

À l'inverse, une condition sur la table de gauche se place dans la clause WHERE : écrite dans le ON, elle ne filtre rien, puisque la jointure externe garde de toute façon chaque ligne de gauche, et elle se contente de vider les colonnes de droite. Quand les lignes sans correspondance ne servent à rien, un INNER JOIN explicite est plus lisible et laisse l'optimiseur choisir librement l'ordre des tables, ce qui peut diviser le temps de réponse quand la table de droite est la plus sélective.

Avant de déplacer une condition, comparer le nombre de lignes renvoyées dans les deux versions : une optimisation qui change le résultat n'en est pas une.

Chercher les absents avec NOT EXISTS plutôt qu'avec IS NULL

Le motif « LEFT JOIN puis WHERE colonne_de_droite IS NULL » sert à trouver les lignes de gauche sans correspondance : clients qui n'ont jamais commandé, produits jamais vendus, comptes sans connexion. NOT EXISTS exprime la même chose plus clairement :

SELECT c.id, c.nom
FROM clients c
WHERE NOT EXISTS (
  SELECT 1 FROM commandes co WHERE co.client_id = c.id
);

Depuis MySQL 8.0.17, l'optimiseur transforme NOT EXISTS et NOT IN en antijointure, une opération qui s'arrête dès la première correspondance trouvée au lieu de lire toutes les lignes de droite. Les deux formes aboutissent souvent au même plan sur une version récente ; NOT EXISTS garde l'avantage de ne dépendre d'aucune colonne NOT NULL. Avec le motif IS NULL, il faut tester une colonne qui ne peut jamais valoir NULL dans la table de droite (sa clé primaire, typiquement), sinon une ligne existante dont la colonne est vide passe pour absente.

NOT IN réserve un piège connu : si la sous-requête renvoie une seule valeur NULL, la condition n'est jamais vraie et la requête ne renvoie aucune ligne. NOT EXISTS n'a pas ce défaut.

Éviter que la jointure ne multiplie les lignes

Une jointure LEFT JOIN sur une relation un-à-plusieurs renvoie une ligne par correspondance, et c'est la cause d'un grand nombre de requêtes lentes ou fausses. Les symptômes : un SELECT DISTINCT ajouté pour « enlever les doublons », un GROUP BY sur toutes les colonnes de gauche, ou des sommes gonflées quand deux tables de droite sont jointes en même temps (chaque commande multipliée par chaque paiement).

Le remède consiste à agréger la table de droite avant la jointure, dans une table dérivée :

SELECT c.id, c.nom, t.nb, t.total
FROM clients c
LEFT JOIN (
  SELECT client_id, COUNT(*) AS nb, SUM(montant) AS total
  FROM commandes GROUP BY client_id
) t ON t.client_id = c.id;

Pour ne garder que la dernière commande de chaque client, MySQL 8.0.14 et suivants acceptent une table dérivée LATERAL, qui peut lire les colonnes de la table de gauche ; avec l'index composite (client_id, date_commande), chaque recherche se résume à une lecture d'index :

SELECT c.id, c.nom, d.date_commande
FROM clients c
LEFT JOIN LATERAL (
  SELECT date_commande FROM commandes co
  WHERE co.client_id = c.id
  ORDER BY date_commande DESC LIMIT 1
) d ON TRUE;

Sur une version plus ancienne, une fonction de fenêtre ROW_NUMBER() (disponible depuis MySQL 8.0) dans une sous-requête donne le même résultat.

Hash join, tampon de jointure et mémoire

Le hash join est l'algorithme que MySQL emploie depuis la version 8.0.18 pour les jointures d'égalité sans index exploitable ; depuis la 8.0.20, il couvre aussi les jointures externes, les semi-jointures et les antijointures, et remplace l'ancien algorithme « block nested loop ». Le serveur construit une table de hachage sur la plus petite entrée, puis la sonde avec l'autre. C'est bien plus rapide qu'une boucle imbriquée sans index, mais cela reste un parcours complet de la table de droite à chaque exécution.

La taille de cette table de hachage est bornée par la variable join_buffer_size (256 Ko par défaut). Au-delà, MySQL déborde sur disque en fichiers temporaires. Augmenter la valeur pour une session précise peut aider un traitement par lots ; l'augmenter globalement est risqué, car chaque connexion peut allouer ce tampon plusieurs fois. Un index adapté reste la solution durable : la jointure de hachage est un filet de sécurité, pas une stratégie.

Les tables temporaires internes posent le même genre de limite. Un GROUP BY ou un DISTINCT sur le résultat d'une grosse jointure en crée une ; si elle dépasse tmp_table_size, ou si elle doit stocker des colonnes TEXT ou BLOB, elle passe sur disque. C'est l'un des arguments pour ne pas ramener un document JSON stocké en BLOB dans une requête de jointure qui n'en a pas besoin.

Ne ramener que les colonnes et les lignes utiles

Un SELECT * sur une jointure ramène toutes les colonnes des deux tables, y compris les colonnes volumineuses, ce qui alourdit les tables temporaires, le réseau et la mémoire de l'application PHP qui reçoit les données. Lister les colonnes nécessaires permet en outre au serveur d'utiliser un index couvrant.

La pagination appelle la même discipline. Un LIMIT 20 OFFSET 50000 sur une jointure oblige MySQL à construire 50 020 lignes pour en jeter 50 000. Deux techniques réduisent ce coût :

1. paginer par clé (« les 20 clients dont l'id dépasse le dernier affiché »), ce qui transforme l'OFFSET en lecture d'index ;
2. paginer d'abord la table de gauche dans une sous-requête, puis faire la jointure sur les seules lignes retenues.

Un ORDER BY sur une colonne de la table de droite empêche souvent l'usage d'un index pour le tri, puisque les lignes complétées par NULL n'existent dans aucun index. Trier sur une colonne de la table de gauche, quand le besoin fonctionnel le permet, évite un tri complet en mémoire ou sur disque ; le même raisonnement vaut pour un ORDER BY RAND() sur une grosse table, autre grand consommateur de tris inutiles.

Quand l'optimiseur se trompe d'ordre

L'optimiseur de MySQL choisit l'ordre de lecture des tables à partir de statistiques ; sur une requête qui enchaîne plusieurs jointures, il lui arrive de partir d'une table volumineuse alors qu'une autre aurait filtré plus tôt. Avant toute intervention, ANALYZE TABLE sur les tables concernées remet les statistiques d'index à jour ; depuis MySQL 8.0, ANALYZE TABLE … UPDATE HISTOGRAM ON colonne ajoute un histogramme sur une colonne non indexée dont la distribution est très inégale.

Si le plan reste mauvais, les indications d'optimiseur forcent la décision : JOIN_ORDER, JOIN_PREFIX ou le mot clé STRAIGHT_JOIN fixent l'ordre des tables, et INDEX ou FORCE INDEX imposent un index. Ces indications figent un plan qui peut devenir mauvais quand les données évoluent : les documenter dans le code et les revoir à chaque montée de version.

Sauvegarder et vérifier avant la mise en production

Toute optimisation d'une requête LEFT OUTER JOIN se valide sur une copie des données de production, pas sur une base de test à quelques centaines de lignes : un plan change avec la volumétrie. L'ajout d'un index sur une grosse table prend du temps et de l'espace disque ; il se lance en heure creuse, après un export de la base.

Ensuite, comparer le résultat avant et après réécriture, en nombre de lignes comme en contenu, surtout si une condition a migré entre ON et WHERE. Le journal des requêtes lentes (slow_query_log, avec long_query_time réglé à une ou deux secondes) indique si la correction tient dans la durée et quelles autres requêtes méritent la même attention. La rubrique bases de données SQL, du schéma aux requêtes rassemble les autres cas traités sur le site.

LEFT OUTER JOIN, lignes manquantes, INNER JOIN et FULL OUTER JOIN

LEFT JOIN et LEFT OUTER JOIN sont-ils différents ?

LEFT JOIN et LEFT OUTER JOIN désignent exactement la même opération dans MySQL : OUTER est un mot clé facultatif. Le plan d'exécution et le résultat sont identiques.

Pourquoi mon LEFT JOIN renvoie-t-il moins de lignes que la table de gauche ?

Une condition sur la table de droite placée dans la clause WHERE élimine les lignes complétées par NULL et transforme la jointure externe en jointure interne. La déplacer dans le ON rétablit toutes les lignes de gauche.

Faut-il préférer INNER JOIN à LEFT JOIN pour la vitesse ?

Un INNER JOIN laisse l'optimiseur choisir l'ordre des tables, ce qui le rend souvent plus rapide ; il ne convient que si les lignes sans correspondance n'ont pas à figurer dans le résultat.

MySQL gère-t-il le FULL OUTER JOIN ?

MySQL ne gère pas FULL OUTER JOIN. On l'émule par l'union d'un LEFT JOIN et d'un RIGHT JOIN, de préférence avec UNION ALL et un filtre IS NULL sur la seconde branche.

Sources : manuel de référence de MySQL 8.0 et 8.4, sections Outer Join Simplification, Hash Join Optimization, Optimizing Subqueries with Semijoin and Antijoin Transformations, EXPLAIN Statement et Lateral Derived Tables.