(php+mysql) Как сделать масовые update данных в таблице?

Question

[Николай Мельник]

Приветствую.

У меня есть таблица пользователей и эти пользователи имеют счета в 3-4 системах. Каждый час я получаю данные со всех систем - с информацией баланса пользователя.

В итоге каждый час мне нужно обновить у себя данные по 150-200тыс записях в таблице пользователей.

Если обновлять в цикле
Update Users SET balance = 100 WHERE id = 123
то часа не хватит чтобы завершить обновление... а балансы нужны постоянно

Ищу решение которое срабатывает хотя бы за пару минут

Answer 1

[ThunderCat]

Транзакции, пачками по 300-500-1000, надо подбирать лучшее значение перебором, другого вроде ничего не придумали пока. И что за апдейты которые на 200к занимают час? Нормальный запрос выполняется 0,001 сек, на 1000 уйдет секунда, значит на 60к нужно минуту времени. 150к максимум 3 минуты. Возможно что с базой что-то не так, проверьте скорость работы update, сделайте explain, посмотрите какие индексы нужно добавить.

Comments

[Николай Мельник]

Сейчас попробую

Answer 2

[Григорий Есин]

Я делаю так, если нужно очень много обновлять:
* сначала создаём временную таблицу с двумя полями: Id, balance:

create table users_update_tmp(
  id,
  balance,
  primary key (`id`)
)

, тип таблицы и тип полей такие же как в "users";
* данные сливаем в временный csv-файл;
* затем делаем "load data local infile" из csv-файла во временную таблицу;
* затем обновляем целевую таблицу: "update users inner join users_update_tmp on users.id = users_update_tmp.id set users.balance = users_update_tmp.balance";
* затем удаляем временную таблицу;
* удаляем временный файл.
Не забудьте только повесить ключь на поле id во временной таблице, чтобы джойн в команде update работал эффективно.
Несколько миллионов кортежей такая схема переваривает легко. Самое тяжелое - слить данные в csv-файл.

Comments

[Николай Мельник]

У нас сейчас какраз работает отдельная таблица как и вы написали и делаем вывод баланса через join..

но ее постоянно нужно чистить (если не почистить больше 1-2 суток) запрос ложиться..

[Григорий Есин]

Я просто удаляю временную таблицу.
Поясню: при временная таблица создаётся на один раз: просто создаётся таблица с уникальным именем. Поэтому чистить времянку не надо - можно просто дропнуть.