Как реализовать пагинацию данных на уровне SQL и в приложении?

Паганация — это разбиение большого набора результатов на отдельные страницы.

1. Пагинация на уровне SQL

Используются операторы LIMIT и OFFSET (или их аналоги, например, FETCH NEXT ... OFFSET в стандарте SQL).

-- Получить 10 записей, начиная с 21-й (т.е. страница 3 при размере страницы 10)
SELECT * FROM products ORDER BY id LIMIT 10 OFFSET 20;

Проблемы OFFSET:

• Низкая производительность на глубоких страницах: СУБД должна пропустить (OFFSET) N записей, что требует их чтения и сортировки.
• Консистентность при изменении данных: Если данные добавляются/удаляются между запросами, результаты могут "поплыть".

Решение — Keyset Pagination (пагинация по ключу):

-- Первая страница
SELECT * FROM products WHERE id > 0 ORDER BY id LIMIT 10;
-- Следующая страница: используем последний ID с предыдущей страницы
SELECT * FROM products WHERE id > :last_seen_id ORDER BY id LIMIT 10;

Этот метод эффективен, так как использует индекс для поиска и не требует пропуска записей.

2. Пагинация в приложении (Java/Spring)

Spring Data JPA предоставляет абстракцию Pageable и Page:

public interface ProductRepository extends JpaRepository<Product, Long> {
    Page<Product> findAllByCategory(String category, Pageable pageable);
}

// Использование в сервисе
Pageable pageable = PageRequest.of(2, 10, Sort.by("id")); // страница 3, размер 10
Page<Product> page = productRepository.findAllByCategory("Electronics", pageable);

List<Product> content = page.getContent(); // Данные страницы
int totalPages = page.getTotalPages();     // Общее число страниц
long totalElements = page.getTotalElements(); // Всего элементов

Практические аспекты:

• Выбор между OFFSET и Keyset Pagination в зависимости от глубины пагинации и требований к консистентности.
• Возврат клиенту не только данных страницы, но и метаданных (общее количество элементов, признак наличия следующей страницы).
• Для очень больших наборов данных общее количество (COUNT(*)) может быть дорогой операцией, иногда его опускают.