Apa itu Materialized View?
Materialzied View adalah Hasil fisik / table dari sekumpulan table yang dilakukan Query JOIN, berbeda dengan View dimana Hasil dari Query JOIN. Materialized View memiliki keuntungan dari segi normalisasi Performa database yang cepat, secepat kita GET data Table, sedangkan View untuk mengambil 1 Data dari sekumpulan Query JOIN sangat banyak dan dengan data yang sangat Banyak dari Table akan memakan Waktu. Permasalahan Utama kita kenapa menggunakan Material View adalah GET data dengan JOIN table yang sangat banyak.
Materialized View akan berfungsi dan dibutuhkan pada saat system Anda berjalan dan sudah banyak data yg berjalan serta system membutuhkan data yang Real Time, Apabila optimasi ini belum diterapkan akan lebih bijaknya Anda pertimbangkan untuk menggunakannya.
Beberapa Database Lain sudah support untuk Materialized View diantaranya:
- Oracle (Resource)
- MongoDB (Resource)
- Cassandra (Resource)
- PostgreSql (Resource)
- SQL Server (Biasa disebut Indexed View) (Resource)
Di artikel Ini kita akan membahas Materialized View di MYSQL, benar MYSQL tidak ada dalam list Database yang support untuk Materialized View, namun disini saya akan mencoba memberikan beberapa Teknik yang lumayan mudah untuk diterapkan Apabila System Anda menggunakan Database MYSQL. Berikut Gambaran dasar Materialized View secara garis Besar:
Berdasarkan Gambaran Diatas bisa akita lihat Hasil query dari beberapa table akan menghasilkan single table. Bagaimana caranya?
Berikut kita masuk ke topik utama yaitu Teknik Materialized View di Database MYSQL.
Sebagai Contoh disini saya membuat database untuk penjualan Rumah Makanan yang sederhana:
1. Table user
CREATE TABLE `user` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(50) NULL DEFAULT NULL COLLATE ‘utf8mb4_general_ci’,
`phone` VARCHAR(50) NULL DEFAULT NULL COLLATE ‘utf8mb4_general_ci’,
`role_id` INT(11) NULL DEFAULT NULL,
`status` TINYINT(4) NULL DEFAULT ‘1’,
`created_at` DATETIME NOT NULL DEFAULT current_timestamp(),
`updated_at` DATETIME NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
PRIMARY KEY (`id`) USING BTREE,
INDEX `role_id` (`role_id`) USING BTREE
)
COLLATE=‘utf8mb4_general_ci’
ENGINE=InnoDB
AUTO_INCREMENT=2;
2. Table role
CREATE TABLE `role` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(50) NULL DEFAULT NULL COLLATE ‘utf8mb4_general_ci’,
`created_at` DATETIME NOT NULL DEFAULT current_timestamp(),
`updated_at` DATETIME NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
PRIMARY KEY (`id`) USING BTREE
)
COLLATE=‘utf8mb4_general_ci’
ENGINE=InnoDB
AUTO_INCREMENT=4;
3. Table menu (menu makanan)
CREATE TABLE `menu` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(50) NULL DEFAULT NULL COLLATE ‘utf8mb4_general_ci’,
`price` DOUBLE(22,0) NULL DEFAULT NULL,
`status` TINYINT(4) NULL DEFAULT NULL,
`created_at` DATETIME NOT NULL DEFAULT current_timestamp(),
`updated_at` DATETIME NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
PRIMARY KEY (`id`) USING BTREE
)
COLLATE=‘utf8mb4_general_ci’
ENGINE=InnoDB
AUTO_INCREMENT=6;
4. Table Invoice
CREATE TABLE `invoice` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`user_id` INT(11) NULL DEFAULT NULL,
`created_at` DATETIME NOT NULL DEFAULT current_timestamp(),
`updated_at` DATETIME NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
PRIMARY KEY (`id`) USING BTREE,
INDEX `user_id` (`user_id`) USING BTREE
)
COLLATE=‘utf8mb4_general_ci’
ENGINE=InnoDB
AUTO_INCREMENT=2;
5. Table invoice_detail
CREATE TABLE `invoice_detail` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`invoice_id` INT(11) NULL DEFAULT NULL,
`menu_id` INT(11) NULL DEFAULT NULL,
`price` DOUBLE(22,0) NULL DEFAULT NULL,
`quantity` INT(11) NULL DEFAULT NULL,
`created_at` DATETIME NOT NULL DEFAULT current_timestamp(),
`updated_at` DATETIME NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
PRIMARY KEY (`id`) USING BTREE,
INDEX `menu_id` (`menu_id`) USING BTREE,
INDEX `invoice_id` (`invoice_id`) USING BTREE
)
COLLATE=‘utf8mb4_general_ci’
ENGINE=InnoDB
AUTO_INCREMENT=2;
6. View invoice_report_list ini adalah view yang akan kita Materialized.
DROP TABLE IF EXISTS `invoice_report_list`;
CREATE ALGORITHM=UNDEFINED SQL SECURITY DEFINER VIEW `invoice_report_list` AS SELECT i.id,id.invoice_id, id.price, id.quantity, i.created_at AS sale_date, u.name AS cahsier, m.name AS menu_name, r.name AS role_name, r.id AS role_id FROM invoice_detail id JOIN invoice i ON (id.invoice_id = i.id) JOIN user u ON (i.user_id = u.id) JOIN menu m ON (id.menu_id = m.id) JOIN role r ON (u.role_id = r.id) ;
Berikut beberapa Teknik Materialized View:
1. Materialize View dengan Event Scheduller dan Store Routine (Tidak Real Time).
Event Scheduller digunakan untuk memanagement Eksekusi suatu event dalam Batabase MYSQL dan Task dijalankan secara barkala atau sesuai dengan penjadwalan. Event Scheduller dalam Materialized View ini digunakan sebagai perintah penjadwalan yang kemudian digunakan untuk eksekusi secara berkala sehingga hasil Materialized View akan terupdate berdasarkan Jadwal dari Event Scheduller. Disini selain menggunakan Event scheduler juga menggunakan Store Routine. Store Routine adalah semacam prosedur fungsi yang bisa digunakan di MYSQL. Dengan menggunakan DB Rumah makan diatas, Berikut Langkah Materialized View:
- Kita akan membuat Table dengan nama invoice_report_mv_event_sche sebagai Materialized View nya yang memiliki field sama dengan View invoice_report_list:
CREATE TABLE `invoice_report_mv_event_sche` (
`id` INT(11) NULL DEFAULT NULL,
`invoice_id` INT(11) NULL DEFAULT NULL,
`price` DOUBLE(22,0) NULL DEFAULT NULL,
`quantity` INT(11) NULL DEFAULT NULL,
`sale_date` DATETIME NULL DEFAULT NULL,
`chasier` VARCHAR(50) NULL DEFAULT NULL COLLATE ‘utf8mb4_general_ci’,
`menu_name` VARCHAR(50) NULL DEFAULT NULL COLLATE ‘utf8mb4_general_ci’,
`role_name` VARCHAR(50) NULL DEFAULT NULL COLLATE ‘utf8mb4_general_ci’,
`role_id` INT(11) NULL DEFAULT NULL
)
COLLATE=‘utf8mb4_general_ci’
ENGINE=InnoDB;
- Buat Store Routine yang digunakan untuk refresh Table invoice_report_mv_event_sche.
CREATE DEFINER=`root`@`localhost` PROCEDURE `store_routine_mv`(
IN `rc` INT
)
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ”
BEGIN
TRUNCATE TABLE invoice_report_mv_event_sche;
INSERT INTO invoice_report_mv_event_sche
select * from invoice_report_list;
SET rc = 0;
END
- Buat Event untuk melakukan penjadwalan refresh dari Store Routine yang telah kita buat tadi. Event disini saya buat akan dijadwalkan setiap 1 Jam akan dieksekusi
CREATE DEFINER=`root`@`localhost` EVENT `event_scheduller_mv`
ON SCHEDULE
EVERY 1 HOUR STARTS ‘2020-10-25 10:46:00’
ON COMPLETION NOT PRESERVE
ENABLE
COMMENT ”
DO BEGIN
CALL `store_routine_mv`(@rc);
END
- Untuk menjalankan Event Scheduller di system dengan menggunakan command berikut:
SET GLOBAL event_scheduler = ON;
- Apabila semua step diatas sudah dilaksanakan maka table invoice_report_mv_event_sche akan ter refresh setiap 1 Jam.
2. Materialized View dengan Trigger (Real Time)
Materialized View dengan Trigger ini digunakan untuk setiap perubahan 1 data akan menambah data dari Table Material View sehingga akan terlihat Real Time. Jadi INSERT, UPDATE, DELETE akan mempengaruhi Tabel dari material view khusus untuk hanya 1 (INSERT, UPDATE, DELETE) data row saja. Berikut Langkah untuk Materialized View untuk INSERT dengan Trigger:
- Buat Table Materialized View dengan nama invoice_report_mv_trigger
CREATE TABLE `invoice_report_mv_trigger` (
`id` INT(11) NULL DEFAULT NULL,
`invoice_id` INT(11) NULL DEFAULT NULL,
`price` DOUBLE(22,0) NULL DEFAULT NULL,
`quantity` INT(11) NULL DEFAULT NULL,
`sale_date` DATETIME NULL DEFAULT NULL,
`chasier` VARCHAR(50) NULL DEFAULT NULL COLLATE ‘utf8mb4_general_ci’,
`menu_name` VARCHAR(50) NULL DEFAULT NULL COLLATE ‘utf8mb4_general_ci’,
`role_name` VARCHAR(50) NULL DEFAULT NULL COLLATE ‘utf8mb4_general_ci’,
`role_id` INT(11) NULL DEFAULT NULL
)
COLLATE=‘utf8mb4_general_ci’
ENGINE=InnoDB
ROW_FORMAT=DYNAMIC;
- Buat Trigger yang akan update setiap ada penambahan di table invoice_detail kemudian insert ke table Materialized View.
CREATE DEFINER=`root`@`localhost` TRIGGER `trigger_invoice_detail_mv` AFTER INSERT ON `invoice_detail` FOR EACH ROW BEGIN
INSERT INTO invoice_report_mv_trigger SELECT * FROM invoice_report_list WHERE id = NEW.id;
END
Apabila step sudah dilakukan maka data di table Materialized View invoice_report_mv_trigger akan terupdate dan bisa dikatakan Real Time.
3. Materialized View dengan Easy Script
Easy Script disini kita tinggal panggil query SQL setiap ada perubahan dari table utama kemudian kita update hasil View ke Materialized View, car aini hamper sama dengan MV Trigger, ini tergantung dengan logic yang Anda gunakan apaah bisa digunakan atau tidak, tapi tentunya bisa digunakan sesuai dengan alur scripting Anda. Untuk report Rumah Makan kita akan memperhatikan perubahan dari table invoice_detail, untuk setiap penambahan id dari invoice_detail maka akan kita update row di table Materialized View invoice_report_mv_trigger. Untuk query terserah Anda sesuai kreasi Anda namun di Report Rumah Makan ini saya hanya akan menggunakan query INSERT berikut untuk mengupdate:
INSERT INTO invoice_report_mv_trigger SELECT * FROM invoice_report_list WHERE id = $id_invoice_detail
Silahkan menggunakan File SQL Berikut Untuk Mencoba Sendiri Teknik Materialized View di Database MYSQL : Download
Materialized View akan terasa gunanya pada waktunya namun sebaiknya anda impementasikan diawal saat pembuatan system apabila menggunakan MYSQL, ditakutkan apabila diimplementasikan ditengah tengah program berjalan akan mempengaruhi performa dan kestabilan jalannya program apabila tidak berhati hati.
Semoga Bermanfaat
Wassalamualaikum