Optimasi Database (MySQL)

Teknik optimasi database adalah hal yang cukup merepotkan pada database dengan jumlah record sedikit, namun akan sangat berguna bahkan bisa dibilang “sebuah kewajiban” untuk database dengan ribuan bahkan mungkin bisa membludak sampe ratusan ribu.

Dulu sewaktu saya awal-awal belajar database menggunakan MySQL, saya pernah beberapa kali membaca artikel tentang optimasi database. Namun karena saat itu saya tidak merasa perlu, artikel-artikel itu saya abaikan saja. Bahkan saat setelah beneran bekerja menggunakan database, teknik optimasi yang pernah saya baca pun tetap saja banyak yang saya abaikan. Setelah record database membludak, saya baru merasa menyesal mengabaikan optimasi database.

Saya menulis artikel ini dengan bumbu-bumbu pengalaman saya sendiri, agar pembaca (terutama yang baru belajar atau menggunakan database) tidak melakukan kesalahan yang sama. Kalau dari awal kita sudah banyak melakukan “dosa” saat membuat struktur table dan query database, seringkali saat jumlah record sudah membludak, kita akan takut mengubah struktur database apalagi sampai mengubah query. Mungkin karena malas, karena perlu waktu yang lama untuk mengubahnya. Atau mungkin malah takut karena database kita sudah telanjur digunakan pada aplikasi yang kompleks. Sedikit saja salah melakukan langkah perubahan, bisa-bisa yang terjadi malah membuat aplikasi error.

Oke, sekarang ke inti optimasi database MySQL, tapi sebagian besar juga bisa diterapkan untuk semua database. Secara garis besar, optimasi database terbagi menjadi dua kelompok yaitu optimasi struktur table dan juga optimasi query.
Optimasi Struktur table:

1. Pastikan untuk membuat primary key untuk setiap table yang dibuat. Alasannya tentu saja untuk memberikan indexing inti pada tiap table. Dan biasanya primary key adalah field paling mudah digunakan untuk pencarian menggunakan WHERE dan juga untuk relasi tabel dengan JOIN.
2. Lebih baik primary key dengan tipe data Integer. Meskipun kita field unik no_pelanggan di tabel pelanggan dengan pattern “xxx-9999” (char atau varchar), usahakan anda menambahkan satu field lagi yang berperan sebagai primary key dengan tipe data integer.
3. Hindari null dan gunakan not null. Ini yang paling sering saya lupakan dan saya sudah merasakan akibatnya beberapa kali. Disamping ukuran field null lebih besar dari not null, mengijinkan field untuk menerima inputan null seringkali membuat kita harus bekerja dua kali saat menggunakannnya dalam applikasi. Sebagai gantinya kita bisa mengeset inputan default untuk memastikan field tersebut tidak kosong (yang berpotensi menimbulkan error).
4. Tambahkan index, tapi jangan terlalu banyak. Index dapat meningkatkan kecepatan SELECT namun juga menurunkan kecepatan INSERT dan UPDATE, maka kita harus berhati-hati memilih field mana yang perlu diberikan index mana yang tidak. Prioritas memasang index pada suatu field adalah yang sering digunakan untuk 1) WHERE clause 2) JOIN clause 3) ORDER BY clause 4) SELECT clause. Memberikan index untuk semua field sepertinya bukan ide yang bagus.
5. Gunakan tipe data yang sesuai. Menggunakan tipe data Text untuk field nama hanya akan membebani memori saja, karena umumnya nama orang biasanya tidak lebih dari 100 karakter, sehingga menggunakan varchar (100) sepertinya lebih dari cukup. Demikian juga untuk field harga (dalam rupiah), sepertinya lebih bijaksana jika menggunakan Integer, mengingat dalam satuan rupiah kita tidak pernah menemui harga dalam bentuk decimal.
6. Gunakan Enum untuk field berisi pilihan. Field pilihan seperti jenis_kelamin, agama, kewarganegaraan lebih baik menggunakan tipe data Enum. Enum disimpan oleh database dalam bentuk binary, dan di-display dalam bentuk teks. Dengan menggunakan enum kita dapat menghemat space dan sekaligus mempermudah retrieve.

Optimasi query:

1. Hindari SELECT *. Ini adalah basic dari optimasi query namun paling sering dilupakan pemula. Dengan menggunakan karakter wildcard (*) kita akan membaca semua field yang termasuk field-field yang seharusnya tidak kita perlukan. Ini tentu saja akan membebani memori. Ini juga berlaku untuk SELECT COUNT(*), sebaiknya query tersebut dihindari.
2. Biasakan menggunakan WHERE. Bahkan untuk paling sederhana lebih baik kita tetap menggunakan WHERE 1 yang pasti akan mengembalikan hasil true. Contoh: SELECT nama FROM student WHERE 1.
3. Gunakan LIMIT untuk membatasi hasil. Kalau anda yakin query anda hanya menghasilkan satu record atau hanya akan berefek pada satu record gunakan LIMIT 1, ini akan mencegah DBMS untuk mencari kemungkinan lain di sepanjang tabel. Begitu juga kalau kita hanya memerlukan 100 record, tidak perlu meng-query ribuan data yang ada pada tabel.
4. ORDER BY dapat menurunkan performa namun meningkatkan keterbacaan. Saya ingat dulu pernah dimarahin karena data yang saya tampilkan tidak sortingnya, jadi susah untuk dibaca. Tapi seumpama ada alternatif lain yang lebih baik, misalkan sorting menggunakan javascript, sepertinya ide bagus untuk menghindari ORDER BY.
5. Tabel yang lebih sedikit disebutkan lebih dulu dalam JOIN. Misalkan kita mempunyai tabel category dengan 30 record dan tabel article dengan 150 record, maka contoh joinnya adalah SELECT * FROM category JOIN article ON (category.id = article.category_id). Category disebutkan lebih dulu daripada article.
6. Manfaatkan View dan Stored Procedure. Sebenarnya untuk view masih ada perdebatan antara mana yang lebih cepat menggunakan View atau plain query dengan JOIN, tapi beberapa kali percobaan menggunakan MySQL saya melihat hasilnya lebih cepat menggunakan View. Sedangkan stored procedure sangat bermanfaat untuk mengeksekusi beberapa query sekaligus. Misalkan untuk menambahkan data konsumen kita perlu melakukan insert pada tabel consumer dan juga consumer_group, dari pada mengeksekusi 2 query pada aplikasi lebih baik menggunakan stored procedure, lebih menghemat bandwith pada aplikasi dengan jaringan yang lambat.
7. Hindari menggunakan Trigger. Dulu saya kira alasannya adalah karena MySQL adalah pemain baru dalam dunia trigger, sehingga algorithma yang digunakan mungkin kurang bagus. Tapi ternyata di DBMS yang lain pun kondisinya sama, Trigger dieksekusi lebih lambat dari pada Stored Procedure. Oleh sebab itu usahakan untuk mengganti peran Trigger (memanipulasi logika Trigger) menggunakan Stored Procedure.

Sepertinya sudah cukup banyak. Saya yakin masih banyak lagi teknik optimasi database selain yang sudah saya sebutkan di atas. Saya menunggu komentar dari anda semua untuk menambahkan koleksi teknik optimasi database.

Oh ya, salah satu alasan kenapa dulu saya malas untuk mempraktekkan teknik optimasi adalah karena saya nggak mempunyai data yang cukup banyak untuk diuji coba. Pada data yang masih sedikit, teknik optimasi di atas sepertinya tidak akan berefek banyak, bahkan hasilnya tidak dapat diobservasi dalam hitungan millisecond. Untungnya kemarin saya baru saja menemukan tool yang cukup bagus untuk meng-generate data uji coba (you can call it dummy data). Situs generatedata.com menyediakan tool untuk meng-generate data sesuai dengan pattern dan tipe data yang kita inginkan. Enaknya lagi tool tersebut dapat diuji coba secara online, dan juga aplikasinya (dalam bentuk PHP & MySQL) dapat di-download dan di-install di lokal.

by fahri-blog