Cara menggunakan mysql scalability

Database Tuning | Database Tuning adalah sejumlah aktifitas yang dilakukan untuk memperbaiki atau meningkatkan kinerja atau performance sebuah database. Aktifitas tuning ini meliputi banyak aspek dari software hingga hardware, antara lain I/O Tuning, DBMS Tuning, Query Tuning, dan Database Maintenance. Masing-masing memiliki tekniknya sendiri-sendiri, dan membutuhkan skill yang mumpuni. Namun kita tetap bisa mempelajari teknik-teknik dasarnya. Dalam artikel ini, kita akan mencoba melakukan Query Tuning dengan bantuan Database Index.

Umumnya programmer, pada saat mengembangkan sebuah aplikasi database, pasti akan membuat sebuah database dan memasukkan beberapa data contoh ke dalamnya. Pada saat pengembangan, tidak ada masalah dan kelihatannya semua baik-baik saja, namun seiring berjalannya waktu, aplikasi mulai terkesan lambat dan bisa jadi sampai hang. Hal ini terjadi karena biasanya programmer tidak pernah melakukan load testing menggunakan data besar, dalam artian, jumlah row yang buanyak.

Nah... untuk contoh kasus, kita akan menggunakan database MySQL dan membuat sebuah table sederhana saja berisikan 9 (sembilan) juta rows, caranya seperti terlihat di bawah ini... perlu waktu sekitar 2 menit untuk men-generate table contoh tersebut. Perlu diperhatikan, setelah perintah di bawah ini selesai, maka baru table saja yang dibuat, belum ada index.

mysql> -- --------------------------------------------------
mysql> -- coba bikin sebuah table raksasa dengan 9 juta rows
mysql> -- setelah selesai dibuat, table ini belum memiliki index
mysql> -- --------------------------------------------------
mysql> create table big_table as
    -> select @baris := @baris+1 as baris
    -> from ( select @baris := 0 ) x
    -> join ( select 1 kolom from information_schema.tables ) a
    -> join ( select 1 kolom from information_schema.tables ) b
    -> join ( select 1 kolom from information_schema.tables ) c ;
Query OK, 9938375 rows affected (2 min 25.70 sec)
Records: 9938375  Duplicates: 0  Warnings: 0

Salah satu teknik yang banyak digunakan dalam aplikasi database adalah pagination, dimana teknik ini mengharuskan kita mengetahui total rows yang ada dalam sebuah table untuk menentukan jumlah page yang ada. Coba kita lakukan query count(*) untuk mengetahui total rows dalam table tersebut, dan lihat waktu yang diperlukan untuk mendapatkan hasilnya :)

mysql> -- --------------------------------------------------
mysql> -- coba hitung jumlah row yang ada di dalam big_table
mysql> -- terlihat mysql memerlukan waktu 19 detik 
mysql> -- untuk mendapatkan hasil yang kita inginkan
mysql> -- --------------------------------------------------
mysql> select count(*) from big_table ;
+----------+
| count(*) |
+----------+
|  9938375 |
+----------+
1 row in set (18.82 sec)

Rupanya perlu waktu 18 detik untuk mendapatkan hasilnya.... untuk user yang penyabar, mungkin hal ini tidak menjadi masalah besar. Sekarang kita coba lihat execution plan yang dipilih oleh mysql saat mengeksekusi perintah count(*) yang kita berikan, maka terlihat seperti di bawah ini, bahwa mysql melakukan full table scan, ditandai dengan type=ALL pada execution plan yang muncul. Full table scan artinya, mysql meload seluruh table ke memory dan menghitung row yang ada di dalamnya satu persatu.... wow....

mysql> -- --------------------------------------------------
mysql> -- jika kita lihat execution plan query tersebut
mysql> -- terlihat bahwa mysql melakukan full table scan 
mysql> -- ditandai dengan type=ALL ke seluruh 9 juta rows
mysql> -- --------------------------------------------------
mysql> explain
    -> select count(*) from big_table ;
+----+-------------+-----------+------+---------------+------+---------+------+---------+-------+
| id | select_type | table     | type | possible_keys | key  | key_len | ref  | rows    | Extra |
+----+-------------+-----------+------+---------------+------+---------+------+---------+-------+
|  1 | SIMPLE      | big_table | ALL  | NULL          | NULL | NULL    | NULL | 9938689 |       |
+----+-------------+-----------+------+---------------+------+---------+------+---------+-------+
1 row in set (0.00 sec)

Sekarang kita coba lagi dengan model query lain yang sangat umum, yaitu query searching dengan menggunakan WHERE clause. Kita coba mencari sebuah row tertentu yang kira-kira lokasinya ada di tengah-tengah table. Pada contoh di bawah ini bisa terlihat bahwa mysql tetap melakukan full table scan ke seluruh rows, padahal row yang kita inginkan sudah ditemukan di tengah-tengah table. Dan rupanya perlu waktu lebih lama, yaitu 19 detik, dibandingkan dengan proses count(*) yang hanya 18 detik.

mysql> -- --------------------------------------------------
mysql> -- coba kita test dengan query yang lain
mysql> -- yaitu mendapatkan sebuah row yang kira2 
mysql> -- berada di tengah2 table besar ini
mysql> -- lagi2 terlihat mysql memerlukan waktu 19 detik 
mysql> -- untuk mendapatkan hasil yang kita inginkan
mysql> -- --------------------------------------------------
mysql> select * from big_table
    -> where baris = 5346781 ;
+---------+
| baris   |
+---------+
| 5346781 |
+---------+
1 row in set (19.03 sec)

