MySQL Partition Table

Secara definisi partisi table adalah memecah tabel menjadi beberapa bagian/segmen, tujuan dari menggunakan partisi adalah untuk mempercepat proses query.

Misalkan kita memilik data sebanyak 10 juta record, bila pada table konvensional maka data sebanyak itu akan di simpan dalam satu segmen, tetapi bila menggunakan partisi maka data sebanyak itu akan di pecah-pecah ke banyak partisi berdasarkan sebuah kondisi, misalkan berdasarkan tanggal, maka ketika melakukan query hanya men-scan segmen dimana data itu berada, tidak 10 juta record itu di scan, sehingga proses query menjadilebih cepat.

Feature partisi tabel ini ada hadir dari MySQL versi 5.1, sedangkan MySQL yg gw pake saat eksperimen adalah MySQL versi 5.1.31 dengan sistem operasi yang gw pake adalah Ubuntu 9.04 Jaunty Jackalope


Tapi sebelum-nya melakukan partisi tabel cek lebih dahulu apa databasenya suport partisi atau tidak, caranya seperti di bawah ini

setelah masuk ke console MySQL ketikan perintah dibawah ini:

SHOW VARIABLES LIKE '%partition%';

+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| have_partitioning | YES   +
+-------------------+-------+
1 row in set (0.06 sec)

Partisi tabel terdiri dari beberapa tipe, antara lain

1. Partisi Range, adalah membuat partisi berdasarkan sebuah range, misalkan berdasarkan tanggal, sebuah nilai, contoh nya seperti di bawah ini

CREATE TABLE tbl_penjualan (
id INT NOT NULL,
nama VARCHAR(30),
tgl_transaksi DATE NOT NULL DEFAULT '1970-01-01'
)
PARTITION BY RANGE ( YEAR(tgl_transaksi) ) (
PARTITION p0 VALUES LESS THAN (1990),
PARTITION p1 VALUES LESS THAN (1995),
PARTITION p2 VALUES LESS THAN (2000),
PARTITION p3 VALUES LESS THAN MAXVALUE
);

contoh di atas membuat partisi berdasarkan tahun transaksi, jadi transaksi yg dari tahun 1990 akan masuk ke partisi p0, kemudian dari tahun 1995 akan masuk ke partisi p1, kemudian yang dari tahun 2000 akan masuk ke partisi p2 dan selebihnya akan masuk ke partisi p3.

contoh lengkapnya bisa diliat di: http://dev.mysql.com/doc/refman/5.1/en/partitioning-range.html

2. Partisi List, adalah membuat partisi berdasarkan kondisi dari sebuah nilai yang nilai nya cocok dengan yang sudah di tentukan, contohnya seperti dibawah ini

CREATE TABLE tbl_pegawai (
kode_pegawai  VARCHAR(30),
kode_golongan INT,
nama_lengkap  VARCHAR(255)
)
PARTITION BY LIST(kode_golongan) (
PARTITION p0 VALUES IN (1, 2, 3),
PARTITION p1 VALUES IN (4, 5, 6)
);

contoh diatas membuat partisi berdasarkan golongan pegawai, jadi pegawai dengan golongan 1,2 dan 3 akan masuk ke partisi p0 sedangkan karyawan dengangolongan 4,5 dan 6 akan masuk ke partisi p1

contoh lengkapnya bisa diliat di http://dev.mysql.com/doc/refman/5.1/en/partitioning-list.html

3. Partisi Hash, adalah membuat partisi berdasarkan sebuah kolom yg sudah ditentukan dengan pembagian segmen nya diatur oleh MySQL, contohnya seperti dibawah ini

CREATE TABLE tbl_pegawai (
kode_pegawai  VARCHAR(30),
kode_golongan INT,
nama_lengkap  VARCHAR(255)
)
PARTITION BY HASH( kode_pegawai )
PARTITIONS 4;

contoh diatas membuat partisi berdasarkan kode pegawai, jadi data pegawai akan di pecah menjadi empat partisi, dan yg mengatur sebuah kode pegawai masuk ke partisi yang mana langsung di atur otomatis oleh MySQL

contoh lengkapnya bisa diliat di http://dev.mysql.com/doc/refman/5.1/en/partitioning-hash.html

4. Partisi Key, adalah membuat partisi berdasarkan sebuah key dari tabel, contoh nya seperti dibawah ini

CREATE TABLE tbl_nama (
id INT NOT NULL PRIMARY KEY,
nama_lengkap VARCHAR(255)
)
PARTITION BY KEY()
PARTITIONS 2;

