Как правильно организовать хранение статистики?

Question

[entermix]

Есть таблица (InnoDB, ~ 80 полей типа INT), туда складывается статистика по пользователям, например:

date, user_id, sessions, sessions_active, sessions_failed, payments, .....

(это лишь пример)

Заполняется по срабатыванию триггеров MySQL. Получается так, что если пользователь хоть как то был активен (например авторизовался 1 раз), создается строка в БД.

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

Я вижу:

1. Размер таблицы (В данный момент ~1000 записей и 368 КиБ)
   
2. Блокировка строки (Будет медленно работать в случае большой активности на стороне пользователя)
   
3. ?...
   

Стоит ли разбивать эту таблицу на несколько мелких по 5-15 полей?

Answer 1

[Rsa97]

Основные тормоза при вставке - перестроение индексов. Так что если таблица в основном работает на запись, то, возможно, есть смысл минимизировать количество индексов, потеряв при этом производительность при выборке/сортировке.

Comments

[entermix]

Там только 2 индекса (uniq date + user_id и fk user_id), все остальные поля это unsigned int, и decimal(10,2). В приниципе эта статистика будет использоваться только для админ. целей. Настораживает то, что даже ради заполнения какого-то одного поля будет создана целая строка, это ведь повлечет за собой быстрое увеличение размера таблицы/БД

[Rsa97]

entermix: Есть другой вариант - хранить события. Записывать id пользователя, время, тип события и параметры события. Но в этом случае анализ становится сложнее.

[Eduard]

пока у вас меньше 50 миллионов пользователей, это вообще не проблема.

[entermix]

Rsa97: есть такое, но это как доп. статистика, например даже если мы удаляем событие с БД, в статистике мы увидим, что оно было в такой-то день, ну и для того, чтобы не делать выборки COUNT по огромному списку событий.

[entermix]

Eduard: тут скорее вопрос в том не съест ли такая структура очень много памяти, так как по идее от каждого пользователя будет каждый день активность, но это 5-10% полей от всех существующих, т.е. остальные будут по нулям.

Answer 2

[Eduard]

Вам надо пересмотреть вашу схему данных. 80 полей типа INT это перебор. Лучше сделайте так:

CREATE TABLE `user_action` ( 
`date` TIMESTAMP NOT NULL
, `user_id` INT NOT NULL 
, `event` ENUM('login','logout','payment','like') NOT NULL
, `value` VARCHAR NOT NULL
, PRIMARY KEY (`date`, `user_id`)
)

в event перечислите все свои 80 INT. на каждое действие делаете insert. в Value храните связанные с событием данные, есть нужен просто факт события, то сохраняйте там единицу, если данных от события много, то храните json.
В зависимости от скорости наполнения можно создавать такие таблицы на каждый год, месяц, неделю, день.
А вообще, google analytics уже придумали :)

Comments

[entermix]

То-есть Вы предлагаете на каждое событие делать отдельную запись?

[Eduard]

да. К тому же такую структуру проще анализировать.

[entermix]

Eduard: думаете так будет лучше? Например если мне нужно будет построить график с суммой неких действий за каждый день

[Eduard]

entermix: я не понимаю, в чем вы видите проблему.
select event, date, count(*) from user_action where date bewteen '20150101' and '20160101'
group by event, date

[entermix]

Eduard: у Вас есть опыт в выполнении таких запросов в highload?

[Eduard]

entermix: я не на собеседовании, и на ваш вопрос отвечать не буду. Не обижайтесь. Я правда не понимаю, почему вы задаете вопросы не по делу. У вас там в highload может быть только вставка данных, а вся аналитика по сути вещь в оффлайне, то есть для ее создания безразлично сколько времени длится запрос и что он там блокирует. Как я уже заметил, можно создавать таблицы хоть на каждый час. А благодаря партиционированию все это достаточно просто настраивается и автоматизируется. Еще раз: в чем вы видите сложность моего решения?