mufassall

Konspekt · Ma'lumot va saqlash

Bazadagi indekslar

13 daq · O'rta · mufassall.dtwin.uz/uz/l/indexes/

← Darsni ochish

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 Scan va Index Scan farqi
  • 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
Yakuniy sahna
40%35%

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_db

Mermaid: 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_scan

D2

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_db

PlantUML: 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
@enduml

PlantUML: 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
@enduml

Xulosa

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'rsatkichBoshidaOxirida
Sahifa p952 400 ms2 ms
So'rov o'qigan qatorlar40 000 00020
Bir qator yozish0,3 ms0,5 ms
orders'dagi indekslar13

Qadamlar

  1. Muammo Buyurtmalar tarixi 2,4 soniyada ochiladi

    Mijoz «Buyurtmalarim» sahifasini ochadi.

  2. Muammo EXPLAIN butun jadval o'qilganini ko'rsatadi

    EXPLAIN ANALYZE so'rovni bajaradi va baza tanlagan rejani chiqaradi.

  3. Yechim customer_id ustiga B-tree indeks

    Indeks — alohida saralangan tuzilma: customer_id qiymatlarining B-tree daraxti, har biri jadvaldagi o'z qatorlariga ishora qiladi.

  4. Muammo 250 000 buyurtmali ulgurji mijoz

    7 mijoz — 250 000 buyurtmasi bor ulgurji sotuvchi.

  5. Yechim Ikki ustunli bitta indeks

    Composite indeks (customer_id, created_at DESC) avval mijoz bo'yicha, keyin har mijoz ichida sana bo'yicha saralangan.

  6. Muammo Yangi indeks kunlik hisobotga yordam bermaydi

    Kunlik hisobot kechadan beri barcha mijozlarning buyurtmalarini jamlaydi.

  7. Yechim Hisobot so'rovi uchun indeks

    Faqat created_at ustiga indeks qo'shamiz.

  8. Yechim Covering indeks jadvalga umuman tegmaydi

    Composite indeks bilan ham 20 qatorning har biri uchun status va totalni o'qish maqsadida jadvalga sakrash kerak.

  9. Muammo Yettita indeks va import sudraladi

    Bir yil davomida «har ehtimolga qarshi» yana uchta indeks qo'shildi: status, total va buyurtma izohi ustiga.

  10. Yechim Hech kim o'qimaydigan indekslarni o'chirish

    Baza har indeks necha marta ishlatilganini sanaydi.

  11. Xulosa Xulosa

    Rejadan boshlang: EXPLAIN ANALYZE va Rows Removed by Filter qatori 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.