PHP + MySQL, Siapa yang tidak tahu bahwa dua sejoli ini adalah pasangan terpopuler 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, dan programmer PHP, cepat atau lambat, mau tidak mau, pasti harus mengakrabi software database yang satu ini.
Sayangnya, banyak programmer 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 programmer PHP tidak bisa memahami dan memanfaatkan kekuatan penuh database relasional + SQL.
Buat anda para Programmer 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 di praktekkan.
Ketika mulai membangun CMS untuk satunet.com tahun 1999 pun, saya hanya punya skill terbatas tentang SQL : Hanya tahu SELECT, UPDATE, DELETE, 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 berjalan. Tapi toh akhinya semua baik - baik saja.
Mari kita bahas hal - hal dasar apa yang perlu diketahui agar bisa memakai MySQL dengan efektif dan efisien. Mari kita mulai :
1. Tabel MySQL bukanlah Array
Programmer 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 (di simpan di disk).
Cara pandang ini tidak sepenuhnya salah, toh karena dalam mengambil record dari tabel biasanya di tampung ke dalam variable array/hash. Hanya saja, cara pandang ini kadang - kadang membuat programmer 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 diatas, yang bertujuan mencari jumlah record dalam sebuah tabel? Si programmer, terlalu terobsesi menganggap tabel MySQL sebagai array, mencoba membangun dulu arraynya dengan mengisi satu - persatu elemen dari hasil query agar nantinya bisa menggunakan fungsi array count (). Masalanya, bagaimana kalau julah record ada 100 ribu atau satu juta? bahkan ungkin 10 juta? Bukan itu saja, selesai di-count () variabel $rows langsung dibuang lagi! padahal, ada cara yang jauh lebih efisien:
SELECT COUNTU(*) FROM1
Hasil querynya hanyalah sebuah record saja, tidak perlu berapa pun ukuran tabel t1.
Lalu apa yang salah dengan kode SQL kedua? Si programmer Perl, dala hal ini, terobsesi ingin mengabiltiap record di tabel dengan fungsi DBI $sth ->fecthrow_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 dirubah dengan menyisipkan field - field lain diantara field yang sudah ada. Atau field - field lama perlu dihapus. Semuanya akan menjadi mimpi buruk.
Sebagian pembaca mungkin geleng - geleng kepala. Apa benar ada programmer PHP dan Perl yang melakukan kedua hal di atas? Percaya deh, ada. Saya pernah harus ketiban getah memaintenance tabel dengan nama field kriptik seperti ini.
2. Bahasa SQL dan Fungsi - Fungsi MySQL
MySQL adalah database SQL bukan? Sayangnya, programmer 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 SQ, 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 programmer PHP mencoba mensimulasikan klausa WHERE SQL dengan melakukan pengujian kondisi di kode PHP. Padahal, yang seharusnya dilakukan adalah :
SELECT*FROM bigtable WHERE aage >=40
Ini sangat mengirit trafik client/server karena tidak seua record harus dikirimkan dari MySQL ke program PHP anda.
Sebagian pembaca mungkin geleng - geleng kepala. Apa benar ada programmer PHP yang seperti ini? Percaya deh, ada.
SQL sudah menyediakan cara untuk menyortir data, memformat tampilan, mengelompokkan dan memfilter record, dsb. Kenali SQL dan fungsi - fungsi MySQL, jangan duplikasikan ini semua di PHP sebab akan lebih efisien jika dilakukan di level 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 (misal : 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 | ...
+------+--------+-...
Ternyata MyISAM!
Penutup
Sebetulnya untuk memakai MySQL dengan baik dan benar
diperlukan skill-skill dasar lain seperti membackup, merestore, mengeset
parameter-parameter server, memonitor server, dsb. Tapi itu semua lebih
merupakan tugas seorang administrator (DBA).
0 Komentar untuk "8 Hal Yang Harus Diketahui Programmer Pemula Tentang MySQL"