Как делать смещение выборки в SQL запросе?

Смещение выборки в SQL (OFFSET и LIMIT)

Смещение выборки — это техника пагинации и получения подмножества результатов из таблицы. Используются два ключевых предложения: LIMIT (количество строк) и OFFSET (смещение от начала).

Синтаксис

Стандартный подход (PostgreSQL, MySQL, SQLite)

SELECT * FROM users
ORDER BY id
LIMIT 10
OFFSET 20;

Выборка начинается со строки 21 и получает 10 результатов (строки 21-30).

Альтернативный синтаксис (PostgreSQL, MySQL 8+)

SELECT * FROM users
ORDER BY id
LIMIT 10 OFFSET 20;

MSSQL синтаксис (OFFSET ... ROWS ... FETCH)

SELECT * FROM users
ORDER BY id
OFFSET 20 ROWS
FETCH NEXT 10 ROWS ONLY;

Практические примеры

Пример 1: Пагинация

-- Первая страница (10 элементов)
SELECT * FROM products ORDER BY id LIMIT 10 OFFSET 0;

-- Вторая страница
SELECT * FROM products ORDER BY id LIMIT 10 OFFSET 10;

-- Третья страница
SELECT * FROM products ORDER BY id LIMIT 10 OFFSET 20;

Пример 2: Пропуск последних n записей

SELECT * FROM orders
WHERE status = 'active'
ORDER BY created_at DESC
LIMIT 100 OFFSET 50;

Пример 3: Получение n-ой записи

-- Получить 5-ю запись
SELECT * FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 4;

В Python с использованием ORM

SQLAlchemy (Session Query API)

from sqlalchemy import select

# LIMIT + OFFSET
query = select(User).order_by(User.id).limit(10).offset(20)
result = session.execute(query).scalars().all()

SQLAlchemy 2.0 (Core)

from sqlalchemy import select, func

# Пагинация
page = 2
page_size = 10
offset = (page - 1) * page_size

stmt = select(User).order_by(User.id).limit(page_size).offset(offset)
users = session.execute(stmt).scalars().all()

Django ORM

# LIMIT + OFFSET
users = User.objects.all().order_by('id')[20:30]  # Эквивалент LIMIT 10 OFFSET 20

Важные особенности и оптимизация

1. ВСЕГДА используй ORDER BY

Без ORDER BY результаты непредсказуемы:

-- Плохо
SELECT * FROM users LIMIT 10 OFFSET 20;

-- Хорошо
SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 20;

2. Проблема производительности при больших OFFSET

Основная проблема: база читает и выбрасывает все строки до OFFSET. Для больших смещений это неэффективно:

-- Может быть медленно при OFFSET 1000000
SELECT * FROM huge_table ORDER BY id LIMIT 10 OFFSET 1000000;

Решение 1: Keyset Pagination (Cursor-based)

SELECT * FROM users
WHERE id > LAST_SEEN_ID
ORDER BY id
LIMIT 10;

Решение 2: Кэширование ID или временные таблицы

# Кэш последнего ID
last_id = cache.get('pagination_key')
query = f'SELECT * FROM users WHERE id > {last_id} ORDER BY id LIMIT 10'

3. Сложность с COUNT при пагинации

-- Дорогая операция для больших таблиц
SELECT COUNT(*) FROM users;  -- Полный скан

-- Альтернатива: использовать приблизительные значения
SELECT COALESCE(reltuples, 0) as estimate
FROM pg_class WHERE relname = 'users';  -- только PostgreSQL

Практический паттерн пагинации в Python

from typing import Tuple, List
from sqlalchemy import select, func

class UserRepository:
    def paginate(
        self,
        page: int = 1,
        page_size: int = 10
    ) -> Tuple[List[dict], int]:
        """Пагинированная выборка с подсчётом"""
        offset = (page - 1) * page_size
        
        # Выборка
        stmt = select(User).order_by(User.id).limit(page_size).offset(offset)
        users = session.execute(stmt).scalars().all()
        
        # Подсчёт (кэшируй эту операцию!)
        count_stmt = select(func.count()).select_from(User)
        total = session.execute(count_stmt).scalar()
        
        return users, total

# Использование
users, total = repo.paginate(page=2, page_size=20)
total_pages = (total + page_size - 1) // page_size

Заключение

OFFSET/LIMIT — это стандартный способ пагинации, но для больших таблиц (>1M записей) лучше использовать Keyset Pagination. Всегда помни про ORDER BY и оптимизацию с индексами.