Tingkatkan kinerja kueri dengan pernyataan SQL - MaxCompute

Topik ini menjelaskan skenario umum untuk mengoptimalkan pernyataan SQL guna meningkatkan kinerja, serta memberikan contoh optimasi.

Optimalkan konkurensi

Tingkat paralelisme adalah ukuran komputasi paralel. Misalnya, dalam rencana eksekusi, jika suatu tugas dengan ID M1 menggunakan 1.000 instans, tingkat paralelismenya adalah 1000. Anda dapat meningkatkan efisiensi tugas dengan mengatur dan menyesuaikan tingkat paralelisme.

Bagian ini menjelaskan skenario di mana konkurensi dapat dioptimalkan.

Paksa satu instans untuk mengeksekusi

Dalam beberapa operasi, sistem secara paksa hanya memanggil satu instance untuk mengeksekusi tugas. Berikut adalah contoh operasi tersebut:

Anda melakukan agregasi tanpa menentukan klausa group by, atau Anda menggunakan konstanta dalam klausa group by.
Anda menentukan konstanta untuk partition by dalam klausa over dari fungsi jendela.
Anda menentukan konstanta untuk distribute by atau cluster by dalam pernyataan SQL.

Solusi: Periksa apakah operasi berdasarkan konstanta diperlukan. Kami sarankan Anda membatalkan operasi ini untuk mencegah sistem secara paksa memanggil hanya satu instance untuk mengeksekusi tugas.

Jumlah instance yang terlalu besar atau terlalu kecil dipanggil

Penting

Kinerja eksekusi tidak selalu meningkat seiring dengan peningkatan konkurensi. Jika Anda memanggil jumlah instance yang terlalu besar untuk suatu pekerjaan, kecepatan eksekusi mungkin menurun karena alasan berikut:

Jumlah instance yang terlalu besar menyebabkan waktu tunggu sumber daya lebih lama dan antrian yang lebih panjang.
Membutuhkan waktu untuk menginisialisasi setiap instance. Semakin tinggi konkurensi, semakin lama total waktu inisialisasi, sehingga persentase waktu eksekusi yang valid menjadi lebih rendah.

Berikut adalah skenario di mana sistem secara paksa memanggil jumlah instance yang terlalu besar:

Sistem harus membaca data dari banyak partisi berukuran kecil. Misalnya, jika Anda mengeksekusi pernyataan SQL untuk membaca data dari 10.000 partisi, sistem memaksa memanggil 10.000 instans.
Solusi: Optimalkan pernyataan SQL Anda untuk mengurangi jumlah partisi yang ingin dibaca. Contohnya, Anda dapat memangkas partisi yang tidak perlu dibaca atau membagi pekerjaan besar menjadi beberapa pekerjaan kecil.
Membaca hanya 256 MB data dalam satu waktu tidak cukup. Hal ini menyebabkan waktu eksekusi instans menjadi singkat. Jika total data masukan besar, hal ini menghasilkan tingkat paralelisme yang terlalu tinggi, sehingga instans menghabiskan sebagian besar waktunya untuk mengantri sumber daya.
Solusi: Jalankan perintah berikut untuk mengurangi jumlah maksimum instance yang dapat dipanggil secara bersamaan untuk tugas reduce. Dalam hal ini, jumlah data yang diproses pada setiap instance meningkat.
```
SET odps.stage.mapper.split.size=<256>;
SET odps.stage.reducer.num=<Jumlah maksimum instance bersamaan>;
```

Konfigurasikan jumlah instance

Tugas yang melibatkan pembacaan tabel
- Metode 1: Sesuaikan konkurensi dengan mengonfigurasi parameter.
```
-- Konfigurasikan jumlah maksimum data masukan mapper. Satuan: MB.
-- Nilai default: 256. Nilai valid: [1,Integer.MAX_VALUE].
SET odps.sql.mapper.split.size=<nilai>;
```
- Metode 2: Gunakan petunjuk ukuran split yang disediakan oleh MaxCompute untuk menyesuaikan konkurensi operasi baca pada tabel tunggal.
```
-- Setel ukuran split menjadi 1 MB. Pengaturan ini menunjukkan bahwa tugas dibagi menjadi subtugas berdasarkan ukuran 1 MB saat data di tabel src dibaca.
SELECT a.key FROM src a /*+split_size(1)*/ JOIN src2 b ON a.key=b.key;
```
- Metode 3: Pisahkan data tabel berdasarkan jumlah data, jumlah baris, atau dengan menentukan konkurensi.
Pada Metode 1, parameter odps.sql.mapper.split.size hanya mendukung pengaturan global untuk tahap Mapper, dengan nilai minimum 1 MB. Jika diperlukan, Anda dapat menyesuaikan tingkat paralelisme berdasarkan dimensi tabel. Hal ini sangat berguna ketika jumlah data per baris kecil tetapi komputasi selanjutnya berat. Anda dapat mengurangi jumlah baris yang diproses secara paralel untuk meningkatkan tingkat paralelisme tugas.
Anda dapat menjalankan salah satu perintah berikut untuk menyesuaikan konkurensi:
- Konfigurasikan ukuran satu shard untuk pemrosesan bersamaan dalam tabel.
```
SET odps.sql.split.size = {"table1": 1024, "table2": 512};
```
- Konfigurasikan jumlah baris untuk pemrosesan bersamaan dalam tabel.
```
SET odps.sql.split.row.count = {"table1": 100, "table2": 500};
```
- Konfigurasikan konkurensi untuk tabel.
```
SET odps.sql.split.dop = {"table1": 1, "table2": 5};
```
Catatan
Parameter odps.sql.split.row.count dan odps.sql.split.dop hanya dapat digunakan untuk tabel internal, tabel non-transaksional, dan tabel non-terkluster.
Tugas yang tidak melibatkan pembacaan tabel
Anda dapat menyesuaikan konkurensi menggunakan salah satu metode berikut:
- Metode 1: Sesuaikan nilai odps.stage.reducer.num. Gunakan perintah berikut untuk mengatur tingkat paralelisme Reducer. Pengaturan ini memengaruhi semua tugas terkait.
```
-- Konfigurasikan jumlah instance yang dipanggil untuk mengeksekusi tugas reducer.
-- Nilai valid: [1,99999].
SET odps.stage.reducer.num=<nilai>;
```
- Metode 2: Sesuaikan nilai odps.stage.joiner.num. Gunakan perintah berikut untuk mengatur tingkat paralelisme Joiner. Pengaturan ini memengaruhi semua tugas terkait.
```
-- Konfigurasikan jumlah instance yang dipanggil untuk mengeksekusi tugas joiner.
-- Nilai valid: [1,99999].
SET odps.stage.joiner.num=<nilai>;
```
- Metode 3: Sesuaikan nilai odps.sql.mapper.split.size.
  Untuk tugas yang tidak melibatkan pembacaan tabel, konkurensinya dipengaruhi oleh konkurensi tugas yang melibatkan pembacaan tabel. Anda dapat menyesuaikan konkurensi tugas yang tidak melibatkan pembacaan tabel dengan menyesuaikan konkurensi tugas yang melibatkan pembacaan tabel.

