MongoDB索引决策：最大化性能并最小化索引的技术咨询

Hey Joe, let’s tackle this index design problem step by step—since the goal is to maximize query performance while keeping indexes as lean as possible, we need to anchor everything to your actual query patterns (I’ll cover the most common inventory-related queries below, but adjust based on your real usage).

First, let’s clarify the document structure I’m assuming (based on your description):

{
  "_id": ObjectId("..."),
  "catalog": "CATALOG_X",
  "location": "NYC",
  "good": "Wireless Headphones",
  "stock_or_not": 1 // 1 = in stock, 0 = out of stock
}

Each (catalog, location, good) tuple is unique, mapping to a single stock_or_not value.

Core Principles to Guide Indexing #

• Prioritize high-frequency queries: Build indexes for the queries you run most often, not edge cases.
• Use compound indexes for multiple filters: A well-structured compound index can cover multiple query patterns, eliminating the need for separate single-field indexes.
• Leverage partial indexes: Since stock_or_not only has two values, indexing only in-stock (or out-of-stock) documents drastically reduces index size if one state is queried far more often.
• Prefix matching matters: MongoDB uses the leftmost prefix of compound indexes, so order fields by how often they’re used as filters (or by cardinality—higher-cardinality fields first to narrow results faster).

Recommended Index Strategies #

1. Primary Compound Partial Index (Covers Most Common Queries) #

If your top queries are:

• "Show all in-stock goods in CATALOG_X for location NYC"
• "Check if Wireless Headphones are in stock at NYC in CATALOG_X"
• "Count in-stock goods per location in CATALOG_X"

Create this partial index (smaller, faster, and enforces data uniqueness):

db.inventory.createIndex(
  { catalog: 1, location: 1, good: 1 },
  { 
    unique: true, // Ensures no duplicate (catalog, location, good) entries
    partialFilterExpression: { stock_or_not: 1 } // Only index in-stock items
  }
)

Why this works:

• Exact match support: Queries for (catalog, location, good) hit the index directly—if the document isn’t in the index, it’s out of stock.
• Prefix filter support: Queries filtering by catalog + location (to get all in-stock goods at a location) use the index’s leftmost prefix, avoiding full collection scans.
• Data integrity: The unique constraint prevents duplicate inventory entries for the same catalog/location/good.
• Smaller footprint: By only indexing in-stock items, you cut down on index storage (critical if out-of-stock entries are common).

2. Secondary Index (For Reverse Prefix Queries) #

If you frequently run queries like:

• "Show all locations where Wireless Headphones are in stock in CATALOG_X"

The primary index won’t help here (since it uses catalog + location + good as the prefix, not catalog + good). For this case, add a second partial index:

db.inventory.createIndex(
  { catalog: 1, good: 1, location: 1 },
  { partialFilterExpression: { stock_or_not: 1 } }
)

This covers the reverse prefix pattern while still keeping the index small via partial filtering.

3. Handling Out-of-Stock Queries #

If out-of-stock queries are rare, skip indexing them—MongoDB will do a targeted scan only when needed. If they’re common, mirror the above indexes but with partialFilterExpression: { stock_or_not: 0 }, but only if the performance gain justifies the extra index storage.

Bonus: Covered Queries for Maximum Speed #

If your queries only return specific fields (e.g., just good for in-stock items at a location), the primary index already acts as a covered query—MongoDB can return results directly from the index without accessing the full document. For example:

// This uses the primary index as a covered query (no document lookups)
db.inventory.find(
  { catalog: "CATALOG_X", location: "NYC", stock_or_not: 1 },
  { good: 1, _id: 0 }
)

内容的提问来源于stack exchange，提问作者JoeSlav