> В каком порядке строить составной индекс для user_id и даты (Python)

Короткий ответ

Порядок колонок в составном индексе определяется паттерном запросов: сначала колонка с условием равенства (=), затем - с диапазоном или сортировкой. Для user_id и даты чаще всего правильный порядок - (user_id, date), так как типичный запрос фильтрует по конкретному пользователю и затем сортирует или ограничивает по дате. Если же запросы часто идут по диапазону дат без привязки к пользователю, индекс (date, user_id) может быть оправдан, но это редкий случай.

Подробное объяснение

Составной индекс в B-tree работает как упорядоченный список по всем колонкам слева направо. Это означает, что:

• Индекс (user_id, date) эффективен для запросов вида WHERE user_id = ? AND date > ?, WHERE user_id = ? ORDER BY date, WHERE user_id = ?.
• Индекс (date, user_id) эффективен для WHERE date BETWEEN ? AND ? AND user_id = ? только если фильтр по user_id применяется после сужения по дате - но в этом случае БД сможет использовать индекс только для первой колонки, а user_id будет фильтроваться как post-filter.

Ключевой принцип: первая колонка должна быть селективной (с высокой кардинальностью) и использоваться в условиях равенства. Если первая колонка - дата, а запросы всегда фильтруют по user_id, индекс будет бесполезен для таких запросов, потому что БД не сможет пропустить диапазон дат без учёта пользователя.

Дополнительные соображения:

• Если нужна сортировка по дате в обратном порядке, индекс (user_id, date DESC) может быть создан отдельно, но обычно достаточно (user_id, date) - БД умеет сканировать в обратном направлении.
• Если запросы включают date в ORDER BY и LIMIT, индекс (user_id, date) позволяет избежать filesort.
• Если запросы часто агрегируют по пользователю за период, индекс (user_id, date) также помогает с covering index, если добавить нужные колонки в INCLUDE.

На практике

Для типового backend-приложения с таблицей событий или заказов:

• Основной запрос: "все записи пользователя за последний месяц" → (user_id, date).
• Запрос "последние N записей пользователя" → (user_id, date) с ORDER BY date DESC LIMIT N.
• Запрос "все пользователи, активные в определённый день" → (date, user_id) - но это редкий аналитический сценарий.

Если в таблице есть и другие частые фильтры (например, status), стоит рассмотреть отдельные индексы или включить status в индекс после user_id, но перед датой - только если он используется как равенство.

Пример кода

SQL
-- Основной индекс для типового сценария
CREATE INDEX idx_user_date ON events (user_id, date);

-- Запрос, который использует индекс полностью
SELECT *
FROM events
WHERE user_id = 123
  AND date >= '2025-01-01'
ORDER BY date
LIMIT 100;

-- Если нужен covering index для частого запроса
CREATE INDEX idx_user_date_covering ON events (user_id, date)
INCLUDE (event_type, payload);

Как отвечать на собеседовании

Начни с вопроса: "Какие запросы преобладают?" - это показывает, что ты не даёшь шаблонный ответ. Затем объясни принцип: первая колонка - для равенства, вторая - для диапазона или сортировки. Приведи пример типичного запроса и покажи, как индекс ускоряет его. Упомяни, что порядок можно проверить через EXPLAIN - если видишь Using filesort или Using where после index scan, значит порядок неоптимален. Заверши trade-off: если есть редкий запрос по дате без user_id, можно добавить отдельный индекс (date), а не менять порядок основного.

Что проверяет интервьюер

• Понимание внутреннего устройства B-tree индекса и ограничения на префиксное использование колонок.
• Умение связывать структуру индекса с реальными паттернами запросов.
• Способность рассуждать о trade-off между разными порядками колонок.
• Знание, как проверить эффективность индекса (EXPLAIN, покрывающие индексы).
• Понимание разницы между условием равенства и диапазоном.

Типичные ошибки

• Ответ "всегда ставь user_id первым" без анализа запросов - это не универсальное правило.
• Утверждение, что индекс (date, user_id) подойдёт для запроса по user_id - не подойдёт, если фильтр по дате не ограничивает диапазон.
• Игнорирование сортировки: если нужен ORDER BY date DESC, индекс (user_id, date) работает, но если нужен ORDER BY user_id, date DESC - потребуется отдельный индекс.
• Предложение менять порядок колонок в существующем индексе без учёта других запросов, которые его используют.
• Забывание про covering index - в некоторых случаях можно избежать чтения таблицы целиком.