Выгрузка всех новостей с комментариями при большом объеме данных

21. Выгрузить все новости с комментариями при большом объёме данных

Условие задачи:
Даны две таблицы:

news — новости;
comments — комментарии к новостям.

Необходимо выгрузить все новости вместе со всеми их комментариями при большом объёме данных, не загружая весь результат целиком в память.

Новости без комментариев также должны попадать в результат.

Код:

news

id INT AUTO_INCREMENT PRIMARY KEY

comments

id      INT AUTO_INCREMENT PRIMARY KEY
news_id INT REFERENCES news(id)

Спойлеры к решению

Подсказки

💡 Используй LEFT JOIN, чтобы сохранить новости без комментариев.
💡 При большом объёме данных лучше читать новости порциями.
💡 LIMIT/OFFSET на результате после JOIN может разбить комментарии одной новости между страницами.
💡 Лучше сначала выбрать очередную порцию новостей, а затем присоединить к ним все комментарии.
💡 Для больших таблиц предпочтительна keyset pagination по id, а не большой OFFSET.

Решение

Для первой порции можно выбрать ограниченное количество новостей и только после этого присоединить комментарии:

WITH news_batch AS (
    SELECT *
    FROM news
    ORDER BY id
    LIMIT :batch_size
)
SELECT
    n.*,
    c.*
FROM news_batch n
LEFT JOIN comments c
    ON c.news_id = n.id
ORDER BY n.id, c.id;

Для следующих порций лучше использовать последний обработанный id:

WITH news_batch AS (
    SELECT *
    FROM news
    WHERE id > :last_news_id
    ORDER BY id
    LIMIT :batch_size
)
SELECT
    n.*,
    c.*
FROM news_batch n
LEFT JOIN comments c
    ON c.news_id = n.id
ORDER BY n.id, c.id;

Например:

batch_size   = 1000
last_news_id = 15000

следующая порция будет содержать до 1000 новостей с id > 15000 и все комментарии к этим новостям.

Важно ограничивать количество именно новостей до выполнения JOIN.

Такой вариант:

SELECT n.*, c.*
FROM news n
LEFT JOIN comments c
    ON c.news_id = n.id
ORDER BY n.id
LIMIT 1000 OFFSET 1000;

ограничивает количество строк уже после соединения. Если у одной новости много комментариев, её данные могут оказаться разделены между несколькими страницами.

Для производительности также полезен индекс:

CREATE INDEX idx_comments_news_id
    ON comments(news_id);

При последовательной обработке всех порций можно выгрузить весь набор данных, не удерживая все новости и комментарии одновременно в памяти.