首页 文章

减少mongodb查询（使用查找）执行时间

提问于
浏览 次
3

大家好，

我正在和mongodb一起工作 . 我有一个集合名称预订，共有19,000份文件 . 我的查询需要3秒才能执行，我只想要每个查询只有20条记录 . 我在查询中有多个查找 .

我添加了索引如下：

{ 
    status: 1, 
    end_date_timestamp: 1, 
    is_visible: 1, 
    arrival_date_time: 1 
}

我的mongodb查询日志如下：

{ 
        aggregate: "bookings", 
            pipeline: [ 
                { $lookup: 
                    { 
                        localField: "uid", 
                        from: "users", 
                        foreignField: "_id", 
                        as: "customer_info" 
                    } 
                }, 
                { $unwind: "$customer_info" }, 
                { $lookup: 
                    { 
                        foreignField: "_id", 
                        as: "provider_info", 
                        localField: "provider_ids", 
                        from: "users" 
                    } 
                }, 
                { $match: 
                    { status: { $in: [ 0, 6, 7, 8 ] }, 
                    end_date_timestamp: { $gte: 1539839212 }, 
                    is_visible: true, 
                    customer_info.status: { $ne: 9 }, 
                    provider_info.status: { $ne: 9 } } 
                }, 
                { $lookup: 
                    { 
                        localField: "address_id", 
                        from: "user_addresses", 
                        foreignField: "_id", 
                        as: "address" 
                    } 
                }, { $unwind: "$address" }, 
                { $sort: { arrival_date_time: 1 } }, 
                { $skip: 0 }, 
                { $limit: 20 }, 
                { $project: 
                    { 
                        reminder_before_day_hour_time: 0, 
                        customer_info.apt_no: 0, 
                        customer_info.invitation_sent: 0, 
                        provider_info.password: 0, 
                    } 
                } 
            ], 
            cursor: {}, 
            $readPreference: 
            { mode: "secondaryPreferred" }, 
            $db: "ironetwork_bk_db" } 
            planSummary: IXSCAN 
            { 
                status: 1, 
                end_date_timestamp: 1, 
                is_visible: 1, 
                arrival_date_time: 1 
            } 
            keysExamined:16281 
            docsExamined:16277 
            hasSortStage:1 
            cursorExhausted:1 
            numYields:183 
            nreturned:20 
            reslen:102932 
            locks:{ 
                Global: 
                    { acquireCount: { r: 97909 } }, 
                Database: 
                    { acquireCount: { r: 97909 } }, 
                Collection: 
                    { acquireCount: { r: 97908 } } 
            } 
            protocol:op_query 3352ms

请建议我如何减少查询执行时间？

mongodb

1 回答

  • 0

    您可以使用以下优化的管道

    db.bookings.aggregate([
  { "$match": {
    "status": { "$in": [ 0, 6, 7, 8 ] }, 
    "end_date_timestamp": { "$gte": 1539839212 }, 
    "is_visible": true
  }},
  { "$lookup": { 
    "from": "users", 
    "let": { "uid": "$uid" },
    "pipeline": [
      { "$match": { "$expr": { "$eq": ["$_id", "$$uid" ] }}},
      { "$project": { "apt_no": 0, "invitation_sent": 0 }}
    ],
    "as": "customer_info"
  }},
  { "$unwind": "$customer_info" },
  { "$match": { "customer_info.status": { "$ne": 9 }},
  { "$lookup": {
    "from": "users",
    "let": { "provider_ids": "$provider_ids" },
    "pipeline": [
      { "$match": { "$expr": { "$eq": ["$_id", "$$provider_ids" ] }}},
      { "$project": { "password": 0 }}
    ],
    "as": "provider_info"
  }},
  { "$unwind": "$provider_info" },
  { "$match": { "provider_info.status": { "$ne": 9 }}
  { "$lookup": { 
    "from": "user_addresses",
    "let": { "address_id": "$address_id" },
    "pipeline": [
      { "$match": { "$expr": { "$eq": ["$_id", "$$address_id" ] }}}
    ],
    "as": "address"
  }},
  { "$unwind": "$address" },
  { "$sort": { "arrival_date_time": 1 } }, 
  { "$skip": 0 }, 
  { "$limit": 20 }, 
  { "$project": { "reminder_before_day_hour_time": 0 }}
])
    回复于

相关问题