Posted inDatabase

Tối ưu Compound Index trong MongoDB với quy tắc ESR: Tránh lỗi Sort exceeded memory limit

Database tutorial - IT technology blog
Database tutorial - IT technology blog

1. Vấn đề thực tế: Khi MongoDB sập vì In-Memory Sort

Đợt sale 11/11 vừa qua, cụm MongoDB Replica Set bên mình chạm đỉnh 98% CPU. Hàng loạt request tạo và xem đơn hàng treo cứng. Log trả về mã lỗi 500 kèm thông báo quen thuộc:

Executor error during query :: caused by :: Sort exceeded memory limit of 104857600 bytes, but did not allow sorting on disk.

Mặc định, MongoDB giới hạn 100MB RAM cho mỗi phép sắp xếp dữ liệu (ở MongoDB 4.2 trở về trước là 32MB). Nếu tập dữ liệu cần sort vượt quá con số này mà không có index hỗ trợ, database sẽ ngắt truy vấn ngay lập tức để tránh cạn RAM.

Collection orders khi đó có khoảng 14 triệu documents (~18GB). Đội ngũ phát triển đã tạo index cho cả ba trường: status, amount và createdAt. Tuy nhiên, MongoDB vẫn phải bốc hàng trăm nghìn bản ghi lên RAM để sort lại từ đầu. Đánh index sai thứ tự khiến write throughput giảm 35%, trong khi query read vẫn chậm chạp.

2. Bản chất vấn đề: Tại sao có đủ field mà query vẫn quét RAM?

Xét một document mẫu trong collection orders:

{
  _id: ObjectId("65e0a1b2c3d4e5f6a7b8c9d0"),
  status: "COMPLETED",
  amount: 250000,
  createdAt: ISODate("2026-03-01T10:00:00Z")
}

Query thực tế từ dashboard quản trị:

db.orders.find({
  status: "COMPLETED",
  amount: { $gte: 100000 }
}).sort({ createdAt: -1 });

Index hiện tại trên database:

db.orders.createIndex({ status: 1, amount: 1, createdAt: -1 });

Chạy thử explain("executionStats") để xem cách engine thực thi:

"stage": "SORT",
"sortPattern": { "createdAt": -1 },
"memLimit": 104857600,
"inputStage": {
  "stage": "IXSCAN",
  "indexName": "status_1_amount_1_createdAt_-1"
}

Cấu trúc B-Tree nói lên điều gì?

Compound Index sắp xếp dữ liệu tuần tự từ trái qua phải trên cây B-Tree. Khi đặt trường so sánh khoảng amount trước createdAt, quy trình xử lý diễn ra như sau:

  • Engine tìm nhanh đến nhánh chứa status: "COMPLETED".
  • Tiếp theo, nó quét dọc dải giá trị amount >= 100000. Vì là phép lọc khoảng (Range), các bản ghi nằm rải rác trên nhiều node lá khác nhau.
  • Thứ tự của createdAt bị xé nhỏ theo từng mốc amount. MongoDB không thể lấy kết quả đã xếp sẵn từ index. Hệ thống bắt buộc phải nạp toàn bộ document thỏa mãn lên RAM để chạy thuật toán sort (Blocking Sort).

3. Các giải pháp tạm thời và hệ quả

Giải pháp 1: Bật cờ allowDiskUse

Nhiều người chọn cách lách luật bằng tùy chọn allowDiskUse: true:

db.orders.find({
  status: "COMPLETED",
  amount: { $gte: 100000 }
}).sort({ createdAt: -1 }).allowDiskUse(true);

Hệ quả: Lỗi 500 biến mất, nhưng latency vọt từ 60ms lên hơn 3.2 giây. Việc ghi file tạm xuống đĩa liên tục sẽ vét cạn IOPS của ổ cứng SSD, kéo chậm toàn bộ các tác vụ khác trên cluster.

Giải pháp 2: Tạo nhiều single-field index riêng lẻ

Đánh ba index độc lập: status, amount và createdAt rồi trông chờ vào Index Intersection.

Hệ quả: MongoDB phải tốn thêm chu kỳ CPU để ghép các tập con trỏ RecordId. Cách này vừa tốn dung lượng lưu trữ index, vừa không triệt tiêu được bước SORT trên RAM.

4. Giải pháp triệt để: Áp dụng quy tắc ESR

Để tối ưu cả tốc độ lọc lẫn sắp xếp, thứ tự các field trong Compound Index cần tuân thủ nghiêm ngặt quy tắc ESR: Equality → Sort → Range.

Nguyên lý hoạt động của ESR

  1. E – Equality (So sánh bằng): Đặt các trường so sánh chính xác lên đầu tiên (như status, shopId). Bước này gom tập dữ liệu về một nhánh cây B-Tree liên tục.
  2. S – Sort (Sắp xếp): Đặt trường cần sort ngay phía sau (ở đây là createdAt). Khi tập dữ liệu từ bước Equality đã liền mạch, con trỏ index chỉ cần duyệt thẳng theo chiều mong muốn mà không cần đảo thứ tự trên RAM.
  3. R – Range (So sánh khoảng): Đặt các trường lọc khoảng ở cuối cùng (như amount với $gte, $lt, hoặc $in). Index vừa duyệt theo chiều sort, vừa lọc bỏ các bản ghi không khớp điều kiện khoảng.

Thực thi trên Production

Đầu tiên, xóa index cũ:

db.orders.dropIndex("status_1_amount_1_createdAt_-1");

Tạo index mới theo đúng công thức ESR (Equality: status, Sort: createdAt, Range: amount):

db.orders.createIndex({ status: 1, createdAt: -1, amount: 1 });

Đánh giá lại bằng executionStats

db.orders.find({
  status: "COMPLETED",
  amount: { $gte: 100000 }
}).sort({ createdAt: -1 })
  .explain("executionStats");

Execution plan mới:

"stage": "FETCH",
"inputStage": {
  "stage": "IXSCAN",
  "indexName": "status_1_createdAt_-1_amount_1",
  "direction": "forward"
}

Node "stage": "SORT" biến mất hoàn toàn. Thay vào đó, MongoDB dùng Index-assisted Sort (quét đến đâu lấy luôn thứ tự đến đó). Thời gian thực thi giảm từ 1.840ms xuống 4ms, đồng thời totalBytesSorted trở về 0.

Lưu ý thực tế với toán tử $in

Về mặt cú pháp, $in là so sánh bằng. Nhưng dưới tầng lưu trữ, $in với mảng nhiều phần tử (như status: { $in: ["PENDING", "PAID", "SHIPPING"] }) hoạt động tương tự tập hợp nhiều phép OR. Con trỏ index phải nhảy qua nhiều phân vùng khác nhau. Lúc này, MongoDB khó đảm bảo thứ tự sort liền mạch. Hãy chạy explain() để so sánh giữa { status: 1, createdAt: -1 } và { createdAt: -1, status: 1 } tùy theo độ phân tán (cardinality) của dữ liệu.

Share:
Tags: