Cara menggunakan mysql optimize inner join

Bahasan optimasi query ini akan dibagi menjadi beberapa part. Pada part satu, saya akan menjelaskan gambaran arsitektur MySQL dan optimizer-nya. Hal ini agar pembaca dapat melihat secara keseluruhan bagaimana query optimizer bekerja. Lalu dibagian ini juga, saya akan membahas bagaimana mengidentifikasi apakah sebuah query masih memiliki ruang untuk dioptimasi. Setelah itu, pada part berikutnya, saya akan membahas ke bagaimana optimasi dilakukan disertai dengan contoh-contoh praktis.

SQL adalah sebuah bahasa yang deklaratif, artinya statement tidak mendeskripsikan bagaimana sebuah proses dijalankan, tetapi statement mendeskripsikan hasil apa yang didapat. Contohnya, di dalam SQL, kita hanya perlu menulis kolom-kolom dari tabel tertentu beserta kondisinya untuk mendapatkan data dari sebuah database. Selanjutnya, peran query interpreter-lah yang menentukan bagaimana data tersebut didapatkan.

Untuk beberapa kasus, query interpreter dapat menjalankan tugasnya dengan baik. Namun, pada kasus-kasus lainnya query interpreter mengambil jalan yang “buruk” untuk mendapatkan sebuah data. Oleh karena itu, perlu tindakan tertentu agar query interpreter mengambil jalan yang “baik”. Tentunya diperlukan pengetahuan dasar mengenai bagaimana query interpreter bekerja.

Pada gambaran tingkat tinggi, MySQL terdiri dari tiga lapisan utama. Lapisan pertama bertugas untuk menangani koneksi antar klien dan server; penanganan koneksi, autentikasi, keamanan dan sebagainya. Lapisan kedua, yang menjadi pembahasan utama part ini, mencangkup penerjemahan dan optimasi query, caching, seluruh fungsi bawaan (date, times, math dsb), dan fungsi-fungsi seperti storage procedure, triggers, dan view. Bagian ketiga berisi storage engine, yang bertanggung jawab untuk menyimpan dan mengambil seluruh data di MySQL.

Dalam bahasan ini, kita hanya fokus pada bagian lapisan kedua, dimana penerjemahan, rencana dan optimasi query terjadi dibagian ini. Mari kita bahas ke secara umum bagaimana proses tersebut dilakukan.

Parser and name resolver

Parser dan name resolver bekerja dengan memecah query statement menjadi bagian-bagian kecil. Lalu bagian-bagian kecil tersebut dipetakan ke dalam objek database seperti kolom, tabel dan sebagainya.

Optimizer

Optimizer adalah tema utama pembahasan ini. Di dalamnya, terdapat proses transformasi query menjadi bentuk yang ekuivalen. Lalu proses evaluasi untuk menentukan rencana mana yang harus dipilih dengan mempertimbangkan harga terkecil.

Logical transformation

Pada bagian ini, MySQL mengubah query trivial menjadi bentuk yang ekuivalen secara logis sehingga lebih efisien tanpa mempengaruhi hasil akhirnya. Misalnya kondisi berikut

WHERE column1 = column2 AND column2 = 'x'

diubah menjadi

WHERE column1 = ’x’ AND column2 = ’x’.

Untuk informasi mengetahui lebih detil mengenai proses logical transformation, pembaca dapat melihat dokumentasi MySQL melalui tautan berikut:

• Equality and constant propagation
• Conversion of outer to inner join
• Subquery transformation
• Constant folding optimization

Prepare cost-based-optimization

Pada bagian ini hal yang diperlukan untuk melakukan cost-based optimization dikumpulkan. Ref access analysis mengumpulkan kemungkinan index yang bisa digunakan beserta urutannya. Range analysis mengumpulkan kemungkinan jenis index yang digunakan untuk optimasi dibandingkan dengan harga akses tabel.

Cost-based optimization

MySQL menggunakan metode cost-based optimization untuk menentukkan bagiamana sebuah query dijalankan. Secara sederhana prosesnya berjalan seperti berikut:

1. Tentukan harga untuk masing-masing operasi dalam satu rencana
2. Jumlahkan totalnya
3. Evaluasi total harga untuk setiap rencana yang mungkin
4. Pilih rencana dengan harga total terendah

Misalnya kita ingin melakukan query dengan yang ada join di dalamnya. Optimizer akan menentukan harga untuk satu operasi dengan cara mengalikan unit harga dengan konstanta operasi, misalnya jumlah baris yang dibaca dikali konstanta disk read. Kemudian semua operasi dalam satu rencana dihitung dan dijumlahkan harganya menghasilkan harga total dalam satu rencana. Selanjutnya, optimizer akan mencari seluruh kemungkinan rencana dan menghitung total harga lalu menentukan harga paling dari seluruh rencana tersebut.

Idealnya, seluruh kemungkinan rencana akan dievaluasi untuk mendapatkan harga terendah. Namun, ketika jumlah tabel yang akan di join semakin banyak, rencana yang mungkin akan bertambah secara eksponensial. Hal ini akan berimbas pada beban kinerja evaluasi optimizer yang akan berujung pada lamanya proses evaluasi optimasi. Untuk mengatasi itu, kita dapat mengubah variabel yang menentukan sejauh mana evaluasi optimizer dilakukan seperti yang dijelaskan di dokumentasi berikut.

Plan refinement

Table condition pushdown menentukan index mana yang dapat dipakai atau index mana yang membutuhkan evaluasi kembali. Prepare temporary table menunjukkan temporary table mana yang akan digunakan.

Query execution

Query akan dieksekusi sesuai dengan query execution plan terpilih.

Sumber:

High Performance MySQL. O’Reilly (2008)

Percona Live Open Source Database Conference 2019: The MySQL Query Optimizer Explained Through Optimizer Trace