ProHoster > Blog > Administration > SQL HowTo: Schreiben Sie eine while-Schleife direkt in die Abfrage, oder „Elementare Drei-Wege-Abfrage“

SQL HowTo: Schreiben Sie eine while-Schleife direkt in die Abfrage, oder „Elementare Drei-Wege-Abfrage“

Es stellt sich regelmĂ€ĂŸig die Aufgabe, verwandte Daten anhand einer Reihe von SchlĂŒsseln zu finden, bis wir die benötigte Gesamtanzahl an DatensĂ€tzen erreicht haben..

Das gĂ€ngigste Beispiel ist, die 20 Ă€ltesten Aufgaben anzuzeigen,die auf der Liste der Mitarbeiter stehen, (zum Beispiel innerhalb einer Abteilung). FĂŒr verschiedene Management-Dashboards mit kurzen Zusammenfassungen zu den Arbeitsbereichen ist ein solches Thema hĂ€ufig erforderlich. In diesem Artikel betrachten wir die Implementierung einer „naiven“ Lösung fĂŒr eine solche Aufgabe in PostgreSQL sowie eine „intelligentere“ und ganz komplexe Algorithmus-„Schleife“ in SQL mit einem Ausstiegsbedingung basierend auf gefundenen Daten,

SQL HowTo: Schreiben Sie eine while-Schleife direkt in die Abfrage, oder „Elementare Drei-Wege-Abfrage“

die sowohl fĂŒr die allgemeine Weiterbildung als auch fĂŒr die Anwendung in anderen Ă€hnlichen FĂ€llen nĂŒtzlich sein kann. Lassen Sie uns einen Beispieldatensatz ausdem vorherigen Artikel nehmen. Um sicherzustellen, dass die ausgegebenen DatensĂ€tze bei Übereinstimmung der sortierten Werte nicht von Mal zu Mal „hĂŒpfen“,

erweitern wir den Index, indem wir den PrimĂ€rschlĂŒssel hinzufĂŒgen. Das verleiht ihm zugleich Einzigartigkeit und gewĂ€hrleistet die Eindeutigkeit der Sortierreihenfolge:. Um sicherzustellen, dass die ausgegebenen EintrĂ€ge bei gleichen sortierten Werten nicht "springen", erweitern wir den Index durch die HinzufĂŒgung des PrimĂ€rschlĂŒssels.Das verleiht ihm zudem sofortige Einzigartigkeit und garantiert uns eine eindeutige Sortierreihenfolge:

INDEX FÜR task(owner_id, task_date, id) ERSTELLEN;
-- und entfernen wir den alten
INDEX task_owner_id_task_date_idx HEBEN;

So wie es klingt, so wird es geschrieben

ZunĂ€chst entwerfen wir die einfachste Anfrage, indem wir die IDs der AusfĂŒhrer ĂŒbergeben als Array als Eingabeparameter:

SELECT
  *
FROM
  task
WHERE
  owner_id = ANY('{1,2,4,8,16,32,64,128,256,512}'::integer[])
ORDER BY
  task_date, id
LIMIT 20;

SQL HowTo: Schreiben Sie eine while-Schleife direkt in die Abfrage, oder „Elementare Drei-Wege-Abfrage“
[sehen Sie sich explain.tensor.ru an]

Es ist etwas enttĂ€uschend – wir haben nur 20 DatensĂ€tze angefordert, aber der Index-Scan hat uns 960 Zeilen zurĂŒckgegeben, die wir dann auch noch sortieren mussten
 Lassen Sie uns versuchen, weniger zu lesen.

unnest + ARRAY

Der erste Gedanke, der uns helfen wird – wenn wir nur 20 sortierte DatensĂ€tze brauchen, reicht es, nicht mehr als 20 sortierte in derselben Reihenfolge pro SchlĂŒssel zu lesen. GlĂŒcklicherweise haben wir einen geeigneten Index (owner_id, task_date, id). Wir nutzen dasselbe Extraktions- und „Spaltenumwandlungs“-Mechanismus

der vollstĂ€ndigen Tabelle , wie auch in. Zudem wenden wir die Aggregation in ein Array mit der Funktion in einem frĂŒheren ArtikelARRAY() WITH T AS ( SELECT unnest(ARRAY( SELECT t FROM task t WHERE owner_id = unnest ORDER BY task_date, id LIMIT 20 -- hier begrenzen wir... )) r FROM unnest('{1,2,4,8,16,32,64,128,256,512}'::integer[]) ) SELECT (r).* FROM T ORDER BY (r).task_date, (r).id LIMIT 20; -- ... und hier auch - ebenfalls:

