MongoDB的条件操作符 && limit（）、skip（）、sort（）方法 &&（单健、多健、复合）索引

一、MongoDB 条件操作符

1、条件操作符

>	$gt
>=	$gte
<	$lt
<=	$lte

演示如下：

> db.test.find()
{ "_id" : ObjectId("5d3818f585ad3637d228a6ad"), "title" : "PHP", "tag" : "php", "likes" : 100 }
{ "_id" : ObjectId("5d38190585ad3637d228a6ae"), "title" : "JAVA", "tag" : "java", "likes" : 150 }
{ "_id" : ObjectId("5d38193585ad3637d228a6af"), "title" : "MangoDB", "tag" : "mongodb", "likes" : 200 }
> db.test.find({likes : {$gt :150}}).pretty()		//相当于 SELECT * FROM  test WHERE likes > 150;
{
        "_id" : ObjectId("5d38193585ad3637d228a6af"),
        "title" : "MangoDB",
        "tag" : "mongodb",
        "likes" : 200
}

> db.test.find({likes : {$lt:200,$gt:100}}).pretty() 		//相当于 SELECT * FROM  test WHERE likes < 200 AND likes > 100;
{
        "_id" : ObjectId("5d38190585ad3637d228a6ae"),
        "title" : "JAVA",
        "tag" : "java",
        "likes" : 150
}

2、条件操作符 $type
$type操作符是基于BSON类型来检索集合中匹配的数据类型，并返回结果。
示例：获取 “test” 集合中 likes 为 String 的数据

> db.test.find()
{ "_id" : ObjectId("5d3820ef00308636f4d6a21d"), "title" : "PHP", "tag" : "php", "likes" : "100" }
{ "_id" : ObjectId("5d38210400308636f4d6a21e"), "title" : "JAVA", "tag" : "java", "likes" : 150 }
{ "_id" : ObjectId("5d38211900308636f4d6a21f"), "title" : "MangoDB", "tag" : "mangodb", "likes" : 200 }
> db.test.find({"likes" : {$type : 'string'}})
{ "_id" : ObjectId("5d3820ef00308636f4d6a21d"), "title" : "PHP", "tag" : "php", "likes" : "100" }
> db.test.find({"likes" : {$type : 2}})		//string类型对应数字即为2
{ "_id" : ObjectId("5d3820ef00308636f4d6a21d"), "title" : "PHP", "tag" : "php", "likes" : "100" }

二、MongoDB常用方法

1、limit方法
limit()方法接受一个数字参数，该参数指定从MongoDB中读取的记录条数。

> db.test.find()
{ "_id" : ObjectId("5d3820ef00308636f4d6a21d"), "title" : "PHP", "tag" : "php", "likes" : "100" }
{ "_id" : ObjectId("5d38210400308636f4d6a21e"), "title" : "JAVA", "tag" : "java", "likes" : 150 }
{ "_id" : ObjectId("5d38211900308636f4d6a21f"), "title" : "MangoDB", "tag" : "mangodb", "likes" : 200 }
> db.test.find().limit(2)
{ "_id" : ObjectId("5d3820ef00308636f4d6a21d"), "title" : "PHP", "tag" : "php", "likes" : "100" }
{ "_id" : ObjectId("5d38210400308636f4d6a21e"), "title" : "JAVA", "tag" : "java", "likes" : 150 }

2、skip（）方法
我们除了可以使用limit()方法来读取指定数量的数据外，还可以使用skip()方法来跳过指定数量的数据，skip方法同样接受一个数字参数作为跳过的记录条数。

> db.test.find().limit(1).skip(1)
{ "_id" : ObjectId("5d38210400308636f4d6a21e"), "title" : "JAVA", "tag" : "java", "likes" : 150 }

3、sort() 方法
在 MongoDB 中使用 sort() 方法对数据进行排序，sort() 方法可以通过参数指定排序的字段，并使用 1 和 -1 来指定排序的方式，其中 1 为升序排列，而 -1 是用于降序排列。

> db.test.find().sort({"likes" : -1})		//降序
{ "_id" : ObjectId("5d3820ef00308636f4d6a21d"), "title" : "PHP", "tag" : "php", "likes" : "100" }		//此行likes值为字符串类型
{ "_id" : ObjectId("5d38211900308636f4d6a21f"), "title" : "MangoDB", "tag" : "mangodb", "likes" : 200 }
{ "_id" : ObjectId("5d38210400308636f4d6a21e"), "title" : "JAVA", "tag" : "java", "likes" : 150 }
> db.test.find().sort({"likes" : 1})		//升序
{ "_id" : ObjectId("5d38210400308636f4d6a21e"), "title" : "JAVA", "tag" : "java", "likes" : 150 }
{ "_id" : ObjectId("5d38211900308636f4d6a21f"), "title" : "MangoDB", "tag" : "mangodb", "likes" : 200 }
{ "_id" : ObjectId("5d3820ef00308636f4d6a21d"), "title" : "PHP", "tag" : "php", "likes" : "100" }

