我对 MongoDB 完全陌生,想要比较 NoSQL 数据模型相对于关系数据库对应部分的查询性能。我将其写入 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
通过写这个声明:
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
选择加入。",
所以我删除了排序子句并尝试得到一个explain
。但结果是:
类型错误:db.businesses.aggregate(...).explain 不是函数
所以我的问题是:
首先,我想知道性能差异SELECT age FROM employees WHERE age >= 50 ORDER BY age DESC
与 MongoDB 的聚合查询对应部分相比。或多或少是一样的吗?其中一个会比另一个更快或更高效吗?
或者,如何修复 MongoDB 查询,以便获得性能详细信息以与 MySQL 查询对应部分进行比较?