mongodb(六):索引、执行计划及地理空间索引
索引是用来加速查询的准备数据
>db.users.insert({"username":"smith","age":48,"user_id":0})
>db.users.insert({"username":"smith","age":30,"user_id":1})
>db.users.insert({"username":"john","age":36,"user_id":2})
>db.users.insert({"username":"john","age":18,"user_id":3})
>db.users.insert({"username":"joe","age":36,"user_id":4})
>db.users.insert({"username":"john","age":7,"user_id":5})
> db.users.insert({"username":"simon","age":3,"user_id":6})
>db.users.insert({"username":"joe","age":27,"user_id":7})
>db.users.insert({"username":"jacob","age":17,"user_id":8})
>db.users.insert({"username":"sally","age":52,"user_id":9})
>db.users.insert({"username":"simon","age":59,"user_id":10})
一.单键索引
1.在没有创建索引前的执行计划(explain)
>db.users.find({"username":"joe"}).explain()
{
"cursor": "BasicCursor",(cursor为BasicCursor说明该查询没有走上索引)
"nscanned": 11,(查询的时候浏览了11个文档)
"nscannedObjects": 11,
"n": 2,(返回文档的数量)
"millis": 0,(执行查询时间，这里的文档的username重复很高，所以看不出效果，在这里只给出测试方法)
"indexBounds": {
}
}
2.创建索引和查看索引
>db.users.ensureIndex({"username":1},{"name":"index_username"})
> db.users.getIndexes()
[
{
"name": "_id_",
"ns": "test.users",
"key": {
"_id": 1
}
},
{
"_id": ObjectId("53f9ec055724be10470e0f42"),
"ns": "test.users",
"key": {
"username": 1
},
"name": "index_username"
}
]
> db.system.indexes.find()
{ "name" : "_id_","ns" : "test.blog", "key" : { "_id" :1 } }
{ "name" : "_id_","ns" : "test.foo", "key" : { "_id" :1 } }
{ "name" : "_id_","ns" : "test.c", "key" : { "_id" : 1} }
{ "name" : "_id_","ns" : "test.users", "key" : { "_id": 1 } }
{ "_id" :ObjectId("53f9ec055724be10470e0f42"), "ns" :"test.users", "key" : { "username" : 1 },"name" : "index_username" }
3.在创建索引后的执行计划
>db.users.find({"username":"joe"}).explain()
{
"cursor": "BtreeCursor index_username",(这里走上了索引)
"nscanned": 2,
"nscannedObjects": 2,
"n": 2,
"millis": 0,
"indexBounds": {
"username": [
[
"joe",
"joe"
]
]
}
}
4.删除索引，先查在删
>db.runCommand({"dropIndexes": "users", "index":"index_username"})
{ "nIndexesWas" : 2,"ok" : 1 }
二.复合索引(多键)
1.创建复合索引
>db.users.ensureIndex({"username":1,"age":1})
> db.system.indexes.find()
{ "name" : "_id_","ns" : "test.blog", "key" : { "_id" :1 } }
{ "name" : "_id_","ns" : "test.foo", "key" : { "_id" :1 } }
{ "name" : "_id_","ns" : "test.c", "key" : { "_id" : 1} }
{ "name" : "_id_","ns" : "test.users", "key" : { "_id": 1 } }
{ "_id" :ObjectId("53f9f62c5724be10470e0f45"), "ns" :"test.users", "key" : { "username" : 1,"age" : 1 }, "name" : "username_1_age_1" }
>db.users.ensureIndex({"age":1,"username":1})
> db.system.indexes.find()
{ "name" : "_id_","ns" : "test.blog", "key" : { "_id" :1 } }
{ "name" : "_id_","ns" : "test.foo", "key" : { "_id" :1 } }
{ "name" : "_id_","ns" : "test.c", "key" : { "_id" : 1} }
{ "name" : "_id_","ns" : "test.users", "key" : { "_id": 1 } }
{ "_id" :ObjectId("53f9f62c5724be10470e0f45"), "ns" :"test.users", "key" : { "username" : 1,"age" : 1 }, "name" : "username_1_age_1" }
{ "_id" :ObjectId("53f9f7ea5724be10470e0f46"), "ns" :"test.users", "key" : { "age" : 1,"username" : 1 }, "name" : "age_1_username_1" }
上面有两个索引username_1_age_1和age_1_username_1
2.在来看查询的执行计划
>db.users.find({"age":18,"username":/.*/}).explain()
{
"cursor": "BtreeCursor age_1_username_1 multi",
"nscanned": 1,
"nscannedObjects": 1,
"n": 1,
"millis": 0,
"indexBounds": {
"age": [
[
18,
18
]
],
"username": [
[
"",
{
}
],
[
/.*/,
/.*/
]
]
}
这里走上了age_1_username_1
我们希望走上的是username_1_age_1
所以应该强制让它走上username_1_age_1(用hint)
这里和oracle中对CBO调优类似,让查询按照我们想要的索引走，而不是根据优化器走
(小记：oracle中的调优技术:nestloop,hash join,merge join根据不同的需求进行选择)
>db.users.find({"age":18,"username":/.*/}).hint({"username":1,"age":1}).explain()
{
"cursor": "BtreeCursor username_1_age_1 multi",
"nscanned": 1,
"nscannedObjects": 1,
"n": 1,
"millis": 0,
"indexBounds": {
"username": [
[
"",
{
}
],
[
/.*/,
/.*/
]
],
"age": [
[
18,
18
]
]
}
}
3.修改索引
> db.users.ensureIndex({"age":1,"username":1},{"background":true})
作用索引的老化，不得不重新修改
三.地理空间索引
Mongodb为坐标平面查询用的
建立地理空间索引的键的值必须是一对值：一个包含两个数值的数组或包含两个键的内嵌文档（内嵌文档的键的名称无所谓），如：{“gps”：，100]}，{“gps”：{“x”：-30，“y”：30}}，{“gps”：{“latitude”：-180，“longitude”：180}}。
准备数据
>db.map.insert({"location":,"desc":"coffee"})
>db.map.insert({"location":[-70,30],"desc":"coffeeshop"})
>db.map.insert({"location":,"desc":"muffins"})
> db.map.insert({"location":[-90,-20],"desc":"espresso"})
1.创建索引
>db.map.ensureIndex({"location":"2d","desc":1})这里用2d且要用双引号
> db.map.getIndexes()
[
{
"name": "_id_",
"ns": "test.map",
"key": {
"_id": 1
}
},
{
"_id": ObjectId("53fa02be5724be10470e0f4c"),
"ns": "test.map",
"key": {
"location": "2d",
"desc": 1
},
"name": "location__desc_1"
}
]
2.查找最近的咖啡店用”$near"
>db.map.find({"location":{"$near":[-70,100]},"desc":"coffee"}).limit(1)
{ "_id" : ObjectId("53fa01625724be10470e0f48"),"location" : [ 50, 100 ], "desc" : "coffee" }
3.在矩形里查找
>db.map.find({"location":{"$within":{"$box":[[-70,0],]}},"desc":"coffeeshop"})
{ "_id" :ObjectId("53fa01a65724be10470e0f49"), "location" : [ -70,30 ], "desc" : "coffeeshop" }
通过{"$box":[[-70,0],]}指定一个矩形，其左下角和右上角坐标;”$within”指定查询在这个范围内的点。
4.在圆形区域查找
>db.map.find({"location":{"$within":{"$center":[[-70,0],50]}},"desc":"coffeeshop"})
{ "_id" :ObjectId("53fa01a65724be10470e0f49"), "location" : [ -70,30 ], "desc" : "coffeeshop" }
{"$center":[[-70,0],50]}，指定了圆形的圆心坐标和半径; ”$within”指定查询在这个范围内的点。
页:
[1]