skip(), limilt(), sort()三个放在一起执行的时候，执行的顺序是先 sort(), 然后是 skip()，最后是显示的 limit()。

> db.test.find().sort({"likes" : 1})
{ "_id" : ObjectId("5d38210400308636f4d6a21e"), "title" : "JAVA", "tag" : "java", "likes" : 150 }
{ "_id" : ObjectId("5d38211900308636f4d6a21f"), "title" : "MangoDB", "tag" : "mangodb", "likes" : 200 }
{ "_id" : ObjectId("5d3820ef00308636f4d6a21d"), "title" : "PHP", "tag" : "php", "likes" : "100" }
> db.test.find().limit(1).skip(1).sort({"likes" : 1})
{ "_id" : ObjectId("5d38211900308636f4d6a21f"), "title" : "MangoDB", "tag" : "mangodb", "likes" : 200 }

三、MongoDB索引

1、创建和删除索引基本语法介绍
MongoDB使用 createIndex() 方法来创建索引。
注：3.0.0 版本前创建索引方法为 db.collection.ensureIndex()，现在也能用，相当于别名

Key 值为你要创建的索引字段，1 为指定按升序创建索引，-1为指定按降序创建索引。

MongoDB 在创建集合期间在_id字段上 创建唯一索引。该索引可防止客户端插入具有相同字段值的两个文档。您不能在该字段上删除此索引

> db.record.getIndexes()			//查看集合索引
[
        {
                "v" : 2,
                "key" : {
                        "_id" : 1
                },
                "name" : "_id_",
                "ns" : "test.record"
        }
]

//删除集合指定索引

//删除集合所有索引

2、单健索引
（1）单个字段上创建索引

> db.record.find()
{ "_id" : ObjectId("5d3837d400308636f4d6a221"), "score" : 1034, "location" : { "state" : "NY", "city" : "New York" } }
{ "_id" : ObjectId("5d3838d200308636f4d6a222"), "score" : 777, "location" : { "state" : "LA", "city" : "Los Angeles" } }
> db.record.createIndex({score : 1})		//创建升序索引
{
        "createdCollectionAutomatically" : false,
        "numIndexesBefore" : 1,
        "numIndexesAfter" : 2,
        "ok" : 1
}
> db.record.getIndexes()
[
     ...	...	//为了更加直观，这里省略其他索引
        {
                "v" : 2,
                "key" : {
                        "score" : 1
                },
                "name" : "score_1",
                "ns" : "test.record"
        }
]
> db.record.find({score :{$gt :800}})
{ "_id" : ObjectId("5d3837d400308636f4d6a221"), "score" : 1034, "location" : { "state" : "NY", "city" : "New York" } }

（2）在嵌入式字段中创建索引

> db.record.createIndex({"location.state" : 1})
{
        "createdCollectionAutomatically" : false,
        "numIndexesBefore" : 1,
        "numIndexesAfter" : 2，
        "ok" : 1
}
> db.record.getIndexes()
[
       ...	...
        {
                "v" : 2,
                "key" : {
                        "location.state" : 1
                },
                "name" : "location.state_1",
                "ns" : "test.record"
        }
]
> db.record.find({"location.state" : "NY"})
{ "_id" : ObjectId("5d3837d400308636f4d6a221"), "score" : 1034, "location" : { "state" : "NY", "city" : "New York" } }

（3）在嵌入式文档中创建索引

> db.record.createIndex({location : 1})
{
        "createdCollectionAutomatically" : false,
        "numIndexesBefore" :1,
        "numIndexesAfter" : 2,
        "ok" : 1
}
> db.record.getIndexes()
[
        ...	...
        {
                "v" : 2,
                "key" : {
                        "location" : 1
                },
                "name" : "location_1",
                "ns" : "test.record"
        }
]

> db.record.find({location : {"state" : "NY","city" : "New York"}})
{ "_id" : ObjectId("5d3837d400308636f4d6a221"), "score" : 1034, "location" : { "state" : "NY", "city" : "New York" } }

此外，createIndex() 接收可选参数，下面演示一些常见的：

> db.record.createIndex({score : 1,location : -1},{background : true})		//后台创建索引
{
        "createdCollectionAutomatically" : false,
        "numIndexesBefore" : 1,
        "numIndexesAfter" : 2,
        "ok" : 1
}

