Індексація та оптимізація запитів

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: малі зовнішні, швидкий індекс на внутрішній.
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, кешування і спостережуваність. Дотримуючись описані патерни, ви отримаєте швидку, передбачувану і економічну систему, стійку до зростання даних і навантаження.