在 MongoDB 中执行等效的 SQL JOIN
MongoDB 非常渴望非结构化数据，属于任何数据库开发人员的工具集。一些聚合过程比关系数据库慢得多，即使使用现有索引也是如此。
在集合之间使用连接时就是这种情况。Lookup，MongoDB 的 JOIN 对应物，目前不能进行 Merge 或 Hash joins；因此，它在当前状态下永远不会很快。
对于选项数量有限的枚举，它明显更合适。你可以通过提供一个索引来帮助查找，该索引允许它进行索引嵌套循环连接，但是你将难以提高任何连接的效率除此之外。
当然，可能有人认为文档集合消除了连接的需要，但这仅适用于非常静态、不变的数据。可能发生变化的数据应始终保存在一个位置。
本文介绍了如何使报告历史数据和缓慢变化数据的 MongoDB 数据库能够正常运行。
MongoDB 中的连接
在文档数据库中，为什么要使用连接？更多的数据库从关系数据库迁移到 MongoDB，并在 MongoDB 中构建了新的数据库。
这些，特别是对于报告，需要大量的查找。一些人建议文档数据库应该去规范化他们的数据以消除查找的需要。
有一个论点是，你是否提供和维护汇总集合（也称为聚合或预聚合）以及你是否利用与应用程序分开的线程在表中的数据更改时更新它们并不重要。
SQL Server 的传统实践数据库 AdventureWorks 的转换将被用于 MongoDB 作为测试平台。之所以选择它，是因为它需要多次查找才能生成报告。
它可能会导致一些对我们的需求有益的令人不安的迁移问题。它还允许直接比较两个数据库系统。
在 MongoDB 中执行不带索引的 SQL JOIN 等效查询
对于下面的示例，我们将从没有任何索引开始，然后稍后添加它们，监控时间以确保它们被使用。MongoDB 分析器也将用于仔细检查该方法。
MongoDB 中的以下 SQL 使用 Studio 3T 运行。
例子：
SELECT p.PersonType, Sum(soh.TotalDue), Count(*)
FROM "Sales.SalesOrderHeader" soh
INNER JOIN "Sales.Customer" c
ON soh.CustomerID = c.CustomerID
INNER JOIN "Person.Person" p
ON c.PersonID = p.BusinessEntityID
GROUP BY p.PersonType
--Primary type of person: SC = Store Contact,
--IN = Individual (retail) customer
结果显示有多少客户和商店联系人以及他们的订单的总价值。
与 SQL Server 的唯一区别是它在集合名称周围使用字符串分隔符。Lookups 用于实现两个连接。
现在，假设按下按钮。5 分 17 秒后结束。
不久之后，来自防止虐待数据库协会的一位关心但责备的人打来电话。她声称，一些索引可以让她免于悲伤。
cursor.maxTimeMS() 是一种仅适用于查询的游标方法。在这个阶段，建议查看自动生成的代码。
use AdventureWorks;
db.getCollection("Sales.SalesOrderHeader").aggregate(
[
{
"$project" : {
"_id" : NumberInt(0),
"soh" : "$$ROOT"
}
},
{
"$lookup" : {
"localField" : "soh.CustomerID",
"from" : "Sales.Customer",
"foreignField" : "_id",
"as" : "c"
}
},
{
"$unwind" : {
"path" : "$c",
"preserveNullAndEmptyArrays" : false
}
},
{
"$lookup" : {
"localField" : "c.PersonID",
"from" : "Person.Person",
"foreignField" : "BusinessEntityID",
"as" : "p"
}
},
{
"$unwind" : {
"path" : "$p",
"preserveNullAndEmptyArrays" : false
}
},
{
"$group" : {
"_id" : {
"p᎐PersonType" : "$p.PersonType"
},
"SUM(soh᎐TotalDue)" : {
"$sum" : "$soh.TotalDue"
},
"COUNT(*)" : {
"$sum" : NumberInt(1)
}
}
},
{
"$project" : {
"p.PersonType" : "$_id.p᎐PersonType",
"SUM(soh᎐TotalDue)" : "$SUM(soh᎐TotalDue)",
"COUNT(*)" : "$COUNT(*)",
"_id" : NumberInt(0)
}
}
],
{
"allowDiskUse" : true
}
);
当你查找 MongoDB 时，你正在搜索的集合中文档的字段是你在聚合阶段提供的关键字段。该字段描述了你将作为文档数组收集的论文。
$lookup 操作将输入文档中的外部字段与输出文档中的本地字段进行比较。
引用文档中的 key 字段可能不存在，在这种情况下，它被假定为 null。如果外部字段没有索引，它将对管道中的每个页面进行完整的集合扫描（COLLSCAN）查询。
这变得非常昂贵，因为你需要索引命中而不是表扫描。
MongoDB 中的索引
如果你需要集合中的一些字段，则使用这些字段和实际查询条件创建一个覆盖索引会显着提高效率。MongoDB 现在可以采用更快的方法从索引中传递结果，而无需访问文档。
这应该对预期经常使用的任何查询进行。应该索引哪些字段？
- 在查找或搜索中识别文档的任何关键字段；和
- 用作外键的字段。
当键具有多个字段（例如名字/姓氏组合）时，应使用复合索引。当使用多个字段进行排序时，最好考虑一下你希望如何对报告进行排序，因为这将决定索引中字段的最佳顺序。
使用 MongoDB 中创建的索引执行 SQL JOIN 等效查询
如果关系数据库包含单个列作为主键，则导入中包含 _id 字段。这些唯一的 _id 字段的功能类似于聚簇索引。
最好将它们命名为 _id 以被批准为聚集索引。为了不中断查询，原始字段将添加到其原始名称下。
你必须为用于 lookup 的所有其他字段创建索引，例如 from 字段，以及 lookup 引用的字段，例如 foreignField.。
这与 JOIN 的 ON 子句中的相同。然后，要使用我们已经编入索引的 _id,，它与客户 ID 具有相同的值，请输入 CustomerID.。
重新测试它，发现反应时间已经减少到 6.6 秒。这比没有索引的 5 分 17 秒要快，但仍不及原始 SQL Server 数据库的功能。
SQL Server 设法在与 MongoDB 相同的服务器上在 160 毫秒内完成相同的聚合。
不幸的是，除了使用了 COLLSCAN 之外，MongoDB 分析器无法告诉任何其他信息。这是不可避免的，因为虽然单个查找谨慎地使用了索引，但索引不能简单地充当更大聚合的组件，除非它包含初始匹配步骤。
假设你在 Studio 3T 中重新排列 SQL 查询中的连接。在这种情况下，SQL Server 使用与以前相同的方法：对客户表和人员表执行哈希表匹配内连接。你将在两行上使用聚集索引扫描，并使用 SalesOrderHeader. 对结果进行内部连接。
Studio 3T 版本如下。
例子：
SELECT p.PersonType, Sum(soh.TotalDue), Count(*)
FROM "Sales.Customer" c
INNER JOIN "Person.Person" p
ON c.PersonID = p.BusinessEntityID
INNER JOIN "Sales.SalesOrderHeader" soh
ON soh.CustomerID = c.CustomerID
GROUP BY p.PersonType
--Primary type of person: SC = Store Contact,
--IN = Individual (retail) customer
Studio 3T 中的聚合顺序反映了连接的顺序；因此，执行顺序不同，在 4.2 秒时更好。在聚合编辑器中优化聚合脚本几乎没有什么区别，只需要三秒多一点。
优化只是将通过管道的字段减少到基本字段。
use AdventureWorks2016;
db.getCollection("Sales.Customer").aggregate(
[
{
"$project" : {
"_id" : NumberInt(0),
"CustomerID" : 1.0,
"PersonID" : 1.0
}
},
{
"$lookup" : {
"localField" : "PersonID",
"from" : "Person.Person",
"foreignField" : "BusinessEntityID",
"as" : "p"
}
},
{
"$unwind" : {
"path" : "$p",
"preserveNullAndEmptyArrays" : false
}
},
{
"$project" : {
"CustomerID" : 1.0,
"PersonID" : 1.0,
"PersonType" : "$p.PersonType"
}
},
{
"$lookup" : {
"localField" : "CustomerID",
"from" : "Sales.SalesOrderHeader",
"foreignField" : "CustomerID",
"as" : "soh"
}
},
{
"$unwind" : {
"path" : "$soh",
"preserveNullAndEmptyArrays" : false
}
},
{
"$project" : {
"CustomerID" : 1.0,
"PersonID" : 1.0,
"PersonType" : 1.0,
"TotalDue" : "$soh.TotalDue"
}
},
{
"$group" : {
"_id" : {
"PersonType" : "$PersonType"
},
"SUM(TotalDue)" : {
"$sum" : "$TotalDue"
},
"COUNT(*)" : {
"$sum" : NumberInt(1)
}
}
},
{
"$project" : {
"PersonType" : "$_id.PersonType",
"Total" : "$SUM(TotalDue)",
"Transactions" : "$COUNT(*)",
"_id" : NumberInt(0)
}
}
],
{
"allowDiskUse" : false
}
);
如果你继续沿着这条路径前进，你将不得不投入大量精力来优化每个查询。
想象一下，你有一大堆经理追着你要一堆收入报告。现在做什么？
使用预聚合集合来简化 MongoDB 中的报告
如果你以你可能报告的最小粒度进行聚合集合，将会有所帮助。这是 OLAP 多维数据集的等价物。
在这种情况下，它现在正在处理从发票中提取的贸易记录。这些不会改变，这是有充分理由的。
如果使用这样的中间集合进行预聚合，
use AdventureWorks2016;
db.getCollection("Sales.Customer").aggregate(
[
{
"$project" : {
"_id" : NumberInt(0),
"CustomerID" : 1.0,
"PersonID" : 1.0
}
},
{
"$lookup" : {
"localField" : "PersonID",
"from" : "Person.Person",
"foreignField" : "BusinessEntityID",
"as" : "p"
}
},
{
"$unwind" : {
"path" : "$p",
"preserveNullAndEmptyArrays" : false
}
},
{
"$project" : {
"CustomerID" : 1.0,
"PersonID" : 1.0,
"PersonType" : "$p.PersonType"
}
},
{
"$lookup" : {
"localField" : "CustomerID",
"from" : "Sales.SalesOrderHeader",
"foreignField" : "CustomerID",
"as" : "soh"
}
},
{
"$unwind" : {
"path" : "$soh",
"preserveNullAndEmptyArrays" : false
}
},
{
"$project" : {
"CustomerID" : 1.0,
"PersonID" : 1.0,
"PersonType" : 1.0,
"TotalDue" : "$soh.TotalDue"
}
},
{
"$group" : {
"_id" : {
"PersonType" : "$PersonType"
},
"SUM(TotalDue)" : {
"$sum" : "$TotalDue"
},
"COUNT(*)" : {
"$sum" : NumberInt(1)
}
}
},
{
"$project" : {
"PersonType" : "$_id.PersonType",
"Total" : "$SUM(TotalDue)",
"Transactions" : "$COUNT(*)",
"_id" : NumberInt(0)
}
}
],
{
"allowDiskUse" : false
}
);
你不会希望在实践中存储这样一个专门的聚合集合。相反，你应该将更一般的数据划分为一个时期，例如几周、几个月或几年，以便你可以随时间绘制销售额。
你还应该提供销售人员和商店的 ID，以便某人获得交易的信用。
聚合将在 SQL 中勾勒出来。但是，你可以将日期计算和输出阶段等内容留在 SQL 之外，因为它可以完成的工作受到限制。
SELECT c.PersonID, p.PersonType, soh.SalesPersonID, psp.Name, psp.CountryRegionCode,
Sum(soh.TotalDue), Count(*)
--,
--Year(soh.OrderDate) AS year, Month(soh.OrderDate) AS month,
--DatePart(WEEK, soh.OrderDate) AS week
FROM "Sales.SalesOrderHeader" AS soh
INNER JOIN "Sales.Customer" AS c
ON c.CustomerID = soh.CustomerID
INNER JOIN "Person.Person" AS p
ON p.BusinessEntityID = c.PersonID
INNER JOIN "Person.Address" AS pa
ON pa.AddressID = soh.BillToAddressID
INNER JOIN "Person.StateProvince" AS psp
ON psp.StateProvinceID = pa.StateProvinceID
GROUP BY c.PersonID, p.PersonType, soh.SalesPersonID, psp.Name,
psp.CountryRegionCode
--, Year(soh.OrderDate), Month(soh.OrderDate),
--DatePart(WEEK, soh.OrderDate);
确保正确的顺序并关闭松散的末端
之后，复制 mongo shell 查询代码并将其放入 Studio 3T 的 MongoDB 聚合查询构建器，即聚合编辑器。
然后应该对聚合进行微调。之后，你可以直接从 Studio 3T 中的 SQL 查询选项卡运行报告。
使用大脑可以将查询从近五分钟缩短到大约 100 毫秒。只需将常识索引放在外键引用和键上，然后尝试覆盖和相交索引来克服等待几分钟的痛苦。
然后，检查你是否在不必要地扫描古老或不变的数据。不幸的是，这是一个如此普遍的错误，几乎成为流行病。
这篇文章展示了一个立方体如何帮助加快使用相同核心数据的许多报告的设计和生成。
最后，正确获取聚合管道中的阶段顺序至关重要。当你只有最终报告所需的文档时，应保存查找，如排序。
在早期，应该进行匹配和投影。
进行分组的那一刻是一个更具战术性的选择，尽管它不是 MongoDB 的一个延长过程。保持管道较小是有意义的，只将你需要的数据推送到每个文档中。
但这最好被视为最终清理的一部分，虽然它会加快速度，但不会提供显着的优势。
另一方面，当前的交易信息永远不能以这种方式处理。
例如，你永远不会想要有关最近交易的过时信息。但是，由于这是少量数据，因此不太可能导致查找出现问题。