Low-Cardinality Column và Index - Khi index trở nên vô nghĩa

Hiểu lầm phổ biến: “Cột nào hay dùng trong WHERE thì đánh index.” Nghe có vẻ đúng, nhưng với cột boolean hoặc cột có ít giá trị phân biệt, index thường KHÔNG được dùng.

Tại sao

Bảng orders 1 triệu dòng, cột is_processed: TRUE 80%, FALSE 20%.

Query WHERE is_processed = FALSE:
 
Phương án 1: Dùng index
  → Index tìm được 200,000 con trỏ
  → Nhảy tới 200,000 vị trí KHÁC NHAU trên đĩa (random I/O) = RẤT CAO
 
Phương án 2: Quét toàn bộ bảng
  → Đọc tuần tự 1,000,000 dòng (sequential I/O), bỏ qua 800,000 dòng không khớp
  → THẤP HƠN (sequential nhanh hơn random 10-100 lần)
 
→ Database chọn phương án 2: BỎ QUA INDEX

Ngay cả thêm LIMIT 5: với 20% dòng khớp, trung bình chỉ cần đọc ~25 dòng tuần tự là đủ 5 kết quả - rẻ hơn nhiều so với 5 lần nhảy qua index + random I/O.

Không chỉ boolean - mọi cột phân phối lệch

Bảng issues sau 2 năm:
│ closed  │ 95,000 │ 95% │ ← Index VÔ NGHĨA
│ open    │  3,000 │  3% │ ← Index CÓ THỂ hữu ích
│ wontfix │  2,000 │  2% │ ← Index CÓ THỂ hữu ích

Tìm status='closed' → index vô dụng (95% khớp). Tìm status='open' → hữu ích (3%, dưới ngưỡng chuyển đổi).

Quy tắc nhớ

Chỉ đánh index cột boolean/trạng thái khi tìm kiếm giá trị HIẾM (dưới vài phần trăm). Với PostgreSQL, ưu tiên Partial Index. Ngoài ra, cột low-cardinality vẫn rất hữu ích khi là thành phần của composite index (ví dụ cột equality đứng trước cột range - Range Condition phá vỡ Phễu).

Liên quan

• Random IO vs Sequential IO - bản chất vật lý của vấn đề
• Query Optimizer và Cost Model - ngưỡng chuyển đổi 10-30%
• Partial Index - giải pháp PostgreSQL
• Statistics và ANALYZE - DB biết phân phối nhờ histogram