re:re:actable
Урок 043 минуты чтения

Как работают ORDER BY, LIMIT и OFFSET

На этой странице
Кратко

ORDER BY задаёт порядок результата (ASC по умолчанию, DESC — по убыванию, несколько ключей применяются последовательно, NULLS FIRST/LAST управляет местом NULL). Без ORDER BY порядок не гарантирован. LIMIT n возвращает не больше n строк, OFFSET k пропускает первые k. Для стабильного порядка сортировка должна быть однозначной — добавляют уникальный ключ (id) последним. Глубокий OFFSET медленный (база всё равно читает и отбрасывает пропущенные строки) и неустойчив к вставкам — для лент используют keyset-пагинацию.

Вопрос

Как работают ORDER BY, LIMIT и OFFSET и в чём их подводные камни?

Сортировка

SELECT id, title, price
FROM products
ORDER BY category_id ASC, price DESC, id;     -- сначала категория, внутри — цена, затем id
ORDER BY rating DESC NULLS LAST               -- товары без рейтинга — в конце
ORDER BY 2                                    -- по номеру столбца (плохо читается)
ORDER BY lower(title)                         -- по выражению
ORDER BY CASE status WHEN 'urgent' THEN 0 ELSE 1 END, created_at

В PostgreSQL NULL по умолчанию считаются больше любых значений: в конце при ASC, в начале при DESC.

LIMIT и OFFSET

SELECT id, title
FROM posts
ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 40;        -- третья страница по 20

Стандартный синтаксис: OFFSET 40 ROWS FETCH FIRST 20 ROWS ONLY. В SQL Server — TOP, в старых Oracle — ROWNUM.

Стабильность порядка

-- ✗ у многих постов одинаковый created_at — между страницами строки «прыгают»
ORDER BY created_at DESC
 
-- ✓ уникальный tie-breaker
ORDER BY created_at DESC, id DESC

Если ключ сортировки не уникален, СУБД может вернуть равные строки в разном порядке в разных запросах, и при пагинации часть записей повторится, а часть пропадёт.

Проблемы OFFSET

ПроблемаПричина
Медленно на дальних страницахЧтобы вернуть 20 строк с OFFSET 100000, нужно прочитать 100 020
Дубли и пропускиНовая запись сверху сдвигает все страницы
НагрузкаБоты, листающие до 5000-й страницы, нагружают БД

Решение — keyset (cursor) pagination: WHERE (created_at, id) < ($1, $2) ORDER BY created_at DESC, id DESC LIMIT 20 — см. урок о пагинации.

Индекс и ORDER BY ... LIMIT

CREATE INDEX posts_created_idx ON posts (created_at DESC, id DESC);

С подходящим индексом база читает первые 20 записей индекса и останавливается — без сортировки всей таблицы. Подробнее — в уроке индекс и ORDER BY.

Частые ошибки

  • Полагаться на порядок без ORDER BY (например, «строки возвращаются по id»).
  • Неуникальный ключ сортировки при пагинации.
  • OFFSET для бесконечной ленты и экспорта больших таблиц.
  • LIMIT без ORDER BY — «случайные» строки.

Что важно помнить

  • Порядок — только явный и однозначный.
  • LIMIT без ORDER BY бессмыслен для пагинации.
  • OFFSET хорош для небольших списков и номеров страниц, keyset — для лент и больших объёмов.

Мини-шпаргалка

  • ORDER BY a ASC, b DESC, id.
  • NULLS FIRST/LAST.
  • LIMIT n OFFSET k.
  • Уникальный tie-breaker в конце сортировки.
  • Глубокие страницы — keyset.

Документация

PostgreSQL: Sorting Rows, PostgreSQL: LIMIT and OFFSET.

Дочитали до конца?
Отметьте урок — прогресс сохранится в браузере.

Поиск

Поиск по урокам