Thanaoscar

Bài 2: Hiểu về Execution Plan

Thana Oscar's photo
Thana Oscar
·

7 min read

Execution Plan là một trong những công cụ quan trọng nhất để phân tích và tối ưu hóa hiệu suất của các truy vấn SQL. Nó cung cấp cái nhìn chi tiết về cách cơ sở dữ liệu (database engine) thực thi một truy vấn, từ đó giúp bạn xác định các điểm nghẽn (bottleneck) và đề xuất các giải pháp tối ưu hóa. Bài viết này sẽ đi sâu vào khái niệm Execution Plan, các thành phần của nó, và cách phân tích để tối ưu hóa truy vấn.

2.1. Execution Plan là gì?

2.1.1. Định nghĩa và mục đích

  • Execution Plan là một bản đồ chi tiết mô tả cách database engine thực thi một truy vấn SQL. Nó bao gồm các bước (operations) mà database thực hiện để trả về kết quả, chẳng hạn như quét bảng (table scan), sử dụng chỉ mục (index scan), hoặc thực hiện các phép join.

  • Mục đích: Execution Plan giúp bạn hiểu rõ cách database xử lý truy vấn, từ đó xác định các vấn đề về hiệu suất và đề xuất các cách tối ưu hóa. Ví dụ, nếu Execution Plan cho thấy một truy vấn đang sử dụng Table Scan thay vì Index Scan, bạn có thể thêm index để cải thiện hiệu suất.

2.1.2. Cách xem Execution Plan trong các hệ quản trị CSDL

  • SQL Server:

    • Sử dụng lệnh SET SHOWPLAN_TEXT ON để xem Execution Plan dưới dạng văn bản.

    • Sử dụng lệnh SET SHOWPLAN_XML ON để xem Execution Plan dưới dạng XML.

    • Trong SQL Server Management Studio (SSMS), bạn có thể bật Execution Plan bằng cách nhấn Ctrl + M hoặc chọn "Include Actual Execution Plan" trước khi chạy truy vấn.

  • MySQL:

    • Sử dụng lệnh EXPLAIN trước truy vấn để xem Execution Plan. Ví dụ: EXPLAIN SELECT * FROM employees WHERE salary > 50000;.

    • MySQL cũng hỗ trợ EXPLAIN ANALYZE để xem thêm thông tin về thời gian thực thi.

  • PostgreSQL:

    • Sử dụng lệnh EXPLAIN để xem Execution Plan. Ví dụ: EXPLAIN SELECT * FROM orders WHERE order_date > '2023-01-01';.

    • Sử dụng EXPLAIN ANALYZE để xem cả thông tin thực thi thực tế.

  • Oracle:

    • Sử dụng lệnh EXPLAIN PLAN FOR để tạo Execution Plan. Ví dụ: EXPLAIN PLAN FOR SELECT * FROM customers WHERE age > 30;.

    • Sau đó, sử dụng SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY); để xem kết quả.

2.2. Các thành phần trong Execution Plan

Execution Plan bao gồm nhiều thành phần khác nhau, mỗi thành phần đại diện cho một bước trong quá trình thực thi truy vấn. Dưới đây là các thành phần chính:

2.2.1. Table Scan vs Index Scan

  • Table Scan:

    • Định nghĩa: Khi database engine quét toàn bộ bảng (table) để tìm dữ liệu phù hợp với điều kiện truy vấn.

    • Khi nào xảy ra: Khi không có index hoặc index không được sử dụng.

    • Ưu điểm: Đơn giản, không cần index.

    • Nhược điểm: Tốn nhiều tài nguyên và thời gian, đặc biệt với bảng lớn.

    • Ví dụ: Nếu bạn chạy truy vấn SELECT * FROM employees WHERE department = 'Sales'; và không có index trên cột department, database sẽ thực hiện Table Scan.

  • Index Scan:

    • Định nghĩa: Khi database engine sử dụng index để tìm kiếm dữ liệu.

    • Khi nào xảy ra: Khi có index phù hợp với điều kiện truy vấn.

    • Ưu điểm: Hiệu suất cao, đặc biệt với bảng lớn.

    • Nhược điểm: Cần có index phù hợp và duy trì index tốn tài nguyên.

    • Ví dụ: Nếu bạn có index trên cột department, truy vấn SELECT * FROM employees WHERE department = 'Sales'; sẽ sử dụng Index Scan.

