Mengoptimalisasi Database

JIKA DIIBARATKAN manusia, database adalah sahabat yang
patuh dan mengerti pada setiap perintah yang diberikan, sayangnya
terkadang tidak berlaku sebaliknya, kita tidak patuh dan
tidak mengerti pada “perintah” yang diberikan database.
Database kadang dapat “mengomel” dengan berbagai cara,
bisa jadi dalam bentuk performance yang menurun, pesan
kesala han, atau bahkan hasil laporan yang tidak sesuai. Semuanya
dapat kita minimalisasi, bahkan sebelum hal itu terjadi.
SQL dan RDBMS
Optimasi dapat dilakukan dengan berbagai cara, dengan memahami
tuning performance pada database dan best practice
dari berbagai sumber, Anda dapat memiliki fundamental yang
kuat dalam mengoptimalkan kinerja database.
Beberapa teknik dan metoda mungkin memerlukan perlakuan
khusus yang berbeda, tergantung pada database yang Anda
gunakan.
Sebagai contoh, peningkatan kinerja bisa dilakukan dari sisi
administrasi database seperti konfi gurasi fi le dan peng-updatean
service atau security pack, yang tentunya masing-masing
database memiliki keunikan dan teknik tersendiri.
Lalu, dengan pertimbangan kompatibilitas, adakah optimasi
yang dapat dilakukan secara umum?
Terdapat seperangkat metode dan teknik yang umum diterapkan
saat Anda bekerja dengan RDBMS (Relational Database
Management System), mungkin tidak semuanya dapat Anda
implementasikan karena sangat tergantung pada lingkungan
aplikasi masing-masing, tetapi setidaknya Anda dapat menggunakannya
sebagai panduan dan referensi untuk membentuk
sistem yang terbaik sesuai dengan kondisi yang dihadapi.
Optimasi melalui perintah SQL juga memegang peranan
yang tidak kalah penting. Inti dari SQL itu sendiri adalah perintah
untuk melakukan pengambilan (retrieval), penambahan
(insertion), modifi kasi (updating), dan penghapusan (deletion)
CODING
Joko Nurjadi
data, disertai dengan fungsi-fungsi pendukung administrasi dan
managemen database.
SQL sendiri merupakan sebuah bahasa atau pemrograman
standar untuk RDBMS. Walaupun disebut bahasa, mungkin
sedikit janggal saat kita menyebut bahasa pemrograman SQL,
lebih familiar jika yang terdengar adalah pemrograman C, Visual
Basic, Java, Delphi, dan seterusnya.
Bahasa-bahasa yang disebut belakangan termasuk dalam
pemrograman imperative, mudahnya adalah bahasa yang
berbentuk instruksi-instruksi inti. Sedangkan, SQL termasuk
dalam pemrograman declarative, yang lebih berbentuk kalimat
atau pernyataan.
Dalam pengembangannya, SQL terbagi-bagi lagi dalam berbagai
extension sehingga melahirkan berbagai sebutan seperti
SQL/PSM (Persistent Stored Modules) yang merupakan standar
ANSI/ISO, T-SQL (Transact-SQL) dari Microsoft dan SyBase,
PL/SQL (PL merupakan singkatan dari Procedural Language)
yang digunakan oleh Oracle, yang kemudian dikembangkan
lagi menjadi PL/pgSQL yang digunakan PostgreSQL.
Cukup membingungkan, bukan? Untungnya konsep dan
elemen-elemen dasar dalam SQL seperti statement, query,
expression, ataupun clause tetap berlaku umum pada setiap
SQL extension.
Kita cukupkan pembahasan teori sampai di sini, berikut adalah
beberapa optimasi sederhana yang dapat Anda lakukan, untuk
setidaknya memperbaiki atau mencegah permasalahan, dan
meningkatkan performa RDBMS Anda.
Index
Optimasi pertama yang kita bahas adalah permasalahan index,
tentu Anda mengetahui bahwa index dapat meningkatkan kecepatan
pencarian pada record yang diinginkan. Tetapi, Anda
harus cukup selektif dalam memilih fi eld yang perlu di-index,
karena tidak semua fi eld memerlukannya.
OPTIMASI
SQL
Database administrator dan programer sering menggunakan SQL
(Structured Query Language) untuk memberikan instruksi kepada
database. Tetapi hati-hati, berikan instruksi yang tepat agar
database Anda tidak ngambek.
12/2008 KNOW-HOW 105
Ibaratnya membaca buku, proses pencarian atau scan akan
membaca dari awal hingga akhir halaman. Pada fi eld yang diindex,
pencarian dilakukan secara index scan, atau membaca
pada index, tidak langsung pada table yang bersangkutan.
Sementara pencarian yang dilakukan langsung dengan
membaca record demi record pada table disebut dengan
table scan.
Apakah index scan selalu lebih cepat dibandingkan dengan
table scan? Ternyata tidak juga, table scan bisa jadi bekerja
lebih cepat saat mengakses record dalam jumlah relatif kecil,
ataupun pada saat aplikasi memang memerlukan pembacaan
table secara keseluruhan.
Sebaliknya dalam mengakses record yang besar pada fi eld
tertentu, index scan dapat mengurangi operasi pembacaan I/O
sehingga tidak jarang menghasilkan kinerja yang lebih cepat.
Sebagai patokan, Anda dapat menentukan index pada fi eld
yang sering digunakan, misalnya fi eld yang sering diakses oleh
klausa WHERE, JOIN, ORDER BY, GROUP BY.
Menentukan Tipe Data
Tipe data merupakan permasalahan yang gampang-gampang
susah. Dari sisi daya tampung, tipe data yang terlalu kecil atau
sebaliknya terlalu besar bagi suatu fi eld, dapat menimbulkan
bom waktu yang menimbulkan masalah seiring dengan pertambahan
data yang pesat setiap harinya.
Menentukan tipe data yang tepat memerlukan ketelitian dan
analisa yang baik. Sebagai contoh, kita perlu mengetahui kapan
kita menggunakan tipe data char atau varchar.
Keduanya menampung karakter, bedanya char menyediakan
ukuran penyimpanan yang tetap (fi xed-length), sedangkan
varchar menyediakan ukuran penyimpanan sesuai dengan isi
data (variable-length).
Patokan umum adalah menggunakan tipe data char jika fi eld
tersebut diperuntukkan untuk data dengan panjang yang konsisten.
Misalnya kode pos, bulan yang terdiri dari dua digit (01
sampai 12), dan seterusnya. Varchar digunakan jika data yang
ingin disimpan memiliki panjang yang bervariasi, atau gunakan
varchar(max) jika ukurannya melebihi 8000 byte.
Jangan Izinkan Allow Null
Jika memungkinkan, kurangi penggunaan fi eld yang memperbolehkan
nilai null. Sebagai gantinya, Anda dapat memberikan
nilai default pada fi eld tersebut.
Nilai null kadang rancu dalam intepretasi programer dan
dapat mengakibatkan kesalahan logika pemrograman. Selain
itu, fi eld null mengonsumsi byte tambahan sehingga menambah
beban pada query yang mengaksesnya.
Query yang Mudah Terbaca
Karena SQL merupakan bahasa declarative, maka tidak mengherankan
jika Anda membuat query berbentuk kalimat nan
panjang walaupun mungkin hanya untuk keperluan menampilkan
satu fi eld!
Jangan biarkan query Anda susah dibaca dan dipahami,
kecuali Anda memang berniat membuat pusing siapapun
yang melihat query Anda. Query panjang yang ditulis dalam 1
CODING
baris jelas akan menyulitkan modifi kasi dan pemahaman, akan
jauh lebih baik jika Anda menuliskan query dalam format yang
mudah dicerna.
Pemilihan huruf besar dan kecil juga dapat mempermudah
pembacaan, misalnya dengan konsisten menuliskan keyword
SQL dalam huruf kapital, dan tambahkan komentar bilamana
diperlukan.
Hindari SELECT *
Select mungkin merupakan keyword yang paling sering
digunakan, karena itu optimasi pada perintah SELECT
sangat mungkin dapat memperbaiki kinerja aplikasi secara
keseluruhan.
SELECT * digunakan untuk melakukan query semua fi eld
yang terdapat pada sebuah table, tetapi jika Anda hanya ingin
memproses fi eld tertentu, maka sebaiknya Anda menuliskan
fi eld yang ingin diakses saja, sehingga query Anda menjadi
SELECT fi eld1, fi eld2, fi eld3 dan seterusnya (jangan
pedulikan kode program yang menjadi lebih panjang!). Hal
ini akan mengurangi beban lalu lintas jaringan dan lock pada
table, terutama jika table tersebut memiliki banyak fi eld dan
berukuran besar.
Batasi ORDER BY
Penggunaan ORDER BY yang berfungsi untuk mengurutkan
data, ternyata memiliki konsekuensi menambah beban query,
karena akan menambah satu proses lagi, yaitu proses sort.
Karena itu gunakan ORDER BY hanya jika benar-benar dibutuhkan
oleh aplikasi Anda.
Atau jika dimungkinkan, Anda dapat melakukan pengurutan
pada sisi client dan tidak pada sisi server. Misalnya dengan
menampung data terlebih dahulu pada komponen grid dan
melakukan sortir pada grid tersebut sesuai kebutuhan pengguna.
Subquery Atau JOIN
Adakalanya sebuah instruksi dapat dituliskan dalam bentuk
subquery atau perintah JOIN, disarankan Anda memprioritaskan
penggunaan JOIN karena dalam kasus yang umum akan
menghasilkan performa yang lebih cepat.
Walaupun demikian, mengolah query merupakan suatu
seni, selalu ada kemungkinan ternyata subquery bekerja lebih
cepat dibandingkan JOIN, misalnya dalam kondisi penggunaan
JOIN yang terlalu banyak, ataupun logika query yang belum
optimal.
Menulis query dengan rapi dalam program.
106 KNOW-HOW 12/2008
Gunakan WHERE dalam SELECT
“Di mana ada gula di sana ada semut”. Untuk programer database,
pepatah itu perlu dimodifi kasi menjadi “di mana ada
SELECT di sana ada WHERE”, untuk mengingatkan pentingnya
klausa WHERE sebagai kondisi untuk menyaring record sehingga
meminimalkan beban jaringan.
Saat sebuah table dengan jumlah data yang sangat besar
diproses, juga terjadi proses lock terhadap table tersebut
sehingga menyulitkan pengaksesan table yang bersangkutan
oleh pengguna yang lain.
Bahkan jika Anda bermaksud memanggil seluruh record,
tetap menggunakan WHERE merupakan kebiasaan yang baik.
Jika Anda telah menggunakan WHERE pada awal query, maka
kapanpun Anda ingin menambahkan kondisi tertentu, Anda
tinggal menyambung query tersebut dengan klausa AND diikuti
kondisi yang diinginkan.
Tapi bagaimana menggunakan WHERE jika benar-benar tidak
ada kondisi apapun? Anda dapat menuliskan suatu kondisi yang
pasti bernilai true, misalnya SELECT .... WHERE 1=1. Bahkan
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 berarti
nilai true.
Kecepatan Akses Operator
WHERE 1=1 dan WHERE 0 <> 1 sama-sama merupakan kondisi
yang menghasilkan nilai true. Tetapi, dalam hal ini lebih baik
Anda menggunakan WHERE 1=1 daripada WHERE 0 <> 1. Hal
ini dikarenakan operator = diproses lebih cepat dibandingkan
dengan operator <>.
Dari sisi kinerja, urutan operator yang diproses paling cepat
adalah:
1. =
2. >, >=, <. <=
3. LIKE
4. <>
Tidak dalam setiap kondisi operator dapat disubtitusikan
seperti contoh sederhana di atas, tetapi prioritaskanlah penggunaan
operator yang tercepat.
Membatasi Jumlah Record
Bayangkan Anda menampilkan isi sebuah table dengan menggunakan
SELECT, dan ternyata table tersebut memiliki jutaan
record yang sangat tidak diharapkan untuk tampil seluruhnya.
Skenario yang lebih buruk masih dapat terjadi, yaitu query
tersebut diakses oleh ratusan pengguna lain dalam waktu bersamaan!
Untuk itu, Anda perlu membatasi jumlah record yang berpotensi
mengembalikan record dalam jumlah besar (kecuali
memang benar-benar dibutuhkan), pada SQL Server, Anda
dapat menggunakan operator TOP di dalam perintah SELECT.
Contohnya SELECT TOP 100 nama... akan menampilkan
100 record teratas fi eld nama.
Jika menggunakan MySQL, Anda dapat menggunakan LIMIT
untuk keperluan yang sama.
Batasi Penggunaan Function
Gunakan fungsi-fungsi yang disediakan SQL seperlunya saja.
Sebagai contoh, jika Anda menemukan query sebagai berikut:
SELECT nama FROM tbl_teman WHERE ucase(nama) =
‘ABC’, nampak query tersebut ingin mencari record yang memiliki
data berisi “abc”, fungsi ucase digunakan untuk mengubah
isi fi eld nama menjadi huruf besar dan dibandingkan dengan
konstanta “ABC” untuk meyakinkan bahwa semua data “abc”
akan tampil, walaupun dituliskan dengan huruf kecil, besar,
ataupun kombinasinya.
Tetapi, cobalah mengganti query tersebut menjadi SELECT
nama FROM tbl_teman WHERE nama = ‘ABC’, perhatikan
query ini tidak menggunakan function ucase. Apakah menghasilkan
result yang sama dengan query pertama? Jika pengaturan
database Anda tidak case-sensitive (dan umumnya secara
default memang tidak case-sensitive), maka hasil kedua query
tersebut adalah sama. Artinya, dalam kasus ini Anda sebenarnya
tidak perlu menggunakan function ucase!
Baca dari Kiri ke Kanan
Query yang Anda tulis akan diproses dari kiri ke kanan, misalkan
terdapat query WHERE kondisi1 AND kondisi2 AND kondisi3,
maka kondisi1 akan terlebih dahulu dievaluasi, lalu kemudian
kondisi2, kondisi3, dan seterusnya. Tentunya dengan asumsi
tidak ada kondisi yang diprioritaskan/dikelompokkan dengan
menggunakan tanda kurung.
Logika operator AND akan langsung menghasilkan nilai false saat
ditemukan salah satu kondisi false, maka letakkan kondisi yang
paling mungkin memiliki nilai false pada posisi paling kiri. Hal ini
dimaksudkan agar SQL tidak perlu lagi mengevaluasi kondisi berikutnya
saat menemukan salah satu kondisi telah bernilai false.
Jika Anda bingung memilih kondisi mana yang layak menempati
posisi terkiri karena kemungkinan falsenya sama atau tidak bisa
diprediksi, pilih kondisi yang lebih sederhana untuk diproses.
Gambar dalam Database
Database memang tidak hanya diperuntukkan sebagai penyimpanan
teks saja, tetapi dapat juga berupa gambar. Kalau pepatah
mengatakan sebuah gambar bermakna sejuta kata, tidak
berarti kita harus menyediakan tempat penyimpanan seukuran
sejuta kata untuk menampung satu gambar! Akan lebih baik
bagi kinerja database jika Anda hanya menyimpan link atau
phpMyAdmin mengingatkan penggunaan WHERE.
CODING
12/2008 KNOW-HOW 107
lokasi gambar di dalam database, dibandingkan menyimpan
fi sik gambar tersebut.
Kecuali jika Anda tidak memiliki pilihan lain, misalnya karena
alasan keamanan atau tidak tersedianya tempat penyimpanan
lain untuk gambar Anda selain di dalam database.
Tetapi, jelas jika Anda dapat memisahkan gambar secara fi sik
dari database, maka ukuran dan beban database akan relatif
berkurang drastis, proses seperti back-up dan migrasi akan
lebih mudah dilakukan.
Pengukuran Kinerja
Terdapat tools optimizer yang bervariasi untuk tiap RDBMS, Anda
dapat menggunakannya sebagai panduan untuk meningkatkan
kinerja query, di mana Anda dapat mengetahui berapa lama waktu
eksekusi atau operasi apa saja yang dilakukan sebuah query.
Jika Anda menemukan sebuah query tampak tidak optimal,
berusahalah menulis ulang query tersebut dengan teknik dan
metode yang lebih baik. Semakin banyak query yang dapat
dioptimasi, akan semakin baik kinerja aplikasi Anda. Terutama
saat frekuensi pemakaian query tersebut relatif tinggi.
Back-up
Buatlah back-up otomatis secara periodik, sebaiknya tes dan
simulasikan prosedur restore database dan perhitungkan waktu
yang diperlukan untuk membuat sistem pulih kembali jika terjadi
sesuatu yang tidak diharapkan pada database.
Lakukan proses back-up pada waktu di mana aktivitas relatif
rendah agar tidak mengganggu kegiatan operasional.
Banyak Jalan Menuju Roma
Berikan satu masalah pada beberapa programer, maka Anda
mungkin akan mendapatkan beberapa solusi yang berbedabeda.
Banyak alternatif yang dapat diciptakan untuk menghasilkan
sesuatu, tetapi tentunya kita menginginkan alternatif
yang terbaik.
Karena itu, jangan ragu mencoba menuliskan ulang query
Anda dengan cara lain jika Anda melihat kemungkinan peningkatan
kinerja, contohnya pada potongan query berikut:
WHERE SUBSTRING(nama,1,1) =’b’
Query di atas akan mengambil record dengan kondisi karakter
pertama kolom nama adalah “b”, sehingga akan tampil isi
record seperti “Budi”, “Badu”, “Benny” dan seterusnya. Cara
lain untuk menghasilkan record yang sama adalah sebagai
berikut:
WHERE nama LIKE ‘b%’
Hasil yang ditampilkan kedua query tersebut akan sama,
tetapi performa yang dihasilkan (terutama untuk record berukuran
besar) akan berbeda. Umumnya kondisi LIKE akan bekerja
dengan lebih cepat dibandingkan function SUBSTRING.
Contoh lain yang lebih kompleks adalah seperti query berikut:
SELECT NIP, nama FROM tbl_pegawai WHERE dept = ‘IT’ OR kota
= ‘jakarta’ OR divisi = ‘programer’
Perhatikan query di atas memiliki tiga kondisi yang dipisahkan
oleh klausa OR. Alternatif lain adalah dengan menuliskan query
sebagai berikut:
SELECT NIP, nama FROM tbl_pegawai WHERE dept = ‘IT’
UNION ALL
SELECT NIP, nama FROM tbl_pegawai WHERE kota = ‘jakarta’
UNION ALL
SELECT NIP, nama FROM tbl_pegawai WHERE divisi = ‘programer’
Walaupun penulisan query menjadi lebih panjang, bisa jadi alternatif
ini akan lebih baik. Mengapa? Dengan asumsi fi eld dept
memiliki index, sementara fi eld kota dan divisi tidak diindex,
query pertama tidak akan menggunakan index dan melakukan
table scan. Berbeda dengan query kedua, index akan tetap
dilakukan pada sebagian query sehingga akan menghasilkan
kinerja yang relatif lebih baik.
Ah... Beda Tipis Saja!
Pastinya masih banyak terdapat teknik lain yang tidak akan dapat
dibahas semuanya dalam artikel ini. Di antara (atau mungkin
semua) teknik optimasi yang dibahas di atas, mungkin Anda
akan menemukan bahwa setelah diuji dengan data sampel maka
kinerja sebelum dan sesudah optimasi ternyata sama sekali tidak
signifi kan, beda tipis, atau tidak ada bedanya sama sekali!
Memang benar, dengan spesifi kasi hardware yang semakin
meningkat, data yang relatif kecil, dan alur yang sederhana,
Anda mungkin tidak akan mendapatkan perbedaan yang
signifi kan.
Tetapi jika Anda siap untuk terjun menghadapi tantangan
menangani aplikasi yang lebih besar, maka perbedaan antara
tanpa dan dengan optimasi akan sangat nyata, dengan pemahaman
dan kebiasaan coding yang baik, Anda akan dapat
menghasilkan aplikasi yang juga lebih baik.
Tidak ada salahnya menerapkan optimasi yang Anda ketahui
sedini mungkin dalam pengembangan sistem aplikasi Anda.
Bahkan jika sebuah aplikasi tnampaknya memiliki kinerja yang
cukup baik, tidak berarti lepas dari usaha optimasi lebih lanjut.
Terutama jika Anda mengharapkan aplikasi tersebut mampu
berkembang lebih jauh, tidak pernah ada kata sempurna bagi
suatu sistem aplikasi, tetapi setiap sistem selalu ada kesempatan
menjadi lebih berguna. Salah satunya dengan selalu mencari

Posted in: Pengetahuan