Verwenden von Fensterfunktionen und CTEs in MySQL 8.0, um eine kumulative Summe ohne Hacks zu implementieren

Ca. übers. : In diesem Artikel gibt der Teamleiter des britischen Unternehmens Ticketsolve eine Lösung für sein sehr spezifisches Problem und demonstriert allgemeine Ansätze zur Erstellung sogenannter Akkumulationsfunktionen mit modernen MySQL 8.0-Funktionen. Die Auflistungen sind klar und mit detaillierten Erklärungen versehen, die helfen, das Wesentliche des Problems zu verstehen, selbst für diejenigen, die sich nicht so tief damit befasst haben.



Eine gängige Strategie für die Durchführung von Aktualisierungen mithilfe kumulativer Funktionen in MySQL ist die Verwendung benutzerdefinierter Variablen und MusterUPDATE [...] SET mycol = (@myvar := EXPRESSION(@myvar, mycol)).



Dieses Muster funktioniert nicht gut mit dem Optimierer (was zu nicht deterministischem Verhalten führt), daher haben sie beschlossen, es aufzugeben. Das Ergebnis ist eine Art Leere, weil (relativ) komplexe Logik jetzt schwieriger zu implementieren ist (zumindest mit der gleichen Einfachheit).



In diesem Artikel werden zwei Möglichkeiten zur Implementierung erläutert: Verwendung von Fensterfunktionen (kanonischer Ansatz) und Verwendung rekursiver CTEs (allgemeine Tabellenausdrücke).

Anforderungen und Hintergrund

Obwohl CTEs ziemlich intuitiv sind, empfehle ich für diejenigen, die mit ihnen nicht sehr vertraut sind, auf meinen vorherigen Beitrag zu diesem Thema zu verweisen .



Gleiches gilt für Fensterfunktionen: Ich werde Abfragen / Konzepte im Detail kommentieren, aber eine allgemeine Idee tut immer noch nicht weh. Eine große Anzahl von Büchern und Veröffentlichungen widmet sich Fensterfunktionen (weshalb ich immer noch nicht darüber geschrieben habe); In den meisten Beispielen werden Berechnungen jedoch entweder anhand von Finanzergebnissen oder anhand demografischer Indikatoren durchgeführt. In diesem Artikel werde ich jedoch einen realen Fall verwenden.



Auf der Softwareseite empfehle ich die Verwendung von MySQL 8.0.19 (aber nicht erforderlich). Alle Ausdrücke müssen in derselben Konsole ausgeführt werden, um wiederverwendet zu werden @venue_id.



In der Softwarewelt gibt es ein bekanntes Architekturdilemma: Sollte Logik auf Anwendungsebene oder auf Datenbankebene implementiert werden? Während dies eine legitime Frage ist, gehe ich in unserem Fall davon aus, dass die Logik auf der Basisebene bleiben sollte ; Der Grund hierfür könnten beispielsweise Geschwindigkeitsanforderungen sein (wie dies in unserem Fall der Fall war).

Aufgabe

Bei dieser Aufgabe vergeben wir Sitzplätze in einem bestimmten Saal (Theater).



Für geschäftliche Zwecke muss jedem Standort eine sogenannte "Gruppierung" zugewiesen werden - eine zusätzliche Nummer, die ihn darstellt.



Hier ist der Algorithmus zum Bestimmen des Gruppierungswerts:

1. beginne bei 0 und oben links;
2. Wenn zwischen dem aktuellen und dem vorherigen Leerzeichen ein Leerzeichen steht oder dies eine neue Zeile ist, addieren wir 2 zum vorherigen Wert (wenn dies nicht der absolute erste Platz ist), andernfalls erhöhen wir den Wert um 1;
3. einem Ort eine Gruppierung zuweisen;
4. Gehen Sie zu einer neuen Stelle in derselben Reihe oder zur nächsten Reihe (wenn die vorherige vorbei ist) und wiederholen Sie ab Punkt 2; Wir machen alles weiter, bis die Plätze ausgehen.

Algorithmus im 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

Im wirklichen Leben möchten wir, dass die Konfiguration links die rechts angezeigten Werte angibt:

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

Ausbildung

Lassen Sie die Basistabelle die folgende minimalistische Struktur haben:

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)
);

Wir brauchen die rowund Spalten nicht wirklich number. Auf der anderen Seite möchten wir keine Tabelle verwenden, deren Datensätze vollständig im Index enthalten sind (nur um den tatsächlichen Problemen näher zu sein).



