Query Optimizer và Cost Model - Bên trong “bộ não” của database

Câu hỏi gây bực bội nhất: “Tại sao database không dùng index của tôi?” Để debug được, phải hiểu quy trình ra quyết định.

Quy trình thực thi query: 4 bước

1. PARSE          → 2. INITIAL PLAN      → 3. OPTIMIZE        → 4. EXECUTE
   Phân tích cú pháp   Tạo plan cơ bản        Tìm plan tốt hơn      Chạy plan
                       (full table scan)      (dùng index?)         tốt nhất

Điểm mấu chốt ở bước 2-3: database luôn bắt đầu với plan “full table scan” (vì luôn chạy được, không cần index). Sau đó optimizer kiểm tra có plan nào tốt hơn không. Không tìm được plan tốt hơn → giữ full table scan. Index bị “bỏ qua” không phải vì database không thấy index, mà vì nó đánh giá full scan nhanh hơn.

Cost Model: cách tính chi phí

Database lưu thống kê về mỗi cột (distinct values, histogram, tổng số row…) và tính chi phí ước tính cho từng plan:

Ví dụ: bảng 10,000 rows
 
Plan A: Full table scan
  - Đọc sequential: 10,000 rows × 0.01 cost/row = 100
 
Plan B: Dùng index, match 5,000 rows
  - Index lookup:           5,000 × 0.005 = 25
  - Load rows (random I/O): 5,000 × 0.04  = 200
  - Tổng: 225
 
Plan A (100) < Plan B (225) → Full table scan THẮNG!

Điểm mấu chốt: O rất nhiều. Khi query match khoảng 10-30% rows trở lên, full table scan thường nhanh hơn - và database ĐÚNG khi bỏ index.

Bảng nhỏ (100-200 rows)

Database thường full scan luôn: overhead tra cứu index (đọc internal nodes → leaf → nhảy đến table) lớn hơn lợi ích so với đọc thẳng 100-200 rows. Hành vi bình thường, không cần lo - và là lý do query nhanh trên local (vài trăm record) nhưng chậm trên production (vài triệu record).

Checklist khi index bị bỏ qua

1. Chạy EXPLAIN xem plan thực tế
2. Column Transformation - cột bị biến đổi → index “mù”
3. Type Mismatch và Implicit Cast - so sánh khác kiểu
4. Statistics và ANALYZE - thống kê cũ → quyết định sai
5. Low-Cardinality Column và Index - match quá nhiều row → DB đúng khi bỏ index
6. Index Selection - Xung đột Filter và Sort - DB chọn index khác
7. Invisible index (MySQL) - index bị đánh dấu ẩn, xem Unused Index

Liên quan

• EXPLAIN - công cụ nhìn vào quyết định của optimizer
• Nested Loop Join - optimizer cũng chọn thứ tự join