ApsaraDB for SelectDB：Optimalkan kueri join

Operator fisik yang didukung

ApsaraDB for SelectDB mendukung dua operator join fisik berikut untuk kueri join dalam mesin mandiri:

• Hash Join: Anda dapat membuat tabel hash di tabel kanan berdasarkan kolom yang ada di kedua tabel kiri dan kanan. Kemudian, ApsaraDB for SelectDB menggunakan tabel hash untuk melakukan perhitungan join di tabel kiri secara streaming. Operator ini hanya berlaku untuk kueri join dengan kondisi ekuivalen.

• Nest Loop Join: ApsaraDB for SelectDB melakukan perhitungan join dalam dua loop for. Operator ini berlaku untuk kueri join dengan kondisi non-ekuivalen, seperti penggunaan operator ">" atau "<". Meskipun merupakan operator join umum, performanya cenderung buruk.

Metode shuffle

ApsaraDB for SelectDB adalah database prosesor paralel masif terdistribusi (MPP). Saat menjalankan kueri join pada tabel ApsaraDB for SelectDB, Anda perlu mengacak data sebelum menggabungkannya menggunakan operator fisik. ApsaraDB for SelectDB mendukung empat metode shuffle berikut, dan bagian ini juga menyertakan contoh penggunaan setiap metode untuk mengacak data.

Contoh ini melibatkan penggabungan Tabel S dan Tabel R. N menunjukkan jumlah node yang terlibat dalam perhitungan join, sedangkan T menunjukkan jumlah catatan data dalam sebuah tabel.

• Broadcast Join

  Metode ini mengirimkan data penuh dari Tabel S ke Tabel R. Setiap node yang terlibat dalam perhitungan join memiliki data penuh dari Tabel S, yaitu T(R). Metode ini mendukung operator Hash Join dan Nest Loop Join. Overhead jaringan dihitung menggunakan rumus berikut: N × T(R).

  Data dari Tabel S tidak dipindahkan, sedangkan data dari Tabel R dikirim ke node pemindaian yang memindai data dari Tabel S.

• Shuffle Join

  Saat menggunakan operator Hash Join, Anda dapat menghitung nilai hash dari data di Tabel S dan Tabel R berdasarkan kolom join, lalu mengirim nilai hash yang sama ke node yang sama dalam sistem terdistribusi. Hal ini meningkatkan kecepatan kueri join. Overhead jaringan dihitung menggunakan rumus berikut: T(S) + T(R). Namun, metode ini hanya mendukung operator Hash Join karena nilai hash dihitung berdasarkan kolom join.

  Data dari Tabel S dan Tabel R dihitung berdasarkan partisi, lalu hasilnya dikirim ke node partisi yang berbeda.

• Bucket Shuffle Join

  Data tabel ApsaraDB for SelectDB disimpan dalam bucket berdasarkan nilai hash dari data. Dengan cara ini, Anda dapat mengacak data yang ingin digabungkan berdasarkan kolom bucket di tabel. Misalnya, jika dua tabel (Tabel S dan Tabel R) digabungkan dan kolom join adalah kolom bucket dari Tabel S, data dari Tabel S tidak perlu dipindahkan. Perhitungan join dapat dilakukan dengan hanya memindahkan data dari Tabel R.

  Overhead jaringan dari metode ini adalah T(R), yang menunjukkan bahwa perhitungan join dapat dilakukan dengan hanya mengacak data dari Tabel R. Untuk informasi lebih lanjut tentang Bucket Shuffle Join, lihat Bucket Shuffle Join.

  Data dari Tabel S tidak dipindahkan, sedangkan data dari Tabel R dikirim ke node yang memindai data dari Tabel S berdasarkan hasil yang dihitung berdasarkan partisi.

• Colocation Join

  Untuk beberapa tabel yang saling terkait, jumlah shard harus sama saat tabel dibuat. Dengan cara ini, Anda dapat melewati pengacakan data dalam kueri dan langsung melakukan perhitungan join, sehingga meningkatkan performa kueri. Untuk informasi lebih lanjut tentang Colocation Join, lihat Colocation Join.

  Data telah dipartisi sebelumnya, sehingga Anda tidak perlu mempertimbangkan overhead jaringan. Perhitungan join dapat dilakukan langsung di komputer lokal.

Tabel berikut menjelaskan empat metode shuffle:

Metode shuffle sebelumnya diurutkan berdasarkan tingkat fleksibilitas secara menurun. Jika suatu metode shuffle memiliki persyaratan yang lebih tinggi untuk distribusi data, metode tersebut memberikan performa yang lebih baik dalam perhitungan join.

Runtime Filter

