Composite Index và Nguyên tắc Phễu (Nguyên tắc 3: Từ trái sang phải)
Nguyên tắc quan trọng nhất và dễ hiểu sai nhất. Multi-column index (composite index) là nơi mang lại cải thiện performance lớn nhất. Hình dung nó như cái phễu lọc dữ liệu từ trái sang phải.
Cách composite index được sắp xếp
Index (country, lastname, firstname): sort theo country trước, trong mỗi country sort theo lastname, trong mỗi lastname sort theo firstname.
│ JP │ Sato │ Kenji │ Trong mỗi country, lastname được sorted.
│ JP │ Suzuki │ Yuki │ Nhưng nhìn TOÀN BỘ cột lastname thì
│ US │ Johnson │ Emily │ KHÔNG sorted (Sato, Suzuki, Johnson...)
│ US │ Smith │ Alice │ → Đây là lý do phải đi từ trái sang phải!
│ VN │ Nguyen │ An │ ← target
│ VN │ Nguyen │ Huy │ ← target
│ VN │ Tran │ Duc │WHERE country='VN' AND lastname='Nguyen' AND firstname='Huy'
Bước 1: country = 'VN' → Phễu thu hẹp: 4 entries (VN block)
Bước 2: lastname = 'Nguyen' → Phễu thu hẹp: 2 entries
Bước 3: firstname = 'Huy' → Phễu thu hẹp: 1 entry (chính xác!)Query nào dùng được index (country, lastname, firstname)?
✅ WHERE country='VN' AND lastname='Nguyen' AND firstname='Huy' -- 3/3 phễu
✅ WHERE country='VN' AND lastname='Nguyen' -- 2/3 phễu
✅ WHERE country='VN' -- 1/3 phễu
❌ WHERE lastname='Nguyen' -- bỏ qua cột đầu → 'Nguyen' rải rác khắp nơi
❌ WHERE firstname='Huy' -- bỏ qua cả 2 cột đầuCâu thần chú: “Từ trái sang phải, không bỏ qua cột.”
Bỏ qua cột GIỮA: vẫn dùng được, nhưng kém hiệu quả
Index (firstname, lastname, country) với WHERE firstname='Huy' AND country='VN': database fast lookup theo firstname, rồi scan toàn bộ block entries có firstname=‘Huy’ và filter country từng entry. Vẫn tốt hơn không có index (giới hạn được phạm vi scan, filter ngay trong index không cần load row).
Hiểu nhầm phổ biến: “Đặt cột selective nhất lên đầu”
Chưa chắc đúng! Nếu query dùng đủ cả 3 cột thì số bước phễu như nhau, selectivity không tạo khác biệt. Cách đúng: thứ tự cột được quyết định bởi TẬP HỢP QUERY mà app chạy, nhằm tối đa số query được phục vụ bởi cùng một index:
Q1: WHERE country='VN' (rất thường xuyên)
Q2: WHERE country='VN' AND lastname='Nguyen' (thường xuyên)
Q3: WHERE country='VN' AND lastname='Nguyen' AND firstname='Huy'
Index (country, lastname, firstname) → phục vụ CẢ 3 query ✅
Index (lastname, firstname, country) → chỉ phục vụ Q3 tốt, Q1 không dùng đượcLiên quan
- Range Condition phá vỡ Phễu - nguyên tắc 4, giới hạn của phễu
- Fast Lookup, Quét một hướng - nguyên tắc 1 & 2
- Index Write Overhead - quy tắc dọn index trùng lặp prefix
- ORDER BY và Index - cột filter trước, cột sort sau