Является ли MySQL естественным замедление в этом виде запроса, или я неправильно настроил его?
Следующий запрос предназначен для получения списка непрочитанных сообщений пользователем. Он включает в себя 3 таблицы: recipients
содержит отношение пользователей к идентификаторам сообщений, messages
содержит сами сообщения, а message_readers
содержит список пользователей, которые прочитали эти сообщения.
Запрос надежно занимает 4,9 секунды - это серьезно ухудшает нашу производительность, и это особенно тревожно, так как мы надеемся, что база данных в конечном итоге будет на несколько порядков больше. Конечно, это по сути тяжелый запрос, но набор данных крошечный, и интуитивно кажется, что он должен быть намного быстрее. На сервере достаточно памяти (32gb), что вся база данных должна быть всегда загружена в ОЗУ, и в этом поле ничего не работает.
Таблицы все крошечные:
recipients: 23581
messages: 9679
message_readers: 2685
Сам запрос:
SELECT
m.*
FROM
messages m
INNER JOIN recipients r ON r.message_id = m.id
LEFT JOIN message_readers mr ON mr.message_id = m.id
WHERE
r.id = $user_id
AND (mr.read_by_id IS NULL OR mr.read_by_id <> $user_id)
План объяснения довольно прост:
+----+-------------+-------+--------+-----------------------------------+-----------------------------------+---------+--------------------------------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+-----------------------------------+-----------------------------------+---------+--------------------------------+-------+-------------+
| 1 | SIMPLE | r | ref | index_recipients_on_id | index_recipients_on_id | 768 | const | 11908 | Using where |
| 1 | SIMPLE | m | eq_ref | PRIMARY | PRIMARY | 4 | db.r.message_id | 1 | Using index |
| 1 | SIMPLE | mr | ALL | NULL | NULL | NULL | NULL | 2498 | Using where |
+----+-------------+-------+--------+-----------------------------------+-----------------------------------+---------+--------------------------------+-------+-------------+
Здесь есть индекс на message_readers.read_by_id
, но я думаю, он не может действительно использовать его из-за состояния IS NULL.
Я использую все настройки по умолчанию, кроме следующих:
key_buffer=4G
query_cache_limit = 256M
query_cache_size = 1G
innodb_buffer_pool_size=12G
Спасибо!
Ответы
Ответ 1
Предполагая, что message_readers
является подмножеством recipients
, я рекомендую внести следующие изменения:
-
Избавьтесь от таблицы message_readers
и замените ее флагом в таблице recipients
. Это устранит нулевую проверку и удалит соединение.
-
Возможно, это уже есть, но убедитесь, что ваш кластерный индекс для recipients
меньше id, message_id
, а не message_id, id
, так как почти все поиски сообщений будут основаны на получателях.
Вот результат SELECT:
SELECT
r.whatever,
m.whatever,
-- ...
FROM
recipients r
INNER JOIN messages m ON m.id = r.message_id
WHERE
r.id = $user_id
AND r.read_flag = 'N'
UPDATE
Вот правильная версия вашего запроса с использованием существующей схемы:
SELECT
r.whatever,
m.whatever,
-- ...
FROM
recipients r
INNER JOIN messages m ON r.message_id = m.id
LEFT JOIN message_readers mr ON mr.read_by_id = r.id
AND mr.message_id = m.id
WHERE
r.id = $user_id
AND mr.read_by_id IS NULL
Это предполагает, что ваши кластеризованные индексы будут ожидаться:
recipients: id, message_id
messages: id
message_readers: read_by_id, message_id
Ответ 2
Предполагая, что вам просто нужен счет, как показано в вашем запросе), что произойдет, если вы так измените соединения?
Я использую MSSQL, и это может ускорить его. Я никогда не использовал MySQL, но он должен работать, не так ли?
SELECT count(m.id)
FROM messages m
INNER JOIN recipients r ON r.message_id = m.id AND r.id = $user_id
LEFT JOIN message_readers mr ON mr.message_id = m.id AND (mr.read_by_id IS NULL OR mr.read_by_id <> $user_id)
EDIT: А как насчет безумной идеи? Я думал, что вы можете разделить OR
на два отдельных левых соединения, а затем взять запись, если какой-либо из них что-то возвращает.
SELECT count(m.id)
FROM messages m
LEFT JOIN recipients r ON r.message_id = m.id AND r.id = $user_id
LEFT JOIN message_readers mr ON mr.message_id = m.id AND mr.read_by_id IS NULL
LEFT JOIN message_readers mr2 ON mr2.message_id = m.id AND mr2.read_by_id <> $user_id
WHERE COALESCE(mr.message_id, mr2.message_id) IS NOT NULL
Ответ 3
Вы можете избавиться от состояния IS NULL, когда вы переписываете свой запрос следующим образом:
SELECT
count(m.id)
FROM
messages m
INNER JOIN recipients r ON re.message_id = m.id
WHERE r.id = $user_id
AND NOT EXISTS
(SELECT mr.id
FROM message_readers mr
WHERE mr.message_id = m.id
AND mr.read_by_id = $user_id)
В основном это читается так: получите все messages
для recipient
, где не в message_readers
, и описывает проблему simpeler.
Ответ 4
Какое время запроса для
select distinct message_id
from message_readers
where read_by_id <> $user_id
Примечание: логика "null" должна быть поймана этим, поскольку null не равен чему-либо
Если это быстро, попробуйте следующее:
SELECT count(m.id)
FROM messages m
INNER JOIN recipients r ON r.message_id = m.id
where r.id = $user_id
and m.id in (
select distinct message_id
from message_readers
where read_by_id <> $user_id)
Исходный ответ не помог: Попробуйте включить message_id и id в индекс покрытия получателей и посмотрите, что произойдет.
Ответ 5
Если мне что-то не хватает, вам вообще не нужна таблица сообщений. То, что вам действительно нужно, - это количество идентификаторов сообщений, которые появляются для этого пользователя у получателей, и не отображаются для этого пользователя в сообщениях_процессоров.
Если я прав, вы можете выполнить то, что хотите, с помощью MINUS:
SELECT count(message_id)
FROM (
SELECT r.message_id
FROM recipients r
WHERE r.id = $user_id
MINUS
SELECT mr.message_id
FROM message_readers mr
WHERE mr.read_by_id = $user_id
)
Это полностью исключает объединение. Теперь, если вам действительно нужны данные из таблицы сообщений для вашего производственного запроса, вы можете присоединиться к таблице сообщений в этот подзапрос (или вставить его в предложение IN).
Возможно, что я здесь не нахожусь, так как мой опыт в Oracle-land, но MySQL поддерживает MINUS, поэтому это, вероятно, стоит сделать.
Ответ 6
комментарий
count (m.id) означает count not null values, но m.id никогда не является нулевым, поэтому его дополнительные. хорошо попробуйте с этим
SELECT count(*)
FROM
messages m
INNER JOIN recipients r ON r.message_id = m.id
left join
(
select m.id
messages m
INNER JOIN message_readers mr
ON mr.message_id = m.id
and (mr.read_by_id <> $user_id or mr.read_by_id IS NULL)
)as sub
on sub.id = m.id
WHERE r.id = $user_id
одно сомнение может быть правильным в вашей бизнес-логике, почему все пользователи могут читать входящие сообщения (mr.read_by_is null) и почему сообщение может быть прочитано для других или не используется конкретным получателем (mr.read_by_id < > $user_id)
его пул, я думаю
один лучший подход - это изменение внутреннего в подзапросе существующим. см., что "mr.read_by_id IS NULL" не является обязательным, если mr_read_by_id равно null, поэтому означает, что "mr.read_by_id = $user_id" является ложным "
SELECT count(*)
FROM
messages m
INNER JOIN recipients r ON r.message_id = m.id
left join
(
select m.id
messages m
where not exists(select * from message_readers mr
where mr.message_id = m.id
and mr.read_by_id = $user_id)
)as sub
on sub.id = m.id
WHERE r.id = $user_id