回顾mongo索引


测试索引

新建50万条数据
for(var i=0;i<500000;i++){db.myusers.insert({"i":i,"username":"user"+i,"age":Math.floor(Math.random()*120),"created":new Date()});}
db.myusers.find({username:"user111"}).explain(true)
{
............
"executionStats" : {
"executionSuccess" : true,
"nReturned" : 1,
"executionTimeMillis" : 238,//-------------
"totalKeysExamined" : 0,
"totalDocsExamined" : 500000,//-------------
"executionStages" : {
"stage" : "COLLSCAN",//-------------
"filter" : {
"username" : {
"$eq" : "user111"
},
..............
}

增加索引
db.myusers.createIndex({"username":1})
好处:提高查询效率
坏处:数据增删改时会变慢
限制:每个集合最多64个索引
db.myusers.find({username:"user111"}).explain(true)
{
.................
"executionStats" : {
"executionSuccess" : true,
"nReturned" : 1,
"executionTimeMillis" : 0, //-------------
"totalKeysExamined" : 1,
"totalDocsExamined" : 1,//-------------
"executionStages" : {
"stage" : "FETCH",//-------------
"nReturned" : 1,
"executionTimeMillisEstimate" : 0,
..................
}


复合索引
索引--{"age":1,"username":1}
db.myusers.find({"age":21}) 高效
db.myusers.find({"age":21}).sort({"username":-1})高效
db.myusers.find({"age":{"$gte":21,"$lte":30}})高效
db.myusers.find({"age":{"$gte":21,"$lte":30}}).sort({"username":-1})一般


特殊索引
创建一个固定集合,最大1000字节,最多100个文档,超过后会自动删除最老的文档
db.createCollection('my_collection',{'capped':true,'size':1000,'max':'100'})

TTL索引
在lastUpdated字段建立TTL索引,当服务器时间比该字段存储的日期类型时间晚86400秒之后,文档立即删除。
Mongodb每分钟对ttl索引执行一次清理
db.my_collection.createIndex({"lastUpdated":1},{"expireAfterSecs":86400})


地理空间索引
2dsphere,用于球体表面,使用GeoJSON格式(geojson.org)

{'name':'Sjm','loc':{'type':'Point','coordinates':[116.34,40.02]}}
线
{'name':'River','loc':{'type':'Line','coordinates':[[0,1],[0,2],[1,2]]}}

{'name':'Beijing','loc':{'type':'Polygon','coordinates':[[2,1],[2,2],[4,2]]}}

创建索引
db.world.createIndex({'loc':'2dsphere'})
查询
var littleVillage={
'type':'Polygon',
'coordinates':[[-70,30],[-71,40],[-70,38],[-71,40]]
}
与littleVillage有交集的区域的文档:
db.world.find({'loc':{'$geoIntersects':{'$geometry':littleVillage}}})
完全包含在littleVillage区域的文档
db.world.find({'loc':{'$within':{'$geometry':littleVillage}}})
littleVillage区域附近的文档

db.world.find({'loc':{'$near':{'$geometry':littleVillage}}})


分类:   数据库  / 
标签:   mongo 索引
 /  浏览量:  275
2018-9-17 15:17 Monday