|
Professor Seleznov
|
Всем доброго времени суток!
Недавно пережитый опыт работы с отчетом хочу выставить на всеобщее обозрение,
вдруг кому-то будет полезен. Чтобы не томить и сохранить более технический подход, давайте сразу начнем "разбор полёта".
В какой-то момент у нас появился вот такой запрос:
- выгрузить Excel-отчёт
- около 150k строк данных
- Go + excelize
- обычный HTTP endpoint
SQL-запросы работали быстро. Индексы были в порядке. Памяти серверу хватало.
Но сам Excel-отчёт генерировался больше минуты.
На локальной машине выгрузка занимала около 16 секунд. На тестовом сервере — 35-40 секунд, после чего nginx начинал отдавать 502 Bad Gateway.
Проблема оказалась совсем не там, где ожидалось.
Bottleneck был:
- не в БД
- не в сети
- не в JSON
- а в генерации Excel.
Причём основное время уходило не на запись данных, а на:
- стили
- XML-сериализацию
- allocations
- постоянные вызовы SetCellStyle
- и создание тысяч объектов внутри циклов.
В статье покажу:
- почему наивная генерация Excel медленная
- как SetCellStyle убивает производительность
- зачем нужен StreamWriter
- почему StreamWriter ломает шаблоны
- как совместить потоковую генерацию и Excel template
- и почему большие Excel-экспорты лучше вообще выносить из HTTP lifecycle.
Наивная реализация
Первое, с чего начинается любая задача подобного рода — это стандартный подход через excelize.NewFile() и последовательное заполнение ячеек.
На этом этапе всё выглядит абсолютно нормально: есть файл, есть цикл по данным, есть запись значений в ячейки.
Пример упрощённой реализации выглядел примерно так:
xlsx := excelize.NewFile()
sheet := "Report"
index, _ := xlsx.NewSheet(sheet)
xlsx.DeleteSheet("Sheet1")
startRow := 4
for i, v := range payments {
row := startRow + i
xlsx.SetCellValue(sheet, fmt.Sprintf("A%d", row), i+1)
xlsx.SetCellValue(sheet, fmt.Sprintf("B%d", row), v.Account)
xlsx.SetCellValue(sheet, fmt.Sprintf("C%d", row), v.Amount)
xlsx.SetCellValue(sheet, fmt.Sprintf("D%d", row), v.Reward)
xlsx.SetCellValue(sheet, fmt.Sprintf("E%d", row), v.ServiceName)
}
На первый взгляд — ничего подозрительного. Обычный код, который делает ровно то, что ожидается: заполняет Excel-таблицу построчно.
Дальше добавляется оформление, потому что “без стилей Excel выглядит плохо”.
И именно здесь начинается деградация производительности:
xlsx.SetCellStyle(sheet, fmt.Sprintf("A%d", row), fmt.Sprintf("D%d", row), someStyle)
И, что важно — это делается внутри цикла на каждую строку.
В итоге одна строка превращается не просто в запись данных, а в:
- создание координат ячеек
- применение стилей
- генерацию XML-узлов внутри файла
- множество внутренних allocations в библиотеке
На объёмах в 100k+ строк это начинает играть критическую роль.
И если на локальной машине это ещё терпимо, то на сервере под нагрузкой это легко превращается в десятки секунд генерации и, как следствие, timeout на уровне nginx.
Где начинается проблема
Сначала подозрения стандартные — БД, сеть, сериализация. Запустили explain analyseи показатели были хорошими с учетом объема:
Результат анализа
Limit (cost=2.16..64407.86 rows=155169 width=461) (actual time=0.951..1432.129 rows=157770 loops=1)
-> Nested Loop (cost=2.16..64407.86 rows=155169 width=461) (actual time=0.949..1405.383 rows=157770 loops=1)
-> Nested Loop (cost=1.88..60261.77 rows=155169 width=436) (actual time=0.840..1245.680 rows=157770 loops=1)
-> Nested Loop (cost=1.73..56479.45 rows=155169 width=412) (actual time=0.786..1106.804 rows=157770 loops=1)
-> Nested Loop (cost=1.59..52987.72 rows=155169 width=388) (actual time=0.728..963.468 rows=157770 loops=1)
-> Nested Loop (cost=1.43..49112.43 rows=155169 width=354) (actual time=0.676..820.912 rows=157770 loops=1)
-> Nested Loop (cost=1.15..44973.22 rows=155169 width=314) (actual time=0.503..666.356 rows=157770 loops=1)
-> Nested Loop (cost=1.00..41403.40 rows=155169 width=290) (actual time=0.359..530.395 rows=157770 loops=1)
-> Nested Loop (cost=0.72..37512.22 rows=155169 width=271) (actual time=0.304..390.222 rows=157770 loops=1)
-> Index Scan using idx_payments_export on payments p (cost=0.43..33525.35 rows=155169 width=220) (actual time=0.199..192.239 rows=157770 loops=1)
Index Cond: ((created_at >= '2025-07-01 00:00:00+00'::timestamp with time zone) AND (created_at < '2026-04-30 23:59:00+00'::timestamp with time zone))
-> Memoize (cost=0.29..0.31 rows=1 width=59) (actual time=0.000..0.000 rows=1 loops=157770)
Cache Key: p.dealer_id
Cache Mode: logical
Hits: 157443 Misses: 327 Evictions: 0 Overflows: 0 Memory Usage: 54kB
-> Index Scan using dealers_pkey on dealers d (cost=0.28..0.30 rows=1 width=59) (actual time=0.012..0.012 rows=1 loops=327)
Index Cond: (id = p.dealer_id)
-> Memoize (cost=0.29..0.30 rows=1 width=35) (actual time=0.000..0.000 rows=1 loops=157770)
Cache Key: p.srv_id
Cache Mode: logical
Hits: 157718 Misses: 52 Evictions: 0 Overflows: 0 Memory Usage: 8kB
-> Index Scan using services_pkey on services s (cost=0.28..0.29 rows=1 width=35) (actual time=0.042..0.042 rows=1 loops=52)
Index Cond: (id = p.srv_id)
-> Memoize (cost=0.14..0.17 rows=1 width=40) (actual time=0.000..0.000 rows=1 loops=157770)
Cache Key: d.region_id
Cache Mode: logical
Hits: 157765 Misses: 5 Evictions: 0 Overflows: 0 Memory Usage: 1kB
-> Index Scan using regions_pkey on regions r (cost=0.13..0.16 rows=1 width=40) (actual time=0.030..0.030 rows=1 loops=5)
Index Cond: (id = d.region_id)
-> Memoize (cost=0.29..0.31 rows=1 width=56) (actual time=0.000..0.000 rows=1 loops=157770)
Cache Key: p.user_id
Cache Mode: logical
Hits: 157174 Misses: 596 Evictions: 0 Overflows: 0 Memory Usage: 96kB
-> Index Scan using tusers_pkey on tusers u (cost=0.28..0.30 rows=1 width=56) (actual time=0.013..0.013 rows=1 loops=596)
Index Cond: (id = p.user_id)
-> Memoize (cost=0.15..0.27 rows=1 width=50) (actual time=0.000..0.000 rows=1 loops=157770)
Cache Key: d.branch_id
Cache Mode: logical
Hits: 157745 Misses: 25 Evictions: 0 Overflows: 0 Memory Usage: 4kB
-> Index Scan using branches_pkey on branches b (cost=0.14..0.26 rows=1 width=50) (actual time=0.015..0.015 rows=1 loops=25)
Index Cond: (id = d.branch_id)
-> Memoize (cost=0.14..0.16 rows=1 width=40) (actual time=0.000..0.000 rows=1 loops=157770)
Cache Key: p.partner_id
Cache Mode: logical
Hits: 157768 Misses: 2 Evictions: 0 Overflows: 0 Memory Usage: 1kB
-> Index Scan using partners_pkey on partners pr (cost=0.13..0.15 rows=1 width=40) (actual time=0.028..0.028 rows=1 loops=2)
Index Cond: (id = p.partner_id)
-> Memoize (cost=0.15..0.19 rows=1 width=40) (actual time=0.000..0.000 rows=1 loops=157770)
Cache Key: s.category_id
Cache Mode: logical
Hits: 157760 Misses: 10 Evictions: 0 Overflows: 0 Memory Usage: 2kB
-> Index Scan using categories_pkey on categories c (cost=0.14..0.18 rows=1 width=40) (actual time=0.009..0.009 rows=1 loops=10)
Index Cond: (id = s.category_id)
-> Memoize (cost=0.29..0.32 rows=1 width=16) (actual time=0.000..0.000 rows=1 loops=157770)
Cache Key: p.term_id
Cache Mode: logical
Hits: 157180 Misses: 590 Evictions: 0 Overflows: 0 Memory Usage: 70kB
-> Index Scan using terminals_pkey on terminals t (cost=0.28..0.31 rows=1 width=16) (actual time=0.017..0.017 rows=1 loops=590)
Index Cond: (id = p.term_id)
Planning Time: 21.857 ms
Execution Time: 1445.455 ms
SQL отрабатывал быстро, данные приходили мгновенно, а общий ответ всё равно занимал 30–40 секунд.
При этом под нагрузкой nginx начинал отдавать 502.
После профилирования стало видно: время уходит не на получение данных, а на генерацию Excel через excelize.
Основной вклад давали операции внутри цикла — особенно SetCellStyle и работа с ячейками.
Стало очевидно: проблема не в данных, а в построении Excel-файла.
Почему Excel оказался медленным
Формат .xlsx — это не “таблица”, а ZIP-архив с набором XML-файлов внутри (недавно сам узнал).
Каждая запись в Excel через excelize фактически превращается в:
- генерацию XML-узлов
- упаковку структуры в архив
На небольших объёмах это незаметно.
Но на 100k+ строк начинают проявляться две ключевые проблемы:
1. Работа с ячейками
Когда мы используем методы вроде SetCellValue или SetCellStyle, они изменяют только одну конкретную ячейку. Каждое обращение — это отдельная операция: библиотека открывает внутренний XML‑файл Excel, находит нужную ячейку и вносит правку.
2. Стили внутри цикла
Применение стиля не “кэшируется по строке”, а повторно обрабатывается для каждого вызова.
В итоге генерация становится не просто записью данных, а большим количеством мелких операций над XML-структурой.
И чем больше данных — тем сильнее растёт стоимость не самих данных, а их “обёртки” в Excel.
Первая оптимизация: убираем очевидные потери
После профилирования стало понятно, что проблема не в одном месте, а в накопленных мелочах внутри цикла.
Первым делом убрали самое очевидное:
1. Форматирование строк
fmt.Sprintf в цикле для адресации ячеек оказалось лишним. Его заменили на более дешёвую сборку координат.
2. Повторяющиеся вычисления
Значения вроде статусов и типов терминалов вынесли в map и простые условия, чтобы не пересчитывать их на каждой итерации.
3. Лишние allocations
Слайсы и буферы начали переиспользовать, чтобы снизить нагрузку на GC.
После этого код стал легче, но ключевая проблема осталась — работа с excelize на уровне ячеек и стилей всё ещё доминировала по времени.
Шаг 2: попытка через шаблон
После ускорения генерации данных логичным шагом стало использование Excel-шаблона.
Идея была простая: взять готовый .xlsx файл с оформлением и заполнять его данными, не трогая стили в коде.
Пример инициализации выглядел так:
xlsx, err := excelize.OpenFile("templates/cash-in.xlsx")
if err != nil {
return nil, err
}
Далее мы заполняли служебные поля (например, период отчёта):
_ = xlsx.SetCellValue(templateSheet, "B2",
"C: "+dateFrom.Format("02-01-2006 15:04:05"))
_ = xlsx.SetCellValue(templateSheet, "C2",
"По: "+dateTo.Format("02-01-2006 15:04:05"))
И уже после этого пытались писать данные.
На этом этапе отчёт выглядел правильно — стили и оформление полностью сохранялись.
Но при объёме ~150k строк производительность оставалась проблемой:
генерация по-прежнему занимала десятки секунд.
Шаг 3: переход на StreamWriter
Следующим шагом мы попробовали StreamWriter, чтобы уйти от cell-level API и лишних операций над ячейками.
Базовая схема выглядела так:
sw, err := xlsx.NewStreamWriter("Data")
if err != nil {
return err
}
row := make([]interface{}, 0, 20)
for i, v := range payments {
terminalType := "Тип1"
if v.TerminalType == 2 {
terminalType = "Тип2"
}
row = row[:0] // очищаем
row = append(row,
i+1,
v.Account,
v.Amount,
v.Reward,
...
)
cell, _ := excelize.CoordinatesToCellName(1, startRow+i)
if err := sw.SetRow(cell, row); err != nil {
return err
}
}
После перехода на потоковую запись:
- снизилась нагрузка на память
- ускорилась генерация данных
- уменьшилось количество операций внутри цикла
Но появился новый эффект: StreamWriter не работает с шаблоном напрямую, и часть оформления терялась.
Это и стало причиной перехода к гибридной схеме.
Финальная схема: шаблон + потоковая генерация
В итоге мы пришли к гибридному решению, которое объединило сильные стороны обоих подходов:
- шаблон отвечает за внешний вид отчёта
- StreamWriter — за производительность
Идея заключалась в разделении ответственности:
1. Работа с шаблоном
Шаблон используется как источник оформления и служебных данных:
xlsx, err := excelize.OpenFile("templates/example.xlsx")
if err != nil {
return nil, err
}
_ = xlsx.SetCellValue("Основной лист", "B2",
"C: "+dateFrom.Format("02-01-2006 15:04:05"))
_ = xlsx.SetCellValue("Основной лист", "C2",
"По: "+dateTo.Format("02-01-2006 15:04:05"))
Шаблон сохраняет:
- заголовки
- стили
- ширины колонок
- оформление отчёта
2. Потоковая запись данных
Данные пишутся отдельно через StreamWriter, без работы со стилями:
sw, err := xlsx.NewStreamWriter("Data")
if err != nil {
return err
}
for i, v := range payments {
terminalType := "Тип1"
if v.TerminalType == 2 {
terminalType = "Тип2"
}
row := []interface{}{
i + 1,
v.Account,
v.Amount,
...
}
cell, _ := excelize.CoordinatesToCellName(1, startRow+i)
if err := sw.SetRow(cell, row); err != nil {
return err
}
}
3. Связывание шаблона и данных
После генерации данных выполняется финальная сборка:
- перенос заголовков из шаблона
- копирование ширин колонок
- применение стилей
- замена или переименование sheet
headers, _ := xlsx.GetRows("Основной лист")
if len(headers) >= 3 {
cell, _ := excelize.CoordinatesToCellName(1, 3)
_ = xlsx.SetSheetRow("Data", cell, &headers[2])
}
Итоговая архитектура
В результате получили:
- быстрый потоковый рендер данных
- сохранение шаблонного оформления
- стабильное потребление памяти даже на 150k+ строках
Итог
Главный вывод оказался неожиданно простым:
bottleneck в Excel-отчётах чаще всего не в данных, а в способе их записи.
После перехода на гибридную модель генерация стала стабильной и перестала упираться в таймауты nginx даже на больших объёмах.-Источник
|
