mongo中关于普通的query查询和管道聚合查询最优使用场景

---

前言

最近在项目中使用聚合查询做普通的分页查询性能并没有普通的query查询效果好，下面详细说明一下

---

普通的查询获取总数vs聚合查询获取总数

实验数据 13000条左右；但是由于数据库设计不规范单条数据大小可达到100多k!!!

//耗时27ms左右
db.template_editors.find({
                "handle_status": {
                  "$eq": "online"
                }
              ,
                "in_nas": {
                  "$eq": 2
                }
              ,
                "is_display": {
                  "$eq": 1
                }
              }).count()
{
    "op": "command",
    "ns": "wasteHomme.template_editors",
    "command": {
        "count": "template_editors",
        "query": {"is_display": 1, "handle_status": "online", "in_nas": 2},
        "fields": {},
        "lsid": {"id": UUID("246a8f41-a001-4020-9c47-4b2566bd4663")},
        "$db": "wasteHome"
    },
    "keysExamined": 0,
    "docsExamined": 13555,
    "numYield": 105,
    "locks": {
        "Global": {"acquireCount": {"r": NumberLong(108)}},
        "Database": {"acquireCount": {"r": NumberLong(106)}},
        "Collection": {"acquireCount": {"r": NumberLong(106)}}
    },
    "storage": {},
    "responseLength": 45,
    "protocol": "op_msg",
    "millis": 26,//耗时
    "planSummary": "COLLSCAN",
    "execStats": {
        "stage": "COUNT",
        "nReturned": 0,
        "executionTimeMillisEstimate": 0,
        "works": 13557,
        "advanced": 0,
        "needTime": 13556,
        "needYield": 0,
        "saveState": 105,
        "restoreState": 105,
        "isEOF": 1,
        "invalidates": 0,
        "nCounted": 7647,
        "nSkipped": 0,
        "inputStage": {
            "stage": "COLLSCAN",
            "filter": {"$and": [{"handle_status": {"$eq": "online"}}, {"in_nas": {"$eq": 2}}, {"is_display": {"$eq": 1}}]},
            "nReturned": 7647,
            "executionTimeMillisEstimate": 0,
            "works": 13557,
            "advanced": 7647,
            "needTime": 5909,
            "needYield": 0,
            "saveState": 105,
            "restoreState": 105,
            "isEOF": 1,
            "invalidates": 0,
            "direction": "forward",
            "docsExamined": 13555
        }
    },
    "ts": ISODate("2021-04-14T07:40:00.584Z"),
    "client": "127.0.0.1",
    "appName": "MongoDB Shell",
    "allUsers": [],
    "user": ""
}

//耗时28ms左右
db.template_editors.aggregate([
     {
        $match:{
                "handle_status": {
                  "$eq": "online"
                }
              ,
                "in_nas": {
                  "$eq": 2
                }
              ,
                "is_display": {
                  "$eq": 1
                }
              }},
        {
          $group: {
            _id: null,
            count: {
              $sum: 1
            }
          }
        }
      ]
)

{
    "op": "command",
    "ns": "wasteHomme.template_editors",
    "command": {
        "aggregate": "template_editors",
        "pipeline": [{"$match": {"is_display": 1, "handle_status": "online", "in_nas": 2}}, {
            "$group": {
                "_id": null,
                "count": {"$sum": 1}
            }
        }],
        "cursor": {},
        "lsid": {"id": UUID("246a8f41-a001-4020-9c47-4b2566bd4663")},
        "$db": "wasteHomme"
    },
    "keysExamined": 0,
    "docsExamined": 13555,
    "cursorExhausted": true,
    "numYield": 105,
    "nreturned": 1,
    "locks": {
        "Global": {"acquireCount": {"r": NumberLong(109)}},
        "Database": {"acquireCount": {"r": NumberLong(107)}},
        "Collection": {"acquireCount": {"r": NumberLong(107)}}
    },
    "storage": {},
    "responseLength": 144,
    "protocol": "op_msg",
    "millis": 28,//耗时
    "planSummary": "COLLSCAN",
    "ts": ISODate("2021-04-14T07:38:54.703Z"),
    "client": "127.0.0.1",
    "appName": "MongoDB Shell",
    "allUsers": [],
    "user": ""
}

结论： 差别不大

普通的查询分页数据vs聚合查询获取分页数据（排序）

//日志只会记录5ms 以上的查询（本人设置的 可以修改）。未找到结论小于5ms
db.template_editors.find({
                "handle_status": {
                  "$eq": "online"
                }
              ,
                "in_nas": {
                  "$eq": 2
                }
              ,
                "is_display": {
                  "$eq": 1
                }
              }).sort({updated_at:-1}).skip(10).limit(20).count()

//耗时 670ms
db.template_editors.aggregate([
     {
        $match:{
                "handle_status": {
                  "$eq": "online"
                }
              ,
                "in_nas": {
                  "$eq": 2
                }
              ,
                "is_display": {
                  "$eq": 1
                }
              }},
        {
          $sort: {
           updated_at:-1
          }
        },
        {
         $skip:10
        },
        {
        $limit:20
        }
      ]
)


{
    "op": "command",
    "ns": "wasteHomme.template_editors",
    "command": {
        "aggregate": "template_editors",
        "pipeline": [{"$project": {"scenes": 0}}, {
            "$match": {
                "is_display": 1,
                "handle_status": "online",
                "in_nas": 2
            }
        }, {"$sort": {"updated_at": -1}}, {"$limit": 20}],
        "cursor": {},
        "lsid": {"id": UUID("246a8f41-a001-4020-9c47-4b2566bd4663")},
        "$db": "wasteHomme"
    },
    "keysExamined": 7654,
    "docsExamined": 7654,
    "hasSortStage": true,
    "cursorExhausted": true,
    "numYield": 62,
    "nreturned": 20,
    "locks": {
        "Global": {"acquireCount": {"r": NumberLong(129)}},
        "Database": {"acquireCount": {"r": NumberLong(127)}},
        "Collection": {"acquireCount": {"r": NumberLong(127)}}
    },
    "storage": {},
    "responseLength": 29839,
    "protocol": "op_msg",
    "millis": 670,//耗时
    "planSummary": "IXSCAN { in_nas: 1 }",
    "ts": ISODate("2021-04-14T08:04:14.797Z"),
    "client": "127.0.0.1",
    "appName": "MongoDB Shell",
    "allUsers": [],
    "user": ""
}


              

结论对于数据库数据不需要做二次计算时（例如分组 求和等操作）普通查询性能会优于聚合查询

---

总结

个人理解（欢迎指正）：
对于一般场景（不需要二次计算）请使用普通查询 ，反之 使用聚合查询

聚合查询优势在于不需要用户将数据读取到本地 进行计算，减少io数据量，从而减少时间（以及数据库计算的性能可能比你自己写的垃圾算法好），缺点就是当数据量大时 对内存的要求高（聚合计算会将match后的数据放到内存中进行剩下的管道操作）
所以对于简单的sort limit count skip 等操作呢 强烈建议使用 find查询就行。

其余查询优化就不多说了 （索引以及建表要规范）

上面一些日志参数不懂得可以参考

https://docs.mongodb.com/manual/reference/explain-results/#mongodb-data-explain.queryPlanner.winningPlan.inputStage

以及查询语句分析

https://mongodb.net.cn/manual/tutorial/analyze-query-plan/

如何开启慢查询参考

这里是引用
https://www.gxlcms.com/sql_question-466012.html