Реализация пагинации в 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.