Кратко — какие индексы и изменения дадут ускорение, и почему.
Рекомендуемые индексы
- На таблице customers: компактный индекс, позволяющий быстро получить id и name по region. Примеры:
- MySQL: `CREATE INDEX idx_customers_region_id_name ON customers(region, id, name);`
- PostgreSQL: `CREATE INDEX idx_customers_region ON customers(region) INCLUDE (id, name);`
Почему: условие `c.region='EU'` должно быстро отфильтровать строки и вернуть сразу `id` и `name` (covering index) без дополнительного обращеия к таблице.
- На таблице orders: составной индекс, учитывающий диапазон по дате и связь с customer:
- если фильтр по дате более селективен, то `date` первым:
- MySQL: `CREATE INDEX idx_orders_date_cid ON orders(date, c_id);`
- PostgreSQL: `CREATE INDEX idx_orders_date_cid ON orders(date, c_id) INCLUDE (id);`
- если селективность по клиенту выше, можно попробовать `CREATE INDEX idx_orders_cid_date ON orders(c_id, date);`
Почему: индекс позволит отобрать только нужные по диапазону даты строки и/или быстро найти заказы нужных клиентов; `INCLUDE(id)` или кластерный PK покроет вывод `o.id`.
Итого покрывающие индексы: customers(region, id, name) + orders(date, c_id[, id]) — исключают лишние чтения страниц.
Партиционирование и физическая организация
- Партиционировать orders по дате (RANGE) по месяцам или кварталам, чтобы запрос на период использовал pruning. Пример: диапазон по месяцам для периода ${}^{\prime }2024-01-01^{\prime }$ — ${}^{\prime }2024-06-30^{\prime }$ даст проверку только первых $6$ партиций.
- Для InnoDB/MySQL — рассмотреть CLUSTERED индекс по (date, c_id) (в MySQL кластеризация по PK, значит можно пересмотреть PK если часто нужен по дате; осторожно).
- В PostgreSQL — использовать declarative partitioning: partition by range (date).
Переработка схемы/денормализация
- Добавить в orders поле customer_region (и/или customer_name) и поддерживать триггером/ETL — тогда запрашивать только orders без JOIN. Работает при часто повторяющихся запросах по region.
- Или создать материаализованный VIEW / предагрегированную таблицу orders_by_region_date для быстрых выборок.
Переписать запрос / подсказки планировщику
- Явно фильтровать customers первыми: `FROM customers c JOIN orders o ON o.c_id=c.id WHERE c.region='EU' AND o.date BETWEEN ${}^{\prime }2024-01-01^{\prime }$ AND ${}^{\prime }2024-06-30^{\prime }$` — иногда помогает планировщику делать index-seek по customers и затем по orders.
- Альтернатива: использовать semi-join: `SELECT ... FROM orders o WHERE o.date BETWEEN ${}^{\prime }2024-01-01^{\prime }$ AND ${}^{\prime }2024-06-30^{\prime }$ AND EXISTS (SELECT 1 FROM customers c WHERE c.id=o.c_id AND c.region='EU')` — может уменьшить чтение при больших customers.
- Для MySQL/Postgres можно задать JOIN order hint (если поддерживается) для тестирования.
Оперативные действия и проверки
- Выполните `EXPLAIN` / `EXPLAIN ANALYZE` до/после изменений.
- Соблюдайте актуальность статистики: `ANALYZE` / `VACUUM ANALYZE`.
- Мониторьте селективность: если region низкоселективен (много клиентов в 'EU'), индекс на region хуже; в этом случае ставьте первичным индексом сочетание с id/date.
Краткий план внедрения
1. Создать предложенные индексы (customers: region+id+name; orders: date+c_id).
2. Запустить `ANALYZE` и `EXPLAIN` оригинального запроса.
3. Если всё ещё медленно — добавить партиционирование по date или денормализовать (orders.customer_region).
4. Повторно профилировать и корректировать порядок столбцов в составных индексах по фактической селективности.
Примечание: выбор порядка колонок в составном индексе зависит от фактической селективности для ваших данных — тестируйте варианты `(date,c_id)` vs `(c_id,date)`.