第五章 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