Как оптимизировать / кэшировать кучу тяжелых запросов к MySQL?

Question

[Сергей]

Привет тру-программистам.

Короче сделал я себе на сайте небольшую систему статистики. Выглядит она следующим образом:


Работает на основе таблицы с просмотрами постов, куда заносятся реферер, дата и прочее. Но есть проблема: каждая из этих цифр рассчитывается отдельно - каждое кол-во просмотров, каждого источника + каждая динамика в сравнении с прошлым периодом (в скобках). Получается дофигища тяжелых запросов, из-за чего очень сильно тормозит загрузка списка постов в админке. Именно этот момент создает мне неудобства.

Есть ли способ кэшировать это таким образом, чтобы не создавать лишнюю нагрузку? Просто я смотрю на эти цифры от силы 1-2 раза на день и то не всегда. А если поставить все это на крон, то сервер просто обалдеет от перегрузки, когда ему придется рассчитывать все это для всех страниц сайта. Следовательно, в таком кэшировании смысла нет.

Максимум, что я смог придумать - это подгружать всю эту муть асинхронно аяксом. Тогда страница с постами будет шустро грузиться, но стату все равно придется ждать. Да и как быть с сортировкой например? Она от таких запросов тоже тормозит. Есть идеи у кого-нибудь, чего тут можно сделать?

Comments

[FanatPHP]

Я думаю, можно научиться гуглить

[Сергей]

FanatPHP,

"Окей, гугл. я как кэшировать 100500 запросов, чтобы не взорвать дата-центр"...

P.S. Вот вы бы на моем месте что в гугл забили? Просто интересно ;)

[Tendor]

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

[Сергей]

Tendor, это можно сделать. Но будет не очень удобно. Хотелось бы, чтобы эти цифры остались на своих местах. Но вот способ уменьшить нагрузку придумать не могу.

[FanatPHP]

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

[Сергей]

FanatPHP, я так и сделал. Поэтому я здесь)

[ThunderCat]

Сергей,

Вот вы бы на моем месте что в гугл забили? Просто интересно ;)

хм, давайте ка подумаем, что у нас вообще происходит? Тормозят запросы? наверное да... Как проверить? Ну, можно же посмотреть сколько запрос выполняется? Ок, смотрим "как проверить скорость запроса?"...
(a few hours later...) Спасибо, гугл, понял, проверил, все плохо... ок, "Как оптимизировать скорость запросов?"...
(a few days later...) Спасибо, гугл, понял... ок, "Как использовать explain?"...
(a few days later...) Спасибо, гугл, понял... ок, "Как правильно настраивать индексы для таблиц?"...

Answer 1

[ThunderCat]

1) Проверить индексы на таблицах, с такими объемами у вас все должно летать.
2) Кэширование используется на часто вызываемых запросах, где не критично отставание от точных хранимых данных в пределах некоторго заданного временного интервала, например список последних новостей на сайте изменяется не часто, а запрашивается постоянно, есть смысл хранить его в кеше хотя бы 15-30 минут. А у вас ситуация ровно обратная - запрос делается редко, и данные нужны актуальные.
3) к п.1 нужно соответственно добавить проверку, как работают запросы и используются индексы, explain в руки и вперед.

Comments

[Сергей]

1) Проверить индексы на таблицах, с такими объемами у вас все должно летать.

Не очень понял, что вы имеете в виду под "проверить индексы".

Вот пример запроса получения динамики за период:

$sql = 'SELECT 
			COUNT(CASE WHEN `time` > NOW() - INTERVAL ' . $period . ' DAY THEN `ID` END) -
			COUNT(CASE WHEN `time` < NOW() - INTERVAL ' . $period . ' DAY AND `time` > NOW() - INTERVAL ' . ($period * 2) . ' DAY THEN `ID` END)
			FROM `' . $wpdb->prefix . POST_VIEWS_TABLE_NAME . '` 
			WHERE `ID` = "' . $post_id . '"';

Из таких вещей там состоит все... Получение фул-статы по 10 постам - зависает секунд на 15... И это на сервере reg.ru с тарифом за 1.2к в месяц.

[ThunderCat]

Сергей, Плохо... читайте про SQL, индексы для таблиц, оптимизацию запросов... Волшебного решения "поставить плагин MegaSuperFast" нету, тут работать надо, в основном головой.

И это на сервере reg.ru с тарифом за 1.2к в месяц.

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

[FanatPHP]

Не очень понял, что вы имеете в виду

Эээ..
а ты правда никогда не слышал про индексы в базе данных?

[Сергей]

FanatPHP, Я не профессиональный программист. Окей?) Я любитель. Поэтому знаю о базах далеко не все. Вы так высокомерно отвечаете, как будто, если я о них не слышал, то я вообще не человек. Не надо так.

[ThunderCat]

