Ø 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
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
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
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
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.