Der beste Weg, um die Ergebnisanzahl zu erhalten, bevor LIMIT angewendet wurde

Lesezeit: 1 Minute

Der beste Weg um die Ergebnisanzahl zu erhalten bevor LIMIT
EvilPuppeMaster

Beim Blättern durch Daten, die aus einer Datenbank stammen, müssen Sie wissen, wie viele Seiten vorhanden sind, um die Seitensprungsteuerelemente zu rendern.

Derzeit mache ich das, indem ich die Abfrage zweimal ausführe, einmal eingeschlossen in a count() um die Gesamtergebnisse zu bestimmen, und ein zweites Mal mit einem Limit, um nur die Ergebnisse zu erhalten, die ich für die aktuelle Seite benötige.

Dies scheint ineffizient. Gibt es einen besseren Weg, um festzustellen, wie viele Ergebnisse zuvor zurückgegeben worden wären? LIMIT wurde angewendet?

Ich verwende PHP und Postgres.

1646882047 249 Der beste Weg um die Ergebnisanzahl zu erhalten bevor LIMIT
Erwin Brandstetter

Reines SQL

Die Dinge haben sich seit 2008 geändert. Sie können a verwenden Fensterfunktion um die volle Zählung zu erhalten und das begrenzte Ergebnis in einer Abfrage. Eingeführt mit PostgreSQL 8.4 im Jahr 2009.

SELECT foo
     , count(*) OVER() AS full_count
FROM   bar
WHERE  <some condition>
ORDER  BY <some col>
LIMIT  <pagesize>
OFFSET <offset>;

Beachten Sie, dass dies kann erheblich teurer sein als ohne Gesamtzählung. Alle Zeilen müssen gezählt werden, und eine mögliche Abkürzung, die nur die obersten Zeilen aus einem übereinstimmenden Index nimmt, ist möglicherweise nicht mehr hilfreich.
Spielt bei kleinen Tischen keine große Rolle bzw full_count <= OFFSET + LIMIT. Angelegenheiten für eine wesentlich größere full_count.

Eckfall: wann OFFSET mindestens so groß ist wie die Anzahl der Zeilen aus der Basisabfrage, keine Reihe ist zurück gekommen. Also bekommt man auch nein full_count. Mögliche Alternative:

  • Führen Sie eine Abfrage mit einem LIMIT/OFFSET aus und erhalten Sie auch die Gesamtzahl der Zeilen

Abfolge von Ereignissen in a SELECT Anfrage

( 0. CTEs werden separat ausgewertet und materialisiert. In Postgres 12 oder höher kann der Planer solche wie Unterabfragen einbetten, bevor er an die Arbeit geht.) Nicht hier.

  1. WHERE Klausel (und JOIN Bedingungen, obwohl keine in Ihrem Beispiel) qualifizierende Zeilen aus der/den Basistabelle(n) filtern. Der Rest basiert auf der gefilterten Teilmenge.

(2. GROUP BY und Aggregatfunktionen würden hier hingehen.) Nicht hier.

(3. Sonstiges SELECT Listenausdrücke werden basierend auf gruppierten / aggregierten Spalten ausgewertet.) Nicht hier.

  1. Fensterfunktionen werden abhängig von angewendet OVER -Klausel und die Rahmenspezifikation der Funktion. Das Einfache count(*) OVER() basiert auf allen qualifizierenden Zeilen.

  2. ORDER BY

(6. DISTINCT oder DISTINCT ON würde hier hingehen.) Nicht hier.

  1. LIMIT / OFFSET werden basierend auf der festgelegten Reihenfolge angewendet, um Zeilen für die Rückgabe auszuwählen.

LIMIT / OFFSET wird mit zunehmender Anzahl von Zeilen in der Tabelle zunehmend ineffizient. Erwägen Sie alternative Ansätze, wenn Sie eine bessere Leistung benötigen:

  • Optimieren Sie die Abfrage mit OFFSET für große Tabellen

Alternativen, um die endgültige Zählung zu erhalten

Es gibt völlig unterschiedliche Ansätze, um die Anzahl der betroffenen Zeilen zu ermitteln (nicht die volle Zählung vorher OFFSET & LIMIT wurden angewendet). Postgres verfügt über eine interne Buchhaltung, wie viele Zeilen vom letzten SQL-Befehl betroffen waren. Einige Clients können auf diese Informationen zugreifen oder Zeilen selbst zählen (wie psql).

Sie können beispielsweise die Anzahl der betroffenen Zeilen in abrufen plpgsql unmittelbar nach Ausführung eines SQL-Befehls mit:

GET DIAGNOSTICS integer_var = ROW_COUNT;

Details im Handbuch.

Oder Sie können verwenden pg_num_rows in PHP. Oder ähnliche Funktionen in anderen Clients.

