云上MongoDB常见索引问题及最优索引规则大全

1►背景

腾讯云MongoDB当前已服务于游戏、电商、社交、教育、新闻资讯、金融、物联网、软件服务、汽车出行、音视频等多个行业。

腾讯MongoDB团队在配合用户分析问题过程中，发现云上用户存在如下索引共性问题，主要集中在如下方面：

• 无用索引

• 重复索引

• 索引不是最优

• 对索引理解有误等。

本文重点分析总结腾讯云上用户索引创建不合理相关的问题，通过本文可以学习到MongoDB的以下知识点：

• 如果理解MongoDB执行计划

• 如何确认查询索引是不是最优索引

• 云上用户对索引的一些错误创建方法

• 如何创建最优索引

• 创建最优索引的规则汇总

本文总结的《最优索引规则创建大全》不仅仅适用于MongoDB，很多规则同样适用于MySQL等关系型数据库。

2►MongoDB执行计划

判断索引选择及不同索引执行家伙信息可以通过explain操作获取，MongoDB通过explain来获取SQL执行过程信息，当前持续explain的请求命令包含以下几种：

aggregate, count, distinct, find, findAndModify, delete, mapReduce,and update。

详见explain官网连接：

https://docs.MongoDB.com/manual/reference/command/explain/

explain可以携带以下几个参数信息，各参数信息功能如下：

2.1.queryPlanner信息

获取MongoDB查询优化器选择的最优索引和拒绝掉的非最优索引，并给出各个候选索引的执行阶段信息，queryPlanner输出信息如下：

 cmgo-xxxx:PRIMARY> db.test4.find({xxxx}).explain("queryPlanner")   {            "queryPlanner" : {                    "parsedQuery" : {                            ......；//查询条件对应的expression Tree                   },                    "winningPlan" : {                             //查询优化器选择的最优索引及其该索引对应的执行阶段信息                         ......;                   },                  "rejectedPlans" : [                           //查询优化器拒绝掉的非最优索引及其该索引对应的执行阶段信息                       ......;                    ]          },          ......  }

queryPlanner输出主要包括如下信息：

• parsedQuery信息

内核对查询条件进行序列化，生成一棵expression tree信息，便于候选索引查询匹配。

• winningPlan信息

"winningPlan" : {    "stage" : <STAGE1>,     ...     "inputStage" : {        "stage" : <STAGE2>,       ...        "inputStage" : {           "stage" : <STAGE3>,           ...        }     }  }

winningPlan提供查询优化器选出的最优索引及其查询通过该索引的执行阶段信息，子stage传递该节点获取的文档或者索引信息给父stage，其输出项中几个重点字段需要关注：

字段名	功能说明
stage	表示SQL运行所处阶段信息，根据不同SQL及其不同候选索引，stage不同，常用stage字段包括以下几种： COLLSCAN：该阶段为扫表操作 IXSCAN：索引扫描阶段，表示查询走了该索引 FETCH：filter获取满足条件的doc SHARD_MERGE：分片集群，如果mongos获取到多个分片的数据，则聚合操作在该阶段实现 SHARDING_FILTER ：filter获取分片集群满足条件的doc SORT：内存排序阶段 OR：$orexpression类查询对应stage ……

• rejectedPlans信息

输出信息和winningPlan类似，记录这些拒绝掉索引的执行stage信息。

2.2.executionStats信息

explain的executionStats参数除了提供上面的queryPlanner信息外，还提供了最优索引的执行过程信息，如下：​​​​​​​

 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获取执行过程的详细信息，其中字段信息较多，平时分析索引问题最常用的几个字段如下：

字段名	功能说明
Stage	Stage字段和queryPlanner信息中stage意思一致，用户表示执行计划的阶段信息
nReturned	本stage满足查询条件的数据索引数据或者doc数据条数
executionTimeMillis	整个查询执行时间
totalKeysExamined	索引key扫描行数
totalDocsExamined	Doc扫描行数
executionTimeMillisEstimate	本stage阶段执行时间

executionStats输出字段较多，其他字段将在后续《MongoDB内核index索引模块实现原理》中进行进一步说明。

在实际分析索引问题是否最优的时候，主要查看executionStats.totalKeysExamined、

executionStats.totalDocsExamined、executionStats .nReturned三个统计项，如果存在以下情况则说明索引存在问题，可能索引不是最优的：

1. executionStats.totalKeysExamine远大于executionStats .nReturned

2. executionStats. totalDocsExamined远大于executionStats .nReturned

2.3.allPlansExecution信息

allPlansExecution参数对应输出信息和executionStats输出信息类似，只是多了所有候选索引(包括reject拒绝的非最优索引)的执行过程，这里不在详述。

2.4.总结

从上面的几个explain执行计划参数输出信息可以看出，各个参数功能各不相同，总结如下：

• queryPlanner

输出索引的候选索引，包括最优索引及其执行stage过程(winningPlan)+其他非最优候选索引及其执行stage过程。

注意：queryPlanner没有真正在表中执行整个SQL，只做了查询优化器获取候选索引过程，因此可以很快返回。

• executionStats

相比queryPlanner参数，executionStats会记录查询优化器根据所选最优索引执行SQL的整个过程信息，会真正执行整个SQL。

• allPlansExecution

和executionStats类似，只是多了所有候选索引的执行过程。

3►云上用户建索引常见问题及优化方法

在和用户一起优化腾讯云上MongoDB集群索引过程中，通过和头部用户的交流过程中，发现很多用户对如何创建最优索引有较验证的错误认识，并且很多是大部分用户的共性问题，这些问题总结汇总如下：

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"})

用户创建了如下3个索引：

{a:1, b:1, c:1}

{b:1, a:1, c:1}

{c:1, a:1, b:1}

实际上这3个查询属于同一类查询，只是查询字段顺序不一样，因此只需创建任一个索引即可满足要求。验证过程如下：​​​​​​​

 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&