Коротко — почему медленно
- Применение функции к колонке: `YEAR(created_at) = $2020$` делает условие не sargable, индекс по `created_at` не используется → full table scan.
- Подзапрос в `IN (SELECT id FROM customers WHERE active = $1$)` может приводить к плохим планам (многие СУБД выполняют его как скан/мелкие вложенные соединения) — особенно тяжело при большом числе customers/orders.
- Отсутствие подходящих индексов по `created_at` и/или `customer_id` → много I/O.
- `SELECT *` заставляет читать все столбцы/строки, даже если нужны только некоторые поля.
- При очень больших данных без партиционирования и с неактуальными статистиками любая фильтрация медленна.
Практические оптимизации (с примерами)
1) Сделать условие sargable (заменить YEAR)
- Вместо
SELECT * FROM orders WHERE YEAR(created_at) = $2020$ ...
- писать диапазон:
SELECT * FROM orders
WHERE created_at >= ' $2020$-01-01' AND created_at < ' $2021$-01-01' ...
Это позволит использовать индекс по `created_at`.
2) Переписать подзапрос в JOIN или EXISTS
- JOIN (режет лучше, если customers небольшие или индексированы):
SELECT o.* FROM orders o
JOIN customers c ON c.id = o.customer_id AND c.active = $1$ WHERE o.created_at >= ' $2020$-01-01' AND o.created_at < ' $2021$-01-01';
- Или EXISTS (семи-join, часто оптимальнее для проверки наличия):
SELECT o.* FROM orders o
WHERE o.created_at >= ' $2020$-01-01' AND o.created_at < ' $2021$-01-01'
AND EXISTS (SELECT 1 FROM customers c WHERE c.id = o.customer_id AND c.active = $1$);
3) Индексы (проверяйте селективность и порядок колонок)
- Обязателен индекс на foreign key:
CREATE INDEX idx_orders_customer_id ON orders(customer_id);
- Индекс по дате или покрывающий индекс:
CREATE INDEX idx_orders_created_at ON orders(created_at);
— или для фильтра + JOIN: CREATE INDEX idx_orders_cust_date ON orders(customer_id, created_at);
Выбор порядка колонок зависит от того, что более селективно (если сначала фильтруем по дате — ведущим должен быть `created_at`).
- Для customers:
CREATE INDEX idx_customers_active ON customers(active);
(id обычно PK — уже индексирован)
4) Генерируемый столбец / функциональный индекс (если часто фильтруют по году)
- В MySQL можно добавить вычисляемый столбец и индексировать его:
ALTER TABLE orders ADD COLUMN created_year SMALLINT GENERATED ALWAYS AS (YEAR(created_at)) STORED;
CREATE INDEX idx_orders_created_year ON orders(created_year);
Тогда условие `created_year = $2020$` использует индекс.
5) Партиционирование по дате (для очень больших таблиц)
- PARTITION BY RANGE COLUMNS(created_at) VALUES LESS THAN ('2021-01-01'), ... — позволяет ограничить скан только партицией за `$2020$`.
- В MySQL/PG проверьте поддержку и грамотную схему партиций (по годам/месяцам в зависимости от объёма).
6) Выбирать только нужные столбцы, лимитировать выборки
- Замените `SELECT *` на конкретные колонки, чтобы уменьшить I/O.
- Используйте LIMIT / pagination, если нужны фрагменты результата.
7) Денормализация / материализованные представления
- Если запрос очень частый — хранить флаг `customer_active` в orders (обновлять при изменении клиента) или поддерживать материализованный набор active customer ids.
- Или предкомпилировать агрегаты / снэпшоты по годам.
8) Операционная оптимизация и диагностика
- Запустите EXPLAIN / EXPLAIN ANALYZE, смотрите: full table scan, rows, type, possible_keys.
- Анализируйте статистику (ANALYZE TABLE), обновляйте статистику.
- Проверяйте настройки InnoDB (buffer pool) и I/O — часто бутылочное горлышко на уровне диска/памяти.
Короткий чек-лист для быстрого результата
1. Заменить `YEAR(created_at) = $2020$` на диапазон дат.
2. Переписать `IN (SELECT ...)` в JOIN/EXISTS.
3. Создать/проверить индексы: `orders(created_at)`, `orders(customer_id)`, возможно composite.
4. Убрать `SELECT *`, вернуть только нужные поля.
5. При огромных объёмах — партиционировать по дате или использовать materialized view.
Рекомендую: выполнить EXPLAIN для текущего запроса, затем поэтапно применять пункты (диапазон дат → индекс → переписать на JOIN) и снова смотреть план/время.