Utilisation des fonctions de fenêtre et des CTE dans MySQL 8.0 pour implémenter un total cumulatif sans hacks

Environ. trad. : Dans cet article, le chef d'équipe de la société britannique Ticketsolve partage une solution à son problème très spécifique, tout en présentant des approches générales pour créer des fonctions dites d'accumulation à l'aide des fonctionnalités modernes de MySQL 8.0. Ses listes sont claires et fournies avec des explications détaillées, ce qui aide à comprendre l'essence du problème, même pour ceux qui ne s'y sont pas plongés si profondément.



Une stratégie courante pour effectuer des mises à jour à l'aide de fonctions cumulatives dans MySQL consiste à utiliser des variables et des modèles personnalisésUPDATE [...] SET mycol = (@myvar := EXPRESSION(@myvar, mycol)).



Ce modèle ne fonctionne pas bien avec l'optimiseur (conduisant à un comportement non déterministe), ils ont donc décidé de l'abandonner. Le résultat est une sorte de vide car une logique (relativement) complexe est désormais plus difficile à mettre en œuvre (du moins avec la même simplicité).



L'article discutera de deux façons de l'implémenter: en utilisant des fonctions de fenêtre (approche canonique) et en utilisant des CTE récursifs (expressions de table générales).

Exigences et contexte

Bien que les CTE soient assez intuitifs, pour ceux qui ne les connaissent pas très bien, je recommande de se référer à mon article précédent sur ce sujet .



La même chose est vraie pour les fonctions de fenêtre: je vais commenter les requêtes / concepts en détail, mais une idée générale ne fait toujours pas de mal. Un grand nombre de livres et de publications sont consacrés aux fonctions de fenêtre (c'est pourquoi je n'ai toujours pas écrit à leur sujet); cependant, dans la plupart des exemples, les calculs sont effectués soit sur les résultats financiers, soit sur des indicateurs démographiques. Cependant, dans cet article, j'utiliserai un cas réel.



Pour les logiciels, je recommande d'utiliser MySQL 8.0.19 (mais pas obligatoire). Toutes les expressions doivent être exécutées dans la même console pour être réutilisées @venue_id.



Dans le monde du logiciel, il existe un célèbre dilemme architectural: faut-il implémenter la logique au niveau de l'application ou au niveau de la base de données? Bien que ce soit une question parfaitement valable, dans notre cas, je suppose que la logique doit rester au niveau de base; la raison peut être, par exemple, les exigences de vitesse (comme ce fut le cas dans notre cas).

Tâche

Dans cette tâche, nous attribuons des sièges dans une certaine salle (théâtre).



À des fins commerciales, chaque emplacement doit se voir attribuer un soi-disant «regroupement» - un numéro supplémentaire le représentant.



Voici l'algorithme pour déterminer la valeur de regroupement:

1. commencer à 0 et en haut à gauche;
2. s'il y a un espace vide entre l'actuel et le précédent, ou s'il s'agit d'une nouvelle ligne, alors on ajoute 2 à la valeur précédente (si ce n'est pas la première place absolue), sinon, on augmente la valeur de 1;
3. attribuer un groupement à un lieu;
4. aller à un nouvel endroit dans la même ligne ou à la ligne suivante (si la précédente est terminée) et répéter à partir du point 2; nous continuons tout jusqu'à ce que les places soient épuisées.

Algorithme en pseudocode:

current_grouping = 0

for each row:
  for each number:
    if (is_there_a_space_after_last_seat or is_a_new_row) and is_not_the_first_seat:
      current_grouping += 2
    else
      current_grouping += 1

    seat.grouping = current_grouping

Dans la vraie vie, nous voulons que la configuration de gauche donne les valeurs affichées à droite:

 x→  0   1   2        0   1   2
y   ╭───┬───┬───╮    ╭───┬───┬───╮
↓ 0 │ x │ x │   │    │ 1 │ 2 │   │
    ├───┼───┼───┤    ├───┼───┼───┤
  1 │ x │   │ x │    │ 4 │   │ 6 │
    ├───┼───┼───┤    ├───┼───┼───┤
  2 │ x │   │   │    │ 8 │   │   │
    ╰───┴───┴───╯    ╰───┴───┴───╯

