Langkah pertama adalah memprioritaskan kueri yang akan dioptimalkan:

1. Mengidentifikasi pertanyaan database yang membutuhkan waktu respons terbaik 
2. Mengidentifikasi pertanyaan database yang sering dijalankan

Kedua kategori ini perlu dioptimalkan selama desain skema database.

Transaksi adalah unit logis di mana semua pernyataan yang ada akan di-commit sepenuhnya atau roll back. Transaksi adalah fitur yang menyediakan atomik, konsistensi, isolasi, dan ketahanan (ACID) untuk MySQL. 

Di InnoDB, mesin penyimpanan untuk MySQL, semua aktivitas pengguna terjadi di dalam transaksi. Secara default, mode autocommit diaktifkan, yang berarti setiap pernyataan SQL membentuk satu transaksi sendiri. Untuk melakukan transaksi beberapa pernyataan jika autocommit diaktifkan, mulai transaksi secara eksplisit dengan START_TRANSACTION atau START_TRANSACTION dan akhiri dengan START_TRANSACTION atau START_TRANSACTION. Jika mode autocommit dinonaktifkan, sesi selalu memiliki transaksi yang terbuka hingga COMMIT atau COMMIT mengakhirinya dan memulai transaksi baru. 

Praktik terbaik dalam melakukan transaksi adalah membuatnya sesingkat mungkin. Karena transaksi yang panjang memiliki beberapa kelemahan seperti yang akan dijelaskan lebih lanjut dalam artikel ini.

1. Pertentangan kunci yang berkepanjangan, yang menyebabkan kueri lebih lambat dan potensi kegagalan kueri

• Penguncian tingkat baris InnoDB ditahan selama durasi transaksi
• Hal ini berpotensi meningkatkan waktu tunggu kunci, waktu tunggu habis kunci, dan deadlock yang membuat kueri lebih lambat atau gagal sepenuhnya

2. Performa server menurun karena banyaknya log yang diurungkan 

• Karena Kontrol Konkurensi Multi-versi (MVCC) InnoDB, versi lama dari baris yang diubah disimpan dalam log urung agar pembacaan dan rollback konsisten. Dengan level isolasi repeatable-read default, log urung tidak akan dihapus permanen hingga transaksi dimulai sebelum transaksi selesai. Oleh karena itu, transaksi yang berjalan lama mengakumulasi log urung. Hal ini dapat diamati dan dipantau melalui daftar histori pada fungsi command line SHOW ENGINE INNODB STATUS.
• Jika daftar histori mencapai lebih dari jutaan, performa server akan terpengaruh secara negatif karena pertentangan mutex pada segmen rollback, volume yang meningkat untuk membaca log urung, dan bertambahnya waktu untuk melintasi daftar tertaut dari log urung. Tindakan ini juga menciptakan lebih banyak pekerjaan untuk menghapus permanen thread.

3. Peningkatan penggunaan disk

• Peningkatan jumlah log urung yang tersimpan di disk, baik dalam tablespace sistem atau tablespace urung

4. Waktu penonaktifan yang lebih lama

• Selama penonaktifan normal, transaksi yang sedang berlangsung akan di-roll back. Waktu rollback sering kali lebih lama daripada waktu yang dibutuhkan untuk mencapai intinya. Oleh karena itu, penonaktifan server dapat memerlukan waktu lama untuk rollback.

5. Waktu pemulihan error yang lebih lama

• Selama pemulihan error, InnoDB akan mengulangi transaksi dari checkpoint terakhir dan membatalkan roll transaksi yang tidak di-commit. Transaksi yang panjang akan membuat langkah terkait membutuhkan waktu yang lebih lama.

• Kueri SELECT
• Kueri ini tidak menyimpan kunci baris
• Kueri ini dapat mengurungkan log terakumulasi
• Lihat bagian pengoptimalan kueri di bawah ini
• Kueri UPDATE/INSERT/DELETE
• Kueri batch berperforma lebih baik daripada perubahan baris tunggal lainnya
• Memecah dan membatasi waktu eksekusi batch menjadi beberapa detik

