Оптимизация запросов типа "Последние новости"

Проблемная область

Представьте, что у вас есть «новостные статьи» (строки в таблице) и вам нужна веб-страница, отображающая последние десять статей по определенной теме.

Варианты «темы»:

• Категория
• Тег
• Источник (новостной статьи)
• Производитель (товара для продажи)
• Тикер (финансовая акция)

Варианты «новостной статьи»

• Товар для продажи
• Комментарий к блогу
• Тема блога

Варианты «последние»

• Дата публикации (unix_timestamp)
• Наибольшая популярность (сохранить счет)
• Наибольшее количество пересылок (сохранить счет)
• Ручная ранжировка (1..10 — «топ-десять»)

Варианты «10» — в этом обсуждении нет ничего святого в числе «10».

Проблемы производительности

В настоящее время у вас есть таблица (или столбец), которая связывает тему со статьей. Запрос SELECT для поиска последних 10 статей усложнился, и производительность низкая. Вы сосредоточились на том, какой индекс добавить, но ничего не работает.

• Если для каждой статьи существует несколько тем, вам нужна таблица «многие ко многим».
• У вас есть флаг «is_deleted», который необходимо отфильтровать.
• Вы хотите «странировать» список (десять статей на страницу, для необходимого количества страниц).

Решение

Сначала я предоставлю вам решение, а затем объясню, почему оно хорошо работает.

• Новая таблица, скажем, Lists.
• Lists содержит ровно 3 столбца: тема, article_id, последовательность
• Lists содержит ровно 2 индекса: PRIMARY KEY(тема, последовательность, article_id), INDEX(article_id)
• В Lists находятся только просматриваемые статьи. (Это позволяет избежать фильтрации по «is_deleted» и т. д.)
• Lists использует InnoDB. (Это обеспечивает «кластеризацию».)
• «Последовательность» обычно является датой статьи, но может быть другим порядком.
• «Тема» вероятно, должна быть нормализована, но это некритично для этого обсуждения.
• «article_id» — ссылка на объемную строку в другой таблице(ах), которая предоставляет все подробности об статье.

Запросы

Найти последние 10 статей по теме:

SELECT  a.*
    FROM  Articles a
    JOIN  Lists s ON s.article_id = a.article_id
    WHERE  s.topic = ?
    ORDER BY  s.sequence DESC
    LIMIT  10;

У вас не должно быть никакого условия WHERE, затрагивающего столбцы в таблице Articles.

Когда вы помечаете статью для удаления; вы должны удалить её из Lists:

DELETE  FROM  Lists
    WHERE  article_id = ?;

Я делаю акцент на «должны», потому что флаги и другие фильтры часто являются источником проблем с производительностью.

Почему это работает

К этому моменту вы, возможно, уже обнаружили, почему это работает.

Основная цель — минимизировать обращения к диску. Давайте перечислим, сколько обращений к диску требуется. При поиске последних статей с «обычным» кодом, вы, вероятно, обнаружите, что он выполняет значительные сканирования таблицы Articles, не сумев быстро найти 10 строк, которые вам нужны. С этой конструкцией необходимо только одно дополнительное обращение к диску:

• 1 обращение к диску: 10 смежных, узких строк в Lists — вероятно, в одном «блоке».
• 10 обращений к диску: 10 статей. (Эти обращения неизбежны, но могут быть кэшированы.) PRIMARY KEY и использование InnoDB делают их довольно эффективными.

Хорошо, вы платите за это, удаляя вещи, которых следует избегать.

• 1 обращение к диску: INDEX(article_id) — нахождение нескольких id
• Несколько дополнительных обращений к диску для удаления строк из Lists. Это небольшая плата — и вы не платите её, пока пользователь ожидает отрисовки страницы.

См. также

Рик Джеймс любезно позволил нам использовать эту статью в базе знаний.

Сайт Рика Джеймса содержит другие полезные советы, руководства, оптимизации и советы по отладке.

Исходный источник: http://mysql.rjweb.org/doc.php/lists