Compound index trong MongoDB

Tình huống mở đầu

Cô Tấm có một cuốn danh bạ điện thoại cũ: các số được xếp theo họ, trong cùng họ xếp theo tên, trong cùng tên xếp theo phố. Muốn tìm "Nguyễn Lan ở phố Chợ", bạn lật tới họ Nguyễn, rồi tên Lan, rồi phố Chợ, và gần như không phải đọc thêm gì. Nhưng muốn tìm "ai tên Lan" thì cuốn danh bạ vô dụng: tên Lan nằm rải rác ở mọi họ.

Sổ bán hàng 2000 phiếu của cô cũng có ba field hay được tra cùng nhau: món hàng (good), số lượng (qty) và ngày (day). Anh Bình tạo một index ghép ba field và thấy có truy vấn rất nhanh, có truy vấn không nhanh chút nào. Hãy đoán trước: vì sao thứ tự ba field lại quan trọng đến vậy?

Ôn nhanh bài trước: index duy nhất, TTL, hashed đều là index một field với luật riêng. Hai index một field riêng lẻ trên good và qty có làm việc cùng nhau được cho một truy vấn lọc cả hai không?

Đáp án: thường không tốt bằng một index ghép, và mình sẽ đo ở mục Thử phá nó đi.

Mục tiêu bài học

Sau bài này bạn có thể:

• Tạo index ghép và dự đoán truy vấn nào dùng được nó, theo quy tắc tiền tố (prefix rule): khóa đầu là cửa vào.
• Chọn thứ tự khóa theo quy tắc ESR (bằng, sắp xếp, khoảng) và kiểm chứng bằng số khóa đọc cùng sự có mặt của bước SORT.
• Biết index ghép phục vụ sắp xếp cùng chiều hoặc ngược chiều toàn bộ, nhưng không phục vụ chiều lẫn lộn.
• Nhận ra index thừa (index chỉ là tiền tố của index khác) và hiểu vì sao một index ghép thường tốt hơn hai index đơn.

Bức tranh lớn

Xuyên suốt bài, index ghép là cuốn danh bạ xếp theo good, rồi qty, rồi day. Dự án xuyên suốt của chương vẫn là sổ bán hàng của Tạp Hóa Cô Tấm, lần này với truy vấn nhiều điều kiện.

Hình dưới trả lời một câu hỏi: một truy vấn có dùng được cuốn danh bạ và dùng tốt đến đâu?

Cách đọc hình: truy vấn (ô cyan) trước hết được hỏi có chứa khóa đầu không (ô cam). Không có thì index không dùng được và máy quét cả collection (ô đỏ). Có khóa đầu thì hỏi tiếp các khóa sau có theo đúng thứ tự không: nếu có, các khóa đều bị chặn bounds chặt nên đọc rất ít khóa (ô xanh lá); nếu bỏ qua một khóa giữa, index vẫn thu hẹp được nhưng không chặt bằng (ô tím).

Chỗ ẩn dụ không còn đúng: danh bạ thật chỉ cho bạn lật theo thứ tự, còn MongoDB có thể nhảy qua khóa giữa để dò khóa sau. Cách này làm lời khuyên "phải khớp liên tiếp" mềm hơn một chút, như mục Ví dụ đầu tiên sẽ cho thấy.

Khái niệm cốt lõi

Bài này có ba khái niệm.

1. Index ghép và thứ tự khóa

createIndex({ good: 1, qty: 1, day: 1 }) xếp các phiếu theo good, trong cùng good xếp theo qty, trong cùng qty xếp theo day. Thứ tự khóa là một phần của định nghĩa: { good: 1, qty: 1 } và { qty: 1, good: 1 } là hai index khác nhau, phục vụ các truy vấn khác nhau.

2. Quy tắc tiền tố

