优化MongoDB聚合查询：快速获取KPI集合中的最大extractionDate值

Hey there! Dealing with slow queries on large datasets is never fun, so let's break down how to speed up your request to find the maximum extractionDate for specific type values in your KPI collection.

First, Let's Diagnose the Current Issue #

Your current aggregation pipeline runs a $match → $project → $group → $project flow. Even with an index on extractionDate, the $group stage has to scan all documents that pass the $match filter to compute the max value—this is why it's taking 20+ seconds with 42.8M docs. The single-field index on extractionDate doesn't help much here because your filter is on type, not extractionDate.

Top Optimization Strategies #

1. Create a Compound Index for the Filter + Sort

The biggest win will come from building a compound index that supports both your type filter and the max extractionDate lookup:

db.kpi.createIndex({ type: 1, extractionDate: -1 })

• Why this works: MongoDB can quickly filter documents by the type values in your $in clause using the first part of the index. Since extractionDate is sorted in descending order, the first document in the filtered index results is already the maximum value—no need to scan all matching docs.

2. Replace the Aggregation with a find() + sort() + limit() Query

Instead of using aggregation (which forces a full scan of matching docs for the $group), you can use a simpler find query that leverages the compound index directly:

Java Code:

@Override
public Mono<DBObject> getLastExtractionDate(List<String> targetTypes) {
    Query query = new Query(Criteria.where("type").in(targetTypes))
            // Sort by extractionDate descending to get the largest value first
            .with(Sort.by(Sort.Direction.DESC, EXTRACTION_DATE))
            // Only need the first result (the max value)
            .limit(1)
            // Fetch only extractionDate, exclude _id
            .fields().include(EXTRACTION_DATE).exclude("_id");

    return mongoTemplate.findOne(query, DBObject.class, "kpi")
            .map(resultDoc -> {
                DBObject finalResult = new BasicDBObject();
                finalResult.put("result", resultDoc.get(EXTRACTION_DATE));
                return finalResult;
            });
}

Corresponding MongoDB Query:

db.kpi.find(
    { "type": { "$in": ["INACTIVE_SITE", "DEVICE_NOT_BILLED", "NOT_REPLYING_POLLING", "MISSING_KEY_TECH_INFO", "MISSING_SITE", "ACTIVE_CIRCUITS_INACTIVE_RESOURCES", "INCONSISTENT_STATUS_VALUES"] } },
    { "extractionDate": 1, "_id": 0 }
).sort({ "extractionDate": -1 }).limit(1)

• This cuts out the expensive $group stage entirely. The compound index lets MongoDB jump straight to the largest extractionDate for your filtered type values, returning results in milliseconds instead of seconds.

3. Verify Index Usage with explain()

To make sure your index is being used, run the explain plan on your original aggregation (or the new find query):

// For the aggregation
db.kpi.aggregate([
    { "$match" : { "type" : { "$in" : ["INACTIVE_SITE", "DEVICE_NOT_BILLED", "NOT_REPLYING_POLLING", "MISSING_KEY_TECH_INFO", "MISSING_SITE", "ACTIVE_CIRCUITS_INACTIVE_RESOURCES", "INCONSISTENT_STATUS_VALUES"]}}},
    { "$project" : { "extractionDate" : 1, "_id" : 0}},
    { "$group" : { "_id" : null, "result" : { "$max" : "$extractionDate"}}},
    { "$project" : { "_id" : 0}}
]).explain("executionStats")

// For the find query
db.kpi.find(
    { "type": { "$in": ["INACTIVE_SITE", "DEVICE_NOT_BILLED", "NOT_REPLYING_POLLING", "MISSING_KEY_TECH_INFO", "MISSING_SITE", "ACTIVE_CIRCUITS_INACTIVE_RESOURCES", "INCONSISTENT_STATUS_VALUES"] } },
    { "extractionDate": 1, "_id": 0 }
).sort({ "extractionDate": -1 }).limit(1).explain("executionStats")

• Look for stage: "IXSCAN" in the execution stats—this confirms the index is being used instead of a full collection scan (COLLSCAN).

4. (Optional) Consider Sharding for Long-Term Scalability

If your dataset keeps growing, sharding the KPI collection by type (or a combination of type and extractionDate) can distribute the data across multiple nodes, allowing parallel processing of queries. This is a more involved change, but worth evaluating if you expect continued growth.

Quick Recap #

The fastest fix is to:

1. Add the compound index {type: 1, extractionDate: -1}
2. Replace your aggregation pipeline with a find+sort+limit query

This should drastically reduce your query time from 20+ seconds to something near instantaneous.

内容的提问来源于stack exchange，提问作者mr.Penguin