Հարցումների ինդեքսավորումը և օպտիմիզացումը

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), intainment (<>)։

GiST: geo, միջակայքներ, kNN։

2. 4 BRIN (PostgreSQL)

Սուպեր էժան ինդեքսը «բնական տեսակավորված» սեղանների վրա (append-only ժամանակ)։ Լավ է Time սերիաների համար մեծ բրազիլացիների հետ։

2. 5 Bitmap (MySQL/InnoDB: Ոչ նյարդայնորեն; DW-SUBD/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, 108, hashed (շարդինգի համար միատեսակ ձևով)։

3) Կոմպոզիցիայի և կոմպոզիտային ինդեքսների նախագծումը

3. 1 «Ձախ նախածանց»

Ինդեքսում դաշտերի կարգը որոշում է օգտագործելիությունը։

Հարցումը 'WHLS tenium _ id =? AND created_at >=? ORDER BY created_at DESC` → индекс `(tenant_id, created_at DESC, id DESC)`.

3. 2 Tie-breaker

Ավելացրեք յուրահատուկ պոչը (սովորաբար 'id') կայուն տեսակավորման և seek-pagions համար։

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/MSETE»։

Ինդեքսները տեղական կուսակցություններ են, ավելի քիչ B-Tree, ավելի արագ պլանը։

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

4. 2 Կուսակցության բանալին

OLTP-ում '«tenium _ id» (բեռի տեղայնացումը)։

Time-series/OLAP-ում '«ts» (միջակայքային հարցումներ)։

Հիբրիդ ՝ "(tenium _ id, ts) + ենթատիպեր։

4. 3 Շարդինգ

Consistent hashing/range-shard-ը 'tenium _ id' կամ ժամանակի ընթացքում։

Քրոս Շարդի հարցումը wwww.scatter-gather և k-way merge; պահեք per shard cursor.

5) Վիճակագրությունը, կարդինալությունը և պլանները

5. 1 Իրական վիճակագրություն

Միացրեք մեքենայի վերլուծությունը («autovacuuum/autoanium ze»), ավելացրեք «բանաձևը _ statist.ru _ target» «կեղտոտ» բաշխման համար։

5. 2 Ընդլայնված վիճակագրությունը (PG)

Փոխկապակցված սյուներ

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

5. 3 Կատարման պլան

Տե՛ ս 'SNAIN (ANMS ZE, BUFFERS, VERBOSE) "; հիմնական դաշտերը

`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 Պլանների իրականացում

Բևեռացումը (pared statements) կարող է «կպչել» վատ պլանում։ Օգտագործեք plan cache guardrails (PG: «plan _ cache _ mode = force _ custrone _ 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-inae (PG 3512 inlines CTE լռելյայն, բայց «MATERIALIZED» -ը կարող է ամրագրել միջանկյալ արդյունքը, եթե անհրաժեշտ է)։

Մաքրեք 'III III' .ru փոխանցեք դաշտերը (խնայողությունները IO/ցանցեր)։

Փոխանցեք հաշվարկները «WHLS» -ից ինդեքսավորվող ձևի վրա (կանխատեսելի սյունակներ)։

Ագրեգացիաներ ՝ նախնական ընդհանուր սեղաններ/նյութականացված ներկայացումներ, որոնք ունեն համապատասխան իրական նորարարություն։

8) Բատչինգ, սահմանափակում և պագինացիա

Batch-insport/sportate: 500-5000 տուփեր փոխարենը։

Seek-pagination-ը '(sult_ key, id)' խորը 'MSSET-ի փոխարեն։

Հավաքման սահմանափակումը նախքան տեսակավորումը/ջոինը (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

Հիստոգրամների վիճակագրությունը ("ANMS ZE TABLE... UPDATE HISTOGRAM` в 8. 0).

10. 3 ClickHouse

Առաջնային բանալին = տեսակավորում; «ORDER BY (tenrone _ 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 105 դաշտեր; doc _ values տեսակավորման/ագրեգատների համար։

Heap 'ագրեգացիան' heavy; սահմանափակեք «size» -ը և օգտագործեք «composite» միավորումը (էջանոց նմուշը)։

Մի միացրեք վերլուծությունները այնտեղ, որտեղ հարկավոր է ճշգրիտ համեմատություն։

11) Մրցակցությունը, արգելափակումը և MVCC-ը

Կարճ գործարքներ; խուսափեք «երկար» ընթերցումներից '«REPEATABLE READ» առանց կարիքների։

Ինդեքսային վիրահատությունները նաև վերցնում են արգելափակումը (նվազեցնել write throughput)։

Պլանավորեք առցանց ինդեքսավորումը '"CREATE INDEX CONCURNTLY" (PG), "ALGORITHM = INPLACE '/" ONSA" (MYSQL)։

