Materialized View trong DBMS là gì ?

Phong, một developer tại công ty BSS. Nhân, là nhân viên kế toán cùng công ty.

Một ngày nọ thấy Nhân đang rảnh rỗi không có chuyện gì làm, Phong lấy hết can đảm của mình tới nhờ Nhân hỗ trợ tìm những giấy tờ có liên quan tới Phong bao gồm : bảng lương, bảo hiểm y tế & xã hội, sở hưu cơ sở vật chất được công ty cấp cho nhu cầu công việc MacBook Pro 2019, nhu cầu đi lại xe Mercedes S450, hoá đơn tiền xăng dầu,...

Nhân trên danh nghĩa là người tiếp nhận cũng như tạo ra những loại giấy tờ kể trên để tổng hợp và cất giữ vào kho hồ sơ ở nhiều nơi. Đồng thời cũng là người sẽ phải tính toán và phân tích số liệu để đưa ra dự báo về tình hình phát triển của công ty. Nhân phải mất thời gian trung bình khoảng 5 ngày để giải quyết yêu cầu của Phong.

Vấn đề làm sao để cải thiện hiệu năng làm việc và đáp ứng yêu cầu trả lời nhanh hơn hiện tại mà Nhân đang làm?

Hệ quản trị cơ sở dữ liệu (DBMS)

Database Table

• Là nơi lưu trữ data từ nhiều nguồn.
• Data lưu trữ dạng vật lý.
• Khi có nhu cầu tra cứu thông tin đã từng lưu trữ thì thông tin cũng được trích xuất ra từ những table này.

Database View (DV)

• Đây là cách mà Nhân kế toán của BSS đang làm, Nhân phải lục lại tủ hồ sơ ở nhiều nơi để phân loại và tìm kiếm hồ sơ của Phong.
• DV là 1 use case hỗ trợ cho việc tra cứu trích xuất thông tin đơn giản hơn table .

• DV được định nghĩa bởi 1 câu lệnh base SQL những logic structure của những tables sẽ trả về data (tuỳ vào số lượng tables được join và số lượng các điều kiện tham gia vào mà câu lệnh SQL này trở thành đơn giản hoặc phức tạp)

• Tạo 1 DV :
  

   CREATE VIEW documents_of_employee_phong_dv AS
    SELECT documents.*, document_histories.*, 
    FROM documents, document_histories, employees
    WHERE documents.userId = employees.id AND documents.id = document_histories.documentId AND employees.id = 1; // 1 là id của Phong

• Sử dụng DV:

   SELECT * FROM documents_of_employee_phong_dv

• Thực ra câu lệnh SQL thực hiện bên dưới DBMS là :

   SELECT * FROM (
     SELECT documents.*, document_histories.*, 
     FROM documents, document_histories, employees
     WHERE documents.userId = employees.id AND documents.id = document_histories.documentId AND employees.id = 1;
   )

• DV không lưu trữ data, DV là 1 table ảo, chỉ cần quan tâm đến câu lệnh SQL. Nên khi trích xuất thông tin từ DV thực chất ra là đang chạy lại những câu lệnh base SQL ở trên.
• Câu lệnh base SQL đơn giản (join 1 table) thì DV đơn giản và ngược lại câu lệnh base SQL phức tạp (join nhiều table, nhiều điều kiện tham gia) thì DV phức tạp.

Materialized View (MV)

• Giống với DV là MV được định nghĩa bằng 1 câu lệnh base SQL

• Nhưng khác với cách lưu data của DV. MV lưu data dạng vật lý. Data được lưu là kết quả trả về từ câu lệnh base SQL.

• Tạo 1 DV :
  

   CREATE MATERIALIZED  VIEW documents_of_employee_phong_mv AS
    SELECT documents.*, document_histories.*, 
    FROM documents, document_histories, employees
    WHERE documents.userId = employees.id AND documents.id = document_histories.documentId AND employees.id = 1; // 1 là id của Phong

• Tốc độ trích xuất thông tin từ MV nhanh hơn gấp nhiều lần so với DV vì data đang được lưu tương tự như database table đồng thời không tốn thời gian chạy lại câu lệnh base SQL . Trích xuất data trực tiếp vào table không thông qua câu lệnh base SQL.
• Nhân có thể tăng hiệu năng làm việc và cung cấp các giấy tờ cho Phong nhanh hơn từ 5 ngày xuống còn 1/2 ngày bằng cách mỗi lần khi nhận giấy tờ từ Phong thì Nhân nên đặt tất cả giấy tờ đó vào 1 túi hồ sơ dán tên Phong. Giờ đây khi có bất cứ khi nào Phong nhờ vã cung cấp hồ sơ giấy tờ thì Nhân đều có thể tự tin hỗ trợ Phong ngay lập tức. Còn nhờ vã những chuyện ngoài khả năng như đi chơi, ăn uống thì khó quá nên bỏ qua.
• MV mang lại hiệu năng cao như vậy nhưng gặp phải vấn đề là vấn đề đồng bộ data mới nhất. Một lần nọ, Nhân đang bận đầu tắt mặt tối cả ngày thì Phong đưa hoá đơn tiền xăng xe cho Nhân nhưng sau đó Nhân để đâu quên mất và không cất vào túi hồ sơ của Phong. Nên hoá đơn này chắc chắn sau này sẽ không bao giờ đến được tay của Phong. Tốt nhất nếu Nhân quá bận rộn công việc thì nên cài đặt nhắc nhở trên điện thoại bổ sung túi hồ sơ của Phong sau đó.

• MV cũng có cơ chế tương tự để đồng bộ hoá data mới nhất như vậy nhưng cách tiếp cận theo 2 hướng:

  • BASIC REFRESH
    • DBMS cần thực hiện câu lệnh REFRESH MV để update data mới nhất (ví dụ như sau khi có data mới, data bị xoá hoặc có chỉnh sửa data sẽ ảnh hưởng với kết quả sai khác trả về trước đó). BASIC REFRESH sẽ READ LOCKING tất cả truy xuất tới MV khi đang trong quá trình thực hiện update data (để ngăn ngừa truy xuất ra thông tin cũ rích, sai khác dẫn đến ảnh hưởng gây ra hậu quả nghiêm trọng cho người dùng lẫn hệ thống).

    REFRESH MATERIALIZE VIEW documents_of_employee_phong_mv;
  

  • CONCURRENTLY REFRESH
    • Trong một số trường hợp data không bị quá đòi hỏi khắc khe phải trả về kết quả cập nhật mới nhất, nên có thể trả về data đang trong quá trình tiền xử lý, data trong suốt với người dùng ví dụ như thông tin về tên/thông số sản phẩm trên ecommerce không nên để client ngồi chờ cả vài phút để chờ thực hiện xong lệnh update mới coi được. CONCURRENTLY REFRESH dùng để giải quyết READ LOCKING.

    CREATE UNIQUE INDEX documents_employee_ix ON documents_of_employee_phong_mv (document_id);
    REFRESH MATERIALIZED VIEW CONCURRENTLY documents_of_employee_phong_mv;