腾讯云 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 输出主要包括如下信息：
内核对查询条件进行序列化，生成一棵 expression tree 信息，便于候选索引查询匹配。
"winningPlan" : { "stage" : <STAGE1>, ... "inputStage" : { "stage" : <STAGE2>, ... "inputStage" : { "stage" : <STAGE3>, ... } } }
复制代码
winningPlan 提供查询优化器选出的最优索引及其查询通过该索引的执行阶段信息，子 stage 传递该节点获取的文档或者索引信息给父 stage，其输出项中几个重点字段需要关注：
输出信息和 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 获取执行过程的详细信息，其中字段信息较多，平时分析索引问题最常用的几个字段如下：
executionStats 输出字段较多，其他字段将在后续《MongoDB 内核 index 索引模块实现原理》中进行进一步说明。
在实际分析索引问题是否最优的时候，主要查看 executionStats.totalKeysExamined、
executionStats.totalDocsExamined、executionStats .nReturned 三个统计项，如果存在以下情况则说明索引存在问题，可能索引不是最优的：
executionStats.totalKeysExamine 远大于 executionStats .nReturned
executionStats. totalDocsExamined 远大于 executionStats .nReturned
2.3.allPlansExecution 信息
allPlansExecution 参数对应输出信息和 executionStats 输出信息类似，只是多了所有候选索引(包括 reject 拒绝的非最优索引)的执行过程，这里不在详述。
2.4.总结
从上面的几个 explain 执行计划参数输出信息可以看出，各个参数功能各不相同，总结如下：
输出索引的候选索引，包括最优索引及其执行 stage 过程(winningPlan)+其他非最优候选索引及其执行 stage 过程。
注意：queryPlanner 没有真正在表中执行整个 SQL，只做了查询优化器获取候选索引过程，因此可以很快返回。
相比 queryPlanner 参数，executionStats 会记录查询优化器根据所选最优索引执行 SQL 的整个过程信息，会真正执行整个 SQL。
和 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" : { "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}) //查询1 db.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排序查询 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} ) //查询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组合 查询1 db.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}) //查询2 or--- ------ 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") } }
复制代码
该聚合输出中的几个核心指标信息如下表：
上表中的 ops 代表命中次数，如果命中次数为 0 或者很小，说明该索引很少被选为最优索引使用，因此可以认为是无用索引，可以考虑删除。
4►MongoDB 不同分类查询最优索引总结
说明：
本文总结的《最优索引规则大全》中的规则适用于绝大部分查询场景，但是一些特殊数据分布场景可能会有一定偏差，请根据实际数据分布进行查询计划分析。
有奖调研►
感谢大家一路来对 MongoDB 中文社区的关注与支持，为了让大家有更好的环境交流学习，诚意邀请大家“扫描二维码”填写问卷，你们的想法对我们非常重要！我们会在参加此次问卷中抽选 20 名认真填写的用户送上社区专属马克杯！！！快来参加吧！
添加微信助手小芒果
（mongoingcom）
并进入技术交流群
长按二维码加入我们
评论