Tips Optimasi Query Database MySQL

Salah satu script saya sering error, tulisannya :

Database error: Invalid SQL: select * from leads quick where lnextduedate='0000-00-00′ and lnextdue=" and lstatus='A'MySQL Error: 2008 (MySQL client ran out of memory)Session halted.

Saya ganti hard disknya memakai SSD dan memory RAM ditambahpun tidak ada pengaruhnya. Wah gmn cara ngatasinnya ya???

Optimisasi bisa dilakukan dengan berbagai langkah, dengan mengerti tuning performance pada database dan best practice dari beragam sumber, Anda bisa mempunyai fundamental yang kuat dalam mengoptimalkan performa database.

Beberapa tehnik dan metode kemungkinan membutuhkan tindakan khusus yang lain, bergantung pada database yang Anda gunakan. Sebagai contoh, peningkatan performa dapat dilakukan dari segi administrasi database seperti konﬁgurasi ﬁle dan peng-updatean servis atau security pack, yang tentu saja masing-masing database memiliki keunikan dan tehnik tertentu.

Ada seperangkat metode dan tehnik yang biasa diterapkan saat Anda bekerja dengan RDBMS (Relational Database Manajemen System), kemungkinan tidak semua bisa Anda implementasikan karena benar-benar bergantung di lingkungan program masing-masing, tapi setidaknya Anda bisa meng-gunakannya sebagai tutorial dan rekomendasi untuk membuat sistem yang terbaik sesuai keadaan yang dihadapi.

Optimasi melalui perintah SQL memegang peranan yang juga sangat penting. Pokok dari SQL tersebut ialah perintah untuk melakukan ambil (retrieval), tambahan (insertion), modiﬁkasi (updating), dan penghilangan (deletion) data, disertai dengan beberapa fungsi pendukung administrasi dan managemen database.

SQL sendiri sebuah bahasa atau pemrograman standard untuk RDBMS. Meskipun disebutkan bahasa, kemungkinan sedikit janggal saat kita menyebut bahasa pemrograman SQL, lebih familiar bila yang didengar ialah pemrograman C, Visual Basic, Java, Delphi, dan sebagainya.

Bahasa-bahasa yang disebutkan terakhir terhitung dalam pemrograman imperative, gampangnya ialah bahasa yang berbentuk instruksi-instruksi inti. Dan, SQL termasuk dalam pemrograman declarative, yang lebih berwujud kalimat atau pengakuan.

Dalam pengembangannya, SQL terbagi-bagi kembali pada beragam extension hingga melahirkan beragam sebutan seperti SQL/PSM (Persistent Stored Modules) yang disebut standard ANSI/ISO, T-SQL (Transact-SQL) dari Microsoft dan SyBase, PL/SQL (PL sebagai ringkasan dari Procedural Language) yang digunakan oleh Oracle, yang selanjutnya diperkembangkan kembali jadi PL/pgSQL yang digunakan PostgreSQL.

Cukup membingungkan, bukan ? Untungnya ide dan komponen-komponen dasar dalam SQL seperti pernyataan, query, expression, atau clause masih tetap berlaku umum pada tiap SQL extension.

Kita cukupkan pembahasan teori sampai di sini, berikut beberapa optimisasi simpel yang bisa Anda kerjakan, untuk minimal memperbaiki atau mencegah persoalan, dan tingkatkan performa RDBMS Anda.

Berikut cara-cara optimasi query SQL :

1. Indeks

Mengindeks kolom dalam tabel ialah langkah yang umum dilakukan untuk memaksimalkan hasil penelusuran query SQL. Membuat index dalam sql tidak ubahnya seperti membuat daftar index pada buku. Bayangkan saat anda ingin mencari sebuah section pada buku berkenaan satu hal dan harus cari tiap halaman dari sejak awalnya sampai akhir, dengan index anda cukup cari posisi index kata itu dan segera membuka halaman referensi yang ada di index. Penggunaan index pada eksekusi query SQL akan betul-betul berasa saat tabel dan basis data yang kita punya sudah cukup besar.

Apa indeks scan selalu bisa lebih cepat dibanding dengan table scan? Ternyata tidak ! Table scan bisa saja bekerja bisa lebih cepat saat mengakses record dengan jumlah relatif kecil, atau di saat program memang membutuhkan pembacaan table keseluruhannya. Sebaliknya dalam mengakses record yang besar pada ﬁeld tertentu, indeks scan bisa mengurangi operasi pembacaan I/O hingga sering hasilkan kinerja yang bisa lebih cepat.

