- 1. Tabel MySQL bukanlah array
- 2. Bahasa SQL dan Fungsi-Fungsi MySQL
- 3. LIMIT, LIMIT, LIMIT
- 4. Tipe Data
- 5. Normalisasi dan Pemodelan
- 6. Indeks
- 7. Konkurensi, Locking, dan Transaksi
- 8. Jenis Tabel
- Penutup
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:
$res = mysql_query("SELECT * FROM t1");
$rows = array();
while ($row = mysql_fetch_row($res)) $rows[] = $row;
echo "Jumlah record di tabel t1 = ", count($rows);
atau membuat tabel seperti:
CREATE TABLE t2 ( f0 INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, f1 INT UNSIGNED, f2 VARCHAR(5), f3 VARCHAR(200), f4 VARCHAR(200), f5 BLOB );
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:
SELECT COUNT(*) FROM t1
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()
@row = $sth->fetchrow_array(); print $row[0]; # f0 print $row[1]; # f1 print $row[2]; # f2 # ...
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:
$res = mysql_query("SELECT * FROM bigtable");
while ($row = mysql_fetch_assoc($res)) {
if ($row['age'] >= 40) {
echo "Ditemukan kustomer yang berusia lebih dari 40 tahun!\n";
break;
}
}
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:
SELECT * FROM bigtable WHERE age >= 40
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:
$res = mysql_query("SELECT * FROM customers");
while ($row = mysql_fetch_assoc($res)) {
# format semula yyyy-mm-dd...
preg_match("/(\d\d\d\d)-(\d\d?)-(\d\d?)/", $row[date], $matches);
# ... dan ingin dijadikan dd/mm/yyyy
$tanggal = "$matches[3]/$matches[2]/$matches[1]";
echo "Nama=$row[name], Tanggal lahir=$tanggal<br>\n";
}
Padahal MySQL sudah menyediakan
fungsi pemformatan dan manipulasi tanggal:
$res = mysql_query("SELECT name, DATE_FORMAT(date,'%d-%m-%Y') as tanggal ".
"FROM customers");
while ($row = mysql_fetch_assoc($res)) {
# tidak perlu capek-capek manipulasi string lagi...
echo "Nama=$row[name], Tanggal lahir=$row[tanggal]<br>\n";
}
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.
$res = mysql_query("SELECT name FROM users ORDER BY date");
$rows = array();
for ($i=1; $i<=10; $i++) $rows[] = mysql_fetch_row($res);
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.
SELECT name FROM users ORDER BY date LIMIT 10
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:
$res = mysql_query("SELECT value FROM counters WHERE name='counter1'");
list ($value) = mysql_fetch_row($res);
$value++;
// do something else first...
$res = mysql_query("UPDATE counter SET value=$value WHERE name='counter1'");
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:
// tidak perlu ambil nilai counter dulu...
// do something else first...
$res = mysql_query("UPDATE counter SET value=value+1 WHERE name='counter1'");
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:
mysql_query("LOCK TABLES cuonters");
$res = mysql_query("SELECT value FROM counters WHERE name='counter1'");
list ($value) = mysql_fetch_row($res);
// do something else first... increase value or something...
$res = mysql_query("UPDATE counter SET value=$value WHERE name='counter1'");
mysql_query("UNLOCK TABLES");
atau
(lebih baik karena kita tidak perlu melock keseluruhan tabel):
mysql_query("SELECT GET_LOCK('lock1')");
$res = mysql_query("SELECT value FROM counters WHERE name='counter1'");
list ($value) = mysql_fetch_row($res);
// do something else first... increase value or something...
$res = mysql_query("UPDATE counter SET value=$value WHERE name='counter1'");
mysql_query("SELECT RELEASE_LOCK('lock1')");
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:
CREATE TABLE (...) TYPE=BDB;
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:
mysql> create table t4 (i int) type=innodb; Query OK, 0 rows affected (0.00 sec) mysql> show table status from mydb like 't4'; +------+--------+-... | Name | Type | ... +------+--------+-... | t4 | MyISAM | ... +------+--------+-...
Tidak ada komentar:
Posting Komentar