MySQL сгруппировать максимальные проблемы производительности на миллионной строке таблицы

Question

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

Этот простой запрос находит самую последнюю запись в каждой теме, чтобы пользователь мог определить, есть ли у него какие-либо ответы с тех пор (путем последующего сравнения topic_time)

SELECT p.*, MAX(post_time) as post_time FROM forum_posts AS p   
WHERE p.poster_id = '1' AND p.post_status = '0' 
GROUP BY p.topic_id  
ORDER BY post_time DESC 
LIMIT 50

простой плоский стол выглядит примерно так

post_id | poster_id | topic_id | post_status | post_time | post_text

Однако его производительность падает, когда миллион сообщений, а пользователь сам имеет десятки тысяч сообщений. MySQL больше не может сортировать таблицу в памяти или слишком много строк для сканирования. При использовании в реальном мире это может занять до 3 секунд, что недопустимо в imho, потому что в это время он запускает процессор и замедляет работу всех остальных.

Конечно, я могу сделать любую комбинацию индекса, но MySQL, похоже, больше всего любит использовать комбинацию

poster_id + post_time 

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

Вместо этого я попытался написать эквивалентный JOIN, чтобы я мог использовать более одного индекса, но у меня возникли проблемы с тем фактом, что каждая сторона должна фильтроваться по post_status и poster.

Я думал, что было бы быстрее, по крайней мере для первых нескольких страниц, если бы mysql мог сделать ПЕРВУЮ сортировку данных по индексу по post_time, а затем начать выбирать отдельный topic_id для пользователя в порядке убывания. Я предполагаю, что для этого потребуется подзапрос, и я не уверен, что подзапрос с 50 000 результатов будет лучше, но ему все еще нужна временная таблица.

Конечно, фундаментальным решением было бы дополнить дизайн ядра так, чтобы была еще одна таблица, в которой просто хранится максимальное значение post_time для каждого пользователя в каждой теме, но это слишком большое изменение, если не может быть найдено другое решение.

Спасибо за любые предложения!

---

добавление примера реального мира и ОБЪЯСНЕНИЕ:

медленный журнал

# Query_time: 2.751334  Lock_time: 0.000056 Rows_sent: 40  Rows_examined: 48286
SELECT   p.*, MAX(post_time) as post_time FROM forum_posts AS p   WHERE p.poster_id = '2' AND p.post_status = '0' GROUP BY p.topic_id  ORDER BY post_time DESC LIMIT 7000, 40;

объяснять

select_type     table   type    possible_keys   key     key_len     ref     rows    Extra
SIMPLE          p   ref poster_time poster_time 4   const   27072   Using where; Using temporary; Using filesort

Answer 1

Во-первых, исправьте ваш запрос, чтобы получить определенные результаты:

SELECT p.topic_id, 
       MAX(post_time) as post_time 
FROM forum_posts AS p   
WHERE p.poster_id = '1' AND p.post_status = '0' 
GROUP BY p.topic_id  
ORDER BY post_time DESC 
  LIMIT 50 ;

Затем попробуйте после добавления индекса на (post_status, poster_id, topic_id, post_time),