• Pertimbangkan untuk memisahkan kueri SELECT
• Jika ada logika aplikasi di antara kueri database, pertimbangkan untuk memisahkan transaksi
• Perkirakan jumlah kunci baris yang akan ditahan untuk setiap pernyataan
• Evaluasi urutan eksekusi untuk meminimalkan penguncian baris
• Cari peluang untuk mengurangi ukuran transaksi

Kueri dapat ditangkap di sisi aplikasi atau sisi database.

Praktik pengembangan yang baik adalah mencatat log kueri database dan waktu eksekusi kueri. Pencatatan sisi aplikasi memudahkan evaluasi efektivitas dan efisiensi kueri dalam konteks bisnis. Misalnya, pengguna mungkin mencatat setiap waktu respons kueri atau mencatat waktu respons untuk fungsi tertentu. Ini juga merupakan cara mudah untuk mendapatkan total waktu eksekusi untuk transaksi multi-pernyataan. 

Selain itu, waktu respons kueri yang diukur dari logging sisi aplikasi adalah pengukuran menyeluruh, termasuk waktu jaringan. Hal ini melengkapi waktu eksekusi kueri yang dicatat dari database dan memudahkan identifikasi apakah masalah tersebut berkaitan dengan masalah jaringan atau database.

Insight Kueri MySQL Cloud SQL

Alat Cloud SQL Query Insights memungkinkan penangkapan, pemantauan, dan diagnosis kueri.

Query Insights memudahkan Anda menemukan kueri teratas berdasarkan waktu eksekusi dan frekuensi eksekusi.

Alat ini memiliki opsi pemfilteran seperti rentang waktu, database, akun pengguna, dan alamat klien. Alat ini memiliki grafik untuk menunjukkan penggunaan CPU, dan perincian IO dan waktu tunggu kunci. Tabel “Kueri dan tag teratas” mencantumkan kueri teratas berdasarkan waktu eksekusi dengan kueri yang dinormalisasi. Selain waktu eksekusi, alat ini juga menyertakan statistik tentang "baris rata-rata yang dipindai" dan "baris rata-rata yang ditampilkan" yang memberikan insight tentang efisiensi kueri.

Lihat dokumentasi untuk melihat semua yang ditawarkan dan cara mengaktifkannya.

Menggunakan performance_schema

Di Cloud SQL untuk MySQL, fitur performance_schema diaktifkan secara default untuk MySQL 8.0.26 dan versi yang lebih tinggi dengan memori lebih dari 15 GB. Mengaktifkan atau menonaktifkannya mengharuskan instance dimulai ulang. 

Jika performance_schema=ON, instrumen pernyataan kueri diaktifkan secara default. Tabel sys.statement_analysis memberikan statistik gabungan untuk kueri yang dinormalisasi. Bagian ini menjawab pertanyaan seperti: 

• Kueri mana yang dipindai oleh tabel penuh?
• full_scan/exec_count: untuk mengetahui apakah kueri sering melakukan pemindaian tabel penuh yang sering kali tidak efisien 
• Kueri mana yang berjalan lambat?
• avg_latency: waktu eksekusi kueri rata-rata
• Kueri mana yang tidak efisien? 
• rows_examined_avg/rows_examined_avg: ini adalah untuk kueri baca. Rasio idealnya adalah 1. Semakin besar rasionya, semakin tidak efisien kuerinya. 
• rows_examined_avg/rows_examined_avg: ini untuk kueri tulis. Rasio idealnya adalah 1. Semakin besar rasionya, semakin tidak efisien kuerinya. 
• Kueri mana yang menggunakan tabel sementara dan harus dikonversi menjadi tabel sementara pada disk? 
• tmp_disk_tables/tmp_disk_tables: untuk melihat apakah tmp_disk_tables/tmp_disk_tables sudah cukup
• Kueri mana yang menggunakan filesort? 
• rows_sorted/rows_sorted, rows_sorted/rows_sorted: untuk mengidentifikasi kueri dengan penyortiran dalam jumlah besar dan dapat menggunakanrows_sorted yang lebih besar

