Mesin diagnostik SQL berbasis biaya - Database Autonomy Service

Topik ini menjelaskan mesin diagnostik SQL berbasis biaya yang disediakan oleh Database Autonomy Service (DAS).

Informasi latar belakang

Basis data merupakan komponen penting dalam sistem layanan. Sebagian besar skenario bisnis memerlukan penggunaan basis data, dan gangguan kecil pada basis data dapat memengaruhi layanan secara negatif. Untuk performa bisnis yang optimal, stabilitas basis data harus terjaga dan sering dioptimalkan. Ini menjadi kebutuhan utama bagi layanan. Dalam banyak kasus, aplikasi, instans, dan pernyataan SQL dapat dioptimalkan untuk meningkatkan layanan basis data.

Aplikasi: Logika kode aplikasi dapat dioptimalkan agar aplikasi memproses data lebih efisien.
Instans: Nilai parameter lingkungan dapat disesuaikan agar instans bekerja lebih efisien.
Pernyataan SQL: Pernyataan SQL dapat dioptimalkan dengan metode seperti penyediaan basis data fisik atau penulisan ulang pernyataan SQL, sehingga data dapat di-query lebih efisien.

Dalam banyak situasi, pengembang lebih familiar dengan optimasi aplikasi dan instans dibandingkan optimasi pernyataan SQL. Mereka mungkin mempertanyakan apakah administrator basis data atau pengembang aplikasi bertanggung jawab atas optimasi SQL. Optimasi SQL adalah bagian penting dari optimasi basis data. Jika query SQL online memiliki masalah performa, layanan Anda akan memproses data dengan laju lebih rendah atau bahkan menjadi tidak stabil.

Sekitar 80% masalah basis data dapat diselesaikan melalui optimasi SQL. Namun, optimasi SQL adalah proses kompleks yang memerlukan keahlian dan pengalaman dalam basis data.

Pengembang harus mengidentifikasi hambatan rencana eksekusi dan menentukan rencana eksekusi optimal menggunakan metode seperti optimasi basis data fisik dan penulisan ulang pernyataan SQL. Proses ini memakan waktu karena beban kerja eksekusi pernyataan SQL dan jumlah data yang terlibat sangat besar serta sering berubah, membuat optimasi SQL menjadi kompleks dan mahal.

Tantangan

Berikut adalah dua pertanyaan umum tentang fitur diagnostik dan optimasi SQL:

Apakah fitur ini andal?
Apakah fitur ini komprehensif?

Jawaban untuk kedua pertanyaan tersebut adalah ya. Untuk memastikan bahwa fitur diagnostik dan optimasi SQL andal dan komprehensif, tantangan berikut harus diatasi:

Bagaimana algoritma rekomendasi yang andal dipilih untuk menghasilkan saran optimasi yang andal?
Dalam bidang diagnostik dan optimasi SQL, algoritma berbasis aturan dan model biaya berbasis algoritma adalah pendekatan umum untuk memberikan saran optimasi.
- Metode Berbasis Aturan
  Metode berbasis aturan banyak digunakan dalam basis data, terutama dalam basis data tanpa kemampuan What-If Tool (WIT), seperti MySQL. Metode ini sederhana dan mudah diimplementasikan, tetapi rekomendasinya kurang fleksibel dan tidak selalu memenuhi persyaratan relevan. Contohnya, metode berbasis aturan digunakan untuk merekomendasikan indeks untuk query SQL sederhana berikut:
```
SELECT *
FROM t1
WHERE time_created >= '2017-11-25'
AND consuming_time > 1000
ORDER BY consuming_time DESC;
```
  Indeks kandidat berikut dihasilkan berdasarkan metode berbasis aturan:
```
IX1(time_created)
IX2(time_created, consuming_time)
IX3(consuming_time)
IX4(consuming_time, time_created)
```
  Anda tidak dapat menentukan indeks mana yang direkomendasikan. Jika index ORing atau index ANDing digunakan, beberapa indeks dapat direkomendasikan. Masalah ini disebabkan oleh ketidakmampuan metode berbasis aturan memberikan rekomendasi tepat. Dalam contoh ini, pernyataan SQL dieksekusi untuk menanyakan data dari tabel tunggal. Jika beberapa tabel perlu digabungkan dan query SQL berisi subquery kompleks, hasil rekomendasi lebih sulit ditentukan.
- Metode Berbasis Biaya
  Fitur diagnostik dan optimasi SQL DAS diimplementasikan menggunakan metode berbasis model biaya. Hal ini memungkinkan DAS mengevaluasi masalah optimasi dengan cara yang sama seperti optimizer basis data. DAS dapat mengevaluasi semua kandidat potensial dan memberikan hasil rekomendasi menggunakan metode berbasis model biaya. Dalam kasus ekstrem, DAS mungkin tidak dapat mengevaluasi semua kandidat tetapi tetap mengevaluasi sebanyak mungkin kandidat. Ini karena solusi optimal digunakan untuk menyelesaikan masalah non-deterministik polinomial (NP). Untuk basis data open source seperti MySQL, fitur ini dapat menghadapi masalah berikut:
  - Kekurangan Kemampuan WIT: Kemampuan WIT yang disediakan oleh optimizer kernel basis data tidak dapat digunakan kembali untuk mengevaluasi biaya solusi optimasi kandidat.
  - Kekurangan Statistik: Biaya rencana eksekusi dihitung untuk mengevaluasi biaya solusi optimasi kandidat. Jika statistik tidak tersedia, biaya solusi optimasi kandidat tidak dapat dievaluasi.
Bagaimana fitur diagnostik dan optimasi SQL dapat sepenuhnya kompatibel dengan pernyataan SQL?
Fitur diagnostik dan optimasi SQL yang dikembangkan harus sepenuhnya kompatibel dengan pernyataan SQL. Fitur ini harus mendukung penguraian SQL dan verifikasi semantik SQL. Kompatibilitas SQL langsung terkait dengan kelengkapan fitur dan tingkat keberhasilan diagnostik SQL.
Bagaimana set uji coba kemampuan komprehensif dapat dibangun?
Diagnostik dan optimasi SQL telah lama dianggap sebagai tantangan. Keahlian optimasi basis data perlu dimasukkan ke dalam fitur. Perpustakaan uji coba besar perlu dibangun untuk memverifikasi kemampuan inti fitur. Perpustakaan ini dapat digunakan untuk mengukur dan meningkatkan kemampuan fitur. Perpustakaan uji coba yang komprehensif dan akurat sangat penting untuk meningkatkan fitur.
Persyaratan berikut harus dipenuhi untuk membangun perpustakaan uji coba yang memenuhi syarat:
- Kelengkapan: Optimasi SQL dapat dipengaruhi oleh banyak faktor. Misalnya, pemilihan indeks dipengaruhi oleh ratusan faktor, dan kombinasi faktor-faktor ini membentuk kumpulan besar tanda tangan kasus. Tugas yang menuntut adalah memetakan tanda tangan ini ke kasus uji satu per satu.
- Keahlian dan Informasi yang Cukup: Keahlian dan informasi yang cukup diperlukan untuk merancang kasus uji. Contohnya, kasus rekomendasi indeks mencakup informasi berikut:
  - Desain Skema: Data skema mencakup tabel, indeks yang ada, dan batasan.
  - Berbagai Jenis Statistik.
  - Parameter Lingkungan.
Bagaimana kemampuan diagnostik skala besar dapat dibangun?
Fitur diagnostik dan optimasi SQL harus mampu melayani jutaan instans basis data cloud. Kemampuan online fitur ini menghadapi tantangan besar. Contohnya, arsitektur komputasi yang kompleks diperlukan untuk mengimplementasikan pemisahan layanan dan node komputasi dapat ditambahkan atau dihapus. Tantangan lain termasuk jumlah maksimum permintaan query konkuren, kontrol konkurensi dalam lingkungan akses sumber daya terdistribusi, penjadwalan tugas valid berdasarkan prioritas darurat yang berbeda, dan penanganan nilai puncak.