WITH T AS (
  SELECT
    unnest(ARRAY(
      SELECT
        t
      FROM
        task t
      WHERE
        owner_id = unnest
      ORDER BY
        task_date, id
      LIMIT 20 -- hier begrenzen wir...
    )) r
  FROM
    unnest('{1,2,4,8,16,32,64,128,256,512}'::integer[])
)
SELECT
  (r).* 
FROM
  T
ORDER BY
  (r).task_date, (r).id
LIMIT 20; -- ... und hier ebenfalls -

SQL HowTo: Schreiben Sie eine while-Schleife direkt in die Abfrage, oder „Elementare Drei-Wege-Abfrage“
[sehen Sie sich explain.tensor.ru an]

Oh, das ist schon viel besser! 40 % schneller und 4,5 Mal weniger Daten musste gelesen werden.

Materialisierung von TabelleneintrĂ€gen ĂŒber CTEIch möchte darauf hinweisen, dass in einigen FĂ€llen der Versuch, sofort mit den Feldern des Datensatzes nach dessen Suche im Unterabfrage zu arbeiten, ohne in CTE zu „verpacken“, zu „Vervielfachung“ des InitPlans proportional zur Anzahl dieser Felder fĂŒhren kann:

SELECT
  ((
    SELECT
      t
    FROM
      task t
    WHERE
      owner_id = 1
    ORDER BY
      task_date, id
    LIMIT 1
  ).*);

Ergebnis  (Kosten=4.77..4.78 Zeilen=1 Breite=16) (tatsÀchliche Zeit=0.063..0.063 Zeilen=1 Schleifen=1)
  Puffer: Shared Hit=16
  InitPlan 1 (gibt $0 zurĂŒck)
    ->  Limit  (Kosten=0.42..1.19 Zeilen=1 Breite=48) (tatsÀchliche Zeit=0.031..0.032 Zeilen=1 Schleifen=1)
          Puffer: Shared Hit=4
          ->  Index-Scan mithilfe von task_owner_id_task_date_id_idx auf Aufgabe t  (Kosten=0.42..387.57 Zeilen=500 Breite=48) (tatsÀchliche Zeit=0.030..0.030 Zeilen=1 Schleifen=1)
                Index-Bedingung: (owner_id = 1)
                Puffer: Shared Hit=4
  InitPlan 2 (gibt $1 zurĂŒck)
    ->  Limit  (Kosten=0.42..1.19 Zeilen=1 Breite=48) (tatsÀchliche Zeit=0.008..0.009 Zeilen=1 Schleifen=1)
          Puffer: Shared Hit=4
          ->  Index-Scan mithilfe von task_owner_id_task_date_id_idx auf Aufgabe t_1  (Kosten=0.42..387.57 Zeilen=500 Breite=48) (tatsÀchliche Zeit=0.008..0.008 Zeilen=1 Schleifen=1)
                Index-Bedingung: (owner_id = 1)
                Puffer: Shared Hit=4
  InitPlan 3 (gibt $2 zurĂŒck)
    ->  Limit  (Kosten=0.42..1.19 Zeilen=1 Breite=48) (tatsÀchliche Zeit=0.008..0.008 Zeilen=1 Schleifen=1)
          Puffer: Shared Hit=4
          ->  Index-Scan mithilfe von task_owner_id_task_date_id_idx auf Aufgabe t_2  (Kosten=0.42..387.57 Zeilen=500 Breite=48) (tatsÀchliche Zeit=0.008..0.008 Zeilen=1 Schleifen=1)
                Index-Bedingung: (owner_id = 1)
                Puffer: Shared Hit=4
  InitPlan 4 (gibt $3 zurĂŒck)
    ->  Limit  (Kosten=0.42..1.19 Zeilen=1 Breite=48) (tatsÀchliche Zeit=0.009..0.009 Zeilen=1 Schleifen=1)
          Puffer: Shared Hit=4
          ->  Index-Scan mithilfe von task_owner_id_task_date_id_idx auf Aufgabe t_3  (Kosten=0.42..387.57 Zeilen=500 Breite=48) (tatsÀchliche Zeit=0.009..0.009 Zeilen=1 Schleifen=1)
                Index-Bedingung: (owner_id = 1)
                Puffer: Shared Hit=4

