MongoDB 创建索引以提高查询效率

🏷️ MongoDB

db.phones 中有 11w 条记录,通过 explain 方法解析查询语句：

db.phones.find({display: "+1 800-5550010"}).explain("executionStats")

分析结果如下：

点击查看分析结果

{
    "queryPlanner" : {
        "plannerVersion" : 1,
        "namespace" : "book.phones",
        "indexFilterSet" : false,
        "parsedQuery" : {
            "display" : {
                "$eq" : "+1 800-5550010"
            }
        },
        "winningPlan" : {
            "stage" : "COLLSCAN",
            "filter" : {
                "display" : {
                    "$eq" : "+1 800-5550010"
                }
            },
            "direction" : "forward"
        },
        "rejectedPlans" : [ ]
    },
    "executionStats" : {
        "executionSuccess" : true,
        "nReturned" : 1,
        "executionTimeMillis" : 52,
        "totalKeysExamined" : 0,
        "totalDocsExamined" : 110000,
        "executionStages" : {
            "stage" : "COLLSCAN",
            "filter" : {
                "display" : {
                    "$eq" : "+1 800-5550010"
                }
            },
            "nReturned" : 1,
            "executionTimeMillisEstimate" : 30,
            "works" : 110002,
            "advanced" : 1,
            "needTime" : 110000,
            "needYield" : 0,
            "saveState" : 859,
            "restoreState" : 859,
            "isEOF" : 1,
            "invalidates" : 0,
            "direction" : "forward",
            "docsExamined" : 110000
        }
    },
    "serverInfo" : {
        "host" : "localhost.localdomain",
        "port" : 27017,
        "version" : "3.2.4",
        "gitVersion" : "e2ee9ffcf9f5a94fad76802e28cc978718bb7a30"
    },
    "ok" : 1
}

可以看出执行需要 52ms，总共扫描了 110000 条记录；

使用 ensureIndex 命令在 display 属性上创建唯一索引：

db.phones.ensureIndex(
	{display : 1},
	{unique : true, dropDups : true }
)

执行结果：

{
    "createdCollectionAutomatically" : false,
    "numIndexesBefore" : 1,
    "numIndexesAfter" : 2,
    "ok" : 1
}

再次执行 explain 结果如下：

点击查看 explain 结果

{
    "queryPlanner" : {
        "plannerVersion" : 1,
        "namespace" : "book.phones",
        "indexFilterSet" : false,
        "parsedQuery" : {
            "display" : {
                "$eq" : "+1 800-5550010"
            }
        },
        "winningPlan" : {
            "stage" : "FETCH",
            "inputStage" : {
                "stage" : "IXSCAN",
                "keyPattern" : {
                    "display" : 1
                },
                "indexName" : "display_1",
                "isMultiKey" : false,
                "isUnique" : true,
                "isSparse" : false,
                "isPartial" : false,
                "indexVersion" : 1,
                "direction" : "forward",
                "indexBounds" : {
                    "display" : [
                        "[\"+1 800-5550010\", \"+1 800-5550010\"]"
                    ]
                }
            }
        },
        "rejectedPlans" : [ ]
    },
    "executionStats" : {
        "executionSuccess" : true,
        "nReturned" : 1,
        "executionTimeMillis" : 1,
        "totalKeysExamined" : 1,
        "totalDocsExamined" : 1,
        "executionStages" : {
            "stage" : "FETCH",
            "nReturned" : 1,
            "executionTimeMillisEstimate" : 0,
            "works" : 2,
            "advanced" : 1,
            "needTime" : 0,
            "needYield" : 0,
            "saveState" : 0,
            "restoreState" : 0,
            "isEOF" : 1,
            "invalidates" : 0,
            "docsExamined" : 1,
            "alreadyHasObj" : 0,
            "inputStage" : {
                "stage" : "IXSCAN",
                "nReturned" : 1,
                "executionTimeMillisEstimate" : 0,
                "works" : 2,
                "advanced" : 1,
                "needTime" : 0,
                "needYield" : 0,
                "saveState" : 0,
                "restoreState" : 0,
                "isEOF" : 1,
                "invalidates" : 0,
                "keyPattern" : {
                    "display" : 1
                },
                "indexName" : "display_1",
                "isMultiKey" : false,
                "isUnique" : true,
                "isSparse" : false,
                "isPartial" : false,
                "indexVersion" : 1,
                "direction" : "forward",
                "indexBounds" : {
                    "display" : [
                        "[\"+1 800-5550010\", \"+1 800-5550010\"]"
                    ]
                },
                "keysExamined" : 1,
                "dupsTested" : 0,
                "dupsDropped" : 0,
                "seenInvalidated" : 0
            }
        }
    },
    "serverInfo" : {
        "host" : "localhost.localdomain",
        "port" : 27017,
        "version" : "3.2.4",
        "gitVersion" : "e2ee9ffcf9f5a94fad76802e28cc978718bb7a30"
    },
    "ok" : 1
}

执行只需要 1ms，扫描的记录也变成了 1.