Как правильно реализовать уведомления о новых сообщениях, чтобы mysql б. д. не упала?

Question

[Игорь Самохин]

Привет!

У меня пока 2 идеи как это сделать, первая ( чувствую, что плохая ):

1) Просто делать Select count(*) from messages where user_id = 1 AND status = NULL

То есть каждый раз пробегать всю таблицу и считать сколько новых сообщений - думаю на 500 человек онлайн б. д. откажет.

2) Сделать отдельную таблицу, где одна запись - это один user_id. И в этой записи будет поле mess_ids - где через запятую будут перечислены id непрочитанных сообщений. Таблица будет обновляться при отправке другим пользователем сообщения текущему пользователю. Всяко быстрее чем select count(*)

Может подскажите другие решения на php и mysql. Как организовать логику таблиц и логику работы? Без сторонних решений вроде reactPHP и т. п.?

Answer 1

[Евгений Неверов]

Оба решения так себе.

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

В таблице сообщений сделайте поле is_read (true/false), на которое повесьте индекс, так вы сможете определить, какие сообщения в данный момент не прочитаны.

Держать список ID через запятую в одном поле неэффективно и бессмысленно.

Comments

[Игорь Самохин]

только у меня не таблица с сообщениями,а таблица безопасных сделок. Там уведомления в обе стороны, то есть есть поля:

from_user_id
to_user_id
is_read_from_id ENUM('0','1')
is_read_to_id ENUM('0','1')

тут какие составные ключи должны быть?

[Евгений Неверов]

ну, также: на каждый is_read по своему ключу. и не делайте ENUM, лучше сделайте TINYINT.

[Дмитрий Энтелис]

@deMone ключ на is_read должен быть составным с соответствующим user, иначе бессмысленно

Answer 2

[Rpsl]

второе решение весьма стандартно, только лучше не в отдельную таблицу это класть, а в любое key=>value хранилище, например memcaced или redis, где ключ это id пользователя.

При изменение данных просто сбрасываете ключ.

Comments

[Makaveli]

По-моему, самый годный вариант. Значение удалять из memcached по ключу когда счётчик обновляется, чтобы при следующем запросе значение бралось из БД.

[Дмитрий Энтелис]

Вообще не годный вариант. У них задача - получить уведомление. Узким местом будет не sql а php. От перехода на redis оно будет работать побыстрее конечно, но все равно ляжет уже при паре сотен онлайн. Правильно решение - ниже отдельным постом.

[Rpsl]

@saratovdae я даже не представляю что должно быть за железо или какими ногами нужно программировать чтобы php ложился на 200 онлайн.

[Дмитрий Энтелис]

@Rpsl судя по формулировкам вопроса - вряд ли там выделенный сервер. Какой нибудь shared хостинг может упасть и на меньшем.
Ну и рассуждая формально - в худшем случае 200 онлайн это 200 конкурентных. Дальше продолжать?

[Rpsl]

@saratovdae продолжайте!

[Игорь Самохин]

@saratovdae что значит 200 конкурентных? Запросов? Забьется кеш mysql?

[Дмитрий Энтелис]

@grigor007 если это постоянно изменяемые данные - кеш не спасет. конкурентные запросы к php: ab -c 200

Answer 3

[Дмитрий Гукетлев]

Select count(*) from messages where user_id = 1 AND status = NULL

Вполне норм решение. Просто нужен индекс по user_id + status. Когда начнет отказывать база тогда и подумаете об оптимизации (memcached, вот это все). Преждевременная оптимизация - корень большинства бед.

Answer 4

[Дмитрий Энтелис]

Тут почему то все скатились на обсуждения счетчиков.
Счетчик действительно хорошо делать в redis, особенно если этот счетчик часто меняется.
Хотя если это счетчик просто не прочитанных сообщений у пользователя - вполне можно его хранить и как написано у автора по 2му варианту.

Но заголовок вопроса автора как я понимаю в другом - "как реализовать уведомление".
Видимо подразумевается что кучи веб страничек будут слать пачки ajax запросов.

При такой схеме узким местом будет не sql, а очень даже php.
Правильное решение - поднять любой comet сервер.
Можно на php (медленно и криво), можно на node.js + socket.io (быстро и хорошо)

Comments

[Игорь Самохин]

Извиняюсь, что ввел в заблуждение, мне нужен ответ, как это сделать в mysql, чтобы при 500 запросах на select количества новых сообщений б. д. не упала.

Причем надо еще помечать какие сообщения прочитаны, а какие нет

[Дмитрий Энтелис]

Невнимательно прочитал Ваш второй вариант, он плохой)

Лучше сделать так:

1) Таблица messages где user_id - получатель, status - статус прочел ли он это сообщение. Ключ по user_id.
2) Таблица для хранения количества: user_id, unread_message_cnt.
user_id primary

[Дмитрий Энтелис]

Логика работы с этими таблицами думаю очевидна

Answer 5

[Илья Лесных]

Сделать "счетчик" (хоть в таблице пользователя, хоть в k-v хранилище), который инкрементить при поступлении нового сообщения и декрементить при прочтении оного.

И в этой записи будет поле mess_ids - где через запятую будут перечислены id непрочитанных сообщений.

не надо так

Answer 6

[FacedSID]

Согласен с комментарием выше. nodeJS + socket.io + возможно redis (если нужно что-то посложнее).
Очень полезно, если пользователь пользуется несколькими клиентами. Я как то пытался изобрести велосипед на mysql + ajax запросы, но возникала куча сложностей с получением актуальных обновлений. Дак вот nodeJS + socket.io меня спасли! Времени на разработку потратил раза в два меньше, чем на изобретение велосипеда с mysql.

Comments

[Игорь Самохин]

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

В таблице сообщений сделайте поле is_read (true/false), на которое повесьте индекс, так вы сможете определить, какие сообщения в данный момент не прочитаны."

это решение мне кажется эффективным. Разве нет?

[FacedSID]

Проблема в том, что у меня один пользователь сидит под разными сессиями с разных клиентов: компьютеры, телефоны.

Нужно, что бы на всех клиентах данные обновлялись тут же. Если использовать ваш подход, то при обновлении данных на одном клиенте, остальные эти обновления не получат, т.к. он успел получить их быстрее чем остальные. Делал привязки к разным сессиям, но в итоге объем данных в mysql для пользователей разрастался до огромнейших размеров, т.к. обновления происходят очень часто в итоге выборки обновлений превратились в задержки в работе интерфейса, а для сервиса очень важна скорость.

[Игорь Самохин]

@FacedSID а как именно ведет себя связка nodeJS + socket.io в вашей задаче? Я имею ввиду, как решается проблема обновлений сразу для нескольких устройств?

[FacedSID]

Когда пользователь авторизуется, то под него создается сессия + уникальный ключ и он подписывается на получение уведомлений от nodeJS под этим уникальным ключом. Когда происходит какое то изменение в данных, то пользователь, который сделал это уведомление отправляет запрос в nodeJS, nodeJS уже анализирует кому отправить уведомление об изменениях (имеется ввиду не разные клиенты, а именно пользователей), потом по каждому пользователю находит все активные сессии и отправляет на каждого клиента уведомление о событии. Клиент получает уведомление и отрабатывает определенный js код, которые выводит нотификации или перестраивает интерфейс.