Короткий диагноз
- Причина медлительности: большой объём таблиц + некорректные/отсутствующие индексы и/или план, который сканирует много строк перед фильтрацией (например full scan таблицы orders или крупных наборов после join). Нужно уменьшить чтение IO и обеспечить индексные пути для фильтров по region и по date + для соединения по cust_id.
Рекомендованные индексы (примеры для PostgreSQL/MySQL)
- Индекс на customers.region (быстрый поиск клиентов по региону):
CREATE INDEX idx_customers_region ON customers(region);
- Индекс на orders по полю внешнего ключа и дате (композитный; порядок зависит от селективности):
- если сначала выбираете клиентов, затем ищете их заказы — полезен:
CREATE INDEX idx_orders_custid_date ON orders(cust_id, date);
- если сначала фильтруете по дате (много старых/малой части по дате) — полезен:
CREATE INDEX idx_orders_date_custid ON orders(date, cust_id);
- Покрывающий индекс (чтобы не делать lookup по таблице, если выбираете только некоторые колонки):
PostgreSQL:
CREATE INDEX idx_orders_date_cust_inc ON orders(date, cust_id) INCLUDE (amount, status);
MySQL:
ALTER TABLE orders ADD INDEX idx_orders_date_cust_cols (date, cust_id, amount, status);
- Частичный индекс (Postgres) для постоянно запрашиваемых диапазонов:
CREATE INDEX idx_orders_recent_by_cust ON orders(cust_id, date) WHERE date >= ${}^{\prime }2023-01-01^{\prime }$;
(полезно если запросы почти всегда на недавние данные)
Как выбрать порядок колонок в индексе
- Если селективность фильтра по region высокая (т.е. мало клиентов в EU), ставим индекс на customers(region) + orders(cust_id, date).
- Если селективность по date выше (малый временной диапазон), ставим индекс на orders(date, cust_id).
- Можно иметь оба индекса и смотреть план.
Переструктурирование запроса
- Не SELECT *, выбирайте только нужные колонки — это уменьшит IO и даст шансы на индекс-Only Scan.
- Варианты переписывания (проверяйте EXPLAIN):
1) Оригинал, но с явным ограничением колонок:
SELECT orders.id, orders.date, orders.amount, customers.name
FROM orders JOIN customers ON orders.cust_id = customers.id
WHERE customers.region = 'EU' AND orders.date >= ${}^{\prime }2023-01-01^{\prime }$;
2) Push filter на orders (если date селективен) + EXISTS (часто даёт лучший план):
SELECT /* нужные колонки */
FROM orders
WHERE orders.date >= ${}^{\prime }2023-01-01^{\prime }$ AND EXISTS (SELECT 1 FROM customers WHERE customers.id = orders.cust_id AND customers.region = 'EU');
3) Сначала искать клиентов нужного региона, затем JOIN по преднабору id (подзапрос/CTE) — полезно если клиентов мало:
WITH eu_customers AS (SELECT id FROM customers WHERE region = 'EU')
SELECT /*cols*/ FROM orders JOIN eu_customers ON orders.cust_id = eu_customers.id
WHERE orders.date >= ${}^{\prime }2023-01-01^{\prime }$;
Изменения схемы и архитектурные оптимизации
- Денормализация: сохранять region в orders (orders.customer_region) — позволит индексировать/партиционировать по region без join. Минус — избыточность и необходимость поддержания согласованности.
- Партиционирование таблицы orders по диапазонам даты (range partition by date) — сильно ускорит запросы по date и удаление старых данных.
- Если часто фильтруют одновременно по region и date, можно партиционировать по date и хранить region в строке, либо создать партиции по region (если небольшое число регионов) + по дате.
- Материализованный вид/предсчитанные таблицы для популярных комбинаций region+дат (например orders_by_region_month) — очень эффективно при отчётах.
- Кластеризация таблицы по индексу (Postgres CLUSTER) для локализации строк по cust_id/date.
Операции по оптимизации сервера и статистики
- Соберите актуальные статистики: ANALYZE/ANALYZE VERBOSE (Postgres) или ANALYZE TABLE (MySQL).
- Проверяйте план: EXPLAIN (ANALYZE, BUFFERS) ... и смотрите, какие индексы используются.
- Увеличьте временно work_mem / join_buffers / parallel_workers если операции сортировки/слияния требуют памяти.
- Убедитесь, что foreign key orders.cust_id имеет индекс (обычно он нужен).
Практический порядок действий
1. EXPLAIN ANALYZE текущего запроса — определить, что сканируется.
2. Добавить/проверить индекс на customers(region).
3. Добавить composite индекс на orders: либо (cust_id, date), либо (date, cust_id) в зависимости от селективности.
4. Переписать запрос без SELECT * и протестировать EXPLAIN.
5. При необходимости — денормализация региона в orders или партиционирование по дате.
6. Рассмотреть покрывающий индекс или материализованный вид для частых отчётов.
Если нужно, могу предложить конкретные CREATE INDEX и переписанные варианты запроса под вашу СУБД (Postgres/MySQL) и пример EXPLAIN-анализа — пришлите EXPLAIN текущего выполнения.