本文干货较多，建议收藏学习。先将文章结构速览奉上：
一、背景
二、MongoDB执行计划
2.1 queryPlanner信息
2.2 executionStats信息
2.3 allPlansExecution信息
三、云上用户建索引常见问题及优化方法
3.1 等值类查询常见问题及优化方法
3.1.1 同一类查询创建多个索引问题
3.1.2 多字段等值查询组合索引顺序非最优
3.1.3 最左原则包含关系引起的重复索引
3.1.4 唯一字段和其他字段组合引起的无用重复索引
3.2 非等值类查询常见问题及优化方法
3.2.1 非等值组合查询索引不合理创建
3.2.2 等值 非等值组合查询索引字段顺序不合理
3.2.3 不同类型非等值查询优先级问题
3.3 OR类查询常见问题及优化方法
3.3.1 普通OR类查询优化方法
3.3.2 复杂OR类查询优化方法
3.4 SORT类排序查询常见问题及优化方法
3.4.1 单字段正反序排序查询引起的重复索引
3.4.2 多字段排序查询正反序问题引起索引无效
3.4.3 等值查询 多字段排序组合查询
3.4.4 等值查询 非等值查询 SORT排序查询
3.4.5 OR SORT组合排序查询
3.5 无用索引优化方法
四、MongoDB不同类型查询最优索引总结
一、背景
腾讯云MongoDB当前已服务于游戏、电商、社交、教育、新闻资讯、金融、物联网、软件服务、汽车出行、音视频等多个行业。
腾讯MongoDB团队在配合用户分析问题过程中，发现云上用户存在如下索引共性问题，主要集中在如下方面：
- 无用索引；
- 重复索引；
- 索引不是最优；
- 对索引理解有误等。
本文重点分析总结腾讯云上用户索引创建不合理相关的问题，通过本文可以学习到MongoDB的以下知识点：
- 如何理解MongoDB执行计划？
- 如何确认查询索引是不是最优索引？
- 云上用户对索引的一些错误创建方法；
- 如何创建最优索引？
- 创建最优索引的规则汇总。
本文总结的《最优索引规则创建大全》不仅仅适用于MongoDB，很多规则同样适用于MySQL等关系型数据库。
二、执行计划
判断索引选择及不同索引执行家伙信息可以通过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获取满足条件的docSHARD_MERGE：分片集群，如果mongos获取到多个分片的数据，则聚合操作在该阶段实现SHARDING_FILTER ：filter获取分片集群满足条件的docSORT：内存排序阶段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类似，只是多了所有候选索引的执行过程。
三、云上用户建索引常见问题及优化方法
在和用户一起优化腾讯云上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" : { "stage" : "IXSCAN", ...... "indexName" : "a_1_b_1_c_1", ...... } } MongoDB_4.4_shard2:PRIMARY> MongoDB_4.4_shard2:PRIMARY> 从上面的expalin输出可以看出，3个查询都走向了同一个索引。
3.1.2 多字段等值查询组合索引顺序非最优
例如test表有多条数据，每条数据有3个字段，分别为a、b、c。其中a字段有10种取值，b字段有100种取值，c字段有1000种取值，称为各个字段值的“区分度”。
用户查询条件为db.test.find({"a":"xxx", "b":"xxx", "c":"xxx"})，创建的索引为{a:1, b:1, c:1}。如果只是针对这个查询，该查询可以创建a，b，c三字段的任意组合，并且其SQL执行代价一样，通过hint强制走不通索引，验证过程如下：
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用户创建了如下两个索引：
{b:1, c:1}
{a:1,b:1,c:1}
这两个查询中，查询2中包含有查询1中的字段，因此可以用一个索引来满足这两个查询要求，按照最左原则，查询1字段放左边即可，该索引可以优化为：b,c字段索引 a字段索引，b,c字段顺序可以根据区分排序，加上c字段区分度比b高，则这两个查询可以合并为一个{c:1, b:1, a:1}。两个查询可以走同一个索引验证过程如下：
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}) 用户为这两个查询创建了两个索引，{a:1, b:1}和{a:1, c:1}，但是a字段取值是唯一的，因此这两个查询中a以外的字段无用，一个{a:1}索引即可满足要求。
3.2 非等值类查询常见索引错误创建方法及如何创建最优索引
3.2.1 非等值组合查询索引不合理创建
假设用户有如下查询：
//两字段非等值查询 db.test.find({a:{$gte:1}, c:{$lte:1}}) a,c两个字段都是非等值查询，很多用户直接添加了{a:1, c:1}索引，实际上多个字段的非等值查询，只有最左边的字段才能走索引，例如这里只会走a字段索引，验证过程如下：
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", } }从上面执行计划可以看出，索引数据扫描了10行(也就是a字段满足a:{$gte:1}条件的数据多少)，但是实际上只返回了4条满足{a:{$gte:1}, c:{$lte:1}}条件的数据，可以看出c字段无法做索引。
同理，当查询中包含多个字段的范围查询的适合，除了最左边第一个字段可以走索引，其他字段都无法走索引。因此，上面例子中的查询候选索引为{a:1}或者{b:1}中任何一个就可以了，组合索引中字段太多会占用更多存储成本、同时占用更多IO资源引起写放大。
3.2.2 等值 非等值组合查询索引字段顺序不合理
例如下面查询：
//两字段非等值查询 db.test.find({"d":{$gte:4}, "e":1}) 如上查询，d字段为非等值查询，e字段为等值查询，很多用户遇到该类查询直接创建了{d:1, e:1}索引，由于d字段为非等值查询，因此e字段无法走索引，验证过程如下：
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", ...... } 从上面验证过程可以看出，等值类和非等值类组合查询对应组合索引，最优索引应该优先把等值查询放到左边，上面查询对应最优索引{e:1, d:1}
3.2.3 不同类型非等值查询优先级问题
前面用到的非等值查询操作符只提到了比较类操作符，实际上非等值查询还有其他操作符。常用非等值查询包括：$gt、$gte、$lt、$lte、$in、$nin、$ne、$exists、$type等，这些非等值查询在绝大部分情况下存在如下优先级：
- $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如上，该查询等值部分查询最优索引{a:1, b:1}(假设a区分度比b高)；非等值部分，因为$in操作符优先级最高，排他性更好，加上多个字段非等值查询只会有一个字段走索引，因此非等值部分最优索引为{g:1}。
最终该查询最优索引为：”等值部分最优索引”与”非等值部分最优索引”拼接，也就是{a:1,b:1, g:1}
3.3 OR类查询常见索引错误创建方法及如何创建最优索引
3.3.1 普通OR类查询
例如下面的OR查询：
//or中包含两个查询 db.test.find( { $or: [{ b: 0, d:0 }, {"c":1, "a":{$gte:4}} ] } )该查询很多用户直接创建了{b:1, d:1, c:1, a:1}，用户创建该索引后，发现用户还是全表扫描。
OR类查询需要给数组中每个查询添加索引，例如上面or数组中实际包含{ b: 0, d:0 }和 {"c":1, "a":{$gte:4}}查询，需要创建两个查询的最优索引，也就是{b:1, d:1}和{c:1, a:1}，执行计划验证过程如下(该测试表总共10条数据)：
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", ...... } ] } } } }, 从上面执行计划可以看出，如果该OR类查询走{b:1, d:1, c:1, a:1}索引，则实际上做了全表扫描。如果同时创建{b:1, d:1}、{c:1, a:1}索引，则直接走两个索引，其执行key和doc扫描行数远远小于全表扫描。
3.3.2 复杂OR类查询
这里在提升一下OR查询难度，例如下面的查询：
//等值查询 or类查询 sort排序查询//查询1 db.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如上图，查询1拆分后的两个查询2和查询3组成or关系，因此对应最优索引需要创建两个，分表是：{f:1, g:1, b:1, d:1} 和 {f:1, g:1, b:1, d:1}。对应执行计划如下：
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", } ] } } }, } 同理，不管怎么增加难度，OR查询最终可转换为多个等值、非等值或者等值与非等值组合类查询，通过如上变换最终可以起到举一反三的作用。
说明：这个例子中可能在一些特殊数据分布场景，最优索引也可能是{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) 这两个查询都不带条件，排序方式不一样，因此很多创建了两个索引{a:1}和{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", } } }, }
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字段为正序，b字段为反序排序，很多用户直接创建{a:1, b:1}索引，这时候b字段内容就存在内存排序情况。多字段排序索引，如果没有携带查询条件，则最优索引即为排序字段对应索引，这里切记保持每个字段得正反序和sort完全一致，否则可能存在部分字段内存排序的情况，执行计划验证过程如下：
//{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:1, b:1, c:1, d:1}，结果造成内存排序。这种组合查询最优索引=“多字段等值查询最优索引_多字段排序类组合最优索引”，例如该查询：
{ "a" : 3, "b" : 1}等值查询假设a区分度比b高，则对应最优索引为：{a：1, b：1}
{ c:-1, d:1}排序类查询最优索引保持正反序一致，也就是:{ c:-1, d:1}
因此整个查询就是这两个查询对应最优索引拼接，也就是{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" : "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}) 腾讯云很多用户看到该查询直接创建{a:1, b:1, c:1, d:-1, e:1}索引，发现存在内存排序。等值 非等值 sort排序组合查询，由于非等值查询右边的字段不能走索引，因此如果把d, e放到c的右边，则d，e字段索引无效。
等值 非等值 sort排序最优索引组合字段顺序为：等值_sort排序_非等值，因此上面查询最优索引为:{a:1, b:1, d:-1, e:1, c: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}) 上面组合很多人直接创建{b:1, d:1, c:1, a:1, e:1}，该索引创建后还是会扫表和内存排序，实际上OR SORT组合查询可以转换为下面两个查询：
//查询1等价转换为如下查询 -----db.test.find({ b: 3, d:5 }).sort({e:-1}) //查询2 or--| -----db.test.find( {"c":1, "a":6} ).sort({e:-1}) //查询3 所以这个复杂查询就可以拆分为等值组合查询 sort排序查询，拆分为上面的两个查询，这样我们只需要同时创建查询2和查询3对应最优索引即可。该查询最终拆分后对应最优索引需要添加如下两个：
{b:1, d:1, e:-1}和{c:1, a:1, e:-1}
非最优索引和最优索引执行计划验证过程如下：
//走{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", ...... } ] } } } }, } OR SORT类查询，最终可以《参考前面的OR类查询常见索引错误创建方法》把OR查询转换为多个等值、非等值或者等值与非等值组合查询，然后与sort排序对应索引字段拼接。例如下面查询：
//原查询 db.test.find( {"f":3, g:2, $or: [{ b: 0, d:0 }, {"c":1, "a":6} ] } ).sort({e:-1}) //查询1 拆分后的两个查询组成or关系，如下：
//拆分后查询 ------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如上，查询1 = or: [查询2， 查询3]，因此只需要创建查询2和查询3两个最优索引即可满足查询1要求，查询2和查询3最优索引可以参考前面《or类查询常见索引错误创建方法》，该查询最终需要创建如下两个索引：
{f:1, g:1, b:1, d:1, e:-1}和{ f:1, g:1, c:1, a:1, e:-1}
说明：这个例子中可能在一些特殊数据分布场景，最优索引也可能是{f:1, g:1}或者{f:1, g:1, b:1, d:1, e:-1}或者{ f:1, g:1, c:1, a:1, e:-1}，这里我们只考虑通用场景。
3.5 避免创建太多无用索引及无用索引分析方法
在腾讯云上，我们还发现另外一个问题，很多实例存在大量无用索引，无用索引会引起以下问题：
存储成本增加
没增加一个索引，MongoDB内核就会创建一个index索引文件，记录该表的索引数据，造成存储成本增加。
影响写性能
用户没写入一条数据，就会在对应索引生成一条索引KV，实现索引与数据的一一对应，索引KV数据写入Index索引文件过程加剧写入负载。
影响读性能
MongoDB内核查询优化器原理是通过候选索引快速定位到满足条件的数据，然后采样评分。如果满足条件的候选索引越多，整个评分过程就会越长，增加内核选择最优索引的流程。
下面以一个真实线上实例为例，说明如何找出无用索引：
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) MongoDB默认提供有索引统计命令来获取各个索引命中的次数，该命令如下：
> 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") } } 该聚合输出中的几个核心指标信息如下表：
字段内容 | 说明 |
|---|---|
name | 索引名，代表是针对那个索引的统计。 |
ops | 索引命中次数，也就是所有查询中采用本索引作为查询索引的次数。 |
上表中的ops代表命中次数，如果命中次数为0或者很小，说明该索引很少被选为最优索引使用，因此可以认为是无用索引，可以考虑删除。
四、MongoDB不同类型查询最优索引总结
查询大类 | 子类 | 生成候选索引规则 |
|---|---|---|
普通查询 | 单字段查询 | 无需计算，直接输出索引 |
多字段等值查询 | 分析字段schema，得出区分度如果某字段区分度和采样数据条数一致，则直接添加该字段的索引即可，无需多字段组合，流程结束。给出候选索引，按照区分度从左向右生成组合索引。多字段等值查询，只会有一个候选索引 说明：本身多字段等值查询，最优索引和字段组合顺序无关，但是这里一般有个不成文归档，把区分度最高的字段放在最左边，这样有利于带有该字段新查询的快速排他性 | |
多字段非等值查询 | 非等值查询，通过优先级确定候选索引，非等值操作符优先级顺序如下：$In$gt $gte $lt $lte$nin$ne$type$exist 如果字段优先级一样，则会对应多个候选索引，例如{a>1, b>1,c >1}查询，候选索引是以下3个中的一个：{a:1}{b:1}{c: 1}这时候就需要根据数据分布评估3个候选索引中那个更好。 | |
等值与非等值组合 | 等值与非等值组合，候选索引规则步骤如下：等值按照schema区分度，获取所有等值字段的候选索引，只会有一个候选索引等值部分与所有非等值字段组合为候选索引，最终有多少个非等值查询，就会有多少个候选索引 举例：db.collection.find(a=1, b=2, c>3, d>4)假设(a=1, b=2)等值查询按照区分度最优索引为{b:1,a:1}，则候选索引有如下两种：{b:1，a:1，c:1}{b:1，a:1，d:1} 这时候就需要根据数据分布情况决定加这两个候选索引的哪一个作为最优索引。 | |
排序类型 | 不带查询的排序 | 不带查询条件的排序，例如db.xx.find({}).sort({a:1,b:-1,c:1})，对应候选索引直接是排序索引：{a:1,b:-1,c:1} |
普通查询 sort排序 | 该场景候选索引包括：等值查询候选索引Sort排序候选索引 举例：db.collection.find(a=1, b=2, c>3, d>4).sort({e:1, f:-1})，该查询候选索引：等值查询候选索引{b:1，a:1}{a:1，b:1}非等值部分候选索引{c:1}{d:1}Sort候选索引{ e:1, f:-1}假设等值部分按照区分度最优索引为{a:1, b:1},非等值最优索引为{d:1}，则整个查询最优索引=等值部分最优索引_sort排序最优索引_非等值部分最优索引，也就是{a:1,b:1,e:1,f:-1d:1} | |
OR类查询(可拆分为多个普通查询) | 一个子tree | 候选索引就是该子tree对应候选索引，参考《普通查询》对应候选索引推荐算法 |
多个子tree(无交集字段) | 对每个tree对应普通查询生成一个最优索引，多个子tree会有多个候选索引，每个tree对应候选索引规则参考《普通查询》 | |
更多查询汇总信息 | 参考第三章 | 参考第三章 |
- 分析字段schema，得出区分度
- 如果某字段区分度和采样数据条数一致，则直接添加该字段的索引即可，无需多字段组合，流程结束。
- 给出候选索引，按照区分度从左向右生成组合索引。
- 多字段等值查询，只会有一个候选索引
说明：本身多字段等值查询，最优索引和字段组合顺序无关，但是这里一般有个不成文归档，把区分度最高的字段放在最左边，这样有利于带有该字段新查询的快速排他性多字段非等值查询非等值查询，通过优先级确定候选索引，非等值操作符优先级顺序如下：
- $In
- $gt $gte $lt $lte
- $nin
- $ne
- $type
- $exist
如果字段优先级一样，则会对应多个候选索引，例如{a>1, b>1,c >1}查询，候选索引是以下3个中的一个：
- {a:1}
- {b:1}
- {c: 1}
这时候就需要根据数据分布评估3个候选索引中那个更好。等值与非等值组合等值与非等值组合，候选索引规则步骤如下：
- 等值按照schema区分度，获取所有等值字段的候选索引，只会有一个候选索引
- 等值部分与所有非等值字段组合为候选索引，最终有多少个非等值查询，就会有多少个候选索引
举例： db.collection.find(a=1, b=2, c>3, d>4) 假设(a=1, b=2)等值查询按照区分度最优索引为{b:1,a:1}，则候选索引有如下两种： {b:1，a:1，c:1} {b:1，a:1，d:1} 这时候就需要根据数据分布情况决定加这两个候选索引的哪一个作为最优索引。 排序类型不带查询的排序不带查询条件的排序， 例如db.xx.find({}).sort({a:1,b:-1,c:1})，对应候选索引直接是排序索引： {a:1,b:-1,c:1}普通查询 sort排序该场景候选索引包括：
- 等值查询候选索引
- Sort排序候选索引
举例： db.collection.find(a=1, b=2, c>3, d>4).sort({e:1, f:-1})，该查询候选索引：
- 等值查询候选索引
{b:1，a:1} {a:1，b:1}
- 非等值部分候选索引
{c:1} {d:1}
- Sort候选索引
{ e:1, f:-1} 假设等值部分按照区分度最优索引为{a:1, b:1},非等值最优索引为{d:1}，则整个查询最优索引=等值部分最优索引_sort排序最优索引_非等值部分最优索引，也就是{a:1,b:1,e:1,f:-1d:1}OR类查询 (可拆分为多个普通查询)一个子tree候选索引就是该子tree对应候选索引，参考《普通查询》对应候选索引推荐算法多个子tree (无交集字段)对每个tree对应普通查询生成一个最优索引，多个子tree会有多个候选索引，每个tree对应候选索引规则参考《普通查询》更多查询汇总信息参考第三章参考第三章
说明：
本文总结的《最优索引规则大全》中的规则适用于绝大部分查询场景，但是一些特殊数据分布场景可能会有一定偏差，请根据实际数据分布进行查询计划分析。
DBbrain for MongoDB
最后，本文中所介绍的优化原理即将集成到腾讯云DBbrain for MongoDB的智能索引推荐(规则 代价计算)功能中，届时可帮助用户一键优化索引，无需亲自反复推敲验证，欢迎体验。
关于作者——腾讯云MongoDB团队
腾讯云MongoDB当前服务于游戏、电商、社交、教育、新闻资讯、金融、物联网、软件服务等多个行业；MongoDB团队(简称CMongo)致力于对开源MongoDB内核进行深度研究及持续性优化(如百万库表、物理备份、免密、审计等)，为用户提供高性能、低成本、高可用性的安全数据库存储服务。后续持续分享MongoDB在腾讯内部及外部的典型应用场景、踩坑案例、性能优化、内核模块化分析。
﹀
﹀
﹀
-- 更多精彩 --
腾讯云数据库核心集群索引优化实践
金融行业核心系统如何进行分布式改造?
叮咚买菜自建MangoDB上腾讯云实践
↓↓点击阅读原文，了解更多优惠
