Truy vấn báo cáo dài 20 dòng tương tự đã được sao chép vào năm trang tổng quan và mỗi bản sao bị trôi đi một chút. Một người đếm các đơn đặt hàng bị hủy, trong khi người khác thì không. Chế độ xem SQL giải quyết vấn đề này bằng cách xác định logic đó một lần, trong cơ sở dữ liệu, dưới một tên rõ ràng.

Chúng tôi đã chạy mọi ví dụ bên dưới trong SQLite 3.50 và kết quả hiển thị là từ lần chạy đó. Chế độ xem là SQL tiêu chuẩn, nhưng các chi tiết như cập nhật thông qua chế độ xem, chế độ xem cụ thể hóa và xử lý phần phụ thuộc khác nhau giữa các cơ sở dữ liệu và chúng tôi chỉ ra những điều đó.

Quan điểm là gì?

Chế độ xem là câu lệnh SELECT được lưu trong cơ sở dữ liệu có tên. Khi bạn truy vấn dạng xem, cơ sở dữ liệu sẽ chạy truy vấn đã lưu đó. Nó hoạt động giống như một bảng để đọc, nhưng chế độ xem bình thường không giữ bản sao dữ liệu của riêng nó.

Hãy nghĩ về một bộ lọc đã lưu trong bảng tính. Bộ lọc không phải là bản sao thứ hai của trang tính. Đó là cách xem xét cùng một dữ liệu, luôn phản ánh các giá trị mới nhất.

Các bảng mẫu

CREATE TABLE customers (
  id INT PRIMARY KEY, name TEXT, city TEXT, email TEXT, credit_limit INT
);
CREATE TABLE orders (
  id INT PRIMARY KEY, customer_id INT, total INT, status TEXT
);

INSERT INTO customers VALUES
 (1,'Asha','Pune','asha@example.com',50000),
 (2,'Ben','Delhi','ben@example.com',20000),
 (3,'Chitra','Pune','chitra@example.com',80000);

INSERT INTO orders VALUES
 (101,1,1200,'paid'), (102,1,800,'paid'),
 (103,2,300,'pending'), (104,3,2500,'paid');

Tạo chế độ xem

Chế độ xem này tóm tắt đơn đặt hàng của từng khách hàng. Nó ẩn tham gia, nhóm và biểu thức CASE đằng sau một tên đơn giản:

CREATE VIEW customer_sales AS
SELECT c.id, c.name, c.city,
       COUNT(o.id) AS orders,
       COALESCE(SUM(CASE WHEN o.status = 'paid' THEN o.total END), 0) AS paid_total
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name, c.city;

Bây giờ bất cứ ai cũng có thể sử dụng nó mà không cần biết nó hoạt động như thế nào:

SELECT * FROM customer_sales ORDER BY paid_total DESC;

-- 3 | Chitra | Pune  | 1 | 2500
-- 1 | Asha   | Pune  | 2 | 2000
-- 2 | Ben    | Delhi | 1 |    0

SELECT name, paid_total FROM customer_sales WHERE city = 'Pune';

-- Asha   | 2000
-- Chitra | 2500

Ben xuất hiện với số 0 vì chế độ xem sử dụng LEFT JOIN và COALESCE. Những quyết định như thế này, được đưa ra một lần trong chế độ xem, chính là điều giúp cho các báo cáo luôn nhất quán. Để làm mới hiểu biết của bạn về cách xử lý NULL, hãy xem Giá trị SQL NULL.

Lượt xem luôn cập nhật

Vì chế độ xem bình thường luôn chạy truy vấn nên dữ liệu mới sẽ xuất hiện ngay lập tức. Chúng tôi đã thêm một đơn đặt hàng đã thanh toán cho Ben và truy vấn lại chế độ xem:

INSERT INTO orders VALUES (105, 2, 900, 'paid');

SELECT name, paid_total FROM customer_sales WHERE name = 'Ben';

-- Ben | 900

Không có bước làm mới và không có bản sao để đồng bộ hóa. Đây là điểm khác biệt chính so với việc lưu kết quả truy vấn vào một bảng riêng biệt.