Die gleiche Datensatz wurde viermal „abgefragt“... Bis PostgreSQL 11 war dieses Verhalten regelmĂ€ĂŸig anzutreffen, und die Lösung besteht darin, sie in ein CTE zu „wickeln“, was in diesen Versionen eine eindeutige Grenze fĂŒr den Optimierer darstellt.

Rekursiver Akkumulator

In der vorherigen Version haben wir insgesamt gelesen 200 Zeilen fĂŒr die benötigten 20. Nicht mehr 960, aber noch weniger – ist das möglich?

Lassen Sie uns das Wissen nutzen, dass wir benötigen insgesamt 20 DatensĂ€tze. Das heißt, wir werden die Datenauslesung nur bis zur Erreichung der benötigten Menge iterieren.

Schritt 1: Startliste

Offensichtlich sollte unsere „Ziel“-Liste aus 20 DatensĂ€tzen mit den „ersten“ DatensĂ€tzen eines unserer owner_id-SchlĂŒssel beginnen. Daher finden wir zuerst solche „allerersten“ fĂŒr jeden der SchlĂŒssel und tragen sie in die Liste ein, sortiert nach der Reihenfolge, die wir wollen – (task_date, id).

SQL HowTo: Schreiben Sie eine while-Schleife direkt in die Abfrage, oder „Elementare Drei-Wege-Abfrage“

Schritt 2: Finden der „nĂ€chsten“ DatensĂ€tze

Nun, wenn wir den ersten Datensatz aus unserer Liste nehmen und anfangen, weiter im Index zu „gehen“, wĂ€hrend wir den owner_id-SchlĂŒssel beibehalten, dann sind alle gefundenen DatensĂ€tze genau die nĂ€chsten in der resultierenden Auswahl. NatĂŒrlich nur, bis wir den Anwendungskey des zweiten Datensatzes in der Liste ĂŒberschreiten.

Wenn es so aussieht, dass wir den zweiten Eintrag "ĂŒberschritten" haben, dann sollte der letzte gelesene Eintrag anstelle des ersten in die Liste aufgenommen werden (mit derselben owner_id), danach sortieren wir die Liste erneut.

SQL HowTo: Schreiben Sie eine while-Schleife direkt in die Abfrage, oder „Elementare Drei-Wege-Abfrage“

Das bedeutet, dass wir immer nur einen Eintrag pro SchlĂŒssel in der Liste haben (wenn die EintrĂ€ge aufgebraucht sind und wir die "Grenze" nicht ĂŒberschritten haben, wird der erste Eintrag einfach entfernt und es wird nichts hinzugefĂŒgt), und diese sind immer nach dem aufsteigenden Anwendungs-SchlĂŒssel (task_date, id) sortiert.

SQL HowTo: Schreiben Sie eine while-Schleife direkt in die Abfrage, oder „Elementare Drei-Wege-Abfrage“

Schritt 3: Wir filtern und "entfalten" die EintrÀge

In einigen Zeilen unserer rekursiven Auswahl rv werden EintrĂ€ge dupliziert – zuerst finden wir solche, die "die Grenze des zweiten Eintrags der Liste ĂŒberschreiten", und setzen sie dann als den ersten in der Liste ein. Das erste Auftreten muss gefiltert werden.

Schrecklicher endgĂŒltiger Anfragesatz

