Usando cursores, você pode receber em lote do banco de dados e processar uma grande quantidade de dados sem desperdiçar memória do aplicativo. Estou certo de que todo desenvolvedor da Web enfrentou uma tarefa semelhante pelo menos uma vez, e não apenas uma vez antes de mim. Neste artigo, mostrarei em que tarefas os cursores podem ser úteis e darei código pronto para trabalhar com eles no PHP + Doctrine usando o exemplo do PostrgeSQL.
O problema
Vamos imaginar que temos um projeto PHP, grande e pesado. Certamente, foi escrito com a ajuda de alguma estrutura. Por exemplo, Symfony. Ele também usa um banco de dados, por exemplo, PostgreSQL, e o banco de dados possui uma placa para 2.000.000 de registros com informações sobre pedidos. E o próprio projeto é uma interface para esses pedidos, que pode exibi-los e filtrá-los. E, deixe-me dizer, isso está indo muito bem.
Agora, fomos solicitados (você ainda não foi convidado? Eles definitivamente serão solicitados) a carregar o resultado da filtragem de pedidos em um arquivo do Excel. Vamos apertar um botão com um ícone de tabela, que cuspirá um arquivo com pedidos para o usuário.
Como é geralmente decidido e por que é ruim?
Como um programador que ainda não encontrou essa tarefa? Ele faz SELECT no banco de dados, lê os resultados da consulta, converte a resposta em um arquivo do Excel e a entrega ao navegador do usuário. A tarefa funciona, o teste está concluído, mas os problemas começam na produção.
Certamente, definimos um limite de memória para o PHP em cerca de 1 GB razoável (sem dúvida) por processo, e assim que esses 2 milhões de linhas não se ajustarem mais a esse gigabyte, tudo quebrará. O PHP trava com um erro de “falta de memória” e os usuários reclamam que o arquivo não foi carregado. Isso acontece porque escolhemos uma maneira bastante ingênua de descarregar dados do banco de dados - todos eles são transferidos primeiro da memória do banco de dados (e do disco sob ele) para a RAM do processo PHP, depois são processados e carregados no navegador.
Para que os dados sejam sempre colocados na memória, é necessário retirá-los do banco de dados em pedaços. Por exemplo, 10.000 registros foram lidos, processados, gravados em um arquivo e várias vezes.
Bem, pensa o programador que cumpriu nossa tarefa pela primeira vez. Então, farei um loop e desinflaremos os resultados da consulta em partes, indicando LIMIT e OFFSET. Funciona, mas é uma operação muito cara para o banco de dados e, portanto, o upload do relatório não começa a levar 30 segundos, mas 30 minutos (não é tão ruim!). A propósito, se, além do OFFSET, neste momento, nada mais vem à mente do programador, ainda existem muitas maneiras de conseguir o mesmo sem violar o banco de dados.
Ao mesmo tempo, o próprio banco de dados possui uma capacidade interna de encadear dados de leitura a partir dele - cursores.
Cursores
Um cursor é um ponteiro para uma linha nos resultados de uma consulta que reside no banco de dados. Ao usá-los, podemos executar SELECT não no modo de baixar dados imediatamente, mas abrir o cursor com essa seleção. Em seguida, começamos a receber o fluxo de dados do banco de dados à medida que o cursor avança. Isso nos dá o mesmo resultado: lemos os dados em partes, mas o banco de dados não faz o mesmo trabalho de encontrar a linha com a qual precisa iniciar, como é o caso de OFFSET.
O cursor é aberto apenas dentro da transação e permanece até que a transação esteja ativa (há uma exceção, consulte WITH HOLD ). Isso significa que, se lermos lentamente muitos dados do banco de dados, teremos uma transação longa. Às vezes isso é ruim , você precisa entender e aceitar esse risco.
Cursores em Doutrina
Vamos tentar implementar o trabalho com cursores no Doctrine. Primeiro, como é uma solicitação para abrir um cursor?
BEGIN; DECLARE mycursor1 CURSOR FOR ( SELECT * FROM huge_table );
DECLARE cria e abre o cursor para a consulta SELECT especificada. Após criar o cursor, você pode começar a ler dados dele:
FETCH FORWARD 10000 FROM mycursor1; < 10 000 > FETCH FORWARD 10000 FROM mycursor1; < 10 000 > ...
E assim por diante, até FETCH retornar uma lista vazia. Isso significa que eles rolaram até o fim.
COMMIT;
Delineamos uma classe compatível com o Doctrine que encapsulará o trabalho com o cursor. E para resolver 80% do problema em 20% do tempo , ele funcionará apenas com consultas nativas. Então, vamos chamá-lo de PgSqlNativeQueryCursor.
Construtor:
public function __construct(NativeQuery $query) { $this->query = $query; $this->connection = $query->getEntityManager()->getConnection(); $this->cursorName = uniqid('cursor_'); assert($this->connection->getDriver() instanceof PDOPgSqlDriver); }
Aqui eu gero um nome para o futuro cursor.
Como a classe possui código SQL específico do PostgreSQL, é melhor verificar se nosso driver é PG.
Da classe, precisamos de três coisas:
- Ser capaz de abrir o cursor.
- Ser capaz de retornar dados para nós.
- Consiga fechar o cursor.
Abra o 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 eu disse, os cursores são abertos em uma transação. Portanto, aqui verifico que não esquecemos de fazer uma chamada para esse método dentro de uma transação já aberta. (Graças a Deus, já passou o tempo em que eu seria atraído para abrir uma transação aqui!)
Para simplificar a tarefa de criar e inicializar um novo NativeQuery, clonei o que foi alimentado no construtor e envolva-o em DECLARE ... CURSOR FOR (here_original_query). Eu realizo isso.
Vamos criar o método getFetchQuery. Ele não retornará dados, mas outra solicitação que pode ser usada como você deseja obter os dados desejados em lotes específicos. Isso dá mais liberdade ao código de chamada.
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; }
O método possui um parâmetro - esse é o tamanho do pacote, que se tornará parte da solicitação retornada por esse método. Aplico o mesmo truque com a clonagem de consultas, sobrescrevi os parâmetros nele e substituí o SQL pela construção FETCH ... FROM ...;
Para não esquecer de abrir o cursor antes da primeira chamada para getFetchQuery () (de repente não vou dormir o suficiente), farei uma abertura implícita diretamente no método getFetchQuery ():
public function getFetchQuery(int $count = 1): NativeQuery { if (!$this->isOpen) { $this->openCursor(); } …
E o próprio método openCursor () será privado. Não vejo casos em que precise chamá-lo explicitamente.
Dentro de getFetchQuery (), eu codifiquei FORWARD para mover o cursor para frente um determinado número de linhas. Mas os modos de chamada FETCH são muito diferentes. Vamos adicioná-los também?
const DIRECTION_NEXT = 'NEXT'; const DIRECTION_PRIOR = 'PRIOR'; const DIRECTION_FIRST = 'FIRST'; const DIRECTION_LAST = 'LAST'; const DIRECTION_ABSOLUTE = 'ABSOLUTE';
Metade deles aceita o número de linhas no parâmetro e a outra metade não. Aqui está o que eu tenho:
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; }
Feche o cursor com CLOSE, não é necessário aguardar a conclusão da transação:
public function close() { if (!$this->isOpen) { return; } $this->connection->exec('CLOSE ' . $this->connection->quoteIdentifier($this->cursorName)); $this->isOpen = false; }
Destruidor:
public function __destruct() { if ($this->isOpen) { $this->close(); } }
Aqui está toda a classe na sua totalidade . Vamos tentar em ação?
Abro algum gravador condicional em algum XLSX condicional.
$writer->openToFile($targetFile);
Aqui, recebo o NativeQuery para puxar a lista de pedidos do banco de dados.
$query = $this->getOrdersRepository($em) ->getOrdersFiltered($dateFrom, $dateTo, $filters);
Com base nesta consulta, declaro um cursor.
$cursor = new PgSqlNativeQueryCursor($query);
E para ele, recebo um pedido para receber dados em lotes de 10.000 linhas.
$fetchQuery = $cursor->getFetchQuery(10000);
Eu itero até obter um resultado vazio. Em cada iteração, eu executo FETCH, processo o resultado e escrevo em um arquivo.
do { $result = $fetchQuery->getArrayResult(); foreach ($result as $row) { $writer->addRow($this->toXlsxRow($row)); } } while ($result);
Eu fecho o cursor e o Writer.
$cursor->close(); $writer->close();
Gravo o arquivo no disco em um diretório para arquivos temporários e, após a conclusão da gravação, envio-o ao navegador para evitar, novamente, armazenar em buffer a memória.
RELATÓRIO PRONTO! Usamos uma quantidade constante de memória do PHP para processar todos os dados e não torturamos o banco de dados com uma série de consultas pesadas. E a descarga em si levou um pouco mais de tempo do que a base necessária para atender à solicitação.
Veja se há lugares em seus projetos que podem acelerar / economizar memória com o cursor?