有组的时候MongoDB聚合框架很慢
我正在尝试使用"group"进行汇总查询以获得结果总数。
"requested_items" （我的结果）的总数是+ - 1.900.000。
如果我用“group”执行，查询非常慢（+ - 300秒）。
如果我执行时没有“group”，查询速度非常快（±1sec）。
我究竟做错了什么？
示例代码如下。
慢查询
db.minute.aggregate([
{ $match: {
$and: [
{ "status": "Homologado" },
{ "requested_items.status": /aceito/i },
]
} },
{ $sort: {'_id': 1}},
{ $unwind: "$requested_items" },
{ $unwind: "$requested_items.winner" },
{ $match: {
$and: [
{ "status": "Homologado" },
{ "requested_items.status": /aceito/i },
]
} },
{ $project: {
"_id": 1
} },
{ $group: {
"_id" : null,
"total" : {$sum: 1},
} },
], {allowDiskUse: true});
快速查询
db.minute.aggregate([
{ $match: {
$and: [
{ "status": "Homologado" },
{ "requested_items.status": /aceito/i },
]
} },
{ $sort: {'_id': 1}},
{ $unwind: "$requested_items" },
{ $unwind: "$requested_items.winner" },
{ $match: {
$and: [
{ "status": "Homologado" },
{ "requested_items.status": /aceito/i },
]
} },
{ $project: {
"_id": 1
} },
], {allowDiskUse: true});
数据库结构
{
"_id" : "12345678ABCD",
"field_1" : [
{
"a" : null,
"b" : "ABC"
},
{
"code" : null,
"b" : "ABCD"
}
],
"status" : "Homologado",
"initial_date" : ISODate("2016-05-24T11:31:00.000Z"),
"field_2" : [
{
"a" : "ABC",
"b" : "ABCDE"
},
{
"a" : "ABCF",
"b" : "ABCDEF"
}
],
"field_3" : "Lorem ipsum dolor sit amet...",
"field_4" : [
{
"date" : ISODate("2016-05-24T13:54:48.000Z"),
"a" : "Text",
"b" : "More text..."
}
],
"field_4" : 12312321,
"field_5" : ISODate("2016-05-24T13:55:00.000Z"),
"field_6" : "ABCD",
"requested_items" : [
{
"status" : " Aceito e Habilitado",
"field_a" : "Text...",
"winner" : [
{
"a" : "23213.213213.23/232-23",
"b" : 130446,
"c" : 543223,
"d" : NumberLong(2),
"e" : "ABC 123 FULANO",
"f" : "text",
"g" : {
"description" : "TEXT TEXT TEXT"
}
},
{
"a" : "23213.213213.23/232-23",
"b" : 130446,
"c" : 543223,
"d" : NumberLong(2),
"e" : "ABC 123 FULANO",
"f" : "text",
"g" : {
"description" : "TEXT TEXT TEXT"
}
}
],
"field_c" : {
"_id" : ObjectId("5744dd3271af88052f0cc343"),
"a" : "TEXT",
"b" : "TEXT"
},
"field_d" : NumberLong(2),
"field_e" : 5223,
"field_f" : "Não",
"field_g" : "-",
"field_h" : {
"field_a1" : [
{
"a" : "23213.213213.23/232-23",
"b" : ISODate("2016-05-23T23:54:21.000Z"),
"c" : 103432446,
"d" : 522343,
"e" : "Sim",
"f" : NumberLong(2),
"g" : "TEXT TEXT TEXT",
"h" : "Sim",
"i" : {
"a" : "TEXT TEXT TEXT"
}
},
{
"a" : "23213.213213.23/232-23",
"b" : ISODate("2016-05-23T23:54:21.000Z"),
"c" : 103432446,
"d" : 522343,
"e" : "Sim",
"f" : NumberLong(2),
"g" : "TEXT TEXT TEXT",
"h" : "Sim",
"i" : {
"a" : "TEXT TEXT TEXT"
}
}
],
"field_a2" : [
{
"a" : "23213.213213.23/232-23",
"b" : ISODate("2016-05-23T23:54:21.000Z"),
"c" : 103432446,
"d" : 522343,
"e" : "Sim",
"f" : NumberLong(2),
"g" : "TEXT TEXT TEXT",
"h" : "Sim",
"i" : {
"a" : "TEXT TEXT TEXT"
}
},
{
"a" : "23213.213213.23/232-23",
"b" : ISODate("2016-05-23T23:54:21.000Z"),
"c" : 103432446,
"d" : 522343,
"e" : "Sim",
"f" : NumberLong(2),
"g" : "TEXT TEXT TEXT",
"h" : "Sim",
"i" : {
"a" : "TEXT TEXT TEXT"
}
}
],
"field_a3" : {},
"field_a4" : [
{
"date" : ISODate("2016-05-24T11:34:32.000Z"),
"A" : "TEXT",
"B" : "TEXT"
},
{
"date" : ISODate("2016-05-24T12:12:54.000Z"),
"A" : "TEXT",
"B" : "TEXT"
},
{
"date" : ISODate("2016-05-24T12:48:21.000Z"),
"A" : "TEXT",
"B" : "TEXT"
},
{
"date" : ISODate("2016-05-24T12:55:38.000Z"),
"A" : "TEXT",
"B" : "TEXT"
},
{
"date" : ISODate("2016-05-24T12:55:47.000Z"),
"A" : "TEXT",
"B" : "TEXT"
},
{
"date" : ISODate("2016-05-24T13:01:36.000Z"),
"A" : "TEXT",
"B" : "TEXT"
},
{
"date" : ISODate("2016-05-24T13:15:02.000Z"),
"A" : "TEXT",
"B" : "TEXT"
}
]
},
"field_i" : "Não",
"field_j" : 1
},
{
"status" : " Aceito e Habilitado",
"field_a" : "Text...",
"winner" : [
{
"a" : "23213.213213.23/232-23",
"b" : 130446,
"c" : 543223,
"d" : NumberLong(2),
"e" : "ABC 123 FULANO",
"f" : "text",
"g" : {
"description" : "TEXT TEXT TEXT"
}
}
],
"field_c" : {
"_id" : ObjectId("5744dd3271af88052f0cc343"),
"a" : "TEXT",
"b" : "TEXT"
},
"field_d" : NumberLong(2),
"field_e" : 5223,
"field_f" : "Não",
"field_g" : "-",
"field_h" : {
"field_a1" : [
{
"a" : "23213.213213.23/232-23",
"b" : ISODate("2016-05-23T23:54:21.000Z"),
"c" : 103432446,
"d" : 522343,
"e" : "Sim",
"f" : NumberLong(2),
"g" : "TEXT TEXT TEXT",
"h" : "Sim",
"i" : {
"a" : "TEXT TEXT TEXT"
}
},
{
"a" : "23213.213213.23/232-23",
"b" : ISODate("2016-05-23T23:54:21.000Z"),
"c" : 103432446,
"d" : 522343,
"e" : "Sim",
"f" : NumberLong(2),
"g" : "TEXT TEXT TEXT",
"h" : "Sim",
"i" : {
"a" : "TEXT TEXT TEXT"
}
}
],
"field_a2" : [
{
"a" : "23213.213213.23/232-23",
"b" : ISODate("2016-05-23T23:54:21.000Z"),
"c" : 103432446,
"d" : 522343,
"e" : "Sim",
"f" : NumberLong(2),
"g" : "TEXT TEXT TEXT",
"h" : "Sim",
"i" : {
"a" : "TEXT TEXT TEXT"
}
},
{
"a" : "23213.213213.23/232-23",
"b" : ISODate("2016-05-23T23:54:21.000Z"),
"c" : 103432446,
"d" : 522343,
"e" : "Sim",
"f" : NumberLong(2),
"g" : "TEXT TEXT TEXT",
"h" : "Sim",
"i" : {
"a" : "TEXT TEXT TEXT"
}
}
],
"field_a3" : {},
"field_a4" : [
{
"date" : ISODate("2016-05-24T11:34:32.000Z"),
"A" : "TEXT",
"B" : "TEXT"
},
{
"date" : ISODate("2016-05-24T12:12:54.000Z"),
"A" : "TEXT",
"B" : "TEXT"
},
{
"date" : ISODate("2016-05-24T12:48:21.000Z"),
"A" : "TEXT",
"B" : "TEXT"
},
{
"date" : ISODate("2016-05-24T12:55:38.000Z"),
"A" : "TEXT",
"B" : "TEXT"
},
{
"date" : ISODate("2016-05-24T12:55:47.000Z"),
"A" : "TEXT",
"B" : "TEXT"
},
{
"date" : ISODate("2016-05-24T13:01:36.000Z"),
"A" : "TEXT",
"B" : "TEXT"
},
{
"date" : ISODate("2016-05-24T13:15:02.000Z"),
"A" : "TEXT",
"B" : "TEXT"
}
]
},
"field_i" : "Não",
"field_j" : 2
},
{
"status" : " Aceito e Habilitado",
"field_a" : "Text...",
"winner" : [
{
"a" : "23213.213213.23/232-23",
"b" : 130446,
"c" : 543223,
"d" : NumberLong(2),
"e" : "ABC 123 FULANO",
"f" : "text",
"g" : {
"description" : "TEXT TEXT TEXT"
}
}
],
"field_c" : {
"_id" : ObjectId("5744dd3271af88052f0cc343"),
"a" : "TEXT",
"b" : "TEXT"
},
"field_d" : NumberLong(2),
"field_e" : 5223,
"field_f" : "Não",
"field_g" : "-",
"field_h" : {
"field_a1" : [
{
"a" : "23213.213213.23/232-23",
"b" : ISODate("2016-05-23T23:54:21.000Z"),
"c" : 103432446,
"d" : 522343,
"e" : "Sim",
"f" : NumberLong(2),
"g" : "TEXT TEXT TEXT",
"h" : "Sim",
"i" : {
"a" : "TEXT TEXT TEXT"
}
},
{
"a" : "23213.213213.23/232-23",
"b" : ISODate("2016-05-23T23:54:21.000Z"),
"c" : 103432446,
"d" : 522343,
"e" : "Sim",
"f" : NumberLong(2),
"g" : "TEXT TEXT TEXT",
"h" : "Sim",
"i" : {
"a" : "TEXT TEXT TEXT"
}
}
],
"field_a2" : [
{
"a" : "23213.213213.23/232-23",
"b" : ISODate("2016-05-23T23:54:21.000Z"),
"c" : 103432446,
"d" : 522343,
"e" : "Sim",
"f" : NumberLong(2),
"g" : "TEXT TEXT TEXT",
"h" : "Sim",
"i" : {
"a" : "TEXT TEXT TEXT"
}
},
{
"a" : "23213.213213.23/232-23",
"b" : ISODate("2016-05-23T23:54:21.000Z"),
"c" : 103432446,
"d" : 522343,
"e" : "Sim",
"f" : NumberLong(2),
"g" : "TEXT TEXT TEXT",
"h" : "Sim",
"i" : {
"a" : "TEXT TEXT TEXT"
}
}
],
"field_a3" : {},
"field_a4" : [
{
"date" : ISODate("2016-05-24T11:34:32.000Z"),
"A" : "TEXT",
"B" : "TEXT"
},
{
"date" : ISODate("2016-05-24T12:12:54.000Z"),
"A" : "TEXT",
"B" : "TEXT"
},
{
"date" : ISODate("2016-05-24T12:48:21.000Z"),
"A" : "TEXT",
"B" : "TEXT"
},
{
"date" : ISODate("2016-05-24T12:55:38.000Z"),
"A" : "TEXT",
"B" : "TEXT"
},
{
"date" : ISODate("2016-05-24T12:55:47.000Z"),
"A" : "TEXT",
"B" : "TEXT"
},
{
"date" : ISODate("2016-05-24T13:01:36.000Z"),
"A" : "TEXT",
"B" : "TEXT"
},
{
"date" : ISODate("2016-05-24T13:15:02.000Z"),
"A" : "TEXT",
"B" : "TEXT"
}
]
},
"field_i" : "Não",
"field_j" : 3
},
],
"field_7" : "TEXT",
"field_8" : {
"a" : "TEXT",
"b" : "TEXT",
"c" : "324234",
"d" : "TEXT TEXT TEXT TEXT"
},
"field_9" : 43234
}
说明
{
"waitedMS" : NumberLong(0),
"stages" : [
{
"$cursor" : {
"query" : {
"$and" : [
{
"status" : "Homologado"
},
{
"requested_items.status" : /aceito/i
}
]
},
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "module_database.minute",
"indexFilterSet" : false,
"parsedQuery" : {
"$and" : [
{
"status" : {
"$eq" : "Homologado"
}
},
{
"requested_items.status" : /aceito/i
}
]
},
"winningPlan" : {
"stage" : "COLLSCAN",
"filter" : {
"$and" : [
{
"status" : {
"$eq" : "Homologado"
}
},
{
"requested_items.status" : /aceito/i
}
]
},
"direction" : "forward"
},
"rejectedPlans" : []
}
}
},
{
"$unwind" : {
"path" : "$requested_items"
}
},
{
"$unwind" : {
"path" : "$requested_items.winner"
}
},
{
"$match" : {
"$and" : [
{
"status" : "Homologado"
},
{
"requested_items.status" : /aceito/i
}
]
}
},
{
"$group" : {
"_id" : {
"$const" : null
},
"numberOfdocs" : {
"$sum" : {
"$const" : 1
}
}
}
}
],
"ok" : 1
}
我的服务器是：
操作系统：UBUNTU14 / 64
CPU：6
内存：16 GB
总存储量：80 GB
只运行我的问题的测试。
最后解决了我用group查询的问题。 这是设计模式的错误。 在SQL世界中思考，我在我的应用程序中思考之前设计了集合。 因此，查询速度缓慢。
为了解决这个问题，需要重新设计我的收藏，并将相关数据放在我的文档的第一级。 在我的搜索中，我发现在Aggragation上，索引需要处于管道的第一阶段。 如果我在stage $ unwind之后使用带索引的字段，则不考虑它。
除此之外，我使用包https://github.com/darkskyapp/string-hash为文本字段创建一个int散列。 所以，我的文本字段可以被索引。
所以我的查询改变了300秒5秒。
由于我们没有环境细节，因此很难确定速度。 你可以尝试看看解释是如何通过添加来预测你的查询：
{
explain:true
}
到您的聚集查询db.coll.aggregate([pipeline], {explain:true},{allowDiskUse: true}) 。 还需要考虑的是， $unwind处理的文件数量翻倍。
当你要计算文档的数量 - > 它可能会更快，只需要一个数组的大小（在第一次展开之后）并在稍后进行求和
db.inventory.aggregate(
[
{
$group: {
_id: null,
numberOfdocs: { $sum:{$size: "$requested_items.winner" }}
}
}
]
)
编辑
玩这个查询后，我能够将执行时间缩短约45％。 重点是跳过第二个$match因为这会扫描完整的结果集，所以最后一个$group包含所有数据，我们可以过滤掉最后需要的内容，因为此操作是在小的结果集上完成的。
db.coll.aggregate([{
$match : {
"status" : "Homologado"
}
}, {
$unwind : "$requested_items"
}, {
$unwind : "$requested_items.winner"
}, {
$project : {
x : "$requested_items.status",
}
}, {
$group : {
_id : "$x",
numberOfdocs : {
$sum : 1
}
}
}, {
$match : {
"_id" : /acesssito/i
}
}
], {
allowDiskUse: true
});