0Pricing
MongoDB Academy · 课时

索引策略与查询规划器验证

学习者将为模式定义完整的索引集合，使用 explain() 验证每个索引，并删除冗余索引。

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

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

Index Strategy Overview

An index strategy is a deliberate plan for which indexes to create, not an ad-hoc collection of indexes added whenever a query is slow. Every index has a cost: it speeds up reads but slows down writes (each write must update all indexes on the collection) and consumes RAM (indexes must fit in the working set). A good strategy creates the minimum number of indexes that cover all high-frequency access patterns.

Start With the Access Pattern Register

Map every high-frequency access pattern identified during requirements analysis to a proposed index. Document the index fields, sort direction, and purpose. For our e-commerce platform: the product page uses { slug: 1 }; the category listing uses { categoryId: 1, price: 1, _id: 1 } for keyset pagination; the order history uses { userId: 1, createdAt: -1 }. This register prevents redundant indexes and missed coverage.

// Index register for e-commerce capstone
const indexRegister = [
  { collection: 'products', index: { slug: 1 },                        unique: true,  covers: 'product page' },
  { collection: 'products', index: { categoryId: 1, price: 1, _id: 1 }, unique: false, covers: 'category listing + keyset' },
  { collection: 'products', index: { 'vendor._id': 1 },                 unique: false, covers: 'vendor store page' },
  { collection: 'orders',   index: { userId: 1, createdAt: -1 },        unique: false, covers: 'user order history' },
  { collection: 'orders',   index: { status: 1, createdAt: 1 },         unique: false, covers: 'fulfillment queue' },
  { collection: 'reviews',  index: { productId: 1, createdAt: -1 },     unique: false, covers: 'reviews by product' }
]

Applying the ESR Rule to Compound Indexes

The ESR rule (Equality → Sort → Range) determines field order in compound indexes. Place equality filter fields first (they reduce the candidate set most), sort fields next (so MongoDB can serve the sort from the index), and range filter fields last. This ordering maximises index coverage and lets MongoDB avoid an in-memory sort stage.

// Query: products in category, price under 200, sorted by price
// E = categoryId (equality), S = price (sort), R = none
// Correct ESR order:
db.products.createIndex({ categoryId: 1, price: 1 })

// Query: orders by user, status = 'processing', sorted by date
// E = userId (equality) + status (equality), S = createdAt
db.orders.createIndex({ userId: 1, status: 1, createdAt: -1 })

// Verify with explain
db.orders.find({ userId: userId, status: 'processing' })
  .sort({ createdAt: -1 })
  .explain('executionStats')

Creating the Index Set for the Capstone

Create all planned indexes in a single script so they can be applied atomically to any environment (local, staging, production). Run index creation in the background ({ background: true } in older versions; background by default in MongoDB 4.2+) so it does not block the collection during creation. Always test index creation on a staging environment before running on production data.

// indexes/setup.js — run once per environment
async function createIndexes(db) {
  await db.collection('products').createIndexes([
    { key: { slug: 1 }, unique: true },
    { key: { categoryId: 1, price: 1, _id: 1 } },
    { key: { tags: 1 } },
    { key: { 'vendor._id': 1 } }
  ])

  await db.collection('orders').createIndexes([
    { key: { userId: 1, createdAt: -1 } },
    { key: { status: 1, createdAt: 1 } },
    { key: { 'items.productId': 1 } }
  ])

  await db.collection('reviews').createIndexes([
    { key: { productId: 1, createdAt: -1 } },
    { key: { userId: 1 } }
  ])

  console.log('All indexes created')
}

Validating Indexes With explain()

After creating indexes, validate each critical query using .explain('executionStats'). Look for four key fields in the output: winningPlan.inputStage.stage should be 'IXSCAN' (not 'COLLSCAN'); totalKeysExamined should be close to nReturned; totalDocsExamined should equal nReturned for a covered query; and executionTimeMillis should be acceptably low.

// Validate product category listing query
const result = await db.collection('products')
  .find({ categoryId: new ObjectId('...'), price: { $lte: 200 } })
  .sort({ price: 1 })
  .explain('executionStats')

const { winningPlan, totalKeysExamined, totalDocsExamined, nReturned, executionTimeMillis } = result.executionStats

console.log('Stage:', winningPlan.inputStage.stage)  // IXSCAN or COLLSCAN
console.log('Keys / Docs / Returned:', totalKeysExamined, totalDocsExamined, nReturned)
console.log('Time ms:', executionTimeMillis)

Covered Queries: Eliminating FETCH

A covered query is one where all projected fields are available in the index itself — MongoDB never needs to fetch the actual document. Covered queries are extremely fast because they read only the index (smaller, fits in RAM) rather than loading documents. For the product listing page that needs only slug, name, price, and imageUrl, create an index that includes all these fields.

// Covered index for product listing cards
db.products.createIndex({
  categoryId: 1,
  price: 1,
  name: 1,
  slug: 1,
  imageUrl: 1
})

