索引
自动创建和手工创建
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":1},{"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 运算符 1.查询指定关键字: {"$search":"查询关键字"} 2.查询多个关键字(或关系):{"$search":"关键字1 关键字2 ..."} 3.查询多个关键字(与关系):{"$search":""关键字1" "关键字2" ..."} 4.查询多个关键词(排除某一个):{"$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" : 1 }
全文索引的限制：
一个集合只能创建一个全文索引，每次查询只能指定一个$text查询
$text不能出现在$nor查询中
查询如果包含了$text，$hint不起作用
不支持中文全文检索
地理信息索引
1.2d平面索引
2.2dsphere球面索引
2d 坐标保存的就是经纬度坐标
例子:定义一个商铺集合 db.shop.insert({"loc":[10,10]}); db.shop.insert({"loc":[10,11]}); db.shop.insert({"loc":[13,10]}); db.shop.insert({"loc":[30,40]}); db.shop.insert({"loc":[50,80]}); db.shop.insert({"loc":[100,110]}); 为shop添加2d索引 db.shop.ensureIndex({"loc":"2d"});
2种查询方式 $near 查询距离某个点最近的坐标点 $geoWithin 查询某个形状内的点 db.shop.find({"loc":{"$near":[10,11]}})
> db.shop.find({"loc":{"$near":[10,11]}}) 默认前100个 { "_id" : ObjectId("5944748d2e861d522342cb87"), "loc" : [ 10, 11 ] } { "_id" : ObjectId("5944748d2e861d522342cb86"), "loc" : [ 10, 10 ] } { "_id" : ObjectId("5944748d2e861d522342cb88"), "loc" : [ 13, 10 ] } { "_id" : ObjectId("5944748d2e861d522342cb89"), "loc" : [ 30, 40 ] } { "_id" : ObjectId("5944748d2e861d522342cb8a"), "loc" : [ 50, 80 ] } { "_id" : ObjectId("5944748e2e861d522342cb8b"), "loc" : [ 100, 110 ] } 设置范围3个点内的 db.shop.find({"loc":{"$near":[10,11],"$maxDistance":3}}) > db.shop.find({"loc":{"$near":[10,11],"$maxDistance":3}}) { "_id" : ObjectId("5944748d2e861d522342cb87"), "loc" : [ 10, 11 ] } { "_id" : ObjectId("5944748d2e861d522342cb86"), "loc" : [ 10, 10 ] }
在2d索引里支持最大距离，不支持最小距离
设置查询范围
使用 $geoWithin
矩形范围 {"$box":[[x1,y1],[x2,y2]]}
圆形范围 {"$center":[[x1,y1],r]}
多边形 {"$polygon":[[x1,y1],[x2,y2],[x3,y3] .....]}
1. db.shop.find({"loc":{"$geoWithin":{"$box":[[9,11],[13,20]]}}}) { "_id" : ObjectId("5944748d2e861d522342cb87"), "loc" : [ 10, 11 ] } 2. db.shop.find({"loc":{"$geoWithin":{"$center":[[9,10],3]}}}) { "_id" : ObjectId("5944748d2e861d522342cb86"), "loc" : [ 10, 10 ] } { "_id" : ObjectId("5944748d2e861d522342cb87"), "loc" : [ 10, 11 ] } runCommand() 执行特定命令 db.runCommand({"geoNear":"shop",near:[10,10],maxDistance:3,num:2}) { "results" : [ { "dis" : 0, "obj" : { "_id" : ObjectId("5944748d2e861d522342cb86"), "loc" : [ 10, 10 ] } }, { "dis" : 1, "obj" : { "_id" : ObjectId("5944748d2e861d522342cb87"), "loc" : [ 10, 11 ] } } ], "stats" : { "nscanned" : 32, "objectsLoaded" : 2, "avgDistance" : 0.5, "maxDistance" : 1, "time" : 47 }, "ok" : 1 }