Кратко и по делу — план анализа проблемы, конкретные приёмы оптимизации и обсуждение компромиссов CAP.
1) Быстрое профилирование (что собрать)
- Собрать метрики: QPS, p95/p99 latency, CPU, RAM, IO, диск/сеть.
- Логи медленных запросов и агрегаторы: PostgreSQL `EXPLAIN (ANALYZE, BUFFERS)`, `pg_stat_statements`, `pgBadger`; MySQL slow log, `pt-query-digest`.
- Профилировать планы: сравнить оценки планировщика и реальные строки (ошибка оценки → плохой план).
- Проверить блокировки/контеншен: `pg_locks` / `INFORMATION_SCHEMA.PROCESSLIST`, wait events, lock timeouts.
2) Анализ фрагмента запроса и плана (шаги)
- Выполнить `EXPLAIN ANALYZE` и посмотреть:
- какие операции самые дорогие (Seq Scan vs Index Scan vs Hash Join vs Sort);
- сколько строк фактически возвращается vs оценено.
- Проверить фильтры и JOIN-порядок; выяснить, какие столбцы в WHERE / JOIN / ORDER BY / GROUP BY используются чаще всего.
- Оценить селективность фильтров: $\text{selectivity}=\frac{\text{matching rows}}{\text{total rows}}$. Если $\text{selectivity}$ высокая (много совпадений), индекс может быть невыгоден.
3) Индексирование — рекомендации
- Индексируйте колонки, которые используются в WHERE / JOIN / ORDER BY / GROUP BY.
- Предпочтение составным индексам для запросов с множественными условиями: если запрос WHERE a = ? AND b = ?, лучше $\text{idx}(a,b)$, а не отдельные $\text{idx}(a)$ и $\text{idx}(b)$.
- Для покрытия запроса используйте covering/index-only: включите в индекс возвращаемые столбцы, чтобы избежать доступа к таблице: $\text{idx}(a,b)\text{INCLUDE}(c)$ (Postgres) или $\text{idx}(a,b,c)$.
- Частичные/условные индексы, если фильтр постоянен: индексировать только $\text{WHERE active = true}$.
- Функциональные индексы для выражений: индекс на $\text{lower(email)}$.
- Оценка индекса: индекс полезен при низкой селективности; грубое правило: если ожидаемо меньше ~$10\%$ строк — индекс обычно полезен (зависит от размера строки/страницы).
4) Переписывание запросов и планы
- Убрать SELECT *; выбрать только нужные колонки.
- Ограничить набор строк ранними фильтрами; вынести агрегации в предварительные фильтры/материализованные представления.
- Избегать ненужных сложных CTE в PostgreSQL (раньше CTE выполнялись как оптимизационный барьер).
- Использовать правильный JOIN-тип; при больших таблицах избегать циклических вложенных циклов.
5) Денормализация / материализованные представления
- Денормализовать горячие данные для чтения (дублировать поля, предвычисленные агрегаты) — снижает время чтения, увеличивает сложность записи и потребление места.
- Использовать материализованные представления с периодическим рефрешем или инкрементальными обновлениями (для тяжелых агрегаций).
- Компромисс: быстрее чтения, больше задержки/сложность при записи и необходимость решать актуальность данных (возможно eventual consistency).
6) Репликация и шардирование
- Горизонтальное масштабирование:
- Read replicas: разгрузка чтений, но лаг репликации → eventual consistency для читающих.
- Шардирование по ключу (обычно user_id) для равномерного распределения: hash-shard или range-shard.
- Шардирование даёт линейное увеличение write/read capacity, но:
- кросс-шардовые JOIN/транзакции сложны или дорогие;
- управление rebalancing и распределённые транзакции (2PC) повышают сложность и уменьшают доступность/производительность.
- Если heavy writes по одному пользователю — шардинг по user_id хороший выбор; для графовых/связанных данных — продумать модель.
7) Компромиссы согласованности и доступности (CAP)
- CAP в краткой форме: при разделении сети нельзя одновременно гарантировать Consistency (C) и Availability (A) — нужно выбрать.
- Практически:
- Если система выбирает C (Consistency + Partition tolerance = CP): при расколе часть сервисов может стать недоступной, но данные будут согласованными (пример: банковские транзакции).
- Если выбирает A (Availability + Partition tolerance = AP): сервис остаётся доступным, но данные могут быть несогласованы в краткий период (eventual consistency), что подходит для фидов/кешей.
- Выбор зависит от домена: критичные транзакции → выбирать C; социальные/уведомления → чаще A.
8) Пошаговый план действий (минимальный набор)
1. Снять метрики и медленные запросы, baseline.
2. Для 5–10 самых дорогих запросов: EXPLAIN ANALYZE, посмотреть план и несоответствие оценок.
3. Поправить статистику (ANALYZE), добавить/поправить индексы (составные/частичные/функциональные).
4. Переписать проблемные запросы (убрать SELECT *, снизить объем данных, добавить LIMIT, использовать индекс-ориентированные фильтры).
5. Если чтения доминируют — поставить реплики; если рост записей/пользователей продолжается — проектировать шардинг.
6. При необходимости — денормализация или материализованные представления для горячих путей.
7. Наблюдать, проводить A/B для изменений, мониторить lag и ошибки согласованности.
Короткие практические советы
- Всегда проверяйте реальные строки в EXPLAIN (actual rows) vs estimated. Большие расхождения → статистика/кардинальность.
- Не добавляйте индексы «про запас» — индекс замедляет вставки/обновления и занимает место.
- Используйте профайлер БД и APM, чтобы отделить проблему БД от приложения (например, повторные запросы, N+1).
Если нужно, пришлите конкретный фрагмент SQL и схему таблиц (DDL): я разберу план, укажу конкретные индексы и вариант шардинга.