> db.record.createIndex({score : 1},{name : "myIndex"})		//指定索引名称
{
        "createdCollectionAutomatically" : false,
        "numIndexesBefore" : 2,
        "numIndexesAfter" : 3,
        "ok" : 1
}
> db.record.getIndexes()
[
		...	...		
        {
                "v" : 2,
                "key" : {
                        "score" : 1
                },
                "name" : "myIndex",
                "ns" : "test.record"
        }
]

3、复合索引
MongoDB还支持多个字段的用户定义索引，即 复合索引。语法格式为：

> db.product.find().pretty()
{
        "_id" : ObjectId("5d3920e100308636f4d6a223"),
        "item" : "Banana",
        "category" : [
                "food",
                "produce",
                "grocery"
        ],
        "location" : "4th",
        "stock" : 4,
        "type" : "case"
}
> db.product.createIndex({"item" : 1,"stock" : 1})
{
        "createdCollectionAutomatically" : false,
        "numIndexesBefore" : 1,
        "numIndexesAfter" : 2,
        "ok" : 1
}

除了支持在所有索引字段上匹配的查询之外，复合索引还可以支持与索引字段的前缀匹配的查询。也就是说，索引支持对item字段以及字段item和stock字段的查询。

> db.product.find({item : "Banana"})
{ "_id" : ObjectId("5d3920e100308636f4d6a223"), "item" : "Banana", "category" : [ "food", "produce", "grocery" ], "location" : "4th", "stock" : 4, "type" : "case" }
> db.product.find({item : "Banana","stock" : {$lt : 5}})
{ "_id" : ObjectId("5d3920e100308636f4d6a223"), "item" : "Banana", "category" : [ "food", "produce", "grocery" ], "location" : "4th", "stock" : 4, "type" : "case" }

复合索引中列出的字段顺序具有重要意义。例如，如果有复合索引{“item” : 1,“stock” : 1}，则索引首先按照item升序排序，然后在每个score值内按照stock升序排序。对于复合索引，排序顺序可以决定索引是否可以支持排序操作。
例如，对于下面这个索引，
支持下面这两种排序操作


即，文档中所有键的指定排序方向必须与索引键模式匹配，或者与索引键模式的反转匹配
4、多健索引

要索引包含数组值的字段，MongoDB会为数组中的每个元素创建索引键。这些多键索引支持针对数组字段的高效查询。
创建语法格式如下：

（1）多健索引演示

> db.inventory.find()
{ "_id" : ObjectId("5d394bb800308636f4d6a228"), "type" : "food", "item" : "aaa", "ratings" : [ 5, 8, 9 ] }
{ "_id" : ObjectId("5d394be000308636f4d6a229"), "type" : "food", "item" : "bbb", "ratings" : [ 5, 9 ] }
{ "_id" : ObjectId("5d394c1900308636f4d6a22a"), "type" : "food", "item" : "ccc", "ratings" : [ 9, 5, 8 ] }
{ "_id" : ObjectId("5d394c2100308636f4d6a22b"), "type" : "food", "item" : "ddd", "ratings" : [ 9, 5 ] }
{ "_id" : ObjectId("5d394c2900308636f4d6a22c"), "type" : "food", "item" : "eee", "ratings" : [ 5, 9, 5 ] }

> db.inventory.createIndex({ratings : 1})		//基于ratings字段建立一个多健索引
{
        "createdCollectionAutomatically" : false,
        "numIndexesBefore" : 1,
        "numIndexesAfter" : 2,
        "ok" : 1
}

> db.inventory.find({ratings:[5,9]})		//查询ratings字段为数组[5,9]的文档
{ "_id" : ObjectId("5d394be000308636f4d6a229"), "type" : "food", "item" : "bbb", "ratings" : [ 5, 9 ] }

