COUNT (*) или MAX (id) - что быстрее?

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

Моя таблица БД проста:

ID - Auto Increment, Primary Key (Bigint)
Sender - Varchar (32) // Foreign Key to UserID hash from Users DB Table
Recipient - Varchar (32) // Foreign Key to UserID hash from Users DB Table
Message - Varchar (256) //UTF8 BIN

Я рассматриваю возможность создания API, который будет оценивать наличие новых сообщений для данного пользователя. Я думаю об использовании одного из этих методов:

A) Выберите count(*) сообщений, для которых отправителем или получателем является я.
(если этот номер> предыдущий номер, у меня новое сообщение)

Б) Выберите max(ID) сообщений, где отправителем или получателем является я.
(если max (ID)> чем предыдущий номер, у меня новое сообщение)

У меня вопрос: можно ли как-то рассчитать, какой метод потребляет меньше ресурсов сервера? Или есть какая-то статья? Может быть, другой метод, который я не упомянул?

Ответ 1

В MySQL InnoDB SELECT COUNT(*) WHERE secondary_index =? это дорогостоящая операция, и когда у пользователя много сообщений, этот запрос может занять много времени. Даже при использовании индекса движок все равно должен считать все соответствующие записи. Производительность будет ухудшаться с ростом общего количества сообщений.

С другой стороны, SELECT MAX(id) WHERE secondary_index =? может доставить самый высокий идентификатор в этом индексе очень эффективно, выполнив так называемое свободное сканирование индекса. Производительность останется практически неизменной.

Если вы хотите понять, почему, подумайте о поиске структуры данных B + Tree, которую InnoDB использует для организации своих данных.

Я предлагаю вам использовать SELECT MAX(id), если требуется только проверить, есть ли новые сообщения (а не их количество).

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

Ответ 2

Чтобы получить информацию о том, что у кого-то есть новые сообщения - делайте именно это. Обновите поле в таблице users (я предполагаю, что имя), когда новое сообщение записано в системе. У вас есть идентификатор получателя, это все, что вам нужно. Вы можете создать триггер after insert (допущение: там таблица users2messages), который обновляет таблицу пользователей с логическим флагом, указывающим на наличие сообщения.

Этот подход намного быстрее, чем подсчет индексов, будь то первичный или вторичный индекс. Когда пользователь выполняет действие, вы можете обновить таблицу users с has_messages = 0, когда приходит новое сообщение - вы обновляете таблицу с has_messages = 1. Он простой, он работает, он масштабируется, а использование триггеров для его обслуживания делает его простым и плавным. Я уверен, что найдутся недоговорки, которым не нравятся триггеры, вы можете сделать это вручную, связав пользователя с новым сообщением.

Ответ 3

Если вам нужно узнать количество новых сообщений, используйте Select count(*) from Messages where user_id in (sender, recipient) and id > last_seen_id будет вашим лучшим вариантом.

Я фанат использования exists там, где это возможно, поэтому, чтобы определить, ЕСЛИ есть новые сообщения, мой запрос был бы Select exists(Select 1 from Messages where user_id in (sender, recipient) and id > last_seen_id). Преимущество существует в том, что как только он находит 1 запись, он возвращает true.

Изменение: чтобы избежать путаницы при чтении этого ответа, оба этих запроса также будут включать проверку для other_user_id in (sender, recipient), чтобы возвращать сообщения только между двумя конкретными пользователями.

Ответ 4

@FeHora Вы говорите о неиспользовании ключей для экономии места в БД. Таблицы проектируют трату больше места в БД.

ID - Auto Increment, Primary Key (Bigint)

Действительно ли bigint необходим? Предположим, сообщение отправляется каждую секунду. int unsigned достаточно на 126 лет. И если у вас действительно так много сообщений, ключ обязателен.

Sender - Varchar (32) // Foreign Key to UserID hash from Users DB Table
Recipient - Varchar (32) // Foreign Key to UserID hash from Users DB Table

Почему бы не использовать UserID (обычно int unsigned).

Тогда я бы добавил видимые флаги. Кстати, вы можете добавить для всех поданных атрибут not null.

seen tinyint not NULL.

Не в последнюю очередь я рекомендую вариант @Mjh: определить флаг has_messages, или new_messages, или оба в записи пользователя. Обычно пользовательская запись загружается, поэтому она НЕ является дополнительным запросом к базе данных.