Коротко: основная идея — дать оптимизатору эффективный путь соединения таблиц (правильные индексы + подходящая форма запроса), проанализировать план выполнения и выбрать подходящий алгоритм (nested loop / hash / merge). Ниже — конкретные рекомендации и примеры.
1) Переписывание запроса
- Текущий:
SELECT * FROM orders WHERE customer_id IN (SELECT id FROM customers WHERE active = $1$)
- Эквивалент с JOIN (обычно читабельнее, позволяет оптимизатору выбрать join-алгоритм):
SELECT o.* FROM orders o JOIN customers c ON c.id = o.customer_id WHERE c.active = $1$ - Эквивалент с EXISTS (часто оптимален для семи-join, избегает потенциального дублирования):
SELECT o.* FROM orders o WHERE EXISTS (SELECT 1 FROM customers c WHERE c.id = o.customer_id AND c.active = $1$)
Примечание: если customers.id — первичный ключ, JOIN не должен давать дубликатов. Если нет — EXISTS защищает от дублирования.
2) Индексы
- Обязательный минимум:
- индекс на orders(customer_id): ускоряет поиск по внешнему ключу.
- customers.id обычно уже PK; он нужен для быстрого поиска по id.
- Если фильтр по active селективен (мало активных клиентов):
- индекс на customers(active) или — лучше — частичный индекс (Postgres):
CREATE INDEX ON customers (id) WHERE active = true
(частичный индекс полезен, если вы часто запрашиваете только active = true и таких строк мало).
- Покрывающие индексы для index-only scan: если вы выбираете немного колонок из orders, можно создать составной индекс, например orders(customer_id, status, created_at), чтобы избежать доступа к таблице.
- Учтите: индекс на булевом поле с низкой селективностью бесполезен; частичный индекс при небольшом подмножестве эффективен.
3) План выполнения и выбор алгоритма
- Типы join-алгоритмов:
- Nested Loop: эффективен, если внешний набор маленький; использует индекс на внутренней таблице.
- Hash Join: выгоден при больших наборах; строит хеш на меньшей таблице; требует памяти.
- Merge Join: хорош, когда оба набора отсортированы по ключу или есть подходящие индексы.
- Что смотреть в EXPLAIN / EXPLAIN ANALYZE:
- estimated cost vs actual time/rows (если actual значительно отличается — статистика устарела);
- node type (Seq Scan, Index Scan, Nested Loop, Hash Join, Merge Join);
- number of rows и loops;
- buffers / I/O (в Postgres: EXPLAIN (ANALYZE, BUFFERS)).
- Примеры команд:
- Postgres: EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) SELECT ...
- MySQL: EXPLAIN SELECT ...; (в 8.0+) EXPLAIN ANALYZE SELECT ...
- Если план показывает Seq Scan по большой таблице — добавьте индекс/частичный индекс или приведите статистику (ANALYZE).
4) Практические советы и порядок действий
1. Убедитесь, что есть индекс orders(customer_id).
2. Проверьте, насколько селективен фильтр customers.active. Если селективен — добавьте индекс/частичный индекс.
3. Попробуйте переписать запрос через EXISTS или JOIN и сравните планы (EXPLAIN ANALYZE). Многие СУБД превращают IN(subquery) в semi-join, но не всегда оптимально.
4. Сведите к минимуму SELECT * — выбирайте только нужные колонки (позволяет покрывающий индекс и меньше I/O).
5. Если Hash Join слишком медленный из‑за памяти, увеличьте work_mem / join_buffer_size или смените стратегию (merge/nested).
6. Если таблицы очень большие и запросы частые — рассмотрите партиционирование orders по дате или customer_id.
5) Когда что выбирать (кратко)
- Маленькое число заказов по сравнению с customers: drive from orders + index lookup в customers (nested loop).
- Большие обе таблицы: hash join или merge join (в зависимости от памяти и сортировки).
- Если хотите гарантировать отсутствие дубликатов и не зависеть от уникальности — EXISTS.
6) Заключение
- Начните с индекса на orders(customer_id) и анализа EXPLAIN ANALYZE.
- Перепишите запрос в JOIN/EXISTS, если IN даёт плохой план.
- Подберите дополнительные индексы (включая частичные и покрывающие), оптимизируйте память для join-операций и следите за актуальностью статистики (ANALYZE).
Если нужно, могу предложить конкретные SQL-команды оптимизации и пример сравнения планов для вашей СУБД (Postgres / MySQL) и объёмов данных.