"Time and again I am amazed at how bad the successful software packages
out there are. Apache is easily outperformed by fnord, MySQL isn’t even
a real database, PHP is so slow that Zend actually made a business
model out of selling performance enhancing hacks for it."—diary
Fefe
PHP+MySQL. Siapa yang tidak tahu bahwa duo sejoli ini adalah pasangan
terpopular di dunia open source, bahkan di seluruh Internet. PHP telah
menggeser Perl sebagai bahasa pilihan dalam membuat aplikasi Web,
terutama bagi pemula. Dan PHP hampir selalu bersanding dengan MySQL;
90–95%+ skrip PHP di
www.hotscripts.com mungkin bertuliskan “membutuhkan PHP
dan
MySQL”.
Buku-buku PHP, termasuk untuk pemula, tidak akan dipandang
afdol kalau belum membahas MySQL. Dan programer PHP, lambat atau cepat,
mau atau tidak mau, pasti akan harus mengakrabi software database yang
satu ini.
Sayangnya, banyak programer sudah bermain-main dan memakai MySQL secara
live di situs produksi, padahal masih asing sama sekali dengan konsep
database relasional. Bahkan sebetulnya MySQL juga banyak memiliki
kekurangan dalam hal fitur SQL. Sehingga programer PHP tidak bisa
memahami dan memanfaatkan kekuatan penuh database relasional + SQL.
Buat Anda para programer pemula yang memang masih awam dengan MySQL dan
konsep database pada umumnya, tidak perlu minder atau khawatir.
Di
dunia IT yang bergerak serba cepat ini memang ada terlalu banyak hal
yang harus dipelajari. Dan kebanyakan jadinya dipelajari sambil jalan,
sambil dipraktikkan. Ketika mulai membangun CMS untuk satunet.com tahun
1999 pun, saya hanya punya skill terbatas tentang SQL: hanya tahu SELECT, UPDATE, DELETE, CREATE TABLE, dan DROP TABLE. Apa itu JOIN,
bagaimana mengubah skema tabel, bagaimana mendesain database yang baik
dan ternormalisasi, semuanya masih blank. Dan semua harus dipelajari
sambil jalan. Tapi toh akhirnya semua baik-baik saja.
Lewat artikel ini kita akan membahas hal-hal dasar apa yang perlu
diketahui agar bisa memakai MySQL dengan efektif dan efisien. Mari kita
mulai dengan hal nomor satu:
1. Tabel MySQL bukanlah array
Programer PHP atau Perl tentu saja familiar dengan array dan hash, yang
biasanya dipakai untuk menyimpan sekumpulan data terkait. Sebagian dari
mereka yang tidak familiar dengan MySQL akan cenderung menganalogikan
tabel database dengan array/hash (tepatnya, array of array atau array 2
dimensi). Tabel dipandang sama seperti sebuah array, hanya saja bisa
berukuran besar sekali dan persisten (disimpan di disk).
Cara pandang ini tidak sepenuhnya salah, karena toh dalam mengambil
record dari tabel biasanya ditampung ke dalam variabel array/hash. Hanya
saja, cara pandang ini kadang-kadang membuat programer PHP melakukan
sesuatu seperti:
atau membuat tabel seperti:
Apa yang salah dengan kode PHP pertama di atas, yang bertujuan mencari
jumlah record dalam sebuah tabel? Si programer, yang terlalu terobsesi
menganggap tabel MySQL sebagai sebuah array, mencoba membangun dulu
arraynya dengan mengisi satu-persatu elemen dari hasil query agar
nantinya bisa menggunakan fungsi array count(). Masalahnya, bagaimana kalau jumlah record ada 100 ribu? 1 juta? 10 juta? Bukan itu saja, selesai di-count() variabel $rows langsung dibuang lagi! Padahal, ada cara yang jauh lebih efisien:
Hasil querynya hanyalah sebuah record saja, tak peduli berapa pun ukuran tabel t1.
Lalu apa yang salah dengan kode SQL kedua? Si programer Perl, dalam hal
ini, terobsesi ingin mengambil tiap record di tabel dengan fungsi DBI $sth->fetchrow_array()
Enak bukan? Elemen ke-0 berisi nilai field f0, elemen ke-1 field f1,
dst. Masalahnya, kemudahan ini mengorbankan nama field yang menjadi
sangat tidak deskriptif. Belum lagi kalau tabel perlu diubah dengan
menyisipkan field-field lain di antara field yang sudah ada. Atau
field-field lama perlu dihapus. Semuanya akan menjadi mimpi buruk.
Sebagian pembaca mungkin geleng-geleng kepala. Apa benar ada programer
PHP dan Perl yang melakukan kedua hal di atas? Percaya deh, ada. Saya
pernah harus ketiban getah memaintain tabel dengan nama field kriptik
seperti ini.
2. Bahasa SQL dan Fungsi-Fungsi MySQL
MySQL adalah database SQL bukan? Sayangnya, programer PHP pemula kadang
terbatas sekali pengetahuan SQL-nya. Padahal, untuk menggunakan
database MySQL dengan efektif, ia tidak boleh malas mempelajari bahasa
kedua, yaitu SQL. Jika tidak belajar SQL, maka ada kemungkinan Anda akan
melakukan hal-hal seperti:
Apa salah kode di atas? Si programer PHP mencoba mensimulasikan klausa WHERE SQL dengan melakukan pengujian kondisi di kode PHP. Padahal, yang seharusnya dilakukan adalah:
Ini amat mengirit trafik client/server karena tidak semua record harus dikirimkan dari MySQL ke program PHP Anda.
Sebagian pembaca mungkin geleng-geleng kepala. Apa benar ada programer PHP yang seperti ini? Percaya deh, ada.
SQL sudah menyediakan cara untuk menyortir data, memformat tampilan,
mengelompokkan dan memfilter record, dsb. MySQL juga terkenal banyak
menyediakan fungsi-fungsi, mulai dari manipulasi tanggal, angka, string,
dsb. Kenali SQL dan fungsi-fungsi MySQL; jangan duplikasikan ini semua
di PHP sebab akan lebih efisien jika dilakukan di level MySQL.
Ini contoh lain programer PHP yang tidak memanfaatkan fasilitas dari MySQL:
Padahal MySQL sudah menyediakan fungsi pemformatan dan manipulasi tanggal:
Poin no. 2 ini kedengarannya klise, tapi, seperti nasihat Inggris bilang: know thy tools.
3. LIMIT, LIMIT, LIMIT
Salah satu alasan mengapa MySQL sangat cocok untuk aplikasi Web adalah mendukung klausa LIMIT.
Dengan klausa ini, mudah sekali membatasi jumlah record hasil yang
diinginkan dalam satu perintah SQL. Tidak perlu bermain kursor atau
bersusah payah lewat cara lainnya. Belakangan database lain seperti
PostgreSQL dan Firebird pun ikut mendukung fungsionalitas LIMIT (dengan sintaks yang tidak persis sama tentunya).
Sayangnya, programer PHP sendiri yang belum mengenal MySQL dengan baik tidak menggunakannya dengan semestinya.
Si programer hanya berniat mengambil 10 record, tapi menyuruh MySQL
mengambil semua record yang ada dulu. Bagaimana kalau ada 100 ribu
record? 1 juta record? 10 juta? Seharusnya, setiap kali Anda
menginginkan hanya satu, sepuluh, lima belas record, Anda perlu
memberitahu MySQL lewat klausa LIMIT.
Sehingga kita bisa mengirit trafik komunikasi client/server dan mengizinkan MySQL melakukan optimisasi terhadap query tersebut.
4. Tipe Data
Berbeda dengan PHP dan bahasa-bahasa skripting yang mengizinkan kita menaruh apa saja dalam sebuah $variable
tanpa deklarasi tipe terlebih dahulu, di MySQL kita perlu
mendeklarasikan tipe-tipe data semua field yang ada pada saat membuat
sebuah tabel. Seorang programer PHP yang tidak kenal MySQL kadang-kadang
cenderung memilih jenis data yang salah (umumnya: memilih VARCHAR()
padahal ada tipe data yang lebih tepat) dikarenakan tidak mengenal
jenis-jenis data yang tersedia.
Berikut beberapa contoh kurang tepatnya pemilihan tipe data: 1) memilih CHAR(8) atau VARCHAR(10) dan bukannya DATE untuk menyimpan tanggal; kerugiannya, lebih boros tempat dan tidak bisa memanfaatkan fungsi-fungsi khusus tanggal; 2) memilih CHAR(3) atau CHAR(6) ketimbang TINYINT UNSIGNED
untuk menyimpan data boolean (“YES” dan “NO”; atau “TRUE” dan “FALSE”;
padahal jauh lebih irit dinyatakan dengan 1 dan 0 yang hanya menempati 1
byte); 3) memilih FLOAT atau DOUBLE dan bukannya DECIMAL untuk
menyimpan jumlah uang; kerugiannya, FLOAT dan DOUBLE adalah berbasis
biner dan seringkali tidak eksak dalam menyimpan pecahan desimal.
Nomor 3 sering terjadi karena programer biasanya hanya mengenal
single/double floating point number yang tersedia di bahasa pemrograman.
Padahal database umumnya menyediakan angka pecahan berbasis desimal
yang bisa eksak menyimpan pecahan desimal.
Manual MySQL amat membantu di sini; di subbab tentang Column Types
dijelaskan dengan rinci jenis-jenis data yang ada, termasuk rentang
nilai yang dapat ditampung, berapa byte yang ditempati tipe data
tersebut, dsb.
5. Normalisasi dan Pemodelan
Normalisasi, skema, entiti-atribut, primary key (PK) dan foreign key
(FK), tabel entiti, tabel relasi, OLTP & OLAP… semuanya adalah
istilah-istilah yang umum dijumpai dalam pemodelan fisik database.
Sayangnya, banyak programer pemula tidak memiliki kemampuan modeling.
Sehingga jika disuruh mendesain skema database (sekumpulan tabel-tabel
beserta nama field dan tipenya) hasilnya tidak optimal bahkan
berantakan. Skema yang buruk berakibat terjadinya duplikasi data, tidak
scalable, performance yang buruk, tidak memenuhi requirements, dsb.
Modeling tentunya tidak bisa diajarkan dalam 1–2 hari, apalagi dalam
artikel yang singkat ini. Anda perlu membaca buku-buku mengenai
pemodelan database dan belajar dari pengalaman maupun dari model-model
yang sudah ada. Tapi beberapa nasihat yang mungkin bisa saya berikan di
sini adalah sbb.
Satu, langkah pertama dalam pemodelan adalah menemukan
entiti-entiti. Entiti bisa dibilang “objek” yang akan kita gelluti.
Misalnya, customer, produk, dan transaksi. Setiap entiti umumnya ditaruh
dalam satu tabel, tabel ini disebut tabel entiti. Langkah kedua adalah
mencari atribut-atribut entiti tersebut. Misalnya tabel customers
memiliki atribut sapaan, nama, alamat (jalan + kota + kodepos + propinsi
+ negara), tanggal record ini ditambahkan, dsb. Langkah ketiga adalah
mencari relasi di antara entiti-entiti. Umumnya relasi adalah satu dari:
1-1, 1-many, many-many. Misalnya, relasi antara transaksi dan produk
adalah many-many, artinya sebuah transaksi pembelian dapat berisi banyak
produk dan sebuah produk tentu saja dapat dibeli dalam lebih dari satu
transaksi. Setiap relasi juga akan ditempatkan pada tabel, yaitu tabel
relasi.
Dua, dalam pemodelan tidak ada istilah model yang
benar atau salah. Yang ada adalah model yang tepat dan tidak tepat untuk
keperluan tertentu. Misalnya, untuk aplikasi sederhana modelnya
sederhana. Semakin kompleks aplikasi, model pun semakin rumit (jumlah
entiti, relasi, dan atribut akan bertambah). Pada umumnya, seiring
kompleksitas bertambah, yang tadinya atribut akan berubah menjadi entiti
dikarenakan adanya kenyataan hubungan 1-many/many-many antara atribut.
Contohnya, tabel customers memiliki atribut alamat. Jika kita ingin
mendukung banyak alamat untuk satu customers, maka alamat akan menjadi
entiti dan menempati tabel sendiri. Lalu kita membuat tabel relasi
customers-alamat.
6. Indeks
Indeks adalah sesuatu yang berkaitan erat dengan implementasi, bukan
modeling. Kita seringkali perlu menambahkan indeks pada sebuah field
atau banyak field dikarenakan jika tidak ditambahkan maka performance
database tidak menjadi praktis. Serba-serbi indexing juga mungkin
terlalu panjang untuk bisa dijelaskan dalam artikel pendek ini, tapi
intinya setiap kolom yang: 1) memiliki rentang nilai cukup banyak; 2)
terletak pada tabel yang berisi banyak record; 3) seringkali disebutkan
di klausa WHERE dan/atau ORDER BY dan/atau GROUP BY; perlu diberi indeks. Ini dikarenakan indeks membantu mencari secara cepat sebuah nilai dari banyak nilai yang ada. Beberapa contoh:
* Setiap primary key umumnya otomatis diberi indeks oleh database
server, meskipun tabelnya masih berisi sedikit record atau bahkan
kosong. Ini dikarenakan database perlu selalu mengecek keberadaan sebuah
nilai field ini manakala ada sebuah record yang ditambahkan (ingat, PK
artinya tak boleh ada dua record yang mengandung nilai field ini yang
sama). Tanpa indexing, pengecekan akan linear dan memakan waktu lama.
* Field tanggal lahir dalam tabel customers kemungkinan besar harus diindeks. Bahkan dayofyear()
field ini juga mungkin perlu diindeks. Mengapa? Karena: 1) rentang
nilai cukup besar (365 hari dalam setahun x +- 60 jumlah tahun); 2)
tabel customers potensial ukurannya besar; 3) sering disebutkan di
klausa WHERE (misalnya mencari customer yang ultah hari ini).
* Field memo/notes kemungkinan besar tidak perlu diindeks (secara
biasa). Mengapa? Karena meskipun 1) rentang nilai cukup besar; dan 2)
tabel customers bisa besar; tapi 3) field ini tidak pernah disebutkan di
klausa WHERE secara langsung (mis: Anda tidak pernah menyebutkan: … WHERE notes='nilai catatan tertentu' atau WHERE notes > 'nilai tertentu'). [Catatan: ada indeks lain yang “tidak biasa” di MySQL, yaitu FULLTEXT. Tapi ini di luar cakupan artikel kita kali ini.]
* Field jenis kelamin mungkin tidak perlu diindeks, kecuali jika
perbandingan pria:wanita amat drastis bedanya. Mengapa? Sebab: 1)
rentang nilai yang ada hanyalah dua: L (lelaki) dan P (perempuan).
Meskipun Anda beri indeks, tidak akan memperbaiki kinerja.
7. Konkurensi, Locking, dan Transaksi
Programer web pemula kadang-kadang tidak menyadari bahwa program/skrip
yang dibuatnya tidaklah seperti program desktop yang dijalankan oleh
satu user. Melainkan, dalam satu waktu bisa saja ada 10 atau 100 user
yang “menembak” skrip Anda di Web. Karena itu, isu locking dan
konkurensi penting sekali. Contohnya adalah seperti ini:
Di antara baris pertama (saat kita mengambil nilai record) dan baris
keempat (saat kita menaruh kembali nilai dalam record) mungkin saja
telah terjadi beberapa kali perubahan terhadap si record. Misalnya, pada
baris pertama klien1 memperoleh nilai $value = 100. Di baris 3 $value
di-increment menjadi 101. Tapi apa yang terjadi jika selama selang
waktu itu nilai record counter1 telah menjadi 103 (karena misalnya
klien2, klien3, dan klien4 telah meng-incrementnya)? Oleh si klien1,
counter1 direset kembali menjadi 101 dan akibatnya increment oleh
klien2, klien3, dan klien4 hilang. Seharusnya nilai counter1 menjadi
104.
Untuk kasus di atas, pemecahannya cukup gampang. Lakukan increment secara atomik:
Tapi dalam kasus lain, kadang-kadang kita harus melakukan locking terhadap tabel atau record untuk menjamin bahwa selama kita // do something else… klien2, klien3, dan klien4 tidak bisa seenaknya menaikkan nilai counter:
atau (lebih baik karena kita tidak perlu melock keseluruhan tabel):
Ingat, locking dapat berakibat samping yaitu deadlock.
Transaksi. Transaksi pun sesuatu yang dipergunakan
secara meluas di dunia database, tapi hampir tidak pernah kita jumpai di
bahasa pemrograman (ini karena data di bahasa pemrograman ditaruh dalam
variabel di memori semua; tidak ada isu disk yang
crash/lambat/rusak/harus disinkronkan dengan data di memori). Karena itu
Anda perlu memahami konsep ini dari buku-buku tentang database.
8. Jenis Tabel
Di MySQL dikenal istilah table handler dan jenis tabel. Saat ini ada 3
jenis tabel utama yang bisa dipakai di MySQL: MyISAM (default),
BerkeleyDB, dan InnoDB. Yang perlu diketahui ada tiga hal: 1) tidak
semua tabel mendukung transaksi (MyISAM tidak mendukung transaksi, jadi
COMMIT dan ROLLBACK tidak melakukan sesuatu yang semestinya jika Anda
menerapkan pada tabel MyISAM); 2) tidak semua tabel punya karakteristik
performance yang sama (BerkeleyDB misalnya, lambat jika ukuran tabel
besar) dan disimpan dengan cara yang sama (tabel MyISAM misalnya
disimpan dalam 3 file: .MYI, .MYD, .frm sementara tabel-tabel dan
database-database InnoDB disimpan bersama dalam daerah disk yang disebut
tablespace; 3) distribusi MySQL yang bukan -Max tidak dikompile dengan
dukungan terhadap BerkeleyDB dan InnoDB.
Nomor 3 penting Anda ketahui karena jika kita menginstruksikan MySQL untuk membuat database dengan jenis tertentu:
Dan MySQL tidak dikompile untuk mendukung BerkeleyDB, maka MySQL tidak akan protes dengan error, melainkan membuatkan tabel tersebut untuk kita tapi dengan tipe default yaitu MyISAM. Jadi Anda perlu mengecek dulu menggunakan SHOW TABLE STATUS:
semoga agan-agan tidak kecewa."
