MongoDB Academy · 课时

索引交集与复合索引

您将了解 MongoDB 何时会合并多个单字段索引，以及复合索引何时优于索引交集。

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

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

What Is Index Intersection?

Index intersection is MongoDB's ability to use two or more single-field indexes simultaneously to satisfy a single query. Instead of building one compound index that covers all filter fields, MongoDB scans multiple indexes independently, then takes the intersection of their matching document IDs. It sounds convenient, but in practice it is rarely as fast as a well-designed compound index.

How Index Intersection Works Internally

When MongoDB considers intersecting indexes, the query planner: 1) Scans index A for documents matching condition 1 and collects their record IDs. 2) Scans index B for documents matching condition 2. 3) Computes the intersection of the two ID sets. 4) Fetches the actual documents using those IDs. This is called an AND_SORTED or AND_HASH stage in explain() output.

// With two single-field indexes:
db.orders.createIndex({ status: 1 })
db.orders.createIndex({ customerId: 1 })

// Query may intersect both indexes
db.orders.find({ status: 'pending', customerId: 'c001' })
  .explain('executionStats')
// Look for 'AND_SORTED' or 'AND_HASH' stage in winningPlan

When MongoDB Chooses Intersection

MongoDB uses index intersection only when its query planner calculates that it is cheaper than alternatives. The planner runs up to 200 candidate plans in parallel (using trial execution) and picks the one with the lowest estimated cost. Intersection is more likely to be chosen when the collection is large and each individual index is highly selective — both narrow the candidate set dramatically before the intersection step.

// Check if MongoDB chose to intersect indexes
const plan = db.orders.find({
  status: 'pending',
  customerId: 'c001'
}).explain('executionStats')

// Intersection chosen:
print(JSON.stringify(plan.queryPlanner.winningPlan, null, 2))
// Look for: 'stage': 'AND_SORTED'

Compound Index vs Intersection: Key Difference

A compound index stores keys from multiple fields in a single, pre-sorted B-tree. A query on those fields does a single, efficient index scan and returns documents in sorted order. Index intersection does multiple separate scans and then merges the results in memory. The merge step adds CPU and memory overhead that a compound index avoids entirely.

// Compound index: one scan, sorted output
db.orders.createIndex({ status: 1, customerId: 1 })

// Single scan: fast, no in-memory merge
db.orders.find({ status: 'pending', customerId: 'c001' })
// explain(): IXSCAN stage only — no AND_SORTED

Compound Indexes Win on Sort

Index intersection cannot satisfy a sort — the merged result set is not in any particular order relative to the sort key, so MongoDB must perform an in-memory SORT stage. A compound index that includes the sort field delivers results in order directly from the index, avoiding the sort overhead entirely. For queries that both filter and sort, a compound index almost always wins.

// Index intersection + sort = in-memory sort required
db.orders.find({ status: 'pending', customerId: 'c001' })
  .sort({ createdAt: 1 })
// Even if both status and customerId indexes intersect,
// MongoDB must still sort the merged result in memory

// Compound index avoids the sort stage
db.orders.createIndex({ status: 1, customerId: 1, createdAt: 1 })

When Intersection Can Outperform Compound

Index intersection occasionally beats a compound index when: 1) Both individual indexes are highly selective (each returns very few documents). 2) The query is ad-hoc — you cannot predict which fields will be filtered together so building a compound index for every combination is impractical. 3) The collection is write-heavy — fewer indexes means lower write overhead, so using intersection from two existing indexes avoids adding a third.

Controlling the Query Planner With hint()

You can force MongoDB to use a specific index (or intersection strategy) with .hint(). This bypasses the query planner's automatic selection and is useful for benchmarking — you can compare the execution stats of your manually chosen compound index versus what the planner would do with individual indexes.

// Force a specific compound index
db.orders.find({ status: 'pending', customerId: 'c001' })
  .hint({ status: 1, customerId: 1 })
  .explain('executionStats')

// Force use of a single-field index (no intersection)
db.orders.find({ status: 'pending', customerId: 'c001' })
  .hint({ status: 1 })
  .explain('executionStats')

Detecting Intersection in explain() Output

When MongoDB uses index intersection, the explain() output shows an AND_SORTED or AND_HASH stage as the parent of two IXSCAN stages. AND_SORTED is used when both indexes return results in the same sorted order; AND_HASH builds an in-memory hash of one result set and probes it with the other. Both are signs that a well-chosen compound index could be faster.

// Identify intersection usage
const plan = db.orders
  .find({ status: 'pending', region: 'EU' })
  .explain('executionStats')

// Check for AND_SORTED or AND_HASH
// If found, benchmark against a compound index { status:1, region:1 }

The General Rule: Prefer Compound Indexes

For known, repeated query patterns, a compound index is almost always faster than relying on intersection. The only reasons to prefer intersection are: queries are too unpredictable to cover with compound indexes, or write throughput is so high that adding more indexes is too costly. In those cases, keep individual indexes lean and let the planner intersect when it helps.

Index Audit: Removing Redundant Indexes

As applications evolve, developers add indexes reactively. Over time, collections accumulate redundant indexes that slow writes without benefiting reads. Review regularly using $indexStats: any index with zero accesses.ops over a long period is unused and can be dropped. Also look for indexes made redundant by the compound prefix rule.

// Identify unused indexes
db.orders.aggregate([{ $indexStats: {} }])
// { name: 'status_1', accesses: { ops: 0, since: ... } }
// If ops is 0 since a long time, the index is unused — drop it

db.orders.dropIndex('status_1')

Practical Guidance: A Decision Framework

Use this framework: Query pattern is known and repeated? → Build a compound index using ESR. Query is ad-hoc or hard to predict? → Rely on individual indexes and accept possible intersection. Write throughput is critical? → Minimise total index count; remove unused indexes. Query involves a sort? → Always use a compound index; intersection never satisfies sorts.

Quick Check

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

Lesson Recap

In this lesson you learned: index intersection uses two single-field indexes and merges their results in memory, adding overhead, compound indexes are almost always faster for known, repeated query patterns — especially those with sorts, and use $indexStats to find and remove unused or redundant indexes that slow writes. Next up we explore aggregation pipeline optimization tips.

常见问题解答

「索引交集与复合索引」课时是免费的吗？

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

「索引交集与复合索引」这节课中我会学到什么？

您将了解 MongoDB 何时会合并多个单字段索引，以及复合索引何时优于索引交集。你通过在浏览器中直接运行的动手代码来练习 MongoDB Academy，全天候 AI 导师会在你学习这节课的过程中回答你的问题。

学习 MongoDB Academy 需要有经验吗？

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

「索引交集与复合索引」课时需要多长时间？

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

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

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

此课程中的所有课时

← 返回 MongoDB Academy