Compound Indexes (P2/3): ESR và multi-tenant

Ở phần trước: compound index sắp key theo field từ trái sang, nên prefix quyết định query nào dùng được index. Thiếu field đầu thì không dùng được. Sort bỏ được stage SORT khi đi đúng thứ tự index, sau các điều kiện bằng.

• Cần đọc trước: Compound index và prefix rule
• Dẫn tới: Covered query và quy trình thiết kế

ESR: Equality, Sort, Range

Ý chính

Khi query có điều kiện bằng, có sort và có điều kiện khoảng, hãy xếp field trong index theo thứ tự:

E  Equality   field so sánh bằng          tenantId: "t0042", status: "pending"
S  Sort       field trong .sort()          createdAt
R  Range      field so sánh khoảng          total: { $gte: ... }, $ne, $nin, $regex

Vì sao thứ tự này

Quay lại danh bạ. Câu hỏi: "người ở Hà Nội, có số điện thoại bắt đầu bằng 09, xếp theo tên".

Sổ A sắp theo (tỉnh, tên, số)  = E S R          Sổ B sắp theo (tỉnh, số, tên) = E R S

Hà Nội │ An   │ 0248 ✗ bỏ qua                   Hà Nội │ 0901 │ Minh  ┐
Hà Nội │ An   │ 0913 ✓                         Hà Nội │ 0912 │ An    │ đoạn "09..." liền nhau
Hà Nội │ Bảo  │ 0914 ✓                         Hà Nội │ 0914 │ Bảo   │ nhưng tên lộn xộn
Hà Nội │ Lan  │ 0913 ✓                         Hà Nội │ 0987 │ Lan   ┘
  ...                                             → chép ra, xếp lại theo tên
→ đọc từ trên xuống là có thứ tự tên,
  cần 20 người thì dừng sau ~20 dòng có ích

• E trước: điều kiện bằng thu hẹp về một đoạn liền nhau. Trong đoạn đó, các field sau vẫn có thứ tự.
• S trước R: nếu field sort đứng ngay sau equality, đọc index theo thứ tự là có kết quả đã xếp. Range được kiểm tra ngay trên key (không cần mở document), và có limit thì dừng sớm.
• R trước S (sổ B): range cắt ra một đoạn liền nhau, nhưng trong đoạn đó thứ tự là theo field range, không phải field sort. Phải gom mọi key khớp rồi xếp lại trong bộ nhớ.

Thí nghiệm: hai thứ tự, cùng ba field

Câu hỏi kiểu dashboard: "20 đơn pending mới nhất có tổng tiền từ 5 triệu trở lên".

db.orders.createIndex({ status: 1, createdAt: -1, total: 1 }, { name: "ESR_s" });
db.orders.createIndex({ status: 1, total: 1, createdAt: -1 }, { name: "ERS_s" });
const q = th => db.orders.find({ status: "pending", total: { $gte: th } })
                         .sort({ createdAt: -1 }).limit(20);
show("ESR_s", q(5e6).hint("ESR_s"));
show("ERS_s", q(5e6).hint("ERS_s"));

hint() ép server dùng đúng index được chỉ định, để so sánh hai cách một cách công bằng (tài liệu ESR cũng khuyên dùng hint() khi thử index [tài liệu]). Có 48.751 đơn pending từ 5 triệu trở lên.

ESR_s | LIMIT <- FETCH <- IXSCAN ESR_s        | keys 45    | docs 20 | nReturned 20
ERS_s | FETCH <- SORT <- IXSCAN ERS_s         | keys 48751 | docs 20 | nReturned 20

ESR                                        ERS
IXSCAN đoạn "pending", đi từ mới nhất       IXSCAN đoạn "pending, total ≥ 5tr"
  kiểm tra total trên key                     48.751 key, thứ tự theo total
  45 key → 20 key khớp → dừng (LIMIT)           ↓
  ↓                                          SORT 48.751 key theo createdAt (top 20)
FETCH 20 document                              ↓
                                           FETCH 20 document

ESR đọc 45 key: khoảng một nửa đơn pending có tổng từ 5 triệu, nên đi qua khoảng 45 đơn mới nhất là gom đủ 20. ERS phải đọc cả 48.751 key, vì chỉ khi thấy hết mới biết 20 đơn nào mới nhất. Chênh lệch khoảng 20–35 lần về thời gian và hơn 1.000 lần về số key. Chênh lệch này tăng theo số đơn khớp range: dữ liệu càng lớn, ERS càng đuối.

Cả hai chỉ FETCH 20 document vì, như câu H, bản 8.3 xếp trên key rồi mới fetch [quan sát]. Nếu SORT nằm trên FETCH, ERS còn phải fetch cả 48.751 document.

Ngoại lệ: khi range rất kén chọn

Giờ đổi ngưỡng thành 25 triệu. Chỉ có 5 đơn pending đạt mức này.

ESR_s | LIMIT <- FETCH <- IXSCAN ESR_s        | keys 99637 | docs 5 | nReturned 5
ERS_s | FETCH <- SORT <- IXSCAN ERS_s         | keys 5     | docs 5 | nReturned 5

Thứ tự thắng thua đảo ngược. ESR đi từ đơn mới nhất xuống, kiểm tra từng key, mong gom đủ 20 đơn. Nhưng chỉ có 5 đơn trong cả 99.639 đơn pending, nên nó đi hết cả đoạn. ERS nhảy thẳng vào đoạn "total ≥ 25 triệu", lấy 5 key, xếp 5 phần tử trong bộ nhớ, xong.