Ẩn cột

Chế độ xem thường được sử dụng để chỉ hiển thị các cột mà một người hoặc ứng dụng cần. Ở đây, địa chỉ email và giới hạn tín dụng bị bỏ qua:

CREATE VIEW public_customers AS
SELECT id, name, city FROM customers;

-- columns: id, name, city

Trong cơ sở dữ liệu có quyền, chẳng hạn như PostgreSQL, SQL Server và MySQL, bạn có thể cấp cho người dùng báo cáo quyền truy cập vào dạng xem chứ không phải vào bảng cơ bản. SQLite không có tài khoản người dùng, vì vậy chúng tôi chỉ có thể hiển thị giới hạn cột. Hãy coi chế độ xem là một lớp bảo vệ. Dữ liệu nhạy cảm vẫn cần kiểm soát quyền truy cập thích hợp và một số cơ sở dữ liệu cung cấp bảo mật cấp hàng để lọc hàng theo người dùng.

Hiệu suất: lượt xem không phải là tăng tốc độ

Một lầm tưởng phổ biến là các lượt xem giúp truy vấn nhanh hơn. Chế độ xem thông thường được thay thế bằng định nghĩa của nó khi bạn truy vấn nó, do đó cơ sở dữ liệu thực hiện công việc tương tự như khi bạn tự mình viết toàn bộ truy vấn. Kế hoạch thực hiện của chúng tôi cho thấy việc tham gia và nhóm của chế độ xem đang được chạy như một phần của truy vấn:

EXPLAIN QUERY PLAN SELECT * FROM customer_sales WHERE city = 'Pune';

-- CO-ROUTINE customer_sales
-- SCAN c ...
-- SEARCH o USING AUTOMATIC COVERING INDEX (customer_id=?) LEFT-JOIN
-- USE TEMP B-TREE FOR GROUP BY
-- SCAN customer_sales

Vì vậy, chế độ xem qua truy vấn chậm vẫn là truy vấn chậm. Tốc độ đến từ các chỉ mục và thiết kế truy vấn tốt, như được đề cập trong Tối ưu hóa truy vấn SQL và Giải thích về chỉ mục SQL. Hãy chú ý đến các chế độ xem được xây dựng trên các chế độ xem khác. Các lớp lồng nhau giúp bạn dễ dàng tham gia cùng một bảng lớn nhiều lần mà không nhận thấy.

Chế độ xem cụ thể hóa

Chế độ xem cụ thể hóa không lưu trữ kết quả của nó, giống như một bảng được lưu trong bộ nhớ đệm. Tốc độ đọc rất nhanh nhưng dữ liệu chỉ mới như lần làm mới gần đây nhất.

-- PostgreSQL
CREATE MATERIALIZED VIEW daily_sales AS
SELECT order_date, SUM(total) AS revenue FROM orders GROUP BY order_date;

REFRESH MATERIALIZED VIEW daily_sales;

Hỗ trợ khác nhau. PostgreSQL và Oracle có các chế độ xem cụ thể hóa và SQL Server cung cấp các chế độ xem được lập chỉ mục với các quy tắc riêng của chúng. SQLite và MySQL thiếu chúng, vì vậy mọi người sử dụng bảng tóm tắt thông thường được làm mới theo lịch trình. Chúng tôi không chạy ví dụ PostgreSQL cho bài viết này.

Bạn có thể cập nhật chế độ xem không?

Nó phụ thuộc vào cơ sở dữ liệu và chế độ xem. Một chế độ xem đơn giản trên một bảng duy nhất, không cần nhóm hoặc DISTINCT, thường có thể được cập nhật trong PostgreSQL, MySQL và SQL Server và thay đổi sẽ được chuyển đến bảng. Một chế độ xem có các phép nối hoặc tổng hợp thường không thể thực hiện được vì cơ sở dữ liệu không thể cho biết hàng nào cần thay đổi.

SQLite hoàn toàn không cho phép ghi trực tiếp vào các khung nhìn. Nỗ lực của chúng tôi đã thất bại:

INSERT INTO customer_sales (name) VALUES ('X');