Basierend auf dem obigen Diagramm sind die Koordinaten jedes Ortes (y, x):

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

Beachten Sie, dass wir y als erste Koordinate verwenden, da dies die Verfolgung der Zeilen erleichtert.



Sie müssen eine ausreichend große Anzahl von Datensätzen laden, um zu verhindern, dass der Optimierer unerwartete kurze Pfade findet. Natürlich verwenden wir rekursive CTEs:

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;

Ein paar Anmerkungen:

1. Hier wird CTE auf interessante (hoffentlich!) Weise verwendet: Jede Schleife stellt eine Veranstaltungsort-ID dar. Da jedoch für jeden Veranstaltungsort mehrere Standorte generiert werden sollen, führen wir eine Kreuzverknüpfung mit der Tabelle durch, die die Standorte enthält.
2. Verwendete den Zeilenkonstruktor aus Version 8.0.19 ( VALUES ROW()...), um eine ( verbindbare ) Tabelle darzustellen, ohne sie tatsächlich zu erstellen.
3. Erzeugt zufällige Werte für Zeile und Zahl als Platzhalter.
4. Der Einfachheit halber haben wir keine Optimierungen vorgenommen (z. B. sind Datentypen breiter als erforderlich; Indizes werden vor dem Einfügen von Datensätzen usw. hinzugefügt).

Alter Ansatz

Der gute alte Ansatz ist ganz einfach und unkompliziert:

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)

Nun, das war einfach (aber vergessen Sie nicht die Warnungen)!



Ein kleiner Exkurs: In diesem Fall verwende ich die Eigenschaften der Booleschen Arithmetik. Die folgenden Ausdrücke sind äquivalent:

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

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

Einige finden das intuitiv, andere nicht; es ist Geschmackssache. Von nun an werde ich einen kompakteren Ausdruck verwenden.



Mal sehen, das Ergebnis:

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 |
-- +-------+------+------+----------+

Toller Ansatz!



Leider hat es einen "kleinen" Nachteil: Es funktioniert hervorragend, außer wenn es nicht funktioniert ...



Tatsache ist, dass das Abfrageoptimierungsprogramm nicht unbedingt Berechnungen von links nach rechts durchführt, sodass Zuweisungen (: =) in der falschen Reihenfolge ausgeführt werden können was zum falschen Ergebnis führt. Nach dem Update von MySQL treten häufig Probleme auf.



In MySQL 8.0 ist diese Funktionalität in der Tat veraltet:

--    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)'.
-- [...]

Nun, lassen Sie uns die Situation beheben!

Moderner Ansatz Nr. 1: Fensterfunktionen

Die Einführung von Fensterfunktionen war in der MySQL-Welt ein mit Spannung erwartetes Ereignis.



Im Allgemeinen funktioniert die "gleitende" Natur von Fensterfunktionen gut mit kumulativen Funktionen. Einige komplexe kumulative Funktionen erfordern jedoch die Ergebnisse des letzten Ausdrucks - Funktionen, die Fensterfunktionen nicht unterstützen, da sie mit Spalten arbeiten.



Dies bedeutet nicht, dass das Problem nicht gelöst werden kann: Es muss nur überdacht werden.



In unserem Fall kann die Aufgabe in zwei Teile unterteilt werden. Die Gruppierung für jeden Standort kann als Summe von zwei Werten betrachtet werden:

• die Seriennummer jedes Ortes,
• der kumulative Wert der Inkremente aller Stellen vor dieser

Wer mit Fensterfunktionen vertraut ist, erkennt hier die typischen Muster.



Die Sequenznummer jedes Sitzes ist eine eingebaute Funktion:

ROW_NUMBER() OVER <window>

Aber mit dem kumulativen Wert ist alles viel interessanter ... Um ihn zu berechnen, führen wir zwei Aktionen aus:

• Zählen Sie das Inkrement für jeden Ort und schreiben Sie es in die Tabelle (oder den CTE).
• Dann addieren wir für jeden Standort die Inkremente für diesen Standort mithilfe der Fensterfunktion.

Werfen wir einen Blick auf 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 |
-- +-------+---+---+----------+

Toll!



(Beachten Sie, dass ich das UPDATE der Einfachheit halber von nun an weglasse.)



Lassen Sie uns die Anfrage analysieren.

