Apa itu pengoptimalan kueri?

Optimasi kueri adalah proses menentukan cara paling efisien bagi basis data untuk mengeksekusi kueri.

Ketika pengguna mengirimkan kueri—yang biasanya ditulis sebagai pernyataan bahasa kueri terstruktur (SQL)—basis data akan mengevaluasi berbagai cara untuk mengambil data yang diminta. Proses pengambilan keputusan ini ditangani oleh komponen yang dikenal sebagai pengoptimal kueri, yang memilih strategi eksekusi yang paling efisien.

Sistem manajemen basis data modern (DBMS) menggunakan pengoptimal berbasis biaya yang memperkirakan biaya dari berbagai strategi eksekusi sebelum memilih opsi yang paling efisien. Karena proses ini, dua kueri basis data yang memberikan hasil yang identik dapat memiliki waktu eksekusi yang sangat berbeda—sering kali diukur dalam milidetik—yang berdampak pada kinerja kueri dan waktu respons.

Pengoptimalan kueri, atau pengoptimalan SQL Query, mempengaruhi jauh lebih dari kinerja kueri individu. Hal ini menentukan efisiensi seluruh sistem data, model machine learning, dan inisiatif AI dengan meningkatkan skala sistem dan penggunaan sumber daya.

Aplikasi bergantung pada basis data untuk mengambil informasi dengan cepat dan konsisten. Ketika kueri tidak efisien, basis data dapat menghabiskan waktu yang tidak perlu untuk melakukan pemindaian tabel, menyortir catatan, atau menggabungkan kumpulan data besar. Penundaan ini dapat memperlambat antarmuka pemrograman aplikasi (API) dan beban kerja analitik, sehingga menciptakan kemacetan yang menurunkan pengalaman pengguna secara keseluruhan.

Ketika organisasi mengumpulkan lebih banyak data, basis data harus mendukung beban kerja yang semakin kompleks yang didorong oleh volume yang besar, jenis data yang beragam, dan pola kueri yang lebih menuntut.

Dengan datasphere global yang diperkirakan akan mencapai 393,9 zettabyte pada tahun 2028, kueri yang dulunya memproses ribuan baris pada akhirnya dapat memproses jutaan atau miliaran. Pengoptimalan kueri meningkatkan skalabilitas dengan memungkinkan kueri yang efisien, bahkan ketika jumlah data dan kompleksitas beban kerja meningkat.

Rencana eksekusi yang efisien juga mengurangi sumber daya yang dibutuhkan untuk memproses kueri. Setiap operasi basis data memerlukan sumber daya sistem untuk memproses data, termasuk siklus unit pemrosesan pusat (CPU) dan input/output (I/O) disk.

Kueri yang tidak dioptimalkan dengan baik menghabiskan banyak sumber daya, membutuhkan pemrosesan yang jauh lebih banyak daripada yang diperlukan untuk menghasilkan hasil yang sama. Peningkatan konsumsi sumber daya ini dapat menimbulkan biaya yang besar di lingkungan cloud di mana penggunaan sumber daya secara langsung memengaruhi harga.

Platform data modern yang mendukung machine learning, analitik real-time, generasi dengan dukungan pengambilan data (RAG) dan AI bergantung pada akses yang cepat dan andal ke volume data yang besar. Pengoptimalan kueri membantu memastikan sistem-sistem ini dapat mengambil informasi yang relevan dengan cukup cepat untuk mendukung pengambilan keputusan real-time tanpa mengorbankan anggaran.

Pengoptimal basis data dapat menggunakan beberapa pendekatan ketika mengevaluasi strategi eksekusi potensial. Sistem basis data pada masa awal sering kali menggunakan pengoptimalan berbasis aturan, yang menerapkan aturan yang telah ditetapkan sebelumnya untuk menentukan rencana eksekusi berdasarkan struktur kueri.

DBMS modern biasanya memprioritaskan pengoptimalan berbasis biaya, yang mengevaluasi beberapa kemungkinan strategi eksekusi dan memperkirakan sumber daya yang dibutuhkan untuk masing-masing strategi. Beberapa sistem juga menggabungkan teknik berbasis heuristik, yang menerapkan pedoman praktis untuk menyederhanakan perencanaan kueri dan mengurangi biaya pengoptimalan.

Terlepas dari pendekatan pengoptimalan yang digunakan, beberapa konsep teknis membentuk bagaimana pengoptimal mengevaluasi strategi eksekusi potensial, termasuk:

• Pengoptimal kueri
• Statistik basis data
• Estimasi kardinalitas
• Indeks dan jalur akses
• Algoritma penggabungan

