0Pricing
MongoDB Academy · 课时

读取 explain() 输出以诊断查询

您将解读 explain 输出中的 IXSCAN 与 COLLSCAN 阶段，并根据 nReturned 与 docsExamined 的比值找出缺失的索引。

读取 explain() 输出以诊断查询 是 CoddyKit 上的免费 MongoDB Academy 课时。 这是第 4 节课，共 4 节。 你可以在下方免费阅读本课时的完整内容 — 然后在浏览器中使用内置代码编辑器和全天候 AI 导师进行实践。 这是 MongoDB Academy 学习路径的一部分，你的进度在网页和 CoddyKit 应用中同步。 MongoDB Academy 课程共包含 4 节课。

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

Why explain() Matters

Slow queries in MongoDB are usually caused by missing indexes or suboptimal query plans. The explain() method reveals exactly what MongoDB did to execute a query: which index it chose, how many documents it scanned, and how long each stage took. Without explain(), performance tuning is guesswork; with it, you get a precise diagnostic report.

// Three verbosity levels
db.users.find({ age: { $gt: 25 } }).explain();              // 'queryPlanner'
db.users.find({ age: { $gt: 25 } }).explain('executionStats'); // includes timing
db.users.find({ age: { $gt: 25 } }).explain('allPlansExecution'); // all candidate plans

queryPlanner Mode

The default explain() mode returns the query planner output: the winning plan and rejected plans, but without actually executing the query. This is fast and useful for a quick look at the plan structure. The key field is winningPlan, which describes the execution stage tree MongoDB would use.

const result = db.orders.find({ userId: 'u1' }).explain();

// winningPlan shows the chosen execution strategy
console.log(JSON.stringify(result.queryPlanner.winningPlan, null, 2));
// Example:
// { 'stage': 'FETCH',
//   'inputStage': {
//     'stage': 'IXSCAN',
//     'indexName': 'userId_1' } }

IXSCAN vs COLLSCAN

The two most important stage names in explain() output are: IXSCAN (Index Scan) — the query used an index; and COLLSCAN (Collection Scan) — MongoDB scanned every document. A COLLSCAN on a production collection with millions of documents is almost always a bug. Seeing COLLSCAN is the first sign that you need to add or refine an index.

// BAD: COLLSCAN means no usable index
// { 'stage': 'COLLSCAN', 'filter': { 'email': { '$eq': 'a@b.com' } } }

// GOOD: IXSCAN means an index was used
// { 'stage': 'IXSCAN', 'indexName': 'email_1', 'direction': 'forward' }

// Fix: create the missing index
db.users.createIndex({ email: 1 });

executionStats Mode

explain('executionStats') actually runs the query and collects timing data. The most important metrics are: nReturned — documents returned to the client; totalDocsExamined — documents MongoDB inspected; and totalKeysExamined — index entries scanned. An efficient query should have nReturned ≈ totalDocsExamined. A large gap signals wasted work.

const stats = db.orders
  .find({ userId: 'u1', status: 'active' })
  .explain('executionStats');

const s = stats.executionStats;
console.log('Returned:       ', s.nReturned);
console.log('Keys Examined:  ', s.totalKeysExamined);
console.log('Docs Examined:  ', s.totalDocsExamined);
console.log('Execution ms:   ', s.executionTimeMillis);

Interpreting Key Ratios

Three ratios tell you how efficient a query is: keys examined / keys returned (low is good, 1:1 is perfect), docs examined / docs returned (should be close to 1), and docs examined / keys examined (much greater than 1 means the index is filtering well but fetch is expensive). These ratios guide whether you need a better index, a covered query, or a different filter strategy.

// Efficiency check formula
const ratio = s.totalDocsExamined / s.nReturned;
// ratio = 1  -> perfect, index is very selective
// ratio = 10 -> for every doc returned, 10 were scanned (room to improve)
// ratio = 1000+ -> strong signal to add or redesign index

The FETCH Stage

After an IXSCAN identifies matching index entries, MongoDB may need to FETCH the actual documents from disk to check conditions not covered by the index, or to return fields not in the index. A covered query eliminates the FETCH stage entirely. If you see IXSCAN → FETCH with a high totalDocsExamined, consider adding projected fields to the index to enable coverage.

// With IXSCAN only on userId, fetching to check 'status' adds FETCH
// winningPlan:
// { stage: 'FETCH',
//   filter: { status: { $eq: 'active' } },
//   inputStage: { stage: 'IXSCAN', indexName: 'userId_1' } }

// Fix: compound index so status is in the index too
db.orders.createIndex({ userId: 1, status: 1 });
// Now: IXSCAN only, no FETCH needed for the filter

Rejected Plans and the Plan Cache