Verwandt:

  • Berechnen Sie die Anzahl der Zeilen, die von der Stapelabfrage in PostgreSQL betroffen sind

  • In älteren Versionen von Postgres können Sie mit Cursors etwas Ähnliches tun. z.B BEGIN; DECLARE c CURSOR FOR SELECT * FROM table; MOVE FORWARD 100 IN c; FETCH 10 FROM c; MOVE FORWARD ALL IN c; COMMIT;. Sie erhalten Ihre Daten vom FETCH zurück und können die Anzahl der Zeilen berechnen pg_affected_rows ruft entsprechend an.

    – Frank Bauer

    30. April 2013 um 23:13 Uhr

  • Dies macht eine zusätzliche Spalte full_count für die Gesamtzahl, Wiederholen der gleichen Gesamtzahl für jede Zeile im begrenzten Ergebnis. Das scheint mir ein wenig überflüssig und ineffizient. Ich bin mir nicht sicher, ob es für PostgreSQL langsamer ist, aber ich denke, das wird dazu führen, dass der Datenbankserver mehr Daten an den Anwendungsserver senden muss, richtig? Wäre es besser (schneller und effizienter), a WITH Abfrage, um die Gesamtzahl in einer Zeile zu erhalten, und dann UNION das mit dem begrenzten Ergebnis?

    – ma11hew28

    3. September 2016 um 20:52 Uhr


  • @mattdipasquale: Ein CTE ist normalerweise viel langsamer (mehr Overhead). Versuch es. Sie könnten Holen Sie sich die Zeilenanzahl mit einer plpgsql-Funktion mit GET DIAGNOSTICS

    – Erwin Brandstetter

    3. September 2016 um 23:49 Uhr

  • aber pg_num_rows soll nicht die Anzahl der Ergebnisse nach dem Limit zurückgeben?

    – entrauschen

    28. März 2018 um 21:33 Uhr

  • @denoise: Du hast recht, das war nicht ganz klar. Ich habe es klargestellt, um Missverständnisse zu vermeiden.

    – Erwin Brandstetter

    28. März 2018 um 22:04 Uhr

Der beste Weg um die Ergebnisanzahl zu erhalten bevor LIMIT
Grauer Panther

Wie ich beschreibe auf meinem BlogMySQL hat eine Funktion namens SQL_CALC_FOUND_ROWS. Dadurch entfällt die Notwendigkeit, die Abfrage zweimal auszuführen, aber es muss die Abfrage immer noch vollständig ausgeführt werden, selbst wenn die Begrenzungsklausel es ermöglicht hätte, vorzeitig zu stoppen.

Soweit ich weiß, gibt es keine ähnliche Funktion für PostgreSQL. Eine Sache, auf die Sie bei der Paginierung achten sollten (die häufigste Sache, für die LIMIT verwendet wird): Wenn Sie ein “OFFSET 1000 LIMIT 10” ausführen, bedeutet dies, dass die DB abrufen muss wenigstens 1010 Zeilen, auch wenn Sie nur 10 erhalten. Eine leistungsfähigere Methode besteht darin, sich den Wert der Zeile zu merken, nach der Sie die vorherige Zeile anordnen (in diesem Fall die 1000.), und die Abfrage so umzuschreiben: “.. WHERE order_row > value_of_1000_th LIMIT 10”. Der Vorteil ist, dass “order_row” höchstwahrscheinlich indiziert ist (wenn nicht, haben Sie ein Problem). Der Nachteil besteht darin, dass neue Elemente, die zwischen Seitenaufrufen hinzugefügt werden, ein wenig aus dem Takt geraten können (aber andererseits möglicherweise nicht für Besucher sichtbar sind und einen großen Leistungsgewinn darstellen können).

Sie könnten die Leistungseinbußen mindern, indem Sie die COUNT()-Abfrage nicht jedes Mal ausführen. Zwischenspeichern Sie die Anzahl der Seiten für beispielsweise 5 Minuten, bevor die Abfrage erneut ausgeführt wird. Wenn Sie nicht eine große Anzahl von INSERTs sehen, sollte das gut funktionieren.

Da Sie dies zum Zwecke des Paging wissen müssen, würde ich vorschlagen, die vollständige Abfrage einmal auszuführen, die Daten als serverseitigen Cache auf die Festplatte zu schreiben und diese dann durch Ihren Paging-Mechanismus zu leiten.

Wenn Sie die COUNT-Abfrage ausführen, um zu entscheiden, ob die Daten dem Benutzer bereitgestellt werden sollen oder nicht (dh wenn es > X Datensätze gibt, geben Sie einen Fehler zurück), müssen Sie beim COUNT-Ansatz bleiben.

985860cookie-checkDer beste Weg, um die Ergebnisanzahl zu erhalten, bevor LIMIT angewendet wurde

This website is using cookies to improve the user-friendliness. You agree by using the website further.

Privacy policy