TẠP CHÍ KHOA HỌC VÀ CÔNG NGHỆ, ĐẠI HỌC ĐÀ NẴNG - SỐ 1(30).2009
59
ỨNG DỤNG KHUNG NHÌN THỰC
ĐỂ NÂNG CAO TỐC ĐỘ THỰC THI TRUY VẤN
APPLICATION OF THE MATERIALIZED VIEWS TO IMPROVE
THE QUERY EXECUTION SPEED
Nguyễn Trần Quốc Vinh
Trường Đại học Kinh tế, Đại học Đà Nẵng
TÓM TẮT
Khung nhìn thực (materialized view, KNT) có thể cho phép thực thi các truy vấn phức
tạp trên các cơ sở dữ liệu với dung lượng hàng terabytes trong vài giây hoặc phần nhỏ của
giây, nhưng nó ít được biết đến và ít được ứng dụng. Dù KNT có thể giúp nâng cao đáng kể
năng suất của hệ thống, nhưng không phả i trong mọi trường hợp. Bài viết này giới thiệu về
KNT và đề nghị giải pháp thực hiện một phần ý tưởng KNT trong các hệ quản trị cơ sở dữ liệu
không hỗ trợ KNT, cũng như khắc phục nhược điểm không có khả năng thực hiện cập nhật gia
tăng một số KNT trong một số hệ quản trị cơ sở dữ liệu có hỗ trợ KNT.
ABSTRACT
Materialized views can allow to execute the complex queries upon the large database in
a few seconds or less, they are not well known and that application is not popular. Even though
materialized views can help to significantly improve the performance of the systems, but not for
all cases. This paper introduces the materialized views, and offers the useful solution to carry
out a part of the idea of the materialized views in the database management systems not
supporting the materialized views, and to overcome the weakness of the inability to increase
undates on some systems supported by the materialized views.
1. Đặt vấn đề
KNT không cho phép nâng cao năng suất trong tất cả các trường hợp, hiệu quả
ứng dụng chúng có thể giảm đi rõ rệt nếu thường xuyên xảy ra thay đổi dữ liệu trong
các bảng gốc sử dụng để tạo KNT (hay KNT sử dụng). Nghĩa là, lợi ích sử dụng KNT
thể hiện ở sự chênh lệch tổng chi phí duy trì KNT và tổng chi phí thực thi các truy vấn
trên các bảng gốc ; hoặc là tổng chi phí duy trì cao hơn nhưng được dàn trải theo thời
gian và không gây ảnh hưởng đến hoạt động của hệ thống, trong khi chi phí thực thi
truy vấn giảm đi rõ rệt. Năng suất của hệ thống thông tin có thể bị giảm, thậm chí khả
năng phản hồi các truy vấn bị mất đi nếu số lượng truy vấn sử dụng một tập hợp dữ liệu
ít và trị số các truy vấn thấp, trong khi các phần tử của tập hợp dữ liệu đó được cập nhật
với tần suất cao.
Có ba phương pháp cập nhật KNT, đó là hoàn toàn (COMPLETE), gia tăng
(FAST hay còn gọi là INCREMENTAL) và ép buộc (FORCE) [1, 2]. Cập nhật hoàn
toàn thực tế là tạo lại KNT. Cập nhật gia tăng, chỉ sửa đổi nội dung KNT tương ứng với
các thay đổi trong các bảng gốc. Thông thường, cập nhật gia tăng đòi hỏi chi phí tài
TẠP CHÍ KHOA HỌC VÀ CÔNG NGHỆ, ĐẠI HỌC ĐÀ NẴNG - SỐ 1(30).2009
60
nguyên rất nhỏ so với cập nhật hoàn toàn. Cập nhật ép buộc nghĩa là khi có khả năng thì
thực hiện cập nhật gia tăng, còn nếu không thì sử dụng cập nhật hoàn toàn.
Trong quá trình ứng dụng KNT trong các hệ quản trị cơ sở dữ liệu (HQT CSDL)
hỗ trợ KNT như SQL Server và Oracle nảy sinh vấn đề HQT CSDL không thể thực hiện
cập nhật gia tăng cho nhiều KNT được tạo ra trên cơ sở các truy vấn khác nhau, đặc biệt
trong số đó nhiều truy vấn đòi hỏi nhiều tài nguyên. Khi đó, việc buộc phải cập nhật
hoàn toàn làm cho việc ứng dụng KNT trở nên không còn hiệu quả, thậm chí, KNT có
thể trở thành “gánh nặng” đối với hệ thống. Trong khi đó, trong nhiều trường hợp, kết
quả thực thi các truy vấn đó có thể cần phải có trong chế độ thời gian thực. Ngoài ra, sử
dụng KNT là một ý tưởng rất tốt để nâng cao năng suất hệ thống, nhưng chúng chưa
được hỗ trợ trong tất cả các HQT CSDL nguồn mở và miễn phí như mySQL,
PostgreSQL, FireBird,… kể cả thương mại như Interbase,…
Bài viết đề nghị giải pháp ứng dụng một phần ý tưởng KNT trong các HQT
CSDL đó, cũng như khắc phục nhược điểm không thể thực hiện cập nhật gia tăng trong
nhiều trường hợp đối với các HQT CSDL có hỗ trợ KNT, bằng cách xây dựng các bẫy
sự kiện (trigger) để thực hiện cập nhật gia tăng các bảng KNT.
2. KNT trong các HQT CSDL
Ý tưởng ứng dụng KNT – kết quả thực thi được giữ lại của các truy vấn, xuất
hiện từ những năm 80 của thế kỷ trước, nhưng KNT chỉ được triển khai thực tế cách
đây không lâu trong các phiên bản cuối cùng của một số HQT CSDL thương mại như
Oracle, MS SQL Server, IBM DB2. KNT được tạo ra với ý tưởng ban đầu là một công
cụ hỗ trợ cho các kho dữ liệu và các hệ thống hỗ trợ ra quyết định. Tuy nhiên, nó có thể
được ứng dụng cho bất kỳ CSDL nào.
Một ví dụ điển hình về tính hiệu quả của việc ứng dụng KNT. Một tập đoàn có
nhiều đại diện tại nhiều vùng thuộc nhiều quốc gia cung cấp cho nhiều khách hàng khác
nhau một số lượng lớn các sản phẩm. Như vậy, CSDL trung tâm của tập đoàn này có
thể chứa hàng triệu hoặc hơn bản ghi về chi tiết bán hàng. Bây giờ, người ta cần thống
kê số lượng sản phẩm được bán cũng như tổng doanh thu cho từng loại sản phẩm tại
mỗi vùng theo quốc gia. Truy vấn được thực thi và kết quả được trả lại sau một khoảng
một thời gian T1 nào đó. Kết quả này được lưu lại trong một bảng – KNT bao gồm 200
bản ghi. Sau này, mỗi khi xuất hiện truy vấn đó, thay vì thực thi lại từ đầu bằng việc
quét và xử lý hàng triệu bản ghi, HQT CSDL đọc bảng KNT chứa chỉ 200 bản ghi và trả
lại kết quả trong khoảng thời gian T2 (thường rất nhỏ so với T1
Có những thống kê rất hiếm khi được thực hiện, và chúng rất “nặng” trên khối
lượng dữ liệu lớn, chúng có thể được thực hiện trong chế độ trì hoãn. Nhưng có những
), thường là vài ms.
Thậm chí, KNT có thể được dùng để trả lời các truy vấn tương tự nhưng cho trường hợp
cả thế giới, hoặc một vài vùng nào đó, hoặc trường hợp chỉ cần tính hoặc doanh thu
hoặc số lượng sản phẩm. Tính năng này được gọi là viết lại truy vấn (query rewrite) [1].
Trong các HQT CSDL thương mại, các truy vấn được so sánh và viết lại như thế nào là
điều hầu như không được phổ biến. Bài viết [3] đưa ra một số kỹ thuật so sánh truy vấn
để xác định sự tương đồng, cũng như sự bao phủ giữa các truy vấn với nhau. Các kỹ
thuật đó có thể được sử dụng để triển khai kỹ thuật KNT.
TẠP CHÍ KHOA HỌC VÀ CÔNG NGHỆ, ĐẠI HỌC ĐÀ NẴNG - SỐ 1(30).2009
61
bộ phận kết quả thống kê luôn luôn cần thiết hoặc được yêu cầu trong chế độ thời gian
thực. Các thống kê đó cần phải được thực hiện ngay tức khắc, và việc thực hiện chúng
có thể kiềm h ãm công việc của cả tổ chức, ví d ụ, quyết toán kế toán, thống kê tồn
kho,… KNT cho phép giải quyết các vấn đề đó. Nó có thể giúp tăng tốc nhiều lần các
truy vấn phức tạp trên các CSDL với số lượng bản ghi rất lớn, cho phép thực thi các
truy vấn phức tạp trên các CSDL dung lượng hàng terabytes trong vài giây hoặc phần
nhỏ của giây [1]. HQT CSDL thực hiện việc đó bằng cách sử dụng các thống kê hoặc
các phép nối đã được tính trước của dữ liệu – kết quả thực thi các truy vấn, để trả lời các
truy vấn một cách trong suốt đối với người dùng. Thông thường, các thống kê được tính
trước đó có kích thước rất nhỏ so với nguồn dữ liệu gốc là các bảng, mà nếu không có
KNT, truy vấn sẽ được thực thi trên các bảng đó.
Nếu như dữ liệu cần để trả lời một truy vấn có trong KNT, thì chúng sẽ được
truy xuất bằng cách quét các bảng KNT khi xuất hiện truy vấn. Khi đó các chi phí bao
gồm trong việc xét khả năng sử dụng KNT và quét các bảng KNT đó, và không bao
gồm chi phí cho các thao tác đắt giá như quét các bảng gốc và các thao tác như nối
(JOIN), thống kê (SUM,
COUNT, AVG, MIN, MAX),
nhóm (GROUP BY).
Hình vẽ 1 cho thấy
nguyên tắc sử dụng các KNT:
Các bảng gốc được sử dụng để
trả lời các truy vấn Z1, Z2,
Z5,… trong khi kết quả thực thi
các truy vấn Z3, Z6
Sử dụng KNT vi phạm một số yêu cầu của lý thuyết thiết kế CSDL, chẳng hạn,
vi phạm tính dư thừa và các bất thường, và nó đòi h ỏi chi phí duy trì. Tuy nhiên, một
khi các “tác hại” của nó là rất nhỏ so với “lợi ích” do nó mang lại, thì chúng ta có thể
chấp nhận các “tác hại” đó. Các ưu điểm ứng dụng KNT bao hàm trong việc nâng cao
năng suất hệ thống thông tin nhờ:
,… được lấy
từ các KNT.
- Rút ngắn thời gian thực thi các truy vấn;
- Giảm số lượng các lần đọc/ghi vật lý, bởi vì khối lượng dữ liệu cần xử lý giảm;
- Giảm tải bộ vi xử lý trung tâm và tài nguyên nói chung;
- Giảm khối lượng thao tác nối, sắp xếp cũng như tính các hàm tổng hợp.
Vấn đề sử dụng các KNT để trả lời các truy vấn [4, 5] nhận được sự quan tâm
đáng kể dưới dạng ứng dụng chúng trong nhiều ứng dụng quản trị dữ liệu, chẳng hạn
như trong liên kết dữ liệu, tron g các kho dữ liệu, trong thiết kế web, trong tối ưu hoá
truy vấn và thậm chí, KNT được ứng dụng trong bài toán cập nhật các KNT. Khi ứng
dụng KNT, HQT CSDL phải giải quyết bài toán được định dạng như sau: cho một truy
vấn trên một lược đồ CSDL và tập hợp các KNT trên chính lược đồ CSDL đó, có thể sử
dụng các KNT để trả lời truy vấn đó hay không.
Hình 1. Nguyên tắc ứng dụng KNT
TẠP CHÍ KHOA HỌC VÀ CÔNG NGHỆ, ĐẠI HỌC ĐÀ NẴNG - SỐ 1(30).2009
62
Truy vấn tính tổng doanh thu cho từng khách hàng theo từng vùng trên CSDL
trong SQL Server 2005 sau đây phần nào minh hoạ sức mạnh của KNT ( gọi là indexed
view trong SQL Server 2005) thông qua việc giảm rất mạnh thời gian thực thi truy vấn,
trong khi thời gian thực hiện cập nhật bảng gốc tăng không đáng kể. Tạm gọi truy vấn
này là Q_TongTheoVung:
SELECT a.HoTen, b.KhachHangID, COUNT_BIG(*) as CNT, SUM(d.SoLuong * d.DonGia)
AS TongTien FROM Sales.VungLanhTho a INNER JOIN Sales.KhachHang b ON a.LanhThoID = b.
LanhThoID INNER JOIN Sales.BanHang c ON a. LanhThoID = c. LanhThoID AND b.KhachHangID =
c.KhachHangID INNER JOIN Sales.ChiTietBanHang d ON c.BanHangID = d.BanHangID GROUP BY
a.Name, b.KhachHangID.
Các bảng VungLanhTho, KhachHang, BanHang và ChiTietBanHang được sử
dụng trong truy vấn có số lượng bản ghi tương ứng là 10, 19.185, 31.465 và 121.217.
Khi không sử dụng KNT, truy vấn này có thời gian thực thi trung bình 750 ms – một tốc
độ ấn tượng trên một số lượng bản ghi tương đối lớn nhờ các chỉ mục liên cung đã được
tạo ra cho một số cột của các bảng KhachHang, BanHang, ChiTietBanHang. Thời gian
thực thi truy vấn này được đo bằng cách dùng SET STATISTICS TIME ON hoặc
getdate (để đo chính xác đến ms hoặc với độ chính xác cao hơn như được nêu trong [6])
trên hệ thống với bo mạch chủ DQ965GF, CPU E6800, 2GB RAM và SATA2 RAID 5
với một người dùng. Kế hoạch thực thi do công cụ Query Analyzer cung cấp như sau:
quét d – 27%, quét c – 14%, nối c d – 9%, quét b – 1%, nối (c d) b – 34%, tổng hợp
– 14%, nối (kết quả tổng hợp)a – 0%, chọn kết quả – 0%.
Tuy nhiên, sau khi tạo KNT V_TongTheoVung cho truy vấn trên, thời gian thực
thi của nó đo được xấp xỉ 0 ms. Kế hoạch thực thi ước lượng của truy vấn
Q_TongTheoVung trên sẽ là: quét V_TongTheoVung – 100%, chọn kết quả – 0%. Vì
lệnh CREATE UNIQUE CLUSTERED INDEX đã thực hoá khung nhìn, kết quả thực thi truy
vấn trên đã có sẵn trong KNT và bao gồm chỉ 6 bản ghi, nên thời gian thực thi truy vấn
trên xấp xỉ 0 ms là điều dễ hiểu. Thậm chí, bây giờ thời gian thực thi truy vấn tính tổng
doanh thu toàn cầu cũng xấp xỉ 0 ms, bởi vì SQL Server 2005 cũng sử dụng một cách
linh hoạt KNT để trả lời.
Phân tích kế hoạch thực thi việc cập nhật bảng ChiTietBanHang, chẳng hạn cập
nhật số lượng đã bán – SoLuong, cho thấy SQL Server 2005 cập nhật KNT
V_TongTheoVung theo phương pháp cập nhật gia tăng trong quá trình cập nhật bảng
gốc. Thời gian cập nhật bảng ChiTietBanHang lúc này giao động từ 2 – 3ms, so với xấp
xỉ 1ms khi chưa có KNT. Tuy nhiên, SQL Server không thể thực hiện cập nhật gia tăng
cho mọi KNT. Khi không thể thực hiện cập nhật gia tăng, HQT CSDL sẽ thực hiện cập
nhật toàn phần.
Chẳng hạn, Oracle 11g chỉ có thể cập nhật theo phương pháp cập nhật hoàn toàn
KNT trên cơ sở truy vấn để tính tổng thành tiền cho từng khách hàng theo từng vùng
(tạm gọi là Q_TongTheoKhachHang):
SELECT a.QuocGia_Vung, b.KhachHang_ID, SUM(c.SoLuong*d.DonGia) AS Total FROM
SH.QUOCGIA a INNER JOIN SH.KHACHHANG b ON a. QuocGia_ID = b.QuocGia_ID
TẠP CHÍ KHOA HỌC VÀ CÔNG NGHỆ, ĐẠI HỌC ĐÀ NẴNG - SỐ 1(30).2009
63
INNER JOIN SH.BANHANG c ON b.KhachHang_ID = c.KhachHang_ID INNER JOIN
SH.BANGGIA d ON c.SanPham_ID = d.SanPham_ID AND c.ThoiGian_ID = d.ThoiGian_ID
GROUP BY a.QuocGia_Vung, b.KhachHang_ID.
Các bảng QUOCGIA, KHACHHANG, BANHANG và BANGGIA có số lượng
bản ghi tương ứng là 23, 55.500, 918.843 và 82.112. Thời gian thực thi truy vấn là 5.42s
(đo thời gian thực thi bằng lệnh SET TIMER ON). Thời gian cần thiết để cập nhật một
bản ghi trong bảng BANHANG xấp xỉ 1ms.
Khi KNT đã được cập nhật, Oracle có thể sử dụng nó để trả lời các truy vấn,
chẳng hạn các truy vấn tương đương với truy vấn Q_TongTheoKhachHang nhưng có
cách viết khác ; hoặc truy vấn Q_TongTheoKhachHang có thêm điều kiện WHERE
a.QuocGia_Vung = ‘Americas’; hoặc truy vấn tính tổng thành tiền theo từng vùng sẽ có thời
gian thực thi xấp xỉ 0 ms. Đó là kết quả làm việc của chức năng viết lại truy vấn.
Khi KNT được tạo ra với thông số
REFRESH ON COMMIT, Oracle thực hiện việc cập nhật KNT giống như một phần của
giao tác thực thi cập nhật bảng gốc. Thời gian thực hiện lệnh sửa đổi dữ liệu trong các
bảng gốc sẽ bằng thời gian cập nhật KNT, xấp xỉ 5.4s. Điều đó chứng tỏ KNT này được
Oracle cập nhật theo phương pháp cập nhật hoàn toàn. Một khi KNT được cập nhật sau
khi có sự thay đổi dữ liệu trong các bảng gốc, thời gian thực thi truy vấn
Q_TongTheoKhachHang xấp xỉ 0ms.
3. “KNT” trong các HQT CSDL không hỗ trợ KNT
Nếu HQT CSDL không hỗ trợ KNT, chúng ta vẫn có thể áp dụng ý tưởng KNT
để tăng tốc độ thực thi truy vấn bằng cách sử dụng các bẫy sự kiện (trigger). Chẳng hạn,
cho trường hợp truy vấn Q_TongTheoKhachHang trên đây chúng ta có thể thực hiện
theo sơ đồ ba bước sau đây.
Bước 1. Tạo một bảng có các cột và kiểu dữ liệu tương ứng chứa kết quả thực thi
truy vấn Q_TongTheoKhachHang, sau đó tạo các chỉ mục nếu cần:
CREATE TABLE TMV_TongTheoKhachHang as Q_TongTheoKhachHang.
Bước 2. Tạo các bẫy sự kiện thêm mới (ON INSERT), cập nhật (ON UPDATE)
và xoá (ON DELETE) cho mỗi bản ghi cho mỗi bảng trong các bảng gốc liên quan
QUOCGIA, KHACHHANG, BANHANG, BANGGIA. Các bẫy sự kiện này s ẽ điều
chỉnh giá trị các bản ghi trong bảng TMV_TongTheoKhachHang tương ứng với sự thay
đổi dữ liệu trong các bảng gốc. Ví dụ, bẫy sự kiện thêm mới đơn giản cho bảng
BANHANG có thể được xây dựng theo kịch bản sau đây.
CREATE OR REPLACE TRIGGER TRG_BanHang_AI ON BANHANG
AFTER INSERT FOR EACH ROW
DECLARE Gia number(10, 2);
BEGIN
--Đọc giá trị DonGia từ BANGGIA tương ứng với SanPham_ID và ThoiGian_ID vừa
--được thêm mới; Giả sử new.KhachHang_ID đã có trong bảng TMV_TongTheoKhachHang
SELECT DonGia INTO Gia FROM SH.BANGGIA WHERE
SanPham_ID = :new.SanPham_ID AND ThoiGian_ID = :new.ThoiGian_ID;
--Cập nhật bản ghi trong bảng TMV_TongTheoKhachHang có KhachHang_ID tương ứng
UPDATE TMV_TongTheoKhachHang SET Total = Total + :new.SoLuong * :Gia
WHERE KhachHang_ID = :new.KhachHang_ID;
END;
Các bẫy sự kiện này sẽ đảm bảo cho thông tin chứa trong bảng