0Pricing
MongoDB Academy · 课时

复合索引前缀规则和 ESR 原则

您将把等值-排序-范围索引设计原则应用于复合索引，以最大化查询覆盖率。

复合索引前缀规则和 ESR 原则 是 CoddyKit 上的免费 MongoDB Academy 课时。 这是第 2 节课，共 4 节。 你可以在下方免费阅读本课时的完整内容 — 然后在浏览器中使用内置代码编辑器和全天候 AI 导师进行实践。 这是 MongoDB Academy 学习路径的一部分，你的进度在网页和 CoddyKit 应用中同步。 MongoDB Academy 课程共包含 4 节课。

本课时的部分内容尚未翻译，以英文显示。

Why Compound Index Field Order Matters

A compound index on multiple fields can serve a wide range of queries — but only if the fields appear in the right order. MongoDB can use a compound index to satisfy a query only if the query's filter matches a prefix of the index. The ordering of fields in the index definition directly controls which queries benefit from it.

The Prefix Rule Explained

A compound index { a: 1, b: 1, c: 1 } can be used by queries that filter on: { a }, { a, b }, or { a, b, c }. These are the prefixes. A query filtering only on { b } or { b, c } cannot use this index — it would do a collection scan. The index is like a phone book sorted by last name, then first name: you can look up by last name alone or by last + first, but not by first name alone.

// Index: { status: 1, customerId: 1, createdAt: 1 }
db.orders.createIndex({ status: 1, customerId: 1, createdAt: 1 })

// Uses index (prefix: status)
db.orders.find({ status: 'pending' })

// Uses index (prefix: status + customerId)
db.orders.find({ status: 'pending', customerId: 'c001' })

// Does NOT use index (no leading prefix)
db.orders.find({ customerId: 'c001' })

The ESR Principle: Equality, Sort, Range

The ESR principle is a field-ordering rule for compound indexes: put Equality fields first, Sort fields second, and Range fields last. This ordering maximises the portion of the query the index can satisfy and minimises the number of index entries that must be examined. ESR is the most important compound index design rule in MongoDB.

// Query: find pending orders for customer c001, sorted by date,
// for dates after Jan 2025
// E: status = 'pending' (equality)
// S: createdAt (sort)
// R: customerId in ['c001','c002'] (range / $in)

// ESR-ordered index
db.orders.createIndex({ status: 1, createdAt: 1, customerId: 1 })

Why Equality Fields Come First

Equality predicates (field: value or $eq) narrow the index scan to a single, fixed value. Placing them first dramatically reduces the number of index entries the query planner needs to consider. Once equality has pinpointed the exact bucket of matching keys, the sort and range operations work on a much smaller dataset.

// E first: status equality narrows to ~5% of index
// Then sort on createdAt within that slice
// Then range on amount within that sorted slice
db.orders.createIndex({ status: 1, createdAt: 1, amount: 1 })

db.orders.find({ status: 'shipped' })
  .sort({ createdAt: -1 })
  .hint({ status: 1, createdAt: 1, amount: 1 })

Why Sort Fields Come Before Range

Placing sort fields before range fields allows MongoDB to use the index to satisfy the sort without a blocking in-memory sort. If range fields come before sort fields, MongoDB must scan all matching range documents, sort them in memory, then return results — adding CPU and memory overhead. With sort fields second, results emerge from the index already in the correct order.

// Without ESR: range before sort forces in-memory sort
db.orders.createIndex({ status: 1, amount: 1, createdAt: 1 })

db.orders.find({ status: 'pending', amount: { $gt: 50 } })
  .sort({ createdAt: 1 })
// explain() shows: SORT stage (in-memory sort needed)

// With ESR: sort before range avoids in-memory sort
db.orders.createIndex({ status: 1, createdAt: 1, amount: 1 })
// explain() shows: no SORT stage

Range Fields Last: Why It Works

Range predicates like $gt, $lt, $gte, $lte, $in, and regex span a contiguous portion of the index. By placing them last, MongoDB first narrows results with equality and delivers them in sort order, then applies the range check as a final filter. The index scan stays efficient because range does not break the sorted traversal order set by the sort fields.

// ESR applied correctly
// E: userId (equality)
// S: timestamp (sort)
// R: score (range)
db.events.createIndex({ userId: 1, timestamp: 1, score: 1 })

db.events.find({
  userId: 'u123',
  score: { $gte: 80 }
}).sort({ timestamp: -1 })

Handling $in: Range or Equality?

$in with a small list of values behaves more like equality and can be placed first. When the list is large, it acts more like a range and should go later in the index. A useful rule: if the $in list has fewer than 10–20 values and you query it frequently, treat it as equality (first). For large, dynamic lists, treat it as range (last).

// Small $in (2 values) — treat as equality, put first
db.orders.createIndex({ status: 1, createdAt: 1 })
db.orders.find({ status: { $in: ['pending', 'processing'] } })
  .sort({ createdAt: -1 })

