共计 20232 个字符，预计需要花费 51 分钟才能阅读完成。
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/manu…
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 三个统计项，如果存在以下状况则阐明索引存在问题，可能索引不是最优的：
executionStats.totalKeysExamine 远大于 executionStats .nReturned
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" : {
"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”) } }
该聚合输入中的几个外围指标信息如下表：
字段内容
阐明
name
索引名，代表是针对那个索引的统计。
ops
索引命中次数，也就是所有查问中采纳本索引作为查问索引的次数。
上表中的 ops 代表命中次数，如果命中次数为 0 或者很小，阐明该索引很少被选为最优索引应用，因而能够认为是无用索引，能够思考删除。
4►
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 对应候选索引规定参考《一般查问》
更多查问汇总信息
参考第三章
参考第三章
阐明：
本文总结的《最优索引规定大全》中的规定实用于绝大部分查问场景，然而一些非凡数据分布场景可能会有肯定偏差，请依据理论数据分布进行查问打算剖析。
有奖调研►
感激大家一路来对 MongoDB 中文社区的关注与反对，为了让大家有更好的环境交流学习，诚意邀请大家“扫描二维码”填写问卷，你们的想法对咱们十分重要！咱们会在加入此次问卷中抽选 20 名认真填写的用户送上社区专属马克杯！！！快来加入吧！
