MongoDB aggregate效能优化与排序
MongoDB 在使用aggregate操作对资料进行处理时，需要显示排序操作，否则效能上会有很大的差别。最近，对mongodb
的aggregate慢操作优化时就遇到一个类似案例。
一、优化前后的操作对比
--优化前的SQL
db.getCollection("work_item").aggregate(
[{ "$match" : { "$and" : [{ "projectId" : "6204d665233a5963b1032840"}, { "parentLocalFormCategory" : "demand"}, { "isDeleted" : 0}]}},
{
"$project":
{
"idStr":
{
"$convert": {
"input": "$_id",
"to": "string"
}
},
}
}, {
"$lookup":
{
"from": "version",
"localField": "idStr",
"foreignField": "workItemIds",
"as": "versions"
}
}]
).explain("executionStats");
--優化後的SQL
db.getCollection("work_item").aggregate(
[
{
"$project":
{
"idStr":
{
"$convert": {
"input": "$_id",
"to": "string"
}
},"_id":1,"createTime":1,"rootParentId":1,"projectId":1,"parentLocalFormCategory":1,"isDeleted":1
}
},{ "$sort" : { "_id" : -1}},
{
"$lookup":
{
"from": "version",
"localField": "idStr",
"foreignField": "workItemIds",
"as": "versions"
}
},
{ "$match" : { "$and" : [{ "projectId" : "6204d665233a5963b1032840"} , { "parentLocalFormCategory" : "demand"}, { "isDeleted" : 0},{ "versions._id" : ObjectId('620da463e522ed57d617f7f0')}]}},{ "$skip" :0}, { "$limit" :12}]
).explain("executionStats");
注意：优化前后match和lookup操作的先后顺序不影响SQL的执行计划，后续通过优化前后的SQL执行计划对比可以得知；优化后的SQL显示使用{“$sort”:{"_id":-1}}，也就是这个操作彻底改变了SQL的效能表现。
二、SQL优化前后的执行计划
--优化前的SQL执行计划
{
"stages": [
{
"$cursor": {
"queryPlanner": {
"plannerVersion": NumberInt("1"),
"namespace": "xtc_devsecops_test.work_item",
"indexFilterSet": false,
"parsedQuery": {
"$and": [
{
"isDeleted": {
"$eq": 0
}
},
{
"parentLocalFormCategory": {
"$eq": "demand"
}
},
{
"projectId": {
"$eq": "6204d665233a5963b1032840"
}
}
]
},
"queryHash": "D387943C",
"planCacheKey": "7C8EC593",
"winningPlan": {
"stage": "PROJECTION_DEFAULT",
"transformBy": {
"_id": true,
"rootParentId": true,
"isDeleted": true,
"parentLocalFormCategory": true,
"createTime": true,
"projectId": true,
"idStr": {
"$convert": {
"input": "!---->
"to": {
"$const": "string"
}
}
}
},
"inputStage": {
"stage": "FETCH",
"filter": {
"$and": [
{
"isDeleted": {
"$eq": 0
}
},
{
"parentLocalFormCategory": {
"$eq": "demand"
}
}
]
},
"inputStage": {
"stage": "IXSCAN",
"keyPattern": {
"projectId": NumberInt("1")
},
"indexName": "projectId",
"isMultiKey": false,
"multiKeyPaths": {
"projectId": [ ]
},
"isUnique": false,
"isSparse": false,
"isPartial": false,
"indexVersion": NumberInt("2"),
"direction": "forward",
"indexBounds": {
"projectId": [
"[\"6204d665233a5963b1032840\", \"6204d665233a5963b1032840\"]"
]
}
}
}
},
"rejectedPlans": [
{
"stage": "PROJECTION_DEFAULT",
"transformBy": {
"_id": true,
"rootParentId": true,
"isDeleted": true,
"parentLocalFormCategory": true,
"createTime": true,
"projectId": true,
"idStr": {
"$convert": {
"input": "!---->
"to": {
"$const": "string"
}
}
}
},
"inputStage": {
"stage": "FETCH",
"filter": {
"$and": [
{
"isDeleted": {
"$eq": 0
}
},
{
"projectId": {
"$eq": "6204d665233a5963b1032840"
}
}
]
},
"inputStage": {
"stage": "IXSCAN",
"keyPattern": {
"parentLocalFormCategory": NumberInt("1")
},
"indexName": "parentLocalFormCategory",
"isMultiKey": false,
"multiKeyPaths": {
"parentLocalFormCategory": [ ]
},
"isUnique": false,
"isSparse": false,
"isPartial": false,
"indexVersion": NumberInt("2"),
"direction": "forward",
"indexBounds": {
"parentLocalFormCategory": [
"[\"demand\", \"demand\"]"
]
}
}
}
},
{
"stage": "PROJECTION_DEFAULT",
"transformBy": {
"_id": true,
"rootParentId": true,
"isDeleted": true,
"parentLocalFormCategory": true,
"createTime": true,
"projectId": true,
"idStr": {
"$convert": {
"input": "!---->
"to": {
"$const": "string"
}
}
}
},
"inputStage": {
"stage": "FETCH",
"filter": {
"$and": [
{
"parentLocalFormCategory": {
"$eq": "demand"
}
},
{
"projectId": {
"$eq": "6204d665233a5963b1032840"
}
},
{
"isDeleted": {
"$eq": 0
}
}
]
},
"inputStage": {
"stage": "AND_SORTED",
"inputStages": [
{
"stage": "IXSCAN",
"keyPattern": {
"parentLocalFormCategory": NumberInt("1")
},
"indexName": "parentLocalFormCategory",
"isMultiKey": false,
"multiKeyPaths": {
"parentLocalFormCategory": [ ]
},
"isUnique": false,
"isSparse": false,
"isPartial": false,
"indexVersion": NumberInt("2"),
"direction": "forward",
"indexBounds": {
"parentLocalFormCategory": [
"[\"demand\", \"demand\"]"
]
}
},
{
"stage": "IXSCAN",
"keyPattern": {
"projectId": NumberInt("1")
},
"indexName": "projectId",
"isMultiKey": false,
"multiKeyPaths": {
"projectId": [ ]
},
"isUnique": false,
"isSparse": false,
"isPartial": false,
"indexVersion": NumberInt("2"),
"direction": "forward",
"indexBounds": {
"projectId": [
"[\"6204d665233a5963b1032840\", \"6204d665233a5963b1032840\"]"
]
}
}
]
}
}
}
]
},
"executionStats": {
"executionSuccess": true,
"nReturned": NumberInt("23478"),
"executionTimeMillis": NumberInt("3365"),
"totalKeysExamined": NumberInt("23875"),
"totalDocsExamined": NumberInt("23875"),
"executionStages": {
"stage": "PROJECTION_DEFAULT",
"nReturned": NumberInt("23478"),
"executionTimeMillisEstimate": NumberInt("265"),
"works": NumberInt("23876"),
"advanced": NumberInt("23478"),
"needTime": NumberInt("397"),
"needYield": NumberInt("0"),
"saveState": NumberInt("31"),
"restoreState": NumberInt("31"),
"isEOF": NumberInt("1"),
"transformBy": {
"_id": true,
"rootParentId": true,
"isDeleted": true,
"parentLocalFormCategory": true,
"createTime": true,
"projectId": true,
"idStr": {
"$convert": {
"input": "!---->
"to": {
"$const": "string"
}
}
}
},
"inputStage": {
"stage": "FETCH",
"filter": {
"$and": [
{
"isDeleted": {
"$eq": 0
}
},
{
"parentLocalFormCategory": {
"$eq": "demand"
}
}
]
},
"nReturned": NumberInt("23478"),
"executionTimeMillisEstimate": NumberInt("157"),
"works": NumberInt("23876"),
"advanced": NumberInt("23478"),
"needTime": NumberInt("397"),
"needYield": NumberInt("0"),
"saveState": NumberInt("31"),
"restoreState": NumberInt("31"),
"isEOF": NumberInt("1"),
"docsExamined": NumberInt("23875"),
"alreadyHasObj": NumberInt("0"),
"inputStage": {
"stage": "IXSCAN",
"nReturned": NumberInt("23875"),
"executionTimeMillisEstimate": NumberInt("68"),
"works": NumberInt("23876"),
"advanced": NumberInt("23875"),
"needTime": NumberInt("0"),
"needYield": NumberInt("0"),
"saveState": NumberInt("31"),
"restoreState": NumberInt("31"),
"isEOF": NumberInt("1"),
"keyPattern": {
"projectId": NumberInt("1")
},
"indexName": "projectId",
"isMultiKey": false,
"multiKeyPaths": {
"projectId": [ ]
},
"isUnique": false,
"isSparse": false,
"isPartial": false,
"indexVersion": NumberInt("2"),
"direction": "forward",
"indexBounds": {
"projectId": [
"[\"6204d665233a5963b1032840\", \"6204d665233a5963b1032840\"]"
]
},
"keysExamined": NumberInt("23875"),
"seeks": NumberInt("1"),
"dupsTested": NumberInt("0"),
"dupsDropped": NumberInt("0")
}
}
}
}
},
"nReturned": NumberLong("23478"),
"executionTimeMillisEstimate": NumberLong("330")
},
{
"$lookup": {
"from": "version",
"as": "versions",
"localField": "idStr",
"foreignField": "workItemIds"
},
"nReturned": NumberLong("23478"),
"executionTimeMillisEstimate": NumberLong("3359")
},
{
"$match": {
"versions._id": {
"$eq": ObjectId("620da464e522ed57d617fa03")
}
},
"nReturned": NumberLong("13"),
"executionTimeMillisEstimate": NumberLong("3360")
},
{
"$sort": {
"sortKey": {
"_id": NumberInt("-1")
},
"limit": NumberLong("10")
},
"nReturned": NumberLong("10"),
"executionTimeMillisEstimate": NumberLong("3360")
}
],
"serverInfo": {
"host": "devsecops-mongo-test",
"port": NumberInt("27017"),
"version": "4.4.6",
"gitVersion": "72e66213c2c3eab37d9358d5e78ad7f5c1d0d0d7"
},
"ok": 1
}
--优化后的SQL执行计划
{
"stages": [
{
"$cursor": {
"queryPlanner": {
"plannerVersion": NumberInt("1"),
"namespace": "xtc_devsecops_test.work_item",
"indexFilterSet": false,
"parsedQuery": {
"$and": [
{
"isDeleted": {
"$eq": 0
}
},
{
"parentLocalFormCategory": {
"$eq": "demand"
}
},
{
"projectId": {
"$eq": "6204d665233a5963b1032840"
}
}
]
},
"queryHash": "D387943C",
"planCacheKey": "7C8EC593",
"winningPlan": {
"stage": "PROJECTION_DEFAULT",
"transformBy": {
"_id": true,
"rootParentId": true,
"isDeleted": true,
"parentLocalFormCategory": true,
"createTime": true,
"projectId": true,
"idStr": {
"$convert": {
"input": "!---->
"to": {
"$const": "string"
}
}
}
},
"inputStage": {
"stage": "FETCH",
"filter": {
"$and": [
{
"isDeleted": {
"$eq": 0
}
},
{
"parentLocalFormCategory": {
"$eq": "demand"
}
}
]
},
"inputStage": {
"stage": "IXSCAN",
"keyPattern": {
"projectId": NumberInt("1")
},
"indexName": "projectId",
"isMultiKey": false,
"multiKeyPaths": {
"projectId": [ ]
},
"isUnique": false,
"isSparse": false,
"isPartial": false,
"indexVersion": NumberInt("2"),
"direction": "forward",
"indexBounds": {
"projectId": [
"[\"6204d665233a5963b1032840\", \"6204d665233a5963b1032840\"]"
]
}
}
}
},
"rejectedPlans": [
{
"stage": "PROJECTION_DEFAULT",
"transformBy": {
"_id": true,
"rootParentId": true,
"isDeleted": true,
"parentLocalFormCategory": true,
"createTime": true,
"projectId": true,
"idStr": {
"$convert": {
"input": "!---->
"to": {
"$const": "string"
}
}
}
},
"inputStage": {
"stage": "FETCH",
"filter": {
"$and": [
{
"isDeleted": {
"$eq": 0
}
},
{
"projectId": {
"$eq": "6204d665233a5963b1032840"
}
}
]
},
"inputStage": {
"stage": "IXSCAN",
"keyPattern": {
"parentLocalFormCategory": NumberInt("1")
},
"indexName": "parentLocalFormCategory",
"isMultiKey": false,
"multiKeyPaths": {
"parentLocalFormCategory": [ ]
},
"isUnique": false,
"isSparse": false,
"isPartial": false,
"indexVersion": NumberInt("2"),
"direction": "forward",
"indexBounds": {
"parentLocalFormCategory": [
"[\"demand\", \"demand\"]"
]
}
}
}
},
{
"stage": "PROJECTION_DEFAULT",
"transformBy": {
"_id": true,
"rootParentId": true,
"isDeleted": true,
"parentLocalFormCategory": true,
"createTime": true,
"projectId": true,
"idStr": {
"$convert": {
"input": "!---->
"to": {
"$const": "string"
}
}
}
},
"inputStage": {
"stage": "FETCH",
"filter": {
"$and": [
{
"parentLocalFormCategory": {
"$eq": "demand"
}
},
{
"projectId": {
"$eq": "6204d665233a5963b1032840"
}
},
{
"isDeleted": {
"$eq": 0
}
}
]
},
"inputStage": {
"stage": "AND_SORTED",
"inputStages": [
{
"stage": "IXSCAN",
"keyPattern": {
"parentLocalFormCategory": NumberInt("1")
},
"indexName": "parentLocalFormCategory",
"isMultiKey": false,
"multiKeyPaths": {
"parentLocalFormCategory": [ ]
},
"isUnique": false,
"isSparse": false,
"isPartial": false,
"indexVersion": NumberInt("2"),
"direction": "forward",
"indexBounds": {
"parentLocalFormCategory": [
"[\"demand\", \"demand\"]"
]
}
},
{
"stage": "IXSCAN",
"keyPattern": {
"projectId": NumberInt("1")
},
"indexName": "projectId",
"isMultiKey": false,
"multiKeyPaths": {
"projectId": [ ]
},
"isUnique": false,
"isSparse": false,
"isPartial": false,
"indexVersion": NumberInt("2"),
"direction": "forward",
"indexBounds": {
"projectId": [
"[\"6204d665233a5963b1032840\", \"6204d665233a5963b1032840\"]"
]
}
}
]
}
}
}
]
},
"executionStats": {
"executionSuccess": true,
"nReturned": NumberInt("23478"),
"executionTimeMillis": NumberInt("663"),
"totalKeysExamined": NumberInt("23875"),
"totalDocsExamined": NumberInt("23875"),
"executionStages": {
"stage": "PROJECTION_DEFAULT",
"nReturned": NumberInt("23478"),
"executionTimeMillisEstimate": NumberInt("549"),
"works": NumberInt("23876"),
"advanced": NumberInt("23478"),
"needTime": NumberInt("397"),
"needYield": NumberInt("0"),
"saveState": NumberInt("40"),
"restoreState": NumberInt("40"),
"isEOF": NumberInt("1"),
"transformBy": {
"_id": true,
"rootParentId": true,
"isDeleted": true,
"parentLocalFormCategory": true,
"createTime": true,
"projectId": true,
"idStr": {
"$convert": {
"input": "!---->
"to": {
"$const": "string"
}
}
}
},
"inputStage": {
"stage": "FETCH",
"filter": {
"$and": [
{
"isDeleted": {
"$eq": 0
}
},
{
"parentLocalFormCategory": {
"$eq": "demand"
}
}
]
},
"nReturned": NumberInt("23478"),
"executionTimeMillisEstimate": NumberInt("254"),
"works": NumberInt("23876"),
"advanced": NumberInt("23478"),
"needTime": NumberInt("397"),
"needYield": NumberInt("0"),
"saveState": NumberInt("40"),
"restoreState": NumberInt("40"),
"isEOF": NumberInt("1"),
"docsExamined": NumberInt("23875"),
"alreadyHasObj": NumberInt("0"),
"inputStage": {
"stage": "IXSCAN",
"nReturned": NumberInt("23875"),
"executionTimeMillisEstimate": NumberInt("119"),
"works": NumberInt("23876"),
"advanced": NumberInt("23875"),
"needTime": NumberInt("0"),
"needYield": NumberInt("0"),
"saveState": NumberInt("40"),
"restoreState": NumberInt("40"),
"isEOF": NumberInt("1"),
"keyPattern": {
"projectId": NumberInt("1")
},
"indexName": "projectId",
"isMultiKey": false,
"multiKeyPaths": {
"projectId": [ ]
},
"isUnique": false,
"isSparse": false,
"isPartial": false,
"indexVersion": NumberInt("2"),
"direction": "forward",
"indexBounds": {
"projectId": [
"[\"6204d665233a5963b1032840\", \"6204d665233a5963b1032840\"]"
]
},
"keysExamined": NumberInt("23875"),
"seeks": NumberInt("1"),
"dupsTested": NumberInt("0"),
"dupsDropped": NumberInt("0")
}
}
}
}
},
"nReturned": NumberLong("23478"),
"executionTimeMillisEstimate": NumberLong("611")
},
{
"$sort": {
"sortKey": {
"_id": NumberInt("-1")
}
},
"nReturned": NumberLong("12"),
"executionTimeMillisEstimate": NumberLong("645")
},
{
"$lookup": {
"from": "version",
"as": "versions",
"localField": "idStr",
"foreignField": "workItemIds"
},
"nReturned": NumberLong("12"),
"executionTimeMillisEstimate": NumberLong("655")
},
{
"$match": {
"versions._id": {
"$eq": ObjectId("620da464e522ed57d617fa03")
}
},
"nReturned": NumberLong("10"),
"executionTimeMillisEstimate": NumberLong("655")
},
{
"$limit": NumberLong("10"),
"nReturned": NumberLong("10"),
"executionTimeMillisEstimate": NumberLong("655")
}
],
"serverInfo": {
"host": "devsecops-mongo-test",
"port": NumberInt("27017"),
"version": "4.4.6",
"gitVersion": "72e66213c2c3eab37d9358d5e78ad7f5c1d0d0d7"
},
"ok": 1
}
通过优化前后的执行计划对比可知，在显示sort操作时，sort优先lookup和match操作执行，并且排序操作耗时645毫秒，相比未显示使用sort操作（mongodb内部优化器自行决定sort操作执行）时排序耗时3360快了5倍。
三、总结
在使用mongodb的aggregate操作进行资料彙总处理时，要完整合理使用aggregate操作语法，合理安排sort、lookup、match的顺序，儘可能使SQL效能表现最大化。
微信收款码
支付宝收款码