nokimaro
@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млн строк.

Буду рад услышать ваше мнение и предложения по оптимизации.
  • Вопрос задан
  • 254 просмотра
Пригласить эксперта
Ответы на вопрос 1
VladimirAndreev
@VladimirAndreev
php web dev
А топ страниц нужен в реальном времени?
Не получится раз в час пересчитывать?
Ответ написан
Ваш ответ на вопрос

Войдите, чтобы написать ответ

Войти через центр авторизации
Похожие вопросы