MongoDB索引顺序导致慢SQL分析过程

背景

最近监控MongoDB集群的慢日志,发现存在一个查询需要4s左右，返回结果集大部分情况下都为0(相当于SQL空跑),与研发沟通交流后，这个定时将检查已审核账单数据推送到ES中(双11时直接关闭这个功能,说明这个功能消耗资源)

分析过程

1、业务逻辑

2、账单表

备注:账单结构

3、业务逻辑

账单表字段很少且结构很简单,Java程序多线程间隔拉取数据到ES集群,然后对拉取后的数据进行更新,现在拉取数据平均时间在4s-10s(小分片执行时间在4s,大的分片在10s左右).

4、具体SQL

备注:每次拉取最近一个月内已审核且rpts等于1的账单数据
db.fee_detail.find({ut:{ $gte: new Date(1595638774822) }, 
billSt: 1, rpts: 1 }).limit(5000)

5、更新逻辑

为了避免重复拉取数据,对已拉取的数据进行状态更新,根据no账单号,使用{ $unset: { billSt: "" }}将billSt字段删除.所以来创建稀疏索引来减少索引大小以及磁盘空间.

具体稀疏索引
db.fee_detail.createIndex({"ut" : 1, "billSt" : 1, "rpts" : 1},
{"sparse" : true})

虽然创建的稀疏索引,这个组合索引并不是真正的稀疏索引,根据稀疏索引定义来讲,稀疏索引中不包括不存在字段的文档,但是这个是组合索引，但ut日期字段一直都在.所以此稀疏索引中还是索引key对应文档信息，只是缺少billSt字段而已,所以说此组合是伪稀疏索引.从mongo 3.2开始推荐使用部分索引,因为部分索引提供稀疏索引的超集功能.此处应该创建部分索引能够更好实现稀疏索引功能且只保存条件索引key，从而实现之前创建稀疏的目的，能够降低索引大小以及内存使用。

应该部分索引(这个比稀疏索引更适合)
db.fee_detail.createIndex(
   { ut: 1},
   { partialFilterExpression: { billSt:1,rpts:1 } }
)

6、单个分片shard1慢日志

