Индексация и оптимизация запросов

1) Цели индексации и оптимизации

Латентность: сокращение P50/P95/P99.
Пропускная способность: рост QPS без горизонтального масштабирования.
Предсказуемость: стабильные планы и отсутствие «скачков» времени ответа.
Экономия: меньше IO/CPU, меньше счета за облако.
Надежность: снижение блокировок и дедлоков за счет правильных доступов.

• Любая оптимизация должна сохранять корректность и согласованность.
• Отслеживать эффект в метриках и логах планов.

2) Базовые структуры индексов и когда их применять

2.1 B-Tree (дефолт)

Равно/диапазоны, сортировки, `ORDER BY`.
Хорош для большинства фильтров по времени/ID/статусу.

2.2 Hash

Чистые равенства (`=`), дешевле по памяти, но без порядка (PG: ограниченности сняты, но все еще нишевый выбор).

2.3 GIN / GiST (PostgreSQL)

GIN: массивы/JSONB ключи, полнотекст (tsvector), containment (`@>`).
GiST: гео, диапазоны, kNN.

2.4 BRIN (PostgreSQL)

Супер-дешевый индекс по «естественно отсортированным» столам (append-only по времени). Хорошо для time-series с большими таблицами.

2.5 Bitmap (MySQL/InnoDB: нет нативно; DW-СУБД/OLAP)

Эффективны для низкой кардинальности и фасетов, чаще в колонночных хранилищах.

2.6 Колонночные индексы (ClickHouse)

Primary key + data skipping (minmax), secondary через `skip indexes` (bloom, set).
OLAP-запросы с агрегациями и диапазонами.

2.7 Инвертированные индексы (Elasticsearch/OpenSearch)

Полнотекст, фасеты, гибридный поиск. Для точных фильтров используйте keyword-поля и doc values.

2.8 MongoDB

Single, compound, multikey (массивы), partial, TTL, text, hashed (для шардинга по равномерному ключу).

3) Проектирование ключей и композитных индексов

3.1 Правило «левого префикса»

Порядок полей в индексе определяет используемость.
Запрос `WHERE tenant_id =? AND created_at >=? ORDER BY created_at DESC` → индекс `(tenant_id, created_at DESC, id DESC)`.

3.2 Tie-breaker

Добавляйте уникальный хвост (обычно `id`) для стабильной сортировки и seek-пагинации.

3.3 Частичные/фильтрованные индексы

sql
CREATE INDEX idx_orders_paid_recent
ON orders (created_at DESC, id DESC)
WHERE status = 'paid' AND created_at > now() - interval '90 days';

3.4 Покрывающие индексы

sql
CREATE INDEX idx_user_lastseen_inc ON users (tenant_id, last_seen DESC) INCLUDE (email, plan);

3.5 Функциональные/вычисляемые

sql
CREATE INDEX idx_norm_email ON users (lower(email));

4) Партиционирование и шардирование

4.1 Партиционирование (PG native/табличное наследование; MySQL RANGE/LIST)

Ротация партиций по времени (`daily/weekly`) упрощает `VACUUM/DELETE`.
Индексы локальны партиции → меньше B-Tree, быстрее план.

sql
CREATE TABLE events (
tenant_id bigint,
ts timestamptz,
...
) PARTITION BY RANGE (ts);

4.2 Ключ партиционирования

В OLTP — по `tenant_id` (локализация нагрузки).
В time-series/OLAP — по `ts` (диапазонные запросы).
Гибрид: `(tenant_id, ts)` + субпартиции.

4.3 Шардирование

Consistent hashing / range-shard по `tenant_id` или по времени.
Кросс-шардовый запрос → scatter-gather и k-way merge; держите per-shard cursor.

5) Статистика, кардинальность и планы

5.1 Актуальная статистика

Включите авто-анализ (`autovacuum/autoanalyze`), увеличьте `default_statistics_target` для «грязных» распределений.

5.2 Расширенная статистика (PG)

sql
CREATE STATISTICS stat_user_country_city (dependencies) ON country, city FROM users;
ANALYZE users;

5.3 План выполнения

