PHP, YII2 e a formação de grandes arquivos Excel

Iniciar

Um sistema de contabilidade e relatórios suportado por nossa empresa começou a crescer muito rapidamente na quantidade de dados armazenados. O sistema é escrito em PHP usando a estrutura Yii2. Inicialmente, os relatórios foram criados por meio da biblioteca PhpSpreadsheet, que substituiu o antigo e obsoleto PhpExcel.

Entre os diferentes tipos de relatório, havia um muito grande - na verdade, o conjunto completo de todos os dados armazenados no banco de dados deve ser carregado em uma tabela do Excel. No estágio inicial, não havia problemas, mas quando o volume começou a exceder muitas centenas de milhares de registros, o script de formação de descarregamento começou a cair no limite de tempo limite. Para começar, aumentamos esse limite e começamos a procurar maneiras de resolver o problema. Mas uma solução temporária não durou muito - o problema com o limite de tempo se transformou em um problema com o limite de memória. Eles lançaram a “RAM” para o servidor e removeram o memory_limit para esta operação específica. Muito em breve, os usuários começaram a reclamar sobre erros de tempo de execução novamente. Eu tive que remover o prazo para o relatório completo. Mas sentar e assistir uma dúzia de minutos na tela com um indicador de carregamento não é muito divertido. Além disso, às vezes era necessário um relatório "aqui e agora", e cada minuto gasto em sua formação era crítico. Os experimentos com as configurações do ambiente foram interrompidos, arranharam a parte de trás da cabeça e começaram a otimizar o código.

Procure uma solução

A primeira coisa que foi feita foi que o script de relatório foi colocado no processo em segundo plano e o usuário monitora o progresso através da "barra de progresso". A execução da tarefa em segundo plano foi implementada por meio do mecanismo de fila usando o Redis para armazenamento. O trabalho no sistema não para, você pode executar outras tarefas e retornar periodicamente à página do relatório para verificar se o arquivo está pronto. Assim que o arquivo é formado, o usuário recebe um link para download. Mas, como mencionado acima, às vezes o arquivo era necessário "imediatamente" e o aumento da usabilidade não solucionava esse problema. Enquanto isso, a quantidade de dados continuou a crescer e o tempo necessário para criar o arquivo chegou a 79 minutos! Isso é completamente inaceitável, especialmente considerando que os relatórios são um dos fundamentos da funcionalidade deste sistema. Não, todas as outras partes funcionavam como um relógio, mas essa mosca na pomada estragou a impressão geral.

Primeiros resultados

Sentamos novamente para análise de código. A primeira coisa que foi testada foi o processo de seleção de dados do banco de dados. Mas as consultas já foram otimizadas da maneira máxima possível. Embora a solicitação mais longa tenha sido uma amostra terrível, com cinco ou seis ligações para o monstruoso FIAS, funcionou em 2 a 5 segundos. O ponto fraco não era ele, mas a formação do arquivo "exelnik". Tentativas começaram a otimizar esse processo. Começando do cache em redis, para perversões, como a formação de pequenos "excels" separados em fluxos paralelos, seguidos pela colagem em um arquivo. Mas o resultado sempre foi o mesmo: o problema ao longo do tempo se transformou em um problema de memória e vice-versa. Não havia meio termo, apenas fluindo de um extremo ao outro. Após uma certa quantidade de dados, o consumo de recursos da biblioteca começou a crescer exponencialmente e não foi possível derrotá-lo. PhpSpreadsheet - não é adequado para arquivos grandes. Como resultado, foi decidido alterar a biblioteca. Como opção - escreva seu próprio análogo para a formação de ex-arquivos.

Análise e seleção de ferramentas

Eles não se apressaram em escrever bicicletas, mas, para começar, analisaram as soluções existentes. Das opções possíveis, apenas a caixa / bico era de interesse. Reescreva rapidamente o módulo usando esta biblioteca. Como resultado, um relatório completo foi obtido em 145 segundos. Deixe-me lembrá-lo de que os testes mais recentes com o PhpSpreadsheet são de 79 minutos e aqui de 2,5 minutos! Teste realizado: aumentou a quantidade de dados em 2 vezes. O relatório foi gerado em 172 segundos. A diferença é incrível. Obviamente, a biblioteca não possui todas as mesmas funções que o PhpSpreadsheet, mas nesse caso o conjunto mínimo de ferramentas é suficiente, pois a velocidade é crítica.

Extensão para Yii2

A decisão final foi estruturada como uma extensão para o Yii2. Talvez alguém venha a calhar. A extensão permite fazer upload de qualquer conjunto de dados do GridView para o Excel, mantendo a filtragem e a classificação. Ele usa yii / fila e box / spout como dependências. Faz sentido usar a extensão para formar arquivos realmente grandes, bem, pelo menos 50.000 linhas =) No momento, o módulo, que se tornou a base da extensão, lida com a carga de quase 600.000 linhas.

Link para o github: Extensão Yii2 ExcelReport

Obrigado pela atenção!