2.2.2. Join Types (Nested Loop, Hash Join, Merge Join)

  • Nested Loop Join:

    • Định nghĩa: Phù hợp cho các bảng nhỏ hoặc khi một bảng có số lượng bản ghi ít. Database engine lặp qua từng bản ghi của bảng thứ nhất và tìm kiếm bản ghi phù hợp trong bảng thứ hai.

    • Ưu điểm: Hiệu quả với bảng nhỏ.

    • Nhược điểm: Kém hiệu quả với bảng lớn.

    • Ví dụ: Nếu bạn join hai bảng nhỏ employees và departments, database có thể sử dụng Nested Loop Join.

  • Hash Join:

    • Định nghĩa: Phù hợp cho các bảng lớn. Database engine tạo một bảng băm (hash table) từ một bảng và sử dụng nó để tìm kiếm trong bảng còn lại.

    • Ưu điểm: Hiệu quả với bảng lớn.

    • Nhược điểm: Tốn nhiều bộ nhớ để tạo hash table.

    • Ví dụ: Khi join hai bảng lớn orders và customers, database có thể sử dụng Hash Join.

  • Merge Join:

    • Định nghĩa: Phù hợp khi cả hai bảng đã được sắp xếp theo cột join. Database engine so sánh từng bản ghi của hai bảng theo thứ tự.

    • Ưu điểm: Hiệu quả với bảng đã sắp xếp.

    • Nhược điểm: Cần dữ liệu đã được sắp xếp.

    • Ví dụ: Nếu bạn join hai bảng orders và order_details đã được sắp xếp theo order_id, database có thể sử dụng Merge Join.

2.2.3. Cost và thời gian thực thi

  • Cost:

    • Định nghĩa: Là một chỉ số ước tính về tài nguyên mà database engine cần để thực thi một bước trong Execution Plan.

    • Ý nghĩa: Cost càng cao, bước đó càng tốn nhiều tài nguyên.

    • Ví dụ: Nếu một bước có cost là 100 và một bước khác có cost là 10, bước đầu tiên sẽ tốn nhiều tài nguyên hơn.

  • Thời gian thực thi:

    • Định nghĩa: Là thời gian thực tế mà database engine cần để thực thi một bước.

    • Ý nghĩa: Thời gian này có thể khác với cost vì nó phụ thuộc vào nhiều yếu tố như tải hệ thống và cấu hình phần cứng.

    • Ví dụ: Một bước có cost thấp nhưng thời gian thực thi cao có thể do tải hệ thống cao.

2.3. Phân tích Execution Plan để tối ưu

Phân tích Execution Plan giúp bạn xác định các điểm nghẽn và đề xuất các cách tối ưu hóa truy vấn. Dưới đây là các bước phân tích chi tiết:

2.3.1. Xác định điểm nghẽn (bottleneck)

  • Table Scan: Nếu Execution Plan cho thấy nhiều Table Scan, bạn nên xem xét thêm index để cải thiện hiệu suất.

  • High Cost Operations: Các bước có cost cao thường là điểm nghẽn. Bạn có thể tối ưu hóa bằng cách viết lại query hoặc thêm index.

2.3.2. Đánh giá hiệu quả của index

  • Index Usage: Kiểm tra xem index có được sử dụng hiệu quả không. Nếu index không được sử dụng, bạn có thể cần viết lại query hoặc thay đổi index.

  • Index Selectivity: Index có độ chọn lọc cao (high selectivity) thường hiệu quả hơn. Ví dụ, index trên cột có nhiều giá trị duy nhất (unique values) sẽ hiệu quả hơn index trên cột có ít giá trị duy nhất.

2.3.3. Đọc các chỉ số “Actual vs. Estimated Rows”, “Warnings”

  • Actual vs. Estimated Rows: So sánh số lượng bản ghi thực tế (actual rows) với số lượng bản ghi ước tính (estimated rows). Nếu có sự chênh lệch lớn, database engine có thể đã đưa ra quyết định không tối ưu.

  • Warnings: Các cảnh báo trong Execution Plan có thể chỉ ra các vấn đề tiềm ẩn, chẳng hạn như missing index hoặc type conversion.

2.3.4. Ví dụ trực quan: So sánh Execution Plan trước và sau khi tối ưu

  • Trước khi tối ưu: Một truy vấn có Execution Plan với nhiều Table Scan và cost cao.

  • Sau khi tối ưu: Thêm index và viết lại query, Execution Plan cho thấy Index Scan được sử dụng và cost giảm đáng kể.

Kết luận

Execution Plan là một công cụ mạnh mẽ giúp bạn hiểu cách database thực thi truy vấn và xác định các điểm nghẽn. Bằng cách phân tích Execution Plan, bạn có thể tối ưu hóa truy vấn để cải thiện hiệu suất và giảm thiểu tài nguyên sử dụng. Trong các bài tiếp theo, chúng ta sẽ đi sâu vào các kỹ thuật tối ưu hóa cụ thể, chẳng hạn như sử dụng index hiệu quả và tối ưu câu lệnh JOIN.

Từ vựng chuyên môn (Glossary)

  • Execution Plan: Kế hoạch thực thi.

  • Table Scan: Quét toàn bộ bảng.

  • Index Scan: Quét chỉ mục.

  • Nested Loop Join: Join lồng nhau.

  • Hash Join: Join băm.

  • Merge Join: Join trộn.

  • Cost: Chi phí.

  • Bottleneck: Điểm nghẽn.

  • Index Selectivity: Độ chọn lọc của chỉ mục.

  • Actual Rows: Số lượng bản ghi thực tế.

  • Estimated Rows: Số lượng bản ghi ước tính.

  • Warnings: Cảnh báo.

sql optimize