Index dùng tốt cho truy vấn có các khóa đầu tiên liên tiếp: good; good và qty; cả ba. Truy vấn thiếu khóa đầu (chỉ qty, hoặc qty và day) không dùng được index. Truy vấn bỏ khóa giữa (good và day) dùng được ở mức thu hẹp.

3. ESR và chiều sắp xếp

Quy tắc ESR gợi ý xếp khóa theo thứ tự: bằng (Equality), sắp xếp (Sort), khoảng (Range). Mục đích là để index tự trả về theo đúng thứ tự cần sắp xếp mà không cần bước SORT trong bộ nhớ. Index cũng chạy ngược được, nên sắp xếp ngược toàn bộ khóa vẫn dùng được index.

Ví dụ đầu tiên

Dựng lại sổ 2000 phiếu bằng bộ sinh số cố định như bài trước. Gõ trong mongo shell:

use shop

function seedSales() {
  var names = ["rice", "sugar", "fish sauce", "noodles", "eggs", "salt", "soy sauce", "tea"]
  var x = 42
  var batch = []
  for (var i = 1; i <= 2000; i++) {
    x = (x * 16807) % 2147483647
    var good = names[x % 8]
    x = (x * 16807) % 2147483647
    var qty = 1 + (x % 5)
    x = (x * 16807) % 2147483647
    batch.push({ _id: i, invoice: "INV-" + (1000 + i), good: good, qty: qty, day: 1 + (x % 28) })
  }
  db.sales.insertMany(batch.slice(0, 1000))
  db.sales.insertMany(batch.slice(1000))
  return db.sales.countDocuments({})
}
var seeded = seedSales()

function chain(s) { var out = []; while (s) { out.push(s.stage); s = s.inputStage } return out.join(">") }
function st(q) { var e = q.explain("executionStats"); var x = e.executionStats; return chain(e.queryPlanner.winningPlan) + " keys=" + x.totalKeysExamined + " docs=" + x.totalDocsExamined + " n=" + x.nReturned }
function ec(f) { try { var r = f(); return r && r.ok === 0 ? "code " + r.code + " " + r.codeName : "ok" } catch (e) { return "code " + e.code } }
function names(c) { return c.getIndexes().map(function (i) { return i.name }).join(", ") }
function keys(c) { return tojson(c.validate({ full: true }).keysPerIndex, "", true) }
function used(q) { var p = q.explain().queryPlanner.winningPlan; while (p.inputStage) p = p.inputStage; return p.indexName }

Tạo index ghép rồi tra bảy kiểu:

var made = db.sales.createIndex({ good: 1, qty: 1, day: 1 })
print("name:", names(db.sales))
print("good:", st(db.sales.find({ good: "tea" })))
print("good, qty:", st(db.sales.find({ good: "tea", qty: 3 })))
print("good, qty, day:", st(db.sales.find({ good: "tea", qty: 3, day: 5 })))
print("good, day:", st(db.sales.find({ good: "tea", day: 5 })))
print("qty:", st(db.sales.find({ qty: 3 })))
print("qty, day:", st(db.sales.find({ qty: 3, day: 5 })))
print("day:", st(db.sales.find({ day: 5 })))
print("qty forced:", st(db.sales.find({ qty: 3 }).hint("good_1_qty_1_day_1")))

Dự đoán trước khi xem kết quả: truy vấn chỉ lọc qty có dùng được index không? Truy vấn good và day, bỏ qty, đọc nhiều hay ít khóa hơn so với chỉ good?

Kết quả [Đã chạy: MongoDB 4.4.19, mongo shell 4.4.19]:

name: _id_, good_1_qty_1_day_1
good: FETCH>IXSCAN keys=270 docs=270 n=270
good, qty: FETCH>IXSCAN keys=51 docs=51 n=51
good, qty, day: FETCH>IXSCAN keys=1 docs=1 n=1
good, day: FETCH>IXSCAN keys=21 docs=11 n=11
qty: COLLSCAN keys=0 docs=2000 n=379
qty, day: COLLSCAN keys=0 docs=2000 n=13
day: COLLSCAN keys=0 docs=2000 n=65
qty forced: FETCH>IXSCAN keys=2000 docs=2000 n=379