Сергей, Вопрос вполне резонный, если вы занимаетесь чем-либо на уровне "хочу сделать хорошо и правильно", то азы и фундамент изучаете хотя бы поверхностно, а приведенная вами задача не совсем как бы тривиальная, и требует вполне профессиональных навыков. Никто же вас не заставляет это делать (надеюсь)? Можете делегировать эту задачу кому-то кто шарит, или разбираться самому, тут выбор за вами. Но учить вас от азов до профи никто не будет, тем более с такими серьезными пробелами в элементарных знаниях.

Вы так высокомерно отвечаете,

Вполне закономерная реакция легкого ох*вания, от того что человек вроде как решает серьезную задачу, но в азах её ни в зуб ногой...

[Дмитрий]

Сергей, Этот тип всегда так общается. Просто не обращайте внимания. Тем не менее без знания индексов и как ими управлять задача не решаема.

[Сергей]

ThunderCat, я пришел не за тем, чтобы меня учили. Я пришел, чтобы мне подсказали, в какую сторону копать. Вот, я узнал про индексы например. Сижу, разбираюсь.

Чет вы злые тут все какие-то...

[FanatPHP]

Ну про высокомерие конкретно здесь ты нафантазировал
Про неумение пользоваться гуглем - там да. А здесь чисто удивление/уточнение.

[ThunderCat]

Сергей,

Я пришел, чтобы мне подсказали, в какую сторону копать.

Если честно - лучший ответ я привел в комментариях к вопросу, там все что нужно уметь - гуглить, а ключевым умением для человека занимающегося программированием является навык постановки задач, в том числе и четкое формулирование проблемы. Что сильно облегчает не только программирование, но и жизнь в целом ) Учитесь ставить правильные вопросы и ваш прогресс в освоении любого дела пойдет просто семимильными шагами, инфа сотка.

Answer 2

[Сергей]

Короче. Проблему решил так.

- Услышал про индексы, пошел разбираться.
- Наткнулся на длинную серьезную статью по этой теме: https://ruhighload.com/%D0%98%D0%BD%D0%B4%D0%B5%D0...
- Изучил от и до; приписка в конце положила под стол.
- Как опытный, крайне смекалистый программист-любитель я практически моментально и до последнего слова все не понял, поэтому от нудной теории перешел сразу к веселой практике.
- Начал тестить; в процессе научился работать с БД через консоль.
- Как оказалось, волшебная таблетка - создание индекса на поле ID для таблицы просмотров.

$sql = 'CREATE INDEX id ON wp_post_views(ID)';
$wpdb->get_results($sql);

* здесь ID - это ID поста, к которому относится просмотр, а не primary key, как может показаться

Заткнул я это на хук смены темы (надеюсь я правильно понял, что этот индекс не нужно обновлять вручную?). Залил патч на сервер и, - о чудо! - реально все летает. Там, где раньше грузилось за 10-15 сек стало грузить за 0.5-1.

Mission Complete.

P.S. Всем спасибо, кто помогал. Но впредь сильно не матюкайтесь.. Не все такие крутые гуру, как вы. Некоторые учатся в процессе. Отсюда и шишки.

Comments

[Vitsliputsli]

Очень странное решение... Под id обычно подразумевают уникальный идентификатор, на который вешают primary key и соответственно создание никаких дополнительных индексов не нужно.
Если положение улучшилось, то скорее всего в id храните "Google", "Yandex" и прочее, либо их идентификаторы. Так?

К слову, оптимальным будет написать 1 запрос на все. И так как он будет обрабатывать все строки в таблице, то и индексы ему будут не нужны. Что-то вроде:

select
    project,
    title,
    count(CASE WHEN date>'16.09.2020 00:00:00' THEN 1 ELSE null END) count_in_day,
    count(CASE WHEN date>'14.09.2020 00:00:00' THEN 1 ELSE null END) count_in_week,
    count(CASE WHEN date>'01.09.2020 00:00:00' THEN 1 ELSE null END) count_in_month,
    count(*) count_all
    from table
    group by project,title

[Сергей]

Очень странное решение... Под id обычно подразумевают уникальный идентификатор, на который вешают primary key и соответственно создание никаких дополнительных индексов не нужно.
Если положение улучшилось, то скорее всего в id храните "Google", "Yandex" и прочее, либо их идентификаторы. Так?

ID у меня там - это ID поста, на самом деле, к которому относится просмотр. А id просмотра - это view_id. Я не помню, почему я так сделал, но раз сделал, то была причина какая-то...
А в колонках гугл, яндекс и так далее у меня просто булево значение.

К слову, оптимальным будет написать 1 запрос на все

У меня плохо с SQL, поэтому я так глубоко в это не вникал. Попробую завтра объединить по вашему рецепту. Может станет еще лучше.