Optimalkan fungsi jendela

Jika fungsi jendela digunakan dalam pernyataan SQL, tugas reduce ditugaskan untuk setiap fungsi jendela. Banyak fungsi jendela mengonsumsi sejumlah besar sumber daya. Anda dapat mengoptimalkan fungsi jendela yang memenuhi kedua kondisi berikut:

Klausa OVER yang mendefinisikan cara mempartisi dan mengurutkan baris dalam tabel harus sama.
Beberapa fungsi jendela harus dieksekusi pada tingkat nesting yang sama dalam pernyataan SQL.

Fungsi jendela yang memenuhi kondisi di atas digabungkan untuk dieksekusi oleh satu tugas reduce. Pernyataan SQL berikut memberikan contoh:

SELECT
RANK() OVER (PARTITION BY A ORDER BY B desc) AS RANK,
ROW_NUMBER() OVER (PARTITION BY A ORDER BY B desc) AS row_num
FROM MyTable;

Optimalkan subquery

Pernyataan berikut berisi subquery:

SELECT * FROM table_a a WHERE a.col1 IN (SELECT col1 FROM table_b b WHERE xxx);

Jika subkueri pada tabel table_b mengembalikan lebih dari 9.999 nilai untuk kolom col1, sistem melaporkan kesalahan berikut: records returned from subquery exceeded limit of 9999. Dalam kasus ini, gunakan pernyataan JOIN sebagai gantinya, seperti pada contoh berikut.

SELECT a.* FROM table_a a JOIN (SELECT DISTINCT col1 FROM table_b b WHERE xxx) c ON (a.col1 = c.col1);

Catatan

Jika kata kunci DISTINCT tidak digunakan, tabel hasil subquery c mungkin berisi nilai duplikat di kolom col1. Dalam hal ini, query pada tabel a mengembalikan lebih banyak hasil.
Jika kata kunci DISTINCT digunakan, hanya satu worker yang ditugaskan untuk melakukan subquery. Jika subquery melibatkan sejumlah besar data, seluruh query melambat.
Jika Anda yakin bahwa nilai yang memenuhi kondisi subquery di kolom col1 unik, Anda dapat menghapus kata kunci DISTINCT untuk meningkatkan kinerja query.

Optimalkan joins

Saat menggabungkan dua tabel, kami sarankan Anda menggunakan klausa WHERE berdasarkan aturan berikut:

Tentukan batas partisi tabel utama dalam klausa WHERE. Kami sarankan Anda mendefinisikan subquery untuk tabel utama untuk mendapatkan data yang diperlukan terlebih dahulu.
Tulis klausa WHERE tabel utama di akhir pernyataan.
Tentukan batas partisi tabel sekunder dalam klausa ON atau subquery, bukan dalam klausa WHERE.

Kode berikut memberikan contoh:

SELECT * FROM A JOIN (SELECT * FROM B WHERE dt=20150301)B ON B.id=A.id WHERE A.dt=20150301;
SELECT * FROM A JOIN B ON B.id=A.id WHERE B.dt=20150301; -- Kami sarankan Anda tidak menggunakan pernyataan ini. Sistem melakukan operasi JOIN sebelum melakukan pemangkasan partisi. Ini meningkatkan jumlah data dan menyebabkan kinerja query menurun.
SELECT * FROM (SELECT * FROM A WHERE dt=20150301)A JOIN (SELECT * FROM B WHERE dt=20150301)B ON B.id=A.id;

Optimalkan fungsi agregat

Untuk mengoptimalkan fungsi agregat, Anda dapat mengganti fungsi collect_list dengan fungsi wm_concat. Contoh berikut menunjukkan cara melakukannya.

-- Implementasikan fungsi collect_list.
SELECT concat_ws(',', sort_array(collect_list(key))) FROM src;
-- Implementasikan fungsi wm_concat untuk kinerja yang lebih baik.
SELECT wm_concat(',', key) WITHIN GROUP (ORDER BY key) FROM src;


-- Implementasikan fungsi collect_list.
SELECT array_join(collect_list(key), ',') FROM src;
-- Implementasikan fungsi wm_concat untuk kinerja yang lebih baik.
SELECT wm_concat(',', key) FROM src;