Индексы и перестройки схемы для быстрого отчёта «общая сумма платежей по пользователю за последний год».
1) Быстрые индексы (минимум, без изменения схемы)
- На Orders: индекс по полям, участвующим в фильтре и соединении:
- `CREATE INDEX idx_orders_user_date ON Orders (user_id, date);`
- На Payments: индекс по внешнему ключу, покрывающий сумму:
- `CREATE INDEX idx_payments_order ON Payments (order_id);`
- либо (Postgres) покрывающий индекс: `CREATE INDEX idx_payments_order_inc_amount ON Payments (order_id) INCLUDE (amount);`
- Результат: план использует индекс Orders(user_id,date) для фильтра по дате и lookup по user_id, затем по order_id из Payments.
2) Добавление полей в Payments (легкая денормализация) — убирает JOIN
- Добавить поля `user_id` и (опционально) `order_date` в Payments:
- `ALTER TABLE Payments ADD COLUMN user_id INT;`
- `ALTER TABLE Payments ADD COLUMN order_date DATE;`
- поддерживать значения через trigger/приложение при вставке/обновлении Orders.
- Индекс покрывающий фильтр и агрегацию:
- `CREATE INDEX idx_payments_user_date_amount ON Payments (user_id, order_date) INCLUDE (amount);`
- Запрос без JOIN будет читать только Payments-партицию/индекс: быстрее при большом объёме.
3) Материализованные агрегаты / summary table (рекомендуется для отчётов OLAP)
- Материализованное представление или отдельная таблица с периодической (или инкрементальной) переработкой:
- Пример создания (Postgres):
- `CREATE MATERIALIZED VIEW payments_by_user_year AS
SELECT p.user_id, SUM(p.amount) AS total_amount
FROM Payments p JOIN Orders o ON p.order_id = o.id
WHERE o.date >= now() - INTERVAL '$1$ year'
GROUP BY p.user_id;`
- Обновлять раз в час/день или поддерживать триггерами/CDC для near-real-time.
- Очень быстрый отчёт, но требует обновления/синхронизации.
4) Партиционирование по дате
- Партиционировать Payments (или Orders) по диапазону даты, чтобы сканировать только последние партиции:
- `ALTER TABLE Payments PARTITION BY RANGE (order_date);`
- Партиции за последние $1\text{year}$ будут небольшими — скан быстрее.
5) Пример итогового оптимального подхода (баланс)
- Для OLTP-системы: оставить нормальную схему + индексы (Orders(user_id,date), Payments(order_id) + возможно покрывающий индекс). Накладные расходы на запись минимальны.
- Для отчётов/OLAP: завести materialized view или summary table (agg per user per day/month) и/или денормализовать Payments (user_id, order_date) + партиционирование.
Компромиссы (кратко)
- Нормализация:
- Плюсы: целостность, меньше дублирования, простота обновления (OLTP-friendly).
- Минусы: JOINы и сканы при больших объёмах (медленнее аналитики).
- Денормализация / агрегаты:
- Плюсы: быстрые запросы, уменьшение JOIN-ов, пользу для OLAP/отчётов.
- Минусы: дополнительное место, сложность поддержки согласованности, более дорогие записи/обновления.
- Практика: комбинировать — OLTP таблицы нормализованы и проиндексированы; для аналитики — партиционирование + материальные агрегаты/денормализованные поля в Payments, обновляемые батчами или через CDC.
Короткая практическая рекомендация
- Быстро начать: добавить `idx_orders_user_date` и `idx_payments_order`, проверять план.
- Если отчёт критичен и частый — создать материализованный агрегат или добавить `user_id, order_date` в Payments и партиционировать по дате.