Kako optimizirati počasne agregacije nad veliko tabelo?

Question

IT Interview Practice · Accepted Answer

Pri optimizaciji počasne agregacije najprej ugotovite, **kaj rezultat pomeni in katera operacija porablja čas**. Agregacija združi vrstice v povzetek, denimo prihodek po trgovcih. Strošek je odvisen od količine podatkov, ki jih mora prebrati in združiti.

Vzemimo nadzorno ploščo s prihodkom prejšnjega meseca. Na tem primeru presodite, ali izboljšati poizvedbo, shraniti pripravljen povzetek ali poročanje preseliti drugam.

## 1. Pred pospeševanjem potrdite pravilen odgovor

Pojasnite, ali prihodek pomeni plačana ali ustvarjena naročila, kako obravnavate vračila in katere valute vključujete. Določite tudi zahtevano svežino prikaza. Te odločitve spremenijo poizvedbo in razpoložljive optimizacije.

Pogosta napaka je povezati plačilo z več postavkami naročila in nato sešteti znesek plačila:

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

Številke so ponazoritev. Poizvedba je pomnožila plačilo, zato bi pospešitev le hitreje vrnila napačno vsoto. Pred oceno zmogljivosti agregirajte na želeni poslovni ravni ali spremenite povezovanje.

## 2. Poiščite drag del izvajanja

Izvedbeni načrt kaže operacije za pridobitev rezultata. Preverite drage korake in velikost njihovih vhodov, namesto da predpostavite, da tabela potrebuje samo dodaten indeks.

| Opažanje | Vprašanje za raziskavo |
| --- | --- |
| Prebranih je veliko nepomembnih vrstic | Jih lahko izločijo filtri ali izpuščanje particij? |
| Dejansko število vrstic močno odstopa od ocen | Ali statistika predstavlja te podatke? |
| Povezovanje večkrat ponavlja delo | Je vhod prevelik ali dostopna pot neprimerna? |
| Združevanje ali razvrščanje piše začasne podatke na disk | Je mogoče zmanjšati vhod pred spremembo omejitev pomnilnika? |

V PostgreSQL EXPLAIN (ANALYZE, BUFFERS) izvede poizvedbo in poroča o izvajanju. Drago raziskavo izvajajte v nadzorovanem okolju ali z namerno določenim produkcijskim proračunom virov. [PostgreSQL EXPLAIN](https://www.postgresql.org/docs/current/using-explain.html).

## 3. Zmanjšajte delo v trenutni bazi

Za nadzorno ploščo izberite potreben mesec in stolpce. Preverite, ali načrt dejansko preskoči nepovezane particije in ali povezovanja prinašajo podatke, ki jih povzetek ne potrebuje.

Indeks je uporaben, ko prihrani dovolj branja. Če poročilo potrebuje večino vrstic, je zaporedni pregled lahko primeren. Presojajte dobljeni načrt in izmerjene stroške, ne zgolj prisotnosti indeksa.

Če razvrščanje uporablja disk, lahko težavo reši manjši vhod. Povečanje pomnilnika za vsako poizvedbo pa lahko izčrpa RAM pri sočasnih poročilih. Testirajte z reprezentativno sočasno obremenitvijo.

## 4. Presodite pripravo rezultata vnaprej

Če veliko uporabnikov zahteva iste vsote in je zamik sprejemljiv, ponavljajoče pregledovanje surovih naročil zapravlja delo. Zbirna tabela lahko hrani vsote po dnevu, trgovcu in valuti; nadzorna plošča združi te manjše rezultate.

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

Korist izhaja iz enkratnega skupnega izračuna namesto ponavljanja pri vsaki zahtevi. Materializirani pogled je drug način shranjevanja rezultata poizvedbe, s svojimi pravili osveževanja.

Prihranek prinese odgovornosti:

- Določite pogostost osveževanja in uporabniku pokažite ustrezno svežino.
- Upoštevajte vračila, zapoznela naročila in popravke brez dvojnega štetja.
- Omogočite varno ponovno obdelavo in ohranite možnost obnove povzetkov.
- Za združevanje povprečij hranite vsoto in število; povprečenje povprečij je lahko napačno.

Za to nadzorno ploščo so pripravljeni povzetki možnost samo, če njihova svežina in pravila popravkov ustrezajo zahtevi iz prvega koraka.

## 5. Ločite poročanje, ko to upravičijo meritve

Če obsežni analitični pregledi ostajajo dragi in ovirajo nakupe, ocenite analitično shrambo za takšno delo. Vključite zamik sinhronizacije, obnovo in stroške upravljanja dodatnega sistema.

Bralna replika lahko izolira obremenitev poročanja, a ista poizvedba je lahko draga tudi tam. Ločitev spremeni mesto stroška; ni nujno, da ga odstrani.

Vsako spremembo primerjajte z zaupanja vrednim rezultatom in enako testno obremenitvijo. Preverite odzivni čas, vire, svežino in vpliv na pisanje.

**Zapomnite si:** pravilen rezultat → izmerjeno ozko grlo → manj nepotrebnega dela → pripravljeni povzetki, če ustrezajo → ločena analitika, če je še potrebna.

Opažanje	Vprašanje za raziskavo
Prebranih je veliko nepomembnih vrstic	Jih lahko izločijo filtri ali izpuščanje particij?
Dejansko število vrstic močno odstopa od ocen	Ali statistika predstavlja te podatke?
Povezovanje večkrat ponavlja delo	Je vhod prevelik ali dostopna pot neprimerna?
Združevanje ali razvrščanje piše začasne podatke na disk	Je mogoče zmanjšati vhod pred spremembo omejitev pomnilnika?

1. Pred pospeševanjem potrdite pravilen odgovor

2. Poiščite drag del izvajanja

3. Zmanjšajte delo v trenutni bazi

4. Presodite pripravo rezultata vnaprej

5. Ločite poročanje, ko to upravičijo meritve

Povezana vprašanja