MATERI 5 - OPTIMASI DATABASE , DAN PARTISI TABEL

 Saya, Marinda Cahya P, dari Kelas 2B dengan nomor absen 16, menulis materi ini sebagai rangkuman dari Praktikum Instalasi dan Konfigurasi Server Database yang dilaksanakan pada tanggal 12 Maret 2025. Topik yang dibahas dalam praktikum ini adalah Optimasi Database dan Partisi Tabel Rangkuman ini disusun sebagai salah satu syarat untuk memenuhi tugas praktikum Mata Kuliah Sistem Manajemen Basis Data. Jika terdapat kesalahan dalam penulisan atau gambar yang kurang jelas, saya mohon maaf.

OPTIMASI DATABASE DAN PARTISI TABEL

Latar Belakang

Seiring dengan bertambahnya volume data dalam sistem basis data, performa akses dan pengolahan data menjadi tantangan besar. Untuk mengatasi masalah ini, beberapa teknik optimasi database diperlukan, seperti indexing, query optimization, caching, denormalisasi, dan partisi tabel.

Salah satu metode yang sangat efektif untuk menangani tabel dengan jutaan hingga miliaran baris adalah partisi tabel. Teknik ini membagi tabel besar menjadi beberapa bagian yang lebih kecil berdasarkan kriteria tertentu, sehingga mempercepat pencarian dan pengolahan data.

Permasalahan yang diangkat

Adapun Permasalahan yang diangkat dalam tugas ini yakni :

1. Bagaimana cara mengoptimalkan performa database dalam menangani jumlah data yang besar?
2. Bagaimana cara menerapkan partisi tabel untuk mempercepat akses data?
3. Bagaimana pengaruh partisi tabel terhadap performa query?
4. Bagaimana perbandingan performa antara tabel tanpa partisi dan tabel dengan partisi?

Skenario aktivitas

Dalam tugas ini, dilakukan beberapa tahapan:

1. Untuk memahami konsep partisi tabel dan penerapannya dalam optimasi database, dilakukan beberapa tahapan:
2. Pembuatan tabel dengan partisi berdasarkan tahun transaksi.
3. Pemasukan data ke dalam tabel yang telah dipartisi.
4. Pengujian performa query sebelum dan sesudah menggunakan partisi.
5. Studi kasus penerapan partisi tabel pada database minimarket.
6. Menganalisis perbandingan performa antara tabel dengan partisi dan tanpa partisi.

Pembahasan (Latihan Soal)

1. Jalankan semua contoh script diatas sampai anda benar-benar memahami konsep  partisi, cara kerja partisi, manfaat partisi tabel. 

• Membuat tabel transactions dengan partisi berdasarkan tahun transaksiJangan lupa, tempat menyimpan file dataset.

CREATE TABLE transactions (

    id INT NOT NULL AUTO_INCREMENT,

    transaction_date DATE NOT NULL,

    amount DECIMAL(10,2) NOT NULL,

    customer_id INT NOT NULL,

    PRIMARY KEY (id, transaction_date)

)

PARTITION BY RANGE (YEAR(transaction_date)) (

    PARTITION p0 VALUES LESS THAN (2020),

    PARTITION p1 VALUES LESS THAN (2021),

    PARTITION p2 VALUES LESS THAN (2022),

    PARTITION p3 VALUES LESS THAN (2023),

    PARTITION p4 VALUES LESS THAN (2024)

);

• Menambahkan data ke dalam tabel transactions

INSERT INTO transactions (transaction_date, amount, customer_id) VALUES

('2019-06-15', 150000, 101),

('2020-07-20', 250000, 102),

('2021-08-25', 500000, 103),

('2022-09-10', 350000, 104),

('2023-10-05', 400000, 105);

• Mengecek jumlah record per partisi

SELECT TABLE_NAME, PARTITION_NAME, TABLE_ROWS

FROM INFORMATION_SCHEMA.PARTITIONS

WHERE TABLE_NAME = 'transactions';

• Menjalankan query yang di optimasi dengan partisi

SELECT * FROM transactions WHERE transaction_date BETWEEN '2023-01-01' AND '2023-12-31';

2. Studi Kasus mengacu pada database minimarket

3. Pada tabel tr_penjualan, lakukan scenario sebagai berikut: 

a.  Redesaian tabel tr_penjualan, tambahkan partisi pada tabel tersebut. Sehingga  ada tabel baru tr_penjualan_partisi

CREATE TABLE tr_penjualan_partisi (

tgl_transaksi DATETIME DEFAULT NULL,

kode_cabang VARCHAR(10) DEFAULT NULL,

kode_kasir VARCHAR(10) DEFAULT NULL,

kode_item VARCHAR(7) DEFAULT NULL,

kode_produk VARCHAR(12) DEFAULT NULL,

jumlah_pembelian INT(11) DEFAULT NULL,

nama_kasir VARCHAR(40) DEFAULT NULL,

harga INT(6) DEFAULT NULL

)

