mongo查询count很慢怎么办?

suparek
  • 211

我使用mongodb3.4,目前有个查询需要做分页,分页需要返回总的数量和页数。
但问题就出在这里,分页查询还算快,但计数就特别慢

计数语句如下:

db.message.find(
    {
        "field_1":"ajian",
        "field_2":{ "$exists": true },
        "field_3":{ "$exists": true },
        "field_4":{ "$exists": true },
        "field_5":{"$regex":value1},
        "field_6":{"$regex":value2}
    }
).count()

返回结果要2秒5的时间!
我explain的info结果如下:

{
    "queryPlanner" : {
        "plannerVersion" : 1,
        "namespace" : "engine.message",
        "indexFilterSet" : false,
        "parsedQuery" : {
            "$and" : [ 
                {
                    "field_1" : {
                        "$eq" : "ajian"
                    }
                }, 
                {
                    "field_2" : {
                        "$regex" : ""
                    }
                }, 
                {
                    "field_3" : {
                        "$regex" : "未处理"
                    }
                }, 
                {
                    "field_4" : {
                        "$exists" : true
                    }
                }, 
                {
                    "field_5" : {
                        "$exists" : true
                    }
                }, 
                {
                    "field_6" : {
                        "$exists" : true
                    }
                }
            ]
        },
        "winningPlan" : {
            "stage" : "COLLSCAN",
            "filter" : {
                "$and" : [ 
                    {
                        "field_1" : {
                            "$eq" : "ajian"
                        }
                    }, 
                    {
                        "field_2" : {
                            "$regex" : ""
                        }
                    }, 
                    {
                        "field_3" : {
                            "$regex" : "未处理"
                        }
                    }, 
                    {
                        "field_4" : {
                            "$exists" : true
                        }
                    }, 
                    {
                        "field_5" : {
                            "$exists" : true
                        }
                    }, 
                    {
                        "field_6" : {
                            "$exists" : true
                        }
                    }
                ]
            },
            "direction" : "forward"
        },
        "rejectedPlans" : []
    },
    "executionStats" : {
        "executionSuccess" : true,
        "nReturned" : 1537253,
        "executionTimeMillis" : 2536,
        "totalKeysExamined" : 0,
        "totalDocsExamined" : 1610191,
        "executionStages" : {
            "stage" : "COLLSCAN",
            "filter" : {
                "$and" : [ 
                    {
                        "field_1" : {
                            "$eq" : "ajian"
                        }
                    }, 
                    {
                        "field_2" : {
                            "$regex" : ""
                        }
                    }, 
                    {
                        "field_3" : {
                            "$regex" : "未处理"
                        }
                    }, 
                    {
                        "field_4" : {
                            "$exists" : true
                        }
                    }, 
                    {
                        "field_5" : {
                            "$exists" : true
                        }
                    }, 
                    {
                        "field_6" : {
                            "$exists" : true
                        }
                    }
                ]
            },
            "nReturned" : 1537253,
            "executionTimeMillisEstimate" : 2417,
            "works" : 1610193,
            "advanced" : 1537253,
            "needTime" : 72939,
            "needYield" : 0,
            "saveState" : 12588,
            "restoreState" : 12588,
            "isEOF" : 1,
            "invalidates" : 0,
            "direction" : "forward",
            "docsExamined" : 1610191
        },
        "allPlansExecution" : []
    },
    "serverInfo" : {
        "host" : "secret",
        "port" : 27017,
        "version" : "3.4.10",
        "gitVersion" : "2234234232325323343"
    },
    "ok" : 1.0
}

为什么mongo的count计数这么慢呢?大神们在做分页的时候都怎么解决这个问题的?我这个问题又该怎么解决呢?

回复
阅读 6.3k
1 个回答

简单地说,不能命中任何索引的查询需要进行全表扫描,这个过程当然是很慢的。你需要添加合适的索引。

撰写回答
你尚未登录,登录后可以
  • 和开发者交流问题的细节
  • 关注并接收问题和回答的更新提醒
  • 参与内容的编辑和改进,让解决方法与时俱进
你知道吗?

宣传栏