Cara menggunakan optimize query mysql

Ketika mengelola website yang menggunakan MySQL sebagai penyimpanan database, mungkin kamu sering menemukan masalah mengenai kurang optimalnya proses query database. Namun, kamu dapat mempelajari cara optimasi database MySQL menggunakan MySQLTuner agar proses query-nya dapat berjalan lancar. Mari simak!

Mengoptimalkan MySQL dapat membantu server agar load time-nya tidak terlalu tinggi. Optimasi MySQL Database ini sama saja seperti tuning Database Tuning, cara ini berlaku juga untuk MariaDB.

Database

Database adalah tempat penyimpanan datamu secara online dalam suatu sistem. Jika menggunakan CentOS, maka PHPMyAdmin adalah software atau perangkat lunak yang menangani operasi MySQL ataupun MariaDB.

MySQLTuner

MySQLTuner adalah script yang ditulis dalam bahasa pemrograman Perl untuk memberikan rekomendasi pengaturan dan konfigurasi MySQL untuk meningkatkan performa database. Tools ini sangat membantu untuk kamu yang tidak paham dengan parameter apa saja yang perlu diubah atau ditambahkan untuk meningkatkan performa database.

Optimasi Database MySQL Menggunakan MySQLTuner

Step 1 – Download MySQLTuner

$ wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl

Unduh script MySQLTuner terlebih dulu.

Step 2 – Ubah Permission

$ chmod +x mysqltuner.pl

Ubah permission tools tersebut agar dapat dieksekusi atau dijalankan.

Step 3 – Jalankan MySQLTuner

$ ./mysqltuner.pl

Output-nya akan seperti di bawah ini.

Ada banyak rekomendasi seperti dari segi Performance Metrics, Log File, Storage Engine, Security, Analysis Performance, dan CVE Security. Rekomendasi pengaturan optimasi database-nya dapat kamu lihat di paling bawah.

Salin pada bagian query_cache_size (=0) sampai innodb_buffer_pool_instance (-1) lalu tempel (paste) ke Notepad atau Text Editor.

Step 4 – Backup my.cnf

$ cp /etc/my.cnf ~/my.cnf-backup

Disarankan untuk melakukan backup sebelum melakukan perubahan pada file my.cnf.

Step 5 – Ubah my.cnf

$ nano /etc/my.cnf

Jika sudah melakukan backup my.cnf, pastekan script yang tadi di copy ke notepad atau text editor ke dalam my.cnf.

Step 6 – Reboot MySQL

$ service mysql restart

Restart service MySQL untuk menerapkan perubahan yang dilakukan pada file my.cnf.

Kemudian ulangi kembali dengan menjalankan script MySQLTuner untuk memastikan apakah masih ada rekomendasi yang perlu disesuaikan atau tidak.

Simpulan

MySQLTuner bukan tools yang melakukan optimasi tweak secara langsung, namun dengan memberikan rekomendasi atau saran saja. Rekomendasi MySQLTuner akan berbeda di setiap server, karena server akan membaca resource VPS seperti RAM dan CPU usage terlebih dulu.

Cara optimasi database MySQL menggunakan MySQLTuner ini bisa berguna bagi para pengguna VPS atau Server yang baru saja belajar. Demikian artikel ini, jangan sungkan untuk meninggalkan ide-ide topik yang ingin kamu baca di blog Dewaweb. Semoga artikel ini membantu!

Pada post sebelumnya, i3 telah membahas tentang bagaimana mengimplementasikan index untuk mempercepat eksekusi Query dan bagaimana mengimplementasikan Partisi pada tabel dalam database. Kali ini i3 akan memberikan tips-tips optimasi query lebih lanjut.

Performansi adalah parameter yang penting bagi aplikasi apapun, karena tidak ada pengguna yang bersedia kompromi terhadap kinerja sistemnya. Seperti semua sistem terdistribusi menggunakan database untuk mengimplementasikan logika bisnis, performa berbanding lurus dengan jumlah query yang di-tuning dan pengoptimalan PL/SQL di database.

Beberapa teknik optimasi query database yang dapat diterapkan untuk mengembangkan sistem yang lebih baik adalah sebagai berikut:

1. Hindari mismatch tipe data untuk pengindeksan kolom

Kebanyakan orang menggunakan tanda kutip tunggal (dalam kondisi filter) terlepas dari tipe data yang mereka query. Hal Ini membuat oracle melakukan internal typecast ke tipe data yang dibutuhkan.

Sebelum OptimasiSetelah Optimasiselect name,age,city,state
from employee
where employee_id=’1000′;select name,age,city,state
from employee
where employee_id=1000;Waktu yang dibutuhkan : 2.3 secWaktu yang dibutuhkan : 0.3 sec

