Ta yaya za ka hanzarta jinkirin aggregate a babban tebur?

Question

IT Interview Practice · Accepted Answer

Don hanzarta aggregate mai jinkiri, da farko gano **ma’anar sakamakon da kuma wace aiki ce take cin lokaci**. Aggregate yana haɗa layuka zuwa taƙaitaccen sakamako, kamar kuɗin shiga ga kowane ɗan kasuwa. Kuɗin aikinsa ya danganta da yawan bayanan da za a karanta da haɗawa.

Ka ɗauki dashboard da ke nuna kuɗin shigar watan da ya gabata. Yi amfani da wannan misalin wajen zaɓar ko a inganta query, a adana taƙaitaccen sakamako da aka shirya, ko a mayar da rahotanni wani wuri.

## 1. Tabbatar da amsar kafin hanzarta ta

Bayyana ko kuɗin shiga yana nufin odar da aka biya ko aka ƙirƙira, yadda za a lissafa kuɗin da aka mayar da nau’in kuɗaɗen da za a haɗa. Tambayi tsawon jinkirin da dashboard zai iya ɗauka. Waɗannan shawarar suna canza query da hanyoyin inganta shi.

Kuskure na gama gari shi ne haɗa biyan kuɗi da abubuwa da yawa na oda, sannan a tara kuɗin biyan:

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

Lambobin misali ne. Query ya ninka biyan kuɗin, don haka hanzarta shi zai dawo da kuskuren jimla ne kawai da sauri. Tara bayanan a matakin kasuwanci da ake so ko gyara join kafin auna sauri.

## 2. Gano ɓangaren aiwatarwa mai tsada

Execution plan yana nuna ayyukan da ake amfani da su don samun sakamako. Duba matakai masu tsada da girman bayanan shigarsu, maimakon ɗauka cewa tebur yana buƙatar ƙarin index kawai.

| Abin da aka gani | Tambayar bincike |
| --- | --- |
| Ana karanta layuka marasa amfani da yawa | Shin filters ko partition pruning za su iya ware su? |
| Adadin layuka na gaske ya bambanta da hasashe sosai | Shin statistics suna wakiltar waɗannan bayanai? |
| Join yana maimaita aiki sau da yawa | Shin bayanan shiga sun yi yawa ko hanyar samun su ba ta dace ba? |
| Grouping ko sorting suna rubuta bayanan wucin gadi a disk | Za a iya rage bayanan shiga kafin sauya iyakar memory? |

A PostgreSQL, EXPLAIN (ANALYZE, BUFFERS) yana gudanar da query da bayar da bayanan lokacin aiki. Yi bincike mai tsada a muhalli mai sarrafawa ko da iyakar albarkatun production da aka tsara. [PostgreSQL EXPLAIN](https://www.postgresql.org/docs/current/using-explain.html).

## 3. Rage aiki a database na yanzu

Don dashboard, zaɓi watan da columns da ake bukata kawai. Duba ko plan yana tsallake partitions marasa alaƙa a zahiri, da ko joins suna kawo bayanan da taƙaitawar ba ta bukata.

Index yana amfani idan yana rage isasshen karatu. Idan rahoton yana bukatar yawancin layuka, sequential scan na iya dacewa. Auna plan da kuɗin aikin da aka gani, ba kasancewar index kawai ba.

Idan sorting ya zuba zuwa disk, rage bayanan shiga na iya warware matsalar. Ƙara memory ga kowane query na iya ƙare RAM idan rahotanni da yawa sun gudana tare. Gwada da nauyin aiki na lokaci guda mai wakilci.

## 4. Yanke ko a shirya sakamakon tun da farko

Idan mutane da yawa suna neman jimla iri ɗaya kuma jinkiri ya halatta, sake scan na odar asali yana ɓata aiki. Teburin taƙaitawa zai iya adana jimla bisa rana, ɗan kasuwa da nau’in kuɗi; dashboard ya haɗa waɗannan ƙananan sakamakon.

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

Amfanin yana fitowa daga yin lissafin da ake rabawa sau ɗaya maimakon kowane request. Materialized view wata hanya ce ta adana sakamakon query, tare da nata tsarin sabuntawa.

Wannan tanadi yana kawo nauyin kulawa:

- Bayyana yawan sabunta sakamako kuma a nuna wa mai amfani shekarun bayanan da suka dace.
- Aiwatar da refunds, odar da ta makara da gyare-gyare ba tare da lissafawa sau biyu ba.
- Sanya maimaita processing ya zama amintacce, kuma a riƙe hanyar sake gina taƙaitawa.
- Adana sum da count lokacin haɗa matsakaita; matsakaicin matsakaita na iya zama kuskure.

Ga wannan dashboard, taƙaitaccen sakamako da aka shirya zai dace ne kawai idan freshness da ƙa’idar gyara sun cika buƙatar mataki na farko.

## 5. Ware rahotanni idan ma’auni sun ba da hujja

Idan manyan scans na nazari suna da tsada kuma suna hana sayayya, duba ma’ajiyar nazari da ta dace da wannan aiki. Haɗa jinkirin synchronization, recovery da kuɗin gudanar da wani tsarin.

Read replica na iya ware nauyin rahoto, amma query ɗin na iya kasancewa mai tsada a can ma. Warewa yana sauya inda ake biyan kuɗin aiki; ba lallai ya cire shi ba.

Kwatanta kowane canji da sakamako amintacce da nauyin gwaji iri ɗaya. Duba lokacin amsawa, albarkatu, freshness da tasiri ga writes.

**Ka tuna:** sakamako daidai → bottleneck da aka auna → rage aikin banza → taƙaitawa da aka shirya idan ta dace → ware nazari idan har yanzu ana bukata.

Abin da aka gani	Tambayar bincike
Ana karanta layuka marasa amfani da yawa	Shin filters ko partition pruning za su iya ware su?
Adadin layuka na gaske ya bambanta da hasashe sosai	Shin statistics suna wakiltar waɗannan bayanai?
Join yana maimaita aiki sau da yawa	Shin bayanan shiga sun yi yawa ko hanyar samun su ba ta dace ba?
Grouping ko sorting suna rubuta bayanan wucin gadi a disk	Za a iya rage bayanan shiga kafin sauya iyakar memory?

1. Tabbatar da amsar kafin hanzarta ta

2. Gano ɓangaren aiwatarwa mai tsada

3. Rage aiki a database na yanzu

4. Yanke ko a shirya sakamakon tun da farko

5. Ware rahotanni idan ma’auni sun ba da hujja

Tambayoyi masu alaƙa