MongoDB的索引与查询优化 - 《MongoDB学习总结》

单字段索引
复合索引
数组的多键索引
索引管理
慢查询监控

MongoDB的索引的机制与普通数据库基本相似，主要有如下几部分：

单字段索引

MongoDB默认为所有集合创建了一个_id字段的单字段索引，该索引唯一，且不能删除（_id为集合的主键）

索引的创建方法：

db.customers.ensureIndex({name:1},{unique:false} )

查询索引：

 db.system.indexes.find()

查询结果：

{ "v" : 1, "name" : "_id_", "key" : { "_id" : 1 }, "ns" : "test.customers" }
{ "v" : 1, "name" : "name_1", "key" : { "name" : 1 }, "ns" : "test.customers" }

对有索引的查询选择器进行解释：

> db.customers.find({name:'zhangsan'}).explain()
{
    "cursor" : "BtreeCursor name_1",//表示该查询用到了索引
    "isMultiKey" : false,//未使用多键复合索引
    "n" : 10,//查询选择匹配到的记录数量
    "nscannedObjects" : 10,//执行查询扫描到的文档对象数量
    "nscanned" : 10,//扫描到的文档或索引总数
    "nscannedObjectsAllPlans" : 10,//扫描文旦总数在所有查询计划中
    "nscannedAllPlans" : 10,//在所有查询计划中扫描的文档或索条目的总数量
    "scanAndOrder" : false,//从游标取出查询到的数据时，是否对数据进行排序
    "indexOnly" : false,//
    "nYields" : 0,//产生的读锁数
    "nChunkSkips" : 0,
    "millis" : 0,//查询耗时（ms）
    "indexBounds" : {
        "name" : [
            [
                "zhangsan",
                "zhangsan"
            ]
        ]
    },
    "server" : "raspberrypi:27017"
}

对上文含义进行解释看//以后的部分；

注意：以上部分注释以后也会用到，同时在分析查询时会经常用到，最好记下来。

复合索引

复合索引主要是指对多个字段同时添加索引，故而复合索引支持匹配多个字段的查询。创建复合索引：

db.customers.ensureIndex({id:1,age:1})

查询索引结果：

> db.system.indexes.find()
{ "v" : 1, "name" : "_id_", "key" : { "_id" : 1 }, "ns" : "test.customers" }
{ "v" : 1, "name" : "name_1", "key" : { "name" : 1 }, "ns" : "test.customers" }
{ "v" : 1, "name" : "id_1_age_1", "key" : { "id" : 1, "age" : 1 }, "ns" : "test.customers" }

使用索引示例：

> db.customers.find({id:{$lt:5},age:{$gt:12}})
{ "_id" : ObjectId("589835c41c85cb68725f789b"), "id" : 3, "name" : "zhangsan", "age" : 13 }
{ "_id" : ObjectId("589835c41c85cb68725f789c"), "id" : 4, "name" : "zhangsan", "age" : 14 }

解释执行示例：

> db.customers.find({id:{$lt:5},age:{$gt:12}}).explain()
{
    "cursor" : "BtreeCursor id_1_age_1",
    "isMultiKey" : false,
    "n" : 2,
    "nscannedObjects" : 2,
    "nscanned" : 4,
    "nscannedObjectsAllPlans" : 5,
    "nscannedAllPlans" : 9,
    "scanAndOrder" : false,
    "indexOnly" : false,
    "nYields" : 0,
    "nChunkSkips" : 0,
    "millis" : 0,
    "indexBounds" : {
        "id" : [
            [
                -1.7976931348623157e+308,
                5
            ]
        ],
        "age" : [
            [
                12,
                1.7976931348623157e+308
            ]
        ]
    },
    "server" : "raspberrypi:27017"
}

数组的多键索引

对一个值为数组类型的字段创建索引，则会默认对数组中的每一个元素都创建索引

> db.customers.ensureIndex({'orders.orders_id':1})

查看：

> db.system.indexes.find()
{ "v" : 1, "name" : "_id_", "key" : { "_id" : 1 }, "ns" : "test.customers" }
{ "v" : 1, "name" : "name_1", "key" : { "name" : 1 }, "ns" : "test.customers" }
{ "v" : 1, "name" : "id_1_age_1", "key" : { "id" : 1, "age" : 1 }, "ns" : "test.customers" }
{ "v" : 1, "name" : "orders.products.product_name_1", "key" : { "orders.products.product_name" : 1 }, "ns" : "test.customers" }
{ "v" : 1, "name" : "orders.orders_id_1", "key" : { "orders.orders_id" : 1 }, "ns" : "test.customers" }

解释执行：

> db.customers.find({'orders.orders_id':{$gte:0}}).explain()
{
    "cursor" : "BtreeCursor orders.orders_id_1",
    "isMultiKey" : false,
    "n" : 1,
    "nscannedObjects" : 1,
    "nscanned" : 1,
    "nscannedObjectsAllPlans" : 1,
    "nscannedAllPlans" : 1,
    "scanAndOrder" : false,
    "indexOnly" : false,
    "nYields" : 0,
    "nChunkSkips" : 0,
    "millis" : 0,
    "indexBounds" : {
        "orders.orders_id" : [
            [
                0,
                1.7976931348623157e+308
            ]
        ]
    },
    "server" : "raspberrypi:27017"
}

另外一个：

