Partition Table di Database

Apa itu partition table? Kenapa, dan contoh penggunaannya di mysql?

Partition table adalah teknik membagi sebuah tabel menjadi beberapa bagian (partition) yang tersusun secara horizontal, sehingga dapat mengurangi waktu akses dan meningkatkan performa query. Setiap partition dalam sebuah tabel dapat diatur sesuai kebutuhan, seperti menentukan kriteria pencarian atau merujuk ke lokasi data tertentu.

Contoh penggunaan partition table di MySQL adalah untuk menyimpan data yang banyak dalam sebuah tabel, misalnya tabel yang berisi data transaksi yang terus bertambah setiap harinya. Dengan membuat partition table, data dapat dibagi menjadi beberapa bagian berdasarkan waktu, seperti harian, mingguan atau bulanan. Sehingga ketika melakukan query, hanya data yang memenuhi kriteria tertentu diambil dari partisi yang terkait dan tidak perlu memindai seluruh tabel, sehingga menghasilkan kinerja query yang lebih cepat dan efisien.

General sintaks pembuatan sebuah partition tabel:

CREATE TABLE nama_tabel (
 kolom_satu jenis_data,
 kolom_dua jenis_data,
 …
) ENGINE=InnoDB PARTITION BY RANGE(kolom_yang_di_partition) (
 PARTITION p0 VALUES LESS THAN (nilai_batas_1),
 PARTITION p1 VALUES LESS THAN (nilai_batas_2),
 ….
);

Step by Step guidance untuk pembuatan partition table

1. Buatlah sebuah tabel dengan contoh general sintaks diatas

2. Sesuaikan `nama_tabel` dengan nama tabel yang ingin Anda buat, `kolom_satu`, `kolom_dua`, dan seterusnya dengan nama kolom yang ingin Anda tambahkan, dan `jenis_data` dengan jenis data yang sesuai dengan kolom tersebut (seperti `INT`, `VARCHAR`, atau `DATE`).

3. Atur `ENGINE` ke `InnoDB` (atau engine lain yang mendukung partisi).

4. Tentukan `PARTITION BY RANGE(kolom_yang_di_partition)` untuk menentukan bahwa tabel akan dipartisi berdasarkan kolom tertentu.

5. Tentukan nilai batas untuk setiap partisi. Dalam sintaks di atas, nilai batas diberikan untuk setiap partisi sebagai argumen untuk `VALUES LESS THAN`. Misalnya, `PARTITION p0 VALUES LESS THAN (nilai_batas_1)` berarti bahwa partisi tersebut akan berisi nilai-nilai yang kurang dari `nilai_batas_1`.

Contoh Implementasi Partition Table

CREATE TABLE transaksi (
   id INT NOT NULL AUTO_INCREMENT,
   tanggal_transaksi DATE,
   jumlah DECIMAL(10,2),
   PRIMARY KEY (id)
) ENGINE=InnoDB PARTITION BY RANGE(MONTH(tanggal_transaksi)) (
   PARTITION p0 VALUES LESS THAN (2),
   PARTITION p1 VALUES LESS THAN (3),
   PARTITION p2 VALUES LESS THAN (4),
   PARTITION p3 VALUES LESS THAN (5),
   PARTITION p4 VALUES LESS THAN (6),
   PARTITION p5 VALUES LESS THAN (7),
   PARTITION p6 VALUES LESS THAN (8),
   PARTITION p7 VALUES LESS THAN (9),
   PARTITION p8 VALUES LESS THAN (10),
   PARTITION p9 VALUES LESS THAN (11),
   PARTITION p10 VALUES LESS THAN (12),
   PARTITION p11 VALUES LESS THAN (13)
);

Dalam contoh di atas, `PARTITION BY RANGE(MONTH(tanggal_transaksi))` menentukan bahwa tabel akan dipartisi berdasarkan bulan dari tanggal transaksi. Batas partisi (yaitu `VALUES LESS THAN`) ditentukan dari 2 (Februari) hingga 13 (Terakhir).

Kenapa p0 values di mulai kurang dari dua?

Dalam sintaks pembuatan tabel yang dipartisi tersebut kita menggunakan bulan, dimana p0 dimulai dari 2 karena partisi p0 akan menyimpan semua baris data yang memiliki nilai kolom tanggal_transaksi kurang dari 2. Dalam hal ini, partisi p0 akan menyimpan semua baris data yang memiliki nilai bulan pada kolom tanggal_transaksi kurang dari 2, yaitu bulan Januari. Demikian pula, partisi p1 akan menyimpan semua baris data yang memiliki nilai bulan pada kolom tanggal_transaksi kurang dari 3, yaitu bulan Februari.

Setelah 5 bulan berjalan?

Setelah 5 bulan berjalan, data di tabel transaksi akan dipartisi ke dalam partisi yang berbeda berdasarkan nilai bulan pada kolom tanggal_transaksi. Misalnya, jika kita memiliki data transaksi sebagai berikut:

INSERT INTO transaksi (tanggal_transaksi, jumlah) VALUES
('2023-01-01', 100),
('2023-02-15', 200),
('2023-03-20', 300),
('2023-04-10', 400),
('2023-05-05', 500);

Maka data tersebut akan dipartisi ke dalam partisi yang berbeda sebagai berikut:

• Baris data dengan tanggal_transaksi ‘2023-01-01’ akan disimpan di partisi p0 karena nilai bulan pada kolom tanggal_transaksi kurang dari 2.
• Baris data dengan tanggal_transaksi ‘2023-02-15’ akan disimpan di partisi p1 karena nilai bulan pada kolom tanggal_transaksi kurang dari 3.
• Baris data dengan tanggal_transaksi ‘2023-03-20’ akan disimpan di partisi p2 karena nilai bulan pada kolom tanggal_transaksi kurang dari 4.
• Baris data dengan tanggal_transaksi ‘2023-04-10’ akan disimpan di partisi p3 karena nilai bulan pada kolom tanggal_transaksi kurang dari 5.
• Baris data dengan tanggal_transaksi ‘2023-05-05’ akan disimpan di partisi p4 karena nilai bulan pada kolom tanggal_transaksi kurang dari 6.

Anda dapat mengecek isi setiap partisi dengan menjalankan perintah SELECT dengan klausa PARTITION (nama_partisi) seperti ini:

SELECT * FROM transaksi PARTITION (p0);

Perintah di atas akan menampilkan semua baris data yang disimpan di partisi p0.

Bonus

Untuk melakukan pengecekan apakah table memiliki partisi table atau tidak bisa dengan cara berikut

SHOW CREATE TABLE transaksi;

atau dapat juga dilihat dari informasi schema partition, apakah memiliki partisi atau tidak.

SELECT * FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'transaksi';

Disclaimer

Tulisan ini hanya memberikan contoh dan informasi, contoh di atas mungkin tidak sesuai dengan apa yang anda butuhkan namun bisa memberikan gambaran apa itu partition table, kenapa menggunakan partition table, bagaimana implementasinya, cara select querynya, dan cara check apakah sebuah table memiliki partition table dalam schema nya atau tidak.

References

MySQL :: MySQL 8.0 Reference Manual :: 24.2.1 RANGE Partitioning