Tối ưu database: indexing và N+1 query

Index là gì, và khi nào được dùng

Index là cấu trúc B-tree cho phép tìm nhanh theo cột. Với 10 triệu dòng, tìm không index là quét tuần tự; có index là xuống cây vài tầng. Nhưng index tốn bộ nhớ và làm ghi chậm hơn — chỉ tạo cho cột bạn thật sự lọc/sắp xếp.

// File: index.sql
-- Tạo index cho cột hay xuất hiện trong WHERE/JOIN/ORDER BY
CREATE INDEX idx_jobs_recruiter ON jobs (recruiter_id);
CREATE INDEX idx_jobs_created   ON jobs (created_at DESC);

-- Index ghép cho bộ lọc thường dùng cùng nhau
CREATE INDEX idx_jobs_role_status ON jobs (role, status);

-- Kiểm tra kế hoạch thực thi
EXPLAIN ANALYZE
SELECT * FROM jobs WHERE recruiter_id = 42 ORDER BY created_at DESC;

N+1 query: sát thủ thầm lặng của ORM

// File: n1.js
// 1 truy vấn lấy jobs + N truy vấn lấy recruiter
const jobs = await db.job.findMany({ take: 100 });

for (const j of jobs) {
  j.recruiter = await db.user.findUnique({
    where: { id: j.recruiter_id },   // +100 truy vấn!
  });
}

// File: n2.js
// Một truy vấn, JOIN lấy sẵn recruiter
const jobs = await db.job.findMany({
  take: 100,
  include: { recruiter: { select: { id: true, name: true } } },
});

// Hoặc nạp theo lô 2 truy vấn
const ids = [...new Set(jobs.map((j) => j.recruiter_id))];
const users = await db.user.findMany({ where: { id: { in: ids } } });
const byId = new Map(users.map((u) => [u.id, u]));

Đọc EXPLAIN ANALYZE: rows và loops

// File: explain.txt
Nested Loop  (cost=0.43..8412.11 rows=1 width=64)
             (actual time=0.089..842.310 rows=4821 loops=1)
  ->  Seq Scan on jobs  (actual time=0.011..12.4 rows=4821 loops=1)
        Filter: (status = 'active')
  ->  Index Scan using users_pkey on users
        (actual time=0.171..0.171 rows=1 loops=4821)
        Index Cond: (id = jobs.recruiter_id)
Planning Time: 0.214 ms
Execution Time: 843.907 ms

Nhìn loops=4821: node tìm user chạy 4821 lần dù mỗi lần 0.17ms — tổng ~820ms. Dấu hiệu chắc chắn của N+1. Sửa bằng JOIN hoặc include như trên.

Thống kê cũ và ANALYZE

Rows ước lượng 1 nhưng thật 4821 → optimizer chọn sai kế hoạch vì thống kê cũ. Chạy ANALYZE jobs sau khi đổ dữ liệu lớn, và cân nhắc autovacuum đúng tần suất.

• Tạo index cho cột lọc/sắp xếp hay dùng
• Nhận diện N+1 qua số truy vấn
• Đọc rows ước lượng vs thật và loops
• Chạy ANALYZE sau khi dữ liệu thay đổi lớn