Как ограничить выборку и уменьшить нагрузку на базу при большом объеме данных

Что проверяют

Знаете ли вы приёмы помимо LIMIT и понимаете ли, чем плоха пагинация со смещением.

Как отвечать

По направлениям.

Ограничить объём выборки:

• пагинация по ключу вместо смещения. OFFSET 100000 заставляет базу прочитать и выбросить сто тысяч строк, и чем дальше страница, тем медленнее. Правильный вариант это условие по последнему увиденному ключу:

select * from jobs where id > :last_id order by id limit 50;

• ограничение сверху на размер страницы в API, иначе клиент однажды запросит миллион строк;
• фильтрация и агрегация на стороне базы, а не в приложении. Тянуть строки, чтобы посчитать сумму в коде, это худшее, что можно сделать.

Уменьшить ширину строки:

• выбирать только нужные колонки, а не всю сущность. В JPA это проекции в объект передачи данных, а не загрузка полного объекта;
• не тянуть большие текстовые поля, если они не нужны для списка.

Убрать лишние запросы:

• проблема N+1: сто сущностей плюс сто запросов за связями. Лечится соединением в запросе, графом сущностей или пакетной загрузкой. Проверяется счётчиком запросов в тестах;
• кэш для справочных данных, которые меняются редко.

Обрабатывать много данных, не поднимая всё в память:

• потоковая обработка с курсором и размером выборки на драйвере, а не список целиком;
• пачки при массовых операциях: обработать несколько тысяч строк, зафиксировать, повторить. Один запрос на миллион строк держит длинную транзакцию и раздувает таблицу.

Помочь планировщику: индексы под условия и сортировку, актуальная статистика, проверка плана выполнения перед выводами.

Отдельно про счётчики: COUNT(*) на большой таблице дорог. Для показа общего числа страниц часто хватает приблизительной оценки или отказа от точного числа вовсе.

Где спотыкаются

Используют смещение для глубокой пагинации.

Загружают сущности целиком там, где нужны два поля.

Не замечают N+1 и оптимизируют не то.

Делают массовое обновление одним запросом на всю таблицу.