JavaNotes

Оператор LIMIT

2 min read

LIMIT и пагинация

LIMIT ограничивает число строк в PostgreSQL/MySQL-подобных диалектах:

SELECT id, created_at
FROM orders
ORDER BY created_at DESC, id DESC
LIMIT 20;

В стандартном SQL есть FETCH FIRST 20 ROWS ONLY. Без ORDER BY выражение означает «любые 20 строк», а не стабильную первую страницу.

OFFSET

LIMIT 20 OFFSET 100000

СУБД обычно должна найти и пропустить предыдущие строки. Чем дальше страница, тем дороже запрос. Между запросами вставки и удаления также вызывают пропуски или дубликаты.

Keyset pagination

Для порядка (created_at DESC, id DESC) следующая страница строится от последнего увиденного ключа:

SELECT id, created_at
FROM orders
WHERE (created_at, id) < (:last_created_at, :last_id)
ORDER BY created_at DESC, id DESC
LIMIT 20;

Синтаксис сравнения строк поддерживается не везде; эквивалент:

WHERE created_at < :last_created_at
   OR (created_at = :last_created_at AND id < :last_id)

Подход требует детерминированного порядка и подходящего индекса.

LIMIT при изменении

UPDATE ... LIMIT и DELETE ... LIMIT не являются переносимым SQL. Для пакетной обработки лучше явно выбрать ключи в стабильном порядке, затем изменить выбранные строки с учетом конкуренции.

Ловушки

  • LIMIT 1 без ORDER BY не выбирает «последнюю» или «первую» строку.
  • Один created_at недостаточен, если значения повторяются.
  • Keyset не дает дешевого перехода сразу на произвольную страницу.
  • Параллельные изменения требуют заранее определить семантику снимка выдачи.

Связано: Оператор ORDER BY, Индексы SQL, SQL оценка производительности.

Вопросы

Почему большой OFFSET медленный? Предыдущие строки все равно нужно найти и отбросить.

Зачем id после времени? Это уникальный tie-breaker.

Преимущество keyset pagination? Стоимость страницы не растет линейно с ее номером и меньше сдвигов от новых строк.

Недостаток keyset? Нельзя естественно прыгнуть на страницу 500 по номеру.

Graph View

Backlinks