Индекстөө жана суроо оптималдаштыруу

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 убакыт). Чоң таблицалар менен убакыт сериялары үчүн жакшы.

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

Туруктуу сорттоо жана seek-pagination үчүн уникалдуу куйругун (адатта 'id') кошуу.

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 Sharding

Consistent hashing/range-shard 'tenant _ id' же убакыт боюнча.
Cross-shard суроо → scatter-gather жана k-way merge; per-shard cursor.

5) Статистика, кардиналдуулук жана пландар

5. 1 Актуалдуу статистика

Auto-талдоо ('autovacuum/autoanalyze'), көбөйтүү 'default _ statistics _ target' үчүн "кир" бөлүштүрүү.

5. 2 Advanced Статистика (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) начар планда "жабышып" мүмкүн. Place cache guardrails (PG: 'plan _ cache _ mode = force_custom_plan' көйгөйлүү суроолор үчүн) же константа "бурулуш".

6) оптималдаштыруу join жана сорттоо

6. 1 Стратегиялар

Nested Loop: чакан тышкы, ички тез индекси.
Hash Join: чоң топтомдор, хаш стол үчүн жетиштүү эс.
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)

"Кар" podsoprosov кутулуу; JOIN.
Колдонуңуз CTE-inline (PG ≥ 12 inlines CTE демейки, бирок 'MATERIALIZED' керек болсо, ортодогу натыйжаны оңдой алат).
'SELECT' → талааларды тизмектөө (IO/тармакты үнөмдөө).
Эсептөөлөрдү "WHERE" дегенден индекстелген формага которуңуз.
Агрегациялар: алдын ала суммардык таблицалар/инкременталдык жаңылануу менен материалдаштырылган көрүнүштөр.

8) Батчинг, лимиттөө жана пагинация

Batch-insert/update: пакеттер 500-5000 ордуна бирден.
Seek-pagination '(sort_key, id)' ордуна терең 'OFFSET'.
сорттоо/джойн алдында топтомун чектөө (push-down 'LIMIT').

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

Query-cache деңгээл колдонмо (ачкыч = SQL + bind-vars + версия укугу).
Оор агрегаттар үчүн Materialized views; rotation/refresh планы.
Denormalization: көп эсептелген талаалар (арзандатууну эске алуу менен баасы), ал эми туруктуу үчүн триггер/арткы милдети менен сактоо.
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 сегментациясы: агрегациялар - оор; '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 ms).
• Span менен байланышуу (trace_id).
• Көйгөйлүү суроо-талаптардын пландарын алып салыңыз жана ретроспектива үчүн объекттин сактагычында сактаңыз.

• Окуу P95 '<= 150 ms' менен 'LIMIT <= 50' жана "ысык" тенанте.
• P95 жазуулар '<= 200 ms' 1000 сапка чейин батч менен.

13) Коопсуздук жана көп тенанттуулук

Жеткиликтүүлүктү контролдоо талаалары боюнча индекстер ('tenant _ id', 'owner _ id') милдеттүү.
Саясат (RLS/ABAC) алдын ала чыпкасы болушу керек; антпесе оптимизатор туура эмес пландаштырууда.
Ачык сезгич талааларды индексациялабаңыз; хэш/токендерди колдонуңуз.

14) Анти-үлгүлөрү

Терең 'OFFSET' Seek-курсордук альтернативасы жок.
"Бардыгы үчүн бир индекс" - эс жана write-path ашыкча жүктөө.
критикалык жолдордо 'SELECT'.
Функциялык индекси жок 'WHERE' тилкесиндеги функциялар.
Эски статистикага байланыштуу туруксуз пландар.
Туруктуу тартипти күтүүдө 'ORDER BY' жок.
Индекстер үчүн индекстер: ROI <0 Анткени кымбат жазуу/колдоо.

15) Киргизүү чек-тизмеси

1. Top-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 сыяктуу белгилөө, пландарды сыноо, андан кийин которуу.
Индекстердин ревизиялары - үзгүлтүксүз санитардык тазалоо: дубликаттар, пайдаланылбаган, эски көрүнүштөр үчүн "өлүк".
Партиялардын айлануу планы (эски) жана 'VACUUM/OPTIMIZE' тартиби.

19) Резюме

Суроо-талаптарды оптималдаштыруу - бул системалык инженерия: туура ачкычтар жана индекстер, тыкан пландар, ойлонулган партиялаштыруу жана шардана кылуу, суроо-талаптардагы тартип жана ORM, кэширование жана байкоо жүргүзүү. Сүрөттөлгөн үлгүлөрдү сактоо менен, сиз маалыматтарды жана жүктү өсүшүнө туруктуу тез, алдын ала жана үнөмдүү системаны аласыз.