À l'aide de curseurs, vous pouvez recevoir par lots de la base de données et traiter une grande quantité de données sans gaspiller la mémoire de l'application. Je suis sûr que chaque développeur Web a fait face à une tâche similaire au moins une fois, et pas seulement une fois avant moi. Dans cet article, je vous dirai dans quelles tâches les curseurs peuvent être utiles, et je donnerai du code prêt à l'emploi pour travailler avec eux à partir de PHP + Doctrine en utilisant l'exemple de PostrgeSQL.
Le problème
Imaginons que nous ayons un projet PHP, gros et lourd. Certes, il a été écrit à l'aide d'un cadre. Par exemple, Symfony. Il utilise également une base de données, par exemple, PostgreSQL, et la base de données a une plaque pour 2 000 000 d'enregistrements avec des informations sur les commandes. Et le projet lui-même est une interface vers ces commandes, qui peut les afficher et les filtrer. Et, permettez-moi de dire, cela se passe plutôt bien.
Maintenant, on nous a demandé (ne vous a-t-on pas encore demandé? Ils le seront certainement) de télécharger le résultat des commandes de filtrage dans un fichier Excel. Préparons un bouton avec une icône de tableau, qui crachera un fichier avec des commandes à l'utilisateur.
Comment est-il généralement décidé et pourquoi est-il mauvais?
Comment un programmeur qui n'a pas encore rencontré une telle tâche? Il crée SELECT dans la base de données, lit les résultats de la requête, convertit la réponse en fichier Excel et la transmet au navigateur de l'utilisateur. La tâche fonctionne, les tests sont terminés, mais des problèmes commencent dans la production.
Certes, nous avons fixé une limite de mémoire pour PHP à environ 1 Go raisonnable (sans doute) par processus, et dès que ces 2 millions de lignes ne rentrent plus dans ce gigaoctet, tout casse. PHP se bloque avec une erreur de «mémoire insuffisante» et les utilisateurs se plaignent que le fichier n'est pas téléchargé. Cela se produit parce que nous avons choisi une façon assez naïve de décharger les données de la base de données - toutes sont d'abord transférées de la mémoire de la base de données (et du disque en dessous) vers la RAM du processus PHP, puis elles sont traitées et téléchargées vers le navigateur.
Pour que les données soient toujours placées en mémoire, vous devez les extraire de la base de données en morceaux. Par exemple, 10 000 enregistrements ont été lus, traités, écrits dans un fichier, et tant de fois.
Eh bien, pense le programmeur qui a rempli notre tâche pour la première fois. Ensuite, je vais faire une boucle et dégonfler les résultats de la requête en morceaux, en indiquant LIMIT et OFFSET. Cela fonctionne, mais c'est une opération très coûteuse pour la base de données, et donc le téléchargement du rapport ne commence pas à prendre 30 secondes, mais 30 minutes (ce n'est pas si mal!). Soit dit en passant, si à part OFFSET en ce moment, rien ne vient plus à l'esprit du programmeur, il existe encore de nombreuses façons de réaliser la même chose sans violer la base de données.
Dans le même temps, la base de données elle-même a une capacité intégrée d'enfiler des données lues - des curseurs.
Curseurs
Un curseur est un pointeur sur une ligne dans les résultats d'une requête qui réside dans la base de données. Lorsque vous les utilisez, nous pouvons effectuer SELECT non pas dans le mode de téléchargement immédiat des données, mais ouvrir le curseur avec cette sélection. Ensuite, nous commençons à recevoir le flux de données de la base de données à mesure que le curseur avance. Cela nous donne le même résultat: nous lisons les données par portions, mais la base de données ne fait pas le même travail de recherche de la ligne avec laquelle elle doit commencer, comme c'est le cas avec OFFSET.
Le curseur est ouvert uniquement à l'intérieur de la transaction et reste en vie jusqu'à ce que la transaction soit active (il existe une exception, voir WITH HOLD ). Cela signifie que si nous lisons lentement beaucoup de données de la base de données, nous aurons une longue transaction. C'est parfois mauvais , vous devez comprendre et accepter ce risque.
Curseurs dans la doctrine
Essayons d'implémenter le travail avec les curseurs dans Doctrine. Tout d'abord, à quoi ressemble une demande d'ouverture d'un curseur?
BEGIN; DECLARE mycursor1 CURSOR FOR ( SELECT * FROM huge_table );
DECLARE crée et ouvre le curseur pour la requête SELECT spécifiée. Après avoir créé le curseur, vous pouvez commencer à en lire les données:
FETCH FORWARD 10000 FROM mycursor1; < 10 000 > FETCH FORWARD 10000 FROM mycursor1; < 10 000 > ...
Et ainsi de suite, jusqu'à ce que FETCH renvoie une liste vide. Cela signifie qu'ils ont défilé jusqu'à la fin.
COMMIT;
Nous décrivons une classe compatible avec Doctrine qui encapsulera le travail avec le curseur. Et pour résoudre 80% du problème en 20% du temps , cela ne fonctionnera qu'avec les requêtes natives. Appelons-le donc PgSqlNativeQueryCursor.
Constructeur:
public function __construct(NativeQuery $query) { $this->query = $query; $this->connection = $query->getEntityManager()->getConnection(); $this->cursorName = uniqid('cursor_'); assert($this->connection->getDriver() instanceof PDOPgSqlDriver); }
Ici, je génère un nom pour le futur curseur.
Étant donné que la classe contient du code SQL spécifique à PostgreSQL, il est préférable de vérifier que notre pilote est PG.
De la classe, nous avons besoin de trois choses:
- Pouvoir ouvrir le curseur.
- Être en mesure de nous renvoyer des données.
- Pouvoir fermer le curseur.
Ouvrez le curseur:
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; }
Comme je l'ai dit, les curseurs s'ouvrent dans une transaction. Par conséquent, je vérifie ici que nous n'avons pas oublié d'appeler cette méthode dans une transaction déjà ouverte. (Dieu merci, le temps est passé où je serais amené à ouvrir une transaction ici!)
Pour simplifier la tâche de création et d'initialisation d'une nouvelle NativeQuery, je viens de cloner celle qui a été introduite dans le constructeur et de l'envelopper dans DECLARE ... CURSOR FOR (here_original_query). Je le réalise.
Faisons la méthode getFetchQuery. Il ne renverra pas de données, mais une autre demande qui peut être utilisée à votre guise pour obtenir les données souhaitées dans des lots donnés. Cela donne au code appelant plus de liberté.
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; }
La méthode a un paramètre - c'est la taille du bundle, qui fera partie de la demande retournée par cette méthode. J'applique la même astuce avec le clonage de requête, j'écrase les paramètres qu'il contient et je remplace SQL par la construction FETCH ... FROM ... ;.
Afin de ne pas oublier d'ouvrir le curseur avant le premier appel à getFetchQuery () (du coup je ne dormirai pas assez), j'en ferai une ouverture implicite directement dans la méthode getFetchQuery ():
public function getFetchQuery(int $count = 1): NativeQuery { if (!$this->isOpen) { $this->openCursor(); } …
Et la méthode openCursor () elle-même sera rendue privée. Je ne vois aucun cas lorsque je dois l'appeler explicitement.
Dans getFetchQuery (), j'ai codé en dur FORWARD pour déplacer le curseur vers l'avant un nombre donné de lignes. Mais les modes d'appel FETCH sont très différents. Ajoutons-les aussi?
const DIRECTION_NEXT = 'NEXT'; const DIRECTION_PRIOR = 'PRIOR'; const DIRECTION_FIRST = 'FIRST'; const DIRECTION_LAST = 'LAST'; const DIRECTION_ABSOLUTE = 'ABSOLUTE';
La moitié d'entre eux accepte le nombre de lignes dans le paramètre, et l'autre moitié ne l'accepte pas. Voici ce que j'ai obtenu:
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; }
Fermez le curseur avec CLOSE, il n'est pas nécessaire d'attendre la fin de la transaction:
public function close() { if (!$this->isOpen) { return; } $this->connection->exec('CLOSE ' . $this->connection->quoteIdentifier($this->cursorName)); $this->isOpen = false; }
Destructeur:
public function __destruct() { if ($this->isOpen) { $this->close(); } }
Voici la classe entière dans son intégralité . Essayons en action?
J'ouvre un Writer conditionnel dans un XLSX conditionnel.
$writer->openToFile($targetFile);
Ici, je reçois NativeQuery pour extraire la liste des commandes de la base de données.
$query = $this->getOrdersRepository($em) ->getOrdersFiltered($dateFrom, $dateTo, $filters);
Sur la base de cette requête, je déclare un curseur.
$cursor = new PgSqlNativeQueryCursor($query);
Et pour lui, je reçois une demande pour recevoir des données par lots de 10 000 lignes.
$fetchQuery = $cursor->getFetchQuery(10000);
J'itère jusqu'à ce que j'obtienne un résultat vide. À chaque itération, j'exécute FETCH, je traite le résultat et j'écris dans un fichier.
do { $result = $fetchQuery->getArrayResult(); foreach ($result as $row) { $writer->addRow($this->toXlsxRow($row)); } } while ($result);
Je ferme le curseur et Writer.
$cursor->close(); $writer->close();
Le fichier lui-même que j'écris sur le disque dans un répertoire pour les fichiers temporaires, et une fois l'enregistrement terminé, je le donne au navigateur pour éviter, encore une fois, la mise en mémoire tampon.
RAPPORT PRET! Nous avons utilisé une quantité constante de mémoire de PHP pour traiter toutes les données et n'avons pas torturé la base de données avec une série de requêtes lourdes. Et le déchargement lui-même a pris un peu plus de temps que la base requise pour répondre à la demande.
Voir s'il y a des endroits dans vos projets qui peuvent accélérer / économiser de la mémoire avec le curseur?