云上MongoDB常见索引问题及最优索引规则大全
一、背景
-
无用索引； -
重复索引； -
索引不是最优； -
对索引理解有误等。
-
如何理解MongoDB执行计划？ -
如何确认查询索引是不是最优索引？ -
云上用户对索引的一些错误创建方法； -
如何创建最优索引？ -
创建最优索引的规则汇总。
二、执行计划
cmgo-xxxx:PRIMARY> db.test4.find({xxxx}).explain("queryPlanner"){"queryPlanner" : {"parsedQuery" : {......；//查询条件对应的expression Tree},"winningPlan":{//查询优化器选择的最优索引及其该索引对应的执行阶段信息......;},"rejectedPlans":{//查询优化器拒绝掉的非最优索引及其该索引对应的执行阶段信息......;}},......}
parsedQuery信息
"winningPlan" : {"stage" : <STAGE1>,..."inputStage" : {"stage" : <STAGE2>,..."inputStage" : {"stage" : <STAGE3>,...}}},
|
|
|
|
|
|
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" : {...}}},...}
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
三、云上用户建索引常见问题及优化方法
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}) //查询1db.test.find({"a" : 10, "b" : 5, "c" : 1}) //查询2
MongoDB_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.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]}) 查询1
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排序查询//查询1db.test.find( {"f":3, g:2, $or: [{ b: 0, d:0 }, {"c":1, "a":6} ] } )
------db.test.find( {"f":3, g:2, b: 0, d:0 } ) //查询2or--|------db.test.find( {"f":3, g:2, "c":1, "a":6} ) //查询3
MongoDB_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",}]}}},}
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})
//非最优索引执行计划，存在内存排序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组合 查询1db.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}) //查询2or--|-----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}) //查询2or--｜------db.test.find( {"f":3, g:2, "c":1, "a":6}).sort({e:-1}) //查询3
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") } }
|
|
|
|
|
|
|
|
|
四、MongoDB不同类型查询最优索引总结
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
最后，本文中所介绍的优化原理即将集成到腾讯云DBbrain for MongoDB的智能索引推荐(规则+代价计算)功能中，届时可帮助用户一键优化索引，无需亲自反复推敲验证，欢迎体验。
关于作者——腾讯云MongoDB团队
﹀
﹀
﹀
腾讯云数据库核心集群索引优化实践
叮咚买菜自建MangoDB上腾讯云实践