Хранение хронологии изменения данных — как избежать создания дубликатов и оставить вес данных в таблице минимальным?

Question

[Олег Яценко]

В БД хранится история изменения некоторых данных об игроке, так же именно БД должна отвечать за отсутствие дубликатов записей.

Структура таблицы была:

account_id |   time   | param_1 | param_2 | param_3 |  sum  |
___________|__________|_________|_________|_________|_______|
    10     |    30    |   110   |   120   |   150   |  380  |
    10     |    20    |   100   |   120   |   150   |  370  |

Запросы (insert ignore) могут приходить повторно с теме же параметрами.
Поскольку значения param_n могли только увеличиваться, в sum хранилась сумма параметров param_n.
Уникальный индекс (account_id,sum) позволял не создавать дубликаты.

Теперь же добавились новые параметры X_param_n, значения которых могут как увеличиваться так и уменьшаться.
Структура стала:

account_id |   time   | param_1 | param_2 | param_3 | X_param_1 | X_param_2 |  sum  |
___________|__________|_________|_________|_________|___________|___________|_______|
    10     |    30    |   110   |   120   |   150   |     0     |     0     |  380  |
    10     |    20    |   100   |   120   |   150   |     0     |     0     |  370  |

Теперь возможна ситуация:

INSERT IGNORE INTO table VALUES (10,40,110,120,150,20,20,420);
INSERT IGNORE INTO table VALUES (10,50,110,120,170,0,20,420);
INSERT IGNORE INTO table VALUES (10,60,110,120,170,20,20,440);

, при которой второй запрос не создаст новой строки в БД, данные о том что параметр X_param_1 менял свое значение будут утеряны:

account_id |   time   | param_1 | param_2 | param_3 | X_param_1 | X_param_2 |  sum  |
___________|__________|_________|_________|_________|___________|___________|_______|
    10     |    60    |   110   |   120   |   170   |     20    |     20    |  440  |
    10     |    40    |   110   |   120   |   150   |     20    |     20    |  420  |
    10     |    30    |   110   |   120   |   150   |     0     |     0     |  380  |
    10     |    20    |   100   |   120   |   150   |     0     |     0     |  370  |

Понимаю, что можно использовать вместо суммы параметров их хеш, но это займет значительно дисковое пространство.
Так же можно просуммировать param_n и в конец дописать значения всех X_param_n - этот вариант тоже не подходит, поскольку параметров X_param_n достаточно много.

Нужен вариант, который позволит избежать описанной выше ситуации и при этом незначительно повлияет на вес данных в таблице. (строк на данный момент больше 40kk).
Может что еще посоветуете? Спасибо!

Answer 1

[Дмитрий Энтелис]

Вообще надо понимать что в задаче со строгой консистентностью данных, ситуация когда у Вас срабатывает ignore говорит о том что вы что то не то делаете)

Исходя из условий задачи - у меня сложилось стойкое ощущение что эта таблица изначально спроектирована не совсем корректно.

Я не понимаю почему вы выбрали уникальным (account_id, sum) а не (account_id,time)
Что такое time можно сейчас только догадываться, но если time не является уникальным для каждого account - надо добавить еще какое то поле которое совмесно с account_id и time будет давать уникальный ключ.

Либо как еще один вариант (телепат mode on ): если эта таблица как бы слепок полей из профиля пользователя account_id в момент времени time (когда что то изменилось) - надо просто добавить автоинкрементный primary key, убрать уникальный индекс и вставлять простым insert

если что пишите в скайп dentelis.

Comments

[Олег Яценко]

time - это timestamp, когда запрос был сгенерирован, запросы на БД идут не только в момент обновления данных - в этом основная загвоздка.