Bài 11: Bắt Đầu Với EXPLAIN – Hiểu Cấu Trúc Execution Plan

Tối ưu hóa SQL bắt đầu từ việc đọc hiểu Execution Plan!

---

1. Vấn Đề: Query Chậm Khi Truy Vấn Bảng Lớn

Scenario:
Bạn có một bảng users chứa 1 triệu dòng, thực hiện query đơn giản:

SELECT * FROM users WHERE age > 30;

Kết quả trả về chậm (~2 giây). Tại sao? Làm thế nào để xác định nguyên nhân?

---

2. Phân Tích Execution Plan

Bước 1: Chạy EXPLAIN và EXPLAIN ANALYZE

• PostgreSQL:

    EXPLAIN ANALYZE SELECT * FROM users WHERE age > 30;
  

  Kết quả:

    Seq Scan on users  (cost=0.00..18334.00 rows=500000 width=60) (actual time=0.012..1250.456 rows=480000 loops=1)  
      Filter: (age > 30)  
      Rows Removed by Filter: 520000  
    Planning Time: 0.102 ms  
    Execution Time: 1500.234 ms
  

• MySQL:

    EXPLAIN FORMAT=TREE SELECT * FROM users WHERE age > 30;
  

  Kết quả:

    -> Filter: (users.age > 30)  (cost=100000.00 rows=500000)  
        -> Table scan on users  (cost=100000.00 rows=1000000)
  

• SQL Server:

    SET SHOWPLAN_TEXT ON;  
    GO  
    SELECT * FROM users WHERE age > 30;  
    GO
  

  Kết quả:

    |-- Table Scan(OBJECT:([db].[dbo].[users]), WHERE:([age]>30))
  

Bước 2: Giải Thích Các Thành Trong Execution Plan

• Node: Các bước thực thi (ví dụ: Seq Scan, Index Scan).

• Cost:

  • Startup Cost (PostgreSQL): Chi phí khởi tạo (0.00 trong ví dụ).

  • Total Cost: Tổng chi phí dự kiến (18334.00).

• Rows: Số dòng ước lượng (500,000) và thực tế (480,000).

• Width: Kích thước trung bình mỗi dòng (60 bytes).

• Actual Time: Thời gian thực thi từng node (1250.456 ms).

Phát Hiện Vấn Đề:

• Full Table Scan (Seq Scan/Table Scan): Query quét toàn bộ 1 triệu dòng → Tốn thời gian.

• Filter: Loại bỏ 520,000 dòng không thỏa điều kiện age > 30.

---

3. Tối Ưu: Thêm Index Để Loại Bỏ Full Scan

Bước 1: Tạo B-tree Index Trên Cột age

CREATE INDEX idx_users_age ON users(age);

Bước 2: Chạy Lại EXPLAIN ANALYZE (PostgreSQL)

Index Scan using idx_users_age on users  (cost=0.42..15000.00 rows=500000 width=60) (actual time=0.032..800.123 rows=480000 loops=1)  
  Index Cond: (age > 30)  
Planning Time: 0.205 ms  
Execution Time: 820.456 ms

Kết Quả:

• Index Scan: Thay vì quét toàn bộ bảng, database sử dụng index để tìm dòng phù hợp.

• Giảm Execution Time: Từ 1500 ms → 820 ms (~45% cải thiện).

---

4. Tổng Kết

Lý Do Hiệu Quả:

• Index giúp database nhảy thẳng đến vị trí dòng có age > 30, không cần quét toàn bộ bảng.

• Giảm I/O và CPU do xử lý ít dòng hơn.

---

5. Bài Tập Thực Hành

Dataset Mẫu:

CREATE TABLE products (  
    id SERIAL PRIMARY KEY,  
    name VARCHAR(100),  
    price INT,  
    category_id INT  
);  
-- Insert 500,000 dòng dữ liệu ngẫu nhiên

Yêu Cầu:

1. Chạy query:

    SELECT * FROM products WHERE price > 100;
   

2. Sử dụng EXPLAIN để phân tích Execution Plan.

3. Tạo index trên cột price và so sánh kết quả.

Câu Hỏi:

• Tại sao EXPLAIN ANALYZE không hiển thị thời gian thực thi nếu query có INSERT/UPDATE?

---

6. Mở Rộng & Thảo Luận

Khi Nào Index Không Giúp Ích?

• Bảng nhỏ: Full Scan nhanh hơn sử dụng index.

• Dữ liệu không chọn lọc: Ví dụ WHERE status IN (1,2,3) (95% dòng thỏa điều kiện).

Cẩn Trọng Khi Tạo Index:

• Chi phí duy trì: Index làm chậm INSERT/UPDATE/DELETE.

• Covering Index: Thêm cột cần SELECT vào index để tránh truy cập bảng gốc.

    CREATE INDEX idx_users_age_covering ON users(age) INCLUDE (name, email);
  

Khác Biệt Giữa Các Hệ Quản Trị:

• PostgreSQL: Hỗ trợ EXPLAIN (ANALYZE, BUFFERS) để xem lượng dữ liệu đọc từ disk.

• MySQL: Mặc định sử dụng Index Scan nếu có index phù hợp.

---

Kết Luận

Hiểu Execution Plan qua EXPLAIN là bước cốt lõi để tối ưu SQL. Bắt đầu từ việc xác định Full Scan và áp dụng index phù hợp, bạn đã giảm 45% thời gian thực thi. Trong các bài tiếp theo, chúng ta sẽ đi sâu vào các loại index, cách tối ưu JOIN và subquery!