MongoDB索引

索引通常能够极大的提高查询的效率，如果没有索引，MongoDB在读取数据时必须扫描集合中的每个文档并选取那些符合查询条件的记录, 这种扫描全集合的查询效率是非常低的，特别在处理大量的数据时，查询可以要花费几十秒甚至几分钟，这对网站的性能是非常致命的

索引是特殊的数据结构，索引存储在一个易于遍历读取的数据集合中，索引是对数据库表中一列或多列的值进行排序的一种结构

ensureIndex()建立索引

db.文档名.ensureIndex({KEY:1})

KEY为要创建的索引字段, 1为指定按照升序创建索引, -1为降序

explain

explain可以获知系统如何处理查询请求, 利用explain命令, 可以观察系统如何使用索引来加快检索, 同时可以针对性优化索引

---

现在有数据

> db.goods.find()
{ "_id" : 1, "item" : "f1", "type" : "food", "quantity" : 500 }
{ "_id" : 2, "item" : "f2", "type" : "food", "quantity" : 100 }
{ "_id" : 3, "item" : "p1", "type" : "paper", "quantity" : 200 }
{ "_id" : 4, "item" : "p2", "type" : "paper", "quantity" : 150 }
{ "_id" : 5, "item" : "f3", "type" : "food", "quantity" : 300 }
{ "_id" : 6, "item" : "t1", "type" : "toys", "quantity" : 500 }
{ "_id" : 7, "item" : "a1", "type" : "apparel", "quantity" : 250 }
{ "_id" : 8, "item" : "a2", "type" : "apparel", "quantity" : 400 }
{ "_id" : 9, "item" : "t2", "type" : "toys", "quantity" : 50 }
{ "_id" : 10, "item" : "f4", "type" : "food", "quantity" : 75 }
>

查询

> db.goods.find({quantity:150}).explain('executionStats')
{
    "queryPlanner" : {
        "plannerVersion" : 1,
        "namespace" : "test.goods",
        "indexFilterSet" : false,
        "parsedQuery" : {
            "quantity" : {
                "$eq" : 150
            }
        },
        "winningPlan" : {
            "stage" : "COLLSCAN",
            "filter" : {
                "quantity" : {
                    "$eq" : 150
                }
            },
            "direction" : "forward"
        },
        "rejectedPlans" : [ ]
    },
    "executionStats" : {
        "executionSuccess" : true,
        "nReturned" : 1,
        "executionTimeMillis" : 0,
        "totalKeysExamined" : 0,
        "totalDocsExamined" : 10,
        "executionStages" : {
            "stage" : "COLLSCAN",    // collection scan 集合扫描
            "filter" : {
                "quantity" : {
                    "$eq" : 150
                }
            },
            "nReturned" : 1,    // 返回1条
            "executionTimeMillisEstimate" : 0,
            "works" : 12,
            "advanced" : 1,
            "needTime" : 10,
            "needYield" : 0,
            "saveState" : 0,
            "restoreState" : 0,
            "isEOF" : 1,
            "invalidates" : 0,
            "direction" : "forward",
            "docsExamined" : 10    // 查询了10条数据, 才返回1条
        }
    },
    "serverInfo" : {
        "host" : "ql",
        "port" : 27017,
        "version" : "3.2.10",
        "gitVersion" : "79d9b3ab5ce20f51c272b4411202710a082d0317"
    },
    "ok" : 1
}
>

创建索引, 以下两种方法都行

• ensureIndex({KEY: 1})
• createIndex({KEY: 1})

> db.goods.ensureIndex({quantity: 1})
{
    "createdCollectionAutomatically" : false,
    "numIndexesBefore" : 1,
    "numIndexesAfter" : 2,
    "ok" : 1
}
>

再次检索

> db.goods.find({quantity:150}).explain('executionStats')
{
    "queryPlanner" : {
        "plannerVersion" : 1,
        "namespace" : "test.goods",
        "indexFilterSet" : false,
        "parsedQuery" : {
            "quantity" : {
                "$eq" : 150
            }
        },
        "winningPlan" : {
            "stage" : "FETCH",
            "inputStage" : {
                "stage" : "IXSCAN",
                "keyPattern" : {
                    "quantity" : 1
                },
                "indexName" : "quantity_1",
                "isMultiKey" : false,
                "isUnique" : false,
                "isSparse" : false,
                "isPartial" : false,
                "indexVersion" : 1,
                "direction" : "forward",
                "indexBounds" : {
                    "quantity" : [
                        "[150.0, 150.0]"
                    ]
                }
            }
        },
        "rejectedPlans" : [ ]
    },
    "executionStats" : {
        "executionSuccess" : true,
        "nReturned" : 1,    // 返回1个结果
        "executionTimeMillis" : 0,
        "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,    // 检索1条结果, 就返回了一个结果
            "alreadyHasObj" : 0,
            "inputStage" : {
                "stage" : "IXSCAN",    // Index scan 说明命中索引
                "nReturned" : 1,
                "executionTimeMillisEstimate" : 0,
                "works" : 2,
                "advanced" : 1,
                "needTime" : 0,
                "needYield" : 0,
                "saveState" : 0,
                "restoreState" : 0,
                "isEOF" : 1,
                "invalidates" : 0,
                "keyPattern" : {
                    "quantity" : 1
                },
                "indexName" : "quantity_1",
                "isMultiKey" : false,
                "isUnique" : false,
                "isSparse" : false,
                "isPartial" : false,
                "indexVersion" : 1,
                "direction" : "forward",
                "indexBounds" : {
                    "quantity" : [
                        "[150.0, 150.0]"
                    ]
                },
                "keysExamined" : 1,
                "dupsTested" : 0,
                "dupsDropped" : 0,
                "seenInvalidated" : 0
            }
        }
    },
    "serverInfo" : {
        "host" : "ql",
        "port" : 27017,
        "version" : "3.2.10",
        "gitVersion" : "79d9b3ab5ce20f51c272b4411202710a082d0317"
    },
    "ok" : 1
}
>

查看索引

db.集合名称.getIndex()

> db.goods.getIndexes()
[
    {
        "v" : 1,
        "key" : {
            "_id" : 1    // id也是索引
        },
        "name" : "_id_",
        "ns" : "test.goods"
    },
    {
        "v" : 1,
        "key" : {
            "quantity" : 1    // 刚刚新建的索引
        },
        "name" : "quantity_1",
        "ns" : "test.goods"
    }
]
>

优缺点

索引会增加写操作的负担, 但是可以非常快速的查询

转载于:https://www.cnblogs.com/qlshine/p/5950573.html