Php+Mysql. Минимизация кл-во запросов, для подсчета просмотров, как?

Question

[li_gang_chi]

Есть сайт php + mysql + yii2.
Суть: нужно считать кл-во раз сколько был показан каждый пост из списка.

Есть категорийные страницы, где списком(например по 20) выводяться посты.
В таблице постов есть поле views, которое хранит кол-во сколько раз пост был показан.
Именно показан в списке, а не сколько раз перешли и посмотрели сам пост(один), это важно.

При каждом выводе такого списка нужно обновить поле views++ для каждого из постов в списке.

Например если мы выводим списки по 20 постов мы делаем 20 запросов UPDATE в базу. Каждый раз когда такой список выводится.

Сейчас так у меня это и работает. Но при трафике 6к+ человек в сутки, плюс поисковые боты. Это не самое лучшее решение. Один такой запрос отрабатывает относительно быстро - 0.2 ms. Но сама таблица постов содержит 57к записей, и каждая запись имеет 23 поля. Время ответа сервера падает почти на 1сек

Какое оптимальное решение можно использовать чтобы уменьшить нагрузку на базу, и соответсвенно увеличить время ответа сервера?

Из идей пока только такое приходит в голову:

1. Хранить гдето(например в $_SESSION) показы, и раз в 10 минут переносить данные в базу и обнулять информацию в сессии. Но тут тоже, за 10 мин там насобирается очень много постов и они не просто будут харнится а еще и постоянно обновляться информация(сколько раз был показан пост).Так что не уверен что будет профит по скорости\оптимизации.
   
2. Считать уже на фронте, брать айди всех постов и одним ajax запросом обновлять.
   
3. По мере роста трафика переходить на более мощный сервер. Но это дорого по деньгам, т.к. не будет соответсвовать трафик сайта и мощность сервера. Покупать мощный сервер только для одной фишки - дорого. На данный момент текущего сервера хватает с огромным запасом.
   

UPD: В моем конкретном случае точность не важна. Можно допускать не большую погрешность в подсчете.

Comments

[FanatPHP]

про сессию ты совсем пальцем в небо. надо подучить, что такое сессия
про фронт тоже какая-то странная идея, несовместимая с жизнью
про железо всё верно

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

[Дмитрий]

Добрый день.
Без кода можно только предполагать, что именно нужно сделать.
Берите id на frontend-e, ajax-ом отправляйте список id и одним запросом обновляйте.
В yii есть updateAll().
Можно в очередь ставить обновление данных.

[li_gang_chi]

FanatPHP, сессия как пример, что гдето в промежутке хранить данные, а запрос делать раз в какое-то время

[Илья С]

а про запросы открою маленький секрет: если завернуть все апдейты в транзакцию, то и выполнятся они все за время одного запроса

FanatPHP, не факт. Так будет быстрее, но медленнее чем 1 запрос. Вот есть проверка подобного подхода:
https://stackoverflow.com/a/39831043/2244262

[neol]

FanatPHP,

а про запросы открою маленький секрет: если завернуть все апдейты в транзакцию, то и выполнятся они все за время одного запроса

Формулировка чуть кривенькая. 100% кто-то поймёт это как "если в транзакции обновить 100500 запросов, то они все выполнятся за время одного из них".

[FanatPHP]

Илья С, ой, не надо читать разных ламеров на стаковерфлое :)

[li_gang_chi]

Дмитрий, На счет кода. Просто вопрос скорей не в коде. а в подходе, в хранении и обновлении счетчиков. И не совсем понял про "Можно в очередь ставить обновление данных"

[FanatPHP]

li_gang_chi, не важно, как пример или как например. Возьми учебник и почитай что такое сессия и для чего она служит

[Дмитрий]

li_gang_chi, после загрузки страницы ставить в очередь на обновление данных в базе. Обновление будет происходить в фоне, ничего напрягать не будет. Использовать можно yii2-queue

[li_gang_chi]

Дмитрий, спасибо, подумаю и над этим

[FanatPHP]

