Как реализуешь пагинацию на стороне базы данных?

Реализация пагинации на стороне базы данных

Пагинация на стороне базы данных — это критически важный подход для оптимизации производительности при работе с большими наборами данных. Вместо загрузки всех записей в память приложения, мы делегируем работу по ограничению выборки самой СУБД. Вот ключевые методы реализации:

1. Использование LIMIT и OFFSET (классический подход)

Наиболее распространённый метод, поддерживаемый практически всеми реляционными БД (PostgreSQL, MySQL, SQLite):

SELECT id, title, created_at FROM articles 
ORDER BY created_at DESC 
LIMIT 20 OFFSET 40;

• LIMIT 20 — возвращает только 20 записей
• OFFSET 40 — пропускает первые 40 записей (третья страница при 20 элементах на странице)

Недостатки OFFSET:

• При больших значениях OFFSET производительность деградирует, так как БД всё равно должна просканировать все пропускаемые строки
• Неустойчив к изменениям данных между запросами (могут появляться дубли или пропуски)

2. Ключевой метод на основе курсора (Keyset Pagination)

Более эффективная альтернатива, особенно для больших наборов данных:

-- Первая страница
SELECT id, title, created_at FROM articles 
ORDER BY created_at DESC, id DESC 
LIMIT20;

-- Следующая страница (используем значения последней записи предыдущей страницы)
SELECT id, title, created_at FROM articles 
WHERE created_at < '2024-01-15 10:30:00' 
OR (created_at = '2024-01-15 10:30:00' AND id < 456)
ORDER BY created_at DESC, id DESC 
LIMIT 20;

Преимущества keyset-пагинации:

• Константное время выполнения независимо от номера страницы
• Устойчивость к изменениям данных (новые записи не ломают навигацию)
• Меньшая нагрузка на БД

3. Реализация в ORM и прикладном коде

Пример реализации на Node.js с использованием Sequelize:

// Keyset pagination с Sequelize
async function getArticlesPaginated(lastId = null, lastDate = null, limit = 20) {
  const where = {};
  
  if (lastId && lastDate) {
    where[Op.or] = [
      { created_at: { [Op.lt]: lastDate } },
      { 
        [Op.and]: [
          { created_at: lastDate },
          { id: { [Op.lt]: lastId } }
        ]
      }
    ];
  }
  
  return await Article.findAll({
    where,
    order: [['created_at', 'DESC'], ['id', 'DESC']],
    limit
  });
}

// Пример ответа API с пагинацией
app.get('/api/articles', async (req, res) => {
  const { cursor_id, cursor_date } = req.query;
  const articles = await getArticlesPaginated(cursor_id, cursor_date);
  
  const nextCursor = articles.length > 0 ? {
    id: articles[articles.length - 1].id,
    date: articles[articles.length - 1].created_at
  } : null;
  
  res.json({
    data: articles,
    pagination: {
      next_cursor: nextCursor,
      has_more: articles.length === 20
    }
  });
});

4. Специфичные возможности разных БД

PostgreSQL с оконными функциями:

SELECT * FROM (
  SELECT id, title, created_at,
    ROW_NUMBER() OVER (ORDER BY created_at DESC) as row_num
  FROM articles
) AS numbered_articles
WHERE row_num BETWEEN 41 AND 60;

SQL Server с FETCH/OFFSET (с SQL Server 2012):

SELECT id, title, created_at
FROM articles
ORDER BY created_at DESC
OFFSET 40 ROWS
FETCH NEXT 20 ROWS ONLY;

5. Кэширование и оптимизация

Для дальнейшей оптимизации можно использовать:

• Индексы: Обязательно индексировать поля, используемые в ORDER BY и WHERE -E. Материализованные представления: Для сложных агрегаций с пагинацией
• Кэширование счетчиков: Отдельное хранение общего количества записей

-- Создание индекса для оптимизации keyset-пагинации
CREATE INDEX idx_articles_pagination ON articles(created_at DESC, id DESC);

6. Выбор подхода: рекомендации

1. LIMIT/OFFSET используйте для:

   • Небольших наборов данных (< 10,000 записей)
   • Ситуаций, где нужен прямой доступ к произвольным страницам
   • Админ-интерфейсов с фильтрацией

2. Keyset-пагинацию применяйте для:

   • Больших наборов данных
   • Бесконечной ленты (infinite scroll)
   • API с последовательной навигацией
   • Высоконагруженных систем

7. Комплексный пример: пагинация с фильтрацией

-- Эффективная пагинация с фильтрацией
WITH filtered_articles AS (
  SELECT id, title, category_id, created_at
  FROM articles
  WHERE category_id = 5
    AND status = 'published'
)
SELECT * FROM filtered_articles
ORDER BY created_at DESC, id DESC
LIMIT 20
OFFSET 0;

Ключевые выводы:

• Пагинация на стороне БД значительно эффективнее клиентской
• Keyset-пагинация предпочтительнее OFFSET для больших данных
• Всегда используйте ORDER BY с уникальным полем для стабильности
• Индексы должны соответствовать порядку сортировки пагинации

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