В общем суть такая, есть сырые данные от пользователей (дата, какой раздел открывал, какое действие совершал, продолжительность нахождения в разделах и т.п.) которые непрерывно пишутся в таблицу (сейчас это MySQL). Примерно 200 миллионов записей и оно неуклонно растет, к концу года будет уже 400-500 миллионов.
На основе этих данных, необходимо строить различные отчеты по неделям, месяцам, кварталам, годам и т.п. (Sum, AVG, Count, Count Distinct и т.п).
Понятно, что напрямую запросы не идут, т.к. очень тяжелые, сначала данные агрегируются в другие таблицы, проблема в том, что если агрегация определенных данных в разрезе месяца занимает несколько минут, то в разрезе года это несколько часов или даже дней (запросы к MySQL, индексы есть, они тут не спасают).
Какие тут могут быть решения? Вообще что обычно используют для сбора и аналитики подобных статистических данных? Уместно ли вообще использовать MySQL для хранения постоянного потока данных (думаю что нет)?
Посоветую elasicsearch. Закидывайте в него свои данные (в вашем случае подойдет и logstash). Индексы бейте или на месяцы или на недели, организуйте их по годам/месяцам/дням через алиасы. Отчеты можете делать или через kibana, или сами дергать агрегированные данные из своих приложений. Индексы удобно ротировать, архивировать и удалять старые.
И будет щазтие.
Ну и да, если у вас только аналитика этих данных, то мускул здесь совсем не нужен!
Берите яндекс clickhouse. Он как раз для отчетов и больших объемов и запросы идут напрямую. С ним можно искать по миллиарду записей за 5-20 секунд(core i5, ssd, 16Gb RAM). Для построения отчетов приемлемое время. https://clickhouse.yandex/
SELECT
`param`,
`brand`,
`screen`,
COUNT(`id`) AS `requests`,
SUM(`duration`) AS `duration_sum`,
AVG(`duration`) AS `duration_avg`,
COUNT(DISTINCT `device_id`) AS `devices`
FROM `statistic_prolonged`
WHERE (
`date_start` BETWEEN '2018-01-01 00:00:00' AND '2018-12-31 23:59:59.999999' OR
`date_end` BETWEEN '2018-01-01 00:00:00' AND '2018-12-31 23:59:59.999999')
AND `action` = 'OPEN_SECTION'
GROUP BY `param`, `system_brandname`.`name`, `system_screensize`.`name`
sim3x, на чем основано это такое утверждение?!
У эластика:
1) совсем другой тип хранения данных
2) предназначен как раз для агрегации
3) в отличии от мускулов, может параллелиться во все стороны
4) у меня работает!
Я уже на тостере пару раз описывал одно из решений (с расчетом на прирост до 2000 млн записей в день), в кратце:
* таблицы на свалку, нужно паковать чанками (например чанк - 1 час/день данных в разрезе раздела) с индексами в доль разрезов, можно использовать nosql (mongodb с шардингом, хотя вам и одного сервера наверно хватит)
* чанки паковать (экономия до 95% места)
* далее после завершения периодов запускаются задачи которые наполняют "кеш" - строят отчеты во всех разрезах + промежуточные результаты, что-бы пользователю выдавать результат моментально когда он кликает по интерфейсу.
я делал решение на питоне, там где расчет занимал длительное время - делал с++ вставки, в результате расчет выполнялся в ~ х70 раз быстрее, и питон прокачивал более 10млн записей в сек. в один поток с учетом выкачивания из БД
