8. Requêtes SELECT

Ce chapitre décrit l'instruction SELECT : liste de sélection, FROM et jointures, WHERE, GROUP BY / HAVING, ORDER BY, LIMIT / OFFSET, sous-requêtes et UNION. Les sections 8.12 à 8.20 complètent ce socle : grammaire complète, jointures en pratique, expressions de table communes, fonctions de fenêtrage, verrous, indications d'optimiseur et lecture d'EXPLAIN. Chaque affirmation de ce chapitre a été vérifiée dans le parseur, l'exécuteur et par exécution ; ce qui n'existe pas est marqué non pris en charge.

Les exemples s'appuient sur deux tables, remplies une fois pour tout le chapitre :

CREATE TABLE clients (
    id     INT AUTO_INCREMENT PRIMARY KEY,
    nom    VARCHAR(50) NOT NULL,
    ville  VARCHAR(50),
    solde  DECIMAL(10,2)
);

INSERT INTO clients (nom, ville, solde) VALUES
    ('Alice', 'Oran', 100.50),
    ('Bob', 'Alger', -20.00),
    ('Chloé', 'Oran', 0.00),
    ('David', 'Constantine', NULL),
    ('Émile', 'Alger', 75.25),
    ('Fatima', NULL, 10.00);

CREATE TABLE commandes (
    id        INT PRIMARY KEY,
    client_id INT,
    montant   DECIMAL(10,2)
);

INSERT INTO commandes VALUES
    (1, 1, 10.00), (2, 1, 20.50), (3, 2, 5.00), (4, 9, 1.00), (5, 3, NULL);

8.1 Synopsis#

SELECT [DISTINCT] item [, item ...]
    [FROM table [jointure ...]]
    [WHERE condition]
    [GROUP BY expr [, expr ...]]
    [HAVING condition]
    [ORDER BY expr [ASC|DESC] [, ...]]
    [LIMIT n [OFFSET m] | LIMIT m, n]

Ce synopsis est la forme de base ; la grammaire complète (WITH, UNION, verrous, OVER, etc.) est donnée au §8.12.

8.2 Liste de sélection#

Chaque item est une expression, avec un alias facultatif (AS alias, ou un alias sans AS) :

SELECT nom, solde AS balance, solde * 1.1 marge FROM clients;
SELECT * FROM clients;                -- toutes les colonnes
SELECT clients.* , 1 AS actif FROM clients;   -- toutes les colonnes d'une table nommée, plus une expression

DISTINCT#

DISTINCT élimine les lignes du résultat identiques sur tous les items sélectionnés (une valeur NULL est considérée égale à une autre NULL pour cette élimination, contrairement à = dans un WHERE) :

SELECT DISTINCT ville FROM clients ORDER BY ville;
SELECT DISTINCT ville, solde FROM clients WHERE ville IS NOT NULL ORDER BY ville, solde;

COUNT(DISTINCT expr) compte les valeurs distinctes plutôt que les lignes :

SELECT COUNT(DISTINCT ville) FROM clients;

8.3 FROM et jointures#

Types de jointure pris en charge#

INNER JOIN, LEFT JOIN, RIGHT JOIN et CROSS JOIN peuvent tous porter un alias sur chaque table, et enchaîner plusieurs jointures dans un même FROM.

SELECT c.nom, o.montant FROM clients c JOIN commandes o ON o.client_id = c.id;

SELECT c.nom, COUNT(o.id) FROM clients c LEFT JOIN commandes o ON o.client_id = c.id
GROUP BY c.id, c.nom ORDER BY c.id;

SELECT c.nom, o.id, o.montant FROM clients c RIGHT JOIN commandes o ON o.client_id = c.id
ORDER BY o.id;   -- toutes les commandes, y compris celles sans client existant (id = 9)

SELECT nom, montant FROM clients JOIN commandes USING (id) ORDER BY id;

Auto-jointure#

Une table peut être jointe à elle-même ; chaque occurrence a besoin d'un alias distinct :

SELECT a.nom, b.nom
FROM clients a
JOIN clients b ON a.ville = b.ville AND a.id < b.id;

Table dérivée (sous-requête dans FROM)#

Une sous-requête peut remplacer une table dans FROM ou dans une jointure ; elle a besoin d'un alias :

SELECT t.ville, t.total
FROM (
    SELECT ville, SUM(solde) AS total
    FROM clients
    GROUP BY ville
) t
WHERE t.total > 0;

Non pris en charge#

• NATURAL JOIN (sous toute forme) est rejeté par le parseur (erreur
  1. : joindre explicitement avec ON ou USING à la place.
• Il n'y a pas de FULL [OUTER] JOIN ; un équivalent se construit avec un LEFT JOIN et un RIGHT JOIN combinés par UNION (voir §8.9) :

  SELECT c.nom, o.id FROM clients c LEFT JOIN commandes o ON o.client_id = c.id
  UNION
  SELECT c.nom, o.id FROM clients c RIGHT JOIN commandes o ON o.client_id = c.id;

8.4 WHERE#

WHERE filtre les lignes produites par FROM avant tout regroupement. Opérateurs disponibles :

Les comparaisons de textes (=, <, IN, BETWEEN…), GROUP BY, DISTINCT, ORDER BY et les jointures suivent l'interclassement des opérandes (celui de la colonne contre un littéral, celui d'un COLLATE explicite) : WHERE code = 'a1' ne rend pas 'A1' sur une colonne utf8mb4_bin, WHERE nom COLLATE utf8mb4_unicode_ci = 'strasse' rend 'Straße'. Deux interclassements qui ne s'accordent pas lèvent 1267 (voir Interclassements).

SELECT nom FROM clients WHERE solde BETWEEN 0 AND 100 AND ville IN ('Oran', 'Alger');
SELECT nom FROM clients WHERE nom LIKE '_h%' OR nom REGEXP '^[A-E]';
SELECT nom FROM clients WHERE ville IS NULL;

Logique à trois valeurs et NULL#

Une comparaison avec NULL (y compris NULL = NULL) ne vaut jamais TRUE ni FALSE, mais UNKNOWN ; une ligne n'est retenue par WHERE que si sa condition vaut TRUE (UNKNOWN, comme FALSE, exclut la ligne). Tester l'absence de valeur demande donc IS NULL / IS NOT NULL, jamais = NULL :

SELECT nom FROM clients WHERE solde = NULL;      -- ne renvoie jamais rien
SELECT nom FROM clients WHERE solde IS NULL;      -- David

Le chapitre 13 (§13.5, « Logique à trois valeurs ») détaille ce comportement et ses pièges habituels (NOT IN avec un NULL dans la liste, NOT sur une condition UNKNOWN, agrégats qui ignorent les NULL).

8.5 GROUP BY et HAVING#

GROUP BY regroupe les lignes qui partagent les mêmes valeurs des expressions listées ; les fonctions d'agrégat (COUNT, SUM, AVG, MIN, MAX, GROUP_CONCAT, ...) sont alors calculées par groupe. HAVING filtre les groupes, comme WHERE filtre les lignes (et peut donc porter sur un agrégat, ce que WHERE ne peut pas).

