Является ли 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