记一次Mongodb聚合查询慢查询简单分析

比眉伴天荒 2021-10-09 09:12 574阅读 0赞

记一次Mongodb聚合查询

一、慢查询

db.public_setting_detail.aggregate([
    {
        "$project": {
            "creator": 1,
            "publicStatus": 1,
            "publicObject": 1,
            "isdir": 1,
            "status": 1,
            "resourceId": 1,
            "createTime": 1
        }
    }, {
        "$match": {
            "creator": 32235,
            "publicStatus": 1,
            "publicObject": {
                "$elemMatch": {
                    "classId": {
                        "$in": [1540, 10766, 10777]
                    }
                }
            },
            "isdir": 1,
            "status": 1
        }
    }, {
        "$group": {
            "_id": "$resourceId",
            "resourceId": {
                "$first": "$resourceId"
            },
            "createTime": {
                "$first": "$createTime"
            }
        }
    }
    ],{ explain: true,"allowDiskUse":true})

执行分析结果

// 1
    {
        "stages": [
            {
                "$cursor": {
                    "query": { },
                    "fields": {
                        "createTime": NumberInt("1"),
                        "creator": NumberInt("1"),
                        "isdir": NumberInt("1"),
                        "publicObject": NumberInt("1"),
                        "publicStatus": NumberInt("1"),
                        "resourceId": NumberInt("1"),
                        "status": NumberInt("1"),
                        "_id": NumberInt("1")
                    },
                    "queryPlanner": {
                        "plannerVersion": NumberInt("1"),
                        "namespace": "xh_sourcecenter.public_setting_detail",
                        "indexFilterSet": false,
                        "parsedQuery": {
                            "$and": [ ]
                        },
                        "winningPlan": {
                            "stage": "COLLSCAN",
                            "filter": {
                                "$and": [ ]
                            },
                            "direction": "forward"
                        },
                        "rejectedPlans": [ ]
                    }
                }
            },
            {
                "$project": {
                    "creator": true,
                    "publicStatus": true,
                    "publicObject": true,
                    "isdir": true,
                    "status": true,
                    "resourceId": true,
                    "createTime": true
                }
            },
            {
                "$match": {
                    "creator": 32235,
                    "publicStatus": 1,
                    "publicObject": {
                        "$elemMatch": {
                            "classId": {
                                "$in": [
                                    1540,
                                    10766,
                                    10777
                                ]
                            }
                        }
                    },
                    "isdir": 1,
                    "status": 1
                }
            },
            {
                "$group": {
                    "_id": "$resourceId",
                    "resourceId": {
                        "$first": "$resourceId"
                    },
                    "createTime": {
                        "$first": "$createTime"
                    }
                }
            }
        ],
        "ok": 1,
        "$gleStats": {
            "lastOpTime": Timestamp(1605246348, 1),
            "electionId": ObjectId("5f8ea969fa183fc9333b824c")
        }
    }

很明显，上述执行结果中，我们看到 为全表扫描了

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMjIzNjg4_size_16_color_FFFFFF_t_70][]

为什么呢？其实和聚合的查询顺序是有关系的

db.public_setting_detail.aggregate([
     {
        "$match": {
            "creator": 32235,
            "publicStatus": 1,
            "publicObject": {
                "$elemMatch": {
                    "classId": {
                        "$in": [1540, 10766, 10777]
                    }
                }
            },
            "isdir": 1,
            "status": 1
        }
    }, {
        "$project": { 
            "resourceId": 1,
            "createTime": 1
        }
    },{
        "$group": {
            "_id": "$resourceId",
            "resourceId": {
                "$first": "$resourceId"
            },
            "createTime": {
                "$first": "$createTime"
            }
        }
    }
    ],{ explain: true,"allowDiskUse":true})

调整match与project顺序，先执行索引查询，在执行对应的聚合操作

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMjIzNjg4_size_16_color_FFFFFF_t_70 1][]

二、优化后查询

