Каков наилучший подход к запросам к базе данных, которые возвращают результаты, аналогичные ленте твитов Twitter от людей, за которыми вы следите?

Мой веб-сайт позволяет пользователям отправлять сообщения и подписываться на сообщения других людей. На главной странице сайта отображаются самые последние сообщения людей, на которых подписан пользователь. Количество людей, на которых может подписаться пользователь, не ограничено. Некоторые пользователи подписаны на тысячи других пользователей. Некоторые пользователи сделали более 15 000 сообщений.

Таблица базы данных сообщений организована следующим образом (для ясности несколько несущественных столбцов опущены):

id
author_id
post_content
date_added

У меня есть 2 рабочих решения, но я не уверен, что это лучший подход:

Решение 1:

  1. Получить список author_id, за которыми следит пользователь.

  2. Запросите таблицу для сообщений, которые соответствуют любому из author_id:

     SELECT id FROM posts
 WHERE author_id IN (12, 34, 56, 78, 90, ...)
 ORDER BY date_time DESC
 LIMIT 100;
  3. Кэшировать результат на N минут.

Это работает, но сканирует, когда пользователи подписаны на тысячи людей.

Решение 2:

  1. Получить список author_id, за которыми следит пользователь.
  2. Для каждого идентификатора автора получите кешированную ленту только их идентификаторов сообщений. (Этот фид используется на странице автора)
  3. Объедините все идентификаторы сообщений от всех этих авторов в один гигантский массив и отсортируйте их в порядке убывания (что работает, потому что каждое сообщение получает автоматически увеличивающийся идентификатор).
  4. Кэшировать и возвращать последние 100 идентификаторов сообщений;

Это работает, но иногда происходит сканирование, когда тысячи пользовательских фидов возвращаются и объединяются в массив из более чем 100 000 элементов. Это кажется излишним, когда все, о чем я забочусь, это самые последние 100 элементов. Кроме того, не все пользовательские каналы будут в кеше. Некоторые старые пользователи могут больше не использовать сайт, но за ними по-прежнему следуют новые пользователи, в результате чего фид старого пользователя будет заново запрашиваться (и затем кэшироваться).

Это оптимальные решения? Если нет, то что?


mysql database query-optimization database-performance
person Vidbot    schedule 10.03.2012    source источник

Ответы (2)


arrow_upward
0
arrow_downward

А как насчет (не проверено, но вы поняли):

SELECT id FROM posts
CROSS JOIN followers ON posts.author_id = followers.user_id
WHERE followers.followed_by_user_id = INSERT_USER_ID_HERE
ORDER BY posts.date_time DESC
LIMIT 100;

or

SELECT id FROM posts
WHERE author_id IN (
  SELECT user_id FROM followers 
  WHERE followed_by_user_id = INSERT_USER_ID_HERE
)
ORDER BY date_time DESC
LIMIT 100;

примечание: для пояснения, таблица followers содержит два столбца user_id и followed_by_user_id. Если строка содержит значение (user_id:7, followed_by_user_id:42), это означает, что пользователь 42 следует за пользователем 7.

person CAFxX    schedule 10.03.2012

arrow_upward
0
arrow_downward

Оптимизация вашего решения 2, позволяющая избежать объединения и сортировки всех идентификаторов сообщений:

  1. Создайте массив для хранения результата и скопируйте содержимое первых 100 идентификаторов сообщений первого автора и отсортируйте по id.
  2. For each author:
    1. Check if the minimum id in the result array is greater than the maximum id of the author's posts.
    2. Если да, то пропустите этого автора, так как все его сообщения старше, чем сообщения в вашем массиве результатов.
    3. Если нет, объедините 100 лучших сообщений автора с массивом результатов, отсортируйте и сохраните только 100 лучших сообщений.

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

Для решения 1 упорядочивание по id будет немного быстрее, чем упорядочивание по date_time.

person nikhil500    schedule 10.03.2012