GROUP BY và DISTINCT - Thách thức lớn nhất

Hai operation mà nhiều developer “quên” tối ưu index - sai lầm nghiêm trọng vì chúng thường xử lý hàng chục nghìn đến hàng triệu row.

DISTINCT = GROUP BY về mặt execution. Database chuyển SELECT DISTINCT country FROM users thành SELECT country FROM users GROUP BY country nội bộ → mọi quy tắc index cho GROUP BY áp dụng cho DISTINCT.

Thuật toán “duyệt và đếm” (loop-and-count)

Có index phù hợp: vì giá trị đã sorted, database chỉ scan qua index, đếm entries liên tục có cùng giá trị; gặp giá trị mới → kết thúc group cũ, bắt đầu group mới:

SELECT is_paying, COUNT(*) FROM users GROUP BY is_paying;
-- Index: (is_paying)
-- Scan: [no | no | no | yes | yes | yes | yes]
--        └── count=3 ──┘└──── count=4 ─────┘
-- 1 lần scan, không temporary table, không sort

KHÔNG có index phù hợp → database phải: scan toàn bộ table → tạo hash table tạm trong memory (key = giá trị group) → nếu hash table quá lớn → spill ra disk → rất chậm.

4 trường hợp tạo index cho GROUP BY

1. Simple GROUP BY - index cùng cột, cùng thứ tự:

GROUP BY is_paying, gender  → Index (is_paying, gender)

2. GROUP BY + WHERE - WHERE chạy trước (SQL Execution Order) nên cột WHERE đặt trước:

WHERE onboarding='yes' GROUP BY is_paying, gender
→ Index (onboarding, is_paying, gender)

3. GROUP BY + WHERE range → CẨN THẬN! - Nguyên tắc 4 gây vấn đề lớn:

WHERE age BETWEEN 20 AND 29 GROUP BY is_paying, gender
-- Index (age, is_paying, gender):
-- Sau range scan trên age, is_paying/gender bị XEN KẼ giữa các giá trị age
-- → không dùng được loop-and-count → phải tạo temporary hash table → chậm
-- Giải pháp: biến range thành equality (tạo cột age_group='twenties')

4. GROUP BY + Aggregate function - cột aggregate (AVG, SUM, MAX…) nên nằm cuối index để đọc từ index không cần load row:

SELECT is_paying, gender, AVG(projects_cnt) FROM users GROUP BY is_paying, gender;
-- ❌ (is_paying, gender): phải load 500,000 rows chỉ để đọc 1 cột!
-- ✅ (is_paying, gender, projects_cnt): index-only operation → 0 rows load từ table

Tip viết gọn

GROUP BY trên primary key → không cần liệt kê các cột khác cùng bảng:

GROUP BY actors.id;  -- Primary key là đủ! Database tự thêm các cột khác.

Liên quan

• Biến Range thành Equality - cứu trường hợp 3
• Index-Only Query và Covering Index - trường hợp 4 chính là index-only
• ORDER BY và Index - cùng triết lý “tận dụng thứ tự sorted”