索引
一、索引操作
1.1 查寻索引
db.getCollection('test').getIndexes()
1.2 创建索引
db.getCollection('test').createIndex({time:-1}, {background: true})
db.getCollection('test').createIndex({source_id: 1, time: -1, quality: 1}, {background: true})
- 创建TTL索引(创建了TTL索引之后,当时间到指定时间后记录就会被删除,可以用于定期删除数据)
db.getCollection('test').createIndex({"time":1},{expireAfterSeconds:60*60*24*7})
1.3 索引别名
- 创建索引时,mongodb默认会按照字段顺序给索引创建一个名字,如果字段过多可能会使得这个默认的名字超过127字节的大小限制,此时可以添加一个别名,格式如下:
db.getCollection('test').
createIndex({source_id: 1, time: -1, quality: 1}, {background: true,name:"fakeName"})
二、索引创建进度
- 数据量很大的时候可能会耗费非常久的时间,我们需要查询创建索引的进度。
2.1 查询索引创建进度
- 1.首先我们使用mongo命令连接数据库,然后使用下面的语句可以查询出操作时间大于10秒的操作
db.currentOp( {
"active" : true,
"secs_running" :{
"$gte":10
}
})
- 然后就会显示出创建索引的操作,如下command可以看到这是一个创建索引的操作,msg显示出创建索引的进度,有对应的已经构建索引的数据量和总数据量,opid是操作标识号,可以用于终止该任务(为节约篇幅很多其他的信息已经删除)
{
"inprog" : [
{
"shard" : "rs_shard_server1",
"host" : "ubuntu:20012",
"desc" : "conn6794",
"connectionId" : 6794,
"client_s" : "192.168.13.21:34202",
"appName" : "MongoDB Shell",
"clientMetadata" : {
"application" : {
"name" : "MongoDB Shell"
},
"driver" : {
"name" : "MongoDB Internal Client",
"version" : "3.6.10-rc0"
},
"os" : {
"type" : "Linux",
"name" : "Ubuntu",
"architecture" : "x86_64",
"version" : "18.04"
},
"mongos" : {
"host" : "ubuntu:27017",
"client" : "192.168.13.51:52122",
"version" : "3.6.5"
}
},
"active" : true,
"currentOpTime" : "2019-05-29T11:23:07.511+0000",
"opid" : "rs_shard_server1:571849", //操作标识
"lsid" : {
"id" : UUID("4c9121c5-a961-4d3a-be01-f4245b9975de"),
"uid" : BinData(0,"47DEQpj8HBSa+/TImW+5JCeuQeRkm5NMpJWZG3hSuFU=")
},
"secs_running" : NumberLong(215),
"microsecs_running" : NumberLong(215508284),
"op" : "command",
"ns" : "ifaas_data.$cmd",
"command" : {
"createIndexes" : "multobj",
"indexes" : [
{
"key" : {
"sourceId" : 1,
"sourceType" : 1,
"targetType" : 1,
"time" : -1
},
"name" : "sourceId_1_sourceType_1_targetType_1_time_-1", //构建索引的详情信息
"background" : true
}
],
"lsid" : {
"id" : UUID("4c9121c5-a961-4d3a-be01-f4245b9975de")
},
"shardVersion" : [
Timestamp(0, 0),
ObjectId("000000000000000000000000")
],
"$clusterTime" : {
"clusterTime" : Timestamp(1559128768, 4),
"signature" : {
"hash" : BinData(0,"lvlpeddR0Fcj/6Coy3qVHaYrhdI="),
"keyId" : NumberLong("6694560683847057427")
}
},
"$client" : {
"application" : {
"name" : "MongoDB Shell"
},
"driver" : {
"name" : "MongoDB Internal Client",
"version" : "3.6.10-rc0"
},
"os" : {
"type" : "Linux",
"name" : "Ubuntu",
"architecture" : "x86_64",
"version" : "18.04"
},
"mongos" : {
"host" : "ubuntu:27017",
"client" : "192.168.13.51:52122",
"version" : "3.6.5"
}
},
"$configServerState" : {
"opTime" : {
"ts" : Timestamp(1559128768, 4),
"t" : NumberLong(7)
}
},
"$db" : "ifaas_data"
},
"msg" : "Index Build (background) Index Build (background): 1707499/10691545 15%", //索引构建进度占比
"progress" : {
"done" : 1707500,
"total" : 10691545
},
"numYields" : 16072,
"locks" : {
"Global" : "w",
"Database" : "w",
"Collection" : "w"
},
"waitingForLock" : false,
"lockStats" : {
"Global" : {
"acquireCount" : {
"r" : NumberLong(16073),
"w" : NumberLong(16073)
}
},
"Database" : {
"acquireCount" : {
"w" : NumberLong(16073),
"W" : NumberLong(1)
}
},
"Collection" : {
"acquireCount" : {
"w" : NumberLong(16073)
}
}
}
}
"ok" : 1,
"$clusterTime" : {
"clusterTime" : Timestamp(1559128985, 1),
"signature" : {
"hash" : BinData(0,"AAAAAAAAAAAAAAAAAAAAAAAAAAA="),
"keyId" : NumberLong(0)
}
},
"operationTime" : Timestamp(1559128985, 1)
}
2.2 终止索引创建
- 删除对应的操作(创建索引耗时很久,如果不需要我们可以终止这个动作),命令格式: db.killOp(opid),opid是查询出来的操作号
db.killOp("sh1:7528420")
db.killOp("sh2:8599133")
db.killOp("sh3:9594377")
三、索引优化
四、参考