Bài 12: Đọc Hiểu Cost – Xác Định Bottleneck Trong Query

"Cost không chỉ là con số – nó là tấm gương phản chiếu cách database xử lý query!"

1. Vấn Đề: Query JOIN 3 Bảng Chậm Dù Đã Có Index Đơn

Scenario:
Bạn có 3 bảng:

orders (1 triệu đơn hàng, khóa chính order_id).
order_details (5 triệu dòng, khóa ngoại order_id, product_id).
products (10,000 sản phẩm, khóa chính product_id).

Thực hiện query:

SELECT o.order_date, p.product_name, SUM(od.quantity)  
FROM orders o  
JOIN order_details od ON o.order_id = od.order_id  
JOIN products p ON od.product_id = p.product_id  
WHERE o.order_date BETWEEN '2023-01-01' AND '2023-12-31'  
GROUP BY o.order_date, p.product_name;

Kết quả: Thời gian thực thi ~8 giây.

2. Phân Tích Execution Plan

Bước 1: Chạy `EXPLAIN ANALYZE` (PostgreSQL)

HashAggregate (cost=250000.00..260000.00 rows=50000 width=40) 
              (actual time=7500.123..7800.456 rows=12000 loops=1)  
  Group Key: o.order_date, p.product_name  
  -> Nested Loop (cost=0.00..200000.00 rows=1000000 width=40) 
                 (actual time=0.123..6500.789 rows=500000 loops=1)  
      -> Seq Scan on orders o (cost=0.00..15000.00 rows=300000 width=8) 
                              (actual time=0.012..1200.123 rows=300000 loops=1)  
            Filter: (order_date BETWEEN '2023-01-01' AND '2023-12-31')  
      -> Index Scan using idx_order_details_order_id on order_details od 
                           (cost=0.42..0.60 rows=2 width=16) 
                           (actual time=0.002..0.003 rows=2 loops=300000)  
            Index Cond: (order_id = o.order_id)  
  -> Hash Join (cost=5000.00..10000.00 rows=100000 width=32) 
               (actual time=500.123..1200.456 rows=500000 loops=1)  
        Hash Cond: (od.product_id = p.product_id)  
        -> Seq Scan on order_details od (cost=0.00..20000.00 rows=500000 width=16)  
        -> Hash (cost=4000.00..4000.00 rows=10000 width=20)  
              -> Seq Scan on products p (cost=0.00..4000.00 rows=10000 width=20)

Bước 2: Giải Mã Cost và Xác Định Bottleneck

Startup Cost vs Total Cost:
- HashAggregate: Startup cost = 250,000 (chi phí khởi tạo bộ nhớ cho aggregation).
- Total Cost = 260,000 – tổng chi phí dự kiến.
Node Tốn Kém Nhất:
- Nested Loop (cost=200,000): Quét 300,000 dòng orders và thực hiện 300,000 lần Index Scan trên order_details.
- Hash Join giữa order_details và products (cost=10,000): Tạo hash table từ products.

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

Full Scan trên orders: Không có index trên order_date.
Hash Join không hiệu quả: order_details được quét toàn bộ (Seq Scan) thay vì sử dụng index trên product_id.
Nested Loop chậm: Mặc dù order_details có index trên order_id, việc lặp 300,000 lần vẫn tốn tài nguyên.

3. Tối Ưu: Thêm Index và Điều Chỉnh Join Strategy

Bước 1: Thêm Index Trên Cột Sử Dụng Trong WHERE và JOIN

-- Index cho WHERE (orders.order_date)  
CREATE INDEX idx_orders_date ON orders(order_date);  

-- Composite Index cho JOIN (order_details.order_id + product_id)  
CREATE INDEX idx_order_details_composite ON order_details(order_id, product_id);

Bước 2: Execution Plan Sau Khi Tạo Index (PostgreSQL)

