Kaip optimizuoti lėtas agregavimo užklausas didelėje lentelėje?

Question

IT Interview Practice · Accepted Answer

Optimizuodami lėtą agregavimą pirmiausia nustatykite, **ką reiškia rezultatas ir kuri operacija sunaudoja laiką**. Agregavimas sujungia eilutes į suvestinę, pavyzdžiui, pardavėjo pajamas. Kaina priklauso nuo skaitomų ir jungiamų duomenų kiekio.

Tarkime, skydelyje rodomos praėjusio mėnesio pajamos. Šiuo pavyzdžiu nuspręskite, ar gerinti užklausą, saugoti iš anksto apskaičiuotą suvestinę, ar ataskaitas perkelti kitur.

## 1. Prieš spartindami patvirtinkite teisingumą

Išsiaiškinkite, ar pajamos reiškia apmokėtus, ar sukurtus užsakymus, kaip tvarkomi grąžinimai ir kokios valiutos įtrauktos. Paklauskite, kiek nauji turi būti duomenys. Tai keičia ir užklausą, ir galimas optimizacijas.

Dažna klaida — sujungti mokėjimą su keliomis užsakymo pozicijomis ir tada sumuoti mokėjimo sumą:

```text
[Payment: 100] -> [Join with 3 items] -> [100, 100, 100]
                                               |
                                          [SUM = 300]
```

Skaičiai iliustraciniai. Užklausa padaugino mokėjimą, todėl spartinimas tik greičiau pateiktų klaidingą sumą. Prieš vertindami našumą agreguokite numatytu verslo lygmeniu arba pakeiskite jungimą.

## 2. Raskite brangią vykdymo dalį

Vykdymo planas parodo rezultatui gauti naudojamas operacijas. Tikrinkite brangius žingsnius ir jų įvesties dydžius, užuot manę, kad tereikia dar vieno indekso.

| Pastebėjimas | Tiriamas klausimas |
| --- | --- |
| Skaitoma daug nereikalingų eilučių | Ar filtrai arba particijų atmetimas gali jų išvengti? |
| Tikras eilučių skaičius labai skiriasi nuo įverčio | Ar statistika atspindi šiuos duomenis? |
| Jungimas daug kartų kartoja darbą | Ar įvestis per didelė, ar prieigos kelias netinkamas? |
| Grupavimas ar rūšiavimas rašo laikinus duomenis į diską | Ar prieš didinant atmintį galima sumažinti įvestį? |

PostgreSQL EXPLAIN (ANALYZE, BUFFERS) iš tikrųjų vykdo užklausą ir pateikia vykdymo informaciją. Brangų tyrimą atlikite kontroliuojamoje aplinkoje arba pagal sąmoningai skirtą produkcinį biudžetą. [PostgreSQL EXPLAIN](https://www.postgresql.org/docs/current/using-explain.html).

## 3. Sumažinkite darbą dabartinėje bazėje

Skydeliui pasirinkite reikiamą mėnesį ir stulpelius. Patikrinkite, ar planas tikrai praleidžia nesusijusias particijas ir ar jungimai neįtraukia suvestinei nereikalingų duomenų.

Indeksas naudingas, jei sutaupo pakankamai skaitymo. Kai ataskaitai reikia daugumos eilučių, nuoseklus skaitymas gali būti tinkamas. Vertinkite planą ir išmatuotą kainą, ne vien indekso buvimą.

Jei rūšiavimas persikelia į diską, gali pakakti sumažinti įvestį. Padidinus atmintį kiekvienai užklausai, vienu metu vykdomos ataskaitos gali išnaudoti RAM. Bandykite su reprezentatyvia lygiagrečia apkrova.

## 4. Nuspręskite, ar rezultatą ruošti iš anksto

Jei daug naudotojų prašo tų pačių sumų ir vėlavimas priimtinas, nuolat skaityti neapdorotus užsakymus neefektyvu. Suvestinės lentelėje galima saugoti sumas pagal dieną, pardavėją ir valiutą, o skydelyje jungti mažesnius rezultatus.

```text
[Orders] -> [Scheduled / incremental calculation]
                              |
                       [Daily summaries] -> [Dashboard]
```

Nauda atsiranda bendrus skaičiavimus atliekant vieną kartą, o ne per kiekvieną užklausą. Materializuotas rodinys taip pat saugo užklausos rezultatą, tačiau turi savo atnaujinimo elgseną.

Taupymas sukuria atsakomybes:

- Nustatykite atnaujinimo dažnį ir parodykite naudotojui aktualų duomenų naujumą.
- Grąžinimus, vėluojančius užsakymus ir pataisas taikykite nedubliuodami sumų.
- Užtikrinkite saugų pakartotinį apdorojimą ir galimybę atkurti suvestines.
- Jungdami vidurkius saugokite sumą ir kiekį; vidurkių vidurkis gali būti neteisingas.

Šiam skydeliui išankstinės suvestinės tinka tik jei jų naujumo ir koregavimo taisyklės tenkina pirmojo žingsnio reikalavimą.

## 5. Atskirkite ataskaitas, kai matavimai tai pagrindžia

Jei platūs analitiniai skaitymai tebėra brangūs ir trukdo pirkimams, vertinkite tokiai apkrovai tinkamą analitinę saugyklą. Įtraukite sinchronizacijos vėlavimą, atkūrimą ir papildomos sistemos eksploatavimą.

Skaitymo replika gali izoliuoti ataskaitų apkrovą, bet ta pati užklausa ir ten gali būti brangi. Atskyrimas pakeičia vietą, kur mokama kaina, nebūtinai ją panaikina.

Kiekvieną pakeitimą lyginkite su patikimu rezultatu ir ta pačia bandymo apkrova. Tikrinkite atsako laiką, išteklius, naujumą ir poveikį rašymui.

**Prisiminkite:** teisingas rezultatas → išmatuota kliūtis → mažiau nereikalingo darbo → suvestinės, jei tinka → atskira analitika, jei vis dar būtina.

Pastebėjimas	Tiriamas klausimas
Skaitoma daug nereikalingų eilučių	Ar filtrai arba particijų atmetimas gali jų išvengti?
Tikras eilučių skaičius labai skiriasi nuo įverčio	Ar statistika atspindi šiuos duomenis?
Jungimas daug kartų kartoja darbą	Ar įvestis per didelė, ar prieigos kelias netinkamas?
Grupavimas ar rūšiavimas rašo laikinus duomenis į diską	Ar prieš didinant atmintį galima sumažinti įvestį?

1. Prieš spartindami patvirtinkite teisingumą

2. Raskite brangią vykdymo dalį

3. Sumažinkite darbą dabartinėje bazėje

4. Nuspręskite, ar rezultatą ruošti iš anksto

5. Atskirkite ataskaitas, kai matavimai tai pagrindžia

Susiję klausimai