PARTITION BY RANGE (YEAR(tgl_transaksi)) (

PARTITION p0 VALUES LESS THAN (2008),

PARTITION p1 VALUES LESS THAN (2009),

PARTITION p2 VALUES LESS THAN (2010),

PARTITION p3 VALUES LESS THAN (2011),

PARTITION p4 VALUES LESS THAN (2012),

PARTITION p5 VALUES LESS THAN (2013),

PARTITION p6 VALUES LESS THAN (2014),

PARTITION p7 VALUES LESS THAN (2015)

);

Pada Gambar diatas merupakan query dan hasil (berhasil membuat tabel) untuk membuat tabel tr_penjualan_partisi dengan partisi dan memiliki artribut yang sudah di tentukan. 

b. isikan tabel tr_penjualan_partisi

c. Lakukan penambahan data dummy di tr_penjualan_partisi dengan susunan

• Tahun 2008:

Dibawah ini query untuk menambahkan atribut nama_kasir, dan harga pada tabel tr_penjualan

ALTER TABLE tr_penjualan

ADD COLUMN nama_kasir VARCHAR(40) DEFAULT NULL,

ADD COLUMN harga INT(6) DEFAULT NULL;

Hasil:

Dan setelah di tambah kolom tersebut maka akan berhasil

INSERT INTO tr_penjualan_partisi (tgl_transaksi, kode_cabang, kode_kasir, kode_item,

kode_produk, jumlah_pembelian, nama_kasir, harga)

SELECT tgl_transaksi, kode_cabang, kode_kasir, kode_item, kode_produk, jumlah_pembelian,

nama_kasir, harga FROM tr_penjualan WHERE YEAR(tgl_transaksi) = 2008;

• Tahun 2009:

Script Insert untuk tahun 2009

INSERT INTO tr_penjualan_partisi (tgl_transaksi, kode_cabang, kode_kasir, kode_item,

kode_produk, jumlah_pembelian, nama_kasir, harga) (

SELECT DATE_ADD(tgl_transaksi, INTERVAL 1 YEAR), kode_cabang, kode_kasir, kode_item,

kode_produk, jumlah_pembelian, nama_kasir, harga

FROM tr_penjualan

)

• Tahun 2010:

INSERT INTO tr_penjualan_partisi (tgl_transaksi, kode_cabang, kode_kasir, kode_item,

kode_produk, jumlah_pembelian, nama_kasir, harga)

SELECT DATE_ADD(tgl_transaksi, INTERVAL 1 YEAR), kode_cabang, kode_kasir, kode_item,

kode_produk, jumlah_pembelian, nama_kasir, harga FROM tr_penjualan WHERE YEAR(tgl_transaksi) = 2010;

• Tahun 2011:

INSERT INTO tr_penjualan_partisi (tgl_transaksi, kode_cabang, kode_kasir, kode_item,

kode_produk, jumlah_pembelian, nama_kasir, harga)

SELECT DATE_ADD(tgl_transaksi, INTERVAL 1 YEAR), kode_cabang, kode_kasir, kode_item, kode_produk, jumlah_pembelian, nama_kasir, harga FROM tr_penjualan WHERE YEAR(tgl_transaksi) = 2011;

• Tahun 2012:

-- Mengisi data untuk tahun 2012 dengan menyesuaikan tahun

INSERT INTO tr_penjualan_partisi (tgl_transaksi, kode_cabang, kode_kasir, kode_item,

kode_produk, jumlah_pembelian, nama_kasir, harga)

SELECT DATE_ADD(tgl_transaksi, INTERVAL 1 YEAR), kode_cabang, kode_kasir, kode_item,

kode_produk, jumlah_pembelian, nama_kasir, harga FROM tr_penjualan WHERE YEAR(tgl_transaksi) = 2012;

• Tahun 2013:

-- Mengisi data untuk tahun 2013 dengan menyesuaikan tahun

INSERT INTO tr_penjualan_partisi (tgl_transaksi, kode_cabang, kode_kasir, kode_item,

kode_produk, jumlah_pembelian, nama_kasir, harga)

SELECT DATE_ADD(tgl_transaksi, INTERVAL 1 YEAR), kode_cabang, kode_kasir, kode_item, kode_produk, jumlah_pembelian, nama_kasir, harga FROM tr_penjualan WHERE YEAR(tgl_transaksi) = 2013; 

• Tahun 2014:

-- Mengisi data untuk tahun 2014 dengan menyesuaikan tahun

INSERT INTO tr_penjualan_partisi (tgl_transaksi, kode_cabang, kode_kasir, kode_item,

kode_produk, jumlah_pembelian, nama_kasir, harga)

SELECT DATE_ADD(tgl_transaksi, INTERVAL 1 YEAR), kode_cabang, kode_kasir, kode_item,

