Кратко и по сути — набор практических методов с пояснениями и примерами.
1) Индексы
- Обязателен индекс по первичному ключу users.id (обычно уже есть).
- На таблице orders:
- Если селективность предиката высокая (мало строк с $amount>100$) — индекс по столбцу amount или покрывающий индекс, который возвращает user_id без чтения строки: например в PostgreSQL/MySQL: `CREATE INDEX idx_orders_amount_user ON orders (amount, user_id);`
- В PostgreSQL лучше частичный (filtered) индекс: `CREATE INDEX idx_orders_user_amount_gt100 ON orders (user_id) WHERE amount > 100;` — индекс используется только для этой выборки.
- Если join чаще по user_id, но фильтр слабый — индекс по user_id: `CREATE INDEX idx_orders_user ON orders (user_id);`
- Крышующие (covering) индексы уменьшают число обращений к таблице (index-only scan).
2) Переписывание запроса
- Семантически эквивалентные формы:
- JOIN vs EXISTS: `SELECT name FROM users u WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id AND o.amount > $100$);` — иногда планировщик выполняет полусоедин (semi-join) эффективнее.
- Предпочитайте выбор только нужных колонок (у вас только name).
- Применяйте фильтр как можно раньше (в логике — фильтрация по orders до join).
3) Hint'ы и примеры
- В MySQL: `STRAIGHT_JOIN`, `USE INDEX`, `FORCE INDEX`.
- В Oracle/Postgres (через расширения): `/*+ LEADING(o) USE_HASH(o u) */` и т.п.
- Хинты полезны при известных проблемах планировщика, но рискуют устареть при изменении данных — использовать осторожно и документировать.
4) Разделение таблиц и денормализация
- Партиционирование (range/hash) по дате/диапазону значений уменьшает чтение при наличии локализованного фильтра; по amount обычно нечасто делают, чаще по дате.
- Вертикальное разделение: вынести часто фильтруемые/часто читаемые колонки (amount, user_id) в отдельную таблицу для уменьшения IO.
- Материализованные представления или предварительно агрегированные таблицы для часто выполняемых тяжёлых запросов.
5) Влияние планировщика и настройки
- Обязательно смотреть план: `EXPLAIN` / `EXPLAIN ANALYZE`.
- Обновляйте статистику: `ANALYZE orders;` — планировщик опирается на актуальные кардинальности.
- Параметры, влияющие на выбор алгоритма:
- память для хеш-джоинов (`work_mem` в Postgres) — если мала, планировщик может выбрать nested loop.
- включение/отключение параллелизма, threshold'ы для merge/hash/nested-loop.
- Если план неверен — проверьте точность статистик, гистограммы распределения, используйте extended statistics для коррелированных колонок.
6) Алгоритмы соединений и когда что лучше
- Nested loop + индекс на users.id хорош при малом числе строк из orders после фильтра.
- Hash join или merge join предпочтительнее, когда много строк после фильтра — требуются достаточная память и/или отсортированные входы.
- Экспериментируйте с разным порядком соединения (join order) — иногда полезно заставить планировщик сначала фильтровать orders.
7) Практический чеклист для оптимизации
- Посмотреть план: `EXPLAIN ANALYZE SELECT ...`
- Оценить кардинальности (сколько строк проходит фильтр).
- Создать/корректировать индекс (частичный или покрывающий).
- Обновить статистику.
- Проверить влияние настройки памяти и включить подходящую стратегию join.
- При необходимости — переписать запрос в EXISTS/IN или использовать hint/материализованный вид.
- Нагрузочное тестирование после изменений.
Заключение: наиболее эффективные шаги для запроса вида `SELECT name FROM users u JOIN orders o ON u.id = o.user_id WHERE o.amount > $100$` — сделать покрывающий или частичный индекс на orders, обеспечить актуальные статистики и затем проверить план (EXPLAIN). Если после этого всё ещё медленно — рассмотреть партиционирование/денормализацию или принудительные хинты и настройку памяти для выбранного типа join.