SELECT ville, COUNT(*) FROM clients GROUP BY ville ORDER BY ville;
SELECT ville, COUNT(*) c FROM clients GROUP BY ville HAVING c > 1 ORDER BY ville;

Colonne citée hors GROUP BY#

Le mode SQL de MIRAJ ne comprend pas, par défaut, l'équivalent de la restriction ONLY_FULL_GROUP_BY : une colonne de la liste de sélection qui n'est ni un agrégat ni citée dans GROUP BY est acceptée, et rend une valeur d'une des lignes du groupe (laquelle n'est pas garantie ; l'activer dans sql_mode rétablit l'erreur 1055). Exemple :

SELECT ville, nom, COUNT(*) FROM clients GROUP BY ville;

Ici nom n'est ni agrégée ni dans GROUP BY : pour le groupe 'Oran' (Alice et Chloé), la valeur de nom rendue est celle d'une des deux lignes du groupe, sans garantie de laquelle. N'utiliser cette forme que lorsque la colonne est en réalité constante dans chaque groupe (par exemple une colonne fonctionnellement dépendante de la colonne groupée), sous peine d'un résultat qui dépend de l'ordre interne des lignes.

Résolution d'un nom dans GROUP BY, HAVING et ORDER BY#

Un nom sans qualificatif de table (ville, pas clients.ville) peut désigner soit une colonne d'une table du FROM, soit un alias de la liste SELECT. La règle de résolution diffère selon la clause :

• GROUP BY : une colonne réelle d'une table lue l'emporte sur un alias de même nom, tant qu'elle existe sans ambiguïté entre les tables du FROM. L'alias n'est utilisé que si aucune colonne de ce nom n'existe, ou pour lever une ambiguïté entre plusieurs tables qui portent chacune une colonne de ce nom.
• HAVING et ORDER BY : à l'inverse, un alias de la liste SELECT est prioritaire sur une colonne de table du même nom.
• Dans tous les cas, un nom qualifié (t.col) reste toujours une colonne de table, jamais un alias.

-- l'alias `nom` masque la colonne réelle `ville` pour ORDER BY, mais GROUP BY groupe sur la
-- colonne réelle `nom` de clients : chaque client a un nom distinct, donc un groupe par ligne,
-- pas un groupe par ville comme le donnerait un regroupement sur l'alias
SELECT UPPER(ville) AS nom, COUNT(*) FROM clients WHERE ville IS NOT NULL GROUP BY nom ORDER BY nom;

Un nom porté par plusieurs tables du FROM est ambigu (erreur 1052) sauf s'il désigne sans ambiguïté un alias de la liste SELECT, ou s'il est qualifié.

8.6 GROUP_CONCAT#

GROUP_CONCAT([DISTINCT] expr [, expr ...] [ORDER BY clé [ASC|DESC] [, ...]] [SEPARATOR 'chaîne'])

Concatène, pour chaque groupe, les valeurs de expr (les valeurs NULL sont ignorées), dans l'ordre indiqué par la clause ORDER BY interne à GROUP_CONCAT (indépendante d'un éventuel ORDER BY de la requête), séparées par SEPARATOR (, par défaut). DISTINCT élimine les valeurs répétées avant concaténation.

SELECT GROUP_CONCAT(nom) FROM clients WHERE ville = 'Oran';
-- Alice,Chloé

SELECT GROUP_CONCAT(nom ORDER BY nom DESC) FROM clients WHERE ville = 'Oran';
-- Chloé,Alice

SELECT ville, GROUP_CONCAT(DISTINCT id ORDER BY id DESC SEPARATOR ' / ') FROM clients
GROUP BY ville ORDER BY ville;

8.7 ORDER BY, LIMIT et OFFSET#

ORDER BY trie le résultat par une ou plusieurs expressions, chacune ASC (défaut) ou DESC ; une position numérique (ORDER BY 1) désigne l'item correspondant de la liste de sélection.

SELECT nom, solde FROM clients ORDER BY solde DESC, nom;

LIMIT borne le nombre de lignes rendues ; OFFSET (ou la forme LIMIT décalage, nombre) saute les premières lignes du résultat trié :

SELECT nom FROM clients ORDER BY solde DESC LIMIT 3;
SELECT nom FROM clients ORDER BY solde DESC LIMIT 1 OFFSET 4;
SELECT nom FROM clients ORDER BY solde DESC LIMIT 4, 1;   -- équivalent : LIMIT 1 OFFSET 4

8.8 Sous-requêtes#

Une sous-requête (SELECT entre parenthèses) peut apparaître :

