1. 执行计划查看
db.getCollection('users').find({'username': 'shenjian'}).explain('executionStats')
结果查看，先大致看一遍，后续慢慢来深入理解
{
"queryPlanner": {
"plannerVersion": NumberInt("1"),
"namespace": "study.users",
"indexFilterSet": false,
"parsedQuery": {
"username": {
"$eq": "shenjian"
}
},
"winningPlan": {
"stage": "COLLSCAN",
"filter": {
"username": {
"$eq": "shenjian"
}
},
"direction": "forward"
},
"rejectedPlans": [ ]
},
"executionStats": {
"executionSuccess": true,
"nReturned": NumberInt("0"),
"executionTimeMillis": NumberInt("518"),
"totalKeysExamined": NumberInt("0"),
"totalDocsExamined": NumberInt("1000000"),
"executionStages": {
"stage": "COLLSCAN",
"filter": {
"username": {
"$eq": "shenjian"
}
},
"nReturned": NumberInt("0"),
"executionTimeMillisEstimate": NumberInt("0"),
"works": NumberInt("1000002"),
"advanced": NumberInt("0"),
"needTime": NumberInt("1000001"),
"needYield": NumberInt("0"),
"saveState": NumberInt("7812"),
"restoreState": NumberInt("7812"),
"isEOF": NumberInt("1"),
"direction": "forward",
"docsExamined": NumberInt("1000000")
}
},
"serverInfo": {
"host": "4a8812679047",
"port": NumberInt("27017"),
"version": "4.2.6",
"gitVersion": "20364840b8f1af16917e4c23c1b5f5efd8b352f8"
},
"ok": 1
}
2. 创建索引
db.getCollection('users').createIndex({'username': 1})
// 查看索引
db.getCollection('users').getIndexes({})
// 删除索引
db.getCollection('users').dropIndex({'username_1'})
如果集合特别大，则新开个窗口，执行命令db.currentOp(),重点关注ns为study.users的文档，搜索msg消息查看进度
"msg": "Index Build: inserting keys from external sorter into index Index Build: inserting keys from external sorter into index: 849146/1000000 84%",
"progress": {
"done": NumberInt("849150"),
"total": NumberInt("1000000")
}
3. 复合索引简介
db.getCollection('users').createIndex({'age': 1, 'username': 1})
对于多条件查询，复合查询用处很大，比如上面索引会严格按照age排序在按username排序，所以按照索引键对文档排序速度会快的多
4. MONGO如何选择索引
让多个查询计划相互竞争，只有查询最优的查询计划才会被选中，mongo会维护查询计划的缓存，这样后续查询就可以直接选择索引进行查询
5. 使用复合索引原则
等值过滤的键应该在最前面用于排序的键应该在多值字段之前多值过滤的键应该在最后面
这一块推荐看《MongoDB权威指南》索引章节，特别详细。如
db.getCollection('students').createIndex({'class_id': 1, 'final_grade': 1, 'student_id': 1})
索引对于查询就很高效
db.getCollection('students').find({'class_id': 54, 'student_id': {$gt: 50000}}).sort({'final_grade': 1}).explain('executionStats')
5.1 选择键的方向
基于多键排序时，方向才重要，如
db.getCollection('users').createIndex({'age': 1, 'username': -1})
5.2 使用覆盖索引
当索引中的字段包含用户请求的所有字段，呢么这个索引就覆盖了本次查询，就不会去获取实际的文档了，注意如果索引中不包含_id，则需要考虑_id不显示才能够覆盖索引查询,如
db.getCollection('users').find({'age': 110, 'username': 'user12'}, {'age': 1, 'username': 1, '_id': 0}).explain('executionStats')
{
"queryPlanner": {
"winningPlan": {
// 获胜的执行计划 覆盖索引
"stage": "PROJECTION_COVERED",
"transformBy": {
"age": 1,
"username": 1,
"_id": 0
},
"inputStage": {
"stage": "IXSCAN",
"keyPattern": {
"age": 1,
"username": 1
},
"indexName": "age_1_username_1"
}
}
},
"executionStats": {
"executionSuccess": true,
"nReturned": NumberInt("1"),
"executionTimeMillis": NumberInt("0"),
"totalKeysExamined": NumberInt("1"),
// 扫描的文档数为0
"totalDocsExamined": NumberInt("0")
}
}
5.3 隐式索引
复核索引具有双重功能，比如也具有索引{‘class_id’: 1}、{‘class_id’: 1,‘final_grade’: 1}的功能
db.getCollection('students').createIndex({'class_id': 1, 'final_grade': 1, 'student_id': 1})
但是对于{‘class_id’: 1, ‘student_id’: 1}不具有隐式索引，只有使用索引前缀的查询才会收益
欢迎关注公众号算法小生或沈健的技术博客shenjian.online
好文阅读
发表评论