Дмитрий, вот опять ты тупишь.
какой еще фронтенд?
список генерится на сервере. а это значит что там уже всё есть, ничего с клиента передавать не надо.
вот из-за таких адовых велосипедов над пхпшниками все смеются

[li_gang_chi]

FanatPHP, Хорошо, обязательно куплю учебник, и почитаю что такое сессия, когда на экзамен приходить? п.с. риторический вопрос - можно не отвечать

[Дмитрий]

FanatPHP, во-первых, ещё раз повторяю Вам, тыкать будете в другом месте и другим людям. Умерьте свой пыл.
Во-вторых, исходя из первого что-либо обсуждать с Вами, "очень крутым пхпешником", нет никакого желания.
Научитесь общаться, потом продолжим.

[FanatPHP]

Дмитрий, та я и не стремлюсь к общению. Так, потыкать палочкой, на предмет может дойдёт

[Илья С]

Илья С, ой, не надо читать разных ламеров на стаковерфлое :)

FanatPHP, сдаётся мне, тот "ламер" шарит больше чем ты.
Есть подтверждение, что множество UPDATE'ов внутри транзакции отработают с той же скоростью, что 1 UPDATE с перечислением всех ID? Если даже INSERT ... ON DUPLICATE UPDATE по тестам работает быстрее множества UPDATE в транзакции.

[FanatPHP]

li_gang_chi, так можно и не покупать. я ж не знаю, может ты этому коду никто, бухгалтер какой-нибудь. тогда действительно разбираться в программировании не нужно

[FanatPHP]

neol, хорошее замечание, поправил коммент

[FanatPHP]

Илья С, ну начнем с того, что запрос INSERT ... ON DUPLICATE UPDATE это запрос на вставку. Этот момент упускают все диванные теоретики и любители микробенчмарков со стаковерфлоя, ни разу в жизни свои советы не применявшие на практике. И подходит такой вариант далеко не всегда.

Если же говорить о конкретном запросе, то в данном случае не так важно, что "быстрее". Относительные величины уже будут не принципиальны.
Транзакция - универсальное решение, которое работает для всех случаев с приемлемой скоростью, решая проблему с innodb_flush_log_at_trx_commit, которая тормозит запись в 70-80 раз. И вот если это адское торможение убрать, то дальше уже будет без разницы, как именно вставлять. И уж на выполнении несчастных 20 запросов в микроскопическую таблицу автора разница таки не будет заметна.

Ну и надо всегда помнить, что любители тестирования со стаковерфлоя тестировать-то как раз поголовно и не умеют. И цифры у них могут показывать что угодно.

[Илья С]

FanatPHP, столько понтов, столько воды :) без комментариев в общем

[FanatPHP]

Илья С, ничего, если сейчас непонятно, то в будущем пригодится :)

[FanatPHP]

Илья С,

Есть подтверждение, что множество UPDATE'ов внутри транзакции отработают с той же скоростью, что 1 UPDATE с перечислением всех ID?

Кстаааааати! Не флейма ради, а истины для.
Я тут вчитался. Твой ламер пишет что апдейт таки в 6 раз медленнее транзакции :)))

CASE method was 41.1x slower than the INSERT method w/ 30,000 records (6.1x slower than TRANSACTION).

Там конечно апдейт чуточку посложнее будет, но явно не настолько, тупой кейс посчитаьт это тебе не по диску бегать.

причем именно этот его результат вызывает у меня оооочень большие сомнения.

[Илья С]

FanatPHP, для меня как раз всё очевидно.
INSERT * ON DUPLICATE UPDATE - как раз самый действенный метод. Одна чистая операция, поисков ровно столько сколько надо, проверка всегда идёт по PK, либо UK.
Куча UPDATE'ов в транзакции это конечно эффективнее чем те же апдейты без транзакции, но тем не менее это куча отдельных операций и мускуль не настолько умный, чтобы схлопнуть их в одну.
Ну а UPDATE + CASE сложнее тем, что на каждое обновление он ещё дополнительные вычисления производит. Если без CASE то UPDATE был бы быстрее конечно.

