Массовая загрузка данных в БД: как ускорить bulk insert без просадки сервера

Массовую загрузку данных — импорт прайсов, миграцию из старой системы, синхронизацию с 1С или маркетплейсом — ускоряют не «более мощным сервером», а правильной техникой вставки: пакетами (батчами), с временно отключёнными лишними индексами, в одной транзакции и, где возможно, специальными командами вроде COPY. Это может сократить время загрузки в десятки раз без единого рубля дополнительных вложений в железо.

Почему вставка данных «по одной строке» такая медленная?

Когда приложение вставляет строки одну за другой — обычным INSERT в цикле — каждая операция обходится базе данных недёшево. На каждую строку СУБД тратит время на разбор запроса, проверку ограничений, обновление индексов, запись в журнал транзакций и, если автокоммит включён, на фиксацию транзакции. Если журнал транзакций синхронно сбрасывается на диск при каждом коммите, а таких коммитов миллион, то именно диск, а не процессор, становится узким местом.

Дополнительно каждая отдельная вставка — это отдельный сетевой запрос от приложения к серверу базы данных. Если приложение и БД разделены сетью (а в облачной инфраструктуре это почти всегда так), то время на сетевую задержку («круговой путь» запроса) умножается на количество строк. Даже небольшая задержка в доли миллисекунды при миллионе строк превращается в ощутимые минуты простоя.

В чём разница между обычным INSERT и bulk insert?

Bulk insert («массовая вставка») — это не отдельная команда, а подход: данные передаются и записываются пакетами, а не строка за строкой. В зависимости от СУБД это может быть:

• один INSERT с сотнями строк значений через запятую вместо сотен отдельных INSERT;
• специализированные команды для потоковой загрузки (например, команда COPY в PostgreSQL или LOAD DATA в MySQL);
• пакетная вставка через драйвер приложения (batch insert), когда несколько операций отправляются на сервер одним сетевым пакетом.

Интересный и широко подтверждённый факт: команда COPY в PostgreSQL для загрузки данных из файла может быть на порядок быстрее обычных построчных INSERT именно потому, что она минует часть накладных расходов на разбор SQL и обмен по сети для каждой строки — данные передаются потоком.

Стоит ли отключать индексы перед загрузкой?

Да, если индексов на таблице несколько и объём загружаемых данных большой. Каждый индекс при вставке строки нужно обновить: добавить запись, при необходимости перестроить структуру дерева. Чем больше индексов — тем дороже обходится каждая вставка. При загрузке миллионов строк часто быстрее временно удалить индексы (кроме первичного ключа), загрузить данные, а затем создать индексы заново одной командой — построение индекса «пакетом» по готовым данным обычно эффективнее, чем постепенное обновление на лету.

Важно: это оправдано именно для больших разовых загрузок. Если вставки идут постоянно, небольшими порциями и параллельно с чтением — отключать индексы нельзя, иначе запросы на чтение начнут работать медленно или вообще упадут с ошибками.

Как транзакции влияют на скорость загрузки?

Если каждая строка вставляется в своей собственной транзакции с автокоммитом, база данных вынуждена фиксировать изменения на диске после каждой строки — это дорогая операция, связанная с гарантией сохранности данных. Объединение множества вставок в одну транзакцию (или в транзакции по несколько тысяч строк) резко сокращает число таких фиксаций.

Здесь есть баланс. Слишком большая транзакция — это риск: если что-то пойдёт не так на строке номер 900 000 из миллиона, откатится вся работа, а сама транзакция будет долго удерживать ресурсы и мешать другим процессам. Слишком мелкие транзакции — медленно из-за накладных расходов на коммит. На практике для больших импортов данные разбивают на пакеты по несколько тысяч–десятков тысяч строк и коммитят пакетами, логируя прогресс, чтобы при сбое можно было продолжить с места остановки, а не начинать заново.

Как правильно подобрать размер батча?

Единого числа, подходящего всем, не существует — размер зависит от ширины строки, количества индексов, объёма оперативной памяти и настроек конкретной СУБД. Практический подход простой: начать с умеренного размера пакета, замерить время загрузки и потребление ресурсов, затем постепенно увеличивать размер пакета и следить, когда прирост скорости перестаёт окупать рост потребления памяти или риск долгих транзакций.

Общий принцип: слишком мелкие пакеты (по одной-две строки) почти не дают выигрыша по сравнению с обычной построчной вставкой, а слишком крупные (сотни тысяч строк в одном запросе) могут упереться в лимиты памяти или максимальный размер одного запроса, установленный сервером или сетевым оборудованием.

Помогает ли параллельная загрузка данных?

Параллельная загрузка — когда несколько потоков одновременно пишут в базу — может ускорить процесс, если у сервера есть запас по процессору, дисковым операциям и свободным соединениям. Но у параллелизма есть обратная сторона: несколько потоков, пишущих в одну и ту же таблицу, могут конкурировать за блокировки строк или страниц, что не ускоряет, а замедляет общий процесс, особенно если данные попадают в одни и те же диапазоны индекса.

Разумный компромисс — параллельная загрузка в разные таблицы или в разные партиции одной таблицы (партиционирование как раз снимает часть конкуренции за блокировки), а также ограничение числа параллельных потоков разумным пределом, который подбирается тестированием под конкретное железо и конкретную СУБД.

Какие ошибки чаще всего допускают при массовой загрузке?

Список типичных проблем, с которыми сталкиваются при импорте больших объёмов данных:

• вставка строка за строкой с автокоммитом вместо пакетной загрузки;
• загрузка данных в таблицу с активными триггерами и внешними ключами, которые проверяются на каждой строке;
• отсутствие логирования прогресса — при сбое приходится начинать импорт с нуля;
• загрузка «в лоб» на продакшн-сервер в рабочие часы, когда с той же базой одновременно работают обычные пользователи;
• забытая пересборка статистики планировщика запросов после загрузки — без свежей статистики запросы к новым данным могут строить неоптимальные планы выполнения.

Последний пункт особенно важен: после крупной загрузки полезно явно обновить статистику таблицы, чтобы оптимизатор запросов СУБД знал реальное распределение данных и строил быстрые планы выполнения для последующих выборок.

Как эту задачу закрывают готовые облачные решения?

Настраивать всё это вручную — от подбора размера батча до мониторинга блокировок во время импорта — требует времени и опыта администрирования баз данных. Сервис «Клауд АйСи» предлагает готовую облачную инфраструктуру для баз данных и серверов, где ресурсы под пиковую нагрузку во время массовых загрузок можно временно увеличить, не переустанавливая систему и не мигрируя данные на новое железо, а также инструменты резервного копирования, которые снимают часть риска при экспериментах с крупными импортами и перестройкой индексов.