Runtime Filter dibuat secara dinamis selama rencana eksekusi. Pada kueri join, HashJoinNode mengonversi tabel kanan menjadi kondisi filter dan mendorong kondisi tersebut ke ScanNode. Selanjutnya, ScanNode menyaring serta memangkas data saat tabel kiri dipindai. Metode ini secara signifikan mengurangi waktu yang diperlukan untuk membaca dan menghitung data selama kueri, sehingga meningkatkan performa kueri. Untuk informasi lebih lanjut tentang Runtime Filter, lihat Runtime Filter.

Join Reorder

Dalam skenario multi-tabel join, urutan join secara signifikan memengaruhi performa keseluruhan kueri.

Sebagai contoh, tiga tabel digabungkan, seperti ditunjukkan pada gambar berikut. ScanA, ScanB, dan ScanC menunjukkan data dari Tabel A, Tabel B, dan Tabel C yang dipindai berdasarkan kondisi kueri.

Di bagian kiri gambar, data yang dipindai dari Tabel A dan Tabel B digabungkan, menghasilkan 2.000 baris hasil antara. Kemudian, hasil antara digabungkan dengan data yang dipindai dari Tabel C.

Di sisi kanan gambar, urutan join telah disesuaikan. Data yang dipindai dari Tabel A dan Tabel C digabungkan, menghasilkan 100 baris hasil antara. Selanjutnya, hasil antara tersebut digabungkan dengan data yang dipindai dari Tabel B. Meskipun hasil join akhirnya sama, jumlah hasil antara yang dihasilkan di gambar kiri 20 kali lebih banyak dibandingkan dengan gambar kanan, sehingga menyebabkan celah performa yang signifikan.

ApsaraDB for SelectDB mendukung algoritma Join Recorder berbasis aturan. Berikut ini adalah penjelasan logika algoritma tersebut:

• Gabungkan tabel besar dengan tabel kecil untuk menghasilkan jumlah hasil antara yang lebih kecil.

• Letakkan tabel yang memiliki kondisi join di depan From dalam pernyataan SELECT untuk menyaring data tabel yang memiliki kondisi join.

• Operator Hash Join memiliki prioritas lebih tinggi daripada operator Nest Loop Join karena kecepatan eksekusinya jauh lebih cepat.

Solusi untuk optimasi kueri join

Bagian berikut menjelaskan langkah-langkah dalam proses optimasi kueri join. Untuk mengoptimalkan kueri join, ikuti langkah-langkah berikut:

1. Gunakan profil yang disediakan oleh ApsaraDB for SelectDB untuk mendiagnosis kueri. Profil mencatat berbagai informasi dalam seluruh kueri, yang sangat penting untuk optimasi performa.

2. Pahami mekanisme join ApsaraDB for SelectDB dan analisis alasan kueri lambat.

3. Gunakan variabel sesi untuk memodifikasi beberapa perilaku operasi join guna mengoptimalkan operasi join.

4. Periksa rencana kueri untuk menentukan apakah optimasi berlaku.

Empat langkah sebelumnya menjelaskan proses optimasi standar untuk operasi join. Jika kecepatan kueri tidak meningkat setelah menerapkan langkah-langkah tersebut, Anda mungkin perlu menulis ulang pernyataan SQL untuk penggabungan data atau menyesuaikan distribusi data serta memverifikasi apakah data telah didistribusikan dengan benar. Dalam situasi ini, semua pernyataan join dalam kueri harus dimodifikasi secara manual. Namun, pendekatan ini memiliki biaya yang relatif tinggi dan biasanya digunakan untuk analisis lebih lanjut jika metode sebelumnya gagal meningkatkan kecepatan kueri.

Saran untuk mengoptimalkan kueri join

Bagian berikut memberikan panduan untuk mengoptimalkan kueri join di ApsaraDB for SelectDB:

• Saat melakukan kueri join, pilih kolom dengan tipe yang sama untuk mengurangi data cast, atau pilih kolom dengan tipe sederhana untuk mempercepat perhitungan join.

• Pilih kolom kunci untuk menggabungkan data. Materialisasi akhir dapat diimplementasikan secara efektif dengan menggabungkan data kolom kunci. Untuk informasi lebih lanjut, lihat Runtime Filter.

• Lakukan join di antara tabel besar dalam mode Colocation karena penggabungan tabel besar menyebabkan overhead jaringan yang besar, meningkatkan biaya pengacakan data.

• Gunakan Runtime Filter dengan tepat. Runtime Filter cocok untuk skenario di mana tingkat filter tinggi diperlukan dalam join. Namun, ini juga memiliki beberapa efek samping. Anda harus menentukan apakah akan menggunakan Runtime Filter berdasarkan kolom yang terlibat dalam pernyataan SQL.

• Jika beberapa tabel digabungkan, pastikan rasionalitas join. Pastikan bahwa tabel kiri adalah tabel besar dan tabel kanan adalah tabel kecil. Dalam hal ini, operator Hash Join bekerja lebih baik daripada operator Nest Loop Join. Anda dapat menulis ulang pernyataan SQL dan menggunakan metode hint untuk menyesuaikan urutan join.