Что такое LIMIT OFFSET?

Что такое LIMIT OFFSET

LIMIT OFFSET — это SQL клауза для пагинации результатов запроса. LIMIT ограничивает количество возвращаемых строк, а OFFSET пропускает N первых строк. Это один из основных способов реализации постраничного отображения данных в веб-приложениях.

Основной синтаксис

# SQL
SELECT * FROM users
LIMIT 10       # Возвращаем максимум 10 строк
OFFSET 0       # Начиная с позиции 0 (первая строка)

# Результат: строки 1-10

Как это работает

# OFFSET пропускает строки, LIMIT ограничивает результат
SELECT * FROM users
LIMIT 10 OFFSET 20

# Означает: пропусти первые 20 строк, затем возьми 10
# Результат: строки 21-30

Пример с PostgreSQL

import psycopg2

conn = psycopg2.connect('dbname=shop')
cursor = conn.cursor()

# Страница 1: строки 1-10
cursor.execute('SELECT id, name, price FROM products LIMIT 10 OFFSET 0')
page1 = cursor.fetchall()

# Страница 2: строки 11-20
cursor.execute('SELECT id, name, price FROM products LIMIT 10 OFFSET 10')
page2 = cursor.fetchall()

# Страница 3: строки 21-30
cursor.execute('SELECT id, name, price FROM products LIMIT 10 OFFSET 20')
page3 = cursor.fetchall()

SQLAlchemy / ORM

from sqlalchemy import create_engine
from sqlalchemy.orm import Session
from models import User

session = Session(engine)
page = 2
page_size = 10

# Рассчитываем offset: (page - 1) * page_size
offset = (page - 1) * page_size

users = session.query(User).limit(page_size).offset(offset).all()
print(f'Страница {page}: {len(users)} пользователей')

FastAPI с пагинацией

from fastapi import FastAPI, Query
from sqlalchemy.orm import Session
from typing import List

app = FastAPI()

@app.get('/users')
async def get_users(
    skip: int = Query(0, ge=0),           # offset = skip
    limit: int = Query(10, ge=1, le=100)  # максимум 100
):
    # skip и limit передаём в БД
    db_users = db.query(User).limit(limit).offset(skip).all()
    return db_users

# Использование:
# GET /users?skip=0&limit=10   → страница 1
# GET /users?skip=10&limit=10  → страница 2
# GET /users?skip=20&limit=10  → страница 3

Вычисление offset в коде

def calculate_offset(page: int, page_size: int) -> int:
    """Вычисляет offset из номера страницы"""
    if page < 1:
        raise ValueError('Page должна быть >= 1')
    return (page - 1) * page_size

# Примеры
assert calculate_offset(1, 10) == 0    # Страница 1 → offset 0
assert calculate_offset(2, 10) == 10   # Страница 2 → offset 10
assert calculate_offset(3, 10) == 20   # Страница 3 → offset 20
assert calculate_offset(5, 25) == 100  # Страница 5 с размером 25

Вариант: LIMIT offset, limit (PostgreSQL)

# Альтернативный синтаксис: LIMIT count OFFSET offset
SELECT * FROM products
LIMIT 10 OFFSET 20

# Эквивалентно (в некоторых БД):
SELECT * FROM products
LIMIT 10, 20  # MySQL синтаксис (LIMIT offset, count)

Сортировка ОБЯЗАТЕЛЬНА при пагинации

НЕПРАВИЛЬНО (без ORDER BY):

# Опасно! Порядок может измениться между запросами
SELECT * FROM users LIMIT 10 OFFSET 0
SELECT * FROM users LIMIT 10 OFFSET 10  # Может быть другой порядок

ПРАВИЛЬНО (с ORDER BY):

# Порядок фиксирован
SELECT * FROM users
ORDER BY id ASC
LIMIT 10 OFFSET 0

SELECT * FROM users
ORDER BY id ASC
LIMIT 10 OFFSET 10  # Гарантированно строки 11-20

Пример с Django ORM

from django.core.paginator import Paginator
from .models import Product

# Способ 1: встроенный Paginator
products = Product.objects.all()
paginator = Paginator(products, 10)  # 10 на странице
page = paginator.get_page(1)  # Страница 1

# Под капотом использует LIMIT OFFSET

# Способ 2: напрямую
page_num = 2
page_size = 10
offset = (page_num - 1) * page_size

products = Product.objects.all()[offset:offset + page_size]
# Django преобразует это в: LIMIT 10 OFFSET 10

Производительность: OFFSET проблема

Проблема OFFSET при больших смещениях:

# Быстро: OFFSET 0
SELECT * FROM products LIMIT 10 OFFSET 0

# Медленнее: OFFSET 1000
SELECT * FROM products LIMIT 10 OFFSET 1000
# БД читает 1010 строк, затем отбрасывает первые 1000!

# Очень медленно: OFFSET 100000
SELECT * FROM products LIMIT 10 OFFSET 100000
# БД читает 100010 строк! Неэффективно!

Оптимизация: Keyset Pagination

Для больших смещений лучше использовать keyset pagination (seek-based):

# Вместо OFFSET, используем WHERE с последним ID
LAST_ID = 5000  # ID последнего элемента на предыдущей странице

# НЕПРАВИЛЬНО (медленно при больших offset)
SELECT * FROM products LIMIT 10 OFFSET 10000

# ПРАВИЛЬНО (быстро, использует индекс)
SELECT * FROM products
WHERE id > LAST_ID
ORDER BY id ASC
LIMIT 10

Полный пример пагинации

from fastapi import FastAPI, Query
from sqlalchemy import func
from sqlalchemy.orm import Session

app = FastAPI()

class PaginationResponse:
    def __init__(self, items, total, page, page_size):
        self.items = items
        self.total = total
        self.page = page
        self.page_size = page_size
        self.pages = (total + page_size - 1) // page_size

@app.get('/products')
async def get_products(
    page: int = Query(1, ge=1),
    page_size: int = Query(10, ge=1, le=100),
    db: Session = Depends(get_db)
):
    # Считаем общее количество
    total = db.query(func.count(Product.id)).scalar()
    
    # Рассчитываем offset
    offset = (page - 1) * page_size
    
    # Получаем данные
    items = (
        db.query(Product)
        .order_by(Product.id)
        .limit(page_size)
        .offset(offset)
        .all()
    )
    
    return PaginationResponse(items, total, page, page_size)

# Результат:
# {
#   "items": [...],
#   "total": 1000,
#   "page": 1,
#   "page_size": 10,
#   "pages": 100
# }

Правила и лучшие практики

✅ ВСЕГДА:

• Используй ORDER BY при пагинации
• Ограничивай максимальный LIMIT (например, <= 100)
• Валидируй page и page_size на входе
• Кэшируй COUNT если он дорогой

❌ НИКОГДА:

• Не забывай ORDER BY
• Не используй OFFSET для очень больших смещений (>10000)
• Не позволяй клиентам устанавливать LIMIT > 100
• Не выполняй COUNT для каждого запроса без кэша

Оптимизация для большших наборов:

# Переходи на keyset pagination при offset > 10000
if offset > 10000:
    # Используй WHERE id > last_id вместо OFFSET
    query = db.query(Product).filter(Product.id > last_id)
else:
    query = db.query(Product).offset(offset)

LIMIT OFFSET — простой и надёжный способ пагинации для большинства случаев, но требует внимания к производительности при больших смещениях.