如何加快MongoDB视图上的计数 [英] How to Speed Up Count on MongoDB View
问题描述
我一直在解决为什么我创建的MongoDB视图这么慢的问题.该视图以transactions集合为目标，并返回具有openBalance大于0的记录.我还运行了一些附加的聚合阶段，以按所需方式对数据进行整形.
I have been troubleshooting why a MongoDB view I created is so slow. The view targets the transactions collection, and returns records that have an openBalance that is greater than 0. I also run some additional aggregation stages to shape the data the way I want it.
为了加快视图的执行速度，它通过在视图的聚合管道的第一阶段中与索引字段进行匹配来利用目标集合上的索引，如下所示:
In order to speed up the execution of the view it makes use of an index on the targeted collection by matching on the indexed field in stage one of the view's aggregation pipeline, like so:
// View Stage 1
{ "transactions.details.openBalance" : { "$exists" : true, "$gt" : 0.0 } }
经过大量研究，我确定视图中的聚合非常快地返回了数据.缓慢的是作为端点一部分运行的计数:
After much investigation I have determined that the aggregation from the view returns data very quickly. What's slow is the count that's run as part of the endpoint:
let count = await db.collection('view_transactions_report').find().count();
所以我现在想弄清楚的是为什么视图上的计数比基础集合上的计数慢得多，并且我可以做些什么来加快计数的速度.或者，也许还有另一种方法来生成计数?
So what I'm trying to figure out now is why the count is so much slower on the view than on the underlying collection, and what I can do to speed it up. Or, perhaps there's an alternative way to generate the count?
基础集合有大约800,000条记录，但是计数很快返回.但是视图的计数返回的速度要慢得多，该视图仅返回最初的800,000条记录中的10,000个过滤后的集合.在细节方面，我说的是要返回集合计数的3/4秒，相对于要在mongo视图上返回计数的计数是6秒.
The underlying collection has something like 800,000 records, but the count returns quickly. But the count on the view, which only returns a filtered set of 10,000 of those initial 800,000 records, returns much more slowly. In terms of specifics, I'm talking about 3/4 of a second for the count on the collection to return, verses six seconds for the count on the mongo view to return.
因此，首先，为什么视图的计数(数据集要小得多)要比基础集合慢得多，其次，我该如何处理视图的计数速度?
So, first off, why is the count so much slower on the view (with it's much smaller data set) than on the underlying collection, and secondly, what can I do to address the speed of the count for the view?
我正在运行几个其他的聚合查询来确定totalCustomers和totalOpenBalance，它们似乎也运行缓慢(请参见下面的代码).
I have a couple other aggregation queries I'm running, to determine totalCustomers and totalOpenBalance, that also seem to run slow (see code below).
我的端点功能代码的相关部分如下:
The relevant part of my endpoint function code looks like this:
// previous code
let count = await db.collection('view_transaction_report').find(search).count();
let totalCustomers = await db.collection('view_transaction_report').find(search).count({
$sum: "customer._id"
});
let result = {};
if (totalCustomers > 0) {
result = await db.collection('view_transaction_report').aggregate([{
$match: search,
},
{
$group: {
_id: null,
totalOpenBalance: {
$sum: '$lastTransaction.details.openBalance'
}
}
}
]).next();
}
db.collection('view_transaction_report').find(search).skip(skip).limit(pagesize).forEach(function (doc) {
docs.push(doc);
}, function (err) {
if (err) {
if (!ioOnly) {
return next(err);
} else {
return res(err);
}
}
if (ioOnly) {
res({
sessionId: sessID,
count: count,
data: docs,
totalCustomers: totalCustomers,
totalOpenBalance: result.totalOpenBalance
});
} else {
res.send({
count: count,
data: docs,
totalCustomers: totalCustomers,
totalOpenBalance: result.totalOpenBalance
});
}
});
对于executionStats，这是生成的视图的queryPlanner部分显示的内容:
In terms of executionStats, this is what shows for the queryPlanner section of the generated view:
"queryPlanner" : {
"plannerVersion" : 1.0,
"namespace" : "vio.transactions",
"indexFilterSet" : false,
"parsedQuery" : {
"$and" : [
{
"transactions.details.openBalance" : {
"$gt" : 0.0
}
},
{
"transactions.destails.openBalance" : {
"$exists" : true
}
}
]
},
"winningPlan" : {
"stage" : "CACHED_PLAN",
"inputStage" : {
"stage" : "FETCH",
"filter" : {
"transactions.details.openBalance" : {
"$exists" : true
}
},
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"transactions.details.openBalance" : 1.0
},
"indexName" : "openBalance",
"isMultiKey" : true,
"multiKeyPaths" : {
"transactions.details.openBalance" : [
"transactions",
"transactions.details"
]
},
"isUnique" : false,
"isSparse" : true,
"isPartial" : false,
"indexVersion" : 2.0,
"direction" : "forward",
"indexBounds" : {
"transactions.details.openBalance" : [
"(0.0, inf.0]"
]
}
}
}
},
"rejectedPlans" : [
]
}
推荐答案
在评论中，@ Wan Bachtiar提到"openBalance"看起来像是
In the comments, @Wan Bachtiar mentioned that "openBalance" looks to be a multikey index. To clarify, yes, in the targeted collection, the "openBalance" field is an embedded field within an array. This is the case even though, in the view, the data is shaped in such a way that "openBalance" is an embedded field that is not within an array.
问题所在在于目标集合上的多键索引，因为Mongo不需要遍历与该"openBalance"字段相关的每个数组元素，因此从逻辑上讲，这会大大增加扫描范围时间-因为有时有很多与此特定字段有关的数组元素.
The multikey index on the targeted collection is where the issue lies, because instead of a 1 for 1 document situation, Mongo needs to look through every array element pertaining to this "openBalance" field, which, logically, dramatically increases the scan time - because sometimes there are many, many array elements pertaining to this particular field.
经过进一步的检查，我意识到我可以通过更改通过ETL将"openBalance"填充到我们的mongo集合中的方式来解决此问题.通过进行此更改，我将能够使"openBalance"成为标准索引，而不是多键索引，从而使mongo可以搜索更小的数据集以返回我的计数.
After some further checking, I realized I can address this issue by changing how I populate "openBalance" to our mongo collection via the ETL. By making this change I'll be able to make "openBalance" a standard index, rather than a multikey index, which in turn will allow mongo to search a much smaller data set in order to return my counts.
这篇关于如何加快MongoDB视图上的计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
