前言
最近在项目中使用聚合查询做普通的分页查询性能并没有普通的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