Vì sao truy vấn good và day đọc 21 khóa mà không phải 270? Xem bounds mà kế hoạch dùng cho từng khóa:

var made = db.sales.createIndex({ good: 1, qty: 1, day: 1 })
function bounds(q) { var p = q.explain().queryPlanner.winningPlan; while (p.inputStage) p = p.inputStage; return tojson(p.indexBounds, "", true) }
print("good, qty:", bounds(db.sales.find({ good: "tea", qty: 3 })))
print("good, day:", bounds(db.sales.find({ good: "tea", day: 5 })))

Kết quả [Đã chạy: MongoDB 4.4.19]:

good, qty: { "good" : [ "[\"tea\", \"tea\"]" ], "qty" : [ "[3.0, 3.0]" ], "day" : [ "[MinKey, MaxKey]" ] }
good, day: { "good" : [ "[\"tea\", \"tea\"]" ], "qty" : [ "[MinKey, MaxKey]" ], "day" : [ "[5.0, 5.0]" ] }

Với good và day, bounds của qty là toàn đoạn (MinKey đến MaxKey) nhưng bounds của day vẫn chặt ở 5. Số liệu khớp với cách hiểu rằng máy đi qua từng giá trị qty và dò day = 5 trong mỗi giá trị [Suy luận: mình không in cấu trúc bên trong của việc nhảy khóa]. Vì vậy mới là 21 khóa chứ không phải 270.

Xem từng bước

Animation dưới đây chạy năm truy vấn trên index ghép đó. Hãy trả lời câu dự đoán trước khi bấm bước đầu tiên.

Bảng trace bằng chữ [Đã chạy: MongoDB 4.4.19]:

Bạn vừa làm được: dự đoán truy vấn nào dùng được index ghép và đọc bao nhiêu khóa, theo vị trí của khóa đầu và các khóa liên tiếp.

Concept Check

Index ghép { good: 1, qty: 1, day: 1 } trên 2000 phiếu.

Câu 1. Truy vấn { qty: 3, day: 5 } (thiếu good) chạy thế nào?

• A. Dùng index, đọc đúng số phiếu khớp.
• B. Dùng index nhưng đọc cả 2000 khóa.
• C. COLLSCAN, quét cả 2000 phiếu vì thiếu khóa đầu.
• D. Lỗi vì truy vấn không theo đúng thứ tự khóa.

Câu 2. Truy vấn { good: "tea", day: 5 } (bỏ qty) chạy thế nào?

• A. COLLSCAN, vì bỏ khóa giữa làm index vô dụng.
• B. Dùng index, đọc 21 khóa và lấy 11 phiếu.
• C. Dùng index, đọc 270 khóa và 270 phiếu.
• D. Lỗi vì thiếu khóa qty.

Câu 3. Truy vấn lọc bằng good, khoảng qty, sắp xếp theo day. Thứ tự khóa nào theo ESR?

• A. { qty: 1, good: 1, day: 1 }
• B. { day: 1, good: 1, qty: 1 }
• C. { good: 1, qty: 1, day: 1 }
• D. { good: 1, day: 1, qty: 1 }

Đáp án và giải thích:

• Câu 1: C. Khóa đầu good vắng mặt nên index không dùng được. A tưởng index dùng được ở mọi khóa; B chỉ đúng nếu ép bằng hint; D bịa ra lỗi.
• Câu 2: B. Có khóa đầu good nên index vẫn thu hẹp được, dù bỏ qty. A coi bỏ khóa giữa là vô dụng; C coi như không có day; D bịa ra lỗi.
• Câu 3: D. Bằng (good), rồi sắp xếp (day), rồi khoảng (qty). C để khoảng trước sắp xếp nên cần bước SORT; A và B bỏ khóa bằng khỏi vị trí đầu.

