PHP, YII2 и формирование больших excel-файлов

Начало

Одна поддерживаемая нашей компанией учетно-отчетная система начала очень быстро разрастаться в количестве хранимых данных. Система написана на PHP с использованием фреймворка Yii2. Изначально отчеты строились через библиотеку PhpSpreadsheet, которая пришла на смену, уже давно ставшему deprecated, PhpExcel.

Среди разного вида отчетности был один очень крупный – фактически полный набор всех хранящихся в БД данных должен выгружаться в одну excel-таблицу. На начальном этапе проблем не возникало, но когда объем стал превышать многие сотни тысяч записей, то скрипт формирования выгрузки стал отваливаться в timeout limit. Для начала повысили этот самый лимит и начали искать пути решения проблемы. Но врЕменного решения хватило ненадолго – проблема с лимитом времени превратилась в проблему с лимитом памяти. Серверу накинули «оперативки» и вообще сняли memory_limit для данной отдельно взятой операции. Очень скоро пользователи снова начали жаловаться на ошибки по времени выполнения. Пришлось убрать и временной лимит для полного отчета. Но сидеть и смотреть десяток минут на экран с индикатором загрузки – мало удовольствия. К тому же иногда отчет нужен был «здесь и сейчас», и каждая потраченная минута на его формирование оказывалась критичной. Эксперименты с настройками окружения прекратили, почесали затылок и приступили к оптимизации кода.

Поиск решения

Первое, что было сделано – скрипт отчетности вынесен в фоновый процесс, а пользователь наблюдает за ходом через «прогрессбар». Фоновое выполнение заданий реализовали через механизм очередей с использованием Redis для хранения. Работа в системе не останавливается, можно заниматься другими задачами и периодически возвращаться на страницу с отчетом – посмотреть, а не готов ли файл. Как только файл формируется, пользователю предлагается ссылка на скачивание. Но, как уже упоминалось выше, иногда файл требовался «немедленно», а повышение юзабилити никак не решало эту проблему. Тем временем количество данных продолжало расти и время построения файла дошло до 79 минут! Это совершенно не приемлемо, особенно учитывая, что отчетность — одна из основ функционала данной системы. Нет, все остальные части работали как часы, но эта ложка дегтя портила общее впечатление.

Первые результаты

Снова сели за анализ кода. Первое, что было протестировано – процесс выбора данных из БД. Но запросы уже были оптимизированы максимально возможным способом. Хоть самый долгий запрос и представлял собой страшную выборку с пятью-шестью обращениями к монструозному ФИАСу, но отрабатывал за 2-5 секунд. Слабым местом был не он, а формирование файла-«эксельника». Начались попытки оптимизации этого процесса. Начиная от кеширования в redis, до извращений вроде формирования отдельных небольших «эксельников» в параллельных потоках с последующим склеиванием в один файл. Но результат был всегда один: проблема со временем превращалась в проблему с памятью и наоборот. Золотой середины не было, только перетекание из крайности в крайность. После определенного количества данных потребление ресурсов библиотекой начинало расти экспоненциально и победить это не представлялось возможным. PhpSpreadsheet – не подходит для больших файлов. В итоге было принято решение сменить библиотеку. Как вариант – написание своего аналога для формирования эксель-файлов.

Анализ и выбор инструмента

Спешить с написанием велосипедов не стали, а для начала провели аналитику существующих решений. Из возможных вариантов заинтересовал только box/spout. Быстро переписали модуль с использованием этой библиотеки. В итоге, полный отчет получился за 145 секунд. Напомню, что последние тесты с PhpSpreadsheet — 79 минут, а тут 2,5 минуты! Провели тестирование: увеличили объем данных в 2 раза. Отчет сформировался за 172 секунды. Разница потрясающая. Конечно, библиотека не обладает всеми теми же функциями, что и PhpSpreadsheet, но в данном случае хватает и минимального набора инструментов, так как критичным является скорость работы.

Расширение для Yii2

Итоговое решение оформили в виде расширения для Yii2. Может быть, кому-то пригодится. Расширение позволяет выгрузить любой набор данных из GridView в excel с сохранением фильтрации и сортировки. В качестве зависимостей использует yii/queue и box/spout. Применять расширение имеет смысл для формирования действительно больших файлов, ну, хотя бы 50 000 строк =) В данный момент модуль, ставший основой для расширения, лихо справляется с нагрузкой почти в 600 000 строк.

Ссылка на github: Yii2 ExcelReport Extension

Спасибо за внимание!