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": "$_id",
"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": "$_id",
"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": "$_id",
"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": "$_id",
"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": "$_id",
"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": "$_id",
"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": "$_id",
"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": "$_id",
"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效能表現最大化。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29357786/viewspace-2877200/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- mongodb效能優化MongoDB優化
- MongoDB效能優化指南MongoDB優化
- MongoDB：aggregate與aggregateCursorMongoDB
- 開發高效能的MongoDB應用：淺談MongoDB效能優化MongoDB優化
- MongoDB索引與優化詳解MongoDB索引優化
- 實戰 MongoDB AggregateMongoDB
- mongodb aggregate 實踐MongoDB
- MongoDB 效能優化五個簡單步驟MongoDB優化
- Mongodb優化MongoDB優化
- redis學習(六) 排序（sort,by，store,效能優化）Redis排序優化
- Nginx安全優化與效能調優Nginx優化
- Golang效能分析與優化Golang優化
- 前端工程與效能優化前端優化
- mongodb之使用explain和hint效能分析和優化MongoDBAI優化
- PHP 7革新與效能優化PHP優化
- MongoDB副本集學習(三)：效能和優化相關MongoDB優化
- 【前端效能優化】vue效能優化前端優化Vue
- MongoDB的效能調優工具 -- DexMongoDB
- Orderby 排序優化排序優化
- 外部排序優化排序優化
- 效能優化指南：效能優化的一般性原則與方法優化
- 效能分析優化的道與術優化
- FlutterWeb效能優化探索與實踐FlutterWeb優化
- 前端效能優化原理與實踐前端優化
- babel-polyfill使用與效能優化Babel優化
- PHP 7 的革新與效能優化PHP優化
- 資料庫效能優化-索引與sql相關優化資料庫優化索引SQL
- jemalloc Mongodb Nginx 優化MongoDBNginx優化
- Spark讀取MongoDB資料的方法與優化SparkMongoDB優化
- Java 效能優化之——效能優化的過程方法與求職面經總結Java優化求職
- 直播分享| 騰訊雲 MongoDB 智慧診斷及效能優化實踐MongoDB優化
- 效能優化優化
- 效能優化案例-SQL優化優化SQL
- Flutter 應用效能檢測與優化Flutter優化
- Java常見排序演算法之插入排序-簡單的效能優化技巧Java排序演算法優化
- 快速排序及其優化排序優化
- [Hive]Hive排序優化Hive排序優化
- 快速排序及優化排序優化