Làm cùng mình

Cô Tấm muốn xem năm phiếu bán trà có số lượng lớn nhất: find({ good: "tea" }).sort({ qty: -1 }).limit(5).

Bước 1. Vẽ ra giấy

Dự đoán số document phải đọc khi chưa có index. Rồi dự đoán khi có index ghép good rồi qty giảm dần, và khi có index đảo thứ tự qty giảm dần rồi good.

Bước 2. Điền chỗ trống

// TODO 1: đo truy vấn khi chưa có index bằng st(...)
// TODO 2: tạo index ghép good tăng dần rồi qty giảm dần, đo lại
// TODO 3: tạo index đảo thứ tự, ép dùng bằng hint, đo lại

Gợi ý chỗ trống thứ hai: createIndex({ good: 1, qty: -1 }). Chỗ trống thứ ba: createIndex({ qty: -1, good: 1 }) rồi hint("qty_-1_good_1").

Bước 3. So kết quả

Chạy xong, đối chiếu với dự đoán ở mục Lời giải và đáp án.

Bước 4. Hỏi thêm

Vì sao index đảo thứ tự đọc nhiều khóa hơn hẳn dù cùng hai field?

Thử phá nó đi

Hình dưới trả lời một câu hỏi: thứ tự khóa nào nên đặt trước, khi truy vấn vừa lọc bằng, vừa sắp xếp, vừa lọc khoảng?

Cách đọc hình: các field bằng (ô cyan) đặt đầu tiên vì chúng chỉ cho một giá trị, nên không làm vỡ thứ tự phía sau. Field sắp xếp (ô xanh dương) đặt tiếp theo để index trả về đúng thứ tự cần. Field khoảng (ô tím) đặt cuối vì khoảng nhiều giá trị sẽ làm vỡ thứ tự của khóa sau nó. Kết quả là thứ tự khóa của index (ô xanh lá). Quy tắc này là hướng dẫn thực dụng, không phải định luật: hãy đo bằng số liệu thật như ở dưới.

So sánh hai thứ tự khóa

var q = function () { return db.sales.find({ good: "tea", qty: { $gte: 4 } }).sort({ day: -1 }) }
print("no index:", st(q()))
var a = db.sales.createIndex({ good: 1, qty: 1, day: -1 }, { name: "equality_range_sort" })
print("E, R, S:", st(q().hint("equality_range_sort")))
var b = db.sales.createIndex({ good: 1, day: -1, qty: 1 }, { name: "equality_sort_range" })
print("E, S, R:", st(q().hint("equality_sort_range")))
print("planner picks:", used(q()))

Kết quả [Đã chạy: MongoDB 4.4.19]:

no index: SORT>COLLSCAN keys=0 docs=2000 n=116
E, R, S: FETCH>SORT>IXSCAN keys=116 docs=116 n=116
E, S, R: FETCH>IXSCAN keys=144 docs=116 n=116
planner picks: equality_sort_range

Thứ tự ESR đọc nhiều khóa hơn (144 so với 116) nhưng bỏ được bước SORT. Với 2000 phiếu thì chưa thấy khác biệt, nhưng khi sắp xếp trong bộ nhớ vượt giới hạn thì lệnh sẽ lỗi, như bài Sắp xếp ở chương trước đã chạy thật. Kết quả cụ thể phụ thuộc phân bố dữ liệu, nên hãy đo trên dữ liệu của bạn [Có thể khác nhau].

Chiều sắp xếp

var a = db.sales.createIndex({ good: 1, qty: 1 })
print("same order:", st(db.sales.find().sort({ good: 1, qty: 1 }).limit(5)))
print("both reversed:", st(db.sales.find().sort({ good: -1, qty: -1 }).limit(5)))
print("mixed:", st(db.sales.find().sort({ good: 1, qty: -1 }).limit(5)))
var b = db.sales.createIndex({ good: 1, qty: -1 })
print("mixed with its own index:", st(db.sales.find().sort({ good: 1, qty: -1 }).limit(5)))
print("used:", used(db.sales.find().sort({ good: 1, qty: -1 }).limit(5)))

