背 景
最近研发提交业务需求,大概逻辑就是先统计总数,然后分页进行导出。SQL 查询条件很简单。根据时间范围以及 productTags 字段必须存在作为条件。目前每天大约 5000 万数据量,数据保留 6 个月满足条件数据不多。但在没有索引的情况下,前端导出是卡死的。本次只讨论count性能问题,分页导数同样需要优化。具体SQL如下:
db.xiaoxu20220704.count({ "productTags" : { "$exists" : true } ,"deliveryTime" : { "$gte" : { "$numberLong" : "1656864000000" } }, "$lt" : { "$numberLong" : "1657814400000" } } )
目前是基于 4.4 版本的分片集群。下班后创建索引语句跑起来,第二天上班创建成功,一共执行了 8 小时。通知研发可以进行验证。悲催的事情,执行 count 同样卡死。创建索引语句( 4.2 开始不区分前后台创建引),以下是分析过程。
db.xiaoxu20220704.createIndex({deliveryTime:1,productTags:1})
分析过程
分析执行计划
explain() 查看执行计划发现 "productTags" : { "$exists" : true } 没有用上索引,而是回表后进行过滤。
IXSCAN+FETCH 执行计划,而不是 COUNT_SCAN 执行计划。
explain(“executionStats”) 执行一个小时都没有出来,初步猜测在于 5000 万 fetch+filter 导致的慢。需要找研发了解数据情况。
db.xiaoxu20220704.explain().count({ "productTags" :{ "$exists" :
true } , "deliveryTime" : { "$gte" : NumberLong("1656864000000") ,
"$lt" : NumberLong("1657814400000") }} )
"winningPlan" : {
"stage" : "COUNT",
"inputStage" : {
"stage" : "FETCH",
"filter" : {
"productTags" : {
"$exists" : true
}
,
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"deliveryTime" : 1,
"productTags" : 1
},
"indexName" : "deliveryTime_1_productTags_1",
"isMultiKey" : true,
"multiKeyPaths" : {
"deliveryTime" : [ ],
"productTags" : [
"productTags"
]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"deliveryTime" : [
"[1656864000000, 1657814400000)"
],
"productTags" : [
"[MinKey, MaxKey]"
]
沟通业务逻辑
经了解,导数据通常是按天的,但也会存在按周、按月的需求,为什么会存在按周、按月,业务不想自己去合并表格,每天满足条件数据在 10万 左右,同时这个只有10万记录存在这个 productTags 字段,其他将近 5000 万都不存在这个字段。
有没有办法只把满足 "productTags" :{ "$exists" : true } 这个条件的记录索引?
如果能实现,这样查询每天的数据大约在 10 万次,此时如果 FETCH+FILTER 只有 10 万,相比之前 5000 万次,减少了 99.8% 次数。如果能实现查询覆盖,count 效率会更高。
MongoDB 中确实有这样功能,稀疏索引与部分索引都可以实现这个功能。部分索引功能是稀疏索引的超集同时提供更多的表达式,所以推荐使用部分索引。
优化索引--创建部分索引
db.xiaoxu20220704.createIndex({deliveryTime:1,productTags:1},{partialFilterExpression:{ "productTags" : { "$exists" : true }}})
partialFilterExpression: 支持如下表达式,$exists: true等价稀疏索引 (sparse:1)
-
equality expressions (i.e. field: value or using the $eq operator),
-
$exists: true expression,
-
$gt, $gte, $lt, $lte expressions,
-
$type expressions,
-
$and operator at the top-level only
查看最新执行计划
这个分片表执行计划只显示一个 shard,其他 shard 都类似,一共 8 个 shard 。总共加起来10万。执行计划本身没有改变,只是总的 totalKeysExamined 以及 totalDocsExamined 减少 99% ,所以速度很快。
为什么不能使用覆盖查询?
正常说只要统计出 deliveryTime 个数就知道知道总 count ,因为 productTags 都是满足 "$exists" : true 。
注意:
分片集合与非分片集合的查询覆盖有区别:分片集合想要使用覆盖查询必须包括分片键( readConcern 不是 avaiable 即可),使用非分片集合时,同样无法使用覆盖索引。在目前版本以及包括 5.0 版本使用 $exists:true 时都无法覆盖索引,部分索引能否使用覆盖查询,答案是肯定。
目前在不改代码逻辑的情况下&