云上MongoDB常见索引问题及最优索引规则大全
1 ►
背景
-
无用索引 -
重复索引 -
索引不是最优 -
对索引理解有误等。
-
如果理解MongoDB执行计划 -
如何确认查询索引是不是最优索引 -
云上用户对索引的一些错误创建方法 -
如何创建最优索引 -
创建最优索引的规则汇总
2 ►
MongoDB执行计划
2.1.queryPlanner信息
cmgo-xxxx:PRIMARY> db.test4.find({xxxx}).explain("queryPlanner") { "queryPlanner" : { "parsedQuery" : { ......；//查询条件对应的expression Tree }, "winningPlan" : { //查询优化器选择的最优索引及其该索引对应的执行阶段信息 ......; }, "rejectedPlans" : [ //查询优化器拒绝掉的非最优索引及其该索引对应的执行阶段信息 ......; ] }, ...... }-
parsedQuery信息
-
winningPlan信息
"winningPlan" : { "stage" : <STAGE1>, ... "inputStage" : { "stage" : <STAGE2>, ... "inputStage" : { "stage" : <STAGE3>, ... } } }|
|
|
|
|
|
-
rejectedPlans信息
2.2.executionStats信息
db.test4.find({xxxx}).explain("executionStats") "executionStats" : { "executionSuccess" : <boolean>, "nReturned" : <int>, "executionTimeMillis" : <int>, "totalKeysExamined" : <int>, "totalDocsExamined" : <int>, "executionStages" : { "stage" : <STAGE1> "nReturned" : <int>, "executionTimeMillisEstimate" : <int>, "works" : <int>, "advanced" : <int>, "needTime" : <int>, "needYield" : <int>, "saveState" : <int>, "restoreState" : <int>, "isEOF" : <boolean>, ... "inputStage" : { "stage" : <STAGE2>, "nReturned" : <int>, "executionTimeMillisEstimate" : <int>, ... "inputStage" : { ... } } }, ... }|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
executionStats输出字段较多，其他字段将在后续《MongoDB内核index索引模块实现原理》中进行进一步说明。
-
executionStats.totalKeysExamine远大于executionStats .nReturned
-
executionStats. totalDocsExamined远大于executionStats .nReturned
2.3.allPlansExecution信息
2.4.总结
-
queryPlanner
-
executionStats
-
allPlansExecution
3 ►
云上用户建索引 常见问题及优化方法
3.1.等值类查询常见索引错误创建方法及如何创建最优索引
3.1.1. 同一类查询创建多个索引问题
db.test4.find({"a":"xxx", "b":"xxx", "c":"xxx"}) db.test4.find({"b":"xxx", "a":"xxx", "c":"xxx"}) db.test4.find({"c":"xxx", "a":"xxx", "b":"xxx"}) MongoDB_4.4_shard2:PRIMARY> MongoDB_4.4_shard2:PRIMARY> db.test.find({"a" : 1, "b" : 1, "c" : 1}).explain("executionStats").queryPlanner.winningPlan { "stage" : "FETCH", "inputStage" : { "stage" : "IXSCAN", ...... "indexName" : "a_1_b_1_c_1", ...... } } MongoDB_4.4_shard2:PRIMARY> MongoDB_4.4_shard2:PRIMARY> db.test.find({"b" : 1, "a" : 1, "c" : 1}).explain("executionStats").queryPlanner.winningPlan { "stage" : "FETCH", "inputStage" : { "stage" : "IXSCAN", ...... "indexName" : "a_1_b_1_c_1", ...... } } MongoDB_4.4_shard2:PRIMARY> MongoDB_4.4_shard2:PRIMARY> db.test.find({"c" : 1, "a" : 1, "b" : 1}).explain("executionStats").queryPlanner.winningPlan { "stage" : "FETCH", "inputStage" : { "stage" : "IXSCAN", ...... "indexName" : "a_1_b_1_c_1", ...... } } MongoDB_4.4_shard2:PRIMARY> MongoDB_4.4_shard2:PRIMARY>3.1.2. 多字段等值查询组合索引顺序非最优
MongoDB_4.4_shard2:PRIMARY> db.test.find({"a" : 1, "b" : 1, "c" : 1}).hint({"a" : 1, b:1, c:1}).explain("executionStats").executionStats{"nReturned" : 1,"executionTimeMillis" : 0,"totalKeysExamined" : 1,"totalDocsExamined" : 1,......"executionStages" : {"stage" : "FETCH","nReturned" : 1,......"inputStage" : {"stage" : "IXSCAN",......"indexName" : "a_1_c_1_b_1",}}}MongoDB_4.4_shard2:PRIMARY>MongoDB_4.4_shard2:PRIMARY> db.test.find({"a" : 1, "b" : 1, "c" : 1}).hint({"a" : 1, c:1, b:1}).explain("executionStats").executionStats{"nReturned" : 1,"executionTimeMillis" : 0,"totalKeysExamined" : 1,"totalDocsExamined" : 1,"executionStages" : {"stage" : "FETCH","nReturned" : 1,......"inputStage" : {"stage" : "IXSCAN",......"indexName" : "a_1_c_1_b_1",}}}MongoDB_4.4_shard2:PRIMARY>MongoDB_4.4_shard2:PRIMARY> db.test.find({"c" : 1, "a" : 1, "b" : 1}).hint({"a" : 1, c:1, b:1}).explain("executionStats").executionStats{"nReturned" : 1,"executionTimeMillis" : 0,"totalKeysExamined" : 1,"totalDocsExamined" : 1,"executionStages" : {"stage" : "FETCH","nReturned" : 1,......"inputStage" : {"stage" : "IXSCAN",......"indexName" : "a_1_c_1_b_1",}}}
3.1.3. 最左原则包含关系引起的重复索引
db.test.find({"b" : 2, "c" : 1}) //查询1 db.test.find({"a" : 10, "b" : 5, "c" : 1}) //查询2MongoDB_4.4_shard2:PRIMARY> db.test.find({"b" : 2, "c" : 1}).explain("executionStats"){......"winningPlan" : {"stage" : "FETCH","inputStage" : {"stage" : "IXSCAN",......"indexName" : "c_1_b_1_a_1",......}}}MongoDB_4.4_shard2:PRIMARY>MongoDB_4.4_shard2:PRIMARY> db.test.find({"a" : 10, "b" : 5, "c" : 1}).explain("executionStats"){......"winningPlan" : {"stage" : "FETCH","inputStage" : {"stage" : "IXSCAN",......"indexName" : "c_1_b_1_a_1",......}}}
3.1.4. 唯一字段和其他字段组合引起的无用重复索引
db.test.find({a:1,b:1}) db.test.find({a:1,c:1})3.2.非等值类查询常见索引错误创建方法及如何创建最优索引
3.2.1. 非等值组合查询索引不合理创建
//两字段非等值查询 db.test.find({a:{$gte:1}, c:{$lte:1}}) MongoDB_4.4_shard1:PRIMARY> MongoDB_4.4_shard1:PRIMARY> db.test.find({a:{$gte:1}, c:{$lte:1}}).explain("executionStats") { "executionStats" : { "nReturned" : 4, "executionTimeMillis" : 0, "totalKeysExamined" : 10, "totalDocsExamined" : 4, "inputStage" : { ...... "indexName" : "a_1_c_1", } }3.2.2. 等值+非等值组合查询索引字段顺序不合理
//两字段非等值查询 db.test.find({"d":{$gte:4}, "e":1})MongoDB_4.4_shard1:PRIMARY>MongoDB_4.4_shard1:PRIMARY> db.test.find({ "d":{ $gte:4}, "e":1}).hint({d:1, e:1}).explain( "executionStats"){"executionStats" : {……"totalKeysExamined" : 5,"totalDocsExamined" : 3,......"inputStage" : {"stage" : "IXSCAN","indexName" : "d_1_e_1",......}}MongoDB_4.4_shard1:PRIMARY> db.test.find({ "d":{ $gte:4}, "e":1}).hint({e:1, d:1}).explain( "executionStats"){"executionStats" : {......"totalKeysExamined" : 3,"totalDocsExamined" : 3,......"inputStage" : {"indexName" : "e_1_d_1",......}
3.2.3. 不同类型非等值查询优先级问题
-
$In -
$gt $gte $lt $lte -
$nin -
$ne -
$type -
$exist
//等值+多个不同优先级非等值查询 db.test.find({"a":1, "b":1, "c":{$ne:5}, "e":{$type:"string"}, "f":{$gt:5},"g":{$in:[3,4]}) 查询13.3.OR类查询常见索引错误创建方法及如何创建最优索引
3.3.1. 普通OR类查询
//or中包含两个查询 db.test.find( { $or: [{ b: 0,d:0 }, {"c":1, "a":{$gte:4}} ] } )MongoDB_4 .4_shard1:PRIMARY> db.test.find( { $or: [{ b: 0,d: 0 }, { "c": 1, "a":{$gte: 4}}]}).hint({b: 1, d: 1, c: 1, a: 1}).explain( "executionStats"){"executionStats" : {......"totalKeysExamined" : 10,"totalDocsExamined" : 10,"inputStage" : {......"indexName" : "b_1_d_1_c_1_a_1",}}//创建{b:1,d:1}和{c:1, a:1}两个索引后，优化器选择这两个索引做为最优索引MongoDB_4 .4_shard1:PRIMARY>MongoDB_4 .4_shard1:PRIMARY> db.test.find( { $or: [{ b: 0,d: 0 }, { "c": 1, "a":{$gte: 4}}]}).explain( "executionStats"){"executionStats" : {......"totalKeysExamined" : 2,"totalDocsExamined" : 2,"executionStages" : {"stage" : "SUBPLAN",......"inputStage" : {"stage" : "OR","inputStages" : [{"stage" : "IXSCAN","indexName" : "b_1_d_1",......},{"stage" : "IXSCAN","indexName" : "c_1_a_1",......}]} }}},
3.3.2. 复杂OR类查询
//等值查询+or类查询+sort排序查询 db.test.find( {"f":3, g:2, $or: [{ b: 0, d:0 }, {"c":1, "a":6} ] } ) 查询1 ------db.test.find( {"f":3, g:2, b: 0, d:0 } ) //查询2 or--| ------db.test.find( {"f":3, g:2, "c":1, "a":6} ) //查询3MongoDB_4.4_shard1:PRIMARY> db.test.find( {"f":3, g:2, $or: [{ b: 0, d:0 }, {"c":1, "a":6} ] } ).explain("executionStats") { "executionStats" : { ...... "totalKeysExamined" : 7, "totalDocsExamined" : 7, "executionStages" : { "stage" : "FETCH", ...... "inputStage" : { "stage" : "OR", ...... "inputStages" : [ { "stage" : "IXSCAN", "indexName" : "f_1_g_1_c_1_a_1", ...... }, { "stage" : "IXSCAN", "indexName" : "f_1_g_1_b_1_d_1", } ] } } }, }说明：这个例子中可能在一些特殊数据分布场景，最优索引也可能是{f:1, g:1}或者{f:1, g:1, b:1, d:-1}或者{ f:1, g:1, c:1, a:1}，这里我们只考虑大部分通用场景。
3.4.Sort类排序查询常见索引错误创建方法及如何创建最优索引
3.4.1. 单字段正反序排序查询引起的重复索引
db.test.find({}).sort({a:1}).limit(2) db.test.find({}).sort({a:-1}).limit(2)MongoDB_4.4_shard1:PRIMARY>MongoDB_4.4_shard1:PRIMARY> db.test.find({}).sort({a:1}).limit(2).explain("executionStats"){......"winningPlan" : {"stage" : "LIMIT","limitAmount" : 2,"inputStage" : {......"indexName" : "a_1",}}},}MongoDB_4.4_shard1:PRIMARY>MongoDB_4.4_shard1:PRIMARY> db.test.find({}).sort({a:-1}).limit(2).explain("executionStats"){......"winningPlan" : {"stage" : "LIMIT","limitAmount" : 2,"inputStage" : {......"indexName" : "a_1",}}},},
3.4.2. 多字段排序查询正反序问题引起索引无效
//两字段排序查询 db.test.find().sort({a:1, b:-1}).limit(5)//{a:1, b:1}只会有一个字段走索引，另一个字段内存排序MongoDB_4.4_shard1:PRIMARY>MongoDB_4.4_shard1:PRIMARY> db.test.find().sort({a:1, b:-1}).hint({a:1, b:1}).explain("executionStats"){"executionStats" : {"totalKeysExamined" : 15,"totalDocsExamined" : 15,......"inputStage" : {"stage" : "FETCH",......"inputStage" : {"stage" : "SORT",......"inputStage" : {"stage" : "IXSCAN",......"indexName" : "a_1_b_1",}}}}},//{a:1, b:-1}两个字段走索引，不存在内存排序MongoDB_4.4_shard1:PRIMARY>MongoDB_4.4_shard1:PRIMARY> db.test.find().sort({a:1, b:-1}).hint({a:1, b:-1}).explain("executionStats"){"executionStats" : {"totalKeysExamined" : 15,"totalDocsExamined" : 15,"inputStage" : {"stage" : "FETCH",......"inputStage" : {"stage" : "IXSCAN",......"indexName" : "a_1_b_-1",}}}},}
3.4.3. 等值查询+多字段排序组合查询
//多字段等值查询+多字段排序查询 db.test.find({ "a" : 3, "b" : 1}).sort({c:-1, d:1}){ "a" : 3, "b" : 1}等值查询假设a区分度比b高，则对应最优索引为：{a：1, b：1}
{ c:-1, d:1}排序类查询最优索引保持正反序一致，也就是:{ c:-1, d:1}
//非最优索引执行计划，存在内存排序MongoDB_4.4_shard1: PRIMARY>MongoDB_4.4_shard1: PRIMARY> db.test. find({ "a" : 3, "b" : 1}). sort({ c:- 1, d: 1}).hint({a: 1, b: 1, c: 1, d: 1}).explain( "executionStats"){"executionStats" : {......"executionStages" : {"stage" : "FETCH",......"inputStage" : {"stage" : "SORT",......"inputStage" : {"stage" : "IXSCAN","indexName" : "a_1_b_1_c_1_d_1",......}}}},}//最优索引执行计划，直接走排序索引MongoDB_4.4_shard1: PRIMARY>MongoDB_4.4_shard1: PRIMARY> db.test. find({ "a" : 3, "b" : 1}). sort({ c:- 1, d: 1}).hint({a: 1, b: 1, c:- 1, d: 1}).explain( "executionStats"){"executionStats" : {......"executionStages" : {"stage" : "FETCH",......."inputStage" : {"stage" : "IXSCAN",......"indexName" : "a_1_b_1_c_-1_d_1",......}}},}
3.4.4. 等值查询+非等值查询+sort排序查询
//等值+非等值+sort排序查询 db.test.find({"a":3, "b":1, "c":{$gte:1}}).sort({d:-1, e:1})//走部分索引，然后内存排序MongoDB_4.4_shard1:PRIMARY> db.test.find({"a":3, "b":1, "c":{$gte:1}}).sort({d:-1, e:1}).hint({"a":1, b:1, c:1, d:-1, e:1}).explain("executionStats"){"executionStats" : {"totalKeysExamined" : 9,"totalDocsExamined" : 9,......"executionStages" : {"stage" : "FETCH",......"inputStage" : {"stage" : "SORT", //内存排序......"inputStage" : {"stage" : "IXSCAN",......"indexName" : "a_1_b_1_c_1_d_-1_e_1",}}}},}//直接走排序索引MongoDB_4.4_shard1:PRIMARY> db.test.find({"a":3, "b":1, "c":{$gte:1}}).sort({d:-1, e:1}).hint({"a":1, b:1, d:-1, e:1, c:1}).explain("executionStats"){"executionStats" : {"totalKeysExamined" : 10,"totalDocsExamined" : 9,......"executionStages" : {"stage" : "FETCH",......"inputStage" : {"stage" : "IXSCAN","indexName" : "a_1_b_1_d_-1_e_1_c_1",......}}},}
3.4.5. OR +SORT组合排序查询
//or+sort组合 查询1 db.test.find( { $or: [{ b: 0, d:0 }, {"c":1, "a":6} ] } ).sort({e:-1}) //查询1等价转换为如下查询 -----db.test.find({ b: 3, d:5 }).sort({e:-1}) //查询2 or--| -----db.test.find( {"c":1, "a":6} ).sort({e:-1}) //查询3//走{b:1, d:1, c:1, a:1, e:-1}索引，全表扫描加内存排序MongoDB_4.4_shard1: PRIMARY>MongoDB_4.4_shard1: PRIMARY> db.test. find( { $or: [{ b: 0, d: 0 }, { "c": 1, "a": 6} ] } ). sort({e:- 1}).hint({b: 1, d: 1, c: 1, a: 1, e:- 1}).explain( "executionStats"){"executionStats" : {......//测试构造表中23条数据，总数据23条"totalKeysExamined" : 23,"totalDocsExamined" : 23,"executionStages" : {"stage" : "SORT",......"inputStage" : {"stage" : "FETCH",......"inputStage" : {"stage" : "IXSCAN","indexName" : "b_1_d_1_c_1_a_1_e_-1",......}}}},}//走{b:1, d:1, e:-1}和{c:1, a:1, e:-1}两个最优索引的执行计划，无内存排序MongoDB_4.4_shard1: PRIMARY>MongoDB_4.4_shard1: PRIMARY> db.test. find( { $or: [{ b: 0, d: 0 }, { "c": 1, "a": 6} ] } ). sort({e:- 1}).explain( "executionStats"){"executionStats" : {......"totalKeysExamined" : 2,"totalDocsExamined" : 2,"inputStage" : {"stage" : "FETCH",......"inputStage" : {"stage" : "SORT_MERGE","inputStages" : [{"stage" : "IXSCAN","indexName" : "b_1_d_1_e_1",......},{"stage" : "IXSCAN","indexName" : "c_1_a_1_e_1",......}]}}}},}
//原查询 db.test.find( {"f":3, g:2, $or: [{ b: 0, d:0 }, {"c":1, "a":6} ] } ).sort({e:-1}) //查询1 //拆分后查询 ------ db.test.find( {"f":3, g:2, b: 0, d:0} ).sort({e:-1}) //查询2 or--- ------ db.test.find( {"f":3, g:2, "c":1, "a":6}).sort({e:-1}) //查询3
3.5.避免创建太多无用索引及无用索引分析方法
-
存储成本增加
-
影响写性能
-
影响读性能
db.xxx.aggregate({"$indexStats":{}}) { "alxxxId" : 1, "state" : -1, "updateTime" : -1, "itxxxId" : -1, "persxxal" : 1, "srcItxxxId" : -1 } "ops" : NumberLong(88518502) { "alxxxId" : 1, "image" : 1 } "ops" : NumberLong(293104) { "itexxxList.vidxxCheck" : 1, "itemType" : 1, "state" : 1 } "ops" : NumberLong(0) { "alxxxId" : 1, "state" : -1, "newsendTime" : -1, "itxxxId" : -1, "persxxal" : 1 } "ops" : NumberLong(33361216) { "_id" : 1 } "ops" : NumberLong(3987) { "alxxxId" : 1, "createTime" : 1, "checkStatus" : 1 } "ops" : NumberLong(20042796) { "alxxxId" : 1, "parentItxxxId" : -1, "state" : -1, "updateTime" : -1, "persxxal" : 1, "srcItxxxId" : -1 } "ops" : NumberLong(43042796) { "alxxxId" : 1, "state" : -1, "parentItxxxId" : 1, "updateTime" : -1, "persxxal" : -1 } "ops" : NumberLong(3042796) { "itxxxId" : -1} "ops" : NumberLong(38854593) { "srcItxxxId" : -1 } "ops" : NumberLong(0) { "createTime" : 1 } "ops" : NumberLong(62) { "itexxxList.boyunState" : -1, "itexxxList.wozhituUploadServerId" : -1, "itexxxList.photoQiniuUrl" : 1, "itexxxList.sourceType" : 1 } "ops" : NumberLong(0) { "alxxxId" : 1, "state" : 1, "digitalxxxrmarkId" : 1, "updateTime" : -1 } "ops" : NumberLong(140238342) { "itxxxId" : -1 } "ops" : NumberLong(38854593) { "alxxxId" : 1, "parentItxxxId" : 1, "parentAlxxxId" : 1, "state" : 1 } "ops" : NumberLong(132237254) { "alxxxId" : 1, "videoCover" : 1 } { "ops" : NumberLong(2921857) { "alxxxId" : 1, "itemType" : 1 } { "ops" : NumberLong(457) { "alxxxId" : 1, "state" : -1, "itemType" : 1, "persxxal" : 1, " itxxxId " : 1 } "ops" : NumberLong(68730734) { "alxxxId" : 1, "itxxxId" : 1 } "ops" : NumberLong(232360252) { "itxxxId" : 1, "alxxxId" : 1 } "ops" : NumberLong(145640252) { "alxxxId" : 1, "parentAlxxxId" : 1, "state" : 1 } "ops" : NumberLong(689891) { "alxxxId" : 1, "itemTagList" : 1 } "ops" : NumberLong(2898693682) { "itexxxList.photoQiniuUrl" : 1, "itexxxList.boyunState" : 1, "itexxxList.sourceType" : 1, "itexxxList.wozhituUploadServerId" : 1 } "ops" : NumberLong(511303207) { "alxxxId" : 1, "parentItxxxId" : 1, "state" : 1 } "ops" : NumberLong(0) { "alxxxId" : 1, "parentItxxxId" : 1, "updateTime" : 1 } "ops" : NumberLong(0) { "updateTime" : 1 } "ops" : NumberLong(1397) { "itemPhoxxIdList" : -1 } "ops" : NumberLong(0) { "alxxxId" : 1, "state" : -1, "isTop" : 1 } "ops" : NumberLong(213305) { "alxxxId" : 1, "state" : 1, "itemResxxxIdList" : 1, "updateTime" : 1 } "ops" : NumberLong(2591780) { "alxxxId" : 1, "state" : 1, "itexxxList.photoQiniuUrl" : 1} "ops" : NumberLong(23505) { "itexxxList.qiniuStatus" : 1, "itexxxList.photoNetUrl" : 1, "itexxxList.photoQiniuUrl" : 1 } "ops" : NumberLong(0) { "itemResxxxIdList" : 1 } "ops" :NumberLong(7) > db.xxxxx.aggregate({"$indexStats":{}}) { "name" : "alxxxId_1_parentItxxxId_1_parentAlxxxId_1", "key" : { "alxxxId" : 1, "parentItxxxId" : 1, "parentAlxxxId" : 1 }, "host" : "TENCENT64.site:7014", "accesses" : { "ops" : NumberLong(11236765), "since" : ISODate("2020-08-17T06:39:43.840Z") } }|
|
|
|
|
|
|
|
|
4 ►
MongoDB不同分类 查询最优 索引总结
|
|
|
|
|
|
|
|
|
|
| |
|
|
| |
|
|
| |
|
|
|
|
|
|
| |
|
|
|
|
|
|
| |
|
|
|
|
感谢大家一路来对 MongoDB 中文社区的关注与支持，为了让大家有更好的环境交流学习，诚意邀请大家“ 扫描二维码 ”填写问卷，你们的想法对我们非常重要！我们会在参加此次问卷中抽选20名认真填写的用户送上社区专属马克杯！！！ 快来参加吧！
长按二维码加入我们
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/69961190/viewspace-2886288/ ，如需转载，请注明出处，否则将追究法律责任。
上一篇：
MongoDB与亚马逊云科技扩大全球合作
请登录后发表评论
登录
全部评论
最新文章
- 活动报名| MongoDB 核心功能及其原理分析
- 活动报名| MongoDB 使用规范及最佳实践线上直播来啦！
- 线上活动| 如何运用 MongoDB 可调一致性？报名直播技术大佬带你了解！
- 活动报名 | MongoDB 5.0 时序存储特性介绍
- 直播分享| 腾讯云 MongoDB 智能诊断及性能优化实践
- MongoDB Page Fault 的发现与解决、腾讯云 MongoDB 智能诊断及优化实践
- 解决方案| MongoDB PSA 架构痛点以及如何应对？
- 行业案例 | MongoDB 在 QQ 小世界 Feed 云系统中的应用及业务架构优化实践
- 技术干货| MongoDB事务原理
- 行业案例| 千亿级高并发MongoDB集群在某头部金融机构中的应用及性能优化实践(上)