// Large $in — treat as range, put last
db.orders.createIndex({ region: 1, createdAt: 1, userId: 1 })
db.orders.find({
  region: 'EU',
  userId: { $in: hundredsOfUserIds }
}).sort({ createdAt: -1 })

Verifying ESR With explain()

Always verify your index design with explain('executionStats'). Look for: IXSCAN (index scan) — good. COLLSCAN (collection scan) — missing index. SORT stage present — in-memory sort, index field order might be wrong. keysExamined / nReturned should be close to 1 for an optimal compound index.

db.orders.find({ status: 'pending', amount: { $gt: 50 } })
  .sort({ createdAt: 1 })
  .explain('executionStats')

// Good: { stage: 'IXSCAN', nReturned: 42, keysExamined: 44 }
// Bad:  { stage: 'COLLSCAN', nReturned: 42, docsExamined: 500000 }

The Prefix Rule and Partial Index Reuse

Thanks to the prefix rule, a single well-designed compound index can replace several single-field indexes. An index on { a: 1, b: 1, c: 1 } makes separate indexes on { a: 1 } and { a: 1, b: 1 } redundant. Fewer indexes means less write overhead and less memory pressure — important for write-heavy workloads where index maintenance adds latency to every insert, update, and delete.

// One compound index replaces three single-field indexes
db.users.createIndex({ country: 1, city: 1, age: 1 })

// Redundant (covered by compound prefix rule):
// db.users.createIndex({ country: 1 })          -- REDUNDANT
// db.users.createIndex({ country: 1, city: 1 }) -- REDUNDANT

Index Selectivity and Field Order

Beyond ESR, consider selectivity — how many documents share the same value. Put the most selective equality field first (fewest duplicates). For example, userId is more selective than status. Placing the more selective field first narrows the scan faster. When multiple equality fields exist, order them most-selective to least-selective for maximum performance.

// userId is highly selective (millions of users)
// status is low-selectivity (only 5 values)

// More efficient: selective equality field first
db.orders.createIndex({ userId: 1, status: 1, createdAt: 1 })

// Less efficient: low-selectivity field first
db.orders.createIndex({ status: 1, userId: 1, createdAt: 1 })

Putting ESR Into Practice

When designing a compound index, start by listing your top query's filter conditions and sort, then classify each field as E (equality), S (sort), or R (range). Build the index in that order. Run explain('executionStats') to confirm you see an IXSCAN with no SORT stage and a keysExamined/nReturned ratio near 1. Revisit the index whenever query patterns change.

// Practical checklist:
// Query: find users in 'NY' (E), sorted by signup (S), age > 18 (R)
// E: state = 'NY'
// S: signupDate
// R: age > 18
db.users.createIndex({ state: 1, signupDate: 1, age: 1 })

// Verify no in-memory sort and good key ratio:
db.users.find({ state: 'NY', age: { $gt: 18 } })
  .sort({ signupDate: -1 })
  .explain('executionStats')

Quick Check

Test your understanding of MongoDB & NoSQL Databases concepts from this lesson.

Lesson Recap

In this lesson you learned: a compound index can only be used when the query matches a prefix of the index fields, the ESR principle dictates ordering fields as Equality, Sort, Range for maximum query coverage, and placing sort fields before range fields eliminates costly in-memory sort stages. Next up we compare index intersection versus compound indexes.

常见问题解答

「复合索引前缀规则和 ESR 原则」课时是免费的吗？

是的 — 「复合索引前缀规则和 ESR 原则」的完整文本可在网页上免费阅读。要进行交互式练习（内置代码编辑器和全天候 AI 导师）并解锁 MongoDB Academy 课程的其余内容，请升级到 CoddyKit PRO。 MongoDB Academy 课程共包含 4 节课。

「复合索引前缀规则和 ESR 原则」这节课中我会学到什么？

您将把等值-排序-范围索引设计原则应用于复合索引，以最大化查询覆盖率。 你通过在浏览器中直接运行的动手代码来练习 MongoDB Academy，全天候 AI 导师会在你学习这节课的过程中回答你的问题。

学习 MongoDB Academy 需要有经验吗？

无需任何先前经验。CoddyKit 上的 MongoDB Academy 课程适合初学者到高级学习者，你可以从这里开始或从头开始，按照自己的节奏学习。 这是第 2 节课，共 4 节。

「复合索引前缀规则和 ESR 原则」课时需要多长时间？

大多数 CoddyKit 课程大约需要 5–10 分钟。每节课都很精短且互动，所以你能稳步进步，并在网页和应用中从离开的地方继续。

我能在这节 MongoDB Academy 课中编写并运行代码吗？

能。每节 MongoDB Academy 课都包含内置代码编辑器，你可以在浏览器中直接编写并运行真实代码，并获得即时 AI 反馈 — 无需本地设置。

此课程中的所有课时

  1. 数据库分析器和慢查询日志
  2. 复合索引前缀规则和 ESR 原则
  3. 索引交集与复合索引
  4. 聚合管道优化技巧
← 返回 MongoDB Academy