Պոչի մեջ տեղադրված է մեկ ժամ/ID-ը նկարագրում է ինդեքսի «տաք էջերը»։ բաշխեք բանալին (UUIDv7/աղ)։

12) Դիտարկումը և SLO-ն

Մետրիկները

«db _ query _ latency _ 24» (P50/P95/P99) հարցման անվանումով։

`rows_examined`, `rows_returned`, `buffer_hit_ratio`.
`deadlocks`, `lock_wait_ms`, `temp_sort_disk_usage`.

Պլանների մասնաբաժինը «Seq Scan» -ի հետ, որտեղ սպասվում էր «Index Scan»։

Reault-alerts, երբ փոխում է տարբերակը/wwww.D.D.

Լոգա/թրեյսինգ

Միացրեք slow query log-ը շեմով (օրինակ ՝ 200 մզ)։

Հարցումների հարաբերակցությունը սպանների հետ (trace _ id)։

Վերցրեք խնդրահարույց հարցումների պլանները և պահեք օբյեկտի մեջ հետադարձ հայացքի համար։

SLO օրինակ

P95 ընթերցումներ '<= 150 ms' «LIMIT <= 50» և «տաք» tenante։

P95 ձայնագրություններ '<= 200 ms' մինչև 1000 տող։

13) Անվտանգություն և մուլտֆիլմ-տենանտիզմ

Մուտքի վերահսկման ինդեքսները («tenrone _ id», «owner _ id») պարտադիր են։

Քաղաքականությունները (RFC/ABAC) պետք է լինեն ֆիլտրը։ հակառակ դեպքում օպտիմիզատորը պլանավորում է սխալ։

Մի ինդեքսավորեք զգայուն դաշտերը բաց տեսքով։ օգտագործեք հեշեր/հոսանքներ։

14) Anti-patterna

Խորը 'MSSET' առանց seek-կուրսային այլընտրանքի։

«Մեկ ինդեքսը ամեն ինչի վրա» հիշողության ծանրաբեռնվածությունն է և write-path-ը։

«III III» քննադատական ճանապարհներում։

«WHLS» -ի սյունակի վրա գործառույթները առանց ֆունկցիոնալ ինդեքսի։

Անկայուն պլանները հին վիճակագրության պատճառով։

«ORDER BY» -ի բացակայությունը կայուն կարգի սպասելիս։

Ինդեքսները ինդեքսների համար ՝ ROI <0, թանկ ձայնագրման/աջակցության պատճառով։

15) Ներդրման չեկի ցուցակ

1. Top-N հարցումները QPS-ով և ժամանակը պատրաստվում են ընտրել 3-5 թեկնածուներ։

2. «MSAIN ANMS ZE» պլանները, ստուգել vs իրական։

3. Նախագծել ինդեքսները 'դաշտերի կարգը, INCLUDE/partial/functional։

4. Ներդնել կուսակցական մեծ սեղանների համար (ժամանակավոր/տենանտ բանալիներ)։

5. Վերաշարադրել հարցումները 'հեռացնել «MS MS», օգտագործել պարզ CTE-ը, սահմանափակել մի շարք։

6. Միացրեք բատչինգը և seek-pagination։

7. Cash: L1/L2, հաշմանդամություն իրադարձությունների համար։

8. Ներմուծել պլանների և slow-log, ալտերտեր ռեգրեսիայի վրա։

9. Անցկացնել բեռի թեստեր իրական տվյալների բաշխմամբ։

10. Թարմացնել զարգացման համար (ORM-hinta, ինդեքսավորում, limits)։

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», ամրագրեք «sport», օգտագործեք անթափանց հոսանքներ։

Պլան-քաշ 'օգտագործեք բևեռացում; մի ստեղծեք «եզակի» SQL յուրաքանչյուր մարտահրավեր։

18) Մոսկվան և գործողությունը

Ավելացրեք ինդեքսները առցանց և պիտեք որպես MSISIBLE/CONCURRENTLY, փորձարկեք պլանները, ապա անջատեք։

Ինդեքսների իրականացումը կառավարական բյուջետային մաքրում է 'կրկնօրինակներ, չօգտագործված, «մեռած» հին ֆիչի համար։

Կուսակցությունների վերացման պլանը (drop հին) և "VACUUM/OPTIMIZE '2019։

19) Ռեզյումե

Հարցումների օպտիմիզացումը համակարգային ինժեներություն է 'ճիշտ բանալիներ և ինդեքսներ, կոկիկ պլաններ, մտածված խմբաքանակներ և շարդինգ, պահանջների կարգապահություն և ORM, կանխիկացում և դիտարկում։ Եթե պահպանեք փամփուշտները, կստանաք արագ, կանխատեսելի և տնտեսական համակարգ, որը կայուն է տվյալների և բեռի աճի համար։