Mithilfe von Cursorn können Sie Stapelempfang aus der Datenbank erhalten und eine große Datenmenge verarbeiten, ohne Anwendungsspeicher zu verschwenden. Ich bin sicher, dass jeder Webentwickler mindestens einmal und nicht nur einmal vor mir vor einer ähnlichen Aufgabe stand. In diesem Artikel werde ich Ihnen erklären, in welchen Aufgaben Cursor nützlich sein können, und ich werde am Beispiel von PostrgeSQL vorgefertigten Code für die Arbeit mit ihnen aus PHP + Doctrine geben.
Das Problem
Stellen wir uns vor, wir haben ein PHP-Projekt, groß und schwer. Sicherlich wurde es mit Hilfe eines Rahmens geschrieben. Zum Beispiel Symfony. Es wird auch eine Datenbank verwendet, z. B. PostgreSQL, und die Datenbank verfügt über ein Kennzeichen für 2.000.000 Datensätze mit Informationen zu Bestellungen. Und das Projekt selbst ist eine Schnittstelle zu diesen Aufträgen, die sie anzeigen und filtern können. Und lassen Sie mich sagen, das läuft ziemlich gut.
Jetzt wurden wir gebeten (wurden Sie noch nicht gefragt? Sie werden definitiv gefragt), das Ergebnis der Filterbestellungen in eine Excel-Datei hochzuladen. Lassen Sie uns eine Schaltfläche mit einem Tabellensymbol aufschlagen, die eine Datei mit Befehlen an den Benutzer ausspuckt.
Wie wird es normalerweise entschieden und warum ist es schlecht?
Wie geht ein Programmierer, der einer solchen Aufgabe noch nicht begegnet ist? Er macht SELECT in der Datenbank, liest die Ergebnisse der Abfrage, konvertiert die Antwort in eine Excel-Datei und gibt sie an den Browser des Benutzers weiter. Die Aufgabe funktioniert, der Test ist abgeschlossen, aber Probleme beginnen in der Produktion.
Sicherlich haben wir ein Speicherlimit für PHP in vernünftigen (wohl) 1 GB pro Prozess festgelegt, und sobald diese 2 Millionen Zeilen nicht mehr in dieses Gigabyte passen, bricht alles zusammen. PHP stürzt mit einem Fehler "Nicht genügend Speicher" ab und Benutzer beschweren sich, dass die Datei nicht hochgeladen wird. Dies geschieht, weil wir eine eher naive Methode zum Entladen von Daten aus der Datenbank gewählt haben. Alle Daten werden zuerst aus dem Datenbankspeicher (und der darunter liegenden Festplatte) in den RAM des PHP-Prozesses übertragen, dann verarbeitet und in den Browser hochgeladen.
Damit die Daten immer im Speicher abgelegt werden, müssen Sie sie in Teilen aus der Datenbank entnehmen. Zum Beispiel wurden 10.000 Datensätze gelesen, verarbeitet, in eine Datei geschrieben und so oft.
Nun, denkt der Programmierer, der unsere Aufgabe zum ersten Mal erfüllt hat. Dann mache ich eine Schleife und entleere die Abfrageergebnisse in Teilen, wobei LIMIT und OFFSET angegeben werden. Es funktioniert, ist aber ein sehr teurer Vorgang für die Datenbank. Daher dauert das Hochladen des Berichts nicht 30 Sekunden, sondern 30 Minuten (es ist nicht so schlimm!). Übrigens, wenn dem Programmierer außer OFFSET derzeit nichts in den Sinn kommt, gibt es immer noch viele Möglichkeiten, dies zu erreichen, ohne die Datenbank zu vergewaltigen.
Gleichzeitig verfügt die Datenbank selbst über eine integrierte Funktion zum Threading von Lesedaten - Cursor.
Cursor
Ein Cursor ist ein Zeiger auf eine Zeile in den Ergebnissen einer Abfrage, die in der Datenbank gespeichert ist. Wenn Sie sie verwenden, können Sie SELECT nicht im Modus des sofortigen Herunterladens von Daten ausführen, sondern den Cursor mit dieser Auswahl öffnen. Als nächstes empfangen wir den Datenfluss aus der Datenbank, wenn sich der Cursor vorwärts bewegt. Dies führt zu demselben Ergebnis: Wir lesen die Daten partiell, aber die Datenbank findet nicht die gleiche Aufgabe, um die Zeile zu finden, mit der sie beginnen soll, wie dies bei OFFSET der Fall ist.
Der Cursor wird nur innerhalb der Transaktion geöffnet und bleibt so lange bestehen, bis die Transaktion aktiv ist (es gibt eine Ausnahme, siehe WITH HOLD ). Dies bedeutet, dass wir eine lange Transaktion haben, wenn wir langsam viele Daten aus der Datenbank lesen. Das ist manchmal schlecht , Sie müssen dieses Risiko verstehen und akzeptieren.
Cursor in der Lehre
Versuchen wir, die Arbeit mit Cursorn in Doctrine zu implementieren. Wie sieht zunächst eine Aufforderung zum Öffnen eines Cursors aus?
BEGIN; DECLARE mycursor1 CURSOR FOR ( SELECT * FROM huge_table );
DECLARE erstellt und öffnet den Cursor für die angegebene SELECT-Abfrage. Nachdem Sie den Cursor erstellt haben, können Sie Daten daraus lesen:
FETCH FORWARD 10000 FROM mycursor1; < 10 000 > FETCH FORWARD 10000 FROM mycursor1; < 10 000 > ...
Und so weiter, bis FETCH eine leere Liste zurückgibt. Dies bedeutet, dass sie bis zum Ende gescrollt haben.
COMMIT;
Wir skizzieren eine mit Doctrine kompatible Klasse, die die Arbeit mit dem Cursor kapselt. Und um 80% des Problems in 20% der Zeit zu lösen , funktioniert es nur mit nativen Abfragen. Nennen wir es also PgSqlNativeQueryCursor.
Konstruktor:
public function __construct(NativeQuery $query) { $this->query = $query; $this->connection = $query->getEntityManager()->getConnection(); $this->cursorName = uniqid('cursor_'); assert($this->connection->getDriver() instanceof PDOPgSqlDriver); }
Hier generiere ich einen Namen für den zukünftigen Cursor.
Da die Klasse PostgreSQL-spezifischen SQL-Code in der Klasse enthält, ist es besser zu überprüfen, ob unser Treiber PG ist.
Von der Klasse brauchen wir drei Dinge:
- Den Cursor öffnen können.
- Sie können Daten an uns zurücksenden.
- Den Cursor schließen können.
Öffnen Sie den Cursor:
public function openCursor() { if ($this->connection->getTransactionNestingLevel() === 0) { throw new \BadMethodCallException('Cursor must be used inside a transaction'); } $query = clone $this->query; $query->setSQL(sprintf( 'DECLARE %s CURSOR FOR (%s)', $this->connection->quoteIdentifier($this->cursorName), $this->query->getSQL() )); $query->execute($this->query->getParameters()); $this->isOpen = true; }
Wie gesagt, Cursor öffnen sich in einer Transaktion. Daher überprüfe ich hier, dass wir nicht vergessen haben, diese Methode innerhalb einer bereits geöffneten Transaktion aufzurufen. (Gott sei Dank, die Zeit ist vergangen, in der ich eine Transaktion hier eröffnen würde!)
Um das Erstellen und Initialisieren einer neuen NativeQuery zu vereinfachen, klone ich einfach diejenige, die in den Konstruktor eingegeben wurde, und verpacke sie in DECLARE ... CURSOR FOR (here_original_query). Ich führe es aus.
Lassen Sie uns die Methode getFetchQuery erstellen. Es werden keine Daten zurückgegeben, sondern eine weitere Anforderung, die nach Belieben verwendet werden kann, um die gewünschten Daten in bestimmten Stapeln abzurufen. Dies gibt dem aufrufenden Code mehr Freiheit.
public function getFetchQuery(int $count = 1): NativeQuery { $query = clone $this->query; $query->setParameters([]); $query->setSQL(sprintf( 'FETCH FORWARD %d FROM %s', $count, $this->connection->quoteIdentifier($this->cursorName) )); return $query; }
Die Methode hat einen Parameter - dies ist die Größe des Pakets, das Teil der von dieser Methode zurückgegebenen Anforderung wird. Ich wende den gleichen Trick beim Klonen von Abfragen an, überschreibe die darin enthaltenen Parameter und ersetze SQL durch das Konstrukt FETCH ... FROM ...;.
Um nicht zu vergessen, den Cursor vor dem ersten Aufruf von getFetchQuery () zu öffnen (plötzlich bekomme ich nicht mehr genug Schlaf), öffne ich ihn implizit direkt in der Methode getFetchQuery ():
public function getFetchQuery(int $count = 1): NativeQuery { if (!$this->isOpen) { $this->openCursor(); } …
Und die openCursor () -Methode selbst wird privat gemacht. Ich sehe überhaupt keine Fälle, in denen ich es explizit aufrufen muss.
In getFetchQuery () habe ich FORWARD fest codiert, um den Cursor um eine bestimmte Anzahl von Zeilen vorwärts zu bewegen. Die FETCH-Aufrufmodi sind jedoch sehr unterschiedlich. Fügen wir sie auch hinzu?
const DIRECTION_NEXT = 'NEXT'; const DIRECTION_PRIOR = 'PRIOR'; const DIRECTION_FIRST = 'FIRST'; const DIRECTION_LAST = 'LAST'; const DIRECTION_ABSOLUTE = 'ABSOLUTE';
Die Hälfte von ihnen akzeptiert die Anzahl der Zeilen im Parameter und die andere Hälfte nicht. Folgendes habe ich bekommen:
public function getFetchQuery(int $count = 1, string $direction = self::DIRECTION_FORWARD): NativeQuery { if (!$this->isOpen) { $this->openCursor(); } $query = clone $this->query; $query->setParameters([]); if ( $direction == self::DIRECTION_ABSOLUTE || $direction == self::DIRECTION_RELATIVE || $direction == self::DIRECTION_FORWARD || $direction == self::DIRECTION_BACKWARD ) { $query->setSQL(sprintf( 'FETCH %s %d FROM %s', $direction, $count, $this->connection->quoteIdentifier($this->cursorName) )); } else { $query->setSQL(sprintf( 'FETCH %s FROM %s', $direction, $this->connection->quoteIdentifier($this->cursorName) )); } return $query; }
Schließen Sie den Cursor mit CLOSE. Es ist nicht erforderlich, auf den Abschluss der Transaktion zu warten:
public function close() { if (!$this->isOpen) { return; } $this->connection->exec('CLOSE ' . $this->connection->quoteIdentifier($this->cursorName)); $this->isOpen = false; }
Zerstörer:
public function __destruct() { if ($this->isOpen) { $this->close(); } }
Hier ist die ganze Klasse in ihrer Gesamtheit . Versuchen wir es in Aktion?
Ich öffne einen bedingten Writer in einem bedingten XLSX.
$writer->openToFile($targetFile);
Hier lasse ich NativeQuery die Liste der Bestellungen aus der Datenbank abrufen.
$query = $this->getOrdersRepository($em) ->getOrdersFiltered($dateFrom, $dateTo, $filters);
Basierend auf dieser Abfrage deklariere ich einen Cursor.
$cursor = new PgSqlNativeQueryCursor($query);
Und für ihn bekomme ich eine Anfrage, Daten in Stapeln von 10.000 Zeilen zu erhalten.
$fetchQuery = $cursor->getFetchQuery(10000);
Ich iteriere, bis ich ein leeres Ergebnis erhalte. In jeder Iteration führe ich FETCH durch, verarbeite das Ergebnis und schreibe in eine Datei.
do { $result = $fetchQuery->getArrayResult(); foreach ($result as $row) { $writer->addRow($this->toXlsxRow($row)); } } while ($result);
Ich schließe den Cursor und den Writer.
$cursor->close(); $writer->close();
Ich schreibe die Datei in ein Verzeichnis für temporäre Dateien auf die Festplatte und sende sie nach Abschluss der Aufzeichnung an den Browser, um ein erneutes Puffern im Speicher zu vermeiden.
BERICHT BEREIT! Wir haben eine konstante Menge an PHP-Speicher verwendet, um alle Daten zu verarbeiten, und die Datenbank nicht mit einer Reihe schwerer Abfragen gefoltert. Und das Entladen selbst dauerte etwas länger als die Basis, die zur Erfüllung der Anforderung erforderlich war.
Sehen Sie, ob es in Ihren Projekten Stellen gibt, die den Speicher mit dem Cursor beschleunigen / speichern können?