Strategia degli indici e validazione del query planner
I discenti definiranno l'insieme completo degli indici dello schema, convalideranno ogni indice con explain() ed elimineranno gli indici ridondanti.
Strategia degli indici e validazione del query planner è una lezione MongoDB Academy gratuita su CoddyKit. Questa è la lezione 2 di 4. Puoi leggere la lezione completa qui gratuitamente — poi esercitati direttamente nel browser con un editor di codice integrato e un tutor IA disponibile 24/7. Fa parte del percorso di apprendimento MongoDB Academy, e i tuoi progressi si sincronizzano tra il web e l'app CoddyKit. Il corso MongoDB Academy include 4 lezioni in totale.
Parti di questa lezione non sono ancora state tradotte e vengono mostrate in inglese.
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 winningPlanIdentifying 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 itThe 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.
Domande Frequenti
La lezione «Strategia degli indici e validazione del query planner» è gratuita?
Sì — il testo completo di «Strategia degli indici e validazione del query planner» è gratuito qui sul web. Per esercitarvi in modo interattivo (un editor di codice integrato e un tutor IA 24/7) e sbloccare il resto del corso MongoDB Academy, passa a CoddyKit PRO. Il corso MongoDB Academy include 4 lezioni in totale.
Cosa imparerò in «Strategia degli indici e validazione del query planner»?
I discenti definiranno l'insieme completo degli indici dello schema, convalideranno ogni indice con explain() ed elimineranno gli indici ridondanti. Eserciti MongoDB Academy con codice pratico che esegui direttamente nel browser, e un tutor IA 24/7 risponde alle tue domande mentre lavori sulla lezione.
Ho bisogno di esperienza per iniziare MongoDB Academy?
Non è richiesta alcuna esperienza precedente. MongoDB Academy su CoddyKit è strutturato per principianti e studenti avanzati, quindi puoi iniziare da qui o dall'inizio e procedere al tuo ritmo. Questa è la lezione 2 di 4.
Quanto tempo richiede la lezione «Strategia degli indici e validazione del query planner»?
La maggior parte delle lezioni CoddyKit richiede circa 5–10 minuti. Ogni lezione è breve e interattiva, quindi fai progressi costanti e riprendi esattamente da dove hai lasciato su web e app.
Posso scrivere ed eseguire codice in questa lezione MongoDB Academy?
Sì. Ogni lezione MongoDB Academy include un editor di codice integrato, quindi scrivi ed esegui codice reale direttamente nel tuo browser e ricevi feedback istantaneo dall'IA — nessuna configurazione locale necessaria.
Tutte le lezioni di questo corso
- Analisi dei requisiti e progettazione dello schema
- Strategia degli indici e validazione del query planner
- Piano di scalabilità: dal replica set al cluster sharded
- Messa in sicurezza e checklist per la produzione