Mongodb查询执行花费太多时间 [英] Mongodb query execution take too much time

问题描述

我在Go项目上工作，我正在使用mongodb来存储我的数据.但是突然，mongodb查询执行花费了太多时间来获取数据.我有一个名为汽车"的集合，其中包含大约25000个文档，每个文档包含大约200个字段(4.385KB).我有这样的汇总查询:

Iam working on the Go project and I am using mongodb to store my data. But suddenly the mongodb query execution took too much time to get data. I have a collection named "cars" with around 25000 documents and each document containing around 200 fields (4.385KB). I have an aggregate query like this:

db.cars.aggregate([
    {
        $lookup:
        {
            from: "users",
            localField: "uid",
            foreignField: "_id",
            as: "customer_info"
        }
    },{
        $unwind: "$customer_info"
    },{
        $lookup:
        {
            from: "user_addresses",
            localField: "uid",
            foreignField: "_id",
            as: "address"
        }
    },{
        $unwind: "$address"
    },{
    $lookup:
        {
            from: "models",
            localField: "_id",
            foreignField: "car_id",
            as: "model_info"
        }
    },{
    $match:{
        purchased_on:{$gt:1538392491}, 
        status:{$in:[1,2,3,4]}, 
        "customer_info.status":{$ne:9}, 
        "model_info.status":{$ne:9},
        }
    },{
        $sort:{
            arrival_time:1
        }
    },{
        $skip:0
    },{
        $limit:5
    }
])

我的文档结构如下: https://drive.google.com/file/d/1hM-lPwvE45_213rQDYaYuYYbt3LRTgF0/view .

现在，如果运行不带索引的查询，则大约需要10分钟来加载数据.谁能建议我如何减少执行时间?

Now, If run this query with out indexing then it take around 10 mins to load the data. Can anyone suggest me how can I reduce its execution time ?

推荐答案

有许多事情可以优化您的查询.我会尝试的:

There are many things to do to optimize your query. What I would try :

• 正如Anthony Winzlet在评论中所说，尽可能将$ match阶段用作第一阶段.这样，您可以减少传递到以下阶段的文档数量，并使用索引.

• As Anthony Winzlet said in comments, use as possible $match stage as first stage. This way, you can reduce number of documents passed to the following stages, and use indexes.

假设您至少使用3.6版本的mongo，请使用"let/pipeline"语法(

Assuming you use at least 3.6 mongo version, change your lookup stages using the 'let/pipeline' syntax (see here). This way, you can integrate your 'external filters' ( "customer_info.status":{$ne:9}, "model_info.status":{$ne:9} ) in a $match stage in your lookups pipeline. With indexes on right fields / collections, you will gain some time / memory in your $lookup stages.

请尽可能早地展开阶段，以限制传递到以下阶段的文档数量.

Do your unwind stages as late as possible, to restrict number of documents passed to the following stages.

重要的是要了解聚合管道的工作方式:每个阶段都接收数据，执行其工作并将数据传递到下一个阶段.因此，传递给管道的数据越少，查询速度就越快.

It's important to understand how works aggregation pipeline : each stage receive data, do its stuff, and pass data to next stage. So the less data is passed to the pipeline, the faster will be your query.

这篇关于Mongodb查询执行花费太多时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！