kode_produk, jumlah_pembelian, nama_kasir, harga FROM tr_penjualan WHERE YEAR(tgl_transaksi) = 2014;

d. Pengisian tabel tr_penjualan_partisi disesuai dengan kapasitas LAPTOP masing masing. Makin banyak data, makin terlihat efek dari partisi table. 

b. Setelah diisikan, maka susunan record sesuai partisi akan ditampilkan seperti  berikut: 

SELECT TABLE_NAME, PARTITION_NAME, TABLE_ROWS

FROM INFORMATION_SCHEMA.PARTITIONS

WHERE TABLE_NAME = 'tr_penjualan_partisi';

4. Buat tabel tr_penjualan_raw yang isinya sama persis dengan tabel tr_penjualan_partisi.  Yang membedakan hanya struktur tabel nya saja. 

• Tr_penjualan_raw = struktur biasa

-- Membuat tabel tr_penjualan_raw dengan struktur yang sama tetapi tanpa partisi

CREATE TABLE tr_penjualan_raw (

  tgl_transaksi DATETIME DEFAULT NULL,

  kode_cabang VARCHAR(10) DEFAULT NULL,

  kode_kasir VARCHAR(10) DEFAULT NULL,

  kode_item VARCHAR(7) DEFAULT NULL,

  kode_produk VARCHAR(12) DEFAULT NULL,

  jumlah_pembelian INT(11) DEFAULT NULL,

  nama_kasir VARCHAR(40) DEFAULT NULL,

  harga INT(6) DEFAULT NULL

);

• Tr_penjualan_partisi = struktur tabel ter partisi

CREATE TABLE tr_penjualan_partisi (

  tgl_transaksi DATETIME DEFAULT NULL,

  kode_cabang VARCHAR(10) DEFAULT NULL,

  kode_kasir VARCHAR(10) DEFAULT NULL,

  kode_item VARCHAR(7) DEFAULT NULL,

  kode_produk VARCHAR(12) DEFAULT NULL,

  jumlah_pembelian INT(11) DEFAULT NULL,

  nama_kasir VARCHAR(40) DEFAULT NULL,

  harga INT(6) DEFAULT NULL

)

5. Pengujian Tabel 

Jalankan query berikut dengan perulangan 10x. lakukan pencatatan waktu running  setiap query. Dan ambil rata-ratanya. 

            SELECT * FROM tr_penjualan_raw

            WHERE tgl_transaksi > DATE('2010-08-01')

AND tgl_transaksi < DATE('2011-07-31')

            SELECT * FROM tr_penjualan_partisi

            WHERE tgl_transaksi > DATE('2010-08-01')

AND tgl_transaksi < DATE('2011-07-31')

6. Lakukan pengujian juga utk kolom lain. Dengan cara menjalankan query yang where  clause nya bukan tgl_transaksi. Catat waktunya. Jelaskan Hasil yang anda peroleh??

-- Pengujian pada tabel tr_penjualan_raw

SELECT * FROM tr_penjualan_raw

WHERE kode_cabang = 'CB001';

-- Pengujian pada tabel dengan tr_penjualan_partisi

SELECT * FROM tr_penjualan_partisi

WHERE kode_cabang = 'CB001';

1.     Kesimpulan dari tabel percobaan yang dilakukan.

Query dengan tgl_transaksi (kolom partisi) lebih cepat karena MySQL hanya membaca partisi yang relevan.

• Rata-rata waktu eksekusi: 0.0049 detik (sangat cepat). Query dengan kolom lain (kode_cabang, dll.) tidak mendapat keuntungan dari partisi, sehingga waktu eksekusinya hampir sama dengan tabel tanpa partisi.
• Rata-rata waktu eksekusi: 1.14 detik (lebih lambat). Partisi sangat efektif jika sering melakukan pencarian berdasarkan kolom yang digunakan untuk partisi (misalnya tgl_transaksi).

Jika sering mencari berdasarkan kolom lain, gunakan indeks tambahan pada kolom tersebut untuk meningkatkan performa.

Kesimpulan

• Partisi tabel meningkatkan performa database dengan mengurangi jumlah data yang harus diproses dalam query.
• Query dengan kolom partisi jauh lebih cepat, karena MySQL hanya membaca data dalam partisi yang relevan.
• Partisi mempermudah manajemen data, seperti penghapusan data lama tanpa mempengaruhi data baru.
• Namun, partisi tidak selalu efektif jika query lebih sering menggunakan kolom yang tidak dipartisi. Dalam kasus ini, indeks tambahan lebih disarankan.
• Dengan menerapkan partisi tabel yang tepat, database dapat menangani data dalam skala besar dengan lebih efisien, mempercepat eksekusi query, serta mengoptimalkan kinerja sistem secara keseluruhan.

Referensi

MySQL Performance Optimization