Сұрауларды индекстеу және оңтайландыру

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

Тұрақты сұрыптау және seek-пагинация үшін бірегей құйрық (әдетте '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 Шардарлау

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) нашар жоспарда «жабысуы» мүмкін. Жоспар 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 дана орнына.
'(sort_key, id)' бойынша Seek-пагинациясы терең 'OFFSET' орнына.
Сұрыптау/джойн алдында жинақты лимиттеу (push-down 'LIMIT').

9) Кэштеу және денормализациялау

Query-cache бағдарламасы (кілт = SQL + bind-vars + құқық нұсқасы).
Ауыр агрегаттар үшін Materialized views; ротация/рефреш жоспары.
Денормализация: жиі оқылатын есептелетін өрістерді сақтаңыз (баға жеңілдікті ескере отырып), бірақ консистенттілік үшін триггермен/фондық тапсырмамен.
«Ыстық» кілттер үшін L2 ретінде Redis (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`.
• 'Index Scan' күтілген жерде 'Seq Scan' жоспарларының үлесі.
• БҚБЖ нұсқасын/параметрлерін ауыстырғанда регресс-алерта.

• Шекті slow query log қосыңыз (мысалы, 200 мс).
• Сұрауларды спандармен корреляциялау (trace_id).
• Проблемалық сұраулар жоспарларын алып тастаңыз және ретроспектива үшін объектілік қоймаға сақтаңыз.

• Оқу P95 '<= 150 мс' кезінде 'LIMIT <= 50' және «ыстық» тенанте.
• 1000 жолға дейінгі баташтарда '<= 200 мс' жазба P95.

13) Қауіпсіздік және мульти-тенанттық

Қолжетімділікті бақылау жолдары бойынша индекстер ('tenant _ id', 'owner _ id') міндетті.
(RLS/ABAC) саясаты алдын ала сүзгі болуы тиіс; әйтпесе оңтайландырушы қате жоспарлайды.
Сезімтал өрістерді ашық түрде индекстемеңіз; хэштерді/белгілерді пайдаланыңыз.

14) Қарсы үлгілер

Seek-курсорлық баламасыз терең 'OFFSET'.
«Барлығына бір индекс» - жадының шамадан тыс жүктелуі және write-path.
Сыни жолдарда 'SELECT'.
Функциялық индексі жоқ 'WHERE' бағанының үстіндегі функциялар.
Ескі статистикаға байланысты тұрақсыз жоспарлар.
Тұрақты тәртіпті күту кезінде 'ORDER BY' болмауы.
Индекстер үшін индекстер: ROI <0 қымбат жазба/қолдау себебінен.

15) Енгізу чек-парағы

1. QPS және уақыт бойынша Top-N сұрау → 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, кэширлеу және бақылау. Сипатталған үлгілерді сақтай отырып, сіз деректер мен жүктеменің өсуіне төзімді жылдам, болжамды және үнемді жүйені аласыз.