Улучшение messages.php для 1.6.* и 1.7.

2621
Файл, который правим: /components/users/messages.php
Что требует улучшения:
SQL-запрос, отвечающий за выгрузку входящих сообщений (примерно 28 строка в 1.7RC и примерно 50 в 1.6.*):
Код PHP:
                
$sql = "SELECT m.*, m.senddate as fpubdate, m.from_id as sender_id, u.nickname as author, u.login as author_login, u.is_deleted, p.imageurl
                FROM cms_user_msg m
                LEFT JOIN cms_users u ON m.from_id = u.id
                LEFT JOIN cms_user_profiles p ON m.from_id = p.user_id
                WHERE m.to_id = $id
                ORDER BY senddate DESC
                LIMIT ".(($page-1)*$perpage).", $perpage";

Почему требует улучшения: Требует улучшения по одной простой причине, чем больше у вас сообщений в таблице, тем медленнее будет работать запрос так он использует 2 LEFT JOIN. У меня на одном сайте дошло до 9 секунд (только обработка запроса).
Почему LEFT JOIN нельзя заменить на WHERE? Потому что from_id может принимать отрицательные значения (-1, -2 )- информацаионная рассылка, а таких юзеров в cms_users и cms_user_profiles нет!
Также стоит обратить внимание, что ORDER BY id DESC и ORDER BY senddate DESC по смыслу эквивалентны, но сортировка по ключу ВСЕГДА БЫСТРЕЕ. Это, кстати, касается и всех остальных запросов.
Способы решения:
1) Создать юезров с id -1, -2 И переделать запрос на:
Код PHP:
                
$sql = "SELECT m.*, m.senddate as fpubdate, m.from_id as sender_id, u.nickname as author, u.login as author_login, u.is_deleted, p.imageurl
                FROM cms_user_msg m, cms_users u, cms_user_profiles p
                WHERE m.to_id = $id AND m.from_id = u.id AND m.from_id = p.user_id
                ORDER BY id DESC
                LIMIT ".(($page-1)*$perpage).", $perpage";
2) Ничего не создавать, а переписать запрос на:
Код PHP:
                
$sql = "(SELECT m.*, m.senddate as fpubdate, m.from_id as sender_id, u.nickname as author, u.login as author_login, u.is_deleted, p.imageurl
	FROM cms_user_msg m, cms_users u, cms_user_profiles p
	WHERE m.to_id = $id AND m.from_id = u.id AND m.from_id = p.user_id AND m.from_id>0)

UNION

	(SELECT m.*, m.senddate as fpubdate, m.from_id as sender_id, 'NULL', 'NULL', 'NULL', 'NULL'
	FROM cms_user_msg m
	WHERE m.to_id = $id AND m.from_id<1)

ORDER BY id DESC
LIMIT ".(($page-1)*$perpage).", $perpage";	
Можно также предложить ряд запросов, используя IF, но этот мне кажется самым простым и понятным.

1-ый способ работаем быстрее, но для тех кто уже озадачился оптимизации БД и поставил id типа UNSIGNED не подойдет.

Выигрыш по сравнению с иcходным на одном из сайтов где много сообщений (при LIMIT 30):
ДО: Отображает строки 0 - 29 (30 всего, запрос занял 3.9469 сек.)
ПОСЛЕ: Отображает строки 0 - 29 (30 всего, запрос занял 0.1215 сек.)
Прирост производительности более, чем в 30 раз для каждого обращения к входящим сообщениям.
Исправляем bbcode.lib.php (email_2html)
Комментарии (16)
Fuze 28 ноября 2010 в 19:12 +2
У меня на одном сайте дошло до 9 секунд (только обработка запроса).
спасут индексы и только индексы.
Также стоит обратить внимание, что ORDER BY id DESC и ORDER BY senddate DESC по смыслу эквивалентны, но сортировка по ключу ВСЕГДА БЫСТРЕЕ. Это, кстати, касается и всех остальных запросов.
известный факт, я где разбирал конкретный запрос так и делал.
Требует улучшения по одной простой причине, чем больше у вас сообщений в таблице, тем медленнее будет работать запрос так он использует 2 LEFT JOIN.
тоже по моему мнению не так, достаточно взглянуть на explain и все становиться ясно. Еще раз: ПРАВИЛЬНЫЕ ИНДЕКСЫ по соединяющим полям. Кстати можно переделать на INNER JOIN, но не уверен, что будет быстрее.

Не очень уверен в правильности написанном на самом деле, но и не исключаю что это может быть реально быстрее. Надо разобраться, одно скажу - в 1.7 уже точно не буду править.
0 28 ноября 2010 в 19:27 0
Код PHP:
Еще раз: ПРАВИЛЬНЫЕ ИНДЕКСЫ по соединяющим полям.
Даже они не спасут, так как from_id = -1, графы u.nickname as author, u.login as author_login, u.is_deleted, p.imageurl не существуют.
Кстати выигрыш тем заметее, чем больше входящих имеют from_id < 1.

Опять же, Fuze, я не навязываю!smileУ кого есть большая таблица с входящими сообщениями могут поделать тесты! :)
Fuze 28 ноября 2010 в 19:30 0
ольшая таблица с входящими сообщениями
большая это сколько?
Опять же, Fuze, я не навязываю!
да я только за любые оптимизации, только обоснованные или пришедшие к общему мнению путем дебатов))))
0 28 ноября 2010 в 20:34 0
:)) Отлично, рад, что мы поняли друг друга.
Большая это более 40 тыщ)
Fuze 28 ноября 2010 в 20:03 0
AND m.del_to = 0
видимо твоя правка, удали это из запроса чтоб народ не попутать))
0 28 ноября 2010 в 20:36 0
Спасибо поправил.
Fuze 28 ноября 2010 в 20:21 0
переписал так, оттесть если не сложно на своей большой базе:

