Как оптимально импортировать часто изменяемые записи из файла в бд?

Question

[Snewer]

Здравствуйте!

Есть база данных с каталогом продукции (mysql) в которую нужно часто импортировать большой объем записей из CSV файлов, из различных API. У каждого источника есть свой идентификатор (shop_id).

Как организовать импорт данных? Рассматриваю такой вариант:
- Открыть транзакцию
- Удалить все записи для импортируемого shop_id
- Добавить все записи из источника
- Закоммитить транзакцию

На каждый источник порядка 50к записей, импортировать каждый источник нужно каждый час.
Что можете посоветовать? Спасибо.

Answer 1

[Иван Шумов]

А вам обязательно гарантировать целостность данных? Понятное дело что это хорошо, держать все под контролем, но занимает определенное время и блокирует операции. Плюс, что с целостностью идентификаторов?

Comments

[Snewer]

Не обязательно. Каждую запись можно идентифицировать по shop_id + артикул товара

[Иван Шумов]

Snewer, если вам охота поставить это на поток и не беспокоиться о нагрузке, то можете создавать "задания". То есть создаёте базу с импорта и (файл и метаинформация). После этого отправляете его на валидацию (в message broker). Если он валиден то создаёте набор заданий на импорт и спокойно из другой очереди из обрабатываете. Не забудьте про исключения и тому подобные вещи. Так же стоит добавить параллельный обработчик, который удаляет(прячет) товары, которые не находятся в выгрузке

[Snewer]

Иван Шумов, предлагаете брать все строки из файла / API и на каждую создавать задание в очередь? то есть 50к заданий на один источник

[Иван Шумов]

Snewer, да. И обрабатываться они будут независимом можете повесить с другой стороны хоть один воркер, хоть тысячу. Получите масштабируемость и в случае увеличения нагрузки максимум - задержки, но не результат. Кончено стоит добавить каунтер к импорту чтобы знать что он завершился

[Snewer]

Иван Шумов, спасибо, подумаю над таким решением.

Answer 2

[Rsa97]

UPDATE `table` SET `to_delete` = 1 WHERE `shop_id` = :shopId;

LOAD DATA 
  INFILE 'shop.txt' REPLACE
  INTO TABLE `table`
  (`article`, `name`, ...)
  SET `shop_id` = :shopId, `to_delete` = 0;

DELETE FROM `table` WHERE `shop_id` = :shopId AND `to_delete` = 1;

Answer 3

[Vitsliputsli]

А в чем проблема? Заливайте во временную таблицу и из нее обновляйте, 50к записей это мелочи.

Comments

[Snewer]

50к это примерно на один источник (csv файл или API). в сумме нужно обновлять около 500к записей каждый час. такие дела :)

[Vitsliputsli]

Все данные взаимозависимы? Т.е. все должно обновляться единовременно?

[Snewer]

Vitsliputsli, нет

[Vitsliputsli]

Тогда обновляйте последовательно. Транзакция на truncate и добавление 50к не должна создать проблем для хорошего сервера СУБД. Таблица не перегружена индексами?

[Snewer]

Vitsliputsli, нет, один индекс

[Vitsliputsli]

Тогда вряд ли будут проблемы, если будете обновлять последовательными транзакциями

Answer 4

[rPman]

Если вышеописанные советы (заливаете данные во временную таблицу а потом delete + insert + commit) будут сильно нагружать базу, попробуйте вместо удаления делать update (имеет смысл если ваш скрипт единственный, кто правит данные, тогда не придется блокировать таблицу), однозначно это будет на порядок быстрее, так как почти наверняка в старых данных и в новых изменения минимальны (цены и небольшое количество новых и удаленных товаров).