Практика ведения статистики базы данных на основе данных из другой таблицы
Я работаю с большой таблицей, например events в своей базе данных и стремлюсь упростить процесс получения статистических данных, не выполняя каждый раз сложные запросы. Я рассматриваю возможность создания дополнительной таблицы для хранения агрегированных данных. Например, мне нужно получить информацию о количестве событий, созданных за определенные периоды, например, с 1 января по 31 декабря, с разбивкой по месяцам.
У меня есть несколько вопросов:
- Какие есть практики ведения статистики базы данных на основе данных из другой таблицы?
- Какими методами можно автоматизировать процесс обновления этой таблицы статистики при добавлении новых данных в основную таблицу событий?
- Как эффективно создать и обновить таблицу со статистикой, чтобы получить быстрый доступ к данным без выполнения сложных запросов?
Моя цель - запросить данные из таблицы статистики, например по user_id и получить количество событий, созданных этим юзером за определенный период, с разбивкой по месяцам, чтобы представить результат в формате:
January: 10
February: 1
March: 25
Ответы (1 шт):
В первую очередь всё упирается в то, какая именно СУБД, включая версию, и как интенсивно таблица используется. А основная задача такого дополнительного процесса предварительного агрегирования - не поставить сервер на уши своими дополнительными нагрузками.
Соответственно если БД низконагруженная, а сервер БД обладает достаточными ресурсами, то делай как хочешь, хуже не будет. Но если ресурсов не сказать что много, а БД интенсивно используется, то оптимизация пре-агрегации необходима. И в зависимости от СУБД будут доступны различные способы оптимизации.
Например, если СУБД поддерживает per-statement триггеры, то лучше использовать именно их, потому как они обычно гораздо менее нагрузочны, чем per-row триггеры.
Можно также уменьшить нагрузку, если перейти от триггеров к выполняемым по таймеру процедурам расчёта. Такие процедуры могут выполняться в периоды наименьшей нагрузки (скажем, ночью), рассчитывая статистику для ещё не просчитанных периодов, а также (при наличии таких событий) для периодов, исходные данные для которых изменились. И уж совсем отлично, если СУБД имеет собственный планировщик, и ей не требуется внешнее управление для выполнения периодической функции.
Конечно, в этом случае часть статистики по тем данным, которые ещё не обсчитаны, будет получаться не из предрасчётной таблицы, а считаться "на лету", к тому же это приведёт к усложнению общего запроса - но совокупно это, как правило, легче, чем обсчитывать весь массив. А в случае, когда требуется статистика только по закрытым периодам, это и вовсе неактуально.
В дополнение к предыдущему варианту можно ещё использовать и партиционирование. Хотя, конечно, при получении статистики по времени уж само-то поле времени просто обязано быть индексированным, и, значит, эффект от партиционирования может быть не столь уж и велик.