Для чего нужны LIMIT и OFFSET в SQL

Что проверяют

Знаете ли вы, во что обходится OFFSET на больших таблицах и чем его заменить. Следом почти всегда спрашивают, как вы делали пагинацию в Spring Data и почему сотая страница открывается медленнее первой.

Как отвечать

Синтаксис и смысл коротко:

• LIMIT n: вернуть не больше n строк;
• OFFSET m: пропустить первые m строк результата. Запрос с ORDER BY id LIMIT 20 OFFSET 40 отдаёт третью страницу по 20 записей;
• диалекты: LIMIT и OFFSET есть в PostgreSQL, MySQL и SQLite. Стандарт SQL описывает OFFSET 40 ROWS FETCH FIRST 20 ROWS ONLY, так пишут в Oracle и SQL Server. В MySQL есть ещё форма LIMIT 40, 20, где первым идёт смещение.

Обязательно добавьте ORDER BY. Без него порядок строк не гарантирован, и на соседних страницах записи могут повторяться или теряться.

Главное про производительность: OFFSET не перепрыгивает строки. База читает и выбрасывает все m пропущенных, так что стоимость растёт вместе с номером страницы. Для глубокой пагинации используют keyset (seek): запоминают последнее значение и продолжают с него, WHERE (created_at, id) < (?, ?) ORDER BY created_at DESC, id DESC LIMIT 20, с индексом по тем же колонкам. Такой запрос одинаково быстр на любой глубине и не сбивается, когда в начало вставились новые строки.

В Java это выглядит так. В JPA setFirstResult() и setMaxResults() превращаются в OFFSET и LIMIT нужного диалекта. В Spring Data PageRequest.of(page, size, sort) даёт Page<T>, а он делает дополнительный count(*). Если общее число не нужно, берите Slice<T>, а для keyset в Spring Data 3.x есть ScrollPosition и Window.

Где спотыкаются

Пагинируют без ORDER BY и потом ловят дубли и пропуски на границе страниц.

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

Совмещают JOIN FETCH коллекции с пагинацией в Hibernate. Лимит тогда применяется в памяти ко всей выборке, Hibernate пишет об этом предупреждение, а на больших данных дело заканчивается OutOfMemoryError.