SQL Execution Plan là gì? Cách để tối ưu câu lệnh trong SQL

Chiến lược thực thi của câu lệnh SQL (SQL Execution Plan hay Query Plan) là tập hợp các bước mà cơ sở dữ liệu cần thực hiện để chạy một truy vấn trên SQL. Vậy một Execution plan thường bao gồm những thành phần và nội dung gì? Tại sao chúng ta nên tìm hiểu về khái niệm này và chúng có vai trò gì trong truy vấn? Để trả lời những câu hỏi trên, hãy cùng mình tìm hiểu trong bài viết dưới đây.

SQL Execution Plan là gì?

Bất kỳ một hệ quản trị cơ sở dữ liệu nào muốn thực thi được câu lệnh SQL thì điều mà hệ quản trị đó cần biết chính là: “cụ thể sẽ cần làm các bước gì, sẽ phải lấy dữ liệu theo cách thức nào, thực hiện phép join như thế nào, cần thực hiện bước nào trước, bước nào sau”. Quá trình một hệ quản trị tìm ra câu trả lời cho các cho câu hỏi trên được gọi là: Chiến lược thực thi của câu lệnh SQL hoặc thuật ngữ chuyên ngành gọi là SQL Execution Plan.

Estimated Execution Plan và Actual Execution Plan là hai khái niệm quan trọng trong việc thực hiện và tối ưu hóa câu truy vấn trong cơ sở dữ liệu.

Estimated Execution Plan

Khi bạn thực thi một câu truy vấn trong cơ sở dữ liệu, trình tối ưu hóa câu truy vấn sẽ tạo ra một kế hoạch thực hiện ước lượng trước khi thực sự thực hiện truy vấn. Kế hoạch này được xác định dựa trên thống kê và thông tin về cấu trúc của câu truy vấn.

Estimated Execution Plan thường được biểu diễn bằng cách sử dụng một số hình vẽ, biểu đồ hoặc bảng liệt kê các bước thực hiện và phương pháp được ước lượng.

Actual Execution Plan

Sau khi câu truy vấn đã được thực hiện, hệ quản trị cơ sở dữ liệu sẽ tạo ra một kế hoạch thực hiện thực tế dựa trên cách thức mà truy vấn được thực hiện trong môi trường thực tế.

Actual Execution Plan cũng có thể được biểu diễn bằng cách sử dụng các biểu đồ, bảng, hoặc thông tin chi tiết về mỗi bước trong quá trình thực hiện.

Sự khác biệt chính:

– Estimated Execution Plan: Dựa trên dự đoán của trình tối ưu hóa truy vấn trước khi truy vấn thực sự được chạy.

– Actual Execution Plan: Phản ánh thực tế sau khi truy vấn đã được thực hiện, dựa trên thông tin thực tế như số lượng bản ghi, thời gian thực hiện và điều kiện môi trường.

Việc so sánh giữa hai Execution Plan có thể giúp bạn hiểu rõ hơn về hiệu suất của câu truy vấn, giúp việc tối ưu hóa truy vấn dễ dàng hơn, để đảm bảo câu truy vấn được thực thi hiệu quả nhất.

Những thành phần của SQL Execution Plan

Khi thực hiện tạo Execution Plan, chúng ta sẽ được sơ đồ như hình bên dưới.

Đối với những truy vấn đơn giản, Estimated Execution Plan thường giống với Actual Execution Plan. Trong phạm vi bài viết này, chúng ta sẽ tìm hiểu các thành phần của Actual Execution Plan.

Trong Execution Plan được mô tả như hình, nếu bạn di con trỏ qua các thành phần, bạn có thể xem được các số liệu thống kê chi tiết cho từng hoạt động và thành phần được hiển thị trong kế hoạch thực hiện. Kế hoạch được diễn giải từ phải sang trái và từ trên xuống dưới

Các thành phần của Execution Plan có thể chia như sau:

1. Clustered Index Scan
2. Data Flow from Clustered Index Scan
3. Sort Operator
4. Data Flow from Sort Operator
5. Select Operator

Trong phạm vi bài viết này, chúng ta sẽ tìm hiểu về các thành phần của Clustered Index Scan.

