Ø
OPTIMASI
QUERY
Optimasi
query adalah suatu proses untuk menganalisa query untuk menentukan sumber
sumber apa saja yang digunakan oleh
query tersebut dan apakah penggunaan dari sumber tersebut dapat dikurangi tanpa
merubah output. Optimasi query mencakup beberapa teknik seperti transformasi
query ke dalam bentuk logika yang sama, memilih jalan akses yang optimal dan
mengoptimumkan penyimpanan data.
Ada 3 aspek
dasar yang mempengaruhi optimasi query, yaitu :
·
Search space
·
Cost model
·
Search strategy
Tujuan dari
optimasi query :
·
Untuk meminimumkan
waktu proses
·
Untuk waktu respon,
meminimumkan I/O dan meminimumkan penggunaan memory
·
Menemukan jalan akses
yang termurah untuk meminimumkan total waktu pada saat proses sebuah query.
Ø
Optimasi
perintah SQL
Cara
mengoptimalkan perintah SQL tidak cukup dengan mendesain aplikasinya saja,
harus didukung dengan peritah SQL yang digunakan dalam 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 akses data.
Misal digunakan index yang melibatkan
tiga buah kolom yang mengurutkan
kolom
menurut kota, propinsi dan kode pos dari tabel karyawan, sebagai berikut :
CREATE
INDEX idx_kota_prop_kodepos
ON
karyawan(kota, propinsi, kode_pos)
TABLESPACE
INDX;
Kemudian
user melakukan query sebagai berikut :
SELECT
* FROM karyawan WHERE propinsi=’Jawa Barat’;
Pada
saat melakukan 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 karyawan
WHERE
id BETWEEN 1010 AND 2010;
Query
ini akan melakukan “scan” terhadap sedikit data block jika tabel karyawan
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 karyawan_urut
AS
SELECT * FROM karyawan
ORDER
BY id;
Pada
SQL diatas, tabel karyawan_urut berisi data yang sama dengan tabel karyawan
hanya datanya
terurut berdasarkan kolom id.
Ø Informasi Jalur Akses Query
SQL pertama kali digunakan pada sebuah proyek riset laboratorium riset
San Jose, IBM. Dan pada saat ini SQL telah banyak digunakan pada berbagai
platform, baik dari mikrokomputer maupun mainframe. SQL dapat diletakkan pada
bahasa pemrogrman/mesin yaitu C dan Delphi ataupun digunakan secara
mandiri/berdiri sendiri. Saat ini SQL telah menjadi bagian dari sejumlah DBMS,
antara lain : Sybase, MySQL, Oracle, dan Informix.
Elemen dari SQL yang paling dasar
antara lain pernyataan, nama, tipe data, ekspresi, konstanta dan fungsi bawaan.
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 digunakan sebagai identitas, yaitu identitas bagi objek pada DBMS. Misal :
tabel, kolom dan pengguna.
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 digunakan untuk menghasilkan/menghitung nilai.
Misalnya : jumlah=harga-diskon
Ekspresi aritmatika antara lain :
1. + : tambah
2. – : kurang
3. / : bagi
4. * : kali
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 system operasi yang digunakan.
a. Optimasi
Aplikasi
Dalam pembuatan aplikasi, yang
perlu mendapat perhatian adalah apakah aksesterhadap 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.
b. 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.