Execution plan mengkonfirmasikan bahwa mysql melakukan full table scan dari row pertama sampai row terakhir, padahal kita hanya menginginkan satu row saja... :)

mysql> -- --------------------------------------------------
mysql> -- jika kita lihat execution plan query tersebut
mysql> -- terlihat juga bahwa mysql melakukan full table scan 
mysql> -- ditandai dengan type=ALL ke seluruh 9 juta rows
mysql> -- *padahal* kita hanya perlu 1 row saja yang ada di tengah
mysql> -- --------------------------------------------------
mysql> explain
    -> select * from big_table
    -> where baris = 5346781 ;
+----+-------------+-----------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table     | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+----+-------------+-----------+------+---------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | big_table | ALL  | NULL          | NULL | NULL    | NULL | 9938977 | Using where |
+----+-------------+-----------+------+---------------+------+---------+------+---------+-------------+
1 row in set (0.00 sec)

Mari kita buat index..... sabar..... 2 menit.... :)

mysql> -- --------------------------------------------------
mysql> -- sekarang coba kita buat INDEX berdasarkan field baris
mysql> -- harap bersabar... karena cukup lama juga :)
mysql> -- --------------------------------------------------
mysql> create index big_table_pk on big_table(baris) ;
Query OK, 0 rows affected (2 min 4.21 sec)
Records: 0  Duplicates: 0  Warnings: 0

Index selesai dibuat, dan kita coba lakukan proses penghitungan jumlah rows menggunakan query yang sama seperti sebelumnya... dan hasilnya muncul dalam 10 detik, sebuah peningkatan yang luar biasa dari 18 detik menjadi 10 detik saja (y)

mysql> -- --------------------------------------------------
mysql> -- sekarang kita hitung  row yang ada di dalam big_table
mysql> -- rupanya terlihat ada peningkatan performance 
mysql> -- dari awalya perlu 19 detik, menjadi 10 detik saja
mysql> -- LUAR BIASA!
mysql> -- --------------------------------------------------
mysql> select count(*) from big_table ;
+----------+
| count(*) |
+----------+
|  9938375 |
+----------+
1 row in set (10.91 sec)

Jika kita lihat execution plan, maka mysql tidak lagi menggunakan full table scan, namun sekarang menggunakan index scan. walaupun masih melakukan scan ke seluruh rows sebanyak 9 juta, namun index scan jauh leih cepat dibandingkan full table scan.

mysql> -- --------------------------------------------------
mysql> -- jika kita lihat execution plan query tersebut
mysql> -- terlihat bahwa mysql tidak lagi melakukan full table scan 
mysql> -- namun melakukan index scan, jauh lebih cepat dari full table scan
mysql> -- --------------------------------------------------
mysql> explain
    -> select count(*) from big_table ;
+----+-------------+-----------+-------+---------------+--------------+---------+------+---------+-------------+
| id | select_type | table     | type  | possible_keys | key          | key_len | ref  | rows    | Extra       |
+----+-------------+-----------+-------+---------------+--------------+---------+------+---------+-------------+
|  1 | SIMPLE      | big_table | index | NULL          | big_table_pk | 9       | NULL | 9938977 | Using index |
+----+-------------+-----------+-------+---------------+--------------+---------+------+---------+-------------+
1 row in set (0.00 sec)

Sekarang kita coba query searching..... dan hasilnya muncul sangat cepat, hanya 0.05 detik .... LUAR BIASA!

mysql> -- --------------------------------------------------
mysql> -- sekarang kita test dengan query kedua
mysql> -- dan hasilnya sangat LUAR BIASA!
mysql> -- hanya perlu 0.05 detik dari sebelumnya 19 detik !!!
mysql> -- --------------------------------------------------
mysql> select * from big_table
    -> where baris = 5346781 ;
+---------+
| baris   |
+---------+
| 5346781 |
+---------+
1 row in set (0.05 sec)

Jika kita lihat execution plan, maka mysql tidak lagi melakukan scan ke seluruh rows, namun langsung menuju satu row yang kita inginkan. Peningkatan kinerja pencarian dari 19 detik menjadi 0.05 detik adalah peningkatan yang sangat LUAR BIASA!

mysql> -- --------------------------------------------------
mysql> -- jika kita lihat execution plan query tersebut
mysql> -- terlihat bahwa mysql melakukan index "big_table_pk" 
mysql> -- dan langsung menuju rows yang kita inginkan
mysql> -- --------------------------------------------------
mysql> explain
    -> select * from big_table
    -> where baris = 5346781 ;
+----+-------------+-----------+------+---------------+--------------+---------+-------+------+--------------------------+
| id | select_type | table     | type | possible_keys | key          | key_len | ref   | rows | Extra                    |
+----+-------------+-----------+------+---------------+--------------+---------+-------+------+--------------------------+
|  1 | SIMPLE      | big_table | ref  | big_table_pk  | big_table_pk | 9       | const |    1 | Using where; Using index |
+----+-------------+-----------+------+---------------+--------------+---------+-------+------+--------------------------+
1 row in set (0.00 sec)

Perlu diperhatikan bahwa INDEX yang kita buat berdasarkan FIELD yang terlibat dalam WHERE clause, sehingga Query Optimizer di MySQL otomatis akan mengenali index tersebut dan menggunakannya untuk proses pencarian. Jadi untuk teman-teman programmer yang belum membuat index di table yang mereka buat, mulailah buat dari sekarang. SEMOGA BERGUNA!

mysql> -- --------------------------------------------------
mysql> -- SEMOGA BERGUNA!
mysql> -- --------------------------------------------------
mysql>

(y)