Mengungkapkan lima masalah database umum yang muncul dalam aplikasi PHP—termasuk desain skema database, akses database, dan kode logika bisnis yang menggunakan database—dan solusinya.
Jika hanya satu cara menggunakan database yang benar...
Ada banyak cara untuk membuat desain database, akses database, dan kode logika bisnis PHP berbasis database, tetapi biasanya semuanya salah. Artikel ini menjelaskan lima masalah umum yang muncul dalam desain database dan kode PHP yang mengakses database, dan cara memperbaikinya saat Anda menemukannya.
Masalah 1: Menggunakan MySQL secara langsung
Masalah umum adalah kode PHP lama menggunakan fungsi mysql_ secara langsung untuk mengakses database. Listing 1 menunjukkan cara mengakses database secara langsung.
Daftar 1. Akses/get.php
<?php
fungsi get_user_id( $nama )
{
$db = mysql_connect( 'localhost', 'root', 'kata sandi' );
mysql_select_db( 'pengguna' );
$res = mysql_query( "PILIH id DARI pengguna DI MANA login='".$nama."'" );
while( $baris = mysql_fetch_array( $res ) ) { $id = $baris[0] }
kembalikan $id;
}
var_dump( get_user_id( 'mendongkrak' ) );
?＞
Perhatikan bahwa fungsi mysql_connect digunakan untuk mengakses database. Perhatikan juga kuerinya, yang menggunakan penggabungan string untuk menambahkan parameter $name ke kueri.
Ada dua alternatif bagus untuk teknologi ini: modul PEAR DB dan kelas PHP Data Objects (PDO). Keduanya memberikan abstraksi dari pilihan database tertentu. Hasilnya, kode Anda dapat berjalan di IBM® DB2®, MySQL, PostgreSQL, atau database lain yang ingin Anda sambungkan tanpa banyak penyesuaian.
Keuntungan lain dari penggunaan modul PEAR DB dan lapisan abstraksi PDO adalah Anda dapat menggunakan operator ? dalam pernyataan SQL. Melakukan hal ini membuat SQL lebih mudah untuk memelihara dan melindungi aplikasi Anda dari serangan injeksi SQL.
Kode alternatif menggunakan PEAR DB ditunjukkan di bawah ini.
Daftar 2. Akses/get_good.php
<?php
require_once("DB.php");
fungsi get_user_id( $nama )
{
$dsn = 'mysql://root:password@localhost/pengguna';
$db =& DB::Hubungkan( $dsn, array() );
if (PEAR::isError($db)) { die($db->getMessage()); }
$res = $db->query( 'PILIH id DARI pengguna DIMANA login=?',array( $name ) ) ;
$id = nol;
while( $res->fetchInto( $baris ) ) { $id = $baris[0]; }
kembalikan $id;
}
var_dump( get_user_id( 'mendongkrak' ) );
?＞
Perhatikan bahwa semua penggunaan langsung MySQL telah dihilangkan, kecuali untuk string koneksi database di $dsn. Selain itu, kami menggunakan variabel $name dalam SQL melalui operator ? Kemudian, data kueri dikirim melalui array di akhir metode query().
Masalah 2: Tidak menggunakan fitur kenaikan otomatis
Seperti kebanyakan database modern, MySQL memiliki kemampuan untuk membuat pengidentifikasi unik kenaikan otomatis pada basis per-catatan. Selain itu, kita masih akan melihat kode yang terlebih dahulu menjalankan pernyataan SELECT untuk menemukan id terbesar, kemudian menambah id tersebut sebesar 1, dan menemukan rekor baru. Listing 3 menunjukkan contoh pola yang buruk.
Listing 3. Badid.sql
DROP TABLE JIKA ADA pengguna;
BUAT TABEL pengguna (
id SEDANG,
teks masuk,
kata sandi TEKS
);
MASUKKAN KE DALAM NILAI pengguna ( 1, 'jack', 'pass' );
MASUKKAN KE NILAI pengguna ( 2, 'joan', 'pass' );
INSERT INTO users VALUES (1, 'jane', 'pass' );
Di sini bidang id hanya ditentukan sebagai bilangan bulat. Jadi, meskipun harus unik, kita dapat menambahkan nilai apa pun, seperti yang ditunjukkan dalam beberapa pernyataan INSERT setelah pernyataan CREATE. Listing 4 menunjukkan kode PHP untuk menambahkan pengguna ke skema jenis ini.
Daftar 4. Add_user.php
<?php
require_once("DB.php");
fungsi add_user( $nama, $pass )
{
$baris = array();
$dsn = 'mysql://root:kata sandi@localhost/bad_badid';
$db =& DB::Hubungkan( $dsn, array() );
if (PEAR::isError($db)) { die($db->getMessage()); }
$res = $db->query( "PILIH maks(id) DARI pengguna" );
$id = nol;
while( $res->fetchInto( $row ) ) { $id = $row[0]; } $
id += 1;
$sth = $db->prepare( "MASUKKAN KE DALAM NILAI pengguna(?,?,?) " );
$db->eksekusi( $sth, array( $id, $nama, $pass ) );
kembalikan $id;
}
$id = add_user( 'jerry', 'lulus' )
;
?＞
Kode di add_user.php pertama-tama melakukan kueri untuk menemukan nilai maksimum id. File tersebut kemudian menjalankan pernyataan INSERT dengan nilai id bertambah 1. Kode ini akan gagal dalam kondisi balapan di server yang penuh muatan. Ditambah lagi, ini juga tidak efisien.
Jadi apa alternatifnya? Gunakan fitur kenaikan otomatis di MySQL untuk secara otomatis membuat ID unik untuk setiap penyisipan. Skema yang diperbarui terlihat seperti ini.
Listing 5. Goodid.php
DROP TABLE JIKA ADA pengguna;
BUAT TABEL pengguna (
id SEDANG BUKAN NULL AUTO_INCREMENT,
masuk TEKS BUKAN NULL,
kata sandi TEKS BUKAN NULL,
KUNCI UTAMA(id)
);
MASUKKAN KE DALAM NILAI pengguna ( null, 'jack', 'pass' );
MASUKKAN KE DALAM NILAI pengguna (null, 'joan', 'pass' );
INSERT INTO users VALUES ( null, 'jane', 'pass' );
Kami menambahkan tanda NOT NULL untuk menunjukkan bahwa bidang tersebut tidak boleh nol. Kami juga menambahkan tanda AUTO_INCREMENT untuk menunjukkan bahwa bidang tersebut bertambah secara otomatis, dan tanda PRIMARY KEY untuk menunjukkan bahwa bidang tersebut adalah id. Perubahan ini mempercepat segalanya. Listing 6 menunjukkan kode PHP yang diperbarui untuk memasukkan pengguna ke dalam tabel.
Daftar 6. Add_user_good.php
<?php
require_once("DB.php");
fungsi add_user( $nama, $pass )
{
$dsn = 'mysql://root:password@localhost/good_genid';
$db =& DB::Hubungkan( $dsn, array() );
if (PEAR::isError($db)) { die($db->getMessage()); }
$sth = $db->prepare( "MASUKKAN KE DALAM NILAI pengguna(null,?,?)" );
$db->eksekusi( $sth, array( $nama, $pass ) );
$res = $db->query( "PILIH last_insert_id()" );
$id = nol;
while( $res->fetchInto( $baris ) ) { $id = $baris[0]; }
kembalikan $id;
}
$id = tambahkan_pengguna( 'jeri', 'lulus' )
;
?>
Sekarang alih-alih mendapatkan nilai id maksimum, saya langsung menggunakan pernyataan INSERT untuk memasukkan data, dan kemudian menggunakan pernyataan SELECT untuk mengambil id dari catatan yang terakhir dimasukkan. Kode ini jauh lebih sederhana dan efisien dibandingkan versi asli dan pola terkaitnya.
Pertanyaan 3: Menggunakan banyak database
Kadang-kadang, kita akan melihat sebuah aplikasi di mana setiap tabel berada dalam database terpisah. Hal ini wajar dalam database yang sangat besar, namun untuk aplikasi umum tingkat partisi ini tidak diperlukan. Selain itu, kueri relasional tidak dapat dilakukan di seluruh database, sehingga menghilangkan keseluruhan gagasan penggunaan database relasional, belum lagi akan lebih sulit untuk mengelola tabel di beberapa database. Jadi, seperti apa seharusnya tampilan banyak database? Pertama, Anda memerlukan beberapa data. Listing 7 menunjukkan data tersebut dibagi menjadi empat file.
Listing 7. File database
Files.sql:
BUAT file TABEL (
id SEDANG,
user_id SEDANG,
nama TEKS,
jalur TEKS
)
;
MASUKKAN KE DALAM file NILAI ( 1, 1, 'test1.jpg', 'files/test1.jpg' );
MASUKKAN KE DALAM file NILAI (2, 1, 'test2.jpg', 'files/test2.jpg' )
;
DROP TABLE JIKA ADA pengguna;
BUAT TABEL pengguna (
id SEDANG,
teks masuk,
kata sandi TEKS
)
;
MASUKKAN KE DALAM NILAI pengguna ( 1, 'jack', 'pass' );
INSERT INTO user VALUES (2, 'jon', 'pass' );
Dalam versi multi-database dari file-file ini, Anda harus memuat pernyataan SQL ke dalam satu database dan kemudian memuat pernyataan SQL pengguna ke database lain. Kode PHP yang digunakan untuk menanyakan database untuk file yang terkait dengan pengguna tertentu ditunjukkan di bawah ini.
Daftar 8.Getfiles.php
<?php
require_once("DB.php");
fungsi get_user( $nama )
{
$dsn = 'mysql://root:password@localhost/bad_multi1';
$db =& DB::Hubungkan( $dsn, array() );
if (PEAR::isError($db)) { die($db->getMessage()); }
$res = $db->query( "PILIH id DARI pengguna DIMANA login=?",array( $nama ) ) ;
$uid = nol;
while( $res->fetchInto( $row ) ) { $uid = $row[0]
;
}
fungsi get_files( $nama )
{
$uid = get_user( $nama );
$baris = array();
$dsn = 'mysql://root:password@localhost/bad_multi2';
$db =& DB::Hubungkan( $dsn, array() );
if (PEAR::isError($db)) { die($db->getMessage()); }
$res = $db->query( "PILIH * DARI file WHERE user_id=?",array( $uid ) ) ;
while( $res->fetchInto( $baris ) ) { $baris[] = $baris;
kembalikan $baris;
}
$file = get_files( 'mendongkrak' );
var_dump( $file );
?>
Fungsi get_user terhubung ke database yang berisi tabel pengguna dan mengambil ID pengguna tertentu. Fungsi get_files terhubung ke tabel files dan mengambil baris file yang terkait dengan pengguna tertentu.
Cara yang lebih baik untuk melakukan semua hal ini adalah dengan memuat data ke dalam database dan kemudian mengeksekusi query, seperti di bawah ini.
Daftar 9.Getfiles_good.php
<?php
require_once("DB.php");
fungsi get_files( $nama )
{
$baris = array();
$dsn = 'mysql://root:kata sandi@localhost/good_multi';
$db =& DB::Hubungkan( $dsn, array() );
if (PEAR::isError($db)) { die($db->getMessage()); }
$res = $db->query("PILIH file.* DARI pengguna, file DI MANA
pengguna.login=? DAN pengguna.id=file.pengguna_id",
larik($nama) );
while( $res->fetchInto( $baris ) ) { $baris[] = $baris }
kembalikan $baris;
}
$file = get_files( 'mendongkrak' );
var_dump( $file );
?>
Kodenya tidak hanya lebih pendek, tetapi juga lebih mudah dipahami dan efisien. Daripada mengeksekusi dua query, kami mengeksekusi satu query.
Meskipun pertanyaan ini mungkin terdengar tidak masuk akal, dalam praktiknya kita biasanya menyimpulkan bahwa semua tabel harus berada dalam database yang sama kecuali ada alasan yang sangat kuat.
Pertanyaan 4: Tidak menggunakan relasi
Database relasional berbeda dengan bahasa pemrograman karena tidak memiliki tipe array. Sebaliknya, mereka menggunakan hubungan antar tabel untuk membuat struktur satu-ke-banyak antar objek, yang memiliki efek yang sama seperti array. Salah satu masalah yang saya lihat dalam aplikasi adalah ketika para insinyur mencoba menggunakan database seperti bahasa pemrograman, dengan membuat array menggunakan string teks dengan pengidentifikasi yang dipisahkan koma. Lihat polanya di bawah ini.
Listing 10. Bad.sql
DROP TABLE JIKA ADA file;
BUAT file TABEL (
id SEDANG,
nama TEKS,
jalur TEKS
);
DROP TABLE JIKA ADA pengguna;
BUAT TABEL pengguna (
id SEDANG,
teks masuk,
kata sandi TEKS,
file TEKS
);
MASUKKAN KE DALAM file NILAI ( 1, 'test1.jpg', 'media/test1.jpg' );
MASUKKAN KE dalam file NILAI (2, 'test1.jpg', 'media/test1.jpg' );
,
'pass', '1,2' );
Dalam bahasa pemrograman, array harus digunakan untuk mewakili file yang terkait dengan pengguna. Dalam contoh ini, pemrogram memilih untuk membuat bidang file yang berisi daftar id file yang dipisahkan koma. Untuk mendapatkan daftar semua file untuk pengguna tertentu, pemrogram harus terlebih dahulu membaca baris dari tabel pengguna, kemudian mengurai teks file dan menjalankan pernyataan SELECT terpisah untuk setiap file. Kode ditunjukkan di bawah ini.
Daftar 11. Dapatkan.php
<?php
require_once("DB.php");
fungsi get_files( $nama )
{
$dsn = 'mysql://root:password@localhost/bad_norel';
$db =& DB::Hubungkan( $dsn, array() );
if (PEAR::isError($db)) { die($db->getMessage()); }
$res = $db->query( "PILIH file DARI pengguna WHERE login=?",array( $name ) ) ;
$file = nol;
while( $res->fetchInto( $row ) ) { $files = $row[0]; }
$rows = array();
foreach( split( ',',$files ) sebagai $file )
{
$res = $db->query( "PILIH * DARI file DI MANA id=?",
susunan( $file ) );
while( $res->fetchInto( $baris ) ) { $baris[] = $baris;
}
kembalikan $baris;
}
$file = get_files( 'mendongkrak' );
var_dump( $file );
?>
Teknologinya lambat, sulit dipelihara, dan tidak memanfaatkan database dengan baik. Satu-satunya solusi adalah merancang ulang skema untuk mengubahnya kembali ke bentuk relasional tradisional seperti yang ditunjukkan di bawah ini.
Listing 12. Good.sql
DROP TABLE JIKA ADA file;
BUAT file TABEL (
id SEDANG,
user_id SEDANG,
nama TEKS,
jalur TEKS
);
DROP TABLE JIKA ADA pengguna;
BUAT TABEL pengguna (
id SEDANG,
teks masuk,
kata sandi TEKS
);
MASUKKAN KE DALAM NILAI pengguna ( 1, 'jack', 'pass' );
MASUKKAN KE dalam file NILAI ( 1, 1, 'test1.jpg', 'media/test1.jpg' );
INSERT INTO files VALUES (2, 1, 'test1.jpg', 'media/test1.jpg' );
Di sini, setiap file terkait dengan pengguna dalam tabel file melalui fungsi user_id. Ini mungkin bertentangan dengan keinginan siapa pun yang menganggap banyak file sebagai array. Tentu saja, array tidak mereferensikan objek yang dikandungnya—bahkan sebaliknya. Namun dalam database relasional, begitulah cara kerjanya, dan kueri menjadi lebih cepat dan sederhana karenanya. Listing 13 menunjukkan kode PHP yang sesuai.
Daftar 13. Get_good.php
<?php
require_once("DB.php");
fungsi get_files( $nama )
{
$dsn = 'mysql://root:password@localhost/good_rel';
$db =& DB::Hubungkan( $dsn, array() );
if (PEAR::isError($db)) { die($db->getMessage());
$baris = array();
$res = $db->query("PILIH file.* DARI pengguna,file WHERE pengguna.login=?
DAN pengguna.id=files.user_id",array( $nama ) );
while( $res->fetchInto( $baris ) ) { $baris[] = $baris;
kembalikan $baris;
}
$file = get_files( 'mendongkrak' );
var_dump( $file );
?＞
Di sini, kita membuat query ke database untuk mendapatkan semua baris. Kodenya tidak rumit, dan menggunakan database seperti yang dimaksudkan.
Pertanyaan 5: Pola n+1
Saya tidak dapat memberi tahu Anda berapa kali saya melihat aplikasi besar di mana kode pertama-tama mengambil beberapa entitas (katakanlah pelanggan) dan kemudian bolak-balik mengambilnya satu per satu untuk mendapatkan masing-masing Detail entitas. Kami menyebutnya mode n+1 karena kueri dieksekusi berkali-kali - satu kueri mengambil daftar semua entitas, dan kemudian satu kueri dieksekusi untuk masing-masing n entitas. Ini bukan masalah jika n=10, tapi bagaimana dengan n=100 atau n=1000? Maka pasti akan terjadi inefisiensi. Listing 14 menunjukkan contoh pola ini.
Listing 14. Schema.sql
DROP TABLE JIKA ADA penulis;
BUAT TABEL penulis (
id SEDANG BUKAN NULL AUTO_INCREMENT,
nama TEKS BUKAN NULL,
KUNCI UTAMA(id)
);
DROP TABLE JIKA ADA buku;
BUAT TABEL buku (
id SEDANG BUKAN NULL AUTO_INCREMENT,
author_id SEDANG BUKAN NULL,
nama TEKS BUKAN NULL,
KUNCI UTAMA(id)
)
;
INSERT INTO penulis VALUES ( null, 'Dave Thomas' );
INSERT INTO books VALUES ( null, 1, 'Code Generation in Action' );
MASUKKAN KE DALAM NILAI buku ( null, 1, 'Podcasting Hacks' );
MASUKKAN KE DALAM buku NILAI ( null, 1, 'PHP Hacks' );
MASUKKAN KE DALAM buku NILAI ( null, 2, 'Pragmatic Programmer' );
MASUKKAN KE DALAM NILAI buku ( null, 2, 'Ruby on Rails' );
INSERT INTO books VALUES ( null, 2, 'Programming Ruby' );
Polanya dapat diandalkan dan tidak ada kesalahan di dalamnya. Masalahnya terletak pada kode yang mengakses database untuk menemukan semua buku karya penulis tertentu, seperti yang ditunjukkan di bawah ini.
Daftar 15. Dapatkan.php
<?php
require_once('DB.php');
$dsn = 'mysql://root:password@localhost/good_books';
$db =& DB::Hubungkan( $dsn, array() );
if (PEAR::isError($db)) { die($db->getMessage() }
fungsi get_author_id( $nama )
{
global $db;
$res = $db->query( "PILIH id DARI penulis WHERE nama=?",array( $nama ) );
$id = nol;
while( $res->fetchInto( $baris ) ) { $id = $baris[0] }
kembalikan $id;
}
fungsi get_books( $id )
{
global $db;
$res = $db->query( "PILIH id DARI buku WHERE author_id=?",array( $id ) );
$id = susunan();
while( $res->fetchInto( $baris ) ) { $id []= $baris[0] }
kembalikan $id;
}
fungsi get_book( $id )
{
global $db;
$res = $db->query( "PILIH * DARI buku DI MANA id=?", array( $id ) );
while( $res->fetchInto( $baris ) ) { kembalikan $baris; }
kembalikan nol;
}
$author_id = get_author_id( 'Jack Herrington' );
$buku = get_books( $author_id );
foreach( $buku sebagai $book_id ) {
$buku = get_book( $book_id );
var_dump( $buku );
}
?>
Jika Anda melihat kode di bawah ini, Anda mungkin berpikir, "Hei, ini sangat jelas dan sederhana." Pertama, dapatkan id penulisnya, lalu dapatkan daftar bukunya, lalu dapatkan informasi tentang setiap buku. Ya, jelas dan sederhana, tetapi apakah efisien? Jawabannya adalah tidak. Lihat berapa banyak kueri yang dijalankan hanya untuk mengambil buku Jack Herrington. Sekali untuk mendapatkan id, lain kali untuk mendapatkan daftar buku, lalu lakukan query per buku. Tiga buku membutuhkan lima pertanyaan!
Solusinya adalah dengan menggunakan fungsi untuk melakukan query dalam jumlah besar seperti yang ditunjukkan di bawah ini.
Daftar 16. Get_good.php
<?php
require_once('DB.php');
$dsn = 'mysql://root:password@localhost/good_books';
$db =& DB::Hubungkan( $dsn, array() );
jika (PEAR::isError($db)) { die($db->getMessage() }
fungsi get_books( $nama )
{
global $db;
$res = $db->query("PILIH buku.* DARI penulis,buku DI MANA buku.penulis_id=penulis.id DAN penulis.nama=?",
larik($nama) );
$baris = larik();
while( $res->fetchInto( $baris ) ) { $baris []= $baris;
kembalikan $baris;
}
$buku = get_books( 'Jack Herrington' );
var_dump( $buku );
?>
Mengambil daftar sekarang memerlukan satu kueri yang cepat. Ini berarti saya kemungkinan besar harus memiliki beberapa metode jenis ini dengan parameter berbeda, tetapi sebenarnya tidak ada pilihan. Jika Anda ingin memiliki aplikasi PHP yang scalable, Anda harus menggunakan database secara efisien, yang berarti kueri yang lebih cerdas.
Masalah dengan contoh ini adalah contohnya agak terlalu jelas. Secara umum, jenis masalah n+1 atau n*n ini jauh lebih halus. Dan mereka hanya muncul ketika administrator database menjalankan Query Profiler pada sistem ketika sistem mengalami masalah kinerja.
Kesimpulan
Basis data adalah alat yang ampuh, dan seperti semua alat canggih lainnya, Anda dapat menyalahgunakannya jika Anda tidak tahu cara menggunakannya dengan benar. Trik untuk mengidentifikasi dan memecahkan masalah ini adalah dengan lebih memahami teknologi yang mendasarinya. Saya sudah lama mendengar penulis logika bisnis mengeluh bahwa mereka tidak mau memahami database atau kode SQL. Mereka menggunakan database sebagai objek dan bertanya-tanya mengapa kinerjanya sangat buruk.
Mereka gagal menyadari betapa pentingnya pemahaman SQL untuk mengubah database dari kebutuhan yang sulit menjadi aliansi yang kuat. Jika Anda menggunakan database setiap hari tetapi belum terbiasa dengan SQL, silakan baca The Art of SQL. Ini adalah buku praktis dan ditulis dengan baik yang dapat memandu Anda memahami dasar database.