Кратко и по делу — как индексы и план выполнения влияют на скорость, чем диагностировать медленные запросы и какие оптимизации применять.
Как индексы влияют на производительность
- Индекс позволяет выполнить поиск без полного сканирования таблицы, уменьшая количество прочитанных строк: скорость поиска примерно пропорциональна логарифму размера индекса, вместо линейного обхода.
- Полезность индекса зависит от селективности: $\text{selectivity}=\frac{\text{rows matched}}{\text{total rows}}$. Если $\text{selectivity}$ высокая (много строк), последовательный скан может быть быстрее.
- Наличие индекса не гарантирует его использование — решает оптимизатор на основе оценок стоимости плана (I/O vs CPU). План может выбрать индексный скан, последовательный скан, хэш‑джойн или сортировку.
- Стоимость поддержания индекса: каждый INSERT/UPDATE/DELETE обновляет индексы, поэтому операции записи замедляются и увеличивается занимаемое место.
Роль плана выполнения (query plan)
- План показывает, как СУБД реализует запрос: порядок соединений, используемые индексы, типы сканов, оценки и фактические строки.
- Важные признаки проблем: большая разница между оценёнными и фактическими строками (например, $\text{est}=100$, $\text{act}=1000000$), частые диск‑I/O, большие временные файлы на сортировку/хеш, много nested loop при больших входных наборах.
- Оптимизатор минимизирует приблизительную стоимость, обычно выражаемую как комбинацию I/O и CPU: $\text{cost}\approx a\cdot \text{I/O}+b\cdot \text{CPU}$.
Инструменты для диагностики медленных запросов
- Общие: EXPLAIN / EXPLAIN ANALYZE (или SHOW PLAN) — показывает план и реальные затраты; обязательно смотреть фактические значения (ANALYZE).
- PostgreSQL: EXPLAIN ANALYZE, pg_stat_statements (горячие запросы), auto_explain, pgBadger, pgFouine.
- MySQL/MariaDB: EXPLAIN, EXPLAIN ANALYZE (новее), slow query log, pt-query-digest (Percona), performance_schema.
- SQL Server: Actual Execution Plan, Query Store, Extended Events, sys.dm_exec_query_stats / sys.dm_exec_sql_text / sys.dm_exec_query_plan.
- Общие инструменты APM/profilers: New Relic, Datadog, Elastic APM, профайлеры диска/CPU.
- Практика: 1) собрать slow log, 2) воспроизвести с EXPLAIN ANALYZE, 3) сравнить est vs act, I/O, время выполнения, temp files.
Стратегии оптимизации
1) Индексы — какие и когда
- Простые B‑tree индекс для точных равенств и диапазонов.
- Композитные индексы: порядок ключей важен — индекс полезен, если фильтруют/сортируют по префиксу: индекс по (a,b) покрывает WHERE a=... AND b=... и ORDER BY a,b.
- Покрывающие (covering) индексы включают все используемые в SELECT/WHERE/ORDER BY столбцы — позволяет избежать обращения к таблице.
- Частичные/фильтрованные индексы — полезны при низкой доле строк, соответствующих условию.
- Индексы выражений — если в WHERE используется функция, можно индексировать выражение.
- Битовые/GIN/GiST для полнотекстового поиска, JSON, массивов.
- Избегать индексации колонок с очень низкой селективностью (флаги boolean) — индекс неэффективен.
- Учет затрат: каждая вставка/обновление дороже; поддержание индексов увеличивает размер диска.
2) Редизайн запросов
- EXPLAIN ANALYZE → найти горячие места (недооценки, большие вложенные циклы, сортировки).
- Избегать SELECT *; проецировать только нужные столбцы.
- Заменить IN (подзапрос) на EXISTS или JOIN, где это выгодно; сравнивать планы.
- Перенести предикаты в JOIN/WHERE так, чтобы фильтрация происходила как можно раньше.
- Не применять функции к индексируемым столбцам (например, WHERE date_trunc(col) = ... ломает индекс), либо создать индекс выражения.
- Использовать LIMIT, pagination (seek‑pagination, а не OFFSET) для больших наборов.
- Принудительное упрощение сложных CTE: в некоторых СУБД CTE материализуются; переписать в подзапросы или inline.
3) Денормализация и агрегаты
- Денормализация оправдана, если частые запросы требуют соединений нескольких таблиц и ставят узкое место на чтение.
- Варианты: добавление лишних колонок, агрегированных полей, заранее рассчитанных сумм; summary/rollup tables; materialized views с регулярным refresh.
- Трейд‑офф: рост сложности обновлений и риска рассинхронизации; увеличенный объём данных на запись.
4) Шардинг, партиционирование, материализация
- Партиционирование таблиц по дате/ключу уменьшает объем сканируемых данных; индексы должны быть локальными/соответствовать партициям.
- Материализованные представления для тяжёлых агрегатов (с контролем обновления).
- Шардинг/горизонтальное разделение — для очень больших таблиц и распределения нагрузки.
5) Поддержка статистики и конфигурации
- Регулярно запускать ANALYZE/UPDATE STATISTICS, VACUUM (Postgres) — свежая статистика улучшает планирование.
- Настроить параметры планировщика (work_mem, effective_cache_size, random_page_cost и т.д.) в соответствии с железом.
- Следить за планами при параметрическом выполнении (parameter sniffing) — возможно использовать оптимизаторные подсказки или опции recompile/plan hints.
Практический чек‑лист (коротко)
1. Собрать slow log → выбрать горячие запросы.
2. EXPLAIN ANALYZE для каждого: смотреть est vs act, I/O, временные файлы, тип join.
3. Если отсутствует индекс по фильтру/сортировке — добавить правильный (с учётом composite/covering).
4. Переписать запросы: убрать SELECT *, функции на индексах, заменить неэффективные конструкции.
5. При необходимости — добавить материализованные/сводные таблицы или денормализовать.
6. Поддерживать статистику и настроить параметры СУБД.
7. Мониторить после изменений.
Если нужно — могу предоставить краткий чек‑лист команд (EXPLAIN ANALYZE пример для PostgreSQL или MySQL) или рекомендовать конкретные индексы для вашего запроса (пожалуйста, пришлите текст запроса и схему таблицы).