Блог

Мы хотим создать счетчик просмотров за сообщение (https://my-domain.com/posts/post-title ) и мы хотим сохранять просмотры в день, чтобы разрешить сортировку сообщений по «Самым популярным за последние 7 дней», «Самым популярным за последние 30 дней» и т.д. Нам доступно несколько решений.

Решение A: создайте таблицу «view_counters» со столбцами ниже:

 id
post_id INT
08_28_2020 INT DEFAULT 0
08_29_2020 INT DEFAULT 0
08_30_2020 INT DEFAULT 0
08_31_2020 INT DEFAULT 0
...
  

Каждый день задание cron в PHP добавляло столбец с сегодняшней датой в качестве имени.
В каждой записи будет одна строка. При каждом просмотре мы бы увеличивали столбец на текущую дату.
Это решение позволяет иметь что-то более легко читаемое для пользователей с веб-интерфейсом (phpMyAdmin), но все еще создает много столбцов, и это, вероятно, не самое оптимизированное решение для ядра базы данных. поскольку через 1 год в таблице будет более 365 столбцов.

Решение B: создайте таблицу «view_counters» со столбцами ниже:

 id
post_id INT
current_date DATE
counter INT DEFAULT 0
  

На каждое сообщение в день будет приходиться одна строка. При каждом просмотре мы бы увеличивали столбец «счетчик» в строке рассматриваемого сообщения.
По общему признанию, она менее читаема через phpMyAdmin (вам придется сделать небольшой запрос), но, безусловно, ее легче читать и обрабатывать для ядра базы данных. Поправьте меня, если я говорю неправильно!

Решение C: создайте таблицу «view_counters» со столбцами ниже:

 id
post_id INT
current_date DATE
  

Каждый просмотр добавлял бы строку в таблицу. Тогда у нас был бы запрос, который подсчитывал бы количество просмотров такого-то поста за текущую дату (с помощью COUNT()). Однако мы считаем, что это решение не подходит, потому что было бы необходимо выполнять COUNT() каждый раз, когда посетитель загружает страницу публикации, зная, что у нас более 100 000 просмотров страниц в день, что потребовало бы много ресурсов для повторного подсчета каждый раз. Итак, решение, на наш взгляд, не подходит…

Решение D: Если у вас есть другое решение с более оптимизированной структурой, я хотел бы узнать больше!

Надеюсь, что это было ясно и доходчиво. Заранее благодарю вас за ваши ответы!

Решение A

Если решение требует регулярных изменений структуры вашей базы данных в СУБД, то это решение неверно. СУБД работают с определенной структурой, которая может иногда меняться. но это также приводит к изменению кода. Кроме того, как бы вы получили сумму просмотров в пределах диапазона таким образом?

Решение B и C

С точки зрения проектирования базы данных это эквивалентные решения, потому что у вас есть одна запись на измеренное событие. Разница между ними заключается в уровне детализации собираемой вами информации.

Если вас интересует только то, сколько раз сообщение было просмотрено за день, тогда используйте solution B .

Однако, если вам нужна более подробная информация, например, кто посетил сообщение, в какое время дня было посещено сообщение, регулярно ли пользователи посещают одно и то же сообщение и т.д., Тогда вам нужно использовать solution C . Очевидно, что решение C имеет смысл, только если вы сохраняете дополнительные сведения, а не только дату просмотра.

TL; DR ответ

Используйте решение C в сочетании с материализованным представлением ежедневных просмотров страниц.

Более длинный ответ

Решение A не является хорошим решением с точки зрения проектирования базы данных. Во-первых, это затрудняет агрегирование данных и требует ежедневной модификации таблицы. Последнее может привести к неэффективному хранению строк в табличных пространствах.

Решение B является работоспособным и было бы хорошим решением, если бы это была подробная таблица (т. Е. Имела отношение FK к первичной таблице сообщений), и вас не волнует временное разрешение менее суток. На мой взгляд, это немного тупик с точки зрения проектирования базы данных, и я бы не рекомендовал это.

Решение C предоставляет возможность сохранять фактическую дату / время просмотра, а также возможность сохранять дополнительные сведения о просмотре записи (например, просмотр пользователя (логин или аноним), страна происхождения и т.д.). С помощью решения C вы можете создать материализованное представление, которое создает решение B (обращаясь к нему ежедневно), поэтому решение C является решением 2 к 1. Кроме того, решение C также позволит вам создавать другие агрегированные представления (еженедельные агрегаты, просмотры в зависимости от времени суток, просмотры по пользователям и т.д.).

B, но проще

 post_id INT
date DATE   -- (there is nothing "current" about the date")
counter INT DEFAULT 0
  

То есть избавьтесь от id , это бесполезно, так как у вас должно быть PRIMARY KEY(post_id, date) .

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

У вас есть «сводная таблица». Еще обсуждение:http://mysql.rjweb.org/doc.php/summarytables

«Отчет» суммировал бы подсчеты, чтобы получить общее количество для любого (еженедельного / ежемесячного / любого другого) диапазона дат. Затем «Самый популярный» основывается на этих подсчетах.

C

«было бы необходимо выполнять COUNT() каждый раз, когда посетитель загружает страницу» — Это не настоящая проблема; это можно было бы решить с помощью IODKU, который либо добавлял бы новую строку (один раз в день, за публикацию), либо обновлял существующую строку.

И IODKU является альтернативой наличию кода для ежедневного подведения итогов. Примечание: моя схема таблицы будет работать с IODKU. Кроме того, 100 тысяч просмотров в день «тривиальны». Если вы достигнете 10 миллионов в день, вам может потребоваться переключиться на ночное подведение итогов.

Эмпирическое правило: менее 100 запросов в секунду не является проблемой; более того, может потребоваться специальная обработка.

A

Почти всегда плохая идея распределять «массив» по столбцам.