Kết quả [Đã chạy: MongoDB 4.4.19]:

same order: LIMIT>FETCH>IXSCAN keys=5 docs=5 n=5
both reversed: LIMIT>FETCH>IXSCAN keys=5 docs=5 n=5
mixed: SORT>COLLSCAN keys=0 docs=2000 n=5
mixed with its own index: LIMIT>FETCH>IXSCAN keys=5 docs=5 n=5
used: good_1_qty_-1

Index { good: 1, qty: 1 } phục vụ sắp xếp cùng chiều và đảo ngược toàn bộ (đi ngược trong index), nhưng không phục vụ chiều lẫn lộn { good: 1, qty: -1 }; muốn vậy phải có index đúng chiều lẫn lộn (good_1_qty_-1).

Truy vấn covered và hai index đơn

var a = db.sales.createIndex({ good: 1, qty: 1, day: 1 })
print("all in index:", st(db.sales.find({ good: "tea", qty: 3 }, { _id: 0, good: 1, qty: 1, day: 1 })))
print("one extra field:", st(db.sales.find({ good: "tea", qty: 3 }, { _id: 0, good: 1, invoice: 1 })))

Kết quả [Đã chạy: MongoDB 4.4.19]:

all in index: PROJECTION_COVERED>IXSCAN keys=51 docs=0 n=51
one extra field: PROJECTION_SIMPLE>FETCH>IXSCAN keys=51 docs=51 n=51

Khi mọi field cần trả về đều nằm trong index ghép thì không phải đọc phiếu nào (PROJECTION_COVERED). Xin thêm một field ngoài index (invoice) là phải đọc đủ 51 phiếu.

var a = db.sales.createIndex({ good: 1 })
var b = db.sales.createIndex({ qty: 1 })
var q = function () { return db.sales.find({ good: "tea", qty: 3 }) }
print("two single indexes:", st(q()))
print("rejected plans:", q().explain().queryPlanner.rejectedPlans.map(chain).join(", "))
var c = db.sales.createIndex({ good: 1, qty: 1 })
print("one compound:", st(q()))
print("used:", used(q()))

Kết quả [Đã chạy: MongoDB 4.4.19]:

two single indexes: FETCH>IXSCAN keys=270 docs=270 n=51
rejected plans: FETCH>IXSCAN, FETCH>AND_SORTED
one compound: FETCH>IXSCAN keys=51 docs=51 n=51
used: good_1_qty_1

Với hai index đơn, kế hoạch thắng chỉ dùng một index (good_1) rồi lọc qty trên 270 phiếu. Máy có cân nhắc việc gộp hai index (AND_SORTED, nằm trong các kế hoạch bị loại) nhưng không chọn nó trong dữ liệu này. Một index ghép đọc đúng 51 khóa. Việc gộp index có thể được chọn trong tình huống khác [Có thể khác nhau]; đừng trông đợi vào nó.

Index thừa

var a = db.sales.createIndex({ good: 1 })
var b = db.sales.createIndex({ good: 1, qty: 1 })
print("both exist:", used(db.sales.find({ good: "tea" })))
var d = db.sales.dropIndex("good_1")
print("after dropping good_1:", used(db.sales.find({ good: "tea" })), st(db.sales.find({ good: "tea" })))
print("qty alone:", st(db.sales.find({ qty: 3 })))

Kết quả [Đã chạy: MongoDB 4.4.19]:

both exist: good_1
after dropping good_1: good_1_qty_1 FETCH>IXSCAN keys=270 docs=270 n=270
qty alone: COLLSCAN keys=0 docs=2000 n=379

