Melakukan partisi terhadap tabel di MySQL

Pendahuluan
Partisi, apa itu partisi, daku yakin semua orang faham apa itu partisi, yaitu pembagian/pemilahan. Partisi ini sangat bermanfaat jika tahu cara menggunakan. MySQL 5.x sudah mulai mendukung partisi ini. Salah satu fungsi partisi adalah mempercepat eksekusi query. Mengapa?

Ok, tak beri gambaran.
Misalnya ada data seperti ini:
tanggal | kode akun | nominal
--------------------------------
2000-01-01 | x001 | 100000
2001-02-05 | x002 | 200000
2000-03-01 | x001 | 102000
2010-01-04 | x001 | 300000
2010-02-03 | x002 | 400000
2010-01-02 | x001 | 150000
2010-02-03 | x002 | 600000
2010-03-02 | x001 | 120000
2010-01-02 | x001 | 300000
2010-02-03 | x002 | 440000

misalnya ada permintaan:
tolong jumlahkan transaksi bulan januari 2000!

Ok, gampang, tinggal telusuri saja data-data tersebut satu-satu, jika bulan transaksinya 2000-01, maka dijumlahkan, yang lainnya tidak dijumlahkan.

Alhasil, semua data dicek. Ya kalau hanya 10 data, kalau ada 2 juta baris data bagaimana (misalnya: dari tahun 2000-2010)? Capek deh!!

Oleh karena itu, partisi akan sangat terasa di sini. Bayangkan saja jika data-data tersebut dikelompokkan per tahun, kira-kira saja setiap tahun hanya akan ada 200 ribu baris data, yang dijumlahkan hanya 200 ribu baris data tersebut, tidak perlu menghitung seluruh 2 juta baris data yang ada.

Partisi juga ada batasannya, yaitu sekitar 1000+ partisi. Karena masa hidup kita jarang yang sampai 100 tahun (mungkin juga software yang kita buat 20 tahun lagi sudah diganti), maka buat saja sekitar 50 partisi (untuk masa 50 tahun).

Jadi data di atas menjadi:
tanggal | kode akun | nominal
--------------------------------
2000-01-01 | x001 | 100000
2000-03-01 | x001 | 102000

tanggal | kode akun | nominal
--------------------------------
2001-02-05 | x002 | 200000

tanggal | kode akun | nominal
--------------------------------
2010-01-04 | x001 | 300000
2010-02-03 | x002 | 400000
2010-01-02 | x001 | 150000
2010-02-03 | x002 | 600000
2010-03-02 | x001 | 120000
2010-01-02 | x001 | 300000
2010-02-03 | x002 | 440000

Lebih cepat, kan? Kita hanya perlu menghitung di partisi yang pertama, partisi yang kedua dan ketiga diabaikan (tidak ikut dihitung).

Akan lebih hebat lagi jika di sub partisi lagi per bulan. Splendid!

Rencana
Kita akan membuat 50 tahun partisi, dengan sub partisi sebanyak 12 bulan, sehingga menggunakan year(tanggal) untuk ekspresi partisinya dan month(tanggal) untuk ekspresi subpartisinya.

Partisi tabel di MySQL
Ada 2 jenis partisi yang akan digunakan di sini, yaitu "range partition" yang digunakan untuk partisi utama dan "hash partition" yang digunakan pada partisi kedua.

Range Partition adalah pemartisian berdasarkan range/jarak yang telah ditentukan
Hash Partition adalah pemartisian berdasarkan rumus N = MOD(expr, num)

PARTITION BY RANGE(YEAR(tanggal))
SUBPARTITION BY HASH(MONTH(tanggal))
SUBPARTITIONS 12
(
PARTITION p0 VALUES LESS THAN (2001),
PARTITION p1 VALUES LESS THAN (2002),
PARTITION p2 VALUES LESS THAN (2003),
PARTITION p3 VALUES LESS THAN (2004),
PARTITION p4 VALUES LESS THAN (2005),
PARTITION p5 VALUES LESS THAN (2006),
PARTITION p6 VALUES LESS THAN (2007),
PARTITION p7 VALUES LESS THAN (2008),
PARTITION p8 VALUES LESS THAN (2009),
PARTITION p9 VALUES LESS THAN (2010),
PARTITION p10 VALUES LESS THAN (2011),
PARTITION p11 VALUES LESS THAN (2012),
PARTITION p12 VALUES LESS THAN (2013),
PARTITION p13 VALUES LESS THAN (2014),
PARTITION p14 VALUES LESS THAN (2015),
PARTITION p15 VALUES LESS THAN (2016),
PARTITION p16 VALUES LESS THAN (2017),
PARTITION p17 VALUES LESS THAN (2018),
PARTITION p18 VALUES LESS THAN (2019),
PARTITION p19 VALUES LESS THAN (2020),
PARTITION p20 VALUES LESS THAN (2021),
PARTITION p21 VALUES LESS THAN (2022),
PARTITION p22 VALUES LESS THAN (2023),
PARTITION p23 VALUES LESS THAN (2024),
PARTITION p24 VALUES LESS THAN (2025),
PARTITION p25 VALUES LESS THAN (2026),
PARTITION p26 VALUES LESS THAN (2027),
PARTITION p27 VALUES LESS THAN (2028),
PARTITION p28 VALUES LESS THAN (2029),
PARTITION p29 VALUES LESS THAN (2030),
PARTITION p30 VALUES LESS THAN (2031),
PARTITION p31 VALUES LESS THAN (2032),
PARTITION p32 VALUES LESS THAN (2033),
PARTITION p33 VALUES LESS THAN (2034),
PARTITION p34 VALUES LESS THAN (2035),
PARTITION p35 VALUES LESS THAN (2036),
PARTITION p36 VALUES LESS THAN (2037),
PARTITION p37 VALUES LESS THAN (2038),
PARTITION p38 VALUES LESS THAN (2039),
PARTITION p39 VALUES LESS THAN (2040),
PARTITION p40 VALUES LESS THAN (2041),
PARTITION p41 VALUES LESS THAN (2042),
PARTITION p42 VALUES LESS THAN (2043),
PARTITION p43 VALUES LESS THAN (2044),
PARTITION p44 VALUES LESS THAN (2045),
PARTITION p45 VALUES LESS THAN (2046),
PARTITION p46 VALUES LESS THAN (2047),
PARTITION p47 VALUES LESS THAN (2048),
PARTITION p48 VALUES LESS THAN (2049),
PARTITION p49 VALUES LESS THAN (2050),
PARTITION p50 VALUES LESS THAN MAXVALUE
)

Misal:
Jika tanggal yang dimasukkan adalah "2010-03-01", maka masuk partisi yang mana?
Jawab:
1. untuk partisinya akan masuk p10, karena year("2010-03-01") bernilai 2010, sedangkan 2010 sendiri masuk less than (2011) atau kurang dari (2011)
2. untuk suppartisinya akan masuk subpartisi ke-3, dengan perhitungan
--- N = MOD(expr, num)
--- N = MOD(month(tanggal), 12)
--- N = MOD(month("2010-03-01"), 12)
--- N = MOD(3, 12)
--- N = 3

Kesimpulan
Kita tidak perlu melakukan partisi dengan cara yang salah, misalnya membuat 50 tabel untuk transaksi 50 tahun, misal: transaksi2000, transaksi2001, transaksi2002, dst. Cukup hanya 1 tabel transaksi dengan 50 partisi (dan bisa di-subpartisi lagi ke dalam 12 bulan), kita akan menghemat koding dan kita tidak perlu pusing memikirkan datanya mau dimasukkan ke mana, serahkan saja ke MySQL.