> db.inventory.find({ratings:[5,9]}).explain()		//使用explain（）看看执行计划
{
       ...	...
                        "inputStage" : {
                                "stage" : "IXSCAN",
                                "keyPattern" : {
                                        "ratings" : 1
                                },
                                "indexName" : "ratings_1",
                                "isMultiKey" : true,			//表明此次查询用到了多健索引
                                "multiKeyPaths" : {
                                        "ratings" : [
                                                "ratings"
                                        ]
                                },
     ...	...                          
        "ok" : 1
}

（2）基本索引数组
假定存在包含下列文档的survey集合：

{ _id: 1, item: "ABC", ratings: [ 2, 5, 9 ] }

在ratings上创建索引,db.survey.createIndex( { ratings: 1 } ), 这个多键索引则包括2,5,9三个索引键，每一个分别指向相同的文档
（3）基于嵌入式文档的索引数组

> db.inventory.insertMany([				//先创建一个inventory集合
... {
... _id : 1,
... item : "abc",
... stock :[
... {size : "s",color : "red",quantity : 25},
... {size : "s",color : "blue",quantity : 10},
... {size : "m",color : "blue",quantity : 50},
... ]
... },
... {
... _id : 2,
... item : "def",
... stock : [
... {size : "s",color : "red",quantity : 20},
... {size : "m",color : "blue",quantity : 5},
... {size : "m",color : "black",quantity : 10},
... {size : "l",color : "red",quantity : 2},
... ]
... }])

> db.inventory.find({"stock.size" : "l"})
{ "_id" : 2, "item" : "def", "stock" : [ { "size" : "s", "color" : "red", "quantity" : 20 }, { "size" : "m", "color" : "blue", "quantity" : 5 }, { "size" : "m", "color" : "black", "quantity" : 10 }, { "size" : "l", "color" : "red", "quantity" : 2 } ] }
> db.inventory.find({"stock.size" : "l"}).explain()		//看看内嵌文档stock.size为 l 的执行计划
{
       ...	...

                "winningPlan" : {
                        "stage" : "FETCH",
                        "inputStage" : {
                                "stage" : "IXSCAN",		//基于索引的扫描
                                "keyPattern" : {
                                        "stock.size" : 1,
                                        "stock.quantity" : 1
                                },
                                "indexName" : "stock.size_1_stock.quantity_1",
                                "isMultiKey" : true,
                                "multiKeyPaths" : {
                                        "stock.size" : [
                                                "stock"
                                        ],
                                        "stock.quantity" : [
                                                "stock"
                                        ]
                                },
       	...	...
        "ok" : 1
}

> db.inventory.find({"stock.size" : "m","stock.quantity" : {$gt: 20}}).pretty()		// //基于内嵌文档2个键查询
{
        "_id" : 1,
        "item" : "abc",
        "stock" : [
                {
                        "size" : "s",
                        "color" : "red",
                        "quantity" : 25
                },
                {
                        "size" : "s",
                        "color" : "blue",
                        "quantity" : 10
                },
                {
                        "size" : "m",
                        "color" : "blue",
                        "quantity" : 50
                }
        ]
}

5、复合多健索引
如果文档的多个要索引字段是数组，则无法创建复合多键索引。

> db.array.find()		//创建了array集合用于演示
{ "_id" : ObjectId("5d3aa45b886cd3eea1085015"), "a" : [ 1, 2 ], "b" : [ 5, 9 ] }
> db.array.createIndex({a : 1,b : 1})	
{
        "ok" : 0,
        "errmsg" : "cannot index parallel arrays [b] [a]",
        "code" : 171,
        "codeName" : "CannotIndexParallelArrays"		//我们不能创建{a : 1,b : 1}索引，因为a、b字段都是数组
}

对于下面这种情况，复合多健索引是可以的：

> db.array1.find()		//array1只有字段a是数组
{ "_id" : ObjectId("5d3aa698886cd3eea1085016"), "a" : [ 1, 2 ], "b" : 1 }
> db.array2.find()		//array2只有字段b是数组
{ "_id" : ObjectId("5d3aa6d1886cd3eea1085018"), "a" : 1, "b" : [ 5, 9 ] }
> db.array1.createIndex({a : 1,b : 1})
{
        "createdCollectionAutomatically" : false,
        "numIndexesBefore" : 1,
        "numIndexesAfter" : 2,
        "ok" : 1
}
> db.array2.createIndex({a : 1,b : 1})
{
        "createdCollectionAutomatically" : false,
        "numIndexesBefore" : 1,
        "numIndexesAfter" : 2,
        "ok" : 1
}

如果已存在复合多键索引，则无法插入会违反此限制的文档：

> db.array1.find()
{ "_id" : ObjectId("5d3aa698886cd3eea1085016"), "a" : [ 1, 2 ], "b" : 1 }
> db.array1.insert({a : [1,3],b : [2,4]})		//array1存在复合多健索引，不能插入两个数组
WriteResult({
        "nInserted" : 0,
        "writeError" : {
                "code" : 171,
                "errmsg" : "cannot index parallel arrays [b] [a]"
        }
})
> db.array1.dropIndexes()		//删除array的索引
{
        "nIndexesWas" : 2,
        "msg" : "non-_id indexes dropped for collection",
        "ok" : 1
}
> db.array1.insert({a : [1,3],b : [2,4]})
WriteResult({ "nInserted" : 1 })
> db.array1.find()		//插入成功
{ "_id" : ObjectId("5d3aa698886cd3eea1085016"), "a" : [ 1, 2 ], "b" : 1 }
{ "_id" : ObjectId("5d3aaa02b3a8911f2c1a70d3"), "a" : [ 1, 3 ], "b" : [ 2, 4 ] }