Design to Scale
Database Indexing
Vì sao cùng một query mà ngày càng chậm khi bảng lớn dần, B-tree index biến việc quét toàn bảng thành vài lần đọc page ra sao — và vì sao mỗi index thêm vào lại bắt lượt ghi trả giá.
Sau bài này bạn sẽ
- Giải thích khác biệt giữa sequential scan và tra bằng index.
- Dự đoán thời gian query tăng thế nào theo kích thước bảng khi có và không có index.
- Cân nhắc chi phí ghi của mỗi index so với lượt đọc nó giúp tăng tốc.
- Dùng EXPLAIN để xem database thực sự làm gì.
Tổng quan
Tìm một chủ đề trong cuốn sách 1.000 trang bằng cách lật từng trang thì mất cả buổi; mục lục ở cuối sách chỉ thẳng tới trang 412 trong vài giây. Index trong database cũng vậy: một cấu trúc đã sắp xếp, trỏ tới đúng những dòng bạn cần, để database khỏi phải đọc hết.
Không có index, WHERE customer_id = ? bắt database đọc từng dòng một (sequential scan), nên chi phí tăng tuyến tính theo kích thước bảng. B-tree index giữ cột đó được sắp xếp trong một cây nông; mỗi lần tra chỉ đi qua vài tầng, nên chi phí tăng theo log₂(số dòng) — 20 tầng cho một triệu dòng, 30 tầng cho một tỉ dòng.
Chạy thử
Ở A, bảng lớn dần theo mỗi đơn hàng và lần tra nào cũng quét toàn bảng: xem Thời gian đọc trung bình tăng dần cho tới khi database bão hoà — cùng code, cùng lưu lượng. B thêm một index. C có tới mười hai index: đọc thì nhanh, nhưng hãy nhìn Thời gian ghi trung bình.
Một index tốn gì
| Không index | Một index | Nhiều index | |
|---|---|---|---|
| Tra theo cột | Quét mọi dòng | Đi qua ~20 tầng cây | Đi qua ~20 tầng cây |
| Insert / update | Ghi dòng | Dòng + 1 entry index | Dòng + N entry index |
| Ổ đĩa và bộ nhớ | Chỉ có bảng | + dung lượng index | Index có thể to hơn cả bảng |
| Tăng theo bảng | Tuyến tính | Theo logarit | Theo logarit (đọc), tuyến tính theo N (ghi) |
Trong thực tế
EXPLAIN ANALYZE SELECT id, total FROM orders WHERE customer_id = 42 ORDER BY created_at DESC LIMIT 20;
-- Seq Scan on orders (rows=1,800,000) Filter: customer_id = 42 ← đọc toàn bộ
-- Một composite index phục vụ cả điều kiện lọc LẪN sắp xếp; CONCURRENTLY để không khoá lượt ghi.
CREATE INDEX CONCURRENTLY idx_orders_customer_created ON orders (customer_id, created_at DESC);
EXPLAIN ANALYZE SELECT id, total FROM orders WHERE customer_id = 42 ORDER BY created_at DESC LIMIT 20;
-- Index Scan using idx_orders_customer_created (rows=20) ← chỉ đọc 20 dòng
-- Tìm index không ai dùng (chúng vẫn làm chậm mọi lượt ghi):
SELECT relname, indexrelname, idx_scan FROM pg_stat_user_indexes WHERE idx_scan = 0;- Composite index: thứ tự cột rất quan trọng — cột so sánh bằng đứng trước, rồi tới cột khoảng hoặc cột sắp xếp.
(customer_id, created_at)phục vụ đượcWHERE customer_id = ? ORDER BY created_at, nhưng không phục vụ riêngWHERE created_at > ?. - Covering index (
INCLUDE (total)) trả lời query ngay từ index mà không cần đụng tới bảng. - Độ chọn lọc: index trên cột chỉ có hai giá trị (
is_active) hiếm khi có ích; planner vẫn sẽ quét bảng.
Đánh đổi
| Được | Mất | |
|---|---|---|
| Đọc | Tra cứu vẫn nhanh dù bảng lớn lên gấp nhiều lần. | Chỉ query khớp với các cột đầu của index mới được lợi. |
| Ghi | Một index chọn đúng thì tốn rất ít. | Mọi index đều bị cập nhật ở mỗi lần insert và update — bảng ghi nhiều sẽ khổ. |
| Tài nguyên | Các page index nóng nằm sẵn trong bộ nhớ. | Index tốn ổ đĩa và buffer cache; index phình to cần được bảo trì. |
Lưu ý khi chạy production
- Đánh index cho những query tốn tổng thời gian nhiều nhất (
pg_stat_statements), không phải cho mọi cột. - Tạo index kiểu concurrently trên bảng đang chạy, và xoá các index không dùng tới.
- Kiểm tra lại query plan sau khi dữ liệu thay đổi lớn: plan ổn ở 10 nghìn dòng có thể quét toàn bảng ở 10 triệu dòng.
- Load test với dữ liệu cỡ production — bảng test nhỏ sẽ che mất chuyện thiếu index.