Teknik Materialized View dengan Database MySQL

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:

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: 

Gambaran Beberapa Table yang digunakan untuk menghasilkan Table Materialized view

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

 

4. Materialized View dengan Flex View

flexviews menggunakan plugin untuk backlog proses Materialized View di DB Utama sehingga akan erlihat realtime tanpa harus membuat proses yang berlebihan . Repository -> Resource (ada Async biar seperti NoSQL)
Manual -> Resource
PPT -> Resource

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

Leave A Comment

Your email address will not be published. Required fields are marked *