Кратко — цель: уменьшить число строк, которые нужно просуммировать, и/или дать планировщику возможность делать index-only/partitioned read.
Рекомендации (с причинами и примерами):
1) Покрывающий/композитный индекс
- Почему: сначала фильтруем по status, затем группируем по customer_id и суммируем amount — индекс в этом порядке позволит быстро найти только нужные строки и (в идеале) избежать обращения к таблице.
- Postgres (лучше — частичный индекс на completed):
CREATE INDEX ON orders (customer_id) INCLUDE (amount) WHERE status = 'completed';
Обоснование: индекс хранит только строки с status='completed', сканирует и агрегирует по customer_id, amount доступен из индекса.
- MySQL (InnoDB): нет частичных индексов, используйте составной индекс:
CREATE INDEX idx_status_customer_amount ON orders (status, customer_id, amount);
Обоснование: status первым для фильтрации, затем customer_id для группировки; amount в индексе делает его покрывающим.
- Важно: порядок колонок в индексе имеет значение: status → customer_id → amount.
2) Частичный/фильтрованный индекс (если БД поддерживает)
- Если fraction строк с status='completed' мала, создайте индекс только для них (Postgres, SQL Server). Это сильно уменьшит индексный размер и ускорит агрегацию.
3) Денормализация / материализованное представление
- Если запрос выполняется часто и данные обновляются не критично в реальном времени — храните предагрегированные суммы в отдельной таблице (orders_agg_customer) с колонками (customer_id, total_completed).
- Обновление: периодический batch (например, раз в минуту/час) или инкрементные триггеры/CDC. Это даёт очень быстрый ответ на WHERE total>1000.
- Минус: сложность синхронизации, дополнительное место.
4) Перепись запроса / предагрегация
- Разделение на два шага может помочь планировщику и читабельности:
SELECT customer_id, total FROM (
SELECT customer_id, $\text{SUM}(amount)$ AS total
FROM orders
WHERE status='completed'
GROUP BY customer_id
) t
WHERE total $>1000$.
- Это эквивалентно исходному, но полезно для применения индекса/материализации в промежуточном шаге.
5) Партиционирование
- Партиционировать таблицу по статусу (если возможно) или по диапазону/хэшу customer_id. При партиции по status сканируется только "completed" партиция.
- Минус: усложняет DDL/мэйтенанс.
6) Альтернативы/инфраструктура
- Для аналитики большого объёма данных: columnar store (ClickHouse, Redshift), аналитические материализованные представления.
- Для высоких требований к latency — поддерживать агрегаты в реальном времени (streaming/CDC).
7) Практические шаги для оценки
- Выполните EXPLAIN (ANALYZE) исходного запроса.
- Проверьте селективность: SELECT status, count(*) FROM orders GROUP BY status;
- Попробуйте создать предложенный индекс в тестовой среде и снова EXPLAIN/EXPLAIN ANALYZE; оцените выигрыш vs. стоимость вставок/обновлений.
Замечание о производительности и компромиссах:
- Индексы ускоряют чтение, замедляют вставки/обновления и занимают место.
- Частичный/покрывающий индекс и/или материализованная таблица дают наибольший выигрыш для часто выполняемых запросов с одним фильтром status='completed'.
Резюме рекомендаций (самое важное):
- Если Postgres: создать частичный покрывающий индекс: (customer_id) INCLUDE (amount) WHERE status='completed'.
- Если MySQL: создать составной покрывающий индекс: (status, customer_id, amount).
- При очень частых запросах — использовать предагрегированную таблицу/материализованное представление и/или партиционирование.