Bí mật lớn nhất về tăng tốc độ câu lệnh SQL về mức mili giây cực hiệu quả: RESULT CACHE

Mỗi cơ sở dữ liệu đều có tính năng lưu trữ các dữ liệu thường xuyên được sử dụng để tăng tốc độ thực hiện câu lệnh. Đây được gọi là Buffer Cache. Kỹ thuật này giúp tăng tốc độ rất nhiều lần, tuy nhiên, nó vẫn chưa đủ so với kỹ thuật sử dụng Result Cache. Tôi sẽ giúp các anh em hiểu chi tiết hơn vấn đề này. Bài viết này áp dụng cho cơ sở dữ liệu Oracle anh em nhé.

1. Ví dụ đơn giản để giải thích về BUFFER CACHE và RESULT CACHE

Để giải thích nguyên lý của Buffer Cache trong cơ sở dữ liệu, ta có thể dùng ví dụ sau: giả sử tôi và bạn Tèo cùng làm bài thi trắc nghiệm môn Hóa. Câu hỏi số 1 có 4 đáp án và đáp án đúng nằm trên trang 10 của tài liệu. Tôi đã tìm được đáp án và chỉ cho Tèo biết tìm trang số 10 để giải câu hỏi đó. Điều này tương tự như Buffer Cache trong cơ sở dữ liệu, nó giúp tăng tốc độ thực hiện truy vấn bằng cách lưu trữ các dữ liệu thường xuyên được sử dụng. Tuy nhiên, nếu sử dụng kỹ thuật Result Cache, tôi sẽ chỉ cho Tèo biết đáp án là B và giải quyết câu hỏi một cách nhanh chóng hơn nhiều lần.

2. Ưu điểm lớn nhất của RESULT CACHE gồm những gì?

Thứ nhất. Tăng tốc độ truy vấn SQL: Với HINT RESULT CACHE, kết quả của các truy vấn sẽ được lưu trữ trong bộ nhớ cache trong Oracle. Khi có truy vấn tương tự, kết quả sẽ được trả về từ bộ nhớ cache, giúp giảm thời gian thực hiện truy vấn. Thứ hai. Tối ưu tài nguyên: Vì kết quả của các truy vấn đã được lưu trữ trong bộ nhớ cache, nên không cần phải thực hiện các truy vấn lặp lại, giúp giảm tải cho hệ thống và tiết kiệm tài nguyên máy chủ. Thứ ba. Dễ dàng triển khai: Việc áp dụng HINT có thể thực hiện vô cùng đơn giản, chỉnh sửa một câu lệnh từ không có RESULT CACHE sang sử dụng RESULT CACHE chỉ trong chưa tới 1 phút.

3 Nhược điểm của RESULT CACHE là gì?

Tuy nhiên, RESULT CACHE cũng có các nhược điểm. Anh em cần hiểu rõ các nội dung này trước khi quyết định có áp dụng hay không a. Các trường hợp dữ liệu của các table trong câu lệnh bị thay đổi thì hệ thống sẽ phải tính toán lại kết quả, việc sử dụng RESULT CACHE không có tác dụng trong trường hợp này. b. Nếu sử dụng tùy tiện trên các câu lệnh có dữ liệu thay đổi liên tục có thể dẫn đến nghẽn, wait ở mức bộ nhớ, việc này rất nghiêm trọng.

4. Cách thức sử dụng RESULT CACHE

Câu lệnh chưa dùng RESULT CACHE

SELECT * FROM employees WHERE department_id = 10;

Câu lệnh có dùng RESULT CACHE như sau SELECT /*+ RESULT_CACHE */ * FROM employees WHERE department_id = 10;

5. Demo thực hiện RESULT CACHE để khiến câu lệnh tăng tốc chỉ còn vài mili giây ngay lập tức

Tôi có làm video demo để chứng minh cho hiệu quả của kỹ thuật RESULT CACHE, nếu bạn muốn tận mắt chứng kiến toàn bộ quá trình Demo, bạn có thể xem tại kênh youtube cá nhân của tôi. Link của video Demo này: Click vào đây. Tôi tóm tắt lại nội dung thực hiện Demo như sau

