下载帮

您现在的位置是:首页 > 数据库 > MariaDB

MariaDB

MongoDB aggregate效能优化与排序

2022-03-20 11:58MariaDB

    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

}

MongoDB aggregate效能优化与排序

     通过优化前后的执行计划对比可知,在显示sort操作时,sort优先lookup和match操作执行,并且排序操作耗时645毫秒,相比未显示使用sort操作(mongodb内部优化器自行决定sort操作执行)时排序耗时3360快了5倍。

三、总结

     在使用mongodb的aggregate操作进行资料彙总处理时,要完整合理使用aggregate操作语法,合理安排sort、lookup、match的顺序,儘可能使SQL效能表现最大化。

文章评论