Jika Anda menggunakan MySQL Workbench, alat itu memiliki laporan skema performa berdasarkan tampilan sistem. Laporan ini memiliki bagian tentang "Pernyataan SQL Biaya Tinggi", yang memberikan insight mengenai performa kueri.

Menggunakan log lambat + alat

Log lambat menangkap semua kueri yang berjalan lebih lama daripada long_query_time. Fungsi ini juga mencatat waktu eksekusi kueri, waktu kunci, baris data yang diperiksa, dan baris data yang dikirim. Statistik eksekusi tambahan menjadikannya kandidat yang lebih disukai untuk menganalisis kueri database daripada menggunakan log umum.

Praktik yang baik adalah mengaktifkan log lambat. Biasanya, long_query_time harus dipertahankan pada batas yang wajar untuk menangkap kueri yang ingin Anda lihat dan optimalkan. 

log_output=FILE

slow_query_log=ON

long_query_time=2

Sesekali, sebaiknya tetapkan long_query_time=0 untuk menangkap semua kueri dalam waktu singkat dan mendapatkan ringkasan tentang volume dan performa kueri. 

Ada beberapa alat seperti mysqldumpslow dan pt-query-digest, yang akan mengekstrak tanda tangan kueri dan membuat laporan untuk menampilkan statistik kueri.   

Ada alat pemantauan pihak ketiga lainnya yang menghasilkan laporan tentang statistik kueri, seperti Percona Monitoring and Management, SolarWinds Database Performance Monitor (sebelumnya VividCortex), dan banyak lagi.

Setelah menangkap kueri dalam transaksi, langkah berikutnya adalah mengoptimalkannya.

Perintah EXPLAIN menyediakan rencana eksekusi kueri dan dari 8.0.18, perintah EXPLAIN ANALYZE akan menjalankan pernyataan dan menghasilkan output EXPLAIN bersama dengan waktu dari eksekusi. 

Insight Kueri MySQL menyediakan akses praktis ke paket EXPLAIN.

Apa yang kita cari dalam output-nya? 

• Kolom `rows` menunjukkan jumlah baris yang harus dibaca 
• IO adalah bagian yang paling memakan waktu. Jika kueri perlu membaca data dalam jumlah besar, kemungkinannya akan lambat. Untuk mendapatkan gambaran kasar tentang itu, kalikan `baris` di antara tabel-tabel gabungan. Pada contoh di atas, hasilnya adalah 858 * 23523. Untuk membaca 23523 baris dari t2 untuk masing-masing dan setiap 858 baris dari t1 terdengar kurang optimal. Dengan demikian, pengoptimalan akan mengurangi jumlah akses data dari t2 untuk setiap iterasi.
• Kolom `type` menjelaskan jenis penggabungan tabel 
• Jenis 'index' berarti indeks dipindai. Jika indeks memenuhi semua data yang diperlukan dari tabel, kolom Extra akan menampilkan "Menggunakan indeks" 
• Jenis 'range' berarti bahwa tidak hanya indeks yang digunakan, tetapi juga kondisi rentang disediakan untuk membatasi pemindaian data. 
• Untuk tabel berikutnya dalam urutan penggabungan, jenis 'eq_ref' berarti satu baris dibaca dari tabel ini untuk setiap kombinasi baris dari tabel sebelumnya, yang merupakan yang paling efisien. 
• Jenis 'ref' berarti pencocokan indeks adalah 1:m, bukan 1:1. Lebih dari satu baris akan dibaca dari tabel ini untuk setiap kombinasi baris dari tabel sebelumnya. 
• Jenis yang harus dihindari adalah “ALL”. Ini berarti bahwa pemindaian tabel lengkap dilakukan untuk setiap kombinasi baris dari tabel sebelumnya. 
• Kolom `key` menunjukkan indeks sebenarnya yang digunakan. 
• Pemilihan indeks yang akan digunakan didasarkan pada kardinalitas indeks yang mungkin sudah tidak berlaku. Oleh karena itu, penting untuk memverifikasi bahwa indeks yang paling selektif yang digunakan.  
• Kolom `key_len` menunjukkan panjang kunci dalam byte. 
• Dengan indeks multi-kolom, key_len memberi tahu tentang bagian indeks yang digunakan. Misalnya, jika indeks memiliki (col1, col2, col3), dan kondisi kueri adalah "col1 = n dan col2 seperti '%string%'", maka hanya col1 yang akan digunakan untuk pemfilteran indeks. Jika kueri dapat diubah menjadi "col1 = n dan col2 seperti 'string%'", maka keduanya (col1, col2) akan digunakan untuk pemfilteran indeks. Perubahan kecil ini dapat membuat perbedaan yang dramatis dalam performa kueri. 
• Kolom `Extra` berisi informasi tambahan tentang paket kueri 
• “Menggunakan sementara” berarti tabel sementara internal dibuat yang dapat menghasilkan tabel sementara pada disk
• “Menggunakan filesort” berarti pengurutan tidak dapat memanfaatkan indeks apa pun dan memerlukan buffer pengurutan, serta berpotensi file disk sementara 
• “Menggunakan indeks” berarti semua data yang diperlukan dari tabel ini terdapat dalam indeks; tidak perlu membaca baris data

