索引
自动创建和手工创建
db.stu.drop();
db.stu.insert({"name":"张三","sex":"男","age":18,"score":70,"address":"河南"});
db.stu.insert({"name":"李四","sex":"女","age":20,"score":60,"address":"山东"});
db.stu.insert({"name":"王五","sex":"男","age":17,"score":44,"address":"江苏"});
db.stu.insert({"name":"赵六","sex":"男","age":21,"score":80,"address":"山东"});
db.stu.insert({"name":"孙七","sex":"女","age":23,"score":50,"address":"湖北"});
db.stu.insert({"name":"tom","sex":"男","age":24,"score":20,"address":"海南"});
db.stu.insert({"name":"lucy","sex":"女","age":21,"score":62,"address":"浙江"});
db.stu.insert({"name":"jack","sex":"男","age":20,"score":90,"address":"美国"});
db.stu.insert({"name":"smith","sex":"男","age":19,"score":88,"address":"美国"});
查询默认状态下的stu集合索引内容
db.stu.getIndexes();
> db.stu.getIndexes();
[
{
"v" : 2,
"key" : {
"_id" : 1
},
"name" : "_id_",
"ns" : "test.stu"
}
] v:索引版本
_id:1 表示升序
索引创建
db.集合.ensureIndex({列:1})
1表示升序 -1降序
db.stu.ensureIndex({"age":-1});
> db.stu.ensureIndex({"age":-1});
{
"createdCollectionAutomatically" : false,
"numIndexesBefore" : 1,
"numIndexesAfter" : 2,
"ok" : 1
}
db.stu.getIndexes();
> db.stu.getIndexes();
[
{
"v" : 2,
"key" : {
"_id" : 1
},
"name" : "_id_",
"ns" : "test.stu"
},
{
"v" : 2,
"key" : {
"age" : -1
},
"name" : "age_-1",
"ns" : "test.stu"
}
]
这时索引名是自动命名的。命名规范: 字段名称_索引排序模式
索引使用分析
db.stu.find({"age":21}).explain();
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "test.stu",
"indexFilterSet" : false,
"parsedQuery" : {
"age" : {
"$eq" : 21
}
},
"winningPlan" : {
"stage" : "FETCH",
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"age" : -1
},
"indexName" : "age_-1",
"isMultiKey" : false,
"multiKeyPaths" : {
"age" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"age" : [
"[21.0, 21.0]"
]
}
}
},
"rejectedPlans" : [ ]
},
"serverInfo" : {
"host" : "centos1",
"port" : 27000,
"version" : "3.4.4",
"gitVersion" : "888390515874a9debd1b6c5d36559ca86b44babd"
},
"ok" : 1
}
"stage" : "IXSCAN"
在非索引的列上
> db.stu.find({"score":{"$gt":60}}).explain();
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "test.stu",
"indexFilterSet" : false,
"parsedQuery" : {
"score" : {
"$gt" : 60
}
},
"winningPlan" : {
"stage" : "COLLSCAN",
"filter" : {
"score" : {
"$gt" : 60
}
},
"direction" : "forward"
},
"rejectedPlans" : [ ]
},
"serverInfo" : {
"host" : "centos1",
"port" : 27000,
"version" : "3.4.4",
"gitVersion" : "888390515874a9debd1b6c5d36559ca86b44babd"
},
"ok" : 1
}
"stage" : "COLLSCAN"
db.stu.find({"$or":[
{"age":{"$gt":21}},
{"score":{"$gt":60}}
]}).explain();
此时age上有索引,score上没有，使用的是全表扫描
"stage" : "COLLSCAN",
这时可以使用符合索引
db.stu.ensureIndex({"age":-1,"score":-1},{"name":"age_-1_score_-1"})
执行查询
db.stu.find({"$or":[
{"age":{"$gt":21}},
{"score":{"$gt":60}}
]}).explain(); "stage" : "COLLSCAN",
db.stu.find({"$or":[
{"age":21},
{"score":80}
]}).explain();
依然
"stage" : "COLLSCAN"
强制使用索引
db.stu.find({"$or":[
{"age":21},
{"score":80}
]}).hint({"age":-1,"score":-1}).explain();
"stage" : "IXSCAN"
db.stu.find({"$or":[
{"age":21},
{"score":80}
]}).hint({"age":-1,"score":1}).explain();
会报错
db.stu.find({"$or":[
{"age":{"$gt":21}},
{"score":{"$gt":60}}
]}).hint({"age":-1,"score":-1}).explain();
stage" : "IXSCAN"
删除索引
db.stu.dropIndex({"age":-1,"score":-1});
删除全部索引(除了_id外)
db.stu.dropIndexes();
唯一索引
db.stus.ensureIndex({"name":},{"unique":true});
> db.stu.getIndexes();
[
{
"v" : 2,
"key" : {
"_id" : 1
},
"name" : "_id_",
"ns" : "hk.stu"
}
] db.stu.insert({"name":"smith","sex":"男","age":19,"score":88,"address":"美国"});
> db.stu.ensureIndex({"name":1},{"unique":true});
{
"ok" : 0,
"errmsg" : "E11000 duplicate key error collection: hk.stu index: name_1 dup key: { : \"smith\" }",
"code" : 11000,
"codeName" : "DuplicateKey"
}
>
db.stu.remove({ "_id" : ObjectId("5943194f2e32953979ce4b4d")});
WriteResult({ "nRemoved" : 1 })
> db.stu.ensureIndex({"name":1},{"unique":true});
{
"createdCollectionAutomatically" : false,
"numIndexesBefore" : 1,
"numIndexesAfter" : 2,
"ok" : 1
} > db.stu.getIndexes()
[
{
"v" : 2,
"key" : {
"_id" : 1
},
"name" : "_id_",
"ns" : "hk.stu"
},
{
"v" : 2,
"unique" : true,
"key" : {
"name" : 1
},
"name" : "name_1",
"ns" : "hk.stu"
}
]
过期索引 信息若干时间后过期
db.phones.ensureIndex({"time":1},{expireAfterSeconds:10});
//设置索引在10s后过期
db.phones.insert({"tel":"18600880451","code":"111","time":new Date()});
db.phones.insert({"tel":"18600880452","code":"112","time":new Date()});
db.phones.insert({"tel":"18600880453","code":"113","time":new Date()});
db.phones.insert({"tel":"18600880454","code":"114","time":new Date()});
db.phones.insert({"tel":"18600880455","code":"115","time":new Date()});
10秒后(不准确)
db.phones.find()
数据会消失
全文索引
设置全文检索(必须先有数据,后加索引,drop后必须重建索引)
实现模糊查询
表示全文索引 $text 判断符
数据的查询使用$search 运算符
.查询指定关键字: {"$search":"查询关键字"}
.查询多个关键字(或关系):{"$search":"关键字1 关键字2 ..."}
.查询多个关键字(与关系):{"$search":"\"关键字1\" \"关键字2\" ..."}
.查询多个关键词(排除某一个):{"$search":"关键词1 关键词2 .... -关键词3"}
var data=[
{"title":"apple","con":"this fruit is apple"},
{"title":"pear","con":"this fruit is pear"},
{"title":"peach","con":"this fruit is peach"},
{"title":"apple pear","con":"two fruit"},
{"title":"pear apple peach","con":"three fruit"},
{"title":"pear,apple,peach","con":"three fruit"},
{"title":"apple_1","con":"fruit"},
];
db.fruit.insert(data);
db.fruit.ensureIndex({"title":"text"});
多个能设置权重
{"weights":{"title":2,"con":1}
查询title里含有apple的
db.fruit.find({"$text":{"$search":"apple"}})
{ "_id" : ObjectId("59445eb93bdbe9486ee00ca7"), "title" : "pear apple peach", "con" : "three fruit" }
{ "_id" : ObjectId("59445eb93bdbe9486ee00ca6"), "title" : "apple pear", "con" : "two fruit" }
{ "_id" : ObjectId("59445eb93bdbe9486ee00ca8"), "title" : "pear,apple,peach", "con" : "three fruit" }
{ "_id" : ObjectId("59445eb93bdbe9486ee00ca3"), "title" : "apple", "con" : "this fruit is apple" }
查询有apple或者pear
db.fruit.find({"$text":{"$search":"apple pear"}})
{ "_id" : ObjectId("59445eb93bdbe9486ee00ca4"), "title" : "pear", "con" : "this fruit is pear" }
{ "_id" : ObjectId("59445eb93bdbe9486ee00ca3"), "title" : "apple", "con" : "this fruit is apple" }
{ "_id" : ObjectId("59445eb93bdbe9486ee00ca8"), "title" : "pear,apple,peach", "con" : "three fruit" }
{ "_id" : ObjectId("59445eb93bdbe9486ee00ca6"), "title" : "apple pear", "con" : "two fruit" }
{ "_id" : ObjectId("59445eb93bdbe9486ee00ca7"), "title" : "pear apple peach", "con" : "three fruit" }
查询有apple而且pear的
db.fruit.find({"$text":{"$search":"\"apple\" \"pear\""}})
{ "_id" : ObjectId("59445eb93bdbe9486ee00ca8"), "title" : "pear,apple,peach", "con" : "three fruit" }
{ "_id" : ObjectId("59445eb93bdbe9486ee00ca6"), "title" : "apple pear", "con" : "two fruit" }
{ "_id" : ObjectId("59445eb93bdbe9486ee00ca7"), "title" : "pear apple peach", "con" : "three fruit" }
查询有apple pear没有peach的
db.fruit.find({"$text":{"$search":"\"apple\" \"pear\" -peach"}})
{ "_id" : ObjectId("59445bfd3bdbe9486ee00ca0"), "title" : "apple pear", "con" : "two fruit" }
还可以使用相似度的打分来判断检索成果
为结果打分,分越高越准确
db.fruit.find({"$text":{"$search":"apple pear"}},{"score":{"$meta":"textScore"}})
排序
db.fruit.find({"$text":{"$search":"apple pear"}},{"score":{"$meta":"textScore"}}).sort({"score":{"$meta":"textScore"}})
> db.fruit.find({"$text":{"$search":"apple pear"}},{"score":{"$meta":"textScore"}}).sort({"score":{"$meta":"textScore"}})
{ "_id" : ObjectId("59445eb93bdbe9486ee00ca6"), "title" : "apple pear", "con" : "two fruit", "score" : 1.5 }
{ "_id" : ObjectId("59445eb93bdbe9486ee00ca7"), "title" : "pear apple peach", "con" : "three fruit", "score" : 1.3333333333333333 }
{ "_id" : ObjectId("59445eb93bdbe9486ee00ca8"), "title" : "pear,apple,peach", "con" : "three fruit", "score" : 1.3333333333333333 }
{ "_id" : ObjectId("59445eb93bdbe9486ee00ca4"), "title" : "pear", "con" : "this fruit is pear", "score" : 1.1 }
{ "_id" : ObjectId("59445eb93bdbe9486ee00ca3"), "title" : "apple", "con" : "this fruit is apple", "score" : }
全文索引的限制：
一个集合只能创建一个全文索引，每次查询只能指定一个$text查询
$text不能出现在$nor查询中
查询如果包含了$text，$hint不起作用
不支持中文全文检索
地理信息索引
1.2d平面索引
2.2dsphere球面索引
2d 坐标保存的就是经纬度坐标
例子:定义一个商铺集合
db.shop.insert({"loc":[,]});
db.shop.insert({"loc":[,]});
db.shop.insert({"loc":[,]});
db.shop.insert({"loc":[,]});
db.shop.insert({"loc":[,]});
db.shop.insert({"loc":[,]});
为shop添加2d索引
db.shop.ensureIndex({"loc":"2d"});
2种查询方式
$near 查询距离某个点最近的坐标点
$geoWithin 查询某个形状内的点
db.shop.find({"loc":{"$near":[,]}})
> db.shop.find({"loc":{"$near":[,]}})
默认前100个
{ "_id" : ObjectId("5944748d2e861d522342cb87"), "loc" : [ , ] }
{ "_id" : ObjectId("5944748d2e861d522342cb86"), "loc" : [ , ] }
{ "_id" : ObjectId("5944748d2e861d522342cb88"), "loc" : [ , ] }
{ "_id" : ObjectId("5944748d2e861d522342cb89"), "loc" : [ , ] }
{ "_id" : ObjectId("5944748d2e861d522342cb8a"), "loc" : [ , ] }
{ "_id" : ObjectId("5944748e2e861d522342cb8b"), "loc" : [ , ] }
设置范围3个点内的
db.shop.find({"loc":{"$near":[,],"$maxDistance":}})
> db.shop.find({"loc":{"$near":[,],"$maxDistance":}})
{ "_id" : ObjectId("5944748d2e861d522342cb87"), "loc" : [ , ] }
{ "_id" : ObjectId("5944748d2e861d522342cb86"), "loc" : [ , ] }
在2d索引里支持最大距离，不支持最小距离
设置查询范围
使用 $geoWithin
矩形范围 {"$box":[[x1,y1],[x2,y2]]}
圆形范围 {"$center":[[x1,y1],r]}
多边形 {"$polygon":[[x1,y1],[x2,y2],[x3,y3] .....]}
.
db.shop.find({"loc":{"$geoWithin":{"$box":[[,],[,]]}}})
{ "_id" : ObjectId("5944748d2e861d522342cb87"), "loc" : [ , ] }
.
db.shop.find({"loc":{"$geoWithin":{"$center":[[,],]}}})
{ "_id" : ObjectId("5944748d2e861d522342cb86"), "loc" : [ , ] }
{ "_id" : ObjectId("5944748d2e861d522342cb87"), "loc" : [ , ] }
runCommand() 执行特定命令
db.runCommand({"geoNear":"shop",near:[,],maxDistance:,num:})
{
"results" : [
{
"dis" : ,
"obj" : {
"_id" : ObjectId("5944748d2e861d522342cb86"),
"loc" : [
, ]
}
},
{
"dis" : ,
"obj" : {
"_id" : ObjectId("5944748d2e861d522342cb87"),
"loc" : [
, ]
}
}
],
"stats" : {
"nscanned" : ,
"objectsLoaded" : ,
"avgDistance" : 0.5,
"maxDistance" : ,
"time" :
},
"ok" :
}