第五章 MongoDb索引优化 5.3

4、组合索引

注意，这里的组合索引与上述3中的b中的组合索引是有点不同的，4里面是对一级字段建立组合索引，而上述3中是对二级字段建立组合索引。

看看无组合索引例子：

query.put("startDate","2010-11-12");

query.put("isGold", 1);

运行结果：

SELECT * FROM schedule WHERE startDate='2010-11-12' and isGold=1

查询获得的长度17

查询耗时:63毫秒.

explain分析:{ "cursor" : "BasicCursor" , "indexBounds" : [ ] , "nscanned" : 4597 , "nscannedObjects" : 4597 , "n" : 17 , "millis" : 15 , "oldPlan" : { "cursor" : "BasicCursor" , "indexBounds" : [ ]} , "allPlans" : [ { "cursor" : "BasicCursor" , "indexBounds" : [ ]}]}

这里显示没有使用索引进行查询。

在schedule的isGold和startDate上面创建组合索引

两种方式：

coll.createIndex(new BasicDBObject("startDate", 1).append("isGold", 1));

或者：

BasicDBObject indexs = new BasicDBObject();

    indexs.put("startDate",1);

indexs.put("isGold", 1);

coll.createIndex(indexs);

目前schedule中，存在的索引：

index ---------

{ "name" : "_id_" , "ns" : "ms_basic.schedule" , "key" : { "_id" : 1}}

{ "name" : "rsc_1" , "ns" : "ms_basic.schedule" , "key" : { "rsc" : 1}}

{ "name" : "startDate_1_isGold_1" , "ns" : "ms_basic.schedule" , "key" : { "startDate" : 1 , "isGold" : 1}}

可以清晰看到，组合索引："key" : { "startDate" : 1 , "isGold" : 1}。

---------------------------------------------------------------------

注意：如果这么创建：

BasicDBObject index_1 = new BasicDBObject();

    BasicDBObject index_2 = new BasicDBObject();

    index_1.put("startDate",1);

    index_2.put("isGold", 1);

coll.createIndex(index_1);

coll.createIndex(index_2);

那么，索引数目：

{ "name" : "startDate_1" , "ns" : "ms_basic.schedule" , "key" : { "startDate" : 1}}

{ "name" : "isGold_1" , "ns" : "ms_basic.schedule" , "key" : { "isGold" : 1}}

查询耗时：

explain分析:{ "cursor" : "BtreeCursor startDate_1" , "indexBounds" : [ [ { "startDate" : "2010-11-12"} , { "startDate" : "2010-11-12"}]] , "nscanned" : 441 , "nscannedObjects" : 441 , "n" : 441 , "millis" : 0 , "oldPlan" : { "cursor" : "BtreeCursor startDate_1" , "indexBounds" : [ [ { "startDate" : "2010-11-12"} , { "startDate" : "2010-11-12"}]]} , "allPlans" : [ { "cursor" : "BtreeCursor startDate_1" , "indexBounds" : [ [ { "startDate" : "2010-11-12"} , { "startDate" : "2010-11-12"}]]}]}

当创建组合索引时，字段后面的1表示升序，-1表示降序，是用1还是用-1主要是跟排序的时候或指定范围内查询的时候有关的，具体看下面的英文原文的说明。

When creating an index, the number associated with a key specifies the direction of the index, so it should always be 1 (ascending) or -1 (descending). Direction doesn’t matter for single key indexes or for random access retrieval but is important if you are doing sorts or range queries on compound indexes.

此时，再次查询：

查询耗时:47毫秒.

explain分析:{ "cursor" : "BtreeCursor startDate_1_isGold_1" , "indexBounds" : [ [ { "startDate" : "2010-11-12" , "isGold" : 1} , { "startDate" : "2010-11-12" , "isGold" : 1}]] , "nscanned" : 17 , "nscannedObjects" : 17 , "n" : 17 , "millis" : 0 , "oldPlan" : { "cursor" : "BtreeCursor startDate_1_isGold_1" , "indexBounds" : [ [ { "startDate" : "2010-11-12" , "isGold" : 1} , { "startDate" : "2010-11-12" , "isGold" : 1}]]} , "allPlans" : [ { "cursor" : "BtreeCursor startDate_1_isGold_1" , "indexBounds" : [ [ { "startDate" : "2010-11-12" , "isGold" : 1} , { "startDate" : "2010-11-12" , "isGold" : 1}]]}]}

可以看到使用了索引。

MongoDB组合索引规则 

If you have a compound index on multiple fields, you can use it to query on the beginning subset of fields. So if you have an index on

a,b,c

you can use it query on

a

a,b

如果用过MySQL的话，看起来是不是很熟悉，原理跟MySQL是一样的。

例如：

SELECT * FROM schedule WHERE startDate='2010-11-12'

查询获得的长度441

查询耗时:46毫秒.

explain分析:{ "cursor" : "BtreeCursor startDate_1_isGold_1" , "indexBounds" : [ [ { "startDate" : "2010-11-12" , "isGold" : { "$minElement" : 1}} , { "startDate" : "2010-11-12" , "isGold" : { "$maxElement" : 1}}]] , "nscanned" : 441 , "nscannedObjects" : 441 , "n" : 441 , "millis" : 0 , "oldPlan" : { "cursor" : "BtreeCursor startDate_1_isGold_1" , "indexBounds" : [ [ { "startDate" : "2010-11-12" , "isGold" : { "$minElement" : 1}} , { "startDate" : "2010-11-12" , "isGold" : { "$maxElement" : 1}}]]} , "allPlans" : [ { "cursor" : "BtreeCursor startDate_1_isGold_1" , "indexBounds" : [ [ { "startDate" : "2010-11-12" , "isGold" : { "$minElement" : 1}} , { "startDate" : "2010-11-12" , "isGold" : { "$maxElement" : 1}}]]}]}

本文转自jooben 51CTO博客，原文链接:http://blog.51cto.com/jooben/365905