• `Rows`, `Loops`, `Actual time`, `Shared Read/Hit`, `Recheck Cond`.
• Типы join: Nested Loop, Hash Join, Merge Join.
• Seq Scan vs Index Scan/Only Scan/Bitmap Heap Scan.

5.4 Стабильность планов

Параметризация (prepared statements) может «залипать» на плохом плане. Используйте plan cache guardrails (PG: `plan_cache_mode = force_custom_plan` для проблемных запросов) или «проброс» констант.

6) Оптимизация join-ов и сортировок

6.1 Стратегии

Nested Loop: малыe внешние, быстрый индекс на внутренней.
Hash Join: большие наборы, хватает памяти под hash table.
Merge Join: отсортированные входы, выгодно при уже имеющемся порядке.

6.2 Индексы под join

Для `A JOIN B ON B.a_id = A.id` → индекс на `B(a_id)`.
Для фильтра после join — индекс на колонках фильтра внутренней таблицы.

6.3 Сортировки

Избегайте `ORDER BY` без соответствующего индекса; сортировка на больших наборах дорога по памяти/диску.

7) Переписывание запросов (query rewrite)

Избавляйтесь от «снежинок» подзапросов; разворачивайте в JOIN.
Используйте CTE-inline (PG ≥12 inlines CTE по умолчанию, но `MATERIALIZED` может зафиксировать промежуточный результат, если нужно).
Убирайте `SELECT ` → перечисляйте поля (экономия IO/сети).
Переносите вычисления из `WHERE` на индексируемую форму (предвычисляемые колонки).
Агрегации: предварительные суммарные таблицы/материализованные представления с инкрементальным обновлением.

8) Батчинг, лимитирование и пагинация

Batch-insert/update: пачки 500–5000 вместо поштучно.
Seek-пагинация по `(sort_key, id)` вместо глубокого `OFFSET`.
Лимитирование набора перед сортировкой/джойном (push-down `LIMIT`).

9) Кэширование и денормализация

Query-cache уровня приложения (ключ = SQL + bind-vars + версия прав).
Materialized views для тяжелых агрегатов; план ротации/рефреша.
Денормализация: храните часто читаемые вычисляемые поля (цена с учетом скидки), но с триггером/фоновой задачей для консистентности.
Redis как L2 для «горячих» ключей (с TTL и инвалидацией по событиям).

10) Специфика популярных движков

10.1 PostgreSQL

Индексы: B-Tree, Hash, GIN/GiST, BRIN, partial, functional, INCLUDE.

sql
CREATE INDEX idx_orders_tenant_created_desc
ON orders (tenant_id, created_at DESC, id DESC)
INCLUDE (amount, status);

sql
CREATE INDEX idx_docs_fts ON docs USING GIN (to_tsvector('russian', title          ' '          body));

10.2 MySQL/InnoDB

sql
ALTER TABLE orders ALTER INDEX idx_old INVISIBLE; -- check risk-free plans

Статистика по гистограммам (`ANALYZE TABLE... UPDATE HISTOGRAM` в 8.0).

10.3 ClickHouse

Первичный ключ = сортировка; `ORDER BY (tenant_id, ts, id)`.

sql
CREATE TABLE events (
tenant_id UInt64,
ts DateTime64,
id UInt64,
payload String,
INDEX idx_bloom_payload payload TYPE bloom_filter GRANULARITY 4
) ENGINE = MergeTree()
ORDER BY (tenant_id, ts, id);

10.4 MongoDB

js db. orders. createIndex({ tenant_id: 1, created_at: -1, _id: -1 });
db. orders. createIndex({ status: 1 }, { partialFilterExpression: { archived: { $ne: true } } });

Используйте `hint()` для диагностики, следите за `covered query`.

10.5 Elasticsearch/OpenSearch

Keyword vs text поля; doc_values для сортировки/агрегатов.
Сегментация heap: агрегации — heavy; ограничивайте `size` и используйте `composite` агрегации (постраничная выборка).
Не включайте анализаторы там, где требуется точное сравнение.

11) Конкурентность, блокировки и MVCC

Короткие транзакции; избегайте «долгих» чтений под `REPEATABLE READ` без нужды.
Индексные операции тоже берут блокировки (снижение write throughput).
Планируйте онлайн-индексацию: `CREATE INDEX CONCURRENTLY` (PG), `ALGORITHM=INPLACE`/`ONLINE` (MySQL).
Вставки в хвост по часу/ID → «горячие страницы» индекса; распределяйте ключ (UUIDv7/соль).