Entraînement

Laissez la table de base avoir la structure minimaliste suivante:

CREATE TABLE seats (
  id         INT AUTO_INCREMENT PRIMARY KEY,
  venue_id   INT,
  y          INT,
  x          INT,
  `row`      VARCHAR(16),
  number     INT,
  `grouping` INT,
  UNIQUE venue_id_y_x (venue_id, y, x)
);

Nous n'avons pas vraiment besoin des colonnes rowet number. Par contre, nous ne voulons pas utiliser une table dont les enregistrements sont entièrement contenus dans l'index (juste pour être au plus près de vrais problèmes).



Sur la base du diagramme ci-dessus, les coordonnées de chaque emplacement sont (y, x):

• (0, 0), (0, 1)
• (1, 0), (1, 2)
• (20)

Notez que nous utilisons y comme première coordonnée, car cela facilite le suivi des lignes.



Vous devez charger un nombre suffisant d'enregistrements pour empêcher l'optimiseur de trouver des chemins courts inattendus. Bien sûr, nous utilisons des CTE récursifs:

INSERT INTO seats(venue_id, y, x, `row`, number)
WITH RECURSIVE venue_ids (id) AS
(
  SELECT 0
  UNION ALL
  SELECT id + 1 FROM venue_ids WHERE id + 1 < 100000
)
SELECT /*+ SET_VAR(cte_max_recursion_depth = 1M) */
  v.id,
  c.y, c.x,
  CHAR(ORD('A') + FLOOR(RAND() * 3) USING ASCII) `row`,
  FLOOR(RAND() * 3) `number`
FROM venue_ids v
     JOIN (
       VALUES
         ROW(0, 0),
         ROW(0, 1),
         ROW(1, 0),
         ROW(1, 2),
         ROW(2, 0)
     ) c (y, x)
;

ANALYZE TABLE seats;

Quelques notes:

1. Ici, CTE est utilisé d'une manière intéressante (espérons-le!): Chaque boucle représente un venue_id, mais puisque nous voulons que plusieurs emplacements soient générés pour chaque lieu, nous faisons une jointure croisée avec la table contenant les emplacements.
2. Le constructeur de lignes v8.0.19 ( VALUES ROW()...) est utilisé pour représenter une table ( joignable ) sans la créer.
3. Génère des valeurs aléatoires pour la ligne et le nombre comme espaces réservés.
4. Par souci de simplicité, nous n'avons fait aucune optimisation (par exemple, les types de données sont plus larges que nécessaire; les index sont ajoutés avant l'insertion d'enregistrements, etc.).

Ancienne approche

La bonne vieille approche est assez simple et directe:

SET @venue_id = 5000; --  venue id;  () id 

SET @grouping = -1;
SET @y = -1;
SET @x = -1;

WITH seat_groupings (id, y, x, `grouping`, tmp_y, tmp_x) AS
(
  SELECT
    id, y, x,
    @grouping := @grouping + 1 + (seats.x > @x + 1 OR seats.y != @y),
    @y := seats.y,
    @x := seats.x
  FROM seats
  WHERE venue_id = @venue_id
  ORDER BY y, x
)
UPDATE
  seats s
  JOIN seat_groupings sg USING (id)
SET s.grouping = sg.grouping
;

-- Query OK, 5 rows affected, 3 warnings (0,00 sec)

Et bien c'était facile (mais n'oubliez pas les avertissements)!



Une petite digression: dans ce cas, j'utilise les propriétés de l'arithmétique booléenne. Les expressions suivantes sont équivalentes:

SELECT seats.x > @x + 1 OR seats.y != @y `increment`;

SELECT IF (
  seats.x > @x + 1 OR seats.y != @y,
  1,
  0
) `increment`;

Certains trouvent cela intuitif, d'autres non; c'est une question de goût. À partir de maintenant, j'utiliserai une expression plus compacte.



Voyons le résultat:

SELECT id, y, x, `grouping` FROM seats WHERE venue_id = @venue_id ORDER BY y, x;

-- +-------+------+------+----------+
-- | id    | y    | x    | grouping |
-- +-------+------+------+----------+
-- | 24887 |    0 |    0 |        1 |
-- | 27186 |    0 |    1 |        2 |
-- | 29485 |    1 |    0 |        4 |
-- | 31784 |    1 |    2 |        6 |
-- | 34083 |    2 |    0 |        8 |
-- +-------+------+------+----------+

Excellente approche!



Hélas, il a un défaut "mineur": ça marche très bien sauf quand ça ne marche pas ...



Le fait est que l'optimiseur de requêtes n'effectue pas forcément des calculs de gauche à droite, donc les opérations d'assignation (: =) peuvent être effectuées dans le mauvais ordre conduisant au mauvais résultat. Les gens sont souvent confrontés à ce problème après la mise à jour de MySQL.



Dans MySQL 8.0, cette fonctionnalité est en effet obsolète:

--    UPDATE.
--
SHOW WARNINGS\G
-- *************************** 1. row ***************************
--   Level: Warning
--    Code: 1287
-- Message: Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
-- [...]

Eh bien, réglons la situation!

Approche moderne n ° 1: fonctions de fenêtrage

L'introduction des fonctions de fenêtre a été un événement très attendu dans le monde MySQL.



D'une manière générale, la nature «glissante» des fonctions de fenêtre fonctionne bien avec les fonctions cumulatives. Cependant, certaines fonctions cumulatives complexes nécessitent les résultats de la dernière expression - fonctionnalité que les fonctions de fenêtre ne prennent pas en charge car elles opèrent sur des colonnes.



Cela ne veut pas dire que le problème ne peut pas être résolu, il doit simplement être repensé.



Dans notre cas, la tâche peut être divisée en deux parties. Le regroupement de chaque emplacement peut être considéré comme la somme de deux valeurs:

• le numéro de série de chaque lieu,
• la valeur cumulée des incréments de toutes les places précédant celle-ci.

Ceux qui sont familiers avec les fonctions de fenêtrage reconnaîtront ici les modèles typiques.



Le numéro de séquence de chaque siège est une fonction intégrée:

ROW_NUMBER() OVER <window>

Mais avec la valeur cumulée, tout est bien plus intéressant ... Pour le calculer, on effectue deux actions:

• compter l'incrément pour chaque lieu et l'écrire dans le tableau (ou CTE),
• puis, pour chaque emplacement, nous additionnons les incréments pour cet emplacement à l'aide de la fonction de fenêtre.

Jetons un œil à SQL:

WITH
increments (id, increment) AS
(
  SELECT
    id,
    x > LAG(x, 1, x - 1) OVER tzw + 1 OR y != LAG(y, 1, y) OVER tzw
  FROM seats
  WHERE venue_id = @venue_id
  WINDOW tzw AS (ORDER BY y, x)
)
SELECT
  s.id, y, x,
  ROW_NUMBER() OVER tzw + SUM(increment) OVER tzw `grouping`
FROM seats s
     JOIN increments i USING (id)
WINDOW tzw AS (ORDER BY y, x)
;

-- +-------+---+---+----------+
-- | id    | y | x | grouping |
-- +-------+---+---+----------+
-- | 24887 | 0 | 0 |        1 |
-- | 27186 | 0 | 1 |        2 |
-- | 29485 | 1 | 0 |        4 |
-- | 31784 | 1 | 2 |        6 |
-- | 34083 | 2 | 1 |        8 |
-- +-------+---+---+----------+

Génial!