MIT REKURSIVE T AS (
  -- #1 : FĂŒgen wir die "ersten" EintrĂ€ge fĂŒr jeden SchlĂŒssel in die Liste ein
  MIT wrap AS ( -- "materialisieren" wir die DatensÀtze, damit der Zugriff auf die Felder keine Multiplikation von InitPlan/SubPlan erzeugt
    MIT T AS (
      AUSWÄHLEN
        (
          AUSWÄHLEN
            r
          VON
            task r
          WO
            owner_id = unnest
          BESTELLEN NACH
            task_date, id
          LIMIT 1
        ) r
      VON
        unnest('{1,2,4,8,16,32,64,128,256,512}'::integer[])
    )
    AUSWÄHLEN
      array_agg(r BESTELLEN NACH (r).task_date, (r).id) list -- sortieren die Liste in der gewĂŒnschten Reihenfolge
    VON
      T
  )
  AUSWÄHLEN
    list
  , list[1] rv
  , FALSE not_cross
  , 0 size
  VON
    wrap
UNION ALL
  -- #2 : Lesen wir die EintrĂ€ge des ersten SchlĂŒssels, bis wir ĂŒber den zweiten SchlĂŒssel hinausgehen
  AUSWÄHLEN
    FALL
      -- wenn nichts fĂŒr den ersten Eintrag gefunden wurde
      WENN X._r IST NICHT UNTERSCHIEDLICH VON NULL DANN
        T.list[2:] -- entfernen wir ihn aus der Liste
      -- wenn wir den zweiten SchlĂŒssel nicht ĂŒberschritten haben
      WENN X.not_cross THEN
        T.list -- ziehen wir einfach die gleiche Liste ohne Modifikationen weiter
      -- wenn der zweite Eintrag nicht mehr in der Liste ist
      WENN T.list[2] IST NULL DANN
        -- einfach eine leere Liste zurĂŒckgeben
        '{}'
      -- sortieren das Dictionary um, entfernen den ersten Eintrag und fĂŒgen den letzten der gefundenen hinzu
      SONST (
        AUSWÄHLEN
          coalesce(T.list[2] || array_agg(r BESTELLEN NACH (r).task_date, (r).id), '{}')
        VON
          unnest(T.list[3:] || X._r) r
      )
    END
  , X._r
  , X.not_cross
  , T.size + X.not_cross::integer
  VON
    T
  , LATERAL(
      MIT wrap AS ( -- "materialisieren" wir die DatensÀtze
        AUSWÄHLEN
          FALL
            -- wenn wir trotzdem ĂŒber den zweiten Eintrag hinausgegangen sind
            WENN NICHT T.not_cross
              -- dann ist der benötigte Datensatz der erste aus der Liste
              DANN T.list[1]
            SONST ( -- wenn wir ihn nicht ĂŒberschritten haben, ist der SchlĂŒssel wie im vorherigen Eintrag geblieben - orientieren wir uns daran
              AUSWÄHLEN
                _r
              VON
                task _r
              WO
                owner_id = (rv).owner_id UND
                (task_date, id) > ((rv).task_date, (rv).id)
              BESTELLEN NACH
                task_date, id
              LIMIT 1
            )
          END _r
      )
      AUSWÄHLEN
        _r
      , FALL
          -- wenn der zweite Eintrag nicht mehr in der Liste ist, aber wir wenigstens etwas gefunden haben
          WENN list[2] IST NULL UND _r IST NICHT UNTERSCHIEDLICH VON NULL DANN
            TRUE
          SONST -- nichts gefunden oder "ĂŒberschritten"
            coalesce(((_r).task_date, (_r).id) < ((list[2]).task_date, (list[2]).id), FALSE)
        END not_cross
      VON
        wrap
    ) X
  WO
    T.size < 20 UND -- begrenzen wir hier die Anzahl
    T.list IST NICHT UNTERSCHIEDLICH VON '{}' -- oder bis die Liste nicht mehr existiert
)
-- #3 : "entfalten" wir die EintrÀge - die Reihenfolge ist durch die Erstellung garantiert
AUSWÄHLEN
  (rv).* 
VON
  T
WO
  not_cross; -- nehmen wir nur die "nicht ĂŒberschreitenden" EintrĂ€ge

SQL HowTo: Schreiben Sie eine while-Schleife direkt in die Abfrage, oder „Elementare Drei-Wege-Abfrage“
[sehen Sie sich explain.tensor.ru an]

So haben wir 50% LesevorgĂ€nge gegen 20% Laufzeit eingetauscht. Das bedeutet, wenn Sie Grund zur Annahme haben, dass das Lesen lange dauern könnte (z.B. wenn die Daten oft nicht im Cache sind und von der Festplatte abgerufen werden mĂŒssen), kann man durch diesen Ansatz weniger auf das Lesen angewiesen sein.

In jedem Fall hat sich die Laufzeit besser ergeben als im „naiven“ ersten Ansatz. Welche dieser 3 Varianten Sie verwenden möchten, liegt an Ihnen.

Quelle: habr.com

Kaufen Sie zuverlĂ€ssiges Hosting fĂŒr Websites mit DDoS-Schutz, VPS VDS-Server đŸ”„ Kaufen Sie zuverlĂ€ssiges Hosting fĂŒr Websites mit DDoS-Schutz, VPS VDS-Server | ProHoster