Skip to content
标签
欢迎扫码关注公众号

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

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

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

分析结果如下:

点击查看分析结果
json
{
    "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 属性上创建唯一索引:

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

执行结果:

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

再次执行 explain 结果如下:

点击查看 explain 结果
json
{
    "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.

Page Layout Max Width

Adjust the exact value of the page width of VitePress layout to adapt to different reading needs and screens.

Adjust the maximum width of the page layout
A ranged slider for user to choose and customize their desired width of the maximum width of the page layout can go.

Content Layout Max Width

Adjust the exact value of the document content width of VitePress layout to adapt to different reading needs and screens.

Adjust the maximum width of the content layout
A ranged slider for user to choose and customize their desired width of the maximum width of the content layout can go.