12) Наблюдаемость и SLO

• `db_query_latency_ms` (P50/P95/P99) по названию запроса.
• `rows_examined`, `rows_returned`, `buffer_hit_ratio`.
• `deadlocks`, `lock_wait_ms`, `temp_sort_disk_usage`.
• Доля планов с `Seq Scan` там, где ожидался `Index Scan`.
• Регресс-алерты при смене версии/параметров СУБД.

• Включите slow query log с порогом (например, 200 мс).
• Корреляция запросов со спанами (trace_id).
• Снимайте планы проблемных запросов и сохраняйте в объектное хранилище для ретроспективы.

• P95 чтений `<= 150 мс` при `LIMIT <= 50` и «горячем» тенанте.
• P95 записей `<= 200 мс` при батчах до 1000 строк.

13) Безопасность и мульти-тенантность

Индексы по полям контроля доступа (`tenant_id`, `owner_id`) обязательны.
Политики (RLS/ABAC) должны быть пред-фильтром; иначе оптимизатор планирует неверно.
Не индексируйте чувствительные поля в открытом виде; используйте хэши/токены.

14) Анти-паттерны

Глубокий `OFFSET` без seek-курсорной альтернативы.
«Один индекс на все» — перегрузка памяти и write-path.
`SELECT ` в критичных путях.
Функции над колонкой в `WHERE` без функционального индекса.
Нестабильные планы из-за старой статистики.
Отсутствие `ORDER BY` при ожидании стабильного порядка.
Индексы ради индексов: ROI < 0 из-за дорогой записи/поддержки.

15) Чек-лист внедрения

1. Топ-N запросов по QPS и времени → выбрать 3–5 кандидатов.
2. Снять планы `EXPLAIN ANALYZE`, проверить кардинальность vs фактическая.
3. Спроектировать индексы: порядок полей, INCLUDE/partial/functional.
4. Внедрить партиционирование для больших таблиц (временные/тенантные ключи).
5. Переписать запросы: убрать `SELECT `, инлайнить простые CTE, ограничить набор.
6. Включить батчинг и seek-пагинацию.
7. Настроить кэш: L1/L2, инвалидация по событиям.
8. Ввести мониторинг планов и slow-лог, алерты на регрессы.
9. Провести нагрузочные тесты с реальным распределением данных.
10. Обновить гайдлайны для разработки (ORM-хинты, индексация, лимиты).

16) Примеры «до/после»

sql
SELECT FROM orders
WHERE status = 'paid'
ORDER BY created_at DESC
LIMIT 50 OFFSET 5000;

sql
-- Индекс: (status, created_at DESC, id DESC) INCLUDE (amount, currency)
SELECT id, amount, currency, created_at
FROM orders
WHERE status = 'paid'
AND (created_at, id) < (:last_ts,:last_id)   -- seek
ORDER BY created_at DESC, id DESC
LIMIT 50;

17) ORM и протоколы API

Избегайте N+1: жадные выборки (`includes`, `JOIN FETCH`, `preload`).
Явные проекции полей, paginate курсором.
gRPC/REST: лимитируйте `page_size`, фиксируйте `sort`, используйте непрозрачные токены.
План-кэш: используйте параметризацию; не генерируйте «уникальные» SQL на каждый вызов.

18) Миграции и эксплуатация

Добавляйте индексы онлайн и метите как INVISIBLE/CONCURRENTLY, тестируйте планы, затем переключайте.
Ревизии индексов — регулярная санитарная уборка: дубликаты, неиспользуемые, «мертвые» для старых фич.
План ротации партиций (drop старых) и `VACUUM/OPTIMIZE` расписание.

19) Резюме

Оптимизация запросов — это системная инженерия: правильные ключи и индексы, аккуратные планы, продуманное партиционирование и шардирование, дисциплина в запросах и ORM, кэширование и наблюдаемость. Соблюдая описанные паттерны, вы получите быструю, предсказуемую и экономичную систему, устойчивую к росту данных и нагрузки.