Как на PHP реализовать постраничную навигацию (pagination) для списка из 10 миллионов строк без потери производительности?
Реализация постраничной навигации для таблиц с десятками миллионов строк — одна из классических задач, где стандартный подход с `LIMIT offset, count` быстро становится узким местом. Разберём проблемы и эффективные решения.
## Почему OFFSET не работает на больших данных
Классический запрос `SELECT * FROM table LIMIT 100 OFFSET 9999900` заставляет базу данных прочитать и отбросить почти 10 миллионов строк, прежде чем вернуть нужные 100. Время выполнения растёт линейно с номером страницы, и на 500-й странице пользователь будет ждать секунды.
## Метод 1: Keyset Pagination (Cursor-based)
Это наиболее производительный подход. Вместо смещения используется значение последнего элемента предыдущей страницы:
php
// Первая страница
$sql = «SELECT id, name FROM users ORDER BY id ASC LIMIT 100»;
// Следующие страницы — передаём last_id из предыдущей
$lastId = (int) $_GET[‘last_id’];
$sql = «SELECT id, name FROM users WHERE id > ? ORDER BY id ASC LIMIT 100″;
$stmt = $pdo->prepare($sql);
$stmt->execute([$lastId]);
$rows = $stmt->fetchAll();
$nextLastId = end($rows)[‘id’];
Запрос использует индекс по `id` и работает за O(log N) независимо от глубины страницы. Ограничение: нельзя перейти на произвольную страницу по номеру.
## Метод 2: Отложенное соединение (Deferred Join)
Если нужен классический OFFSET, но с приемлемой скоростью:
php
$page = max(1, (int) $_GET[‘page’]);
$perPage = 100;
$offset = ($page — 1) * $perPage;
$sql = »
SELECT u.* FROM users u
INNER JOIN (
SELECT id FROM users ORDER BY id LIMIT ? OFFSET ?
) AS tmp ON u.id = tmp.id
«;
$stmt = $pdo->prepare($sql);
$stmt->execute([$perPage, $offset]);
Внутренний подзапрос работает только с индексом (covering index), не загружая все поля строки. Это в 3–10 раз быстрее наивного OFFSET.
## Метод 3: Кэширование счётчика и диапазонов
Запрос `SELECT COUNT(*)` на 10 млн строк тоже медленный. Кэшируйте его:
php
$totalCount = $cache->get(‘users_total_count’);
if (!$totalCount) {
$totalCount = $pdo->query(‘SELECT COUNT(*) FROM users’)->fetchColumn();
$cache->set(‘users_total_count’, $totalCount, 300); // 5 минут
}
$totalPages = ceil($totalCount / $perPage);
## Метод 4: Индексирование и партиционирование
— Убедитесь, что столбец сортировки проиндексирован: `CREATE INDEX idx_created ON users(created_at, id)`.
— Для составной сортировки используйте составные индексы.
— Рассмотрите партиционирование таблицы по дате или диапазону ID в MySQL/PostgreSQL.
## Метод 5: Elasticsearch или Redis для поиска
Если нужна фильтрация и поиск по тексту — вынесите данные в Elasticsearch. PHP-клиент позволяет делать пагинацию через `search_after`, что аналогично keyset-подходу и работает на миллиардах документов.
## Рекомендации по выбору метода
| Сценарий | Метод |
|—|—|
| Бесконечная прокрутка / API | Keyset / Cursor |
| Классические страницы с номерами | Deferred Join + кэш COUNT |
| Полнотекстовый поиск | Elasticsearch |
| Аналитика | ClickHouse / партиционирование |
## Итог
Для PHP-приложений с большими таблицами оптимальная стратегия: keyset pagination для последовательной навигации, deferred join для классических номеров страниц, обязательное кэширование COUNT и правильные индексы. Никогда не используйте голый OFFSET на глубоких страницах в продакшне.