MongoDB聚合查询与MySQL SELECT field1 FROM表的比较 [英] MongoDB aggregate queries vs. MySQL SELECT field1 FROM table

问题描述

我对MongoDB完全陌生，想比较NoSQL数据模型相对于其关系数据库计数器部分的查询性能.我把它写进了MongoDB shell

I am completely new to MongoDB and wanted to compare query performance of a NoSQL data model relative to its relational database counter part. I wrote this into MongoDB shell

// Make 10 businesses
// Each business has 10 locations
// Each location has 10 departments
// Each department has 10 teams
// Each team has 100 employees
(new Array(10)).fill(0).forEach(_=>
    db.businesses.insert({
        "name":"Business Name",
        "locations":(new Array(10)).fill(0).map(_=>({
            "name":"Office Location",
            "departments":(new Array(10)).fill(0).map(_=>({
                "name":"Department",
                "teams":(new Array(10)).fill(0).map(_=>({
                    "name":"Team Name",
                    "employees":(new Array(100)).fill(0).map(_=>({
                        "age":Math.floor(Math.random()*100)
                    }))
                }))
            }))
        }))
    })
);

然后，我通过编写以下语句来尝试与MySQL的EXPLAIN SELECT age,name,(and a few other fields) FROM employees WHERE age >= 50 ORDER BY age DESC等效:

Then I attempted the equivalent of MySQL's EXPLAIN SELECT age,name,(and a few other fields) FROM employees WHERE age >= 50 ORDER BY age DESC by writing this statement:

db.businesses.aggregate([
    { $unwind: "$locations" },
    { $unwind: "$locations.departments" },
    { $unwind: "$locations.departments.teams" },
    { $unwind: "$locations.departments.teams.employees" },
    { $project: { _id: 0, age: "$locations.departments.teams.employees.age" } },
    { $match: { "age": { $gte: 50 }} },
    { $sort: {"age" : -1}}
]).explain("executionStats")

结果是:

"errmsg":排序超出内存限制104857600字节，但没有 选择进行外部排序.中止操作.传递allowDiskUse:true 选择加入."，

"errmsg" : "Sort exceeded memory limit of 104857600 bytes, but did not opt in to external sorting. Aborting operation. Pass allowDiskUse:true to opt in.",

因此，我删除了sort子句并尝试获取explain.但是结果是:

So I deleted the sort clause and try to get an explain. But the result was:

TypeError:db.businesses.aggregate(...).explain不是函数

TypeError: db.businesses.aggregate(...).explain is not a function

所以我的问题是:

1. 最重要的是，我想知道与MongoDB的聚合查询计数器部分相比，SELECT age FROM employees WHERE age >= 50 ORDER BY age DESC的性能差异.差不多一样吗?一个会比另一个更快或更有效吗?

1. Primarily, I want to know the performance difference of SELECT age FROM employees WHERE age >= 50 ORDER BY age DESC when compared with the MongoDB's aggregate query counter part. Is it more or less the same? Will one be substantially faster or more performant than the other?

或者，如何解决MongoDB查询，以便获得性能详细信息以与MySQL查询计数器部分进行比较?

Alternatively, how do I fix my MongoDB query so that I can get performance details to compare against my MySQL query counter part?

推荐答案

员工是单一实体；因此，您可能不想在部门，位置和团队的丰富结构中对团队成员的age进行如此深入的建模.拥有一个单独的employees集合并只需执行以下操作就可以了:

Employees are single entities; thus, you probably don't want to model age of a team member so deeply in the rich structure of departments and locations and teams. It is perfectly fine to have a separate employees collection and simply do:

db.businesses.aggregate([
{$match: {"age": {$gt: 50} }}
,{$sort: {"age": -1} }
]);

在您的businesses收藏集中，您可以拥有:

Deep in your businesses collection you can have:

{ teams: [ {name: "T1", employees: [ "E1", "E34" ]} ] }

或者，尝试以下操作:

db.businesses.aggregate([ your pipeline] ,{allowDiskUse:true});

OP的设置是10个业务-> 10个位置-> 10个部门-> 10个团队-> 100个雇员.前三个展开会产生10000倍的数据爆炸，而最后一个则超出100倍.我们可以使用$filter来缩小点击量:

The OP has a setup of 10 biz -> 10 loc -> 10 depts -> 10 teams -> 100 emps. The first 3 unwinds creates a 10000x explosion of data but the last one is 100x beyond that. We can shrink the hit by using $filter:

db.businesses.aggregate([
{ $unwind: "$locations" },
{ $unwind: "$locations.departments" },
{ $unwind: "$locations.departments.teams" },

{$project: {
        XX: {$filter: {
                    input: "$locations.departments.teams.employees",
                    as: "z",
                    cond: {$gte: [ "$$z.age", 50] }
            }}
    }}
,{$unwind: "$XX"}
,{$sort: {"XX.age":-1}}])

这篇关于MongoDB聚合查询与MySQL SELECT field1 FROM表的比较的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！