0Pricing
MongoDB Academy · Aula

Estratégia de índices e validação do planejador de consultas

Os alunos definirão o conjunto completo de índices do esquema, validarão cada índice com explain() e removerão índices redundantes.

Estratégia de índices e validação do planejador de consultas é uma aula grátis de MongoDB Academy no CoddyKit. Esta é a aula 2 de 4. Você pode ler a aula completa abaixo gratuitamente — depois pratica ao vivo no navegador com um editor de código integrado e um tutor de IA 24/7. Faz parte do caminho de aprendizado de MongoDB Academy, e seu progresso é sincronizado entre a web e o app CoddyKit. O curso de MongoDB Academy inclui 4 aulas no total.

Partes desta aula ainda não foram traduzidas e aparecem em inglês.

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.

Perguntas Frequentes

A aula “Estratégia de índices e validação do planejador de consultas” é grátis?

Sim — o texto completo de “Estratégia de índices e validação do planejador de consultas” é grátis para ler aqui na web. Para praticá-la interativamente (um editor de código integrado e um tutor de IA 24/7) e desbloquear o restante do curso de MongoDB Academy, atualize para CoddyKit PRO. O curso de MongoDB Academy inclui 4 aulas no total.

O que vou aprender em “Estratégia de índices e validação do planejador de consultas”?

Os alunos definirão o conjunto completo de índices do esquema, validarão cada índice com explain() e removerão índices redundantes. Você pratica MongoDB Academy com código prático que executa diretamente no navegador, e um tutor de IA 24/7 responde suas dúvidas enquanto trabalha na aula.

Preciso ter experiência prévia para começar MongoDB Academy?

Nenhuma experiência prévia é necessária. MongoDB Academy no CoddyKit é estruturado para alunos iniciantes até avançados, então você pode começar aqui ou desde o início e aprender no seu ritmo. Esta é a aula 2 de 4.

Quanto tempo leva a aula “Estratégia de índices e validação do planejador de consultas”?

A maioria das aulas CoddyKit leva cerca de 5–10 minutos. Cada uma é compacta e interativa, então você faz progresso constante e retoma exatamente de onde parou entre web e app.

Posso escrever e executar código nesta aula de MongoDB Academy?

Sim. Cada aula de MongoDB Academy inclui um editor de código integrado, então você escreve e executa código real direto no navegador e recebe feedback de IA instantaneamente — nenhuma configuração local necessária.

Todas as aulas deste curso

  1. Análise de requisitos e projeto de esquema
  2. Estratégia de índices e validação do planejador de consultas
  3. Plano de escalabilidade: de conjunto de réplicas a cluster fragmentado
  4. Reforço da segurança e lista de verificação de produção
← Voltar para MongoDB Academy