Denormalization - Lọc và sắp xếp khi JOIN: bài toán KHÔNG giải được bằng index

Tình huống khiến nhiều developer bối rối: đã tạo index hoàn hảo cho TỪNG bảng, nhưng query JOIN vẫn chậm. Vấn đề không nằm ở index - mà ở cách thiết kế bảng.

Ví dụ: ứng dụng quản lý công việc

-- Hiển thị task đang mở của nhóm, chỉ từ các project đang hoạt động
SELECT tasks.* FROM tasks
JOIN projects USING(project_id)
WHERE tasks.team_id = 4 AND tasks.status = 'open'
  AND projects.status = 'open';

Trên dữ liệu test (vài trăm dòng): tức thì. Trên production (tasks 500k, projects 10k):

Hướng 1 - bắt đầu từ tasks:  lọc → 20,000 dòng khớp
  → 20,000 lần JOIN với projects → kết quả cuối chỉ 40 dòng
  → 20,000 lần join chỉ để lấy 40 dòng = lãng phí cực kỳ!
Hướng 2 - bắt đầu từ projects: 2,000 project mở → 2,000 lần JOIN → vẫn chậm!

Dù tạo index nào, bản chất vấn đề là thông tin lọc nằm ở 2 bảng khác nhau. Database phải join hàng chục nghìn lần trước khi biết kết quả cuối.

Giải pháp: thay đổi thiết kế bảng (denormalize)

-- Cách 1: Copy trạng thái project vào bảng tasks
ALTER TABLE tasks ADD COLUMN project_status VARCHAR(20);
-- (cập nhật bằng trigger hoặc logic ứng dụng khi project đổi trạng thái)
SELECT * FROM tasks
WHERE team_id = 4 AND status = 'open' AND project_status = 'open';
-- Index (team_id, status, project_status) xử lý hoàn hảo!
 
-- Cách 2: Đánh dấu tasks là "archived" khi project đóng
-- Khi đóng project → UPDATE tasks SET status='archived' WHERE project_id=? AND status='open'
-- → Loại bỏ hoàn toàn nhu cầu JOIN

Trường hợp tương tự: lọc một bảng, sắp xếp bảng khác

SELECT * FROM invoices
JOIN invoices_metadata USING(invoice_id)
WHERE invoices.tenant_id = 4236
ORDER BY invoices_metadata.due_date LIMIT 30;
-- Lọc trên invoices nhưng sort trên invoices_metadata
-- → lọc hàng nghìn hóa đơn → join tất cả → sort → lấy 30
-- Giải pháp: đưa due_date vào bảng invoices, hoặc hợp nhất hai bảng

Quy tắc nhớ

Khi query cần lọc/sắp xếp trên nhiều bảng khác nhau và phải join hàng chục nghìn lần - đó là tín hiệu cần THIẾT KẾ LẠI SCHEMA, không phải thêm index. Đôi khi một chút dư thừa dữ liệu (denormalization) mang lại hiệu suất tốt hơn hẳn so với thiết kế “chuẩn hóa hoàn hảo”.

Liên quan

• Nested Loop Join - vì sao join N lần đắt
• Pre-sort và Pre-aggregation - denormalize ở mức độ tiếp theo
• JSON Column - một dạng denormalize khác