Tài liệu ESR ghi đúng ngoại lệ này: nếu range predicate rất kén chọn, đặt nó trước field sort (ERS) để giảm số document phải xếp và cho phép sort trong bộ nhớ [tài liệu]. Quy tắc vì vậy không phải "luôn ESR", mà là:

Range khớp nhiều, có sort + limit         → E S R   (tránh sort, dừng sớm)
Range khớp rất ít                        → E R S   (đọc ít key, sort vài phần tử)
Không biết trước / cả hai đều xảy ra      → đo bằng hint() trên dữ liệu thật,
                                            có khi cần cả hai index

[quan sát] Không có hint, planner chọn ESR_s cho ngưỡng 5 triệu và ERS_s cho ngưỡng 25 triệu. Lần này nó chọn đúng cả hai. Nhưng đó là cùng một query shape với hai giá trị tham số khác nhau, và plan được chọn có thể được cache rồi dùng lại cho giá trị kia. Đó là câu chuyện "plan cache lật" của bài Query Planner & Plan Cache.

Cùng thí nghiệm trên tenant t0042 (1.948 đơn, index { tenantId, createdAt, total } so với { tenantId, total, createdAt }, query { tenantId, total ≥ ngưỡng } sort createdAt limit 20) cho cùng chiều kết quả, chỉ nhỏ hơn: ngưỡng 5 triệu thì ESR đọc 37 key, ERS 927 key (median một lượt: 0,72 ms so với 0,92 ms). Ngưỡng 20 triệu (5 đơn) thì ESR đọc 1.949 key, ERS 5 key (1,76 ms so với 0,32 ms). Với một tenant nhỏ, chọn sai thứ tự không gây hoạ. Với cả collection hay một tenant cỡ lớn thì có.

Operator nào là E, operator nào là R

Toàn bộ bảng là [tài liệu] theo trang ESR. Tài liệu cũng ghi rõ ngưỡng 201 phần tử của $in "không được đảm bảo giữ nguyên ở mọi phiên bản". Trong nhóm Equality, các field có thể xếp theo bất kỳ thứ tự nào với nhau, miễn là đứng trước mọi field sort và range.

Multi-tenant: { tenantId, createdAt } hay { createdAt }

Màn hình phổ biến nhất của một hệ thống SaaS: "20 đơn mới nhất của shop tôi". Một lỗi hay gặp là tạo index { createdAt: -1 } vì "query nào cũng sort theo ngày". Hai index được so sánh:

db.orders.createIndex({ createdAt: -1 });
db.orders.createIndex({ tenantId: 1, createdAt: -1 });
const page = t => db.orders.find({ tenantId: t }).sort({ createdAt: -1 }).limit(20);

Ba tenant: t0042 (1.948 đơn, cỡ trung bình), t9999 (30 đơn, đều cũ gần một năm) và t7777 (không tồn tại, chẳng hạn một shop vừa đăng ký).

{ createdAt: -1 }: đi từ đơn mới nhất của CẢ HỆ THỐNG xuống

  2026-09-30 t0311 ✗ → FETCH, kiểm tra tenantId, vứt
  2026-09-30 t0042 ✓
  2026-09-30 t0187 ✗ → FETCH, vứt
  ...                                   t0042: trung bình 1/500 đơn là của nó
                                        t9999: 30 đơn nằm gần CUỐI index
{ tenantId: 1, createdAt: -1 }: nhảy thẳng vào đoạn "t0042", đọc 20 key, dừng

Ba điều rút ra:

1. Chi phí của { createdAt: -1 } phụ thuộc vào tenant. Tenant lớn và hoạt động nhiều thì thấy nhanh. Tenant nhỏ, ít hoạt động, hoặc vừa đăng ký thì quét gần cả collection. Đây là lỗi kiểu "dev thì nhanh, khách nhỏ thì kêu chậm", vì môi trường dev thường test bằng tenant có nhiều dữ liệu.
2. IXSCAN + FETCH trên cả collection còn chậm hơn COLLSCAN. Với t9999, đi theo { createdAt: -1 } mất 918–1.644 ms. COLLSCAN có hint({ $natural: 1 }) cho cùng query đo được median 152 ms. [quan sát] Đi theo index nghĩa là FETCH từng document theo thứ tự của index, tức là nhảy lung tung trong collection. COLLSCAN đọc lần lượt. Bài Index Fundamentals đã đo hiện tượng này ở dạng "index làm chậm khi selectivity thấp".
3. { tenantId, createdAt } cho chi phí gần như không đổi (20 key, 20 document) bất kể tenant to hay nhỏ. Đó là tính chất bạn muốn ở một hệ thống nhiều tenant: chi phí của một request tỉ lệ với kết quả, không tỉ lệ với kích thước của người khác.

Cái giá: tenantId_1_createdAt_-1 chiếm 14,6 MB so với 11,8 MB của createdAt_-1, vì key dài hơn. Phân trang tiếp theo trang này (range/cursor theo createdAt + _id) là việc của bài Pagination.

Cột mốc: Bạn đã có thể chọn thứ tự ESR cho query có điều kiện bằng, sort và khoảng, và biết khi nào nên đảo range lên trước sort. Tiếp theo: Covered query và quy trình thiết kế.

Hỏi & đáp