Pengoptimal kueri adalah komponen basis data yang bertanggung jawab untuk memilih rencana eksekusi yang efisien, sering kali menggunakan teknik pengoptimalan berbasis biaya. Pada basis data relasional, proses ini membantu mesin basis data menentukan cara yang paling efisien untuk mengeksekusi kueri SQL.

Alih-alih bergantung pada aturan tetap, pengoptimal berbasis biaya menganalisis karakteristik data dan struktur kueri untuk menentukan pendekatan yang paling efisien. Fleksibilitas ini memungkinkan basis data untuk menyesuaikan strategi eksekusi seiring berkembangnya kumpulan data dan beban kerja.

Pengoptimal sangat bergantung pada statistik basis data untuk memperkirakan seberapa mahal rencana eksekusi yang berbeda nantinya. Statistik menggambarkan karakteristik utama dari data yang disimpan, termasuk:

• Jumlah baris dalam setiap tabel
• Distribusi nilai di dalam kolom
• Selektivitas kolom yang diindeks
• Hubungan antar tabel
• Jenis data setiap kolom

Statistik ini memungkinkan pengoptimal untuk memperkirakan berapa banyak baris yang akan dikembalikan oleh suatu kueri dan seberapa besar beban kerja yang dibutuhkan oleh strategi eksekusi yang berbeda. Jika statistik menjadi usang atau tidak akurat, pengoptimal dapat memilih rencana eksekusi yang tidak efisien.

Estimasi kardinalitas mengacu pada prediksi jumlah baris yang akan dihasilkan dari setiap langkah dalam sebuah kueri. Misalnya, jika sebuah kueri menyaring baris menggunakan klausa WHERE seperti:

Wilayah LOKASI = 'Amerika Utara'

pengoptimal harus memperkirakan berapa banyak catatan yang cocok dengan filter tersebut.

Perkiraan ini mempengaruhi beberapa keputusan penting. Pengoptimal dapat menggunakannya untuk menentukan urutan tabel mana yang harus digabungkan, urutan gabungan yang paling efisien, algoritma penggabungan mana yang akan digunakan atau apakah pemindaian indeks harus digunakan alih-alih memindai tabel lengkap.

Indeks memungkinkan basis data untuk menemukan data tertentu lebih efisien daripada memindai seluruh tabel. Pengoptimal menggunakan indeks untuk mengurangi jumlah pekerjaan yang diperlukan untuk pengambilan data.

Jalur akses umum termasuk pemindaian tabel lengkap, yang membaca setiap baris dalam tabel; pemindaian indeks, yang membaca baris melalui struktur indeks; pencarian indeks, yang mengambil baris tertentu menggunakan pencarian indeks; dan pemindaian indeks saja, yang mengambil data langsung dari indeks tanpa mengakses tabel yang mendasarinya.

Memilih jalur akses yang benar dapat secara signifikan mengurangi jumlah pekerjaan yang diperlukan untuk menjalankan kueri, terutama saat bekerja dengan tabel besar.

Banyak kueri mengambil data dari beberapa tabel. Ketika hal ini terjadi, pengoptimal harus menentukan bagaimana tabel-tabel tersebut harus digabungkan. Algoritma penggabungan umum meliputi:

• Penggabungan loop bersarang: Membandingkan baris dari satu kumpulan data dengan baris dari kumpulan data lain secara berurutan. Pendekatan ini dapat berjalan dengan baik jika salah satu tabel berukuran relatif kecil atau jika indeks memungkinkan pencarian cepat untuk penggabungan dalam.
  
  
• Gabungan hash: Membuat tabel hash dari satu kumpulan data dan menggunakannya untuk mencocokkan baris dari kumpulan data lain secara efisien. Strategi ini sering bekerja dengan baik untuk kumpulan data besar.
  
  
• Gabungkan gabungan: Menggabungkan baris dari dua kumpulan data yang diurutkan dengan memindainya secara bersamaan.

Pengoptimal memilih di antara algoritma ini berdasarkan faktor-faktor seperti ukuran data, indeks yang tersedia, dan perkiraan jumlah baris.

Untuk memahami cara kerja pengoptimalan kueri, akan sangat membantu jika kita memandang SQL sebagai bahasa deklaratif: bahasa ini menjelaskan data apa yang harus diambil, bukan bagaimana data tersebut harus diambil.

Pengoptimal bertugas menentukan bagaimana melaksanakan permintaan, dan dengan cara yang paling efisien. Untuk mencapai hal ini, sebagian besar basis data mengikuti beberapa langkah pengoptimalan:

• Parsing dan validasi
• Menulis ulang kueri
• Menghasilkan rencana eksekusi
• Memperkirakan biaya rencana
• Memilih rencana eksekusi

Ketika kueri dikirimkan, basis data pertama mengurai pernyataan SQL dan memvalidasi sintaksnya. Selama tahap ini, sistem mengonfirmasi bahwa tabel, kolom, dan indeks yang direferensikan ada dan bahwa struktur kueri valid.

Ini juga memverifikasi bahwa objek yang relevan dalam skema basis data tersedia. Langkah ini memastikan bahwa basis data memahami permintaan sebelum mencoba mengoptimalkan atau menjalankannya.

Setelah parsing, basis data dapat menulis ulang kueri ke dalam bentuk setara yang dapat dieksekusi lebih efisien. Transformasi ini mempertahankan hasil kueri sambil meningkatkan struktur eksekusi. Teknik penulisan ulang kueri yang umum meliputi:

• Penurunan predikat, yang menerapkan filter pada tahap awal eksekusi kueri sehingga jumlah baris yang perlu diproses pada tahap selanjutnya menjadi lebih sedikit.
  
  
• Perataan subkueri, yang mengubah kueri bersarang menjadi gabungan yang sering kali dapat dieksekusi dengan lebih efisien.
  
  
• Pengurutan ulang gabungan, yang mengubah urutan tabel yang digabungkan untuk mengurangi hasil antara.
  
  
• Menghapus operasi yang berlebihan, seperti penyortiran yang tidak perlu atau penghapusan duplikat.

Transformasi ini memungkinkan pengoptimal untuk Jelajahi lebih lanjut strategi eksekusi yang lebih efisien tanpa mengubah hasil akhir. Mereka juga dapat membantu membatasi pemrosesan data yang tidak perlu.

Setelah kueri ditulis ulang, pengoptimal menghasilkan beberapa rencana eksekusi potensial. Setiap rencana mewakili strategi yang berbeda untuk mengambil data yang diminta.

Rencana mungkin berbeda berdasarkan indeks mana yang digunakan, urutan penggabungan tabel atau bagaimana hasil perantara diproses. Bahkan kueri yang relatif sederhana dapat menghasilkan beberapa strategi eksekusi yang mungkin.

Misalnya, satu kueri yang mengambil pesanan dari minggu lalu memiliki beberapa opsi: mungkin memindai tabel pesanan dan memfilter baris sesudahnya, menggunakan indeks pada tanggal pesanan untuk menemukan catatan terbaru dengan cepat atau mempersempit kumpulan data terlebih dahulu sebelum bergabung dengan pelanggan terkait atau tabel produk.

Pengoptimal selanjutnya mengevaluasi setiap rencana kandidat menggunakan model biaya. Model biaya memperkirakan berapa banyak pekerjaan yang perlu dilakukan basis data untuk melaksanakan rencana tertentu. Perkiraan ini biasanya mempertimbangkan faktor-faktor seperti:

• Persyaratan pemrosesan CPU
• Operasi I/O disk diperlukan untuk mengambil data
• Konsumsi memori untuk operasi seperti penyortiran atau hashing
• Transfer jaringan di lingkungan terdistribusi

Karena basis data tidak dapat mengetahui biaya pastinya sebelumnya, ia bergantung pada informasi statistik yang disimpan tentang data. Informasi tersebut membantu pengoptimal memperkirakan kemungkinan waktu pemrosesan dan menentukan algoritma dan struktur data pendukung mana yang paling tepat.

Setelah mengevaluasi rencana kandidat, pengoptimal memilih rencana dengan perkiraan biaya terendah. Strategi yang dipilih ini menjadi rencana eksekusi kueri, yang menjelaskan urutan operasi yang dilakukan basis data saat menjalankan kueri.

Rencana eksekusi yang efisien biasanya mencakup operasi seperti pemindaian tabel, penggabungan, penyortiran dan agregasi (misalnya, menggunakan GROUP BY atau LEFT JOIN). Pengguna dapat meninjau rencana EXPLAIN untuk melihat langkah-langkah yang diambil pengoptimal untuk mengambil data yang diminta.

Terlepas dari kecanggihan pengoptimal basis data modern, beberapa faktor dapat membuat pengoptimalan kueri menjadi sulit.

• Statistik yang tidak akurat: Jika statistik menjadi usang atau tidak lengkap, pengoptimal mungkin membuat asumsi yang salah tentang distribusi data. Hal ini dapat menyebabkan rencana eksekusi yang tidak efisien yang melakukan lebih banyak pekerjaan daripada yang diperlukan.