// This query is now covered — no FETCH stage
db.products.find(
  { categoryId: ObjectId('...') },
  { _id: 0, name: 1, slug: 1, price: 1, imageUrl: 1 }
).sort({ price: 1 }).explain('executionStats')
// Verify: no FETCH stage in winningPlan

Identifying Redundant Indexes

Indexes are expensive — each one adds write overhead. A redundant index is one whose prefix is identical to another index. If you have { userId: 1 } and { userId: 1, createdAt: -1 }, the first is redundant because the compound index satisfies all queries that the single-field index would. Use db.collection.aggregate([{ $indexStats: {} }]) to see which indexes have low access counts and consider dropping them.

// Find rarely used indexes
db.orders.aggregate([{ $indexStats: {} }])
  .then(stats => {
    stats.forEach(s => {
      console.log(s.name, 'accesses:', s.accesses.ops)
    })
  })
// Indexes with ops = 0 since last restart may be candidates for removal

// Drop a redundant index
db.orders.dropIndex('userId_1')  // if userId_1_createdAt_-1 already covers it

The Text Index for Product Search

Full-text search on product names, descriptions, and tags requires a text index. Create a compound text index covering all searchable string fields. Add a weights option to rank name matches higher than description matches. Validate that $text queries use TEXT stage in explain and return results sorted by textScore.

// Text index for product search
db.products.createIndex(
  { name: 'text', description: 'text', tags: 'text' },
  { weights: { name: 10, tags: 5, description: 1 }, name: 'product_text_idx' }
)

// Text search query with relevance sorting
db.products.find(
  { $text: { $search: 'wireless noise cancelling' } },
  { score: { $meta: 'textScore' } }
).sort({ score: { $meta: 'textScore' } }).limit(20)

Sparse and Partial Indexes for Optional Fields

Some documents have optional fields that only a subset of documents carry. Create a partial index with partialFilterExpression to index only the documents where the field exists and meets the condition. This keeps the index small and efficient compared to indexing null values in a sparse index. For orders with a couponCode field (present on only 10% of orders), a partial index is ideal.

// Partial index: only index orders that have a coupon
db.orders.createIndex(
  { couponCode: 1 },
  {
    partialFilterExpression: { couponCode: { $exists: true } },
    name: 'orders_with_coupon'
  }
)

// Partial index: only index active products
db.products.createIndex(
  { categoryId: 1, price: 1 },
  {
    partialFilterExpression: { isActive: true },
    name: 'active_products_by_category'
  }
)

Unique Indexes for Data Integrity

Unique indexes prevent duplicate data at the database level — the safest place to enforce uniqueness. Create unique indexes on fields that must be unique: user.email, product.slug, vendor.slug. A unique index is more reliable than application-level checks because it prevents duplicates even under race conditions or concurrent writes from multiple application instances.

// Unique indexes for business constraints
db.users.createIndex({ email: 1 }, { unique: true })
db.products.createIndex({ slug: 1 }, { unique: true })
db.vendors.createIndex({ slug: 1 }, { unique: true })

// Partial unique index: unique email only for verified users
db.users.createIndex(
  { email: 1 },
  {
    unique: true,
    partialFilterExpression: { emailVerified: true }
  }
)

Index Maintenance and Monitoring

Indexes need ongoing maintenance. Use MongoDB Compass or Atlas Performance Advisor to monitor query performance and receive index suggestions automatically. The Performance Advisor analyses slow queries (those exceeding the slowMs threshold) and suggests compound indexes to improve them. Periodically review $indexStats to prune indexes that are no longer being used as query patterns evolve.

// Enable profiling to capture slow queries
db.setProfilingLevel(1, { slowms: 50 })  // log queries > 50ms

// Query the profiler for the slowest recent operations
db.system.profile.find().sort({ millis: -1 }).limit(10).pretty()

// Check index usage statistics (reset on mongod restart)
db.products.aggregate([{ $indexStats: {} }])

Quick Check

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

Lesson Recap

In this lesson you learned: the ESR rule (Equality → Sort → Range) determines the optimal field order in compound indexes for maximum coverage, explain('executionStats') validates that queries use IXSCAN and reveals the documents examined vs returned ratio, and $indexStats identifies redundant or unused indexes that should be pruned to reduce write overhead. Next up we draft the scaling plan — from replica set to sharded cluster.

常见问题解答

「索引策略与查询规划器验证」课时是免费的吗？

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

「索引策略与查询规划器验证」这节课中我会学到什么？

学习者将为模式定义完整的索引集合，使用 explain() 验证每个索引，并删除冗余索引。 你通过在浏览器中直接运行的动手代码来练习 MongoDB Academy，全天候 AI 导师会在你学习这节课的过程中回答你的问题。

学习 MongoDB Academy 需要有经验吗？

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

「索引策略与查询规划器验证」课时需要多长时间？

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

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

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

此课程中的所有课时

  1. 需求分析与模式设计
  2. 索引策略与查询规划器验证
  3. 扩展计划：从副本集到分片集群
  4. 安全加固与生产环境检查清单
← 返回 MongoDB Academy