Кратко и по шагам.
1) Диагностика (обязательно)
- Выполнить план выполнения: `EXPLAIN` / `EXPLAIN ANALYZE` (Postgres/MySQL). Смотреть: есть ли последовательное чтение (seq scan), оценочная/реальная кардинальность, количество прочитанных страниц/строк.
- Посчитать долю совпадающих строк $f=\frac{\text{matched\_rows}}{\text{total\_rows}}$ (если $f$ большая — индекс может не помочь).
2) Индексы
- Простое решение: B-tree индекс по колонке с фильтром. Пример (MySQL/Postgres): `CREATE INDEX idx_orders_order_date ON orders(order_date);`
- Лучший вариант для этого запроса (чтобы избежать обращений к таблице): покрывающий индекс на $(order\_date,user\_id)$. Пример:
- MySQL/Postgres: `CREATE INDEX idx_orders_date_user ON orders(order_date, user_id);`
- Postgres можно `CREATE INDEX ... ON orders(order_date) INCLUDE (user_id);`
- Альтернатива для очень больших временных таблиц с физически упорядоченными данными: BRIN индекс (`USING BRIN(order_date)`) — очень компактный, но менее точный; хорош при естественном порядке по дате.
- Когда индекс не помогает: если $f$ велико (например $f\gtrsim 0.1$ — эмпирическое правило), чтение по индексу приведёт к большому числу случайных I/O и может быть медленнее полного сканирования.
3) Партиционирование
- RANGE-партиционирование по `order_date` (месяц/квартал/год) позволяет partition pruning и уменьшает объём данных, просматриваемых для запроса.
- Пример (Postgres/MySQL): `PARTITION BY RANGE (order_date) (...)`
- Плюсы: меньшие индексы на каждую партицию, ускорение запросов по недавним датам, лёгкое удаление старых данных.
- Минусы: усложнение DDL/backup/restore, возможные ограничения (в MySQL старые версии не поддерживают глобальные индексы), накладные расходы при большое число партиций.
4) Денормализация / материализованные представления / агрегаты
- Если запросы часто про последние даты — поддержать отдельную таблицу (materialized view) с нужными полями (user_id, order_date) или агрегатом; ревьюшать/refresh либо потоково обновлять (CDC).
- Плюсы: очень быстрые чтения.
- Минусы: сложность синхронизации, задержка актуальности, дополнительная логика на записи.
5) Прочие оптимизации
- CLUSTER/OPTIMIZE TABLE / физическая реорганизация — привести данные в порядок по `order_date`, улучшает локальность и эффективность B-tree/BRIN.
- Обновить статистику: `ANALYZE` / `ANALYZE VERBOSE`.
- Убедиться, что типы колонок компактны и правильно индексируются.
- Рассмотреть кеширование / CDN / Redis для очень частых повторных запросов.
6) Как измерять эффект (методология)
- Базовая метрика: wall time выполнения запроса (медиана и перцентили, например p95/p99).
- Использовать `EXPLAIN ANALYZE` для получения реального времени и чтений страниц.
- Разделять холодные и тёплые прогоны: делать $\ge 3$ холодных запуска (после очистки кеша) и $\ge 10$ тёплых, брать стабильные медианы.
- Собирать I/O (logical/physical reads), CPU, throughput (QPS) и задержания.
- Формула улучшения времени: \(\text{improvement\_%} = \frac{T_{\text{before}}-T_{\text{after}}}{T_{\text{before}}}\times 100\%\).
- Инструменты: `pg_stat_statements`, slow query log, performance_schema, iostat, monitoring (Prometheus/Grafana).
7) Компромиссы (чего ожидать)
- Индексы увеличивают время вставки/обновления и потребляют пространство на диске.
- Покрывающие индексы эффективны для чтения, но дороже на запись и хранение.
- Партиционирование усложняет администрирование; некоторые СУБД имеют ограничения на глобальные индексы или операции между партициями.
- BRIN экономит место, но даёт менее точные селекции.
- Материализованные представления — быстро читаются, но могут быть устаревшими и требуют расходов на поддержание.
- При низкой селективности фильтра индекс может ухудшить производительность — иногда лучше seq scan.
Резюме: начать с анализа плана (`EXPLAIN`), добавить покрывающий индекс `ORDER_DATE, USER_ID` или BRIN при больших упорядоченных данных; если запросы по диапазонам времени частые — рассмотреть партиционирование; для очень горячих запросов — материализованная таблица. Измерять через `EXPLAIN ANALYZE`, медианы/перцентили и I/O; учитывать компромисс чтение vs запись и усложнение поддержки.