Variabel status sesi dapat digunakan untuk mendapatkan detail eksekusi kueri. 

Pertama, hapus variabel sesi, lalu jalankan kueri dan periksa penghitung. Misalnya, status Handler_* menampilkan pola akses data dan jumlah baris. Created_* akan muncul jika tabel sementara dan/atau tabel sementara pada disk dibuat. Sort_* akan menampilkan jumlah penerusan penggabungan pengurutan dan jumlah baris yang diurutkan. Variabel sesi lainnya dijelaskan dalam dokumentasi.

Pernyataan SHOW PROFILE menyediakan waktu eksekusi kueri pada tahap eksekusi yang juga dapat menjadi informasi yang bermanfaat.

Setelah rencana eksekusi kueri dipahami, ada beberapa cara untuk memengaruhi dan mengoptimalkannya. 

• Tambahkan atau update definisi indeks 
• Untuk pemfilteran yang lebih baik, lebih sedikit akses data
• Untuk pengurutan, hindari filesort
• Perbarui statistik indeks jika nonaktif
• ANALYZE TABLE <tbl>;
• Kemudian periksa kembali output rencana EXPLAIN
• Gunakan Petunjuk Indeks
• Untuk menyarankan atau memaksa indeks tertentu digunakan untuk pemfilteran, gabungkan atau urutkan menurut / kelompokkan menurut 
• Gunakan STRAIGHT_JOIN untuk menentukan urutan penggabungan tabel 
• Gunakan Petunjuk Pengoptimal

Untuk mengoptimalkan konfigurasi server untuk kueri tertentu, sebaiknya gunakan variabel tingkat sesi, bukan mengubah nilai global yang memengaruhi semua sesi. 

Nilai sesi yang sering digunakan adalah:

Singkatnya, untuk penyesuaian kueri, kami membahas tiga aspek: 

• Buat keputusan sadar saat menulis kueri. Keputusan ini merupakan faktor penentu performa kueri, throughput server secara keseluruhan, dan performa server. 
• Melacak data eksekusi kueri di sisi aplikasi dan sisi database. Pencatatan log sisi aplikasi adalah hal penting. Ini dapat dikonfigurasi berdasarkan kepentingan bisnis dan mencerminkan operasi bisnis. 
• Terakhir, ada beberapa alat yang membantu Anda memahami rencana eksekusi kueri, biaya yang terkait dengan berbagai langkah, dan cara mengoptimalkan kueri.