Index ghép { good: 1, qty: 1 } đã phục vụ được truy vấn chỉ có good. Sau khi xóa good_1, truy vấn good đổi sang dùng good_1_qty_1 và vẫn đọc 270 khóa như trước. Index đơn good_1 chỉ là tiền tố nên thừa: nó tốn thêm một khóa mỗi lần ghi mà không mang lại gì. Index qty một mình thì không có khóa đầu good nên không dùng được (COLLSCAN).

Tự tay làm

Dùng chung sổ 2000 phiếu. Mỗi bài nêu đầu vào, kết quả mong đợi và tiêu chí đạt.

Cơ bản

Bài 1. Điền bảng trace. Đầu vào: index { good: 1, qty: 1, day: 1 }. Bốn truy vấn: A là good và qty; B là qty một mình; C là good và day; D là day một mình. Điền bước và số khóa. Tiêu chí đạt: A đọc 51 khóa; B và D quét 2000 phiếu; C đọc 21 khóa và 11 phiếu. Gợi ý: truy vấn có khóa đầu good mới dùng được index.

Vận dụng

Bài 2. So sánh hai thứ tự. Đầu vào: truy vấn good: "salt", day <= 7, sắp xếp theo qty tăng. Tạo hai index { good: 1, day: 1, qty: 1 } (bằng, khoảng, sắp xếp) và { good: 1, qty: 1, day: 1 } (bằng, sắp xếp, khoảng), ép từng cái bằng hint. Tiêu chí đạt: thứ tự bằng, khoảng, sắp xếp có bước SORT; thứ tự bằng, sắp xếp, khoảng không có; bộ lập kế hoạch chọn thứ tự sau. Gợi ý: nhìn chuỗi bước trong kết quả của st.

Thử thách (tùy chọn)

Bài 3. Dọn index thừa. Đầu vào: ba index { good: 1 }, { good: 1, qty: 1 }, { qty: 1, good: 1 }. Tìm index thừa, xóa nó, và kiểm tra rằng các truy vấn good; good và qty; qty vẫn có index phục vụ. Tiêu chí đạt: còn lại hai index ghép, good dùng good_1_qty_1, qty dùng qty_1_good_1. Gợi ý: index nào là tiền tố của index khác?

Tự kiểm tra

Trả lời bằng lời của bạn, không nhìn lại bài.

1. (Mục tiêu 1, nhớ lại) Quy tắc tiền tố nói gì về khóa đầu?
2. (Mục tiêu 2, giải thích) Vì sao đặt field khoảng trước field sắp xếp lại sinh ra bước SORT?
3. (Mục tiêu 3, dự đoán) Index { a: 1, b: -1 } có phục vụ sort({ a: -1, b: 1 }) không?
4. (Mục tiêu 4, tìm lỗi) Bạn có { good: 1 } và { good: 1, qty: 1 }. Có cần giữ cả hai không?
5. (Mục tiêu 4, chọn cách) Truy vấn lọc good và qty, hai index đơn hay một index ghép tốt hơn, và vì sao?

Điều cần nhớ

• Index ghép xếp theo từng khóa theo thứ tự; khóa đầu là cửa vào, thiếu khóa đầu thì index không dùng được.
• ESR: field bằng trước, rồi field sắp xếp, rồi field khoảng; luôn kiểm chứng bằng số khóa đọc và sự có mặt của SORT.
• Sắp xếp cùng chiều hay ngược toàn bộ dùng được index; chiều lẫn lộn cần index đúng chiều lẫn lộn.
• Index chỉ là tiền tố của index khác thì thừa; một index ghép thường tốt hơn hai index đơn.

Cheat sheet:

Hình tóm tắt của bài là sơ đồ quy tắc tiền tố ở mục Bức tranh lớn.

Câu hỏi để dành cho ngày mai: cô Tấm muốn thêm ô tìm kiếm chữ cho ghi chú món hàng, ví dụ gõ "gạo" để thấy mọi ghi chú có chữ gạo. Index thường có làm được không, và index kiểu nào lưu từng từ?

