本文使用Docker安装MongoDB,首先安装Dokcer
适合操作系统: ubuntu18
Docker安装
# 卸载旧版本
sudo apt-get purge docker-ce
sudo apt-get remove docker docker-engine docker.io containerd runc
sudo rm -rf /var/lib/docker
# 安装curl工具
sudo apt-get install curl -y
# 使用官方安装脚本
curl -fsSL https://get.docker.com -o get-docker.sh
sudo sh get-docker.sh
# 使得当前账户也能使用docker命令,若生效,可能需要重新登录或重启虚拟机
sudo usermod -aG docker ${USER}
若使用ubuntu18,可使用以下命令安装,docker的版本也是比较新的。
sudo apt-get update
# 卸载旧版本
sudo apt-get purge docker-ce
sudo apt-get remove docker docker-engine docker.io containerd runc
sudo rm -rf /var/lib/docker
# 安装docker
sudo apt install docker.io -y
# 使得当前账户也能使用docker命令,若生效,需要重新登录
sudo usermod -aG docker ${USER}
MongoDB安装
# 拉取官方镜像
sudo docker pull mongo
# 创建docker db挂载目录
mkdir /home/${USER}/docker/db/mongodb
# 运行刚刚拉取的镜像mongo,容器名:mongodb
docker run -p 27017:27017 -v /home/${USER}/docker/db/mongodb:/data/db --restart=always --name=mongodb -d mongo
MongoDB创建账户
# 进入docker mongodb终端
docker exec -it mongodb bash
# 弹出root@966de5e17b68:/#,输入mongo
mongo
# 在数据库admin下创建超级用户admin
use admin
db.createUser({user:"admin", pwd:"admin", roles: [{role : "userAdminAnyDatabase", db:"admin"}]})
db.auth("admin","admin")
# 切换至数据库iot
use iot
# 为数据库iot创建用户iotcloud
db.createUser({user:"iotcloud",pwd:"iotcloud",roles:[{role:"readWrite",db:"iot"}]})
MongoDB索引管理
随着业务的发展,可能有些索引不再需要,或者需要新建索引。
以数据库iot中的集合dev_msg_log为例:
# 进入docker mongodb终端
docker exec -it mongodb bash
# 弹出root@966de5e17b68:/#,输入mongo
mongo
use iot
# 查看所有的索引
db.dev_msg_log.getIndexes();
# 查看所有索引的大小,单位是字节
db.dev_msg_log.totalIndexSize()
# 根据索引name,删除指定的索引,比如删除name为“testidx”的索引
db.dev_msg_log.dropIndex("testidx");
# 创建新的索引,在后台根据字段attr1、attr2、attr3创建组合索引,索引名称“anotheridx”
db.dev_msg_log.ensureIndex({attr1:-1, attr2:-1, attr3:1},{name:"anotheridx", background:true})
查询分析
queryPlanner
查询分析可以帮助设计索引
db.body_sensor_log.find({"deviceId":"FF81BBDCB109FCE76B116779F2B5E425196801BF","uuid":"1F-FF-FF-FF-FF-FF","attr":0}).sort({"createTime":-1}).limit(1).explain();
得到如下结果:
{
"queryPlanner": {
"plannerVersion": 1,
"namespace": "iot.body_sensor_log",
"indexFilterSet": false,
"parsedQuery": {
"$and": [
{
"attr": {
"$eq": 0
}
},
{
"deviceId": {
"$eq": "FF81BBDCB109FCE76B116779F2B5E425196801BF"
}
},
{
"uuid": {
"$eq": "1F-FF-FF-FF-FF-FF"
}
}
]
},
"queryHash": "B9F93D3B",
"planCacheKey": "B69613C5",
"winningPlan": {
"stage": "LIMIT",
"limitAmount": 1,
"inputStage": {
"stage": "FETCH",
"inputStage": {
"stage": "IXSCAN",
"keyPattern": {
"deviceId": -1,
"uuid": -1,
"attr": -1,
"createTime": -1
},
"indexName": "idxDevIdUuidAttrTime",
"isMultiKey": false,
"multiKeyPaths": {
"deviceId": [],
"uuid": [],
"attr": [],
"createTime": []
},
"isUnique": false,
"isSparse": false,
"isPartial": false,
"indexVersion": 2,
"direction": "forward",
"indexBounds": {
"deviceId": [
"[\"FF81BBDCB109FCE76B116779F2B5E425196801BF\", \"FF81BBDCB109FCE76B116779F2B5E425196801BF\"]"
],
"uuid": [
"[\"1F-FF-FF-FF-FF-FF\", \"1F-FF-FF-FF-FF-FF\"]"
],
"attr": [
"[0.0, 0.0]"
],
"createTime": [
"[MaxKey, MinKey]"
]
}
}
}
},
"rejectedPlans": [
{
"stage": "SORT",
"sortPattern": {
"createTime": -1
},
"limitAmount": 1,
"inputStage": {
"stage": "SORT_KEY_GENERATOR",
"inputStage": {
"stage": "FETCH",
"inputStage": {
"stage": "IXSCAN",
"keyPattern": {
"deviceId": -1,
"uuid": -1,
"attr": -1
},
"indexName": "idxDevIdUuidAttr",
"isMultiKey": false,
"multiKeyPaths": {
"deviceId": [],
"uuid": [],
"attr": []
},
"isUnique": false,
"isSparse": false,
"isPartial": false,
"indexVersion": 2,
"direction": "forward",
"indexBounds": {
"deviceId": [
"[\"FF81BBDCB109FCE76B116779F2B5E425196801BF\", \"FF81BBDCB109FCE76B116779F2B5E425196801BF\"]"
],
"uuid": [
"[\"1F-FF-FF-FF-FF-FF\", \"1F-FF-FF-FF-FF-FF\"]"
],
"attr": [
"[0.0, 0.0]"
]
}
}
}
}
}
]
},
"serverInfo": {
"host": "c5cef4a7bfe5",
"port": 27017,
"version": "4.2.1",
"gitVersion": "edf6d45851c0b9ee15548f0f847df141764a317e"
},
"ok": 1
}
各字段的含义
queryPlanner: queryPlanner的返回
queryPlanner.namespace:该值返回的是该query所查询的表
queryPlanner.indexFilterSet:针对该query是否有indexfilter
queryPlanner.winningPlan:查询优化器针对该query所返回的最优执行计划的详细内容。
queryPlanner.winningPlan.stage:最优执行计划的stage,这里返回是FETCH,可以理解为通过返回的index位置去检索具体的文档(stage有数个模式,将在后文中进行详解)。
queryPlanner.winningPlan.inputStage:用来描述子stage,并且为其父stage提供文档和索引关键字。
queryPlanner.winningPlan.stage的child stage,此处是IXSCAN,表示进行的是index scanning。
queryPlanner.winningPlan.keyPattern:所扫描的index内容,此处是did:1,status:1,modify_time: -1与scid : 1
queryPlanner.winningPlan.indexName:winning plan所选用的index。
queryPlanner.winningPlan.isMultiKey是否是Multikey,此处返回是false,如果索引建立在array上,此处将是true。
queryPlanner.winningPlan.direction:此query的查询顺序,此处是forward,如果用了.sort({modify_time:-1})将显示backward。
queryPlanner.winningPlan.indexBounds:winningplan所扫描的索引范围,如果没有制定范围就是[MaxKey, MinKey],这主要是直接定位到mongodb的chunck中去查找数据,加快数据读取。
queryPlanner.rejectedPlans:其他执行计划(非最优而被查询优化器reject的)的详细返回,其中具体信息与winningPlan的返回中意义相同,故不在此赘述。
executionStats
在explain中添加executionStats可获取详细的执行分析,它包含了上述的queryPlanner,又新增了“executionStats”,如下:
db.body_sensor_log.find({"deviceId":"FF81BBDCB109FCE76B116779F2B5E425196801BF","uuid":"1F-FF-FF-FF-FF-FF","attr":0}).sort({"createTime":-1}).limit(1).explain("executionStats")
{
"queryPlanner": {
"plannerVersion": 1,
"namespace": "iot.body_sensor_log",
"indexFilterSet": false,
"parsedQuery": {
"$and": [
{
"attr": {
"$eq": 0
}
},
{
"deviceId": {
"$eq": "FF81BBDCB109FCE76B116779F2B5E425196801BF"
}
},
{
"uuid": {
"$eq": "1F-FF-FF-FF-FF-FF"
}
}
]
},
"winningPlan": { // 如果存在多个索引,会择优选择,竞选成功的称为winningPlan
"stage": "LIMIT", // limit比sort要好,sort在内存中排序了,limit限制返回结果数
"limitAmount": 1,
"inputStage": {
"stage": "FETCH", // 通过inputStage中的索引找到的数据位置拉取文档
"inputStage": {
"stage": "IXSCAN", // 扫描了索引B+树获取对应的数据位置
"keyPattern": {
"deviceId": -1,
"uuid": -1,
"attr": -1,
"createTime": -1
},
"indexName": "idxDevIdUuidAttrTime",
"isMultiKey": false,
"multiKeyPaths": {
"deviceId": [],
"uuid": [],
"attr": [],
"createTime": []
},
"isUnique": false,
"isSparse": false,
"isPartial": false,
"indexVersion": 2,
"direction": "forward",
"indexBounds": {
"deviceId": [
"[\"FF81BBDCB109FCE76B116779F2B5E425196801BF\", \"FF81BBDCB109FCE76B116779F2B5E425196801BF\"]"
],
"uuid": [
"[\"1F-FF-FF-FF-FF-FF\", \"1F-FF-FF-FF-FF-FF\"]"
],
"attr": [
"[0.0, 0.0]"
],
"createTime": [
"[MaxKey, MinKey]"
]
}
}
}
},
"rejectedPlans": [ // 竞选失败的称为rejectedPlan
{
"stage": "SORT", // 在内存中排序
"sortPattern": {
"createTime": -1
},
"limitAmount": 1,
"inputStage": {
"stage": "SORT_KEY_GENERATOR",
"inputStage": {
"stage": "FETCH", // 通过inputStage中的索引拉取文档
"inputStage": {
"stage": "IXSCAN", // 扫描了索引B+树获取对应的数据位置
"keyPattern": {
"deviceId": -1,
"uuid": -1,
"attr": -1
},
"indexName": "idxDevIdUuidAttr",
"isMultiKey": false,
"multiKeyPaths": {
"deviceId": [],
"uuid": [],
"attr": []
},
"isUnique": false,
"isSparse": false,
"isPartial": false,
"indexVersion": 2,
"direction": "forward",
"indexBounds": {
"deviceId": [
"[\"FF81BBDCB109FCE76B116779F2B5E425196801BF\", \"FF81BBDCB109FCE76B116779F2B5E425196801BF\"]"
],
"uuid": [
"[\"1F-FF-FF-FF-FF-FF\", \"1F-FF-FF-FF-FF-FF\"]"
],
"attr": [
"[0.0, 0.0]"
]
}
}
}
}
}
]
},
"executionStats": {
"executionSuccess": true,
"nReturned": 1,
"executionTimeMillis": 0,
"totalKeysExamined": 1,
"totalDocsExamined": 1,
"executionStages": {
"stage": "LIMIT",
"nReturned": 1,
"executionTimeMillisEstimate": 0,
"works": 2,
"advanced": 1,
"needTime": 0,
"needYield": 0,
"saveState": 0,
"restoreState": 0,
"isEOF": 1,
"limitAmount": 1,
"inputStage": {
"stage": "FETCH",
"nReturned": 1,
"executionTimeMillisEstimate": 0,
"works": 1,
"advanced": 1,
"needTime": 0,
"needYield": 0,
"saveState": 0,
"restoreState": 0,
"isEOF": 0,
"docsExamined": 1,
"alreadyHasObj": 0,
"inputStage": {
"stage": "IXSCAN",
"nReturned": 1,
"executionTimeMillisEstimate": 0,
"works": 1,
"advanced": 1,
"needTime": 0,
"needYield": 0,
"saveState": 0,
"restoreState": 0,
"isEOF": 0,
"keyPattern": {
"deviceId": -1,
"uuid": -1,
"attr": -1,
"createTime": -1
},
"indexName": "idxDevIdUuidAttrTime",
"isMultiKey": false,
"multiKeyPaths": {
"deviceId": [],
"uuid": [],
"attr": [],
"createTime": []
},
"isUnique": false,
"isSparse": false,
"isPartial": false,
"indexVersion": 2,
"direction": "forward",
"indexBounds": {
"deviceId": [
"[\"FF81BBDCB109FCE76B116779F2B5E425196801BF\", \"FF81BBDCB109FCE76B116779F2B5E425196801BF\"]"
],
"uuid": [
"[\"1F-FF-FF-FF-FF-FF\", \"1F-FF-FF-FF-FF-FF\"]"
],
"attr": [
"[0.0, 0.0]"
],
"createTime": [
"[MaxKey, MinKey]"
]
},
"keysExamined": 1,
"seeks": 1,
"dupsTested": 0,
"dupsDropped": 0
}
}
}
},
"serverInfo": {
"host": "c5cef4a7bfe5",
"port": 27017,
"version": "4.2.1",
"gitVersion": "edf6d45851c0b9ee15548f0f847df141764a317e"
},
"ok": 1
}
各字段的含义
第一层,executionTimeMillis
最为直观explain返回值是executionTimeMillis值,指的是我们这条语句的执行时间,这个值当然是希望越少越好。
其中有3个executionTimeMillis,分别是:
executionStats.executionTimeMillis
该query的整体查询时间。
executionStats.executionStages.executionTimeMillisEstimate
该查询根据index去检索document获得2001条数据的时间。
executionStats.executionStages.inputStage.executionTimeMillisEstimate
该查询扫描2001行index所用时间。
第二层,index与document扫描数与查询返回条目数
这个主要讨论3个返回项,nReturned、totalKeysExamined、totalDocsExamined,分别代表该条查询返回的条目、索引扫描条目、文档扫描条目。
这些都是直观地影响到executionTimeMillis,我们需要扫描的越少速度越快。
对于一个查询,我们最理想的状态是:
nReturned=totalKeysExamined=totalDocsExamined
第三层,stage状态分析
那么又是什么影响到了totalKeysExamined和totalDocsExamined?是stage的类型。类型列举如下:
COLLSCAN:全表扫描
IXSCAN:索引扫描
FETCH:根据索引去检索指定document
SHARD_MERGE:将各个分片返回数据进行merge
SORT:表明在内存中进行了排序
LIMIT:使用limit限制返回数
SKIP:使用skip进行跳过
IDHACK:针对_id进行查询
SHARDING_FILTER:通过mongos对分片数据进行查询
COUNT:利用db.coll.explain().count()之类进行count运算
COUNTSCAN:count不使用Index进行count时的stage返回
COUNT_SCAN:count使用了Index进行count时的stage返回
SUBPLA:未使用到索引的$or查询的stage返回
TEXT:使用全文索引进行查询时候的stage返回
PROJECTION:限定返回字段时候stage的返回
对于普通查询,我希望看到stage的组合(查询的时候尽可能用上索引):
Fetch+IDHACK
Fetch+ixscan
Limit+(Fetch+ixscan)
PROJECTION+ixscan
SHARDING_FITER+ixscan
COUNT_SCAN
不希望看到包含如下的stage:
COLLSCAN(全表扫描),SORT(使用sort但是无index),不合理的SKIP,SUBPLA(未用到index的$or),COUNTSCAN(不使用index进行count)