[FanatPHP]

Илья С, ой ну не смеши. какие там "вычисления" - тупой кейс, каждая ветка в три инструкции процессора :)
Это же не во where кейс, а просто для понимания какое число куда писать. Ни один современный проессор его вообще не заметит.

Куда интереснее то что UPDATE - это тоже "Одна чистая операция, поисков ровно столько сколько надо, проверка всегда идёт по PK, либо UK". И она должна быть на самом деле "быстрее" потому что нет предварительной попытки вставить, зарезервировать id, обработки ошибки, и только после этого - апдейт.
Так что тупит твой ламер. Всё как я говорил - меряет сам не знает что, цифры от балды :)

А про INSERT - поверь, не один и не два разработчика больно кусали себе локти, когда у них вместо апдейтов вдруг вставлялась куча левых строк. Не стоит использовать запрос INSERT там где тебе нужен UPDATE. Ну только если ты не эксперт со Stack Overflow :)
INSERT * ON DUPLICATE UPDATE имеет очень четкую область применения. И не нужно его использовать как лайфхак для апдейтов.

[Илья С]

FanatPHP, у меня на данный момент есть проект, где ежедневно нужно делать расчеты объемом несколько десятков гб и миллионов строк.
Большинство операций как раз обновление данных сразу на куче строк. Практика показала, что именно INSERT ... ON DUPLICATE KEY UPDATE работает быстрее всего. Конечно нужно быть на 100% уверенным, что новых данных не вставится, но это не такая уж сложная задача.

[FanatPHP]

Илья С, как-то это странно.
INSERT соверенно точно производит проверку первичного ключа и совершенно точно резервирует автоинкремент при его наличии. и только потом производит собственно апдейт. То есть делает то же самое что и апдейт, но при этом ещё всякое. Я не вижу на чем тут может быть потеря производительности у апдейта.
Ну, будет время, попробую посмотреть.

Answer 1

[Илья С]

А зачем делать 20 UPDATE запросов, если можно сделать 1 запрос? :)

Просто указать все ID списком:

UPDATE posts SET views = views + 1 WHERE id IN (1,2,3,5)

Comments

[li_gang_chi]

спасибо, логично, чего-то я протупил)) просто в контексте сайта, вариант с 20-ю запросами был проще и быстрее.
но всеравно интересно может какие есть еще варианты

[Илья С]

li_gang_chi, другие варианты ещё сложнее.
Если запросов очень много и даже один UPDATE тормозит, тогда придётся вводить буферизацию.
Например складывать id'шники всех статей в некий массив в redis и раз в час по крону доставать эти id из redis'а и добавлять views соответствующим постам.
Тут ещё всё сильно зависит от реализации. Если там сотни тысяч просмотров, то там явно не помешало бы кеширование, но в таком случае механизм счётчиков придётся ещё больше усложнять.

Answer 2

[Alex]

Кроме ответа Илья С, можно уменьшить точность подсчетов. Это подходит для ситуаций когда много трафика и 100%-я точность показателей не нужна. Например, допустимая погрешность = ±5 просмотров. Тогда можно делать обновление не при каждом посещении, а примерно раз в 5 посещений и обновлять просмотры не на +1 а на +5.
Даже с погрешностью ±5 просмотров вы уменьшите количество обновлений БД на 80%. А Увеличивая допустимую погрешность (±10, ±50, ±100) можно ещё сильнее уменьшить нагрузку.

Comments

[li_gang_chi]

да, точность не важна, т.к. там цыфры достигают сотни тысяч просмотров на одном посте, и выводятся в формате К КК ККК. спасибо за ответ, интересно будет подумать в направлении что точность не важна

Answer 3

[Vladislav]

Опоздал, ну ладно...

UPDATE articles 
SET view = view+1
WHERE id IN (1,2,3);

Answer 4

[Александр Лыкасов]

Вот тут есть мысли по поводу организации счётчиков в Mysql

Comments

[li_gang_chi]

спасибо

[FanatPHP]

это ему как танк чтобы муху прибить :)