• Kemiringan data: Distribusi data yang tidak merata dapat menyulitkan pengoptimal untuk memperkirakan berapa banyak baris kueri yang akan dikembalikan. Ketika nilai-nilai tertentu muncul jauh lebih sering daripada yang lain, teknik estimasi standar dapat menghasilkan prediksi yang tidak akurat.

• Kueri kompleks: Kueri yang melibatkan banyak operasi penggabungan, operasi bersarang, atau subkueri dapat menghasilkan sejumlah besar rencana eksekusi potensial. Mengevaluasi setiap rencana yang mungkin tidak praktis, mengharuskan pengoptimal untuk mengandalkan heuristik dan perkiraan. Ini adalah salah satu alasan mengapa mengoptimalkan SQL Query menjadi lebih sulit seiring pertumbuhan sistem.

• Lingkungan data dinamis: Ketika data sering berubah, perilaku kueri juga dapat berubah seiring waktu. Rencana eksekusi yang sekali dilakukan secara efisien mungkin menjadi kurang efektif karena distribusi data berkembang.

Meskipun pengoptimalan kueri terjadi secara otomatis, pengembang, administrator, dan insinyur data dapat meningkatkan kinerja melalui beberapa teknik pengoptimalan.

Banyak platform basis data juga menyediakan alat bawaan yang membantu pengembang dan administrator menganalisis kinerja kueri dan mengidentifikasi rencana eksekusi yang tidak efisien.

Misalnya, SQL Server Management Studio (SSMS) dapat membantu memantau kinerja kueri dan mengidentifikasi hambatan; MySQL Workbench menyediakan alat untuk menganalisis rencana kueri dan mengoptimalkan eksekusi; dan Oracle SQL Tuning Advisor dapat menghasilkan rekomendasi otomatis untuk meningkatkan SQL Query.

Pengoptimalan kueri dan penyetelan kueri sangat berkaitan tetapi mewakili proses yang berbeda.

Pengoptimalan kueri mengacu pada basis data proses otomatis yang digunakan untuk menentukan strategi eksekusi yang efisien.

Sebaliknya, penyetelan kueri mengacu pada upaya manual untuk meningkatkan kinerja kueri. Upaya ini mungkin termasuk menulis ulang kueri yang tidak efisien, membuat indeks baru, memperbarui statistik atau menyesuaikan pengaturan konfigurasi basis data.

Dalam praktiknya, pengoptimalan kueri dan penyetelan kueri sering bekerja bersama-sama untuk meningkatkan kinerja basis data. Bersama-sama, mereka membentuk seperangkat strategi pengoptimalan praktis untuk meningkatkan kinerja SQL dalam sistem produksi.

Pengoptimalan kueri berkembang melampaui perencanaan berbasis biaya tradisional. Sistem basis data modern sekarang menggabungkan otomatisasi, eksekusi adaptif, dan kecerdasan buatan untuk meningkatkan cara kueri dianalisis dan dieksekusi.

Salah satu arah yang muncul adalah pengembangan kemampuan basis data otonom, di mana sistem terus memantau kinerja dan merespons masalah secara otomatis. Alih-alih bergantung sepenuhnya pada pemecahan masalah reaktif, sistem ini menganalisis perilaku beban kerja, kinerja kueri, dan sinyal sistem untuk mengidentifikasi potensi masalah kinerja secara dini dan merekomendasikan tindakan korektif.

Banyak arsitektur basis data otonom mengatur kemampuan ini menjadi tiga area operasional, sering didukung oleh agen AI.

• Pemeliharaan agentic mengotomatiskan tugas operasional rutin seperti tambalan, pemeriksaan kesehatan, dan pengoptimalan kinerja.
  
  
• Pemulihan agentik terus menganalisis perilaku sistem untuk mendeteksi anomali seperti regresi kueri, masalah penguncian, atau hambatan beban kerja sebelum berdampak pada pengguna.
  
  
• Respons agentik membantu tim menyelesaikan insiden lebih cepat dengan menganalisis apa yang berubah dalam sistem dan menampilkan insight kontekstual yang memandu remediasi.

Kemampuan agentik ini dirancang untuk beroperasi dalam model human-in-the-loop, di mana otomatisasi menangani tugas-tugas operasional yang terdefinisi dengan baik sementara tim basis data mempertahankan pengawasan terhadap sistem yang penting.

Ketika organisasi terus menskalakan platform data dan mengadopsi aplikasi berbasis AI, sistem yang dapat memantau, mengoptimalkan, dan memelihara diri mereka sendiri akan memainkan peran yang semakin penting dalam memastikan kinerja basis data yang andal.