Кратко и по делу — план диагностики и список оптимизаций с критериями выбора.
1) Диагностика (обязательно, прежде чем менять что‑то)
- Получить план выполнения: `EXPLAIN ANALYZE` (+`BUFFERS` в PostgreSQL) или эквивалент в MySQL. Сравнить estimated vs actual rows — большая расхoдка (например $10\times$ и больше) указывает на некорректную статистику.
- Смотреть: наличие последовательных сканов (Seq Scan), Nested Loop над большими наборами, heap fetches / random IO, количество прочитанных блоков/строк, время CPU vs IO, ожидания блокировок.
- Логи: slow query log / pg_stat_statements — частота и аггрегация по тексту запроса.
- Статистика таблицы: количество строк, распределение значений, NULLs, cardinality. Выполнить `ANALYZE`/`OPTIMIZE` если статистика устарела.
- Простая профилировка: запуск запроса с `LIMIT`/проверка отдельных частей (фильтров, join'ов, агрегатов) чтобы локализовать узкое место.
2) Индексы — когда и какие применять
- Когда: план показывает Seq Scan по большой таблице и фильтр/join использует столбцы с высокой селективностью (малая доля строк). Обычно индекс полезен при селективности менее $5\%-10\%$ ($5\%$-$10\%$).
- Какие:
- Простые B‑tree на колонки в WHERE/JOIN/ORDER BY/GROUP BY (учтите порядок для составных индексов — leftmost prefix).
- Составные индексы, если запрос фильтрует/сортирует по нескольким колонкам вместе; порядок колонок по селективности и использованию в предикате/сортировке.
- Покрывающие индексы (include / index-only): включить в индекс все колонки, которые выбираются, чтобы избежать обращений в таблицу.
- Частичные (filtered) индексы для часто встречающихся условий (например `status = 'active'`).
- Функциональные/expression индексы, если запрос использует функцию над колонкой (но лучше сделать предикат sargable).
- Ограничения: индексы замедляют записи/обновления и занимают диск; не индексировать низкоселективные колонки (флаги, булевы). Тестировать на staging.
3) Рефакторинг запроса — что менять и когда
- Минимизировать выбираемые колонки (убрать `SELECT *`).
- Избегать функций/операций на индексируемых колонках в WHERE (например `WHERE LOWER(col) = 'x'` → создать functional index или хранить нормализованное поле).
- JOIN vs subquery: иногда `EXISTS` быстрее, иногда `JOIN` с агрегацией/UNIQUE лучше — смотреть план.
- Переписать OR в WHERE на UNION/UNION ALL с индексированными предикатами, если OR препятствует использованию индекса.
- Упростить сложные CTE: в PostgreSQL CTE по умолчанию материализуются (в версии <12) — возможно лучше инлайн или использовать временные таблицы.
- Использовать агрегаты/параллелизм: иногда разбить тяжёлую агрегацию на этапы с временной таблицей/батчами.
- Настройки памяти/параллелизма (например `work_mem`, `max_parallel_workers_per_gather`) могут изменить план (hash join vs nested loop).
4) Денормализация и материализованные данные — когда выбирать
- Выбирать, когда запросы чтения составляют подавляющее большинство и время реального отклика важнее целостности данных (OLTP read‑heavy, OLAP).
- Подходы:
- Денормализация (дублирование колонок) — уменьшает количество JOIN'ов, ускоряет чтение; требует механизма обновления (триггеры, ETL, application logic).
- Материализованные представления / агрегаты — предвычислять сложные агрегаты/джойны и регулярно обновлять/инкрементировать.
- Кэширование (Redis, memcached) для самых горячих результатов.
- Ограничения: сложнее поддерживать консистентность, увеличивается сложность обновлений; подходит при явном выигрышe по latency и принятии eventual consistency.
5) Другие инструменты и приёмы
- Партиционирование (range/hash/list) — если таблица очень большая по времени/ключу и запросы ограничены партицией → активируется partition pruning.
- Вертикальное разделение (отдельная таблица для редко используемых широких колонок).
- CLUSTER / REORGANIZE / VACUUM для уменьшения случайных чтений (особенно для таблиц, где порядок по индексу важен).
- Мониторинг IO/CPU, масштабирование HW или реплика чтений (read replicas) для распределения нагрузки.
- Тестировать изменения на копии данных и измерять: latency, throughput, план.
6) Рекомендованный рабочий процесс (практически)
- Диагностика → репродукция на тесте → small changes (statistics, hints, query rewrite) → измерение → если не помогают — индекс(ы) тестировать → если частые чтения и сложные агрегаты — материализовать/денормализовать → следить за стоимостью обновлений.
- Всегда мерить до и после (EXPLAIN ANALYZE, метрики сервера), хранить примеры запросов и тестовые данные.
Если хотите, пришлите текст конкретного запроса и структуру таблицы (DDL + пример `EXPLAIN ANALYZE`) — дам конкретные рекомендации по индексам и переписке.