探究Mongodb的复合索引

       最近用mongodb来做一个项目，在看其关于复合索引部分的时候有一个疑惑：
“复合索引同样可用于局部属性的搜索，但必须依照索引字段顺序。比如创建索引字段顺序 "a,b,c"，那么仅对 "a,b,c"、"a,b"、"a" 查询有效，而对 "b,c" 之类的组合无效。” 
来源：传说中”雨痕“的博客  http://www.rainsts.net/article.asp?id=1033
       这不是跟mysql的复合索引一致么？真的是这样么？继续对这个问题的探究。
查阅mongodb的官网，有这样一段描述：
New in 1.6+
Now you can also use the compound index to service any combination of equality and range queries from the constitute fields. If the first key of the index is present in the query, that index may be selected by the query optimizer. If the first key is not present in the query, the index will only be used if hinted explicitly. While indexes can be used in many cases where an arbitrary subset of indexed fields are present in the query, as a general rule the optimal indexes for a given query are those in which queried fields precede any non queried fields.
     初读一遍，发现惊喜，1.6+上的新特性？ 难道当前的复合索引已经支持任意复合索引的组合子集了么？怀着期待去操作了一把：
首先参看子集的版本是否1.6+：
> db.version()                                                           
1.6.5
然后查看我自己的collections的复合索引：
> db.caption.getIndexes()                                                
[
        {
                "name" : "_id_",
                "ns" : "Caption.caption",
                "key" : {
                        "_id" : 1
                }
        },
        {
                "name" : "movie_longth",
                "ns" : "Caption.caption",
                "key" : {
                        "movieID" : 1,
                        "longth" : 1
                },
                "unique" : false,
                "_id" : ObjectId("4d2d714b4637c31478000001")
        }
]
Ok ,现在尝试只用复合索引中的后一字段longth来查询：
> db.caption.find({longth:{$lt:20}}).explain()                  
{
        "cursor" : "BasicCursor",
        "nscanned" : 5,
        "nscannedObjects" : 5,
        "n" : 2,
        "millis" : 0,
        "indexBounds" : {

        }
}
失望，并没有期望中的使用到索引。
尝试查询longth，以movieID排序：
> db.caption.find({longth:{$lt:20}}).sort({movieID:1}).explain()
{
        "cursor" : "BtreeCursor movie_longth",
        "nscanned" : 2,
        "nscannedObjects" : 2,
        "n" : 2,
        "millis" : 0,
        "indexBounds" : {
                "movieID" : [
                        [
                                {
                                        "$minElement" : 1
                                },
                                {
                                        "$maxElement" : 1
                                }
                        ]
                ],
                "longth" : [
                        [
                                -1.7976931348623157e+308,
                                20
                        ]
                ]
        }
}
就是说放过来仍然是能够用到索引的。
nice，验证想法应该可以对任意子集生效的。再次细读官网的说明，发现重点：
” If the first key of the index is present in the query, that index may be selected by the query optimizer. If the first key is not present in the query, the index will only be used if hinted explicitly. “
意思是说如果第一键出现默认是能被优化器用到index的，如果第一键未出现，但还是可以显示指定使用组合索引的，遂继续尝试：
> db.caption.find({longth:{$lt:20}}).hint({movieID:1,longth:1}).explain()
{
        "cursor" : "BtreeCursor movie_longth",
        "nscanned" : 2,
        "nscannedObjects" : 2,
        "n" : 2,
        "millis" : 0,
        "indexBounds" : {
                "movieID" : [
                        [
                                {
                                        "$minElement" : 1
                                },
                                {
                                        "$maxElement" : 1
                                }
                        ]
                ],
                "longth" : [
                        [
                                -1.7976931348623157e+308,
                                20
                        ]
                ]
        }
}
这样总算可以让复合索引被任意子集所使用上了。

     通过这个小细节的测试和探究，得出一个结论：当我们在网络上查阅资料时，请注意出处以及时间，特别是开源的内容，开源力量不断壮大，更新也是非常迅速，可能的老规则已经有新的替代；非常感谢雨痕的分享让大家熟悉了解到mongodb的使用，但从其文章的时间来看是2010-07，那个时候mongodb的版本还在1.4.5-， 而当前已经在12/9/2010发布了1.6.5。
      也给自己在平时做项目使用开源内容的时候多了一份提示：多跟进官网的资料，update新特性。

 

http://johnnychenjun.blog.163.com/blog/static/1374934062011012103129303/