Data yang tersimpan dalam database semakin lama akan semakin besar ukuran atau
volumenya. Kalau tidak didukung dengan kecepatan akses yang memadai maka akan
semakin menurun unjuk kerjanya. Ukuran unjuk kerja dalam hal ini kecepatan akses
data dipengaruhi oleh banyak faktor. Pada bab ini akan membahas tentang optimasi
query serta faktor-faktor lain yang berpengaruh terhadap optimalisasi kecepatan akses
data.
OPTIMASI PERINTAH SQL
Desain aplikasi saja tidak cukup untuk meningkatkan unjuk kerja harus didukung
dengan optimasi dari perintah SQL yang digunakan pada aplikasi tersebut. Dalam
mendesain database, seringkali lokasi fisik data tidak menjadi perhatian penting.
Karena hanya desain logik saja yang diperhatikan. Padahal untuk menampilkan hasil
query dibutuhkan pencarian yang melibatkan struktur fisik penyimpanan data. Inti dari
optimasi query adalah meminimalkan “jalur” pencarian untuk menemukan data yang
disimpan dalam lokasi fisik.
Index pada database digunakan untuk meningkatkan kecepatan akses data. Pada
saat query dijalankan, index mencari data dan menentukan nilai ROWID yang
membantu menemukan lokasi data secara fisik di disk. Akan tetapi penggunaan index
yang tidak tepat, tidak akan meningkatkan unjuk kerja dalam hal ini kecepatan aksesdata.
Misal digunakan index yang melibatkan tiga buah kolom yang mengurutkan
kolom menurut kota, propinsi dan kode pos dari tabel mahasiswa, sebagai berikut :
CREATE INDEX idx_kota_prop_kodepos ON Mahasiswa(kota, propinsi, kode_pos)
TABLESPACE INDX;
Kemudian user melakukan query sebagai berikut :
SELECT * FROM mahasiswa WHERE propinsi=’Jawa Tengah’;
Pada query ini, index tidak akan digunakan karena kolom pertama (kota) tidak digunakan dalam klausa WHERE. Jika user sering melakukan query ini,
maka kolom index harus diurutkan menurut propinsi. Selain itu, proses pencarian data akan lebih cepat jika data terletak pada block tabel yang berdekatan daripada harus mencari di beberapa datafile yang terletak pada block yang berbeda.
Misal pada perintah SQL berikut ini :
SELECT * FROM mahasiswa
WHERE id BETWEEN 1010 AND 2010;
Query ini akan melakukan “scan” terhadap sedikit data block jika tabel mahasiswa
diatas diurutkan berdasarkan kolom id. Untuk mengurutkan berdasarkan kolom yang
berbeda-beda maka tabel disimpan dalam flat file, kemudian tabel diekspor dan
diurutkan sesuai kebutuhan.
Alternatif yang lain, bisa digunakan perintah untuk membuat tabel lain yang
memiliki urutan yang berbeda dari tabel asal, seperti perintah SQL berikut :
CREATE TABLE mahasiswa_urut
AS SELECT * FROM mahasiswa
ORDER BY id;
Pada SQL diatas, tabel mahasiswa_urut berisi data yang sama dengan tabel mahasiswa
hanya datanya terurut berdasarkan kolom id.
INFORMASI JALUR AKSES QUERY
Bagaimana cara melihat jalur akses yang akan digunakan database saat
melakukan query ? Pada Database Oracle, informasi ini dapat dilihat dengan
menggunakan perintah explain plan, yang akan memberi informasi tentang rencana
eksekusi dari suatu query. Informasi ini disimpan dalam tabel PLAN_TABLE yang
terdapat di schema user yang mengeksekusi perintah tersebut.
Sebelum melakukan perintah explain plan, terlebih dahulu buat table
PLAN_TABLE dengan menggunakan script utlxplan.sql yang diambil dari
\%ORACLE_HOME%\RDBMS\ADMIN.
Setelah itu table PLAN_TABLE dapat digunakan seperti contoh berikut :
SQL> explain plan
Set statement_id=’test1’
Into plan_table for
Select * from mahasiswa where nilai_uts=60;
Dalam PLAN_TABLE rencana eksekusi diatas dikenal dengan nama test1 yang
terdefinisi pada kolom statement_id.
Untuk melihat rencana eksekusi dari test1, digunakan perintah SELECT berikut :
SELECT LPAD(’ ’,2*Level)||Operation||’ ’||Options||’ ’||Object_Name Q_Plan
FROM plan_table
WHERE statement_id=’test1’
CONNECT BY PRIOR id=parent_id AND statement_id=’test1’
START WITH id=0 AND statement_id=’test1’;
Contoh hasil dari eksekusi query tersebut :
Q_PLAN
--------------------------------------------------------------------
SELECT STATEMENT
TABLE ACCESS FULL MAHASISWA
Output tersebut dibaca mulai dari yang indent-nya paling dalam yaitu : TABLE
ACCESS FULL MAHASISWA. Dikarenakan klausa WHERE melibatkan kolom nilai
namun kolom nilai tidak ada index-nya, maka Oracle melakukan full table scan. Setelah seluruh tabel mahasiswa selesai dibaca, selanjutnya adalah SELECT STATEMENT yang berfungsi untuk menampilkan hasil query.
Perintah dari SQL yang digunakan untuk meminta sebuah tindakan kepada DBMS.
Pernyataan dasar SQL antara lain :
1. ALTER : Merubah struktur tabel
2. COMMIT : Mengakhiri eksekusi transaksi
3. CREATE : Membuat tabel, indeks
4. DELETE : Menghapus baris pada sebuah tabel
5. DROP : Menghapus tabel, indeks
6. GRANT : Menugaskan hak terhadap basis data kepada user
7. INSERT : Menambah baris pada tabel
8. REVOKE : Membatalkan hak kepada basis data
9. ROLLBACK: Mengembalikan pada keadaan semula apabila transaksi gagal dilaksanakan
10. SELECT : Memilih baris dan kolom pada sebuah tabel
11. UPDATE : Mengubah value pada baris sebuah tabel
Nama
Nama digunakan sebagai identitas, yaitu identitas bagi objek pada DBMS. Misal : tabel, kolom dan pengguna.
Tipe data
Tipe data yang ada dalam MYSQL :
a. Tipe data numerik antara lain :
1. TINYINT : Nilai integer yang sangat kecil
2. SMALLINT : Nilai integer yang kecil
3. MEDIUMINT : Nilai integer yang sedang
4. INT : Nilai integer dengan nilai standar
5. BEGINT : Nilai integer dengan nilai besar
6. FLOAT :Bilangan decimal dengan single-precission
7. DOUBLE :Bilangan decimal dengan double-precission
8. DECIMAL(M,D) : Bilangan float yang dinyatakan sebagai string. M : jumlah
digit yang disimpan, D : jumlah angka dibelakang koma
b. Tipe data String antara lain :
1. CHAR : Karakter yang memiliki panjang tetap yaitu sebanyak n
2. VARCHAR : Karakter yang memiliki panjang tidak tetap yaitu maksimum n
3. TINYBLOB : BLOB dengan ukuran sangat kecil
4. BLOB : BLOB yang memiliki ukuran kecil
5. MEDIUMBLOB : BLOB yang memiliki ukuran sedang
6. LONGBLOB : BLOB yang memiliki ukuran besar
7. TINYTEXT : teks dengan ukuran sangat kecil
8. TEXT : teks yang memiliki ukuran kecil
9. MEDIUMTEXT : teks yang memiliki ukuran sedang
10. LONGTEXT : teks yang memiliki ukuran besar
11. ENUM : kolom diisi dengan satu member enumerasi
12. SET : Kolom dapat diisi dengan beberapa nilai anggota himpunan
c. Tipe data tunggal dan jam :
1. DATE : date memiliki format tahun-bulan-tanggal
2. TIME : time memiliki format jam-menit-detik
3. DATETIME : gabungan dari format date dan time
Ekspresi
Ekspresi digunakan untuk menghasilkan/menghitung nilai.
Misalnya : jumlah=harga-diskon
Ekspresi aritmatika antara lain :
1. + : tambah
2. – : kurang
3. / : bagi
4. * : kali
Fungsi bawaan
Fungsi adalah subprogram yang dapat menghasilkan suatu nilai apabila fungsi tersebut dipanggil. Fungsi Agregat adalah fungsi yang digunakan untuk melakukan summary, statistik yang dilakukan pada suatu tabel/query.
1. AVG(ekspresi) : digunakan untuk mencari nilai rata-rata dalam kolom dari tabel.
2. COUNT(x) : digunakan untuk menghitung jumlah baris dari sebuah kolom
dari tabel
3. MAX(ekspresi) : digunakan untuk mencari nilai yang paling besar dari suatu
kolom dari tabel
4. MIN(ekspresi) : digunakan untuk mencari nilai yang paling kecil dari suatu
kolom dari tabel
5. SUM(ekspresi) : digunakan untuk mengitung jumlah keseluruhan dari suatu
kolom dari tabel
FAKTOR-FAKTOR YANG BERPENGARUH TERHADAP KECEPATAN AKSES DATA
Faktor lain yang berpengaruh terhadap kecepatan akses data, tidak hanya terletak
pada optimasi perintah SQL, tapi terhadap hal-hal lain yang berpengaruh. Diantaranya adalah optimasi aplikasi dan penggunaan cluster dan index. Hal yang akan dibahas dalam optimasi query berikut ini tidak melibatkan penggunaan komponen yang ada dalam Arsitektur database engine, misal pada database Oracle kecepatan akses data dipengaruhi oleh penyesuaian pada shared pool, buffer cache, redo log buffer dan sistem operasi yang digunakan.
1. OPTIMASI APLIKASI
Dalam pembuatan aplikasi, yang perlu mendapat perhatian adalah apakah akses
terhadap data sudah efisien. Efisien dalam hal penggunaan obyek yang mendukung
kecepatan akses, seperti index atau cluster. Kemudian juga bagaimana cara database didesain.
Apakah desain database sudah melakukan normalisasi data secara tepat.
Kadangkala normalisasi sampai level yang kesekian, tidak menjamin suatu
desain yang efisien. Untuk membuat desain yang lebih tepat, kadang setelah melakukan normalisasi perlu dilakukan denormalisasi. Misalnya tabel yang hubungannya one-toone dan sering diakses bersama lebih baik disatukan dalam satu tabel.
2. CLUSTER DAN INDEX
Cluster adalah suatu segment yang menyimpan data dari tabel yang berbeda
dalam suatu struktur fisik disk yang berdekatan. Konfigurasi ini bermanfaat untuk
akses data dari beberapa tabel yang sering di-query. Penggunaan cluster secara tepat dilaksanakan setelah menganalisa tabel-tabel mana saja yang sering di-query secara bersamaan menggunaan perintah SQL join.
Jika aplikasi sering melakukan query dengan menggunakan suatu kolom yang
berada pada klausa WHERE, maka harus digunakan index yang melibatkan kolom
tersebut. Penggunaan index yang tepat bergantung pada jenis nilai yang terdapat dalam kolom yang akan diindex. Dalam RDBMS Oracle, index B-Tree digunakan untuk kolom yang mengandung nilai yang cukup bervariasi, sedangkan untuk nilai yang tidak memiliki variasi cukup banyak, lebih baik menggunakan index bitmap.
RINGKASAN
Ø Data yang tersimpan dalam jumlah yang sangat besar, Terdapat aturan system
informasi dalam organisasi, system basis data dilihat sebagai bagian system
informasi dalam aplikasi berskala besar.
Ø Untuk meningkatkan unjuk kerja tidak hanya desain logik saja yang diperhatikan
tapi juga struktur fisik penyimpanan data.
Ø Penggunaan Index pada database secara tepat, dapat digunakan untuk
meningkatkan kecepatan akses data.
Ø Informasi tentang jalur akses yang digunakan oleh database untuk melaksanakan
query dalam database Oracle dapat dengan menggunakan perintah explain plan.
Ø Selain optimasi perintah SQL, faktor lain yang berpengaruh terhadap kecepatan
akses data adalah optimasi aplikasi dan penggunaan cluster dan index.
Ø Pada sebuah database engine semisal pada database Oracle kecepatan akses data
dipengaruhi oleh beberapa komponen arsitektur pembentuknya seperti shared
pool, buffer cache, dan redo log buffer.
Ø Optimasi aplikasi tergantung pada efisiensi penggunaan obyek yang mendukung
kecepatan akses seperti index atau cluster, dan normalisasi data pada desain database.
Tidak ada komentar:
Posting Komentar