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

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

Понимаете ли вы пагинацию дальше примера из учебника: зависимость от диалекта, роль сортировки и цену большого смещения. Для C#-разработчика продолжение обычно такое: во что превращаются Skip и Take в EF Core и как листать миллион строк.

Как отвечать

• LIMIT n: вернуть не больше n строк, OFFSET m: перед этим пропустить m строк. Страница номер p размером s: LIMIT s OFFSET (p - 1) * s;
• диалекты: LIMIT и OFFSET есть в PostgreSQL, MySQL и SQLite. SQL Server так не умеет: там TOP или OFFSET m ROWS FETCH NEXT n ROWS ONLY, причём только вместе с ORDER BY. Та же форма OFFSET ... FETCH есть в стандарте SQL и в Oracle 12c и новее;
• сортировка: без ORDER BY порядок строк не гарантирован, страницы могут пересекаться и терять записи. Сортировка должна быть однозначной: по created_at и дополнительно по уникальному id;
• EF Core: query.OrderBy(x => x.CreatedAt).ThenBy(x => x.Id).Skip(offset).Take(size) провайдер переводит в диалект своей базы. Без OrderBy EF Core пишет в лог предупреждение о непредсказуемом результате;
• цена смещения: база не прыгает к нужной строке, а читает и отбрасывает все m предыдущих. Первая страница быстрая, десятитысячная медленная.

Для глубоких списков и бесконечной ленты используют keyset-пагинацию: клиент присылает последний увиденный ключ: WHERE (created_at, id) < (@lastCreated, @lastId) ORDER BY created_at DESC, id DESC LIMIT 20. По индексу на (created_at, id) он одинаково быстр на любой глубине, а вставка новых строк не сдвигает страницы. Цена: нельзя перейти сразу на страницу 57. Сравнение кортежей есть в PostgreSQL, в SQL Server условие расписывают через OR.

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

Пагинируют в памяти: вызывают ToList() или переходят к IEnumerable до Skip и Take. Запрос тянет всю таблицу, а страница вырезается уже в приложении.

Забывают ORDER BY или сортируют по неуникальному полю. На проде одна и та же запись попадает на две страницы.

Собирают запрос конкатенацией строк в Dapper или FromSqlRaw: "... OFFSET " + page. Значения передают параметрами, иначе это SQL-инъекция и заодно новый план запроса на каждое число.