Код PHP:
SELECT m.*, m.senddate as fpubdate, m.from_id as sender_id, u.nickname as author, u.login as author_login, u.is_deleted, p.imageurl
						FROM cms_user_msg m
						INNER JOIN cms_users u ON u.id = m.from_id
						INNER JOIN cms_user_profiles p ON p.user_id = u.id
						WHERE m.to_id = '$id' AND m.from_id>0

						UNION
						
						(SELECT m.*, m.senddate as fpubdate, m.from_id as sender_id, 'NULL', 'NULL', 'NULL', 'NULL'
						FROM cms_user_msg m
						WHERE m.to_id = '$id' AND m.from_id
0 28 ноября 2010 в 20:36 0
Отображает строки 0 - 29 (30 всего, запрос занял 2.4243 сек.)
Fuze 28 ноября 2010 в 20:42 0
по каким полям у тебя индексы в участвующих таблицах?
Fuze 28 ноября 2010 в 20:31 0
хотя честно сказать, мои тесты показали, что твой запрос срабатывает гораздо дольше, чем оригинальный с лефт джоин.
0 28 ноября 2010 в 20:40 0
Это странно, я обтестировался на 2-ух сайтах, у меня исходный запрос работает меееедлено.
В общем, спасибо за участие в дебатах. Думаю каждый сам для себя решит! Мне нравится мой.
Буду рад, если кто-то еще потестит и напишет результаты.
Fuze 28 ноября 2010 в 20:45 0
думаю после релиза можно будет загнаться и поставить на какой-нибудь тестовый хост с нуля и искусственно заполнить хоть 50тыс записей. Можно и не только для сообщений.
0 28 ноября 2010 в 21:40 0
Как говориться: будем ждать релиза)
0 10 декабря 2010 в 22:21 +1
Тут даже нечего тестировать.JOIN и EXPLAIN работают на допустимой скорости только пока сайт малопосещаемый. Как только кол-во уников в сутки переваливает за определенное число(зависит от железа), начинаются жуткие тормоза, и решать эту проблему оптимизируя запрос JOINами и EXPLAINами, все равно что лить воду в дырявое ведро. Сколько бы индексов не напихали,JOINы и EXPLAINы - это зло для нагруженной системы, а со злом надо бороться. Распространенные способы решения проблемы(на примере сообщений): 1)начать кэшировать результаты запроса для каждого пользователя в памяти, используя key-value хранилища, http://memcached.org/ или Redis http://code.google.com/p/redis/ (второй предпочтительней), этот способ работает только если у вас есть как минимум свой ВДС, но вы же не думаете что высоконагруженный проект возможен на шаринг хостинге. Запись и чтение в memcached и redis, сверхбыстрые, в три и два раза быстрее чем mysql я вижу это так: Мы обращаемся с запросом к memcached получить ленту сообщений пользователя vasya. если memcached возвращает нулевое значение, то делаем запрос к mysql, получаем данные, выводим ленту на экран, и заодно записываем только что полученные данные из mysql в memcached, скажем в формате YAML. для чего? а для того что, теперь если у нас появляется новое сообщение для пользователя vasya. мы просто берем из memcached закешированную ленту сообщений, добавляем к ней новое сообщение, и быстро записываем уже дополненную ленту обратно в memcached, выводим на экран если надо , и запускаем фоновую задачу которая записывает необходимые данные в mysql. Все, мы получили сверхбыстрые сообщения. 2) Избавляемся от JOIN и EXPLAIN. Избыточность данных. Меняем чуть архитектуру базы.при записи нового сообщения в таблицу cms_user_msg, сразу записываем туда не только идексы на связанные таблицы, но и все данные нужные для отображения ленты сообщений, без джоинтов с другими таблицами:nickname, login, deleted, imageurl. + используем первый способ. дальше извращаться можно как угодно... П.С. конечно для хайлоад, 70000+ и больше уников прийдется менять архитектуру приложения вкорне, переписывать наново, на горизонтально маштабируемое. чтобы просто добавил сервер, и нагрузка спала, и равномерно распределилась по всем серверам.
Fuze 10 декабря 2010 в 22:47 +1

Разговор был о базе данных, а не о средствах кеширования.
 

JOIN и EXPLAIN работают на допустимой скорости

стесняюсь спросить а при чем здесь EXPLAIN???
EXPLAIN используется для ОТЛАДКИ.
А для того чтобы ГРАМОТНО применить кеширование, необходимо разумеется для начала оптимизировать до конца запросы. То, что Вы описываете
 

Меняем чуть архитектуру базы.при записи нового сообщения в таблицу cms_user_msg, сразу записываем туда не только идексы на связанные таблицы, но и все данные нужные для отображения ленты сообщений, без джоинтов с другими таблицами:nickname, login, deleted, imageurl

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

p.s. у нас принято так: кто идею подал - тот ее и реализует. smile
 

Избыточность данных. Меняем чуть архитектуру базы.при записи нового сообщения в таблицу cms_user_msg, сразу записываем туда не только идексы на связанные таблицы, но и все данные нужные для отображения ленты сообщений
InstantCMS 10 декабря 2010 в 23:06 +2
все правильно и грамотно излагаете
только, как верно заметил Fuze, мы не можем внедрять решения не работающие хотя бы на 80% шаред-хостингов
по понятным причинам
да и хайлоад у нас пока только в перспективах, движок молодой все же