MongoDB带索引查询慢且服务崩溃问题求助

尝试从MongoDB拉取30-50MB数据时，即便已创建索引，MongoDB仍自行关闭，日志中捕获到慢查询错误：

{"t":{"$date":"2022-11-22T09:30:02.497+01:00"},"s":"I",  "c":"COMMAND",  "id":51803,   "ctx":"conn11","msg":"Slow query","attr":{"type":"command","ns":"mongodb-db.collection","appName":"MongoDB Compass","command":{"explain":{"find":"collection","filter":{"site":"somesite.com"},"sort":{"_id":1},"skip":9000,"limit":3000,"maxTimeMS":60000},"verbosity":"allPlansExecution","lsid":{"id":{"$uuid":"a157f3e5-19f9-4d60-a569-2676bdad05e6"}},"$db":"mongodb-db"},"numYields":3093,"reslen":11076,"locks":{"FeatureCompatibilityVersion":{"acquireCount":{"r":3094}},"ReplicationStateTransition":{"acquireCount":{"w":3094}},"Global":{"acquireCount":{"r":3094}},"Database":{"acquireCount":{"r":3094}},"Collection":{"acquireCount":{"r":3094}},"Mutex":{"acquireCount":{"r":1}}},"storage":{"data":{"bytesRead":3107404391,"timeReadingMicros":30193302}},"protocol":"op_msg","durationMillis":58737}}

关键问题分析 #

• 查询耗时近58秒，接近设置的60秒超时阈值，资源消耗过大
• 实际读取数据量约2.9GB，远超过预期的30-50MB，说明查询扫描了大量无关数据
• 3093次锁让出（numYields），表明查询持续占用资源，在内存有限的服务器（4核CPU、8GB内存）上，极易触发系统OOM（内存不足）机制，导致MongoDB被强制终止

解决方案 #

• 创建复合索引：针对查询的site过滤条件和_id排序，创建复合索引{site: 1, _id: 1}，让MongoDB直接通过索引定位目标数据，避免全表扫描
• 替换大skip分页：skip=9000会让MongoDB扫描并丢弃前9000条数据，效率极低。改用基于_id的游标分页：记录上一页最后一条数据的_id，将查询条件改为{site: "somesite.com", _id: {$gt: last_page_max_id}}，配合limit=3000，大幅减少数据扫描量
• 调整内存缓存配置：对于8GB内存的服务器，将WiredTiger缓存大小wiredTigerCacheSizeGB设置为3-4GB（默认是内存的50%），确保缓存足够容纳常用数据，降低磁盘IO压力
• 监控系统资源：用top/htop工具实时监控CPU、内存、磁盘IO，确认是否是内存耗尽导致MongoDB被系统终止
• 拆分查询批次：如果业务允许，将大查询拆分为多个小批次拉取，降低单查询的资源占用

内容的提问来源于stack exchange，提问作者Pamir Işık