2. Hindari fungsi pada kolom yang diindeks

Biasanya, kita melakukan identifikasi kolom yang paling sering di query kemudian dibuat index pada kolom tersebut. Tapi query kita menggunakan fungsi pada kolom yang terindeks. Hal ini akhirnya akan membatalkan tujuan menciptakan indeks pada kolom tersebut.

Sebelum OptimasiSetelah Optimasiselect name,age,city
from employee
where substr(employee_name,1,3)=’kar’;select name,age,city
from employee
where employee_name like ‘kar%’;Waktu yang dibutuhkan : 2.8 secWaktu yang dibutuhkan : 0.3 sec

Jika kita terpaksa harus mengunakan fungsi pada query tersebut maka kita bisa membuat function based index pada kolom tersebut.

3. Menentukan kondisi pada WHERE bukan pada HAVING

Sebelum OptimasiSetelah Optimasiselect name,
count(1)
from employee
group by name
having name=’karthi’;select name,
count(1)
from employee
where name=’karthi’
group by name;Waktu yang dibutuhkan = 2.2 secWaktu yang dibutuhkan = 0.3 sec

Ini bukanlah sebuah error. Jika filter dilakukan sebelum pengelompokan, maka semua data yang tidak perlu akan dikelompokan dan akhirnya data yang dibutuhkan akan difilter. Menerapkan filter sebelum pengelompokan akan menghindari sortasi dan pengelompokkan yang tidak perlu.

Baca Juga :
4 Tips SQL*Plus yang Bermanfaat bagi Oracle Developer Pemula

4. Penggunaan join untuk mengganti inner query

Sebelum OptimasiSetelah Optimasiselect employee_name
from employee where employee_id in ( select employee_id from defaulters)select employee_name
from employee e,
defaulters d
where e.employee_id=d.employee_idWaktu yang dibutuhkan : 14.1 secWaktu yang dibutuhkan : 5.5 sec

Hal ini sebenarnya dianggap sebagai praktek yang buruk pada penulisan SQL, menulis hasil inner query pada tiap-tiap baris hasil query tabel utama.

Sebelum OptimasiSetelah Optimasiselect so.documnet_number
count(1)
from activation a,
serv_ord so,
task t
where
t.documnet_number=
so.document_number
and so.serv_item_id=a.serv_item_idgroup by so.document_numberselect so.documnet_number
count(1)
from task t,
serv_ord so,
activation a,
where
t.documnet_number=
so.document_number
and so.serv_item_id=a.serv_item_idgroup by so.document_numberWaktu yang dibutuhkan : 10 SecWaktu yang dibutuhkan : 2.1 Sec

5. Menentukan tabel dengan ukuran paling kecil, pada urutan terakhir pada query join.

Seperti yang kita lihat, menggunakan join menghasilkan hasil yang lebih baik daripada inner query. Kita harus mengurutkan tabel sedemikian rupa sehingga tabel terkecil akan ditentukan pada akhir di SQL, sehingga waktu oracle untuk membandingkan baris akan berkurang.

6. Mengganti NOT IN dengan NOT EXISTS

Hal ini sama halnya dengan menghindari subquery

Sebelum OptimasiSetelah OptimasiSelect count(1)
from task t
where t.document_number not in (
select tt.document_number from task_bkp)select count(1)
from task t
where not exists
(select tt.document_number from task_bkp)Waktu yang dibutuhkan : 500 SecWaktu yang dibutuhkan : 6 Sec

7. Menggunkan FORALL sebagai pengganti FOR

Ini adalah salah satu fitur yang berguna, yang tersedia di oracle untuk memasukan bulk record.

Sebelum OptimasiSetelah OptimasiDECLARE
TYPE NumTab IS TABLE OF NUMBER(5) INDEX BY BINARY_INTEGER;
TYPE NameTab IS TABLE OF CHAR(15) INDEX BY BINARY_INTEGER;
pnums NumTab;
pnames NameTab;
BEGIN
FOR j IN 1..20000 LOOP — load index-by tables
pnums(j) := j;
pnames(j) := ‘Part No. ‘ || TO_CHAR(j);
END LOOP;
FOR i IN 1..20000 LOOP — use FOR loop
INSERT INTO parts VALUES (pnums(i), pnames(i));
END LOOP;
END;DECLARE
TYPE NumTab IS TABLE OF NUMBER(5) INDEX BY BINARY_INTEGER;
TYPE NameTab IS TABLE OF CHAR(15) INDEX BY BINARY_INTEGER;
pnums NumTab;
pnames NameTab;
BEGIN
FOR j IN 1..20000 LOOP — load index-by tables
pnums(j) := j;
pnames(j) := ‘Part No. ‘ || TO_CHAR(j);
END LOOP;
FORALL I in 1 .. 20000 — use FORALL
INSERT INTO parts VALUES (pnums(i), pnames(i));
END;Waktu yang dibutuhkan: 11.0 SecWaktu yang dibutuhkan: 0.5 sec

