mongo-索引（1）

使用集合 db.tianyc_test8 进行测试，该表建立在一个分片集群上，初始索引只有_id。

> db.tianyc_test8.stats()
{
"sharded" : true,
"ns" : "Gps.tianyc_test8",
"count" : 15960997,
"numExtents" : 45,
"size" : 7768445412,
"storageSize" : 11004829696,
"totalIndexSize" : 1628732784,
"indexSizes" : {
"DeviceId_1" : 993236832,
"_id_" : 635495952
},
"avgObjSize" : 486.7142956044663,
"nindexes" : 2,
"nchunks" : 79,
"shards" : {
"seta" : {
"ns" : "Gps.tianyc_test8",
"count" : 7881998,
"size" : 3831673928,
"avgObjSize" : 486.1297767393496,
"storageSize" : 5297451008,
"numExtents" : 23,
"nindexes" : 2,
"lastExtentSize" : 1378918400,
"paddingFactor" : 1,
"systemFlags" : 1,
"userFlags" : 0,
"totalIndexSize" : 804044192,
"indexSizes" : {
"_id_" : 303321424,
"DeviceId_1" : 500722768
},
"ok" : 1
},
"setb" : {
"ns" : "Gps.tianyc_test8",
"count" : 8078999,
"size" : 3936771484,
"avgObjSize" : 487.28456136707035,
"storageSize" : 5707378688,
"numExtents" : 22,
"nindexes" : 2,
"lastExtentSize" : 1496932352,
"paddingFactor" : 1,
"systemFlags" : 1,
"userFlags" : 0,
"totalIndexSize" : 824688592,
"indexSizes" : {
"_id_" : 332174528,
"DeviceId_1" : 492514064
},
"ok" : 1
}
},
"ok" : 1
}

> db.tianyc_test8.getIndexes()
[
{
"v" : 1,
"key" : {
"_id" : 1
},
"ns" : "Gps.tianyc_test8",
"name" : "_id_"
}
]

#1. 在DeviceId, HashId, Time三个列上分别创建索引。

建立索引的过程非常消耗资源。如果集合比较大，创建时记得使用background参数，可以使该过程在后台完成，同时mongo可以正常处理其他请求。否则数据库会阻塞建立索引期间的所有请求，这在生产库上是个灾难。

> db.tianyc_test8.ensureIndex({HashId:1},{background:true})

> db.tianyc_test8.ensureIndex({DeviceId:1},{background:true})

> db.tianyc_test8.ensureIndex({Time:1},{background:true})

# 2. 查询执行计划

> db.tianyc_test8.find({DeviceId:'xjbu',HashId:'jjxbb'}).explain()
{
"clusteredType" : "ParallelSort",
"shards" : {
"setb/10.0.0.40:10000,10.0.0.41:10000" : [
{
"cursor" : "BtreeCursor DeviceId_1",
"isMultiKey" : false,
"n" : 0,
"nscannedObjects" : 0,
"nscanned" : 0,
"nscannedObjectsAllPlans" : 0,
"nscannedAllPlans" : 1,
"scanAndOrder" : false,
"indexOnly" : false,
"nYields" : 0,
"nChunkSkips" : 0,
"millis" : 0,
"indexBounds" : {
"DeviceId" : [
[
"xjbu",
"xjbu"
]
]
},
"server" : "WIN-9B3ASS056VL:10000"
}
]
},
"n" : 0,
"nChunkSkips" : 0,
"nYields" : 0,
"nscanned" : 0,
"nscannedAllPlans" : 1,
"nscannedObjects" : 0,
"nscannedObjectsAllPlans" : 0,
"millisTotal" : 0,
"millisAvg" : 0,
"numQueries" : 1,
"numShards" : 1
}
> db.tianyc_test8.find({DeviceId:'xjbu',HashId:'jjxbb'}).hint('HashId_1').explain()
{
"clusteredType" : "ParallelSort",
"shards" : {
"setb/10.0.0.40:10000,10.0.0.41:10000" : [
{
"cursor" : "BtreeCursor HashId_1",
"isMultiKey" : false,
"n" : 0,
"nscannedObjects" : 0,
"nscanned" : 0,
"nscannedObjectsAllPlans" : 0,
"nscannedAllPlans" : 0,
"scanAndOrder" : false,
"indexOnly" : false,
"nYields" : 0,
"nChunkSkips" : 0,
"millis" : 0,
"indexBounds" : {
"HashId" : [
[
"jjxbb",
"jjxbb"
]
]
},
"server" : "WIN-9B3ASS056VL:10000"
}
]
},
"n" : 0,
"nChunkSkips" : 0,
"nYields" : 0,
"nscanned" : 0,
"nscannedAllPlans" : 0,
"nscannedObjects" : 0,
"nscannedObjectsAllPlans" : 0,
"millisTotal" : 0,
"millisAvg" : 0,
"numQueries" : 1,
"numShards" : 1
}
>

# 3. 删除索引

> db.tianyc_test8.dropIndex('HashId_1')

> db.tianyc_test8.dropIndex('DeviceId_1')

> db.tianyc_test8.dropIndex('Time_1')