Bagaimanakah pengagregatan perlahan pada jadual besar dioptimumkan?

Question

IT Interview Practice · Accepted Answer

Untuk mempercepat agregat yang perlahan, kenal pasti dahulu **makna hasil dan operasi yang menggunakan masa**. Agregat menggabungkan baris menjadi ringkasan, seperti hasil jualan setiap peniaga. Kosnya bergantung pada jumlah data yang dibaca dan digabungkan.

Pertimbangkan papan pemuka hasil bulan lalu. Gunakan contoh ini untuk memilih antara membaiki pertanyaan, menyimpan ringkasan siap atau memindahkan pelaporan.

## 1. Sahkan jawapan sebelum mempercepatnya

Jelaskan sama ada hasil merujuk kepada pesanan dibayar atau dicipta, cara bayaran balik dikira dan mata wang yang disertakan. Tentukan juga kesegaran data yang diperlukan. Semua ini mengubah pertanyaan dan pilihan pengoptimuman.

Kesilapan biasa ialah menggabungkan satu pembayaran dengan beberapa item pesanan lalu menjumlahkan amaun pembayaran:

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

Angka ini sekadar contoh. Pertanyaan menggandakan pembayaran; mempercepatnya hanya memberi jumlah salah dengan lebih cepat. Agregatkan pada peringkat perniagaan yang betul atau ubah join dahulu.

## 2. Cari bahagian pelaksanaan yang mahal

Pelan pelaksanaan menunjukkan operasi untuk mendapatkan hasil. Semak langkah mahal dan saiz inputnya, bukan terus menganggap jadual memerlukan indeks tambahan.

| Pemerhatian | Soalan untuk disiasat |
| --- | --- |
| Banyak baris tidak berkaitan dibaca | Bolehkah penapis atau pengecualian partisi mengelakkannya? |
| Bilangan sebenar jauh berbeza daripada anggaran | Adakah statistik mewakili data ini? |
| Join mengulang kerja berkali-kali | Adakah input terlalu besar atau laluan akses tidak sesuai? |
| Pengelompokan atau pengisihan menulis data sementara ke cakera | Bolehkah input dikurangkan sebelum menaikkan had memori? |

Dalam PostgreSQL, EXPLAIN (ANALYZE, BUFFERS) benar-benar menjalankan pertanyaan dan melaporkan maklumat masa jalan. Jalankan siasatan mahal dalam persekitaran terkawal atau dengan belanjawan beban produksi yang ditetapkan. [PostgreSQL EXPLAIN](https://www.postgresql.org/docs/current/using-explain.html).

## 3. Kurangkan kerja dalam pangkalan data semasa

Untuk papan pemuka, pilih bulan dan lajur yang diperlukan. Semak sama ada pelan benar-benar melangkau partisi lain dan join tidak membawa data yang tidak diperlukan ringkasan.

Indeks berguna apabila menjimatkan pembacaan yang mencukupi. Jika laporan memerlukan kebanyakan baris, imbasan berjujukan mungkin sesuai. Nilai pelan dan kos terukur, bukan semata-mata kewujudan indeks.

Jika pengisihan melimpah ke cakera, pengurangan input mungkin menyelesaikannya. Menambah memori setiap pertanyaan boleh menghabiskan RAM apabila banyak laporan berjalan bersama. Uji dengan beban serentak yang mewakili penggunaan sebenar.

## 4. Tentukan sama ada hasil perlu disediakan awal

Jika ramai pengguna meminta jumlah sama dan kelewatan diterima, mengimbas pesanan mentah berulang kali membazir. Jadual ringkasan boleh menyimpan jumlah mengikut hari, peniaga dan mata wang; papan pemuka menggabungkan hasil kecil ini.

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

Manfaatnya ialah pengiraan bersama dilakukan sekali, bukan pada setiap permintaan. Materialized view juga menyimpan hasil pertanyaan, dengan kelakuan penyegaran tersendiri.

Penjimatan ini membawa tanggungjawab:

- Tentukan kekerapan kemas kini dan tunjukkan kesegaran yang relevan kepada pengguna.
- Gunakan bayaran balik, pesanan lewat dan pembetulan tanpa pengiraan berganda.
- Jadikan pemprosesan berulang selamat dan kekalkan cara membina semula ringkasan.
- Simpan jumlah dan bilangan apabila menggabungkan purata; purata kepada purata boleh salah.

Ringkasan siap hanya sesuai untuk papan pemuka ini jika peraturan kesegaran dan pembetulannya memenuhi keperluan langkah pertama.

## 5. Pisahkan pelaporan apabila ukuran menyokongnya

Jika imbasan analitik luas kekal mahal dan mengganggu pembelian, nilai stor analitik yang sesuai. Sertakan kelewatan penyegerakan, pemulihan dan kos mengendalikan sistem tambahan.

Replika bacaan boleh mengasingkan beban laporan, tetapi pertanyaan sama masih boleh mahal di situ. Pengasingan mengubah tempat kos ditanggung, bukan semestinya menghapuskannya.

Bandingkan setiap perubahan dengan hasil dipercayai dan beban ujian sama. Semak masa respons, sumber, kesegaran dan kesan terhadap penulisan.

**Ingat:** hasil betul → kesesakan terukur → kurang kerja tidak perlu → ringkasan siap jika sesuai → analitik berasingan jika masih perlu.

Pemerhatian	Soalan untuk disiasat
Banyak baris tidak berkaitan dibaca	Bolehkah penapis atau pengecualian partisi mengelakkannya?
Bilangan sebenar jauh berbeza daripada anggaran	Adakah statistik mewakili data ini?
Join mengulang kerja berkali-kali	Adakah input terlalu besar atau laluan akses tidak sesuai?
Pengelompokan atau pengisihan menulis data sementara ke cakera	Bolehkah input dikurangkan sebelum menaikkan had memori?

1. Sahkan jawapan sebelum mempercepatnya

2. Cari bahagian pelaksanaan yang mahal

3. Kurangkan kerja dalam pangkalan data semasa

4. Tentukan sama ada hasil perlu disediakan awal

5. Pisahkan pelaporan apabila ukuran menyokongnya

Soalan berkaitan