Konspekt · Ma'lumot va saqlash
Bazadagi indekslar
Muammo
Jadval 40 million qatorga yetgach, buyurtmalar tarixi sahifasi sekinlashadi. So'rov rejasini o'qiymiz, to'g'ri B-tree indekslarni qo'shamiz va har indeks yozishga qancha tushishini ko'ramiz.
Nimani o'rganasiz
EXPLAINni o'qish:Seq ScanvaIndex Scanfarqi- Nega composite indeksda ustunlar tartibi qaysi so'rovga yordam berishini hal qiladi
- Covering indeks jadvalga tegmasdan qanday javob beradi
- Nega har ortiqcha indeks har
INSERTni sekinlashtiradi
Diagramma kod sifatida
Darsdagi yakuniy sxemani matn ko'rinishida oling va Jira, GitLab, GitHub, Notion yoki hujjatga qo'ying: Mermaid u yerlarda o'zi chiziladi. D2 va PlantUML (Confluence, IntelliJ) uchun alohida variantlar bor.
Mermaid: sxema
flowchart LR
n_app(["Do'kon ilovasi"])
n_api["Buyurtmalar API"]
n_db[("Buyurtmalar bazasi")]
n_report("Kunlik hisobot")
n_import("Ommaviy import")
n_app --> n_api
n_api --> n_db
n_report --> n_db
n_import --> n_dbMermaid: so'rovlar ketma-ketligi
sequenceDiagram
participant n_api as Buyurtmalar API
participant n_db as Buyurtmalar bazasi
participant n_report as Kunlik hisobot
participant n_import as Ommaviy import
Note over n_api,n_db: Buyurtmalar tarixi 2,4 soniyada ochiladi
n_api->>n_db: SELECT id, status, total FROM orders
n_db-->>n_api: (20 rows)
Note over n_api,n_db: EXPLAIN butun jadval o'qilganini ko'rsatadi
n_api->>n_db: EXPLAIN ANALYZE
n_db-->>n_api: Limit (actual rows=20)
Note over n_api,n_db: customer_id ustiga B-tree indeks
n_api->>n_db: CREATE INDEX CONCURRENTLY idx_orders_customer
n_db-->>n_api: CREATE INDEX
Note over n_api,n_db: 250 000 buyurtmali ulgurji mijoz
n_api->>n_db: EXPLAIN ANALYZE
n_db-->>n_api: Limit (actual rows=20)
Note over n_api,n_db: Ikki ustunli bitta indeks
n_api->>n_db: EXPLAIN ANALYZE
n_db-->>n_api: Limit (actual rows=20)
Note over n_report,n_db: Yangi indeks kunlik hisobotga yordam bermaydi
n_report->>n_db: EXPLAIN ANALYZE SELECT count(*), sum(total) FROM orders
n_db-->>n_report: Aggregate
Note over n_report,n_db: Hisobot so'rovi uchun indeks
n_report->>n_db: EXPLAIN ANALYZE SELECT count(*), sum(total) FROM orders
n_db-->>n_report: Aggregate
Note over n_api,n_db: Covering indeks jadvalga umuman tegmaydi
n_api->>n_db: EXPLAIN ANALYZE
n_db-->>n_api: Limit (actual rows=20)
Note over n_import,n_db: Yettita indeks va import sudraladi
n_import->>n_db: INSERT INTO orders (customer_id, created_at, status, total)
n_db-->>n_import: INSERT 0 500
Note over n_api,n_db: Hech kim o'qimaydigan indekslarni o'chirish
n_api->>n_db: SELECT indexrelname, idx_scan
n_db-->>n_api: indexrelname #124; idx_scanD2
direction: right
n_app: "Do'kon ilovasi" {
shape: rectangle
}
n_api: "Buyurtmalar API" {
shape: rectangle
}
n_db: "Buyurtmalar bazasi" {
shape: cylinder
}
n_report: "Kunlik hisobot" {
shape: rectangle
style.multiple: true
}
n_import: "Ommaviy import" {
shape: rectangle
style.multiple: true
}
n_app -> n_api
n_api -> n_db
n_report -> n_db
n_import -> n_dbPlantUML: so'rovlar ketma-ketligi
@startuml
participant "Buyurtmalar API" as n_api
database "Buyurtmalar bazasi" as n_db
participant "Kunlik hisobot" as n_report
participant "Ommaviy import" as n_import
note over n_api, n_db : Buyurtmalar tarixi 2,4 soniyada ochiladi
n_api -> n_db : SELECT id, status, total FROM orders
n_db --> n_api : (20 rows)
note over n_api, n_db : EXPLAIN butun jadval o'qilganini ko'rsatadi
n_api -> n_db : EXPLAIN ANALYZE
n_db --> n_api : Limit (actual rows=20)
note over n_api, n_db : customer_id ustiga B-tree indeks
n_api -> n_db : CREATE INDEX CONCURRENTLY idx_orders_customer
n_db --> n_api : CREATE INDEX
note over n_api, n_db : 250 000 buyurtmali ulgurji mijoz
n_api -> n_db : EXPLAIN ANALYZE
n_db --> n_api : Limit (actual rows=20)
note over n_api, n_db : Ikki ustunli bitta indeks
n_api -> n_db : EXPLAIN ANALYZE
n_db --> n_api : Limit (actual rows=20)
note over n_report, n_db : Yangi indeks kunlik hisobotga yordam bermaydi
n_report -> n_db : EXPLAIN ANALYZE SELECT count(*), sum(total) FROM orders
n_db --> n_report : Aggregate
note over n_report, n_db : Hisobot so'rovi uchun indeks
n_report -> n_db : EXPLAIN ANALYZE SELECT count(*), sum(total) FROM orders
n_db --> n_report : Aggregate
note over n_api, n_db : Covering indeks jadvalga umuman tegmaydi
n_api -> n_db : EXPLAIN ANALYZE
n_db --> n_api : Limit (actual rows=20)
note over n_import, n_db : Yettita indeks va import sudraladi
n_import -> n_db : INSERT INTO orders (customer_id, created_at, status, total)
n_db --> n_import : INSERT 0 500
note over n_api, n_db : Hech kim o'qimaydigan indekslarni o'chirish
n_api -> n_db : SELECT indexrelname, idx_scan
n_db --> n_api : indexrelname | idx_scan
@endumlPlantUML: komponentlar
@startuml
left to right direction
agent "Do'kon ilovasi" as n_app
component "Buyurtmalar API" as n_api
database "Buyurtmalar bazasi" as n_db
collections "Kunlik hisobot" as n_report
collections "Ommaviy import" as n_import
n_app --> n_api
n_api --> n_db
n_report --> n_db
n_import --> n_db
@endumlXulosa
Rejadan boshlang: EXPLAIN ANALYZE va Rows Removed by Filter qatori baza aslida nima qilayotganini ko'rsatadi.
1) B-tree indeks to'liq o'qishni daraxt bo'ylab yurishga aylantiradi. 2) Composite indeksda ustunlar tartibi muhim: avval filtrlar, keyin saralash ustuni. 3) INCLUDE uni covering qiladi va jadval o'qilmaydi. 4) Har indeks har yozishga tushadi: idx_scanni tekshiring va hech kim ishlatmaydiganini o'chiring.
Ko'rsatkichlar: boshida va oxirida
| Ko'rsatkich | Boshida | Oxirida |
|---|---|---|
| Sahifa p95 | 2 400 ms | 2 ms |
| So'rov o'qigan qatorlar | 40 000 000 | 20 |
| Bir qator yozish | 0,3 ms | 0,5 ms |
| orders'dagi indekslar | 1 | 3 |
Qadamlar
- Muammo Buyurtmalar tarixi 2,4 soniyada ochiladi
Mijoz «Buyurtmalarim» sahifasini ochadi.
- Muammo EXPLAIN butun jadval o'qilganini ko'rsatadi
EXPLAIN ANALYZEso'rovni bajaradi va baza tanlagan rejani chiqaradi. - Yechim customer_id ustiga B-tree indeks
Indeks — alohida saralangan tuzilma:
customer_idqiymatlarining B-tree daraxti, har biri jadvaldagi o'z qatorlariga ishora qiladi. - Muammo 250 000 buyurtmali ulgurji mijoz
7mijoz — 250 000 buyurtmasi bor ulgurji sotuvchi. - Yechim Ikki ustunli bitta indeks
Composite indeks
(customer_id, created_at DESC)avval mijoz bo'yicha, keyin har mijoz ichida sana bo'yicha saralangan. - Muammo Yangi indeks kunlik hisobotga yordam bermaydi
Kunlik hisobot kechadan beri barcha mijozlarning buyurtmalarini jamlaydi.
- Yechim Hisobot so'rovi uchun indeks
Faqat
created_atustiga indeks qo'shamiz. - Yechim Covering indeks jadvalga umuman tegmaydi
Composite indeks bilan ham 20 qatorning har biri uchun
statusvatotalni o'qish maqsadida jadvalga sakrash kerak. - Muammo Yettita indeks va import sudraladi
Bir yil davomida «har ehtimolga qarshi» yana uchta indeks qo'shildi:
status,totalva buyurtma izohi ustiga. - Yechim Hech kim o'qimaydigan indekslarni o'chirish
Baza har indeks necha marta ishlatilganini sanaydi.
- Xulosa Xulosa
Rejadan boshlang:
EXPLAIN ANALYZEvaRows Removed by Filterqatori baza aslida nima qilayotganini ko'rsatadi.
Atamalar
- Indeks
- Baza qatorlarni butun jadvalni o'qimasdan topishi uchun alohida saralangan tuzilma, odatda B-tree. Composite indeksda ustunlar tartibi muhim, har indeks esa har yozuvni sekinlashtiradi.