command xiaoxu.fee_detail command: find { find: "fee_detail", filter: { ut: { $gte: new Date(1595638774822) }, billSt: 1, rpts: 1 }, limit: 5000,

planSummary:IXSCAN { ut: 1, billSt: 1, rpts: 1 } keysExamined:2528071 docsExamined:0 nreturned:0 op

op_command 4183ms

7、执行计划

备注:只看单个分片执行即可,shard1检查索引key总数为2528071，同时seeks(如果是Oracle,则对应buffer gets很高)表示索引寻址次数此处为什么seeks次数与keysExamined相同,主要原因索引顺序是RE(索引顺序)导致性能问题(根据索引特性可以直接判断此索引是低效的)

低效表现:(keysExamined:2528071,nReturned:0)，接下来分析为什么这个所以性能低。

db.fee_detail.find({ut:{ $gte: new Date(1595638774822)}, "billSt":1, rpts:1}).
explain("executionStats") 
[总的执行情况]
"executionStats" : {
"nReturned" : 0,
"executionTimeMillis" : 6193,
"totalKeysExamined" : 17116357,
"totalDocsExamined" : 0,
"executionStages" : {
"stage" : "SHARD_MERGE",
"nReturned" : 0,
"executionTimeMillis" : 6193,
"totalKeysExamined" : 17116357,
"totalDocsExamined" : 0,
"shards" : [
{
 [单个分片执行情况]   
"shardName" : "opshard1",
"executionStages" : {
"stage" : "SHARDING_FILTER",
"nReturned" : 0,
"indexName" : "_ut_billSt_rpts_",
"isSparse" : true,
"keysExamined" : 2528071
"seeks" : 2528071 
}

8、分析ER与RE索引区别

备注:简化下查询范围来验证索引顺序为什么性能差,通过相关keysExamined、nReturned、seeks指标来验证性能.

1、RE索引性能

查询语句:
({ut:{ $gte: ISODate("2020-07-25T00:00:00.0000Z"),
$lt:ISODate("2020-07-25T01:00:00.000Z")},billSt:1,rpts:1})
索引:{"ut" : 1, "billSt" : 1, "rpts" : 1}

此时遍历是大于ut时间叶子都要遍历,每一个相同ut类似叶子遍历，遍历完成，去检索下一个ut,直到所有ut都都检索,只统计1小时区间，只画出2个日期，实际有792个ut不同值，相当于有792如下树结构.生产1个月则更多，1个分片就有280万不同值.需要寻址遍历280万.单次寻址时间大概1.42us(280万次差不多4s，单次已经很快，无奈基数太大)

2、ER索引性能

查询语句:{ut:{ $gte: new Date(1595638774822) }, billSt: 1, rpts: 1 }
索引:{"billSt":1,"rpts": 1, "ut" : 1}

因为此时是等值+等值+范围，直接seek一次即可查询满足数据（红色矩形）

因为此时可以直接通过索引一次性访问，类似一个叶子遍历，因为都是等值+等值+范围，直接在一个大的叶子(因为等值都是一样，一个叶子可能放不下，只是大概形式表示).

3、指标对比

4、如何计算seeks

如何大概估算seeks，可以计算组合索引前导列distinct值.主要针对范围，如果是等值，比如这个rpts等于就1个而已，所以seeks就是1就可以找到索引数据，而ut范围扫描，必须要扫描793不同key.为什么是793？因为统计1小时内，ut存在792不同值，这里多seeks 1次，因为是范围，需要检索下个值是否大于最大值.

db.fee_detail.aggregate([{$match:{ut:
{ $gte: ISODate("2020-07-25T00:00:00.0000Z"),
$lt:ISODate("2020-07-25T01:00:00.000Z")}}},
{$group:{_id:{ut:"$ut"},count:{"$sum":1}}},{$count:"ut"}]);
{ "ut" : 792 }

优化思路

通过慢日志以及执行计划来看，主要问题的症结在于索引的扫描范围过大，为什么过大，因为是大范围，如果想要优化，必须降低扫描索引的范围,如何降低，就是调整索引顺序来解决。此案例中索引扫描值达到1700万,返回记录为0.

高效索引为:totalKeysExamined<=nReturned(与oracle、mysql等关系型数据库理念类似)

根据查询语句以及索引来看，创建的索引是RE模式,而不是最佳ESR模式，R是range范围,查询语句：ut:{ $gte: new Date(1595638774822) }, billSt: 1, rpts: 1 }，由于索引特性，如果索引前导列是范围，后面等值列条件只能通过索引过滤.比回表过滤强，但是性能还是差.（只有第一列可以用上索引）,所以查询的范围越大,性能差异越大.最优方案重建索引.

这个查询相对很简单，无需修改应用代码的情况，新建索引即可.

• 针对稀疏,改成部分索引(与原来意图接近,降低索引大小)

db.fee_detail.createIndex(
   { ut: 1},
   { partialFilterExpression: { billSt:1,rpts:1 } }
)
改成如下:能更快速
db.fee_detail.createIndex(
   {billst:1,rpts:1,ut: 1},
   { partialFilterExpression: { billSt:1,rpts:1 } }
)

{

• 调整索引顺序(ER)

db.fee_detail.createIndex(
   {billSt:1,rpts:1 ut: 1}
) 
具体执行计划:
"executionStats" : {
"nReturned" : 0,
"executionTimeMillis" : 47,
"totalKeysExamined" : 0,
"totalDocsExamined" : 0,
"indexBounds" : {
"billSt" : ["[1.0, 1.0]"],
"rpts" : ["[1.0, 1.0]"],
"ut" : [
"[new Date(1595638774822), new Date(9223372036854775807)]"]
},
"keysExamined" : 0
"seeks" : 1

重建普通索引:返回0的执行时间为47ms且seeks为1.即一次性寻址后就检索完索引,符合预期行为.

总结

虽然本次优化很简单，主要存在问题:

第一对于稀疏索引的理解，如果单列稀疏索引的话，索引列被移除的，那么稀疏索引则不包括索引列对应的文档，符合稀疏索引的预期行为，如果是组合稀疏索引，只有所有列都不存在时才符合预期行为.

第二如果只是对满足条件记录进行索引且少量时（无其他不同查询），此时使用部分索引，部分索引是具有稀疏索引超级功能。

【稀疏索引】
db.contacts.createIndex(
   { name: 1 },
   {"sparse" : true}}
)
【部分索引可以替代稀疏索引功能】
db.contacts.createIndex(
   { name: 1 },
   { partialFilterExpression: { name: { $exists: true } } }
)

第三:创建组合索引需要遵循ER原则或者ESR原则来达到最佳效果，E对应查询等于，R是范围查询，S是排序操作,最常见是时间放在索引第一列，通常时间都范围，效果比较差，如本次案例就是如此.或者说第一列是范围查询，如果索引只有一列，范围查询是可以使用且无效率问题，主要针对组合索引时，前导列变成范围查询时+加上等值或者其他条件时，这个组合索引效率才有问题.