Đào sâu

Phần này dành cho bạn tò mò.

• Cơ chế bên trong. Index ghép lưu mỗi phiếu thành một khóa gồm giá trị của cả ba field, xếp thứ tự theo field đầu rồi field sau [Suy luận: theo tài liệu]. Vì vậy dò theo khóa đầu là một đoạn liền, còn dò theo khóa sau cần nhiều đoạn nhỏ, đúng với số liệu 21 khóa ở trên.
• So sánh với lựa chọn khác. Hai index đơn có thể được gộp (AND_SORTED) nhưng kế hoạch gộp hiếm khi thắng; index ghép đúng thứ tự gần như luôn đọc ít hơn. Đổi lại, index ghép chỉ phục vụ tốt các truy vấn theo đúng tiền tố của nó.
• Hiểu lầm nâng cao. ESR không phải luật tuyệt đối: ở bài đã chạy, thứ tự bằng, khoảng, sắp xếp đọc ít khóa hơn nhưng phải sắp xếp trong bộ nhớ. Hãy đo bằng số khóa, số document và sự có mặt của SORT, đừng đo bằng cảm giác.
• Giới hạn của bài. Chưa nói về index ghép với unique, partial, index ghép trên field mảng (bài multikey) hay collation. Cách mongosh in kế hoạch là [Suy luận].
• Đọc thêm. MongoDB Manual 4.4, trang Compound Indexes: https://www.mongodb.com/docs/v4.4/core/index-compound/ và trang Create Indexes to Support Your Queries (ESR): https://www.mongodb.com/docs/v4.4/tutorial/equality-sort-range-rule/

Bài tiếp theo

Bài sau là text index trong MongoDB nhìn từ góc độ index: cấu trúc khóa, trọng số, index text ghép, wildcard text, giới hạn và cách tạo, đổi, xóa. Mini-dự án nối tiếp: ghi chú món hàng có ô tìm chữ cho Tạp Hóa Cô Tấm. Cách dùng $text đã học ở bài Text search, nên bài này không lặp lại.

Lời giải và đáp án

Làm cùng mình, Bước 2. Ba chỗ trống đã điền:

var q = function () { return db.sales.find({ good: "tea" }).sort({ qty: -1 }).limit(5) }
print("no index:", st(q()))
var a = db.sales.createIndex({ good: 1, qty: -1 })
print("good then qty:", st(q()))
var b = db.sales.createIndex({ qty: -1, good: 1 })
print("qty then good:", st(q().hint("qty_-1_good_1")))
print("top five qty:", q().map(function (d) { return d.qty }).join(" "))

no index: SORT>COLLSCAN keys=0 docs=2000 n=5
good then qty: LIMIT>FETCH>IXSCAN keys=5 docs=5 n=5
qty then good: LIMIT>FETCH>IXSCAN keys=383 docs=383 n=5
top five qty: 5 5 5 5 5

Chưa có index, máy quét 2000 phiếu rồi SORT. Với { good: 1, qty: -1 }, index đã đưa các phiếu tea theo qty giảm dần nên LIMIT chỉ cần 5 khóa, 5 phiếu. Với { qty: -1, good: 1 }, index xếp theo qty trước nên các phiếu tea bị rải rác giữa phiếu của món khác: máy đọc 383 khóa mới đủ 5 phiếu bán trà. Thứ tự sai không làm kết quả sai, chỉ làm máy làm nhiều việc hơn.

Bài 1.

var made = db.sales.createIndex({ good: 1, qty: 1, day: 1 })
print("A:", st(db.sales.find({ good: "tea", qty: 3 })))
print("B:", st(db.sales.find({ qty: 3 })))
print("C:", st(db.sales.find({ good: "tea", day: 5 })))
print("D:", st(db.sales.find({ day: 5 })))

