1) Быстрое профилирование (цель — найти узкое место)
- Запустить реальный медленный запрос с `EXPLAIN` / `EXPLAIN ANALYZE` (Postgres) или профиль план в вашей СУБД. Сравнить оценённые и фактические строки.
- Собрать метрики: время выполнения, I/O (reads), CPU, блокировки, ожидания. В СУБД — планировщик, wait events; в ОС — iostat, vmstat.
- Оценить селективность предикатов: $sel=\frac{\text{matched rows}}{\text{total rows}}$. Если $sel$ очень мал, индекс вероятно полезен.
- Определить тип узкого места: полное сканирование таблицы, дорогие сортировки/джойны, частые случайные чтения, блокировки при записи.
2) Быстрые поправки перед больших изменений
- Обновить статистику (`ANALYZE`, `UPDATE STATISTICS`).
- Проверить актуальность и фрагментацию индексов; выполнить реиндексацию если требуется.
- Уменьшить объём возвращаемых данных: SELECT только нужные колонки, добавить LIMIT/пагинацию.
3) Индексирование (как и когда)
- Выбирать индекс по предикатам WHERE/ JOIN/ ORDER BY: если WHERE содержит $a=?$ и $b>?$, композитный индекс на $(a,b)$ эффективнее двух отдельных.
- Учитывать селективность: индексы полезны при малой $sel$. Для высоко-кардинальных колонок — B-tree; для текстовых поисков — GIN/GIN-trgm/FTS.
- Покрывающие индексы: включить колонки SELECT в индекс (INCLUDE) чтобы избежать доступа к основной таблице.
- Частичные и функциональные индексы: индексы, ограниченные условием или выражением, уменьшают размер и ускоряют специфические запросы.
- Учесть стоимость вставки/обновления: каждая запись поддерживает $k$ индексов, добавляет накладные расходы ~ $O(k)$ на запись.
4) Рефакторинг запроса
- Проверить план джойнов: менять порядок джойнов, используется ли hash/merge/nested-loop, и как влияет на выбор плана.
- Заменить IN (subquery) на EXISTS или JOIN в зависимости от планировщика; для больших наборов часто EXISTS быстрее.
- Избегать функций на индексируемых колонках (неsargable): вместо `WHERE f(col)=const` — хранить предвычисленное значение или создать функциональный индекс.
- Минимизировать агрегирование и сортировки: использовать оконные функции если нужно частичное агрегирование; применять LIMIT до JOIN, если логика позволяет.
- Разбивать тяжёлые запросы на этапы (temp / CTE(materialized или not) / промежуточные таблицы), но помнить, что ненужные materialized CTE могут мешать оптимизатору.
5) Денормализация и материалы агрегирования
- Когда счёт запросов на чтение jauh превышает записи, денормализация (копирование колонок, предагрегированные поля) уменьшает сложность джойнов.
- Поддерживаемые агрегаты: summary tables / rollups / materialized views. Подходы к обновлению:
- периодический (nightly) — прост в реализации, устаревание данных допустимо;
- инкрементный/триггерный —实时нее, сложнее и влияет на запись;
- стриминг через ETL/CDC для больших нагрузок.
- Компромисс: улучшенная производительность чтения vs увеличение сложности записи и попыток обеспечить согласованность.
6) Партиционирование и физическая организация
- Партиционирование по дате/диапазонам уменьшит скан объёма: если запросы ограничены по партиции, план избегает чтения остальных.
- CLUSTER/physically order data по часто используемому индексу уменьшит случайные I/O.
- Сжатие (compression) для больших исторических таблиц уменьшает I/O, но увеличивает CPU.
7) Материалы (кеширование, approximate)
- Кэширование на уровне приложения или Redis для горячих результатов.
- Approximate algorithms (HyperLogLog, sketches) для быстрых приближённых подсчётов, если допустима погрешность.
8) Мониторинг, тестирование и откат
- Ввести тестирование изменений на слепке данных или нагрузочном стенде.
- Измерять latency, throughput, влияние на записи.
- Роллбэк изменений при ухудшении; иметь план для реиндексации/пересчёта материализованных таблиц.
9) Компромиссы консистентности
- При денормализации/материализации возможна задержка обновления: модель согласованности может менятьcя на:
- строгая согласованность (synchronous updates) — быстрый read-consistency, высокая нагрузка на записи;
- eventual consistency (асинхронные обновления) — дешёвая запись, читатели получают устаревшие данные.
- Выбирать стратегию в зависимости от требований: финансовые/транзакционные данные требуют сильной консистентности; аналитика допускает задержки.
- Для критических случаев использовать транзакции/локи/консистентные МВ, для остальных — фоновые обновления и TTL.
10) Резюме действий в порядке приоритета
1. EXPLAIN ANALYZE, собрать метрики, обновить статистику.
2. Быстрые оптимизации: вернуть меньше колонок, убрать функции в WHERE.
3. Добавить/переупорядочить индексы (композитные/покрывающие/частичные).
4. Рефакторить запросы (JOIN/EXISTS/агрегаты).
5. Партиционирование/кластеризация, материализованные агрегаты/денормализация.
6. Внедрить мониторинг и выбрать стратегию согласованности (trade-off чтение/запись).
Если нужно — могу прислать чек-лист команд (EXPLAIN образец, конкретные индексные шаблоны, примеры переписывания запросов) для вашей СУБД.