Sebagai dasar, Anda bisa tentukan indeks pada ﬁeld yang sering digunakan, misalkan ﬁeld yang kerap diakses oleh klausa WHERE, JOIN, ORDER BY, GROUP BY.

2. Symbol Operator

Penggunaan operator simbol seperti <, >, =, !=, ><, dan lain-lain benar-benar membantu dalam hasilkan kecepatan penelusuran dari query. Langkah kerja database manajemen sistem (DBMS) dalam mengaplikasikan query dengan keadaan ">" dibandingkan dengan "=>" sedikit akan berbeda, jika kita menggunakan "=>" karena itu DBMS akan mengaplikasikan dua kriteria pada tiap hasil, tidak langsung cari pada keadaan langsung seperti jika kita menggunakan ">".

3. Wildcard

Wildcard yang digambarkan dengan simbol "%" yang kita tulis pada keadaan/persyaratan query akan punya pengaruh pada lamanya proses penelusuran. Wildcard sendiri bisa kita bagi jadi tiga sisi penggunaan, yakni wildcard penuh (contoh ‘%kata%'), postfix wildcard (contoh ‘kata%'), dan prefix wildcard (contoh ‘%kata'). Sebisa mungkin jauhi pemakaian wildcard penuh karena untuk ukuran tabel dengan baris yang banyak, dan penelusuran kolom dengan panjang kolom yang besar akan betul-betul menyiksa basis data saat lakukan penelusuran.

4. Operator Negatif

Penggunaan operator negatif (seperti NOT, NOT LIKE, NOT IN, NOT EXIST, != dan lain-lain) semakin lebih memakan waktu lama dibanding operator positif. Operator = sebagai operator paling mempermudah database dalam melakukan penelusuran, khususnya saat database telah terindeks karena itu penelusuran exact match ini akan dibuat secara cepat.

5. COUNT vs EXIST

Sering kali dalam pembuatan sebuah loop dan logika dalam program- kita ingin membuat penguji simpel dengan pembuatan perintah yang dilakukan cuma bila sebuah nilai ada pada tabel (pada kolom tertentu dalam tabel) - kita memakai COUNT sebagai persyaratan. Dalam pengertian saat COUNT menghasilkan angka > 0 karena itu loop atau logika akan dijalankan.

SELECT KOLOM FROM TABEL WHERE COUNT > 0

Penggunaan COUNT akan lakukan penelusuran dari sejak awalnya sampai akhir baris dari tabel. Sebagai alternative yang lebih bagus anda bisa memakai EXIST, persyaratan ini akan hentikan penelusuran saat persyaratan/keadaan telah ditemukan.

6. Wildcard vs Substr

Penggunaan substr() dalam query akan membuat DBMS usaha memotong semua kolom dalam sebuah tabel sama sesuai keadaan substr(). Sebagai alternative yang lebih bagus bisa dipakai postfix wildcard untuk membuat penelusuran jadi lebih cepat.

7. Indeks Unique Column

Hal ini masih terkait dengan indexing pada ulasan pertama. Indeks bertipe unik (unique) dalam beberapa DBMS akan memberi kecepatan penelusuran tertentu, khususnya indeks yang paling sering dilakukan penelusuran.

8. Operator Max dan Min

Max dan Min ialah operator yang sedapat mungkin tidak sering dipakai karena penelusuran yang dipakai dilakukan secara detail dari sejak awalnya sampai akhir tabel. Penggunaan indeks akan membantu bila nilai maksimal/minimal yang dijadikan sebagai kriteria ialah index. Tetapi secara umum type kolom yang jadi kriteria ialah int, dec, dan date/time.

9. Tipe Data

Pakai tipe data yang paling kecil yang bisa digunakan dengan ikut memerhatikan kebutuhan di masa mendatang. Penggunaan tinyint dan int akan memberikan hasil yang lain, begitu halnya hasil penelusuran.

10. Primary Indeks

Primary Column yang dipakai untuk index harus dibuat sependek mungkin, dengan demikian analisis dari baris-baris yang ada semakin lebih efektif untuk DBMS.

11. String Indexing

Pengindeks-an semua string sebetulnya tak perlu dilakukan karena akan lebih bagus bila kita memakai prefix atau postfix dari string akan menghasilkan daftar isi yang lebih singkat dan efektif. Ingat, makin pendek index karena itu penelusuran akan jadi lebih cepat serta lebih hemat sumber daya.

12. Batasi Hasil Query

Hasil query yang tampilkan semua tabel memang tidak begitu permasalahan bila memang kita memerlukan hasil penuh. Tetapi saat bicara program yang akan dilihat oleh pengguna (bukan pengolahan data selanjutnya) karena itu membatasi hasil query akan lebih bagus. Pagination dengan memakai LIMIT sebesar 10, 30, atau 50 dirasakan cukup untuk menampilkan program yang akan dibaca oleh pengguna.

13. Gunakan Nilai Automatis (Default Value)

Penggunaan nilai automatis pada beberapa DBMS yang menyediakan feature ini benar-benar bermanfaat terutama dalam soal lakukan insertpada basis data. Nilai automatis akan mempercepat kerja DBMS dalam Insert data saat tidak ada nilai yang dikirim dari query yang dieksekusi.

14. IN SUBQUERY

Penggunaan IN umum digunakan saat kriteria yang kita pakai berbentuk array. Penggunaan subquery kerap digunakan dalam membuat persyaratan aktif yang akan digunakan dalam persyaratan IN. Tetapi ini semakin lebih berat karena DBMS akan menggunakan kriteria induk lebih dulu, baru selanjutnya memakai kriteria subquery. Di dalam perihal ini DBMS bekerja 3 tahapan, di mana dengan kriteria IN yang stabil hanya akan menjalankan 1 tahapan penelusuran saja.

Sebagai alternative anda bisa membuat array dengan mendeclare lebih dulu subquery ke sebuah variabel, dengan begitu variabel ini bisa dipakai berulang-kali dalam query lainnya. Akan lebih bagus bila kita melibatkan sumber daya yang dimiliki pengguna dengan membuat sesi.

15. Menggunakan UNION dibanding OR

Penggunaan OR sebagai persyaratan ganda ialah jamak dilakukan. Tetapi ada alternative yang lain bisa dibuat yakni dengan memakai UNION jika kolom yang ingin kita mencari ialah sama. Tetapi tentu saja penggunaan UNION ini memiliki keterbatasan. Perbedaan kecepatan untuk kriteria OR, UNION, dan IN sebetulnya bisa diperdebatkan mengingat ketiga keadaan kriteria di atas membutuhkan trick algoritma koding yang lain dari setiap programmer.

16. Hindari SELECT

Select mungkin sebagai kata kunci yang kerapkali digunakan, sebab itu optimasi pada perintah SELECT memungkinkan bisa memperbaiki kemampuan aplikasi secara keseluruhan.SELECT * dipakai untuk melakukan query semua ﬁeld yang ada pada suatu table, tapi bila Anda cuma ingin mengolah ﬁeld tertentu, karena itu seharusnya Anda tuliskan ﬁeld yang ingin diakses saja, hingga query Anda jadi SELECT ﬁeld1, ﬁeld2, ﬁeld3 dan sebagainya (jangan pedulikan code program yang menjadi lebih panjang!). Ini akan mengurangi beban lalu lintas jaringan dan lock pada table, terutama jika table itu banyak memiliki ﬁeld dan mempunyai ukuran besar.

17. Batasi ORDER BY

Penggunaan ORDER BY yang berfungsi untuk mengurutkan data, rupanya mempunyai resiko menambah beban query, karena akan menambah satu proses lagi, yakni proses sort.Karenanya gunakan ORDER BY cuma bila betul-betul diperlukan oleh program Anda.Atau bila bisa saja, Anda bisa lakukan pengurutan pada segi klien dan tidak pada sisi server. Misalnya dengan menampung data lebih dulu pada komponen grid dan melakukan sortir pada grid itu sama sesuai kebutuhan pengguna.

18. Subquery Atau JOIN

Terkadang sebuah perintah bisa dituliskan berbentuk subquery atau perintah JOIN, disarankan Anda memprioritaskan penggunaan JOIN karena dalam kasus yang umum akan hasilkan performa yang bisa lebih cepat.Walau begitu, memproses query sebagai satu seni, ada selalu peluang rupanya subquery bekerja bisa lebih cepat dibanding JOIN, misalkan pada keadaan penggunaanJOIN yang kebanyakan, atau logika query yang belum maksimal.

19. Gunakan WHERE dalam SELECT

"Di mana terdapat gula di situ ada semut". Untuk programer database, pepatah itu perlu dimodiﬁ kasi jadi "di mana terdapat SELECT di situ ada WHERE", untuk mengingatkan pentingnya klausa WHERE sebagai keadaan untuk memfilter record hingga meminimalisir beban jaringan.Saat sebuah table dalam jumlah data yang besar sekali diproses, terjadi proses lock pada table itu hingga merepotkan pengaksesan table yang berkaitan oleh pengguna lainnya.Bahkan juga bila Anda bermaksud memanggil semua record, masih tetap memakai WHERE sebagai kebiasaan yang bagus.Bila Anda sudah memakai WHERE di awal query, karena itu kapan saja Anda ingin menambah keadaan tertentu, Anda tinggal menyambung query itu dengan klausa AND diikuti kondisi yang diharapkan.

Tetapi bagaimana menggunakan WHERE bila benar-benar tidak ada kondisi apapun? Anda bisa tuliskan satu keadaan yang jelas bernilai true, misalkan SELECT …. WHERE 1=1. Bahkan juga tools open source phpMyAdmin yang berfungsi untuk mena ngani database MySQL selalu menyertakan default klausa WHERE 1 pada perintah SELECT, di mana angka 1 pada MySQL memiliki arti nilai true.

20. Kecepatan Akses Operator

WHERE 1=1 dan WHERE 0 <> 1 sama sebagai keadaan yang hasilkan nilai true. Tapi, dalam masalah ini lebih bagus Anda menggunakan WHERE 1=1 dibanding WHERE 0 <> 1. Ini karena operator = diolah bisa lebih cepat dibanding dengan operator <>.Dari segi performa, posisi operator yang diolah tercepat ialah:1. =2. >, >=, <. <=3. LIKE4. <>Tidak dalam tiap keadaan operator bisa disubtitusikan mirip contoh simpel di atas, tapi prioritaskanlah penggunaan operator yang paling cepat.

21. Membatasi Jumlah Record

Bayangkan Anda menampilkan isi sebuah table dengan menggunakan SELECT, dan rupanya table itu mempunyai juta-an record yang paling tidak diharap untuk tampil semuanya.Scenario yang lebih buruk masih bisa terjadi, yakni query itu diakses oleh beberapa ratus pengguna lain dalam kurun waktu bersamaan!Karena itu, Anda perlu membatasi jumlah record yang mempunyai potensi mengembalikan record dengan jumlah besar (terkecuali memang benar-benar diperlukan), pada SQL Server, Anda bisa memakai operator TOP dalam perintah SELECT.Misalnya SELECT TOP 100 nama… akan menampilkan 100 record paling atas ﬁeld nama.Bila memakai MySQL, Anda bisa memakai LIMIT untuk kepentingan yang serupa.

22. Batasi Penggunaan Function

Gunakan fungsi-fungsi yang disediakan SQL seperlunya saja.Sebagai contoh, bila Anda menemukan query seperti berikut: SELECT nama FROM tbl_teman WHERE ucase(nama) = ‘ABC', terlihat query itu ingin cari record yang mempunyai data berisi "abc", fungsi ucase dipakai untuk mengganti isi ﬁeld nama jadi huruf besar dan dibanding dengan konstanta "ABC" untuk memberikan keyakinan jika semua data "abc" bakal tampil, meskipun ditulis dengan huruf kecil, besar, atau kombinasinya.Tapi, coba mengganti query itu jadi SELECT nama FROM tbl_teman WHERE nama = ‘ABC', lihat query ini tidak memakai function ucase. Apa hasilkan result yang serupa dengan query pertama? Bila pengaturan database Anda tidak case-sensitive (dan biasanya secara standar memanglah tidak case-sensitive), karena itu hasil kedua query itu ialah sama. Artinya, dalam kasus ini Anda sebenarnya tak perlu memakai function ucase!

23. Baca dari Kiri ke Kanan

Query yang Anda tulis akan diolah dari kiri ke kanan, misalnya ada query WHERE kondisi1 AND kondisi2 AND kondisi3, karena itu kondisi1 akan lebih dulu dipelajari, selanjutnya kondisi2, kondisi3, dan sebagainya. Tentu saja dengan anggapan tidak ada kondisi yang diprioritaskan/dikelompokkan dengan memakai pertanda kurung. Logika operator AND langsung akan hasilkan nilai false saat diketemukan salah satunya keadaan false, karena itu tempatkan kondisi yang paling kemungkinan mempunyai nilai false pada status paling kiri. Ini ditujukan supaya SQL tidak perlu menilai kondisi selanjutnya saat menemukan salah satu kondisi sudah berharga false.

Jika Anda bingung pilih kondisi yang mana pantas tempati status terkiri karena kemungkinan falsenya perbedaan dapat diprediksikan, tentukan kondisi yang lebih sederhana untuk diproses.