• Physical Operation: Đây là toán tử thực hiện thao tác theo chỉ dẫn của các toán tử logic. Tất cả các toán tử vật lý thường là đối tượng thực hiện một thao tác. Một số ví dụ là: Clustered Index Scan, Index Seek,…
• Logical Operation: Các toán tử này mô tả phép toán đại số thực tế được sử dụng để xử lý truy vấn. Ví dụ: Right Anti Semi Join, Hash Join,…
• Actual Execution Mode: Đây là chế độ thực thi thực tế được hệ quản trị cơ sở dữ liệu sử dụng để thực hiện truy vấn. Ví dụ: Row, Batch
• Estimated Execution Mode: Chế độ này tương tự như Actual Execution Mode nhưng hiển thị giá trị ước tính
• Storage: Thông số này cho biết cách trình tối ưu hóa truy vấn sẽ lưu trữ các kết quả đang được truy vấn trích xuất
• Actual Number of Rows Read: Thông số này trả về tổng số bản ghi đang được toán tử đọc từ chỉ mục của bảng (Table Index)
• Actual Number of Rows for All Executions: Thông số này cho chúng ta biết tổng số bản ghi đã được trả về dựa trên điều kiện trong mệnh đề WHERE.
• Actual Number of Batches: Nếu chế độ thực hiện truy vấn là “Batch” thì đây là thông số sẽ liệt kê số lô (batch) được thực thi để trả về kết quả.
• Estimated I/O Cost: Thông số này cho chúng ta biết chi phí của các hoạt động đầu vào/đầu ra của tập kết quả
• Estimated Operator Cost: Thông tin này mang tính tương đối, sẽ phụ thuộc vào tài nguyên của máy chủ, ví dụ: CPU, RAM,…
• Estimated CPU Cost: Chi phí mà CPU sẽ phải chịu để xử lý hoạt động truy vấn
• Estimated Subtree Cost: Đây là thông số quan trọng: phản ánh chi phí thực thi câu truy vấn, để tận dụng thông tin này hiệu quả nhất, chúng ta thực hiện đọc từ phải sang trái và từ trên xuống dưới trong một Execution Plan
• Number of Executions: Cho chúng ta biết về số lần thực thi mà trình tối ưu hóa có thể xử lý
• Estimated Number of Executions: Điều này cũng tương tự như Number of Executions chỉ là giá trị ước tính
• Estimated Number of Rows: Số hàng mà trình tối ưu hóa cho rằng sẽ được trả về từ câu truy vấn
• Estimated Number of Rows to be Read: Số lượng hàng mà trình tối ưu hóa cho rằng sẽ được người vận hành sẽ đọc khi nhận được kết quả trả về
• Estimated Row Size: Kích thước lưu trữ mỗi hàng
• Actual Rebinds: Số lần phải thực hiện việc đánh giá lại đối tượng để xử lý toán tử
• Actual Rewinds: Thông số này cho biết liệu có bất kỳ thay đổi nào trong các giá trị tương quan của đối tượng đang được xử lý hay không
• Ordered: Thuộc tính này xác định xem tập dữ liệu có được thực hiện ở trạng thái được sắp xếp hay không
• Node ID: Đây là việc tự động gán một số theo thứ tự, được gán trong Execution Plan và đọc từ phải sang trái, từ trên xuống dưới

Cách lấy được thông tin SQL Execution Plan

Sử dụng chức năng trên thanh công cụ

Cách sử dụng Microsoft SQL Server Management Studio để xem thông tin SQL Execution Plan

Hiển thị Estimated Execution Plan: Trên thanh công cụ, chọn nút Display Estimated Execution Plan. Kế hoạch thực hiện ước tính được hiển thị ở tab Execution Plan.

Hiển thị Actual Execution Plan: Trên thanh công cụ, chọn nút Include Actual Execution Plan.

Sử dụng câu truy vấn để truy xuất thông tin Database

• Đối với Cơ sở dữ liệu Oracle: Đảm bảo đã bật Autotrace khi đăng nhập vào SQLplus
  • Ví dụ: SQL> set autotrace traceonly
• Đối với Cơ sở dữ liệu PostgreSQL: Bạn thêm từ khóa Explain ở trước câu lệnh SQL
  • Ví dụ : postgres=# explain select * from customer where id=12;
• Đối với Cơ sở dữ liệu MySQL: Bạn thêm từ khóa Explain ở trước câu lệnh SQL
  • Ví dụ: mysql> explain select * from customers where customerNumber=103;

Vì sao SQL Execution Plan có vai trò quan trọng

SQL Execution Plan đóng một vai trò quan trọng trong việc đánh giá hiệu suất của cơ sở dữ liệu và quản lý các câu truy vấn

Biết được cách câu truy vấn thực hiện

Execution Plan cung cấp một kế hoạch chi tiết về cách mà hệ quản trị cơ sở dữ liệu thực hiện một câu truy vấn. Điều này giúp người dung hiểu rõ quy trình thực hiện và làm thế nào dữ liệu được truy cập.

Có thể tối ưu hiệu suất

Bằng cách xem Execution Plan, có thể đánh giá hiệu suất của câu truy vấn. Có thể xác định được những phần của câu truy vấn mà có thể được tối ưu hóa để cải thiện thời gian thực hiện và tài nguyên sử dụng.

Có thông tin về các vấn đề liên quan đến hiệu suất

Nếu có vấn đề về hiệu suất, Execution Plan có thể giúp xác định nơi mà câu truy vấn đang có hiện tượng “bottleneck”. Bằng cách phân tích các phần tử có trong Execution Plan, có thể xác định được điểm yếu và thực hiện các biện pháp cần thiết để cải thiện hiệu suất.

Đưa ra được các dự đoán, ước lượng

Execution Plan thường kèm theo các thông tin về ước lượng số lượng bản ghi, độ lớn dữ liệu. Điều này giúp người quản trị dự đoán cách câu truy vấn sẽ ảnh hưởng đến tài nguyên và có thể thực hiện các điều chỉnh trước để tránh các vấn đề hiệu suất.

Debugging và tối ưu hóa Execution Plan:

Nếu có lỗi hoặc vấn đề trong câu truy vấn, Execution Plan cung cấp một cách để phân tích và debug vấn đề. Bằng cách theo dõi và đánh giá kế hoạch thực hiện, người quản trị có thể xác định nguyên nhân của vấn đề và áp dụng các biện pháp sửa lỗi.

Tóm lại, SQL Execution Plan là một công cụ quan trọng để hiểu và tối ưu hóa hiệu suất của câu truy vấn, giúp cải thiện trải nghiệm người dùng và đảm bảo tài nguyên cơ sở dữ liệu được sử dụng hiệu quả.

Tải SQL tại đây: SQL Server Downloads | Microsoft

Tham khảo hướng dẫn cài đặt SQL Server 2022 tại đây

My Trần Hà