(Notez que j'omets désormais la mise à jour par souci de simplicité.)



Analysons la demande.

Logique de haut niveau

Le CTE suivant (édité) :

SELECT
  id,
  x > LAG(x, 1, x - 1) OVER tzw + 1 OR y != LAG(y, 1, y) OVER tzw `increment`
FROM seats
WHERE venue_id = @venue_id
WINDOW tzw AS (ORDER BY y, x)
;

-- +-------+-----------+
-- | id    | increment |
-- +-------+-----------+
-- | 24887 |         0 |
-- | 27186 |         0 |
-- | 29485 |         1 |
-- | 31784 |         1 |
-- | 34083 |         1 |
-- +-------+-----------+

… Calcule les incréments pour chaque emplacement à partir du précédent (plus d'informations LAG()plus tard). Il fonctionne sur chaque enregistrement et celui qui le précède et n'est pas cumulatif.



Maintenant, pour calculer les incréments cumulatifs, nous allons simplement utiliser une fonction de fenêtre pour calculer la somme jusqu'à et y compris chaque emplacement:

-- (CTE here...)
SELECT
  s.id, y, x,
  ROW_NUMBER() OVER tzw `pos.`,
  SUM(increment) OVER tzw `cum.incr.`
FROM seats s
     JOIN increments i USING (id)
WINDOW tzw AS (ORDER BY y, x);

-- +-------+---+---+------+-----------+
-- | id    | y | x | pos. | cum.incr. | (grouping)
-- +-------+---+---+------+-----------+
-- | 24887 | 0 | 0 |    1 |         0 | = 1 + 0 (curr.)
-- | 27186 | 0 | 1 |    2 |         0 | = 2 + 0 (#24887) + 0 (curr.)
-- | 29485 | 1 | 0 |    3 |         1 | = 3 + 0 (#24887) + 0 (#27186) + 1 (curr.)
-- | 31784 | 1 | 2 |    4 |         2 | = 4 + 0 (#24887) + 0 (#27186) + 1 (#29485) + 1 (curr.)
-- | 34083 | 2 | 1 |    5 |         3 | = 5 + 0 (#24887) + 0 (#27186) + 1 (#29485) + 1 (#31784)↵
-- +-------+---+---+------+-----------+     + 1 (curr.)

Fonction de fenêtre LAG ()

La fonction LAG, dans sa forme la plus simple ( LAG(x)), renvoie la valeur précédente d'une colonne donnée. L'inconvénient classique de ces fonctions est de gérer la ou les premières entrées de la fenêtre. Puisqu'il n'y a aucun enregistrement précédent, ils renvoient NULL. Dans le cas de LAG, vous pouvez spécifier la valeur souhaitée comme troisième paramètre:

LAG(x, 1, x - 1) --    `x -1`
LAG(y, 1, y)     --    `y`

En spécifiant les valeurs par défaut, nous nous assurons que la toute première place dans les limites de la fenêtre aura la même logique que pour la place suivant l'autre (x-1) et sans changer la ligne (y).



Une solution alternative consiste à utiliser IFNULL, cependant, les expressions sont assez lourdes:

--  ,  !
--
IFNULL(x > LAG(x) OVER tzw + 1 OR y != LAG(y) OVER tzw, 0)
IFNULL(x > LAG(x) OVER tzw + 1, FALSE) OR IFNULL(y != LAG(y) OVER tzw, FALSE)

Le deuxième paramètre LAG()est le nombre de positions à reculer dans la fenêtre; 1 est la valeur précédente (c'est aussi la valeur par défaut).

Aspects techniques

Fenêtres nommées

Notre requête utilise la même fenêtre plusieurs fois. Les deux requêtes suivantes sont formellement équivalentes:

SELECT
  id,
  x > LAG(x, 1, x - 1) OVER tzw + 1
    OR y != LAG(y, 1, y) OVER tzw
FROM seats
WHERE venue_id = @venue_id
WINDOW tzw AS (ORDER BY y, x);

SELECT
  id,
  x > LAG(x, 1, x - 1) OVER (ORDER BY y, x) + 1
    OR y != LAG(y, 1, y) OVER (ORDER BY y, x)
FROM seats
WHERE venue_id = @venue_id;

Cependant, la seconde peut conduire à un comportement non optimal (que j'ai rencontré - du moins dans le passé): l'optimiseur peut considérer les fenêtres indépendantes et calculer chacune séparément. Pour cette raison, je vous conseille de toujours utiliser des fenêtres nommées (du moins lorsqu'elles sont répétées).

Instruction PARTITION BY

Les fonctions de fenêtrage sont généralement exécutées sur une partition. Dans notre cas, cela ressemblera à ceci:

SELECT
  id,
  x > LAG(x, 1, x - 1) OVER tzw + 1
    OR y != LAG(y, 1, y) OVER tzw
FROM seats
WHERE venue_id = @venue_id
WINDOW tzw AS (PARTITION BY venue_id ORDER BY y, x); -- !

Puisque la fenêtre correspond à l'ensemble complet des enregistrements (qui est filtré par la condition WHERE), nous n'avons pas besoin de le spécifier (la partition).



Mais si cette requête devait être exécutée sur l'ensemble de la table seats, alors il faudrait le faire pour que la fenêtre soit réinitialisée pour tout le monde venue_id.

Tri

La demande ORDER BYest définie au niveau de la fenêtre:

SELECT
  id,
  x > LAG(x, 1, x - 1) OVER tzw + 1
    OR y != LAG(y, 1, y) OVER tzw
FROM seats
WHERE venue_id = @venue_id
WINDOW tzw AS (ORDER BY y, x)

Dans ce cas, le tri des fenêtres est distinct de SELECT. Il est très important! Le comportement de cette demande:

SELECT
  id,
  x > LAG(x, 1, x - 1) OVER tzw + 1
    OR y != LAG(y, 1, y) OVER tzw
FROM seats
WHERE venue_id = @venue_id
WINDOW tzw AS ()
ORDER BY y, x

… indéfini. Passons au manuel :

Les chaînes de résultat de la requête sont déterminées à partir de la clause FROM après que les clauses WHERE, GROUP BY et HAVING ont été exécutées, et l'exécution dans la fenêtre se produit avant ORDER BY, LIMIT et SELECT DISTINCT.

Quelques considérations

En termes généraux, pour ce type de problème, il est logique de calculer le changement d'état pour chaque enregistrement, puis de les additionner - au lieu de représenter chaque enregistrement en fonction du précédent.



Cette solution est plus complexe que la fonctionnalité qu'elle remplace, mais en même temps elle est fiable. Hélas, cette approche n'est pas toujours possible ou facile à mettre en œuvre. C'est là que les CTE récursifs entrent en jeu.

Approche moderne n ° 2: CTE récursifs

Cette approche nécessite un peu de ruse en raison des capacités limitées du CTE dans MySQL. D'un autre côté, il s'agit d'une solution directe unique, qui ne nécessite donc pas de repenser une approche globale.



Commençons par une version simplifiée de la requête finale:

-- `p_`  `Previous`    
--
WITH RECURSIVE groupings (p_id, p_venue_id, p_y, p_x, p_grouping) AS
(
  (
    SELECT id, venue_id, y, x, 1
    FROM seats
    WHERE venue_id = @venue_id
    ORDER BY y, x
    LIMIT 1
  )

  UNION ALL

  SELECT
    s.id, s.venue_id, s.y, s.x,
    p_grouping + 1 + (s.x > p_x + 1 OR s.y != p_y)
  FROM groupings, seats s
  WHERE s.venue_id = p_venue_id AND (s.y, s.x) > (p_y, p_x)
  ORDER BY s.venue_id, s.y, s.x
  LIMIT 1
)
SELECT * FROM groupings;

Bingo! Cette requête est (relativement) simple, mais surtout, elle exprime la fonction de regroupement cumulatif de la manière la plus simple possible:

p_grouping + 1 + (s.x > p_x + 1 OR s.y != p_y)

--   :

@grouping := @grouping + 1 + (seats.x > @x + 1 OR seats.y != @y),
@y := seats.y,
@x := seats.x

La logique est claire même pour ceux qui ne sont pas trop familiers avec CTE. La première rangée est le premier siège de la salle, dans l'ordre:

SELECT id, venue_id, y, x, 1
FROM seats
WHERE venue_id = @venue_id
ORDER BY y, x
LIMIT 1

Dans la partie récursive, on itère:

SELECT
  s.id, s.venue_id, s.y, s.x,
  p_grouping + 1 + (s.x > p_x + 1 OR s.y != p_y)
FROM groupings, seats s
WHERE s.venue_id = p_venue_id AND (s.y, s.x) > (p_y, p_x)
ORDER BY s.venue_id, s.y, s.x
LIMIT 1

Conditionnez WHEREavec les opérateurs ORDER BYet LIMITtrouvez simplement le prochain endroit, un endroit avec le même venue_id, mais utilisé pour les coordonnées lshimi (x, y) dans la séquence (venue_id, x, y).



La partie s.venue_idde l'expression de tri est très importante! Cela nous permet d'utiliser un index.



Opérateur SELECT:

• effectue l'accumulation (calcule (p_)grouping),
• fournit des valeurs pour la position actuelle ( s.id, s.venue_id, s.y, s.x) dans le cycle suivant.

Nous choisissons FROM groupingsde répondre aux exigences de récursivité CTE.



Ce qui est intéressant ici, c'est que nous utilisons un CTE récursif comme itérateur, en récupérant une table groupingsdans une sous-requête récursive et en la joignant seatspour trouver les données pour un traitement ultérieur.



JOINest formellement croisé, mais LIMITun seul enregistrement est renvoyé à cause de l'opérateur .

Version de travail

Malheureusement, la requête ci-dessus ne fonctionne pas car elle ORDER BYn'est actuellement pas prise en charge dans les sous-requêtes récursives. De plus, la sémantique LIMITutilisée ici diffère de la sémantique typique qui s'applique à une requête externe :

LIMIT est désormais pris en charge [..] L'effet sur l'ensemble de données résultant est le même que l'utilisation de LIMIT avec un SELECT externe

Cependant, ce n'est pas un problème si grave. Jetons un coup d'œil à une version de travail:

WITH RECURSIVE groupings (p_id, p_venue_id, p_y, p_x, p_grouping) AS
(
  (
    SELECT id, venue_id, y, x, 1
    FROM seats
    WHERE venue_id = @venue_id
    ORDER BY y, x
    LIMIT 1
  )

  UNION ALL

  SELECT
    s.id, s.venue_id, s.y, s.x,
    p_grouping + 1 + (s.x > p_x + 1 OR s.y != p_y)
  FROM groupings, seats s WHERE s.id = (
    SELECT si.id
    FROM seats si
    WHERE si.venue_id = p_venue_id AND (si.y, si.x) > (p_y, p_x)
    ORDER BY si.venue_id, si.y, si.x
    LIMIT 1
  )
)
SELECT * FROM groupings;

-- +-------+------+------+------------+
-- | p_id  | p_y  | p_x  | p_grouping |
-- +-------+------+------+------------+
-- | 24887 |    0 |    0 |          1 |
-- | 27186 |    0 |    1 |          2 |
-- | 29485 |    1 |    0 |          4 |
-- | 31784 |    1 |    2 |          6 |
-- | 34083 |    2 |    0 |          8 |
-- +-------+------+------+------------+

Il est un peu inconfortable d'utiliser une sous-requête, mais cette approche fonctionne et le passe-partout est minimal ici, car plusieurs expressions sont nécessaires de toute façon.



Ici, au lieu de faire le tri et la limitation associés à l'union groupingset seats, nous le faisons dans la sous-requête et la passons à la requête externe, qui sélectionne alors uniquement l'enregistrement cible.

Réflexions sur la performance

Examinons le plan d'exécution de la requête en utilisant EXPLAIN ANALYZE:

mysql> EXPLAIN ANALYZE WITH RECURSIVE groupings [...]

-> Table scan on groupings  (actual time=0.000..0.001 rows=5 loops=1)
    -> Materialize recursive CTE groupings  (actual time=0.140..0.141 rows=5 loops=1)
        -> Limit: 1 row(s)  (actual time=0.019..0.019 rows=1 loops=1)
            -> Index lookup on seats using venue_id_y_x (venue_id=(@venue_id))  (cost=0.75 rows=5) (actual time=0.018..0.018 rows=1 loops=1)
        -> Repeat until convergence
            -> Nested loop inner join  (cost=3.43 rows=2) (actual time=0.017..0.053 rows=2 loops=2)
                -> Scan new records on groupings  (cost=2.73 rows=2) (actual time=0.001..0.001 rows=2 loops=2)
                -> Filter: (s.id = (select #5))  (cost=0.30 rows=1) (actual time=0.020..0.020 rows=1 loops=5)
                    -> Single-row index lookup on s using PRIMARY (id=(select #5))  (cost=0.30 rows=1) (actual time=0.014..0.014 rows=1 loops=5)
                    -> Select #5 (subquery in condition; dependent)
                        -> Limit: 1 row(s)  (actual time=0.007..0.008 rows=1 loops=9)
                            -> Filter: ((si.y,si.x) > (groupings.p_y,groupings.p_x))  (cost=0.75 rows=5) (actual time=0.007..0.007 rows=1 loops=9)
                                -> Index lookup on si using venue_id_y_x (venue_id=groupings.p_venue_id)  (cost=0.75 rows=5) (actual time=0.006..0.006 rows=4 loops=9)

Le plan est conforme aux attentes. Dans ce cas, la base du plan optimal réside dans les recherches d'index:

-> Nested loop inner join  (cost=3.43 rows=2) (actual time=0.017..0.053 rows=2 loops=2)
-> Single-row index lookup on s using PRIMARY (id=(select #5))  (cost=0.30 rows=1) (actual time=0.014..0.014 rows=1 loops=5)
-> Index lookup on si using venue_id_y_x (venue_id=groupings.p_venue_id)  (cost=0.75 rows=5) (actual time=0.006..0.006 rows=4 loops=9)

... d'une importance capitale. Les performances chuteront considérablement si vous scannez les index (c'est-à-dire que vous scannez linéairement les enregistrements d'index au lieu de rechercher les bons).



Ainsi, pour que cette stratégie fonctionne, les index associés doivent être en place et être utilisés le plus efficacement possible par l'optimiseur.



Si les restrictions sont levées à l'avenir, il ne sera pas nécessaire d'utiliser une sous-requête, ce qui simplifiera considérablement la tâche de l'optimiseur.

Alternative pour les plans sous-optimaux

Dans le cas où le plan optimal ne peut pas être déterminé, utilisez une table temporaire:

CREATE TEMPORARY TABLE selected_seats (
  id INT NOT NULL PRIMARY KEY,
  y INT,
  x INT,
  UNIQUE (y, x)
)
SELECT id, y, x
FROM seats WHERE venue_id = @venue_id;

WITH RECURSIVE
groupings (p_id, p_y, p_x, p_grouping) AS
(
  (
    SELECT id, y, x, 1
    FROM seats
    WHERE venue_id = @venue_id
    ORDER BY y, x
    LIMIT 1
  )

  UNION ALL

  SELECT
    s.id, s.y, s.x,
    p_grouping + 1 + (s.x > p_x + 1 OR s.y != p_y)
  FROM groupings, seats s WHERE s.id = (
    SELECT ss.id
    FROM selected_seats ss
    WHERE (ss.y, ss.x) > (p_y, p_x)
    ORDER BY ss.y, ss.x
    LIMIT 1
    )
)
SELECT * FROM groupings;

Même si les analyses d'index sont passées dans cette requête, elles coûtent cher, car la table est selected_seatstrès petite.

Conclusion

Je suis très heureux que le flux de travail efficace mais défectueux puisse maintenant être remplacé par la fonctionnalité assez simple introduite dans MySQL 8.0.



En attendant, le développement de nouvelles fonctionnalités pour 8.0 se poursuit, ce qui améliore encore une version déjà réussie.



Récursivité réussie!

PS du traducteur

Lisez aussi sur notre blog:

• " Mise à niveau de MySQL (Percona Server) de 5.7 à 8.0 ";
• " Bases de données et Kubernetes (examen et rapport vidéo) ";
• " Davantage de développeurs ont besoin de le savoir sur les bases de données. "