Logik auf hoher Ebene

Der folgende CTE (bearbeitet) :

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 |
-- +-------+-----------+

… Berechnet die Inkremente für jeden Standort gegenüber dem vorherigen (dazu LAG()später mehr). Es funktioniert auf jedem Datensatz und dem vorhergehenden und ist nicht kumulativ.



Um die kumulativen Inkremente zu berechnen, verwenden wir einfach eine Fensterfunktion, um die Summe bis einschließlich jeder Position zu berechnen:

-- (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.)

LAG () Fensterfunktion

Die LAG-Funktion gibt in ihrer einfachsten Form ( LAG(x)) den vorherigen Wert einer bestimmten Spalte zurück. Die klassische Unannehmlichkeit mit solchen Funktionen besteht darin, die ersten Einträge im Fenster zu behandeln. Da es keinen vorherigen Datensatz gibt, geben sie NULL zurück. Bei LAG können Sie den gewünschten Wert als dritten Parameter angeben:

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

Durch die Angabe der Standardwerte stellen wir sicher, dass die allererste Stelle in den Fenstergrenzen dieselbe Logik hat wie die Stelle nach der anderen (x-1) und ohne die Zeile (y) zu ändern.



Eine alternative Lösung ist die Verwendung IFNULL, die Ausdrücke sind jedoch ziemlich umständlich:

--  ,  !
--
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)

Der zweite Parameter LAG()ist die Anzahl der Positionen, die innerhalb des Fensters zurückbewegt werden müssen. 1 ist der vorherige Wert (dies ist auch der Standardwert).

Technische Aspekte

Benannte Fenster

Unsere Abfrage verwendet oft dasselbe Fenster. Die folgenden zwei Abfragen sind formal gleichwertig:

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;

Das zweite kann jedoch zu einem nicht optimalen Verhalten führen (auf das ich - zumindest in der Vergangenheit - gestoßen bin): Der Optimierer kann die Fenster als unabhängig betrachten und jedes einzeln berechnen. Aus diesem Grund empfehle ich Ihnen, immer benannte Fenster zu verwenden (zumindest wenn sie wiederholt werden).

PARTITION BY-Anweisung

Normalerweise werden Fensterfunktionen für eine Partition ausgeführt. In unserem Fall sieht es so aus:

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); -- !

Da das Fenster mit dem vollständigen Satz von Datensätzen übereinstimmt (der nach der Bedingung gefiltert wird WHERE), müssen wir ihn nicht angeben (die Partition).



Wenn diese Abfrage jedoch für die gesamte Tabelle ausgeführt seatswerden müsste, müsste sie durchgeführt werden, damit das Fenster für alle zurückgesetzt wird venue_id.

Sortierung

Die Anforderung ORDER BYwird auf Fensterebene festgelegt:

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)

In diesem Fall ist die Fenstersortierung von SELECT getrennt. Es ist sehr wichtig! Das Verhalten dieser Anfrage:

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

… nicht definiert. Wenden wir uns dem Handbuch zu :

Die Abfrageergebniszeichenfolgen werden aus der FROM-Klausel bestimmt, nachdem die WHERE-, GROUP BY- und HAVING-Klauseln ausgeführt wurden, und die Ausführung innerhalb des Fensters erfolgt vor ORDER BY, LIMIT und SELECT DISTINCT.

Einige Überlegungen

Im Allgemeinen ist es für diese Art von Problem sinnvoll, die Statusänderung für jeden Datensatz zu berechnen und dann zu summieren - anstatt jeden Datensatz als Funktion des vorherigen darzustellen.



Diese Lösung ist komplexer als die Funktionalität, die sie ersetzt, aber gleichzeitig zuverlässig. Leider ist dieser Ansatz nicht immer möglich oder einfach zu implementieren. Hier kommen rekursive CTEs ins Spiel.

Moderner Ansatz Nr. 2: Rekursive CTEs

Dieser Ansatz erfordert aufgrund der begrenzten Funktionen des CTE in MySQL einige Tricks. Auf der anderen Seite handelt es sich um eine einheitliche, direkte Lösung, bei der kein Überdenken eines globalen Ansatzes erforderlich ist.



Beginnen wir mit einer vereinfachten Version der endgültigen Anfrage:

-- `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! Diese Abfrage ist (relativ) einfach, aber was noch wichtiger ist, sie drückt die kumulative Gruppierungsfunktion auf einfachste Weise aus:

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

Die Logik ist auch für diejenigen klar, die mit CTE nicht allzu vertraut sind. Die erste Reihe ist der erste Platz in der Halle, in der Reihenfolge:

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

Im rekursiven Teil iterieren wir:

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

Konditionieren Sie WHEREzusammen mit den Operatoren ORDER BYund LIMITsuchen Sie einfach den nächsten Ort, einen Ort mit demselben venue_id, der jedoch für lshimi-Koordinaten (x, y) in der Sequenz (Veranstaltungsort_ID, x, y) verwendet wird.



Der Teil s.venue_idim Sortierausdruck ist sehr wichtig! Es erlaubt uns, einen Index zu verwenden.



Betreiber SELECT:

• führt Akkumulation durch (berechnet (p_)grouping),
• stellt Werte für die aktuelle Position ( s.id, s.venue_id, s.y, s.x) in dem nächsten Zyklus.

Wir entscheiden uns, FROM groupingsdie Anforderungen für die CTE-Rekursivität zu erfüllen.



Interessant ist hier, dass wir einen rekursiven CTE als Iterator verwenden, aus einer Tabelle groupingsin einer rekursiven Unterabfrage abrufen und diese zusammenfügen seats, um die Daten für die weitere Verarbeitung zu finden.



JOINist formal cross, aber aufgrund des Operators wird LIMITnur ein Datensatz zurückgegeben.

Arbeitsversion

Leider funktioniert die obige Abfrage nicht, da sie ORDER BYderzeit in rekursiven Unterabfragen nicht unterstützt wird. Darüber hinaus unterscheidet sich die hier verwendete Semantik LIMITvon der typischen Semantik , die für eine externe Abfrage gilt :

LIMIT wird jetzt unterstützt [..] Die Auswirkung auf das resultierende Dataset entspricht der Verwendung von LIMIT mit einem externen SELECT

Dies ist jedoch kein so ernstes Problem. Schauen wir uns eine funktionierende Version an:

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 |
-- +-------+------+------+------------+

Es ist etwas unangenehm, eine Unterabfrage zu verwenden, aber dieser Ansatz funktioniert und die Boilerplate ist hier minimal, da ohnehin mehrere Ausdrücke erforderlich sind.



Anstatt die mit der Vereinigung groupingsund verknüpfte Reihenfolge und Begrenzung seatsdurchzuführen, führen wir dies hier innerhalb der Unterabfrage durch und übergeben es an die äußere Abfrage, die dann nur den Zieldatensatz auswählt.

Überlegungen zur Leistung

Lassen Sie uns den Abfrageausführungsplan mit EXPLAIN ANALYZE untersuchen:

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)

Der Plan entspricht den Erwartungen. In diesem Fall liegt die Basis des optimalen Plans in der Indexsuche:

-> 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)

... von größter Bedeutung. Die Leistung nimmt erheblich ab, wenn Sie die Indizes scannen (dh die Indexdatensätze linear scannen, anstatt nach den richtigen zu suchen).



Damit diese Strategie funktioniert, müssen die verknüpften Indizes vorhanden sein und vom Optimierer so effizient wie möglich verwendet werden.



Wenn die Einschränkungen in Zukunft aufgehoben werden, muss keine Unterabfrage verwendet werden, was die Aufgabe für den Optimierer erheblich vereinfacht.

Alternative für suboptimale Pläne

Wenn der optimale Plan nicht ermittelt werden kann, verwenden Sie eine temporäre Tabelle:

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;

Selbst wenn Index-Scans in dieser Abfrage übergeben werden, kosten sie selected_seatsviel Geld , da die Tabelle sehr klein ist.

Fazit

Ich freue mich sehr, dass der effiziente, aber fehlerhafte Workflow jetzt durch die in MySQL 8.0 eingeführte relativ einfache Funktionalität ersetzt werden kann.



In der Zwischenzeit wird die Entwicklung neuer Funktionen für 8.0 fortgesetzt, was eine bereits erfolgreiche Veröffentlichung noch besser macht.



Erfolgreiche Rekursion!

PS vom Übersetzer

Lesen Sie auch in unserem Blog:

• " Upgrade von MySQL (Percona Server) von 5.7 auf 8.0 ";
• " Datenbanken und Kubernetes (Rückblick und Videobericht) ";
• " Mehr Entwickler müssen dies über Datenbanken wissen. "