Solusi

Fitur diagnostik dan optimasi SQL adalah salah satu fitur inti DAS. DAS memeriksa dan menganalisis pernyataan SQL, lalu memberikan saran optimasi profesional seperti saran tentang indeks dan optimasi SQL, serta informasi seperti peningkatan performa yang diperkirakan. Ini membantu Anda memaksimalkan performa eksekusi pernyataan SQL.

Fitur optimasi SQL otomatis DAS mengubah proses optimasi Anda dari proses padat tenaga manusia menjadi proses otomatis-intelejen.

Pembangunan kemampuan

Bagian ini menjelaskan cara membangun arsitektur inti dan set uji coba kemampuan mesin diagnostik dan optimasi SQL DAS. Set uji coba kemampuan sama dengan perpustakaan kasus uji fitur diagnostik dan optimasi SQL.

Core architecture of the SQL diagnostics and optimization engine

Gambar di atas menunjukkan arsitektur inti mesin diagnostik dan optimasi SQL. Mesin ini adalah optimizer yang independen dari basis data. Mesin ini dapat secara otomatis mengumpulkan statistik lengkap dan menghitung biaya rencana eksekusi. Dengan cara ini, mesin menyediakan kemampuan yang mirip dengan kemampuan WIT. Daftar berikut menjelaskan alur kerja mesin:

Penguraian dan Verifikasi SQL: Mesin mengurai pernyataan SQL, memverifikasi apakah sintaks pernyataan query input sesuai dengan format standar, mengidentifikasi elemen pernyataan query, lalu membentuk pohon sintaks. Pohon sintaks mencakup predikat, jenis predikat, bidang pengurutan, bidang agregasi, bidang query, dan tipe data bidang dalam pernyataan query. Mesin memeriksa apakah tabel dan bidang yang ditentukan dalam pernyataan SQL sesuai dengan skema basis data yang ditentukan.
Generasi Indeks Kandidat: Mesin menghasilkan beberapa kombinasi indeks kandidat berdasarkan pohon sintaks yang dihasilkan.
Evaluasi Berbasis Biaya: Optimizer bawaan yang independen dari kernel basis data mendapatkan statistik basis data. Statistik disimpan dalam cache di mesin diagnostik. Optimizer bawaan untuk mesin diagnostik menghitung biaya indeks kandidat berdasarkan statistik dan mengevaluasi biaya setiap indeks serta biaya setiap metode yang digunakan untuk menulis ulang pernyataan SQL. Dengan cara ini, indeks optimal atau metode optimal yang digunakan untuk menulis ulang pernyataan SQL dapat dipilih berdasarkan biaya.
Penggabungan Indeks dan Pemilihan Indeks Optimal: Mesin dapat memproses satu atau lebih pernyataan SQL atau semua pernyataan SQL yang dieksekusi pada instans basis data. Mesin menghasilkan rekomendasi indeks untuk beberapa pernyataan SQL. Indeks fisik yang ada dan rekomendasi indeks untuk pernyataan SQL yang berbeda mungkin mencakup indeks yang sama, indeks dengan awalan yang sama, dan indeks serupa.

Set uji komprehensif harus dibangun untuk menguji dan meningkatkan kemampuan mesin diagnostik dan optimasi SQL. Fitur diagnostik dan optimasi SQL dikembangkan. Kemampuan yang dikembangkan berpusat pada sistem kasus penggunaan, seperti yang ditunjukkan pada gambar berikut.

Kasus uji dijelaskan berdasarkan tanda tangannya sehingga perpustakaan tanda tangan komprehensif untuk kasus uji dapat dibuat. Ini adalah prinsip dasar yang digunakan untuk membuat kasus uji kemampuan.

