mongodb 的查询如何使用索引

2019独角兽企业重金招聘Python工程师标准>>>

一、准备

        需要在mongodb中添加测试数据

db.user.insert({"name":"张三","age":34,"job":"java 开发","phone":"18989834028","addr":"广东深圳福田"})
db.user.insert({"name":"李四","age":29,"job":"php 开发","phone":"18984834098","addr":"广东深圳南山"})
db.user.insert({"name":"王五","age":38,"job":"部门经理","phone":"18981834098","addr":"广东深圳罗湖"})
db.user.insert({"name":"赵一","age":31,"job":"产品经理","phone":"18989764098","addr":"广东广州"})
db.user.insert({"name":"钱二","age":26,"job":"销售","phone":"18989834968","addr":"广东珠海"})
db.user.insert({"name":"周六","age":54,"job":"销售经理","phone":"18981234098","addr":"广东中山"})
db.user.insert({"name":"吴奇","age":17,"job":"学生","phone":"18735834098","addr":"广东韶关"})
db.user.insert({"name":"郑八","age":45,"job":"教师","phone":"18989834098","addr":"广东惠州"})
db.user.insert({"name":"冯峰","age":23,"job":"java 开发","phone":"13689834098","addr":"广东广州越秀"})

          我们知道使用mongodb objectId数据主键_id是默认有索引的。下面先按名称和手机创建单独的索引，然后创建一个联合主键

db.user.ensureIndex({"phone":1});
db.user.ensureIndex({"name":1});
db.user.ensureIndex({"name":1,"phone":1});

          查看下现在已有的索引

>db.user.getIndexes();
[
        {
                "v" : 1,
                "key" : {
                        "_id" : 1
                },
                "name" : "_id_",
                "ns" : "test.user"
        },
        {
                "v" : 1,
                "key" : {
                        "name" : 1
                },
                "name" : "name_1",
                "ns" : "test.user"
        },
        {
                "v" : 1,
                "key" : {
                        "phone" : 1
                },
                "name" : "phone_1",
                "ns" : "test.user"
        },
        {
                "v" : 1,
                "key" : {
                        "name" : 1,
                        "phone" : 1
                },
                "name" : "name_1_phone_1",
                "ns" : "test.user"
        }
]

二、测试

       使用explain()方法查看执行计划，首先看下不使用查询条件。

> db.user.find().explain();
{
        "queryPlanner" : {
                  "winningPlan" : {
                        "stage" : "COLLSCAN",
  
}

           可以看到没有使用查询条件的情况下使用的是全表扫描。

           再来看下根据name,和phone作为查询条件的情况下，使用的是联合索引，还是单独的索引。

> db.user.find({"phone":"13678763456","name":"李斯"}).explain();
{
        "queryPlanner" : {
                  "winningPlan" : {
                                "indexName" : "name_1",                           
                },
                "rejectedPlans" : [
                     {
                                        "indexName" : "name_1_phone_1",
                                        ....                                      
                                }
                        },
                        {
.....
                                        "indexName" : "phone_1",
                                       ......                                       
                                }
                        }
                ]
        },
}

         可以看到，并没有使用联合主键并没有在最优计划（winningPlan）中，而是在rejectedplan中。先使用了phone的单独索引。调整查询条件顺序，索引的顺序是name,phone，看下

> db.user.find({"name":"李斯","phone":"18737260965"}).explain();
{
        "queryPlanner" : {
            ....
                "winningPlan" : {
                       ......
                        "inputStage" : {
                               .......
                                "indexName" : "name_1",
                             ......                            
                        }
                },
                "rejectedPlans" : [
                        {
                                "inputStage" : {
                                      .........
                                        "indexName" : "name_1_phone_1",
                                      ........
                                     
                        },
                        {    
                                "inputStage" : {                                      
                                        "indexName" : "phone_1",
                                      .......                                    
                        }
                ]
        },

}

        调整顺序之后还是一样，在winningPlan使用name的单独索引而不是联合索引。删除单独的索引再测试

db.user.dropIndex("name_1");
db.user.dropIndex("phone_1");

   测试

> db.user.find({"name":"李斯","phone":"18737260965"}).explain();
....
 "winningPlan" : {                       
                       ...
                                "indexName" : "name_1_phone_1",
                                ...
                                
                 
> db.user.find({"phone":"13678763456","name":"李斯"}).explain();
  "winningPlan" : {
                       .....
                                "indexName" : "name_1_phone_1",
                                ....
> db.user.find({"phone":"13678763456","name":"李斯","age":23}).explain();
  "winningPlan" : {
  。。。
 "indexName" : "name_1_phone_1",
 
> db.user.find({"phone":"13678763456","age":23,"name":"李斯"}).explain();

 "winningPlan" : {
      "indexName" : "name_1_phone_1",
      
> db.user.find({"phone":"13678763456"}).explain();
 "winningPlan" : {
                        "stage" : "COLLSCAN",
                        
> db.user.find({"name":"张三"}).explain();
  "winningPlan" : {
。。。。
                                "indexName" : "name_1_phone_1",
                                
> db.user.find({"age":33,"name":"张三"}).explain();
 "winningPlan" : {
                     。。。。
                                "indexName" : "name_1_phone_1",

     可以看到对于联合索引，如果只使用一部分，则必须是按顺序的（在本测试中只用使用name才用到索引），如果满足条件（name,phone 都出现在条件中），则没有顺序要求。

   三、终结

          通过简单的测试，对mongodb（ "version" : "3.2.4",）的索引工作原理理解：

               1、如果某个字段同时出现在单独索引和联合索引中，winningPlan 使用的是单独索引。

               2、如果查询条件满足索引的条件，则不需要管理条件字段顺序，

               3、如果查询条件字段不满足联合索引，则必须是按创建索引的字段顺序查询才会使用索引。（可以有其他字段在索引字段前如，{"age":33,"name":"张三"}).

转载于:https://my.oschina.net/u/2563693/blog/656251