HashAggregate (cost=80000.00..85000.00 rows=50000 width=40) 
              (actual time=2500.123..2800.456 rows=12000 loops=1)  
  Group Key: o.order_date, p.product_name  
  -> Hash Join (cost=5000.00..60000.00 rows=500000 width=40) 
               (actual time=200.123..1800.789 rows=500000 loops=1)  
        Hash Cond: (od.product_id = p.product_id)  
        -> Nested Loop (cost=0.42..40000.00 rows=500000 width=16) 
                       (actual time=0.032..1000.123 rows=500000 loops=1)  
              -> Index Scan using idx_orders_date on orders o 
                           (cost=0.42..5000.00 rows=300000 width=8)  
                    Index Cond: (order_date BETWEEN '2023-01-01' AND '2023-12-31')  
              -> Index Scan using idx_order_details_composite on order_details od 
                           (cost=0.00..0.12 rows=2 width=16)  
                    Index Cond: (order_id = o.order_id)  
        -> Hash (cost=4000.00..4000.00 rows=10000 width=20)  
              -> Seq Scan on products p (cost=0.00..4000.00 rows=10000 width=20)

Kết Quả:

Index Scan thay thế Full Scan:
- orders sử dụng idx_orders_date → Giảm cost từ 15,000 → 5,000.
- order_details sử dụng composite index → Tránh Hash Join không cần thiết.
Giảm Execution Time: Từ 8 giây → 2.8 giây (~65% cải thiện).

4. Tổng Kết

Chỉ Số	Trước Khi Tối Ưu	Sau Khi Tối Ưu
Total Cost	260,000	85,000
Execution Time	8000 ms	2800 ms
Phương Pháp JOIN	Nested Loop + Hash	Hash Join Hiệu Quả

Lý Do Hiệu Quả:

Index trên order_date giúp lọc nhanh dữ liệu trong WHERE.
Composite index trên order_details giúp tận dụng Index-Only Scan, giảm I/O.

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

Dataset Mẫu:

CREATE TABLE employees (  
    emp_id INT PRIMARY KEY,  
    dept_id INT,  
    hire_date DATE  
);  
-- Insert 100,000 dòng dữ liệu

Yêu Cầu:

Chạy query:

 SELECT dept_id, COUNT(*)  
 FROM employees  
 WHERE hire_date BETWEEN '2020-01-01' AND '2023-12-31'  
 GROUP BY dept_id;

Phân tích Execution Plan và xác định bottleneck.
Thêm index phù hợp để giảm cost.

Câu Hỏi:

Tại sao đôi khi database vẫn chọn Full Scan thay vùng Index Scan dù đã có index?

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

Khi Nào Cost Không Phản Ánh Đúng Thực Tế?

Thống kê lỗi thời: Ví dụ, bảng có 1 triệu dòng nhưng thống kê chỉ ghi nhận 100,000 dòng → database chọn plan sai.
```
  ANALYZE orders; -- Cập nhật thống kê (PostgreSQL)
```
Parameterized Query: Khi sử dụng prepared statement với tham số không xác định, database có thể chọn plan tối ưu sai.

Khác Biệt Giữa PostgreSQL và MySQL

PostgreSQL: Ước tính cost dựa trên CPU và I/O, chi tiết hơn.
MySQL: Cost model đơn giản hơn, thường ưu tiên Index Scan nếu có.

Chiến Lược "Hướng Dẫn" Database Chọn Plan

PostgreSQL: Sử dụng SET enable_nestloop = off; để buộc sử dụng Hash Join.
MySQL: Dùng FORCE INDEX (index_name) để chỉ định index cụ thể.

Kết Luận

Hiểu cost giúp bạn "nhìn thấu" cách database xử lý query. Trong ví dụ này, việc thêm index phù hợp và tối ưu JOIN strategy đã giảm 65% thời gian thực thi. Trong bài tiếp theo (Bài 13), chúng ta sẽ đi sâu vào các loại index và trường hợp áp dụng cụ thể!