Di Alibaba Group, semua pernyataan SQL yang dieksekusi pada instans basis data dikumpulkan dan disimpan secara real-time. Deskripsi berbasis tanda tangan digunakan berdasarkan ekosistem Alibaba dan berbagai skenario SQL untuk menganalisis dan mencari sejumlah besar sumber daya SQL penuh online, dan dengan demikian memperoleh kasus nyata yang memenuhi tanda tangan yang ditentukan dan mengekstrak informasi yang diperlukan untuk kasus uji. Dengan cara ini, perpustakaan kasus uji dapat dibangun.

Catatan Semua data dalam perpustakaan kasus uji dikumpulkan dari bisnis internal Alibaba Group. Informasi online yang diekstraksi, seperti informasi statistik, dienkripsi dan disembunyikan. Proses ini otomatis.

Fitur dalam perpustakaan tanda tangan kasus uji dibandingkan dengan tanda tangan yang termasuk dalam perpustakaan kasus uji untuk mengevaluasi kelengkapan dan cakupan kasus uji. Anda dapat mengevaluasi kasus uji berdasarkan empat aspek berikut:

Kasus uji apa dalam perpustakaan tanda tangan yang dicakup oleh kasus uji dan seberapa lengkap?
Kasus uji apa dalam perpustakaan tanda tangan yang tidak dicakup oleh fitur diagnostik dan optimasi SQL atau gagal verifikasi SQL?
Kasus uji apa dalam perpustakaan tanda tangan yang sering mengalami masalah regresi dalam periode waktu tertentu?
Berapa tingkat cakupan kasus uji di setiap tingkat kemampuan?

Optimasi

Sebelum fitur diagnostik dan optimasi SQL DAS dirilis di Alibaba Cloud, fitur ini telah berjalan di layanan Alibaba Group selama bertahun-tahun. Selama periode ini, fitur ini mendiagnosis rata-rata sekitar 50.000 pernyataan SQL per hari. Dengan cara ini, pernyataan SQL untuk aplikasi Alibaba Cloud dioptimalkan secara efisien. Fitur diagnostik dan optimasi SQL dapat digunakan dalam skenario berikut:

Optimasi Kustom: Fitur diagnostik dan optimasi SQL dapat digunakan untuk mendiagnosis pernyataan SQL abnormal yang ditentukan oleh pengguna Alibaba Group dan memberikan saran optimasi ahli untuk pernyataan SQL tersebut.
Optimasi Otomatis: Fitur diagnostik dan optimasi SQL secara otomatis mengidentifikasi query lambat berdasarkan beban kerja instans basis data, mendiagnosis pernyataan SQL, lalu menghasilkan saran optimasi. Setelah fitur mengevaluasi saran, fitur menjadwalkan tugas optimasi, menyelesaikan optimasi secara online, dan memantau performa pernyataan SQL. Proses optimasi sepenuhnya otomatis untuk meningkatkan performa basis data. Dengan cara ini, instans basis data Anda selalu dapat berjalan dalam kondisi optimal.

Sistem analisis dan umpan balik aktif disediakan untuk fitur diagnostik dan optimasi SQL. Dengan cara ini, kasus rollback yang dihasilkan saat pernyataan SQL dioptimalkan secara otomatis, kasus diagnostik online yang gagal, dan kasus yang diidentifikasi oleh umpan balik pengguna secara otomatis dikirim ke sistem kasus penggunaan. Ini mengulangi dan meningkatkan fitur.

Mesin basis data yang didukung

Diagnostik SQL mendukung mesin basis data berikut: ApsaraDB RDS for MySQL, PolarDB for MySQL, ApsaraDB RDS for PostgreSQL, dan PolarDB for PostgreSQL (Kompatibel dengan Oracle).

Catatan Mesin diagnostik SQL berbasis biaya dapat merekomendasikan Indeks Kolom dalam Memori (IMCI) optimal untuk mesin basis data PolarDB for MySQL. Untuk informasi lebih lanjut tentang fitur IMCI, lihat Ikhtisar.