1. Приветствуем Вас на неофициальном форуме технической поддержки XenForo на русском языке. XenForo - новый и перспективный форумный движок от бывших создателей vBulletin.

Пересчет сообщений пользователей на форуме: удачные и неудачные варианты

Тема в разделе "Статьи по XenForo Framework", создана пользователем FractalizeR, 14 мар 2015.

Загрузка
  1. FractalizeR

    FractalizeR XenForo Addicted Команда форума

    Регистрация:
    27.09.10
    Сообщения:
    1.085
    Симпатии:
    822
    Версия XF:
    1.3.2
    Столкнулся с задачей пересчета сообщений у пользователей на форуме и решил поделиться с сообществом исследованиями на тему ее наиболее эффективного решения.

    Вариант №1 (аддон sonnbRebuildUserPostCount 1.0.0 20120725).

    Этот аддон пересчитывает сообщения пользователей в цикле, для каждого пользователя выполняя запрос:

    Код:
      SELECT COUNT(*)
      FROM `xf_post` AS post
      INNER JOIN `xf_thread` AS thread
      ON (post.thread_id = thread.thread_id)
      WHERE post.user_id = $userId AND
      thread.node_id IN (" . XenForo_Application::getDb()->quote(array_keys($nodes)) . ")

    Неудачные моменты:
    • Долго работает на больших форумах (на нашем сейчас 700.000+ пользователей). Выполняет количество запросов превышающее количество пользователей на форуме, что достаточно сильно нагружает сервер (нужно ведь дождаться, пока выполнятся эти 700 тысяч запросов)
    Вариант №2 от patrikq

    Выполнить запрос

    Код:
    UPDATE xf_user AS user
    SET message_count = (
      SELECT COUNT(*)
      FROM xf_post AS post
      LEFT JOIN xf_thread AS thread ON (thread.thread_id = post.thread_id)
      WHERE post.user_id = user.user_id
      AND post.message_state = 'visible' AND thread.discussion_state = 'visible'
      GROUP BY post.user_id
    );
    Неудачные моменты:

    • Неэффективен, поскольку EXPLAIN показывает, что для выполнения такого запроса будут выполняться два подзапроса. Сильно нагружает базу.
    • Не учитывает, что некоторые форумы могут быть настроены так, чтобы не учитывать сообщения пользователей в этих разделах (встроенная возможность движка). Нужно добавить еще JOIN с таблицей xf_forum и условие на count_messages в WHERE.


    Поиск альтернативы.

    В XenForo существует таблица xf_thread_user_post, в которой закешировано количество сообщений пользователя в каждом треде форума. Запросы по этой таблице будут выполняться быстрее, чем запросы к xf_post поскольку она гораздо меньше по объему (если таблица в память не помещается, InnoDB читает информацию с диска страницами по 16KB даже в том случае, если из всей страницы нужен только 1 байт).


    Вариант №3 (ForumHouse):

    Выполнить запрос:

    Код:
    UPDATE xf_user U SET message_count = (
      SELECT
      SUM(post_count) AS user_post_count
      FROM xf_thread_user_post TUP
      INNER JOIN xf_thread T USING (thread_id)
      INNER JOIN xf_forum F USING (node_id)
      WHERE count_messages AND TUP.user_id = U.user_id
      )

    Неудачные моменты:

    • EXPLAIN показывает, что будут выполняться три SUBQUERIES, что недостаточно эффективно.

    Вариант №4 (ForumHouse):

    Выполнить запрос:

    Код:
    UPDATE xf_user U
      INNER JOIN (
                   SELECT
                     SUM(post_count) AS user_post_count,
                     TUP.user_id
                   FROM xf_thread_user_post TUP
                     INNER JOIN xf_thread T USING (thread_id)
                     INNER JOIN xf_forum F USING (node_id)
                   WHERE count_messages AND discussion_state = 'visible'
                   GROUP BY TUP.user_id) AS PC ON U.user_id = PC.user_id
    SET message_count = user_post_count
    На нащих объемах (700.000+ пользователей, 200.000+ тем и 8.700.000+ сообщений) отрабатывает за 3 секунды. Если посмотреть его EXPLAIN, будет видно, что SUBQUERIES нет. Есть только несколько DERIVED таблиц.

    Разница по времени выполнения между вариантом №3 и 4 примерно 10 раз. Так что остановились пока на нем ;)
     
    Последнее редактирование: 15 мар 2015
    Mirovinger, akinak, Gatses и ещё 1-му нравится это.
  2. akinak

    akinak Местный

    Регистрация:
    12.02.13
    Сообщения:
    259
    Симпатии:
    241
    Версия XF:
    1.1.3
    Третий и четвертый варианты считают сообщения в мягко удаленных темах. А 2-й не считает (но считает в разделах, где считать не надо).

    Код:
    SELECT
      SUM(post_count) AS user_post_count,
      TUP.user_id
    FROM xf_thread_user_post TUP
      INNER JOIN xf_thread T USING (thread_id)
      INNER JOIN xf_forum F USING (node_id)
    WHERE count_messages
          AND T.discussion_state = 'visible'
    GROUP BY TUP.user_id
    
    выберет не удаленные темы (и не на модерации). Но еще надо и xf_post присоеденять, так как сообщение может быть удаленное или на модерации. А по discussion_state индекса нет, так что запрос врядли будет быстрый. Хотя индекс можно и добавить.
     
    Последнее редактирование модератором: 15 мар 2015
    GERAsimov нравится это.
  3. FractalizeR

    FractalizeR XenForo Addicted Команда форума

    Регистрация:
    27.09.10
    Сообщения:
    1.085
    Симпатии:
    822
    Версия XF:
    1.3.2
    Спасибо за ваше замечание. Действительно, мой запрос считал сообщения и в удаленных темах.

    Полагаю, что не нужно. XenForo поддерживает счетчик в таблице только по видимым сообщениям:
    \XenForo_DataWriter_DiscussionMessage_Post::_messagePostSave:
    PHP:
            if ($this->isChanged('message_state'))
            {
                if (
    $this->get('message_state') == 'visible')
                {
                    
    $this->_getThreadModel()->modifyThreadUserPostCount($this->get('thread_id'), $this->get('user_id'), 1);
                }
                else if (
    $this->isUpdate() && $this->getExisting('message_state') == 'visible')
                {
                    
    $this->_getThreadModel()->modifyThreadUserPostCount($this->get('thread_id'), $this->get('user_id'), -1);
                }
            }
    На самом деле отсутствие индекса в нашем случае никак не ухудшает ситуацию. Мы ведь и так присоединяли все темы. А сейчас добавление еще одного WHERE просто работает как фильтр. Я только что выполнил измененный запрос на нашем форуме. Время исполнения осталось прежним. Пост обновил.
     

Поделиться этой страницей