Con los cursores, puede recibir por lotes desde la base de datos y procesar una gran cantidad de datos sin desperdiciar la memoria de la aplicación. Estoy seguro de que cada desarrollador web se ha enfrentado a una tarea similar al menos una vez, y no solo una vez antes que yo. En este artículo, le diré en qué tareas los cursores pueden ser útiles, y le daré un código listo para trabajar con ellos desde PHP + Doctrine usando el ejemplo de PostrgeSQL.
El problema
Imaginemos que tenemos un proyecto PHP, grande y pesado. Seguramente, fue escrito con la ayuda de algún marco. Por ejemplo, Symfony. También utiliza una base de datos, por ejemplo, PostgreSQL, y la base de datos tiene una placa para 2,000,000 de registros con información sobre pedidos. Y el proyecto en sí es una interfaz para estos pedidos, que puede mostrarlos y filtrarlos. Y, déjenme decir, esto está bastante bien.
Ahora se nos pidió (¿todavía no se le ha preguntado? Definitivamente se les pedirá) que carguemos el resultado de filtrar pedidos en un archivo Excel. Agitemos un botón con un icono de tabla, que escupirá un archivo con órdenes para el usuario.
¿Cómo se suele decidir y por qué es malo?
¿Cómo funciona un programador que aún no se ha encontrado con esa tarea? Hace SELECCIONAR en la base de datos, lee los resultados de la consulta, convierte la respuesta en un archivo de Excel y la entrega al navegador del usuario. La tarea funciona, la prueba se completa, pero los problemas comienzan en la producción.
Seguramente, hemos establecido un límite de memoria para PHP en un razonable (posiblemente) 1 GB por proceso, y tan pronto como estos 2 millones de líneas ya no caben en este gigabyte, todo se rompe. PHP se bloquea con un error de "falta de memoria", y los usuarios se quejan de que el archivo no está cargado. Esto sucede porque elegimos una forma bastante ingenua de descargar datos de la base de datos: todos ellos primero se transfieren desde la memoria de la base de datos (y el disco debajo de ella) a la RAM del proceso PHP, luego se procesan y se cargan en el navegador.
Para que los datos siempre se coloquen en la memoria, debe tomarlos de la base de datos por partes. Por ejemplo, 10,000 registros fueron leídos, procesados, escritos en un archivo, y tantas veces.
Bueno, piensa el programador que cumplió nuestra tarea por primera vez. Luego haré un ciclo y desinflaré los resultados de la consulta en partes, indicando LIMIT y OFFSET. Funciona, pero es una operación muy costosa para la base de datos y, por lo tanto, cargar el informe no comienza a tomar 30 segundos, sino 30 minutos (¡no es tan malo!). Por cierto, si aparte de OFFSET en este momento, nada más viene a la mente del programador, entonces todavía hay muchas maneras de lograr lo mismo sin violar la base de datos.
Al mismo tiempo, la base de datos en sí misma tiene una capacidad incorporada para enhebrar datos de lectura de ella: cursores.
Cursores
Un cursor es un puntero a una línea en los resultados de una consulta que vive en la base de datos. Al usarlos, podemos realizar SELECCIONAR no en el modo de descarga inmediata de datos, sino abrir el cursor con esta selección. A continuación, comenzamos a recibir el flujo de datos de la base de datos a medida que el cursor avanza. Esto nos da el mismo resultado: leemos los datos en porciones, pero la base de datos no hace el mismo trabajo de encontrar la línea con la que necesita comenzar, como es el caso de OFFSET.
El cursor se abre solo dentro de la transacción y vive hasta que la transacción esté viva (hay una excepción, vea WITH HOLD ). Esto significa que si leemos lentamente muchos datos de la base de datos, tendremos una transacción larga. Esto a veces es malo , debe comprender y aceptar este riesgo.
Cursores en Doctrina
Intentemos implementar el trabajo con cursores en Doctrine. Primero, ¿cómo se ve una solicitud para abrir un cursor?
BEGIN; DECLARE mycursor1 CURSOR FOR ( SELECT * FROM huge_table );
DECLARE crea y abre el cursor para la consulta SELECT especificada. Después de crear el cursor, puede comenzar a leer datos de él:
FETCH FORWARD 10000 FROM mycursor1; < 10 000 > FETCH FORWARD 10000 FROM mycursor1; < 10 000 > ...
Y así sucesivamente, hasta que FETCH devuelva una lista vacía. Esto significará que se desplazaron hasta el final.
COMMIT;
Esbozamos una clase compatible con Doctrine que encapsulará el trabajo con el cursor. Y para resolver el 80% del problema en el 20% del tiempo , solo funcionará con consultas nativas. Entonces llamémoslo, PgSqlNativeQueryCursor.
Constructor:
public function __construct(NativeQuery $query) { $this->query = $query; $this->connection = $query->getEntityManager()->getConnection(); $this->cursorName = uniqid('cursor_'); assert($this->connection->getDriver() instanceof PDOPgSqlDriver); }
Aquí genero un nombre para el futuro cursor.
Dado que la clase tiene un código SQL específico de PostgreSQL en la clase, es mejor verificar que nuestro controlador sea PG.
De la clase necesitamos tres cosas:
- Poder abrir el cursor.
- Poder devolvernos datos.
- Poder cerrar el cursor.
Abre el 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; }
Como dije, los cursores se abren en una transacción. Por lo tanto, aquí verifico que no nos hemos olvidado de hacer una llamada a este método dentro de una transacción ya abierta. (¡Gracias a Dios, ha pasado el tiempo en que me atraería abrir una transacción aquí mismo!)
Para simplificar la tarea de crear e inicializar una nueva NativeQuery, simplemente clono la que se introdujo en el constructor y la envuelvo en DECLARE ... CURSOR FOR (here_original_query). Lo llevo a cabo.
Hagamos el método getFetchQuery. No devolverá datos, sino otra solicitud que puede usarse como desee para obtener los datos deseados en lotes dados. Esto le da al código de llamada más libertad.
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; }
El método tiene un parámetro: este es el tamaño del paquete, que formará parte de la solicitud devuelta por este método. Aplico el mismo truco con la clonación de consultas, sobrescribo los parámetros en él y reemplazo SQL con FETCH ... FROM ...; construcción.
Para no olvidar abrir el cursor antes de la primera llamada a getFetchQuery () (de repente no dormiré lo suficiente), haré una apertura implícita directamente en el método getFetchQuery ():
public function getFetchQuery(int $count = 1): NativeQuery { if (!$this->isOpen) { $this->openCursor(); } …
Y el método openCursor () se convertirá en privado. No veo casos en absoluto cuando necesito llamarlo explícitamente.
Dentro de getFetchQuery (), codifiqué FORWARD para mover el cursor hacia adelante un número determinado de líneas. Pero los modos de llamada FETCH son muy diferentes. ¿Vamos a agregarlos también?
const DIRECTION_NEXT = 'NEXT'; const DIRECTION_PRIOR = 'PRIOR'; const DIRECTION_FIRST = 'FIRST'; const DIRECTION_LAST = 'LAST'; const DIRECTION_ABSOLUTE = 'ABSOLUTE';
La mitad de ellos acepta el número de líneas en el parámetro, y la otra mitad no. Esto es lo que obtuve:
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; }
Cierre el cursor con CLOSE, no es necesario esperar a que se complete la transacción:
public function close() { if (!$this->isOpen) { return; } $this->connection->exec('CLOSE ' . $this->connection->quoteIdentifier($this->cursorName)); $this->isOpen = false; }
Destructor
public function __destruct() { if ($this->isOpen) { $this->close(); } }
Aquí está toda la clase en su totalidad . ¿Intentamos en acción?
Abro un escritor condicional en algún XLSX condicional.
$writer->openToFile($targetFile);
Aquí obtengo NativeQuery para extraer la lista de pedidos de la base de datos.
$query = $this->getOrdersRepository($em) ->getOrdersFiltered($dateFrom, $dateTo, $filters);
Basado en esta consulta, declaro un cursor.
$cursor = new PgSqlNativeQueryCursor($query);
Y para él recibo una solicitud para recibir datos en lotes de 10,000 líneas.
$fetchQuery = $cursor->getFetchQuery(10000);
Repito hasta que obtengo un resultado vacío. En cada iteración, realizo FETCH, proceso el resultado y escribo en un archivo.
do { $result = $fetchQuery->getArrayResult(); foreach ($result as $row) { $writer->addRow($this->toXlsxRow($row)); } } while ($result);
Cierro el cursor y el escritor.
$cursor->close(); $writer->close();
El archivo en sí lo escribo en el disco en un directorio para archivos temporales, y después de que se completa la grabación, se lo entrego al navegador para evitar, nuevamente, el almacenamiento en búfer en la memoria.
INFORME LISTO! Utilizamos una cantidad constante de memoria de PHP para procesar todos los datos y no torturamos la base de datos con una serie de consultas pesadas. Y la descarga en sí misma tomó un poco más de tiempo que la base requerida para cumplir con la solicitud.
¿Ves si hay lugares en tus proyectos que pueden acelerar / guardar memoria con el cursor?