Contoh diatas membuat partisi berdasarkan primary key yang bernama id, jadi data dari tbl_nama akan di pecah menjadi 2 partisi, dan yang mengatur sebuah data masuk
kepartisi yang mana, diatur otomatis oleh MySQL

contoh lengkapnya bisa diliat di http://dev.mysql.com/doc/refman/5.1/en/partitioning-key.html

Berikut ini gw akan membuat sebuah contoh dan men-share hasil banchmark gw yang bikin secara *ecek-ecek* aja..... :D

misalkan saya mempunyai tabel seperti dibawah ini..

CREATE TABLE anggota (
username VARCHAR(16) NOT NULL,
namalengkap VARCHAR(25) NOT NULL,
tanggal DATE NOT NULL,
PRIMARY KEY(username)
)
PARTITION BY KEY(username)
PARTITIONS 6;

dari table diatas kita ingin melihat apakah tabel anggota mempunyai 6 partisi, dan sebuah data berada di partisi yang mana...

1. untuk melihat partition dari tabel, bisa dengan cara di bawah ini

explain partitions select * from anggota;
+----+-------------+---------+-------------------+------+---------------+------+---------+------+-------+-------+
| id | select_type | table   | partitions        | type | possible_keys | key  | key_len | ref  | rows  | Extra |
+----+-------------+---------+-------------------+------+---------------+------+---------+------+-------+-------+
|  1 | SIMPLE      | anggota | p3,p4,p5 | ALL  | NULL          | NULL | NULL    | NULL | 99570 |       |
+----+-------------+---------+-------------------+------+---------------+------+---------+------+-------+-------+
1 row in set (0.00 sec)

2. untuk melihat sebuah data berada di partisi yang mana, bisa dengan cara dibawah ini

explain partitions select * from anggota where username='50000' ;
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table   | partitions| type  | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | anggota |p4        | const | PRIMARY       | PRIMARY | 18      | const |    1 |       |
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+-------+
1 row in set (0.00 sec)

Selanjutnya saya akan membuat benchmark antara tabel yang tidak di partisi, dengan tabel yang di partisi dengan KEY, dan tabel yang di partisi dengan RANGE, di bawah ini adalah struktur tabel nya, jumlah data untuk ekperimen ini sebanyak 100.000 record.

1. tabel tanpa partisi

CREATE TABLE anggota 
username INT NOT NULL,
namalengkap VARCHAR(25) NOT NULL,
tanggal DATE NOT NULL,
PRIMARY KEY(username)

2. tabel dengan partisi KEY

CREATE TABLE anggota2 (
username INT NOT NULL,
namalengkap VARCHAR(25) NOT NULL,
tanggal DATE NOT NULL,
PRIMARY KEY(username)
)
PARTITION BY KEY(username)
PARTITIONS 6;

3. partisi dengan partisi RANGE

CREATE TABLE anggota3 (
username INT NOT NULL,
namalengkap VARCHAR(25) NOT NULL,
tanggal DATE NOT NULL,
PRIMARY KEY(username)
)
PARTITION BY RANGE(username)
(
PARTITION P0 VALUES LESS THAN (20000),
PARTITION P1 VALUES LESS THAN (40000),
PARTITION P2 VALUES LESS THAN (60000),
PARTITION P3 VALUES LESS THAN (80000),
PARTITION P4 VALUES LESS THAN (100001)
)

pengukurannya dengan query sederhana saja dan yang menjadi ukuran nya adalah waktu yang di butuhkan dalam eksekusi query tersebut, dibawah ini adalah hasilnya:

1. yg tidak menggunakan partisi

Select count(*) from anggota where username > 25000 and username < 85000
+----------+
| count(*) |
+----------+
|    59999 |
+----------+
1 row in set (0.15 sec)

2. yg menggunakan partisi KEY

Select count(*) from anggota2 where username > 25000 and username < 85000 
+----------+
| count(*) |
+----------+
|    59999 |
+----------+
1 row in set (0.20 sec)

3. yg menggunakan partisi RANGE

Select count(*) from anggota3 where username > 25000 and username < 85000
+----------+
| count(*) |
+----------+
|    59999 |
+----------+
1 row in set (0.07 sec)

Ternyata yang terlama adalah tabel yang menggunkan partisi KEY (0.20 sec) dan yang tercepat adalah tabel yang menggunakan partisi RANGE (0.07).

Jadi silakan simpulkan sendiri saja....
dan mana yang akan digunakan/dipakai tergantung kasus yang di hadapinya....
*ngak ada yg lebih baik dan juga ngak ada yg salah*