Реализация пагинации в SQL-запросе: skip, take функционал

Быстрый ответ

Для осуществления постраничного вывода данных воспользуйтесь командой OFFSET, что позволит пропустить заданное количество строк, а также командой FETCH NEXT, с помощью которой можно ограничить общее число строк. Примером будет вывод второй страницы данных из таблицы YourTable по 10 записей на каждую:

SELECT * FROM YourTable
ORDER BY YourColumn -- Поддерживает порядок следования
OFFSET 10 ROWS -- пропускаем первые десять строк
FETCH NEXT 10 ROWS ONLY; -- выбираем следующие десять

Замените YourColumn на имя колонки, по которой должна проводиться сортировка, и настройте параметры OFFSET и FETCH в соответствии с размером страницы и её номером.

Приведите беспорядок в порядок: использование ORDER BY

Применяйте ORDER BY в качестве напоминания о том, что необходимо упорядочить данные перед их разбиением на страницы. Это поможет избежать хаоса и беспорядка в данных. Если у вас отсутствуют четкие критерии сортировки, попробуйте использовать что-нибудь нестандартное, например, CURRENT_TIMESTAMP.

Особенности реализации постраничного вывода в зависимости от версии SQL Server

SQL Server 2012 и последующие версии

В SQL Server с 2012 года и более поздних версий используйте OFFSET и FETCH NEXT для эффективного постраничного вывода данных. Обязательно должна быть использована команда ORDER BY перед их применением.

Подробнее об этом расскажет наш спикер на видео

Версии SQL Server до 2012 года включительно

В случае работы с версиями SQL Server, выпущенными до 2012 года, команды OFFSET и FETCH NEXT будут недоступны. Однако можно достичь аналогичного результата с использованием ROW_NUMBER() и Common Table Expression (CTE):

WITH OrderedRows AS (
    SELECT ROW_NUMBER() OVER (ORDER BY YourColumn) AS RowNumber, *
    FROM YourTable
)
SELECT *
FROM OrderedRows
WHERE RowNumber BETWEEN 11 AND 20; -- Выбираем строки с 11 по 20

Альтернативные способы реализации постраничного вывода

Использование ROW_NUMBER() без использования OFFSET и FETCH

При отсутствии доступности команд OFFSET и FETCH можно воспользоваться ROW_NUMBER():

SELECT * FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY YourColumn) as RowNum, *
    FROM YourTable
) AS RowsWithNumbers
WHERE RowNum > 10 and RowNum <= 20;

Обратите внимание, что этот способ может быть менее производительным.

Избегаем использование подзапросов с NOT IN

Использование вложенных подзапросов с применением NOT IN может существенно ухудшить производительность при больших объёмах данных, поэтому старайтесь избегать его.

Визуализация

Воспринимайте базу данных как огромную книжную полку, где каждая книга представляет строки из запроса:

| Позиция в серии     | Название книги            |
| ------------------- | ------------------------- |
| 1                   | Страница 1 📚               |
| 2                   | Страница 2...             |
| ...                 | Страница...               |
| N                   | Последняя страница 📚     |

Постраничный вывод помогает найти нужную главу:

Желаем прочесть: Главы с 5 по 7

Мы пропускаем первые четыре главы и выбираем следующие три:

Пропущено: [📚, 📚, 📚, 📚] -- Прошлые главы
Выбрано:   [📖, 📖, 📖] -- Желаемые главы

И вот пример SQL-запроса для постраничного вывода:

SELECT * FROM books
ORDER BY chapterNo
OFFSET 4 ROWS       -- Пропускаем 4 главы
FETCH NEXT 3 ROWS   -- Выбираем следующие 3 главы

Расслабьтесь и наслаждайтесь чтением выбранных глав!

Дополнительные рекомендации

Не используйте команду TOP для постраничного вывода

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

Задружите с индексацией

Индексированные столбцы облегчают сортировку и фильтрацию данных, тем самым ускоряя постраничный вывод.

Поддерживайте порядок

Если сортировка невозможна, поэкспериментируйте с ORDER BY (SELECT NULL). Но будьте готовы к неожиданным результатам.

Желаете узнать больше?

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

Полезные материалы

1. Как оптимизировать постраничный вывод в SQL — объясняется, почему использование OFFSET может быть не лучшим решением, и предлагаются альтернативы.
2. Постраничный вывод в SQL Server — содержатся рекомендации по использованию ROW_NUMBER() для постраничного вывода данных.
3. Вопросы по постраничному выводу — страница Ask Tom с информацией о влиянии постраничного вывода на производительность в Oracle.
4. Документация PostgreSQL по LIMIT и OFFSET — официальное руководство PostgreSQL по использованию LIMIT и OFFSET.