> db.customers.find({'orders.products.product_name':'iphone'})
{ "_id" : ObjectId("58983d0fc55e261327343eab"), "id" : 11, "name" : "lisi", "orders" : [     {     "orders_id" : 1,     "create_time" : "2017-02-06",     "products" : [     {     "product_name" : "MiPad",     "price" : "$100.00" },     {     "product_name" : "iphone",     "price" : "$399.00" } ] } ], "mobile" : "13161020110", "address" : { "city" : "beijing", "street" : "taiyanggong" } }
> db.customers.find({'orders.products.product_name':'iphone'}).explain()
{
    "cursor" : "BtreeCursor orders.products.product_name_1",
    "isMultiKey" : true,
    "n" : 1,
    "nscannedObjects" : 1,
    "nscanned" : 1,
    "nscannedObjectsAllPlans" : 1,
    "nscannedAllPlans" : 1,
    "scanAndOrder" : false,
    "indexOnly" : false,
    "nYields" : 0,
    "nChunkSkips" : 0,
    "millis" : 0,
    "indexBounds" : {
        "orders.products.product_name" : [
            [
                "iphone",
                "iphone"
            ]
        ]
    },
    "server" : "raspberrypi:27017"
}

索引管理

索引的创建我们已经在上文中有过讲述，下面总结下索引的创建格式：

db.collection.ensureIndex(keys,options)

其中key是一个document文档，包含需要添加索引的字段以及索引的排序方向；option可选，控制索引的创建方式；所有的索引都保存在集合system.indexes中;索引的删除方式为：

db.collection.dropIndex(indexName)

如：删除customers的name_1索引：

> db.customers.dropIndex('name_1')
{ "nIndexesWas" : 5, "ok" : 1 }
> db.system.indexes.find()
{ "v" : 1, "name" : "_id_", "key" : { "_id" : 1 }, "ns" : "test.customers" }
{ "v" : 1, "name" : "id_1_age_1", "key" : { "id" : 1, "age" : 1 }, "ns" : "test.customers" }
{ "v" : 1, "name" : "orders.products.product_name_1", "key" : { "orders.products.product_name" : 1 }, "ns" : "test.customers" }
{ "v" : 1, "name" : "orders.orders_id_1", "key" : { "orders.orders_id" : 1 }, "ns" : "test.customers" }

慢查询监控

1、MongoDB会自动的将查询语句执行时间超过100ms的输出到日志中，其中100ms可以通过mongod的启动选项 slowms设置，默认100ms2、还可以通过打开数据库的监视功能，默认是关闭的，通过如下命令打开

db.setProfilingLevel(level,[slowms])

level：监视级别，值为0为关闭，1：只记录慢日志，2：记录所有的操作监视的结果都保存在system.profile中。

示例如下：

> db.setProfilingLevel(2)
{ "was" : 0, "slowms" : 100, "ok" : 1 }
> db.customers.find()
{ "_id" : ObjectId("589835c41c85cb68725f7899"), "id" : 1, "name" : "zhangsan", "age" : 11 }
{ "_id" : ObjectId("589835c41c85cb68725f789a"), "id" : 2, "name" : "zhangsan", "age" : 12 }
{ "_id" : ObjectId("589835c41c85cb68725f789b"), "id" : 3, "name" : "zhangsan", "age" : 13 }
{ "_id" : ObjectId("589835c41c85cb68725f789c"), "id" : 4, "name" : "zhangsan", "age" : 14 }
{ "_id" : ObjectId("589835c41c85cb68725f789d"), "id" : 5, "name" : "zhangsan", "age" : 15 }
{ "_id" : ObjectId("589835c41c85cb68725f789e"), "id" : 6, "name" : "zhangsan", "age" : 16 }
{ "_id" : ObjectId("589835c41c85cb68725f789f"), "id" : 7, "name" : "zhangsan", "age" : 17 }
{ "_id" : ObjectId("589835c41c85cb68725f78a0"), "id" : 8, "name" : "zhangsan", "age" : 18 }
{ "_id" : ObjectId("589835c41c85cb68725f78a1"), "id" : 9, "name" : "zhangsan", "age" : 19 }
{ "_id" : ObjectId("589835c41c85cb68725f78a2"), "id" : 10, "name" : "zhangsan", "age" : 20 }
{ "_id" : ObjectId("58983d0fc55e261327343eab"), "id" : 11, "name" : "lisi", "orders" : [     {     "orders_id" : 1,     "create_time" : "2017-02-06",     "products" : [     {     "product_name" : "MiPad",     "price" : "$100.00" }, {     "product_name" : "iphone",     "price" : "$399.00" } ] } ], "mobile" : "13161020110", "address" : { "city" : "beijing", "street" : "taiyanggong" } }
> db.system.profile.find()
{ "op" : "query", "ns" : "test.system.indexes", "query" : { "expireAfterSeconds" : { "$exists" : true } }, "ntoreturn" : 0, "ntoskip" : 0, "nscanned" : 4, "keyUpdates" : 0, "numYield" : 0, "lockStats" : { "timeLockedMicros" : { "r" : NumberLong(294), "w" : NumberLong(0) }, "timeAcquiringMicros" : { "r" : NumberLong(13), "w" : NumberLong(15) } }, "nreturned" : 0, "responseLength" : 20, "millis" : 0, "ts" : ISODate("2017-02-09T03:45:41.845Z"), "client" : "0.0.0.0", "allUsers" : [ ], "user" : "" }
{ "op" : "query", "ns" : "test.customers", "query" : {  }, "ntoreturn" : 0, "ntoskip" : 0, "nscanned" : 11, "keyUpdates" : 0, "numYield" : 0, "lockStats" : { "timeLockedMicros" : { "r" : NumberLong(252), "w" : NumberLong(0) }, "timeAcquiringMicros" : { "r" : NumberLong(19), "w" : NumberLong(16) } }, "nreturned" : 11, "responseLength" : 995, "millis" : 0, "ts" : ISODate("2017-02-09T03:45:49.972Z"), "client" : "127.0.0.1", "allUsers" : [ ], "user" : "" }
>