MongoDB evaluates multiple candidate plans in parallel during a trial run and picks the winner based on how many documents each plan returns per unit of work. The winning plan is cached for that query shape so future executions skip re-evaluation. You can view rejected plans using allPlansExecution verbosity. The cache is invalidated when indexes change or collection statistics update significantly.

// See all candidate plans and why the winner was chosen
const allPlans = db.orders
  .find({ userId: 'u1', status: 'active' })
  .explain('allPlansExecution');

// rejectedPlans shows what MongoDB tried but discarded
console.log(allPlans.queryPlanner.rejectedPlans.length, 'plans rejected');

SORT and SORT_KEY Stages

When MongoDB cannot use an index to satisfy a sort, it adds an in-memory SORT stage to the plan. In-memory sorts are limited to 100 MB by default; beyond that, the query fails unless you enable allowDiskUse. Seeing a SORT stage is a signal to add a compound index whose key order matches the sort, eliminating the in-memory sort entirely.

// explain shows in-memory sort when index doesn't cover the sort order
// { stage: 'SORT', sortPattern: { createdAt: -1 },
//   inputStage: { stage: 'IXSCAN', ... } }

// Fix: compound index that includes the sort field
db.orders.createIndex({ userId: 1, createdAt: -1 });
// Now the SORT stage disappears from the plan

Forcing an Index With hint()

MongoDB's query planner usually chooses the best index, but sometimes it picks a suboptimal plan (especially when statistics are stale). You can force a specific index with .hint(), passing either the index key pattern or the index name. This is useful for debugging to compare plans or as a last resort in production when the planner makes poor choices.

// Force use of a specific index by key pattern
db.orders.find({ userId: 'u1', status: 'active' })
  .hint({ userId: 1, status: 1 })
  .explain('executionStats');

// Force by index name
db.orders.find({ userId: 'u1' })
  .hint('idx_orders_user');

// Force a COLLSCAN (bypass all indexes)
db.orders.find({ userId: 'u1' })
  .hint({ $natural: 1 });

explain() on Aggregation Pipelines

Aggregation pipelines also support explain(). Pass { explain: true } to aggregate() to see how the pipeline stages execute and whether early stages like $match use indexes. The key insight: a $match at the start of the pipeline can push a filter down to an IXSCAN; a $match after a $group cannot.

// explain() on an aggregation pipeline
db.orders.explain('executionStats').aggregate([
  { $match: { userId: 'u1', status: 'active' } }, // <-- pushed to IXSCAN
  { $group: { _id: '$productId', total: { $sum: '$amount' } } },
  { $sort: { total: -1 } }
]);

Common explain() Red Flags

When reviewing explain() output, watch for these warning signs: a COLLSCAN on a large collection; totalDocsExamined much greater than nReturned; an in-memory SORT stage; or executionTimeMillis above your SLA. Each of these indicates a specific remediation: add an index, change the index key order, add sort fields to the compound index, or redesign the query.

// Red flag checklist:
// 1. stage: 'COLLSCAN' -> add index
// 2. totalDocsExamined >> nReturned -> compound index or partial index
// 3. stage: 'SORT' -> extend compound index to cover sort order
// 4. executionTimeMillis > 100 -> investigate stages above

Quick Check

Test your understanding of MongoDB explain() output from this lesson.

Lesson Recap

In this lesson you learned: explain('executionStats') runs the query and provides timing and document counts, COLLSCAN vs IXSCAN in the winning plan instantly tells you whether an index was used, and the nReturned / totalDocsExamined ratio measures index efficiency. Next up we explore text indexes for full-text search.

常见问题解答

「读取 explain() 输出以诊断查询」课时是免费的吗？

是的 — 「读取 explain() 输出以诊断查询」的完整文本可在网页上免费阅读。要进行交互式练习（内置代码编辑器和全天候 AI 导师）并解锁 MongoDB Academy 课程的其余内容，请升级到 CoddyKit PRO。 MongoDB Academy 课程共包含 4 节课。

「读取 explain() 输出以诊断查询」这节课中我会学到什么？

您将解读 explain 输出中的 IXSCAN 与 COLLSCAN 阶段，并根据 nReturned 与 docsExamined 的比值找出缺失的索引。 你通过在浏览器中直接运行的动手代码来练习 MongoDB Academy，全天候 AI 导师会在你学习这节课的过程中回答你的问题。

学习 MongoDB Academy 需要有经验吗？

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

「读取 explain() 输出以诊断查询」课时需要多长时间？

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

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

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

此课程中的所有课时

  1. MongoDB B 树索引的工作原理
  2. 创建单字段索引和复合索引
  3. 索引属性：唯一、稀疏、部分和 TTL
  4. 读取 explain() 输出以诊断查询
← 返回 MongoDB Academy