Сортировка страниц по количеству просмотров за день, неделю, месяц?

Question

[nokimaro]

Пытаюсь решить задачу по построению топа страниц на основании просмотров за день/неделю/месяц.
Суммарно страниц 20 млн и в день делается порядка 1 млн просмотров.
Данные хранятся в MySQL и поэтому решение задачи на нём же.
Ниже текущее решение и его недостатки.
А вопрос в том, можно ли сделать по другому (оптимальнее) на MySQL/SQL либо сделать лучше с использованием других технологий? Был ли у вас опыт решения подобных задач и что использовали?

Текущее решение такое:
Есть 2 таблицы, собственно страницы (items) и доп. таблица для подсчёта количества просмотров каждой страницы за определённый день.
Чтобы не делать лишний JOIN сделана денормализация и счётчики просмотров для сортировки items продублированы в таблицу items.

Могу выводить items простым запросом с сортировкой

ORDER BY items.count_views_day DESC
ORDER BY items.count_views_week DESC
ORDER BY items.count_views_month DESC

Упрощённая структура таблиц

CREATE TABLE `items` 
( 
     `item_id`           BIGINT NOT NULL, 
     `count_views_day`   INT NOT NULL, 
     `count_views_week`  INT NOT NULL, 
     `count_views_month` INT NOT NULL, 
     PRIMARY KEY (`item_id`)
) 

CREATE TABLE `item_views` 
( 
    `item_id`       BIGINT NOT NULL , 
    `day_timestamp` INT NOT NULL , 
    `count_views`   INT NOT NULL , 
)

В момент когда делается просмотр страницы, я делаю UPDATE счётчиков в items и делаю INSERTили UPDATE счётчика в item_views.
Раз в сутки я делаю апдейт счётчиков в таблице items путём суммирования данных из item_views за нужный период.
Фактически у меня получается что топ строится по кол-ву просмотров за нужный период (день/неделя/месяц) + (сегодняшние просмотры).

Минус моего решения в том что ежедневный апдейт счётчиков в items вызывает блокировки и выполняется достаточно долго.
Запрос для апдейта ниже. Повторяется 3 раза для день/неделя/месяц, за счёт изменения дат в условии BETWEEN

UPDATE items SET count_views_week = (
   SELECT IFNULL(SUM(item_views.count_views), 0)
   FROM item_views
   WHERE ( 
     item_views.day_timestamp BETWEEN ... AND ...
   ) AND item_views.item_id = items.item_id
)

Напрягают блокировки, так как пока идёт апдейт items.count_views_week все остальные запросы на увеличение счётчика лочатся. Фактически запрос апдейтит каждую строчку в items, а на текущий момент это 20млн строк.

Буду рад услышать ваше мнение и предложения по оптимизации.

Comments

[Denis]

а лишний джоин сколько потребляет времени? может индексы расставить по другому.

[nokimaro]

Denis, при таком количестве страниц и количестве просмотров, чтобы вывести топ за 30 дней, мне нужно ещё сделать сперва суммирование item_views.count_views за 30 дней с группировкой по item_id, так как данные в item_views разбиты по дням.
И вот подобный запрос из-за того что требует суммирование всех строк из sum_views работает сам по себе достаточно медленно (3-4 сек) и возвращает очень большое количество строк для дальнейшего JOIN'a
То есть возвращается каждый item_id который был скачан хотя бы раз за последние 30 дн, а это тоже миллионы строк.

Индексы в таблицах есть, просто на примере не показал их.

SELECT item_id, SUM(count_views) AS sum_views
FROM item_views 
WHERE day_timestamp > UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 30 day)) 
GROUP BY item_id 
ORDER BY sum_views DESC

[Denis]

В свое время я сталкивался с такой проблемой и там беда была в том что индексы долго перестраивались и мы пришли к мнению отказаться от UPDATE и использовать DELETE INSERT
то есть в Вашем случаее можно было бы сделать таблицы

items
items_views
items_statistic

в items_statistic перенести

`item_id` BIGINT NOT NULL,
`count_views_day` INT NOT NULL,
`count_views_week` INT NOT NULL,
`count_views_month` INT NOT NULL,

и при каждом пересчете очищать и использовать множественное добавление обновленных данных счетчиков (можно не всю сразу а постепенно к примеру по категориям)

[nokimaro]

Denis, да, интересный вариант.
Получается счётчики для сортировки выносим отдельно в сводную таблицу, по которой можно делать сортировка + быстрый JOIN так как нету лишних агрегаций.

DELETE+INSERT чтобы избежать блокировок можно делать путём создания пустой временной таблицы типа items_statistic_tmp, в неё делаем все INSERT с подсчётами, и затем можно атомарно сделать переименование таблиц (одним запросом)

items_statistic -> items_statistic_old
items_statistic_tmp -> items_statistic

Далее уже стереть items_statistic_old.

[Denis]

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

[Дмитрий]

Ещё вариант хранить счётчики не в мускуле, а например в мемкэше, по крону обновлять вторую таблицу

Answer 1

[Андрей]

А топ страниц нужен в реальном времени?
Не получится раз в час пересчитывать?

Comments

[nokimaro]

Пересчёт топа с некой задержкой или добавление кеширования не меняет общей картины, когда при массовом update мы получил блокировку таблицы и ситуация будет ухудшаться с увеличением количества страниц или просмотров.

[Андрей]

nokimaro, кликхаус?