-- cannot modify customer_sales because it is a view

Một số cơ sở dữ liệu hỗ trợ trình kích hoạt INSTEAD OF để làm cho chế độ xem có thể ghi được và nhiều cơ sở dữ liệu cung cấp WITH CHECK OPTION, tính năng dừng ghi sẽ không hiển thị qua chế độ xem. Trong thực tế, hầu hết các nhóm giữ chế độ xem chỉ đọc và ghi vào bảng.

Sự phụ thuộc và sự đổ vỡ

Một khung nhìn phụ thuộc vào các bảng mà nó đọc từ đó. Chúng tôi đã tạo chế độ xem trên một bảng, sau đó xóa bảng:

CREATE TABLE ghost AS SELECT 1 AS x;
CREATE VIEW v_ghost AS SELECT x FROM ghost;
DROP TABLE ghost;

SELECT * FROM v_ghost;
-- no such table: main.ghost

SQLite cho phép loại bỏ và chỉ thất bại sau đó khi chế độ xem được sử dụng. Các cơ sở dữ liệu khác, chẳng hạn như PostgreSQL, từ chối loại bỏ bảng mà chế độ xem phụ thuộc vào, trừ khi bạn thêm CASCADE, thao tác này cũng sẽ xóa chế độ xem. Dù bằng cách nào, việc đổi tên hoặc xóa một cột có thể phá vỡ các chế độ xem, vì vậy hãy kiểm tra chúng trong kiểm tra hồi quy sau khi thay đổi lược đồ.

Ngoài ra còn có một cái bẫy tinh vi với SELECT * trong chế độ xem. Chúng tôi đã tạo CREATE VIEW star AS SELECT * FROM customers, sau đó thêm một cột vào bảng. Trong SQLite, chế độ xem đã chọn cột mới. Các cơ sở dữ liệu khác, bao gồm PostgreSQL, mở rộng danh sách cột khi chế độ xem được tạo, do đó các cột mới không xuất hiện. Vì vậy hãy liệt kê các cột một cách rõ ràng. Nó ghi lại khung nhìn và nó hoạt động giống nhau ở mọi nơi.

Quản lý lượt xem

Việc bỏ chế độ xem chỉ xóa truy vấn đã lưu. Trong thử nghiệm của chúng tôi, bảng customers vẫn còn đủ ba hàng sau khi loại bỏ public_customers. Giữ các định nghĩa chế độ xem trong kiểm soát phiên bản cùng với quá trình di chuyển của bạn để các thay đổi được xem xét giống như bất kỳ mã nào khác.

Xem so với CTE so với bảng

Những lỗi thường gặp

• Mong đợi tốc độ tăng lên. Một chế độ xem bình thường sẽ chạy truy vấn của nó mỗi lần.
• Sử dụng SELECT * trong chế độ xem. Hành vi khác nhau giữa các cơ sở dữ liệu khi bảng thay đổi.
• Lồng nhau sâu. Chế độ xem được xây dựng trên chế độ xem ẩn các phép nối đắt tiền.
• Sử dụng ORDER BY bên trong chế độ xem. Thứ tự không được đảm bảo khi bạn truy vấn nó, vì vậy hãy sắp xếp theo truy vấn bên ngoài.
• Tên không rõ ràng. Tiền tố như v_ hoặc danh từ rõ ràng như customer_sales sẽ hữu ích.
• Bỏ quên các chế độ xem trong quá trình di chuyển. Kiểm tra chúng sau mỗi lần thay đổi lược đồ.

Lời kết

Chế độ xem là một cách đơn giản để đưa logic quan trọng vào cơ sở dữ liệu. Sử dụng chúng để giữ cho các báo cáo nhất quán và ẩn các cột không cần thiết chứ không phải như một thủ thuật hiệu suất. Để bắt đầu, hãy tìm một truy vấn xuất hiện ở một số vị trí trong mã của bạn, biến truy vấn đó thành dạng xem có các cột rõ ràng và hướng từng người gọi đến truy vấn đó.

Câu hỏi thường gặp

Tiếp tục học hỏi