A: FETCH>IXSCAN keys=51 docs=51 n=51
B: COLLSCAN keys=0 docs=2000 n=379
C: FETCH>IXSCAN keys=21 docs=11 n=11
D: COLLSCAN keys=0 docs=2000 n=65

A có hai khóa đầu liên tiếp. B và D thiếu khóa đầu good. C có khóa đầu nhưng bỏ qty nên đọc 21 khóa cho 11 phiếu.

Bài 2.

var q = function () { return db.sales.find({ good: "salt", day: { $lte: 7 } }).sort({ qty: 1 }) }
var a = db.sales.createIndex({ good: 1, day: 1, qty: 1 }, { name: "equality_range_sort" })
var b = db.sales.createIndex({ good: 1, qty: 1, day: 1 }, { name: "equality_sort_range" })
print("E, R, S:", st(q().hint("equality_range_sort")))
print("E, S, R:", st(q().hint("equality_sort_range")))
print("planner picks:", used(q()))

E, R, S: FETCH>SORT>IXSCAN keys=66 docs=66 n=66
E, S, R: FETCH>IXSCAN keys=72 docs=66 n=66
planner picks: equality_sort_range

Thứ tự bằng, khoảng, sắp xếp (good, day, qty) có bước SORT vì day là khoảng nên thứ tự qty bị vỡ. Thứ tự bằng, sắp xếp, khoảng (good, qty, day) không cần SORT, đọc nhiều hơn 6 khóa (72 so với 66) và bộ lập kế hoạch chọn nó.

Bài 3.

var a = db.sales.createIndex({ good: 1 })
var b = db.sales.createIndex({ good: 1, qty: 1 })
var c = db.sales.createIndex({ qty: 1, good: 1 })
var d = db.sales.dropIndex("good_1")
print("good:", used(db.sales.find({ good: "tea" })))
print("good, qty:", used(db.sales.find({ good: "tea", qty: 3 })))
print("qty:", used(db.sales.find({ qty: 3 })))
print("remaining:", names(db.sales))

good: good_1_qty_1
good, qty: good_1_qty_1
qty: qty_1_good_1
remaining: _id_, good_1_qty_1, qty_1_good_1

{ good: 1 } là tiền tố của { good: 1, qty: 1 } nên thừa. { qty: 1, good: 1 } bắt đầu bằng qty nên không thể thay bằng index kia, giữ lại.

Tự kiểm tra.

1. Truy vấn chỉ dùng được index ghép khi có khóa đầu của index; các khóa tiếp theo thu hẹp thêm nếu khớp liên tiếp.
2. Vì khoảng nhiều giá trị làm khóa sau nó không còn xếp thứ tự trên toàn bộ kết quả, nên máy phải sắp xếp lại trong bộ nhớ.
3. Có: đảo ngược toàn bộ chiều là đi ngược trong index. Mình chạy thật với index { good: 1, qty: -1 }:

var a = db.sales.createIndex({ good: 1, qty: -1 })
print("same:", st(db.sales.find().sort({ good: 1, qty: -1 }).limit(3)))
print("reversed:", st(db.sales.find().sort({ good: -1, qty: 1 }).limit(3)))
print("mixed:", st(db.sales.find().sort({ good: 1, qty: 1 }).limit(3)))

same: LIMIT>FETCH>IXSCAN keys=3 docs=3 n=3
reversed: LIMIT>FETCH>IXSCAN keys=3 docs=3 n=3
mixed: SORT>COLLSCAN keys=0 docs=2000 n=3

Cùng chiều và đảo ngược toàn bộ đều dùng index (3 khóa); chiều lẫn lộn { good: 1, qty: 1 } không khớp nên phải SORT sau COLLSCAN. 4. Không: { good: 1 } là tiền tố nên thừa; xóa nó để tiết kiệm khi ghi. 5. Một index ghép: ví dụ trên 2000 phiếu đọc 51 khóa thay vì 270, và không phụ thuộc vào việc máy có chọn gộp index hay không.