FORALL akan mengurangi waktu pengulangan pada PL/SQL dan SQL.

8. Penggunaan BULK COLLECT

BULK COLLECT adalah suatu fitur yang disediakan oleh Oracle untuk menghindari penggunaan loop dalam pengumpulan data dari table. Untuk aplikasi pengolahan data berat, BULK COLLECT akan sangat berguna. Sebagai contoh, kita perlu memilih 1000 baris dari tabel dan memproses baris dan masukkan ke tabel lain, maka kita dapat menggunakan BULK COLLECT.

Sebelum OptimasiSetelah OptimasiDeclare
Type bcode is table of products.barcode%TYPE;
i int;
barc bcode;
cursor cur_seq is
select barcode from products where rownum<100001;
begin
i:=0;
for cur_dta in cur_seq loop
i:=i+1;
barc:=cur_dta.barcode;
end loop;
end;Declare
Type bcode is table of products.barcode%TYPE;
i int;
barc bcode;
begin
select barcode BULK COLLECT into barc from products where rownum<100001;
end;Waktu yang dibutuhkan : 17secWaktu yang dibutuhkan : 1.41 sec

Ringkasan dari Tips Optimasi Query

Gunakan kode seragam di seluruh aplikasi standar
Hindari ketidakcocokan jenis data untuk indeks kolom
Hindari fungsi pada kolom indeks
Pindahkan kondisi dari klausa HAVING ke klausa WHERE
Gunakan joins bukan nested selects, jika memungkinkan
Mengganti Not IN dengan Not EXISTS atau OUTER JOIN
Gunakan bulk inserts pada insert banyak records
Gunakan klausa BULK COLLECT pada fetching records

Baca Juga :
Pernah Mengalami CHECKPOINT NOT COMPLETE Pada Database Oracle? Begini Cara Mengatasinya

Sekian beberapa tips untuk melakukan optimasi database. Di luar tips-tips di atas, tentunya masih banyak lagi hal yang dapat kita lakukan untuk optimasi query database. Pelajari lebih lanjut tentang optimasi query database bersama pelatihan yang diadakan oleh i3.

Untuk info lebih lengkap mengenai training yang tersedia di i3, Anda dapat menghubungi langsung tim sales kami melalui halaman Contact Us.

Tentang i3

PT. Inovasi Informatika Indonesia (i3) dikenal sebagai perusahaan penyedia solusi dan layanan TI yang berfokus pada Open Source, Security, Big Data dan Cloud bagi bisnis. i3 menyediakan layanan TI yang komprehensif, meliputi konsultasi, migrasi dan implementasi, pelatihan, troubleshooting, dan managed services. Untuk informasi lebih lanjut perihal layanan dan solusi yang ditawarkan, Anda dapat menghubungi kami melalui [email protected].

Bagaimana cara melakukan optimasi query pada MySQL?

Cara optimalisasi database mysql.

Menggunakan SELECT nama_kolom sebagai ganti SELECT * ... .

Buatlah kolom yang penting saja dan minimalisir membuat banyak kolom. ... .

Hindari penggunaan DISTINCT pada syntax SQL. ... .

Hindari mencari data menggunakan Cartesian atau CROSS JOIN. ... .

Gunakan wildcard atau % pada akhiran kata saja..

Langkah langkah optimasi query?

Ringkasan dari Tips Optimasi Query.

Gunakan kode seragam di seluruh aplikasi standar..

Hindari ketidakcocokan jenis data untuk indeks kolom..

Hindari fungsi pada kolom indeks..

Pindahkan kondisi dari klausa HAVING ke klausa WHERE..

Gunakan joins bukan nested selects, jika memungkinkan..

Apa yang dimaksud dengan optimasi query?

2.1 Optimasi Query Optimasi Query adalah suatu proses untuk menganalisa query untuk menentukan sumber- sumber apa saja yang digunakan oleh query tersebut dan apakah penggunaan dari sumber tersebut dapat dikurangi tanpa merubah output.

MySQL query untuk apa?

MySQL Query adalah perintah atau instruksi yang dapat digunakan untuk mengelola database atau tabel dalam database MySQL. Query lebih dikenal dengan sebutan SQL (Structured Query Language) yang artinya adalah sebuah bahasa yang digunakan untuk mengakses data dalam basis data relasional.