Следующий запрос предназначен для получения списка непрочитанных сообщений пользователем. Он включает 3 таблицы: получатели
содержат отношение пользователей к идентификаторам сообщений, сообщения
содержат сами сообщения, и message_readers
содержит список, какие сообщения пользователи прочитали.
запрос надежно занимает 4,9 секунды - это серьезно снижает нашу производительность и вызывает особое беспокойство, поскольку мы надеемся, что база данных в конечном итоге будет на несколько порядков больше. Конечно, это сложный запрос, но набор данных крошечный, и интуитивно кажется, что он должен быть намного быстрее. У сервера достаточно памяти (32 ГБ), чтобы вся база данных всегда была загружена в ОЗУ, и на компьютере больше ничего не работает.
Все таблицы крошечные:
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
Спасибо!