MongoDB查询耗时过长求助：附executionStats分析数据

生产环境使用MongoDB 3.4，通过Mongoose执行查询，mycollection集合约4亿条文档，当前查询耗时超200秒，以下是基于提供的executionStats数据的分析：

关键数据解读 #

先贴出查询的执行统计数据：

{    
    "op" : "query",    
    "ns" : "mydb.mycollection",    
    "query" : {
        "find" : "mycollection",
        "filter" : {
            "createdAt" : {
                "$gte" : ISODate("2022-08-05T19:30:00Z"),
                "$lte" : ISODate("2022-09-07T11:17:00Z")
            },
            "clientName" : {
                "$in" : [
                    "ap2015"
                ]
            },
            "scopeName" : {
                "$in" : [
                    "global"
                ]
            },
            "status" : "FAILED"
        },
        "sort" : {
            "createdAt" : -1
        },
        "projection" : {                    },
        "limit" : 20,
        "returnKey" : false,
        "showRecordId" : false
    },
    "keysExamined" : 19388,
    "docsExamined" : 20,
    "fromMultiPlanner" : true,
    "cursorExhausted" : true,
    "numYield" : 8795,
    "locks" : {
        "Global" : {
            "acquireCount" : {
                "r" : NumberLong(17592)
            }
        },
        "Database" : {
            "acquireCount" : {
                "r" : NumberLong(8796)
            }
        },
        "Collection" : {
            "acquireCount" : {
                "r" : NumberLong(8796)
            }
        }
    },
    "nreturned" : 20,
    "responseLength" : 23901,
    "protocol" : "op_query",
    "millis" : 200328,
    "planSummary" : "IXSCAN { clientName: 1.0, createdAt: -1.0, status: 1.0, scopeName: 1.0, Status: 1.0 }",
    "execStats" : {
        "stage" : "LIMIT",
        "nReturned" : 20,
        "executionTimeMillisEstimate" : 984,
        "works" : 19389,
        "advanced" : 20,
        "needTime" : 19368,
        "needYield" : 0,
        "saveState" : 8795,
        "restoreState" : 8795,
        "isEOF" : 1,
        "invalidates" : 0,
        "limitAmount" : 20,
        "inputStage" : {
            "stage" : "FETCH",
            "nReturned" : 20,
            "executionTimeMillisEstimate" : 984,
            "works" : 19388,
            "advanced" : 20,
            "needTime" : 19368,
            "needYield" : 0,
            "saveState" : 8795,
            "restoreState" : 8795,
            "isEOF" : 0,
            "invalidates" : 0,
            "docsExamined" : 20,
            "alreadyHasObj" : 0,
            "inputStage" : {
                "stage" : "IXSCAN",
                "nReturned" : 20,
                "executionTimeMillisEstimate" : 974,
                "works" : 19388,
                "advanced" : 20,
                "needTime" : 19368,
                "needYield" : 0,
                "saveState" : 8795,
                "restoreState" : 8795,
                "isEOF" : 0,
                "invalidates" : 0,
                "keyPattern" : {
                    "clientName" : 1,
                    "createdAt" : -1,
                    "status" : 1,
                    "scopeName" : 1,
                    "Status" : 1
                },
                "indexName" : "clientName_1_createdAt_-1_status_1_scopeName_1_Status_1",
                "isMultiKey" : false,
                "multiKeyPaths" : {
                    "clientName" : [ ],
                    "createdAt" : [ ],
                    "status" : [ ],
                    "scopeName" : [ ],
                    "Status" : [ ]
                },
                "isUnique" : false,
                "isSparse" : false,
                "isPartial" : true,
                "indexVersion" : 2,
                "direction" : "forward",
                "indexBounds" : {
                    "clientName" : [
                        "[\"ap2015\", \"ap2015\"]"
                    ],
                    "createdAt" : [
                        "[new Date(1662549420000), new Date(1659727800000)]"
                    ],
                    "status" : [
                        "[\"FAILED\", \"FAILED\"]"
                    ],
                    "scopeName" : [
                        "[\"global\", \"global\"]"
                    ],
                    "Status" : [
                        "[MinKey, MaxKey]"
                    ]
                },
                "keysExamined" : 19388,
                "seeks" : 19369,
                "dupsTested" : 0,
                "dupsDropped" : 0,
                "seenInvalidated" : 0
            }
        }
    },
    "ts" : ISODate("2022-09-06T11:28:59.115Z"),
    "client" : "192.168.105.130",
    "allUsers" : [
        {
            "user" : "backofficeap2015",
            "db" : "mydb"
        }
    ],
    "user" : "backofficeap2015@mydb"
}

从数据里能看到几个核心问题：

• 总耗时与执行预估差距极大：实际耗时millis:200328（约200秒），但执行时间预估只有984毫秒，说明大部分时间没花在查询执行上，而是耗在锁等待和yield上。
• 大量yield操作：numYield:8795，意味着查询过程中被迫释放锁8000多次，给其他操作让路，来回切换状态严重拖慢了整体速度。
• 索引扫描效率极低：扫描了19388个索引键才找到20条目标数据，而且seeks:19369，几乎每一次推进都要做一次seek跳转，说明索引没法直接定位到符合条件的数据。
• 索引存在冗余字段：索引里同时包含status和Status（大小写不同），其中Status字段在查询里根本没用到，索引范围被迫扩大到[MinKey, MaxKey]，完全浪费了索引空间和扫描效率。
• Partial索引存疑：当前使用的是partial索引，需要确认索引的过滤条件是否覆盖了当前查询的所有文档，如果不匹配，MongoDB要么跳过索引，要么扫描大量无效数据。

优化建议 #

• 重构索引：删掉现有的包含冗余Status字段的索引，创建顺序更合理的复合索引：{clientName:1, status:1, scopeName:1, createdAt:-1}。这个顺序先过滤所有等值条件（clientName、status、scopeName），再按createdAt倒序排序，能直接定位到目标数据，大幅减少keysExamined和seeks的数量。
• 验证Partial索引有效性：检查该partial索引的过滤条件，确保当前查询的文档都满足过滤规则，避免索引失效或扫描无效数据。如果不需要partial索引，直接创建普通复合索引更好。
• 降低服务器锁竞争：排查MongoDB实例的CPU、内存、磁盘IO负载，看看是否有其他慢查询、大写入操作抢占资源。可以通过db.currentOp()查看当前运行的操作，优先处理高负载任务。
• 考虑升级MongoDB版本：MongoDB 3.4是比较旧的版本，后续版本（比如4.0+）在锁机制（文档级锁优化）、查询优化器、索引性能上有很大提升，能有效减少yield次数和锁等待时间。
• 验证新查询计划：创建新索引后，用db.mycollection.find(...).explain("executionStats")重新查看执行计划，确认keysExamined、seeks和millis都大幅下降。

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