Постраничная выборка SQL Server без замедления дальних страниц

Пользователь открывает историю заказов. Первая страница появляется сразу, а восьмитысячная загружается несколько секунд. Приложение по-прежнему возвращает всего 25 строк. Добавление серверов приложения обычно не устраняет причину: OFFSET заставляет SQL Server пройти предыдущие записи, прежде чем вернуть запрошенный фрагмент.

Упорядоченный индекс может избавить от сортировки всей таблицы, однако пропускаемые записи все равно требуют работы. Сравните логические чтения для первой и дальней страницы при одинаковых фильтрах. Если чтения растут вместе со смещением, стоит пересмотреть сам механизм навигации. Это другой случай, чем медленная первая страница из-за сортировки выражения без подходящего индекса.

Задать однозначную границу

При выборке по курсору следующий запрос получает последние значения сортировки из предыдущего ответа. Для истории от новых заказов к старым нужны более ранние даты, а при совпадении даты - меньшие идентификаторы. Второе условие принципиально важно. Время создания редко уникально, и исключение всех строк с одинаковым временем может незаметно пропустить заказы.

Пример использует только временную таблицу. Первая выборка возвращает 105 и 104, следующая должна вернуть 103 и 102. На таком наборе удобно проверить границы до переноса решения на миллионы записей.

CREATE TABLE #Orders
(
    OrderId bigint NOT NULL PRIMARY KEY,
    CreatedAt datetime2(3) NOT NULL,
    Amount decimal(12,2) NOT NULL
);
INSERT #Orders VALUES
(105,'2022-01-10T09:00:00',25),
(104,'2022-01-10T09:00:00',40),
(103,'2022-01-09T12:00:00',15),
(102,'2022-01-08T08:00:00',80),
(101,'2022-01-07T08:00:00',30);

CREATE INDEX IX_Orders_Page
ON #Orders(CreatedAt DESC, OrderId DESC)
INCLUDE(Amount);

SELECT TOP (2) OrderId, CreatedAt, Amount
FROM #Orders
ORDER BY CreatedAt DESC, OrderId DESC;

DECLARE @LastTime datetime2(3) = '2022-01-10T09:00:00';
DECLARE @LastId bigint = 104;

SELECT TOP (2) OrderId, CreatedAt, Amount
FROM #Orders
WHERE CreatedAt < @LastTime
   OR (CreatedAt = @LastTime AND OrderId < @LastId)
ORDER BY CreatedAt DESC, OrderId DESC;

DROP TABLE #Orders;

В индексе столбцы навигации находятся в ключе, а Amount включен для получения результата без дополнительного обращения к таблице. В системе с несколькими клиентами хорошей исходной точкой часто служит ключ TenantId, CreatedAt, OrderId, если TenantId ограничен одним значением. Общий список всех клиентов имеет другой способ доступа. Один индекс не обязательно одинаково хорошо обслуживает оба сценария.

Определить контракт курсора

Передавайте время datetime2 и идентификатор с исходной точностью. Округление времени в клиентском объекте даты способно сдвинуть границу. Свяжите курсор с клиентом, фильтрами, направлением сортировки и версией формата. Курсор списка неоплаченных заказов нельзя незаметно использовать для всех заказов. Подпись помогает обнаружить изменение содержимого, но проверка полномочий все равно должна выполняться для каждого запроса.

Для первой страницы полезен отдельный запрос без границы. Универсальная конструкция «курсор отсутствует ИЛИ ...» может привести к менее избирательному повторно используемому плану. Сравнивайте фактические планы. Даже OR в лексикографическом условии иногда частично остается остаточным фильтром. Смотрите на реально прочитанные строки и логические чтения, а не только на наличие оператора Index Seek.

Получайте одну дополнительную строку, чтобы определить наличие продолжения, но возвращайте пользователю только согласованное количество. Следующий курсор строится по последней действительно возвращенной строке. Если взять дополнительную строку и затем применить строгое сравнение «меньше», она будет пропущена. Точный COUNT всего результата иногда дороже самой страницы. Для большинства историй его не требуется пересчитывать при каждом обращении.

Разобраться с изменениями между запросами

Новые заказы выше границы обычно не сдвигают следующую страницу. Изменение существующих значений сортировки все же способно перенести строки через границу и вызвать повторы или пропуски. Желательно сортировать по неизменяемым столбцам. Для аудиторского экспорта, которому нужен зафиксированный набор, независимых чтений недостаточно. Возможны snapshot-транзакция ограниченной длительности, материализованный набор или явно заданная неизменяемая граница экспорта.

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

Проверка точности должна включать сериализацию курсора самим приложением, а не только запуск SQL в редакторе. Например, преобразование bigint в числовой тип JavaScript может потерять точность для больших идентификаторов. Передавайте такие значения строкой или используйте представление, сохраняющее все разряды. Ошибка формата курсора способна проявляться лишь на части старых данных, поэтому тесты на маленьких идентификаторах недостаточны.

Такой подход обеспечивает предсказуемое последовательное перелистывание. Произвольные переходы к номеру страницы могут по-прежнему требовать OFFSET или заранее подготовленных закладок. Выбирайте механизм по реальному сценарию использования и проверяйте ограниченность чтений на дальних страницах. Критерий успеха объединяет полноту результата, правильную сортировку и стабильную стоимость, а не только быстрое открытие первого экрана.

Техническая документация: Microsoft Learn: ORDER BY · Microsoft Learn: Pagination.