Trong cơ sở dữ liệu của tôi có một bảng lưu thông tin toàn bộ các nhân viên trong một tập đoàn. Bảng này tên là EMP và đang có hơn 1 triệu 100K bản ghi. Ứng dụng của tôi thường xuyên phải thực hiện câu lệnh kiểm tra tổng số nhân viên trong bảng EMP SELECT count(*) FROM EMP

Kết quả khi thực hiện 3 lần liên tiếp của câu lệnh trên (khi chưa sử dụng bất kỳ kỹ thuật tối ưu nào) như sau: Lần 1: Thực hiện mất thời gian 4.84 giây Lần 2: Thực hiện mất thời gian 4.66 giây Lần 3: Thực hiện mất thời gian 5.96 giây

Bây giờ tôi sử dụng HINT RESULT CACHE để tối ưu câu lệnh trên. Câu lệnh khi thêm RESULT CACHE sẽ như sau SELECT /*+ RESULT_CACHE */ count(*) FROM EMP;

Kết quả thực hiện sau khi sử dụng RESULT CACHE như sau Lần 1: Thực hiện mất 4.82 giây Lần 2: Thực hiện mất 0 giây (thực ra là đơn vị mili second nhưng thấp quá nên hệ thống ghi nhận ~0s ) Lần 3: Cũng 0 giây Lần 4, 5, 6 và các lần sau: Cũng 0 giây

Như vậy:

Khi thực hiện thêm HINT RESULT CACHE, ở lần thực hiện đầu tiên, thời gian thực hiện của câu lệnh gần như không có gì khác biệt so với trước đây.
Từ lần chạy thứ 2 trở đi, các thời gian thực hiện chỉ mất ~0s (đã tăng cực kỳ "choáng"), bản chất vì hệ thống đã lưu sẵn kết quả và trả ra ngay lập tức.

Thử nghiệm xem các phiên làm việc khác nhau thì RESULT CACHE có đem lại kết quả hay không? Tôi thử nghiệm sử dụng 2 session khác nhau, sử dụng 2 ứng dụng khác nhau để kết nối vào cơ sở dữ liệu. Cả 2 session đều thực hiện câu lệnh

SELECT /*+ RESULT_CACHE */ count(*) FROM EMP;

Câu lệnh này đã được lưu kết quả từ phần thử nghiệm phía trên. Tuy nhiên 2 session mới là 2 phiên làm việc độc lập. Kết quả cho thấy RESULT CACHE vẫn hiệu quả với 2 phiên làm việc với, thời gian thực thi trả ra ~0 giây. Bản chất bởi vì RESULT CACHE đã lưu kết quả trên phần bộ nhớ dùng chung của toàn bộ Database Instance, tất cả các phiên làm việc với cùng câu lệnh trên đều có thể nhận được lợi thế trả ra kết quả ngay lập tức.

6. Vậy trong trường hợp nào chúng ta nên áp dụng RESULT CACHE

HINT RESULT CACHE thường được áp dụng trong các trường hợp truy vấn SQL có tính chất lặp lại và dữ liệu không thay đổi nhiều. Ví dụ như các truy vấn hiển thị các dữ liệu thống kê, báo cáo hoặc truy vấn các bảng dữ liệu không thay đổi thường xuyên.

7. Nếu bạn muốn hiểu tường tận tất cả các kỹ năng tối ưu SQL tối ưu cơ sở dữ liệu

Khi bạn tham gia chương trình học “Từ điển tối ưu 100x hiệu năng” của Wecommit, bạn sẽ biết được** toàn bộ những kỹ thuật tối ưu cơ sở dữ liệu mà chúng tôi đã và đang áp dụng cho rất nhiều dự án tại ngân hàng, chứng khoán, các công ty viễn thông, các hệ thống tại bệnh viện**…

Không chỉ vậy, bạn còn được đồng hành và tư vấn HÀNG TUẦN, liên tục trong 1 năm, bạn sẽ cảm thấy vô cùng tự tin và khác biệt so với các đồng nghiệp của mình.

Hãy tìm hiểu chương trình tại đây:https://wecommit.com.vn/tu-dien-toi-uu-100x-hieu-nang/

Chi tiết giải pháp được tôi chia sẻ tại bài viết dành riêng cho nhóm học viên đặc quyền chương trình Từ điển tối ưu 100x hiệu năng. Bạn có thể xem chi tiết chương trình tại đây.