• comme valeur scalaire, partout où une expression est attendue (elle doit rendre au plus une ligne et une colonne ; plus d'une ligne à l'exécution est l'erreur « sous-requête à une seule ligne ») ;
• dans un test [NOT] IN (sous-requête) ;
• dans un test [NOT] EXISTS (sous-requête) ;
• comme table dérivée dans FROM (voir §8.3).

Une sous-requête peut être corrélée : elle cite une colonne d'une table de la requête externe et est alors réévaluée pour chaque ligne de celle-ci.

-- scalaire, corrélée
SELECT nom, solde FROM clients c WHERE solde = (
    SELECT MAX(solde) FROM clients v WHERE v.ville = c.ville
);

-- EXISTS / NOT EXISTS, corrélée
SELECT id FROM commandes o WHERE NOT EXISTS (
    SELECT 1 FROM clients c WHERE c.id = o.client_id
);

-- IN avec sous-requête
DELETE FROM commandes WHERE client_id NOT IN (SELECT id FROM clients);

-- sous-requête dans la liste de sélection
SELECT c.nom, (SELECT COUNT(*) FROM commandes o WHERE o.client_id = c.id) AS nb
FROM clients c ORDER BY c.id;

8.9 UNION#

requête_select UNION [ALL] requête_select [UNION [ALL] requête_select ...]
[ORDER BY ...] [LIMIT ...]

UNION combine les résultats de plusieurs SELECT de même nombre de colonnes ; UNION (sans ALL) élimine les lignes en double du résultat combiné, UNION ALL les conserve toutes.

SELECT ville FROM clients WHERE id < 4
UNION
SELECT ville FROM clients WHERE id > 3
ORDER BY ville;

SELECT client_id FROM commandes
UNION ALL
SELECT id FROM clients WHERE id > 5
ORDER BY 1 DESC LIMIT 3;

Un ORDER BY ou un LIMIT final, après le dernier membre, s'applique au résultat combiné de l'union entière, pas au seul dernier SELECT ; il peut citer une colonne d'un membre qualifiée par son nom de table d'origine (ORDER BY clients.id) aussi bien que par sa position ou son alias. Chaque membre peut être parenthésé pour lui donner son propre ORDER BY / LIMIT, appliqué avant la combinaison :

(SELECT nom FROM clients ORDER BY id DESC LIMIT 1)
UNION
(SELECT nom FROM clients ORDER BY id LIMIT 1);

Un ORDER BY ou un LIMIT placé sur un membre non parenthésé suivi d'un UNION est rejeté par la grammaire (erreur de syntaxe 1064) : parenthéser le membre pour lever l'ambiguïté entre « tri de ce membre » et « tri de l'union entière ».

8.10 Utiliser une vue#

Une vue se lit exactement comme une table, dans FROM, une jointure ou une sous-requête :

SELECT * FROM v_clients_actifs WHERE ville = 'Oran';

La création, la modification et le rafraîchissement des vues (vue ordinaire ou CACHED) sont décrits au chapitre 6, « Langage DDL ».

8.11 Fonctionnalités non disponibles dans cette version#

D'après une vérification dans le code du parseur et de l'exécuteur (le fichier readme.txt du projet est daté sur plusieurs de ces points) :

Ce tableau reflète l'état du code au moment de la rédaction.

8.12 Grammaire complète#

Grammaire de SELECT telle que l'accepte le parseur ([ ] : facultatif, | : alternative, { } : au moins un élément) :

requête ::=
    [ WITH nom [(col, ...)] AS ( select | VALUES (expr, ...), ... ) [, ...] ]
    membre [ UNION [ALL | DISTINCT] membre ... ]
    [ ORDER BY expr [ASC|DESC] [, ...] ]          -- après le dernier membre : porte sur l'union
    [ LIMIT ... ]                                  -- idem

membre ::=  select  |  ( select )

select ::=
    SELECT [ /*+ indications */ ] [ DISTINCT | DISTINCTROW | ALL ]
           [ SQL_CALC_FOUND_ROWS | SQL_NO_CACHE | SQL_CACHE | HIGH_PRIORITY | STRAIGHT_JOIN ] ...
           item [, item ...]
    [ INTO @variable [, ...] | INTO OUTFILE 'fichier' ... | INTO DUMPFILE 'fichier' ]
    [ FROM DUAL | source [ jointure ... ] ]
    [ WHERE condition ]
    [ GROUP BY expr [ASC|DESC] [, ...] [WITH ROLLUP]
    | GROUP BY { expr | ROLLUP(...) | CUBE(...) | GROUPING SETS (...) } [, ...] ]
    [ HAVING condition ]
    [ ORDER BY expr [ASC|DESC] [, ...] ]
    [ LIMIT { n | décalage, n | n OFFSET décalage } [ROWS EXAMINED m] ]
    [ FOR UPDATE | FOR SHARE [OF table, ...] [NOWAIT | WAIT s | SKIP LOCKED]
    | LOCK IN SHARE MODE [NOWAIT | WAIT s | SKIP LOCKED] ]

item    ::=  * | table.* | base.table.* | expr [[AS] alias]
source  ::=  table [PARTITION (p, ...)] [FOR SYSTEM_TIME période] [[AS] alias]
          |  JSON_TABLE(document, 'chemin' COLUMNS (...)) [AS] alias
          |  ( select ) [AS] alias  |  ( source jointure ... )
-- FOR SYSTEM_TIME : tables versionnées (chapitre 11) ; JSON_TABLE : chapitre 9, §9.10
jointure ::=
      , source
    | [INNER | CROSS] JOIN source [ON condition | USING (col, ...)]
    | STRAIGHT_JOIN source [ON condition | USING (col, ...)]
    | LEFT [OUTER] JOIN source { ON condition | USING (col, ...) }
    | RIGHT [OUTER] JOIN source { ON condition | USING (col, ...) }

Points de détail vérifiés dans le parseur :

• Un JOIN sans ON ni USING est un produit cartésien ; un LEFT ou RIGHT JOIN sans ON ni USING est une erreur de syntaxe (1064).
• SELECT sans FROM (ou FROM DUAL) est permis ; le mot DUAL n'est reconnu qu'à la place de la première table.
• SQL_NO_CACHE, SQL_CACHE et HIGH_PRIORITY sont acceptés et sans effet sur le résultat ; SQL_CALC_FOUND_ROWS fait compter à FOUND_ROWS() les lignes que le SELECT rendrait sans son LIMIT (chapitre 9, §9.9) ; DISTINCTROW équivaut à DISTINCT.
• Un groupe de jointures entre parenthèses en tête de FROM (FROM (a LEFT JOIN b ON ...)) est accepté ; il est mis à plat, les parenthèses ne changent pas le sens.
• GROUP BY expr ASC|DESC est accepté mais n'impose aucun tri : écrire un ORDER BY.
• LIMIT accepte un entier, un paramètre ? d'une instruction préparée ou une variable locale d'une routine ; LIMIT 18446744073709551615 (idiome « toutes les lignes ») est accepté.
• LIMIT n ROWS EXAMINED m borne le nombre de lignes examinées par toute l'instruction.
• INTO (variables, fichier) n'est permis que dans le premier SELECT de l'instruction, jamais dans une expression de table ni une sous-requête.

Toutes les formes ci-dessous supposent les tables clients et commandes de l'en-tête du chapitre.

8.13 Jointures en pratique#

Récapitulatif des types de jointure#

Exemples et résultats#

INNER JOIN : les clients sans commande et les commandes sans client disparaissent.

SELECT c.nom, o.id FROM clients c JOIN commandes o ON o.client_id = c.id ORDER BY o.id;

+-------+----+
| nom   | id |
+-------+----+
| Alice |  1 |
| Alice |  2 |
| Bob   |  3 |
| Chloé |  5 |
+-------+----+

LEFT JOIN : tous les clients ; NULL là où il n'y a pas de commande.

SELECT c.nom, o.id FROM clients c LEFT JOIN commandes o ON o.client_id = c.id ORDER BY c.id, o.id;

+--------+------+
| nom    | id   |
+--------+------+
| Alice  |    1 |
| Alice  |    2 |
| Bob    |    3 |
| Chloé  |    5 |
| David  | NULL |
| Émile  | NULL |
| Fatima | NULL |
+--------+------+

RIGHT JOIN : toutes les commandes ; la commande 4 référence un client inexistant (id 9).

SELECT c.nom, o.id FROM clients c RIGHT JOIN commandes o ON o.client_id = c.id ORDER BY o.id;

+-------+----+
| nom   | id |
+-------+----+
| Alice |  1 |
| Alice |  2 |
| Bob   |  3 |
| NULL  |  4 |
| Chloé |  5 |
+-------+----+

CROSS JOIN : le produit cartésien de 6 clients par 5 commandes donne 30 lignes ; à réserver aux cas voulus (tables de paramètres, génération de combinaisons).

Anti-jointure (les clients sans aucune commande) : LEFT JOIN ... IS NULL ou NOT EXISTS, qui donnent le même résultat.

SELECT c.nom FROM clients c LEFT JOIN commandes o ON o.client_id = c.id WHERE o.id IS NULL ORDER BY c.id;
SELECT nom FROM clients c WHERE NOT EXISTS (SELECT 1 FROM commandes o WHERE o.client_id = c.id) ORDER BY id;

+--------+
| nom    |
+--------+
| David  |
| Émile  |
| Fatima |
+--------+

Jointure avec une table dérivée (agrégat par client, puis jointure) ; passer en LEFT JOIN conserve les clients sans commande :

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

+--------+-------+
| nom    | total |
+--------+-------+
| Alice  | 30.50 |
| Bob    |  5.00 |
| Chloé  |  NULL |
| David  |  NULL |
| Émile  |  NULL |
| Fatima |  NULL |
+--------+-------+

Jointures multiples : elles s'enchaînent de gauche à droite dans le même FROM, avec un alias distinct par occurrence d'une table (sans alias distinct : erreur 1066) ; les types peuvent être mélangés (JOIN, LEFT JOIN, RIGHT JOIN). Une condition ON peut citer les tables placées avant elle :

SELECT c.nom, o.id AS cmd, x.id AS suivante
FROM clients c
JOIN commandes o ON o.client_id = c.id
LEFT JOIN commandes x ON x.client_id = c.id AND x.id > o.id
ORDER BY o.id;

+-------+-----+----------+
| nom   | cmd | suivante |
+-------+-----+----------+
| Alice |   1 |        2 |
| Alice |   2 |     NULL |
| Bob   |   3 |     NULL |
| Chloé |   5 |     NULL |
+-------+-----+----------+

Auto-jointure : les paires de clients de la même ville (voir §8.3) ; l'alias est obligatoire pour distinguer les deux occurrences. a.id < b.id évite les paires symétriques et les paires d'un client avec lui-même :

SELECT a.nom, b.nom FROM clients a JOIN clients b ON a.ville = b.ville AND a.id < b.id;

+-------+-------+
| nom   | nom   |
+-------+-------+
| Alice | Chloé |
| Bob   | Émile |
+-------+-------+

FULL OUTER JOIN émulé : LEFT JOIN puis RIGHT JOIN réunis par UNION (qui élimine les doublons ; l'ORDER BY final porte sur l'union entière) :

SELECT c.nom, o.id FROM clients c LEFT JOIN commandes o ON o.client_id = c.id
UNION
SELECT c.nom, o.id FROM clients c RIGHT JOIN commandes o ON o.client_id = c.id
ORDER BY 1, 2;

+--------+------+
| nom    | id   |
+--------+------+
| NULL   |    4 |
| Alice  |    1 |
| Alice  |    2 |
| Bob    |    3 |
| Chloé  |    5 |
| David  | NULL |
| Émile  | NULL |
| Fatima | NULL |
+--------+------+

Avec UNION (et non UNION ALL), deux lignes réellement identiques des deux côtés sont fusionnées : si des lignes en double sont significatives, ajouter une colonne d'identité (ici o.id, c.id) ou construire l'union avec UNION ALL et un WHERE o.id IS NULL sur la partie droite.

USING : condition d'égalité sur des colonnes de même nom ; la colonne peut être citée sans qualificatif. Attention : SELECT * rend la colonne une fois par table (deux colonnes ville ci-dessous).

SELECT * FROM clients c1 JOIN clients c2 USING (ville) LIMIT 1;

+----+-------+-------+--------+----+-------+-------+-------+
| id | nom   | ville | solde  | id | nom   | ville | solde |
+----+-------+-------+--------+----+-------+-------+-------+
|  1 | Alice | Oran  | 100.50 |  3 | Chloé | Oran  |  0.00 |
+----+-------+-------+--------+----+-------+-------+-------+

Quel type de jointure choisir#

• Les lignes d'une table qui n'ont pas de correspondance doivent-elles rester dans le résultat ? Non : INNER JOIN. Oui, celles de la table principale : LEFT JOIN (écrire la table principale à gauche ; RIGHT JOIN est son symétrique et n'apporte rien de plus).
• Chercher les lignes sans correspondance : LEFT JOIN ... WHERE droite.clé IS NULL ou NOT EXISTS ; NOT IN (sous-requête) est piégeux avec NULL (voir plus bas).
• Simplement tester l'existence d'une correspondance sans dupliquer les lignes de gauche : WHERE EXISTS (...) ou IN (sous-requête) plutôt qu'une jointure, qui multiplie la ligne de gauche par le nombre de correspondances (Alice apparaît deux fois dans le premier exemple).
• Il faut des valeurs agrégées de l'autre table : agréger d'abord dans une table dérivée puis joindre, ce qui évite de multiplier les lignes avant l'agrégat.

Comportement de NULL dans les jointures#

• Une condition ON a.x = b.y n'est jamais vraie quand l'un des côtés est NULL : deux NULL ne se joignent pas entre eux. Fatima (ville NULL) ne se joint à aucun client dans la jointure par ville ci-dessus, pas même à elle-même.

• Dans un LEFT JOIN, les colonnes du côté droit sans correspondance valent NULL. Une condition WHERE sur une colonne de la table de droite (WHERE o.montant > 10) élimine ces lignes (une comparaison avec NULL n'est jamais vraie) et transforme de fait le LEFT JOIN en INNER JOIN. Pour filtrer la table de droite tout en gardant toutes les lignes de gauche, mettre la condition dans le ON :

  SELECT c.nom, o.id FROM clients c LEFT JOIN commandes o ON o.client_id = c.id AND o.montant > 10 ORDER BY c.id;

  +--------+------+
  | nom    | id   |
  +--------+------+
  | Alice  |    2 |
  | Bob    | NULL |
  | Chloé  | NULL |
  | David  | NULL |
  | Émile  | NULL |
  | Fatima | NULL |
  +--------+------+

  Avec WHERE o.montant > 10 à la place, seule la ligne d'Alice reste.

• Compter les correspondances : COUNT(o.id) ignore les NULL (0 pour un client sans commande), COUNT(*) compte la ligne « sans correspondance » elle-même (1) :

  SELECT c.nom, COUNT(o.id) AS nb, COUNT(*) AS lignes
  FROM clients c LEFT JOIN commandes o ON o.client_id = c.id GROUP BY c.id, c.nom ORDER BY c.id;

  +--------+----+--------+
  | nom    | nb | lignes |
  +--------+----+--------+
  | Alice  |  2 |      2 |
  | Bob    |  1 |      1 |
  | Chloé  |  1 |      1 |
  | David  |  0 |      1 |
  | Émile  |  0 |      1 |
  | Fatima |  0 |      1 |
  +--------+----+--------+

• NOT IN (sous-requête) rend zéro ligne dès que la sous-requête produit un NULL (voir §8.16) ; NOT EXISTS n'a pas ce piège.
• Une colonne d'une table du côté droit d'un LEFT JOIN devient nullable pour le reste de la requête, même si elle est NOT NULL dans la table (de même pour le côté gauche d'un RIGHT JOIN).

Notes de performance propres au moteur#

• Algorithme de jointure. Lorsque la condition ON (ou USING) contient au moins une égalité entre une colonne de chaque côté (o.client_id = c.id, éventuellement combinée par AND à d'autres conditions), le moteur utilise une jointure par hachage. Sinon (ON a.solde > b.solde, OR, expression, produit cartésien) il utilise une boucle imbriquée, dont le coût est le produit des tailles. L'égalité ne doit porter que sur des colonnes de même famille de type (nombre avec nombre, chaîne avec chaîne) : comparer une chaîne à un nombre exclut la jointure par hachage.
• Ordre des jointures. Les jointures sont planifiées dans l'ordre d'écriture du FROM, sans réordonnancement fondé sur les statistiques ; STRAIGHT_JOIN n'y ajoute rien (il est traité comme JOIN). Écrire d'abord la table qui filtre le plus.
• Index. Les index (clé primaire, UNIQUE, secondaires) servent aux lectures de table par égalité avec une constante (WHERE id = 1, WHERE ville = 'Oran', voir §8.20). Dans les essais menés, EXPLAIN d'une jointure indique un accès ALL pour chaque table, l'appariement se faisant par la jointure par hachage : un index sur la colonne de jointure ne remplace pas ce mécanisme. Une lecture par un index non unique sur constante ne vaut que pour l'égalité ; les intervalles (id > 2) parcourent la table.
• Jointures externes inutiles. Un LEFT JOIN dont aucune colonne n'est lue et qui ne peut pas multiplier les lignes (jointure sur une clé unique, ou requête DISTINCT) est retiré du plan. Cette optimisation ne s'applique pas si la table jointe est citée par *, t.*, une colonne qualifiée ou une sous-requête, ni pour une vue ou une table dérivée.
• Requêtes coûteuses. max_join_size (avec sql_big_selects) refuse un bloc SELECT dont l'estimation de lignes examinées est trop grande (erreur 1104), avant toute exécution. Les intervalles et les OR ne réduisent pas cette estimation ; EXPLAIN n'applique pas le contrôle.
• Vues et tables dérivées. Une vue est lue comme sa requête spécialisée par les conditions de l'appelant ; une expression de table commune est remplacée par sa requête (§8.15).

8.14 Opérations ensemblistes : UNION, INTERSECT, EXCEPT#

• UNION [ALL | DISTINCT] : pris en charge (§8.9). DISTINCT après UNION est le comportement par défaut, identique à l'absence de mot-clé. Le nombre de colonnes des membres doit être identique (erreur 1222 sinon) ; les noms de colonnes du résultat sont ceux du premier membre.

• INTERSECT et EXCEPT (ainsi que MINUS, et leurs variantes ALL) : non pris en charge, le parseur répond par l'erreur de syntaxe 1064. Les équivalents s'écrivent avec IN / NOT IN ou EXISTS / NOT EXISTS :

  -- INTERSECT : identifiants présents dans clients ET dans commandes.client_id
  SELECT DISTINCT id FROM clients WHERE id IN (SELECT client_id FROM commandes) ORDER BY id;
  -- EXCEPT : identifiants de clients sans commande (NOT EXISTS, sûr avec NULL)
  SELECT id FROM clients c WHERE NOT EXISTS (SELECT 1 FROM commandes o WHERE o.client_id = c.id) ORDER BY id;

  Résultat de la première (1, 2, 3) et de la seconde (4, 5, 6). Contrairement à INTERSECT et EXCEPT du standard, ces réécritures comparent avec l'égalité, donc ne rapprochent pas deux valeurs NULL.

8.15 Expressions de table communes (WITH)#

WITH nom [(colonne, ...)] AS ( SELECT ... | VALUES (expr, ...), ... ) [, nom2 AS (...)]
SELECT ... ;

Une expression de table commune (CTE) donne un nom à une requête réutilisable dans la suite de l'instruction, y compris plusieurs fois et dans une jointure avec elle-même. Vérifié :

• plusieurs CTE séparées par des virgules, une CTE pouvant citer celles qui la précèdent ;
• une liste de colonnes après le nom renomme les colonnes (le nombre doit correspondre, erreur 1222 ; incompatible avec un corps SELECT *, erreur 1235) ;
• un corps VALUES (...), (...) est accepté ; sans liste de colonnes, les noms de colonnes sont ceux de la première ligne (1, 'a') : donner une liste de colonnes ;
• le WITH s'écrit en tête d'une requête ou d'un membre parenthésé, et vaut pour toute l'union ;
• WITH RECURSIVE permet à une expression de se citer elle-même (voir ci-dessous).

Le moteur remplace chaque référence à une CTE par sa requête (comme une table dérivée), et limite le nombre total de ces expansions pour borner la taille d'une requête : une CTE référencée à de nombreux niveaux imbriqués peut être refusée par une erreur de syntaxe 1064.

WITH c(v, n) AS (SELECT ville, COUNT(*) FROM clients GROUP BY ville),
     m AS (SELECT MAX(n) AS mx FROM c)
SELECT c.v FROM c, m WHERE c.n = m.mx ORDER BY c.v;

+-------+
| v     |
+-------+
| Alger |
| Oran  |
+-------+

WITH t(a, b) AS (VALUES (1, 'a'), (2, 'b')) SELECT * FROM t;

+---+---+
| a | b |
+---+---+
| 1 | a |
| 2 | b |
+---+---+

Expressions récursives (WITH RECURSIVE)#

WITH RECURSIVE nom [(colonne, ...)] AS (
    membre non récursif [UNION [ALL] membre non récursif ...]
    UNION [ALL | DISTINCT]
    membre récursif [UNION [ALL] membre récursif ...]
) SELECT ... ;

Après WITH RECURSIVE, une expression peut se citer dans son propre corps. Les membres qui ne la citent pas (au moins un, en tête) donnent les premières lignes ; chaque itération lit ensuite les membres récursifs sur les seules lignes rendues par l'itération précédente, jusqu'à ce qu'une itération n'en rende plus. Une expression d'un WITH RECURSIVE qui ne se cite pas reste ordinaire.

• Les types des colonnes sont ceux des membres non récursifs : les lignes des membres récursifs y sont converties. Pour construire un chemin qui s'allonge, élargir la colonne dans le membre non récursif (CAST(libelle AS CHAR(200))).
• Avec UNION (ou UNION DISTINCT) au lieu de UNION ALL, une ligne déjà rendue n'est ni rendue ni relue une seconde fois : un parcours de graphe avec cycles se termine.
• cte_max_recursion_depth (session ou globale, défaut 1000, de 0 à 4 294 967 295) borne le nombre d'itérations, celle qui ne rend plus rien comprise : au-delà, erreur 3636 « Recursive query aborted after n iterations ».
• Un LIMIT de l'union entière (sans ORDER BY) arrête les itérations dès qu'il est atteint.
• Erreurs : sans UNION, 3573 ; membre non récursif placé après un membre récursif, 3574 ; agrégat, fonction de fenêtrage ou GROUP BY dans un membre récursif, 3575.
• L'expression est calculée entièrement avant la lecture de la requête qui la cite, et une fois par référence (comme une table dérivée).

-- Comptes du plan comptable sous le compte 60, avec leur niveau et leur chemin
WITH RECURSIVE arbre (id, niveau, chemin) AS (
    SELECT id, 0, CAST(libelle AS CHAR(200)) FROM comptes WHERE id = 60
    UNION ALL
    SELECT c.id, a.niveau + 1, CONCAT(a.chemin, ' > ', c.libelle)
    FROM comptes c JOIN arbre a ON c.parent = a.id
) SELECT id, niveau, chemin FROM arbre ORDER BY chemin;

+------+--------+---------------------------+
| id   | niveau | chemin                    |
+------+--------+---------------------------+
|   60 |      0 | Achats                    |
|  601 |      1 | Achats > Matieres         |
| 6011 |      2 | Achats > Matieres > Acier |
+------+--------+---------------------------+

8.16 Sous-requêtes : compléments#

Formes prises en charge (§8.8) : scalaire, IN, EXISTS, corrélée, table dérivée, et sous-requête dans la liste de sélection, y compris dans une expression (EXISTS (...) comme valeur 0/1).

• Sous-requête scalaire : zéro ligne donne NULL ; plus d'une ligne est l'erreur 1242 (« Subquery returns more than 1 row »).

  SELECT nom FROM clients WHERE id = (SELECT client_id FROM commandes WHERE id = 99);   -- 0 ligne (NULL)
  SELECT (SELECT id FROM clients);                                                         -- erreur 1242

• Corrélée : peut citer une colonne de la requête externe (par alias ou par nom de table, en WHERE, dans la liste de sélection, en IN, en EXISTS), y compris dans un test de comparaison : SELECT nom FROM clients WHERE id IN (SELECT client_id FROM commandes WHERE montant > clients.solde) rend Bob.
• ANY, SOME, ALL : x > ANY (sous-requête), x = SOME (...), x > ALL (...) sont non pris en charge (erreur de syntaxe 1064). Équivalents : x = ANY (S) est x IN (S) ; x > ANY (S) est EXISTS (SELECT 1 FROM ... WHERE x > colonne) ou x > (SELECT MIN(col) ...) ; x > ALL (S) est x > (SELECT MAX(col) ...) (attention aux NULL : filtrer par col IS NOT NULL).
• Constructeur de ligne : (a, b) IN ((1, 'x'), ...) et (a, b) = (SELECT ...) sont non pris en charge (1064). Écrire a = 1 AND b = 'x' ou un EXISTS corrélé.

• LIMIT dans une sous-requête IN : accepté par ce moteur.

  SELECT nom FROM clients WHERE id IN (SELECT client_id FROM commandes ORDER BY id LIMIT 2);

  Rend Alice (les deux premières commandes sont celles du client 1).

• IN et NULL : x IN (S) vaut TRUE si une valeur correspond ; sinon UNKNOWN (et non FALSE) si S contient un NULL. Le piège est dans NOT IN : dès que S contient un NULL, le test ne vaut jamais TRUE, et la requête ne rend aucune ligne.

  SELECT nom FROM clients WHERE id NOT IN (SELECT client_id FROM commandes UNION ALL SELECT NULL);
  -- 0 ligne
  
  SELECT nom FROM clients c WHERE NOT EXISTS (SELECT 1 FROM commandes o WHERE o.client_id = c.id) ORDER BY id;
  -- David, Émile, Fatima

  Préférer NOT EXISTS, ou ajouter WHERE col IS NOT NULL dans la sous-requête de NOT IN.

• Sous-requête non corrélée : elle ne dépend d'aucune colonne externe ; le résultat est le même qu'une valeur constante (WHERE solde > (SELECT AVG(solde) FROM clients) rend Alice et Émile).
• Table dérivée : l'alias est obligatoire (§8.3). LATERAL (table dérivée qui cite une autre table du même FROM) est non pris en charge (1064) ; utiliser une sous-requête corrélée dans la liste de sélection, ou une table dérivée agrégée puis jointe.

8.17 GROUP BY, HAVING et regroupements : compléments#

• GROUP BY accepte des expressions, des positions (GROUP BY 1) et des alias (règles de résolution au §8.5). Les valeurs NULL forment un seul groupe.
• Fonctions d'agrégat prises en charge dans SELECT ... GROUP BY : COUNT, SUM, AVG, MIN, MAX, GROUP_CONCAT (§8.6), STD / STDDEV / STDDEV_POP, VARIANCE (le détail figure au chapitre 9). COUNT(DISTINCT expr) et SUM(DISTINCT expr) filtrent les valeurs distinctes.
• Sans GROUP BY, une fonction d'agrégat produit toujours une ligne, même sur une table vide (COUNT(*) rend 0, les autres NULL) ; avec GROUP BY sur zéro ligne, le résultat est vide.
• HAVING peut citer un alias de la liste de sélection (§8.5) ; une condition sur une colonne non agrégée relève plutôt de WHERE, plus efficace car elle filtre avant le regroupement.
• WITH ROLLUP : GROUP BY a, b WITH ROLLUP ajoute, après chaque groupe, une ligne de sous-total où les colonnes de regroupement résumées valent NULL, puis un total général en dernier. Sans ORDER BY, les lignes sortent dans cet ordre (clés croissantes, NULL d'abord, chaque sous-total derrière son groupe) ; les agrégats (dont COUNT(DISTINCT ...) et GROUP_CONCAT), HAVING, ORDER BY, DISTINCT, LIMIT, les fonctions de fenêtrage, les sous-requêtes, les tables dérivées et les vues s'appliquent aux lignes de sous-total comme aux autres. Sur une table vide, le résultat est vide (pas même de total).
• GROUPING(expr [, ...]) distingue un NULL réel d'un NULL de sous-total : 1 si la ligne résume l'expression, 0 sinon ; avec plusieurs arguments, un masque de bits (le premier argument est le bit de poids fort). Les arguments doivent figurer dans le GROUP BY (erreur 3580), sans appel GROUPING() dans un WHERE ni hors regroupement (1111) ; sans ROLLUP, il vaut toujours 0.

• ROLLUP(...), CUBE(...) et GROUPING SETS (...) (SQL standard, extension de MIRAJ) : ROLLUP(a, b) équivaut à WITH ROLLUP, CUBE(a, b) produit tous les sous-ensembles de colonnes, GROUPING SETS ((a), (b), ()) liste les ensembles voulus (un ensemble imbriqué peut être un ROLLUP ou un CUBE). Plusieurs éléments séparés par des virgules se croisent (GROUP BY a, ROLLUP(b)). Les expressions, alias et positions du GROUP BY ordinaire sont admis. WITH ROLLUP ne se combine pas avec ROLLUP(...) dans le même GROUP BY (1064).

  SELECT ville, COUNT(*) AS n, GROUPING(ville) AS total
  FROM clients WHERE ville IS NOT NULL GROUP BY ville WITH ROLLUP;

  (une ligne par ville, puis une ligne de total dont ville vaut NULL et total vaut 1). Une vue qui contient un ROLLUP n'a pas la condition du WHERE appelant descendue dans sa définition : le filtre extérieur ne change pas les totaux.

• DISTINCT s'applique à la ligne entière ; SELECT DISTINCT ville, solde distingue les couples (§8.2), NULL compris. ORDER BY classe les NULL en premier en ordre croissant et en dernier en ordre décroissant :

  SELECT id, montant FROM commandes ORDER BY montant;

  +----+---------+
  | id | montant |
  +----+---------+
  |  5 |    NULL |
  |  4 |    1.00 |
  |  3 |    5.00 |
  |  1 |   10.00 |
  |  2 |   20.50 |
  +----+---------+

8.18 Fonctions de fenêtrage#

Syntaxe : fonction(...) OVER ([PARTITION BY expr, ...] [ORDER BY expr [ASC|DESC], ...] [cadre]), où le cadre est ROWS | RANGE suivi de borne ou de BETWEEN borne AND borne, une borne étant UNBOUNDED PRECEDING, n PRECEDING, CURRENT ROW, n FOLLOWING ou UNBOUNDED FOLLOWING.

Fonctions prises en charge :

Non pris en charge (erreur 1235 sauf indication) : COUNT(DISTINCT ...), SUM(DISTINCT ...) et GROUP_CONCAT avec OVER ; RANGE avec un décalage numérique (RANGE BETWEEN 1 PRECEDING ... ; RANGE UNBOUNDED ... et RANGE CURRENT ROW restent permis, tout comme ROWS avec décalage) ; toute autre fonction utilisée avec OVER ; la clause nommée WINDOW w AS (...) et OVER w (erreur de syntaxe 1064) : écrire la spécification complète à chaque OVER. Le décalage de LAG/LEAD et le rang de NTILE / NTH_VALUE doivent être des constantes entières (NTILE et NTH_VALUE : au moins 1). FILTER (WHERE ...) et le cadre GROUPS ne sont pas pris en charge.

Cadre par défaut : sans ORDER BY, toute la partition ; avec ORDER BY, de la première ligne de la partition à la ligne courante et ses ex æquo (RANGE UNBOUNDED PRECEDING à CURRENT ROW). C'est pourquoi LAST_VALUE(...) sans cadre explicite rend la ligne courante et non la dernière de la partition : écrire ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.

Une fonction de fenêtrage se place dans la liste de sélection ou dans l'ORDER BY ; dans WHERE elle est refusée (erreur 1111). Pour filtrer sur son résultat, l'encapsuler dans une table dérivée ou une CTE.

Le meilleur de chaque ville (n premiers par groupe) :

SELECT nom, ville, solde FROM (
    SELECT nom, ville, solde,
           ROW_NUMBER() OVER (PARTITION BY ville ORDER BY solde DESC) AS rn
    FROM clients WHERE ville IS NOT NULL
) t WHERE rn = 1 ORDER BY ville;

+-------+-------------+--------+
| nom   | ville       | solde  |
+-------+-------------+--------+
| Émile | Alger       |  75.25 |
| David | Constantine |   NULL |
| Alice | Oran        | 100.50 |
+-------+-------------+--------+

David est premier de Constantine : dans un tri décroissant, la valeur NULL est classée en dernier, et il est seul dans son groupe.

Cumul (somme courante) :

SELECT id, montant, SUM(montant) OVER (ORDER BY id) AS cumul FROM commandes ORDER BY id;

+----+---------+-------+
| id | montant | cumul |
+----+---------+-------+
|  1 |   10.00 | 10.00 |
|  2 |   20.50 | 30.50 |
|  3 |    5.00 | 35.50 |
|  4 |    1.00 | 36.50 |
|  5 |    NULL | 36.50 |
+----+---------+-------+

Somme par partition, rang et valeur précédente :

SELECT id, nom, SUM(solde) OVER (PARTITION BY ville) AS tot_ville FROM clients WHERE ville IS NOT NULL ORDER BY id;
SELECT ville, RANK() OVER (ORDER BY COUNT(*) DESC) AS r, COUNT(*) FROM clients GROUP BY ville ORDER BY r, ville;
SELECT id, nom, solde - LAG(solde) OVER (ORDER BY id) AS diff FROM clients ORDER BY id;

La deuxième requête montre qu'une fonction de fenêtrage peut porter sur un agrégat : Alger et Oran (2 clients) sont ex æquo au rang 1, NULL et Constantine au rang 3. Les fonctions de fenêtrage sont évaluées après WHERE, GROUP BY et HAVING.

Fenêtre glissante (ROWS 1 PRECEDING : ligne précédente et ligne courante) :

SELECT nom, SUM(solde) OVER (ORDER BY id ROWS 1 PRECEDING) AS s FROM clients ORDER BY id;

+--------+--------+
| nom    | s      |
+--------+--------+
| Alice  | 100.50 |
| Bob    |  80.50 |
| Chloé  | -20.00 |
| David  |   0.00 |
| Émile  |  75.25 |
| Fatima |  85.25 |
+--------+--------+

8.19 Verrous de lecture et indications d'optimiseur#

Lectures verrouillantes#

SELECT ... FOR UPDATE   [OF table, ...] [NOWAIT | WAIT secondes | SKIP LOCKED]
SELECT ... FOR SHARE    [OF table, ...] [NOWAIT | WAIT secondes | SKIP LOCKED]
SELECT ... LOCK IN SHARE MODE           [NOWAIT | WAIT secondes | SKIP LOCKED]

La clause s'écrit en dernière position du bloc (après LIMIT) et n'accepte pas OF avec LOCK IN SHARE MODE. Elle s'applique aux tables du bloc, ou à celles de OF. Dans le modèle par défaut (concurrence optimiste multi-versions, MV-OCC), personne n'attend un verrou. Une ligne tenue par une autre transaction (ou modifiée depuis l'instantané de la transaction) donne, selon la clause :

Hors transaction (auto-validation), seul le contrôle est fait : le verrou n'est pas conservé après l'instruction. Les intentions de verrou d'une transaction explicite durent jusqu'à COMMIT ou ROLLBACK. Voir le chapitre 13 (« Transactions et concurrence ») pour les modèles de verrouillage.

START TRANSACTION;
SELECT nom, solde FROM clients WHERE id = 1 FOR UPDATE;
UPDATE clients SET solde = solde - 10 WHERE id = 1;
COMMIT;

Indications d'optimiseur /*+ ... */#

Un commentaire d'indications juste après SELECT est lu. Seule MAX_EXECUTION_TIME(n) a un effet : durée maximale d'exécution en millisecondes (1 à 2 147 483 647) ; au-delà, l'instruction échoue avec l'erreur 1969. Les autres indications reconnues sont lues puis ignorées ; une erreur de syntaxe dans le commentaire le fait ignorer en entier avec un avertissement, sans échec de la requête.

SELECT /*+ MAX_EXECUTION_TIME(1000) */ nom FROM clients WHERE ville = 'Oran';

Les indications d'index USE | FORCE | IGNORE {INDEX | KEY} [FOR {JOIN | ORDER BY | GROUP BY}] (…) (après un nom de table ou son alias, éventuellement plusieurs à la suite) sont acceptées puis ignorées : la requête rend le même résultat que sans indication et le moteur choisit seul l'index. Les noms d'index ne sont pas vérifiés. Aucune indication de jointure ne modifie l'algorithme ou l'ordre des jointures.

LIMIT n ROWS EXAMINED m est une autre borne d'exécution : l'instruction s'arrête après avoir examiné m lignes et rend le résultat partiel accompagné d'un avertissement (« The query exceeded LIMIT ROWS EXAMINED m. The query result may be incomplete »), sans erreur.

8.20 Lire un plan avec EXPLAIN#

EXPLAIN s'applique à SELECT, UPDATE, DELETE et INSERT ... SELECT (l'instruction est planifiée, jamais exécutée) ; EXPLAIN INSERT ... VALUES, EXPLAIN REPLACE ... VALUES et EXPLAIN LOAD DATA répondent par l'erreur 1235. EXPLAIN nom_de_table équivaut à DESCRIBE. Le format se limite à la sortie tabulaire par défaut ; FORMAT=TREE et FORMAT=JSON rendent le plan en arbre d'opérateurs (voir 8.21) et EXPLAIN ANALYZE / ANALYZE exécutent la requête (8.21).

Colonnes de la sortie : id, select_type, table, partitions, type, possible_keys, key, key_len, ref, rows, Extra. key_len vaut toujours NULL (les index sont en mémoire, sans longueur de clé sérialisée).

Exemples (avec un index idx_ville sur clients(ville) et idx_cli sur commandes(client_id)) :

EXPLAIN SELECT * FROM clients WHERE id = 1;

| id | select_type | table   | partitions | type   | possible_keys | key     | key_len | ref   | rows | Extra |
|  1 | SIMPLE      | clients | NULL       | eq_ref | PRIMARY       | PRIMARY |    NULL | const |    1 |       |

EXPLAIN SELECT * FROM clients WHERE ville = 'Oran';

| id | select_type | table   | partitions | type | possible_keys     | key       | key_len | ref   | rows | Extra |
|  1 | SIMPLE      | clients | NULL       | ref  | PRIMARY,idx_ville | idx_ville |    NULL | const |    2 |       |

EXPLAIN SELECT c.nom, o.montant FROM clients c JOIN commandes o ON o.client_id = c.id;

| id | select_type | table | partitions | type | possible_keys   | key  | key_len | ref  | rows | Extra                         |
|  1 | SIMPLE      | c     | NULL       | ALL  | PRIMARY,idx_ville | NULL |    NULL | NULL |    6 |                               |
|  1 | SIMPLE      | o     | NULL       | ALL  | PRIMARY,idx_cli | NULL |    NULL | NULL |    5 | Using join buffer (hash join) |

Lecture : la table c est parcourue en entier, la table o est chargée dans un tampon de jointure et appariée par hachage. Une jointure sans égalité entre colonnes (ON a.solde > b.solde) donne deux lignes ALL sans mention de Using join buffer : boucle imbriquée. Using filesort signale un tri (ORDER BY), Using temporary un regroupement (GROUP BY) ou une union dédoublonnée.

Limites de l'EXPLAIN de cette version, à connaître avant d'en tirer des conclusions :

• Il décrit les lectures de table, pas chaque opérateur : une sous-requête IN (SELECT ...) n'ajoute pas de ligne propre ; une sous-requête corrélée de la liste de sélection apparaît en DEPENDENT SUBQUERY (une ligne par table lue, Using where) ; une table dérivée apparaît par les lectures de son contenu (DERIVED).
• Les lectures par intervalle (id > 2, BETWEEN) et ORDER BY id LIMIT n sont annoncées ALL (avec Using filesort pour le tri) : seule l'égalité avec une constante utilise un index.
• Avec une jointure, une condition WHERE c.id = 1 sur une des tables ne fait pas passer sa lecture à eq_ref dans l'affichage (lecture ALL, condition en Using where).
• Les colonnes key_len et filtered ne sont pas renseignées (filtered n'existe pas).

Conduite à tenir pour une requête lente : lire type (ALL sur une grande table ?), ajouter un index UNIQUE ou secondaire sur la colonne comparée à une constante, vérifier l'égalité de type entre les deux colonnes d'une jointure, agréger avant de joindre, et poser MAX_EXECUTION_TIME ou max_join_size pour borner les requêtes d'exploration.

8.21 EXPLAIN ANALYZE et ANALYZE : temps et lignes réels#

EXPLAIN ANALYZE <instruction> (syntaxe MySQL 8) et ANALYZE [FORMAT=JSON] <instruction> (syntaxe MariaDB) exécutent l'instruction et rendent son plan avec, pour chaque opérateur, le nombre de lignes réellement produites et le temps passé. Les lignes de la requête ne sont jamais envoyées au client : seul le plan est rendu. Une erreur d'exécution (1242, dépassement de max_statement_time, KILL) est rendue telle quelle.

Exemple d'arbre (temps en millisecondes : premier résultat, puis total, tous deux inclusifs de ce qui est sous le nœud) :

-> Sort (top 2 row(s))  (actual time=0.070..0.070 rows=2 loops=1)
    -> Group aggregate (hash, 1 key(s))  (actual time=0.042..0.042 rows=3 loops=1)
        -> Table scan on Users  (rows=5)  (actual time=0.002..0.002 rows=5 loops=1)

(rows=N) est le nombre de lignes de la table (estimation du plan) ; loops=0 s'écrit (never executed) (source d'un LIMIT 0, par exemple). Les opérateurs sont : lecture (Table scan, Index lookup, Single-row index lookup, Vector index scan, avec with pushed-down filter si un prédicat est poussé dans la lecture), Filter, Project, jointures (Inner|Left|Right hash join, Nested loop ... join), Group aggregate / Aggregate, Sort, Limit, Remove duplicates, Derived table, Append (union), Window aggregate, Dependent subqueries.

Parallélisme. Une lecture répartie sur plusieurs fils devient un nœud Parallel gather|aggregate|sort (chronométré) dont les enfants sont la lecture ([parallel: degree N, M morsels]) et chaque étage (filtre, projection, jointure) : leurs lignes sont la somme de tous les fils, mais sans temps propre (la somme des temps des fils n'est pas un temps écoulé). Les lignes sont les mêmes qu'en série. Tables partitionnées (édition Cluster) : les partitions lues figurent sur la lecture.

Écritures. Comme MariaDB, ANALYZE UPDATE et ANALYZE DELETE (ou EXPLAIN ANALYZE UPDATE|DELETE) exécutent vraiment l'instruction : les lignes sont modifiées ou supprimées, avec les droits, verrous, déclencheurs et la transaction d'une instruction ordinaire (un ROLLBACK les défait). Le plan décrit la lecture des lignes à modifier ; r_rows est le nombre de lignes qu'elle a rendues. Un DELETE sans condition vide la table sans la lire : un seul nœud Delete all rows from t (Extra : Deleting all rows). Pour ne rien modifier, utiliser EXPLAIN seul. ANALYZE INSERT ..., ANALYZE REPLACE ... : erreur 1235. Dans un corps de routine, seul ANALYZE SELECT est admis. Une session MCP a besoin du niveau d'écriture pour ANALYZE UPDATE|DELETE.

Écarts avec les références : le JSON a la forme propre à MIRAJ (query_block.plan, nœuds operator, description, r_loops, r_rows, r_total_time_ms, r_first_row_ms, children), pas celle de MariaDB ni de MySQL ; pas d'estimation de coût (cost=) ni de condition dans les libellés ; les sous-requêtes d'expression (IN (SELECT ...), EXISTS, scalaires) ne sont pas détaillées, leur temps est inclus dans celui de l'opérateur qui les évalue ; loops vaut toujours 0 ou 1 ; ANALYZE TABLE reste une instruction reconnue non prise en charge (1235). Surcoût : une requête ordinaire n'est pas instrumentée ; sous ANALYZE, deux lectures d'horloge par lot de 4 096 lignes et par opérateur.