MongoDB 性能优化

一、查询优化
1）不使用不等于或者不包含查询数据;
2）不使用skip分页查询;
3）实时查询不使用 aggregate 和 mapreduce;

二、加索引原则
1）区分度大的字段排左边。若查询条件的区分度不大，加索引也没有效果，请修改查询逻辑；
2）索引最左匹配，查询条件+排序条件相关字段都要加索引，范围查询字段放在最右边；
3）or两边的条件分别加索引；
4）in条件过多导致不走索引时，请修改查询逻辑，拆分成单条件查询。
5）仅使用后台方式加索引；

三、脚本
#查询分析
db.xxx.find({"createTime": {"$lt": {"$date": 1500099200000}}}).explain(true);

#获取索引
db.xxx.getIndexes();

#后台创建索引
db.xxx.ensureIndex({"createTime":1,"xxxx":-1},{background:true})

#查询正在进行中的创建索引操作
db.currentOp({
     $or: [{ op: "command", "query.createIndexes": { $exists: true } },
         { op: "insert", ns: \.system\.indexes\b/ }]})

#删除索引
db.xxx.dropIndex("iMEI_1_oS_1");

四、实例
加索引前：
{
    "queryPlanner" : {
        "plannerVersion" : NumberInt(1),
        "namespace" : "qz_test.baseClientDevice"， #Collection 名称
        "indexFilterSet" : false,
        "parsedQuery" : {
            "$and" : [
                {
                    "$or" : [
                        {
                            "$and" : [
                                {
                                    "aNDROIDID" : {
                                        "$eq" : "ef4b9fab0463831f"
                                    }
                                },
                                {
                                    "uA" : {
                                        "$eq" : "Dalvik/2.1.0 (Linux; U; Android 8.0.0; LND-AL30 Build/HONORLND-AL30)"
                                    }
                                }
                            ]
                        },
                        {
                            "iMEI" : {
                                "$eq" : "869114034622463"
                            }
                        },
                        {
                            "mAC" : {
                                "$eq" : "0C:70:4A:57:C4:70"
                            }
                        }
                    ]
                },
                {
                    "oS" : {
                        "$eq" : NumberInt(1)
                    }
                }
            ]
        },
        "winningPlan" : {
            "stage" : "COLLSCAN", #Collection扫描，没有走索引
            "filter" : {
                "$and" : [
                    {
                        "$or" : [
                            {
                                "$and" : [
                                    {
                                        "aNDROIDID" : {
                                            "$eq" : "ef4b9fab0463831f"
                                        }
                                    },
                                    {
                                        "uA" : {
                                            "$eq" : "Dalvik/2.1.0 (Linux; U; Android 8.0.0; LND-AL30 Build/HONORLND-AL30)"
                                        }
                                    }
                                ]
                            },
                            {
                                "iMEI" : {
                                    "$eq" : "869114034622463"
                                }
                            },
                            {
                                "mAC" : {
                                    "$eq" : "0C:70:4A:57:C4:70"
                                }
                            }
                        ]
                    },
                    {
                        "oS" : {
                            "$eq" : NumberInt(1)
                        }
                    }
                ]
            },
            "direction" : "forward"
        },
        "rejectedPlans" : [

        ]
    },
    "executionStats" : {
        "executionSuccess" : true,
        "nReturned" : NumberInt(6),#返回数据条数
        "executionTimeMillis" : NumberInt(466), #执行时间
        "totalKeysExamined" : NumberInt(0), #扫描索引数
        "totalDocsExamined" : NumberInt(663411), #扫描文档数
        "executionStages" : {
            "stage" : "COLLSCAN",
            "filter" : {
                "$and" : [
                    {
                        "$or" : [
                            {
                                "$and" : [
                                    {
                                        "aNDROIDID" : {
                                            "$eq" : "ef4b9fab0463831f"
                                        }
                                    },
                                    {
                                        "uA" : {
                                            "$eq" : "Dalvik/2.1.0 (Linux; U; Android 8.0.0; LND-AL30 Build/HONORLND-AL30)"
                                        }
                                    }
                                ]
                            },
                            {
                                "iMEI" : {
                                    "$eq" : "869114034622463"
                                }
                            },
                            {
                                "mAC" : {
                                    "$eq" : "0C:70:4A:57:C4:70"
                                }
                            }
                        ]
                    },
                    {
                        "oS" : {
                            "$eq" : NumberInt(1)
                        }
                    }
                ]
            },
            "nReturned" : NumberInt(6),
            "executionTimeMillisEstimate" : NumberInt(420),
            "works" : NumberInt(663413),
            "advanced" : NumberInt(6),
            "needTime" : NumberInt(663406),
            "needYield" : NumberInt(0),
            "saveState" : NumberInt(5182),
            "restoreState" : NumberInt(5182),
            "isEOF" : NumberInt(1),
            "invalidates" : NumberInt(0),
            "direction" : "forward",
            "docsExamined" : NumberInt(663411)
        },
        "allPlansExecution" : [

        ]
    },
    "serverInfo" : {
        "host" : "host-172-16-9-51",
        "port" : NumberInt(8635),
        "version" : "4.0.3",
        "gitVersion" : "fb3d197b853f7bfceb931dfc26da7a112e7d0fde"
    },
    "ok" : NumberInt(1),
    "operationTime" : Timestamp(1600520361, 6)
}
解法：
构造 or 语句，把 A and ( B or C ) 拆成 (A and B) or (A and C);
脚本：
db.baseClientDevice.ensureIndex({"iMEI":1,"oS":1},{background:true});
db.baseClientDevice.ensureIndex({"mAC":1,"oS":1},{background:true});
db.baseClientDevice.ensureIndex({"aNDROIDID":1,"uA":1,"oS":1},{background:true});
加索引后：
{
    "queryPlanner" : {
        "plannerVersion" : NumberInt(1),
        "namespace" : "qz_test.baseClientDevice",
        "indexFilterSet" : false,
        "parsedQuery" : {
            "$and" : [
                {
                    "$or" : [
                        {
                            "$and" : [
                                {
                                    "aNDROIDID" : {
                                        "$eq" : "ef4b9fab0463831f"
                                    }
                                },
                                {
                                    "uA" : {
                                        "$eq" : "Dalvik/2.1.0 (Linux; U; Android 8.0.0; LND-AL30 Build/HONORLND-AL30)"
                                    }
                                }
                            ]
                        },
                        {
                            "iMEI" : {
                                "$eq" : "869114034622463"
                            }
                        },
                        {
                            "mAC" : {
                                "$eq" : "0C:70:4A:57:C4:70"
                            }
                        }
                    ]
                },
                {
                    "oS" : {
                        "$eq" : NumberInt(1)
                    }
                }
            ]
        },
        "winningPlan" : {
            "stage" : "FETCH",
            "inputStage" : {
                "stage" : "OR",
                "inputStages" : [
                    {
                        "stage" : "IXSCAN",
                        "keyPattern" : {
                            "iMEI" : NumberInt(1),
                            "oS" : NumberInt(1)
                        },
                        "indexName" : "iMEI_1_oS_1",
                        "isMultiKey" : false,
                        "multiKeyPaths" : {
                            "iMEI" : [

                            ],
                            "oS" : [

                            ]
                        },
                        "isUnique" : false,
                        "isSparse" : false,
                        "isPartial" : false,
                        "indexVersion" : NumberInt(2),
                        "direction" : "forward",
                        "indexBounds" : {
                            "iMEI" : [
                                "[\"869114034622463\", \"869114034622463\"]"
                            ],
                            "oS" : [
                                "[1.0, 1.0]"
                            ]
                        }
                    },
                    {
                        "stage" : "IXSCAN",
                        "keyPattern" : {
                            "mAC" : NumberInt(1),
                            "oS" : NumberInt(1)
                        },
                        "indexName" : "mAC_1_oS_1",
                        "isMultiKey" : false,
                        "multiKeyPaths" : {
                            "mAC" : [

                            ],
                            "oS" : [

                            ]
                        },
                        "isUnique" : false,
                        "isSparse" : false,
                        "isPartial" : false,
                        "indexVersion" : NumberInt(2),
                        "direction" : "forward",
                        "indexBounds" : {
                            "mAC" : [
                                "[\"0C:70:4A:57:C4:70\", \"0C:70:4A:57:C4:70\"]"
                            ],
                            "oS" : [
                                "[1.0, 1.0]"
                            ]
                        }
                    },
                    {
                        "stage" : "IXSCAN",
                        "keyPattern" : {
                            "aNDROIDID" : NumberInt(1),
                            "uA" : NumberInt(1),
                            "oS" : NumberInt(1)
                        },
                        "indexName" : "aNDROIDID_1_uA_1_oS_1",
                        "isMultiKey" : false,
                        "multiKeyPaths" : {
                            "aNDROIDID" : [

                            ],
                            "uA" : [

                            ],
                            "oS" : [

                            ]
                        },
                        "isUnique" : false,
                        "isSparse" : false,
                        "isPartial" : false,
                        "indexVersion" : NumberInt(2),
                        "direction" : "forward",
                        "indexBounds" : {
                            "aNDROIDID" : [
                                "[\"ef4b9fab0463831f\", \"ef4b9fab0463831f\"]"
                            ],
                            "uA" : [
                                "[\"Dalvik/2.1.0 (Linux; U; Android 8.0.0; LND-AL30 Build/HONORLND-AL30)\", \"Dalvik/2.1.0 (Linux; U; Android 8.0.0; LND-AL30 Build/HONORLND-AL30)\"]"
                            ],
                            "oS" : [
                                "[1.0, 1.0]"
                            ]
                        }
                    }
                ]
            }
        },
        "rejectedPlans" : [

        ]
    },
    "executionStats" : {
        "executionSuccess" : true,
        "nReturned" : NumberInt(6),#返回数据条数
        "executionTimeMillis" : NumberInt(0),#执行时间
        "totalKeysExamined" : NumberInt(15),#扫描索引数
        "totalDocsExamined" : NumberInt(6),#扫描文档数
        "executionStages" : {
            "stage" : "FETCH",
            "nReturned" : NumberInt(6),
            "executionTimeMillisEstimate" : NumberInt(0),
            "works" : NumberInt(18),
            "advanced" : NumberInt(6),
            "needTime" : NumberInt(11),
            "needYield" : NumberInt(0),
            "saveState" : NumberInt(0),
            "restoreState" : NumberInt(0),
            "isEOF" : NumberInt(1),
            "invalidates" : NumberInt(0),
            "docsExamined" : NumberInt(6),
            "alreadyHasObj" : NumberInt(0),
            "inputStage" : {
                "stage" : "OR",
                "nReturned" : NumberInt(6),
                "executionTimeMillisEstimate" : NumberInt(0),
                "works" : NumberInt(18),
                "advanced" : NumberInt(6),
                "needTime" : NumberInt(11),
                "needYield" : NumberInt(0),
                "saveState" : NumberInt(0),
                "restoreState" : NumberInt(0),
                "isEOF" : NumberInt(1),
                "invalidates" : NumberInt(0),
                "dupsTested" : NumberInt(15),
                "dupsDropped" : NumberInt(9),
                "recordIdsForgotten" : NumberInt(0),
                "inputStages" : [
                    {
                        "stage" : "IXSCAN", #使用索引
                        "nReturned" : NumberInt(3),
                        "executionTimeMillisEstimate" : NumberInt(0),
                        "works" : NumberInt(4),
                        "advanced" : NumberInt(3),
                        "needTime" : NumberInt(0),
                        "needYield" : NumberInt(0),
                        "saveState" : NumberInt(0),
                        "restoreState" : NumberInt(0),
                        "isEOF" : NumberInt(1),
                        "invalidates" : NumberInt(0),
                        "keyPattern" : {
                            "iMEI" : NumberInt(1),
                            "oS" : NumberInt(1)
                        },
                        "indexName" : "iMEI_1_oS_1",
                        "isMultiKey" : false,
                        "multiKeyPaths" : {
                            "iMEI" : [

                            ],
                            "oS" : [

                            ]
                        },
                        "isUnique" : false,
                        "isSparse" : false,
                        "isPartial" : false,
                        "indexVersion" : NumberInt(2),
                        "direction" : "forward",
                        "indexBounds" : {
                            "iMEI" : [
                                "[\"869114034622463\", \"869114034622463\"]"
                            ],
                            "oS" : [
                                "[1.0, 1.0]"
                            ]
                        },
                        "keysExamined" : NumberInt(3),
                        "seeks" : NumberInt(1),
                        "dupsTested" : NumberInt(0),
                        "dupsDropped" : NumberInt(0),
                        "seenInvalidated" : NumberInt(0)
                    },
                    {
                        "stage" : "IXSCAN", #使用索引
                        "nReturned" : NumberInt(6),
                        "executionTimeMillisEstimate" : NumberInt(0),
                        "works" : NumberInt(7),
                        "advanced" : NumberInt(6),
                        "needTime" : NumberInt(0),
                        "needYield" : NumberInt(0),
                        "saveState" : NumberInt(0),
                        "restoreState" : NumberInt(0),
                        "isEOF" : NumberInt(1),
                        "invalidates" : NumberInt(0),
                        "keyPattern" : {
                            "mAC" : NumberInt(1),
                            "oS" : NumberInt(1)
                        },
                        "indexName" : "mAC_1_oS_1",
                        "isMultiKey" : false,
                        "multiKeyPaths" : {
                            "mAC" : [

                            ],
                            "oS" : [

                            ]
                        },
                        "isUnique" : false,
                        "isSparse" : false,
                        "isPartial" : false,
                        "indexVersion" : NumberInt(2),
                        "direction" : "forward",
                        "indexBounds" : {
                            "mAC" : [
                                "[\"0C:70:4A:57:C4:70\", \"0C:70:4A:57:C4:70\"]"
                            ],
                            "oS" : [
                                "[1.0, 1.0]"
                            ]
                        },
                        "keysExamined" : NumberInt(6),
                        "seeks" : NumberInt(1),
                        "dupsTested" : NumberInt(0),
                        "dupsDropped" : NumberInt(0),
                        "seenInvalidated" : NumberInt(0)
                    },
                    {
                        "stage" : "IXSCAN", #使用索引
                        "nReturned" : NumberInt(6),
                        "executionTimeMillisEstimate" : NumberInt(0),
                        "works" : NumberInt(7),
                        "advanced" : NumberInt(6),
                        "needTime" : NumberInt(0),
                        "needYield" : NumberInt(0),
                        "saveState" : NumberInt(0),
                        "restoreState" : NumberInt(0),
                        "isEOF" : NumberInt(1),
                        "invalidates" : NumberInt(0),
                        "keyPattern" : {
                            "aNDROIDID" : NumberInt(1),
                            "uA" : NumberInt(1),
                            "oS" : NumberInt(1)
                        },
                        "indexName" : "aNDROIDID_1_uA_1_oS_1",
                        "isMultiKey" : false,
                        "multiKeyPaths" : {
                            "aNDROIDID" : [

                            ],
                            "uA" : [

                            ],
                            "oS" : [

                            ]
                        },
                        "isUnique" : false,
                        "isSparse" : false,
                        "isPartial" : false,
                        "indexVersion" : NumberInt(2),
                        "direction" : "forward",
                        "indexBounds" : {
                            "aNDROIDID" : [
                                "[\"ef4b9fab0463831f\", \"ef4b9fab0463831f\"]"
                            ],
                            "uA" : [
                                "[\"Dalvik/2.1.0 (Linux; U; Android 8.0.0; LND-AL30 Build/HONORLND-AL30)\", \"Dalvik/2.1.0 (Linux; U; Android 8.0.0; LND-AL30 Build/HONORLND-AL30)\"]"
                            ],
                            "oS" : [
                                "[1.0, 1.0]"
                            ]
                        },
                        "keysExamined" : NumberInt(6),
                        "seeks" : NumberInt(1),
                        "dupsTested" : NumberInt(0),
                        "dupsDropped" : NumberInt(0),
                        "seenInvalidated" : NumberInt(0)
                    }
                ]
            }
        },
        "allPlansExecution" : [

        ]
    },
    "serverInfo" : {
        "host" : "host-172-16-9-51",
        "port" : NumberInt(8635),
        "version" : "4.0.3",
        "gitVersion" : "fb3d197b853f7bfceb931dfc26da7a112e7d0fde"
    },
    "ok" : NumberInt(1),
    "operationTime" : Timestamp(1600521524, 10)
}