在 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 的一個延長過程。保持管道較小是有意義的，只將你需要的資料推送到每個文件中。
但這最好被視為最終清理的一部分，雖然它會加快速度，但不會提供顯著的優勢。
另一方面，當前的交易資訊永遠不能以這種方式處理。
例如，你永遠不會想要有關最近交易的過時資訊。但是，由於這是少量資料，因此不太可能導致查詢出現問題。