db.public_setting_detail.aggregate([
    {
        "$match": {
            "creator": 32235,
            "publicStatus": 1,
            "publicObject": {
                "$elemMatch": {
                    "classId": {
                        "$in": [1540, 10766, 10777]
                    }
                }
            },
            "isdir": 1,
            "status": 1
        }
    },
    {
        "$project": {
            "resourceId": 1,
            "createTime": 1
        }
    }, {
        "$group": {
            "_id": "$resourceId",
            "resourceId": {
                "$first": "$resourceId"
            },
            "createTime": {
                "$first": "$createTime"
            }
        }
    }
    ],{ explain: true,"allowDiskUse":true})

执行分析结果

// 1
    {
        "stages": [
            {
                "$cursor": {
                    "query": {
                        "creator": 32235,
                        "publicStatus": 1,
                        "publicObject": {
                            "$elemMatch": {
                                "classId": {
                                    "$in": [
                                        1540,
                                        10766,
                                        10777
                                    ]
                                }
                            }
                        },
                        "isdir": 1,
                        "status": 1
                    },
                    "fields": {
                        "createTime": NumberInt("1"),
                        "resourceId": NumberInt("1"),
                        "_id": NumberInt("1")
                    },
                    "queryPlanner": {
                        "plannerVersion": NumberInt("1"),
                        "namespace": "xh_sourcecenter.public_setting_detail",
                        "indexFilterSet": false,
                        "parsedQuery": {
                            "$and": [
                                {
                                    "publicObject": {
                                        "$elemMatch": {
                                            "classId": {
                                                "$in": [
                                                    1540,
                                                    10766,
                                                    10777
                                                ]
                                            }
                                        }
                                    }
                                },
                                {
                                    "creator": {
                                        "$eq": 32235
                                    }
                                },
                                {
                                    "isdir": {
                                        "$eq": 1
                                    }
                                },
                                {
                                    "publicStatus": {
                                        "$eq": 1
                                    }
                                },
                                {
                                    "status": {
                                        "$eq": 1
                                    }
                                }
                            ]
                        },
                        "winningPlan": {
                            "stage": "KEEP_MUTATIONS",
                            "inputStage": {
                                "stage": "FETCH",
                                "filter": {
                                    "publicObject": {
                                        "$elemMatch": {
                                            "classId": {
                                                "$in": [
                                                    1540,
                                                    10766,
                                                    10777
                                                ]
                                            }
                                        }
                                    }
                                },
                                "inputStage": {
                                    "stage": "IXSCAN",
                                    "keyPattern": {
                                        "creator": NumberInt("1"),
                                        "publicStatus": NumberInt("1"),
                                        "publicObject.classId": NumberInt("1"),
                                        "isdir": NumberInt("1"),
                                        "status": NumberInt("1")
                                    },
                                    "indexName": "idx_public_setting_detail__creator_publicStatus_publicObject_isdir_status",
                                    "isMultiKey": true,
                                    "direction": "forward",
                                    "indexBounds": {
                                        "creator": [
                                            "[32235.0, 32235.0]"
                                        ],
                                        "publicStatus": [
                                            "[1.0, 1.0]"
                                        ],
                                        "publicObject.classId": [
                                            "[1540.0, 1540.0]",
                                            "[10766.0, 10766.0]",
                                            "[10777.0, 10777.0]"
                                        ],
                                        "isdir": [
                                            "[1.0, 1.0]"
                                        ],
                                        "status": [
                                            "[1.0, 1.0]"
                                        ]
                                    }
                                }
                            }
                        },
                        "rejectedPlans": [ ]
                    }
                }
            },
            {
                "$project": {
                    "resourceId": true,
                    "createTime": true
                }
            },
            {
                "$group": {
                    "_id": "$resourceId",
                    "resourceId": {
                        "$first": "$resourceId"
                    },
                    "createTime": {
                        "$first": "$createTime"
                    }
                }
            }
        ],
        "ok": 1,
        "$gleStats": {
            "lastOpTime": Timestamp(1605246348, 1),
            "electionId": ObjectId("5f8ea969fa183fc9333b824c")
        }
    }

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMjIzNjg4_size_16_color_FFFFFF_t_70]: /images/20211009/f7822004419f4bc0a9b555ffaf5edd34.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMjIzNjg4_size_16_color_FFFFFF_t_70 1]: /images/20211009/838d413e88da4d1fa946530ee59df86f.png