作者：阿尔瓦罗•埃尔南德斯（ Álvaro Hernández）
准备工作
在这篇博客中，我们对比了MongoDB和Postgres中的JOIN功能。结论是MongoDB的Join非常脆弱（当情况发生变化时，必须对应用程序进行大量的重新编码），而且与Postgres相比，MongoDB的性能通常很差。我们从第2节开始，讨论MongoDB对Join的支持，然后在第3节中继续介绍Postgres中的Join功能。在第4节中，我们说明了为什么MongoDB连接脆弱，最后在第5节中，我们对比了两个系统的Join性能。
在整个博客文章中，我们将使用表1中员工和部门关系模式作示例，尽可能通俗的阐述问题。
这里，员工具有一个名字，年龄，薪水并且在一个部门中。反过来，部门有一个名称，一个下限和一个预算。请注意，有一个部门Candy没有雇员。
稍后，管理层可能会决定Bill可以在多个部门之间分配时间。在这种情况下，表1中的架构不再有效，并且数据必须更改为表2中的数据。请注意，我们必须添加一个名为work_in的表，其中带有dedication_pct字段以指示Bill在多个部门之间的时间划分。
MongoDB中的JOIN
在MongoDB中，有两种主要的表达关系的方式，即“嵌入”和“引用”。使用嵌入式方法，必须确定哪个是“外部”文档，哪个是“内部”文档。将首选文档作为外部文档是很自然的，我们可以选择Employee作为该角色。
{"_id": "1","ename": "Bill","age": 36,"salary": 10000,"department": {"dname": "Shoe","floor": 1,"budget": 1200}}
换句话说，部门信息存储在每个员工文档中。基本上，这是“嵌入”表示。在文档应用程序中，这种表示可能有意义，但是在结构化数据中，它有两个主要缺点：
首先，为部门中的每个员工重复部门信息。由于Bill和Fred都在Shoe（制鞋）部门，因此信息将被复制。当Shoe信息更新（例如调整预算）时，必须找到所有副本并正确更新。如果省略了一个副本，那么将导致数据库不一致（损坏）。更糟糕的是，这种多记录更新操作是非原子的（MongoDB的默认设置）。或者需要MongoDB的4.0+多文档事务，这有几个限制，会导致性能下降。在第一种情况下，数据库可能会损坏；在第二种情况下，性能会变差。两种情况都是有问题的。
其次，没有地方可以放置部门为Candy（糖果）的信息，因为该部门目前没有员工。由于这两个限制，我们不在此博客中考虑这种嵌入表示形式。
另一种表示形式是“引用”。在这种情况下，比尔的记录将存储为：
{"_id": "1","ename": "Bill","age": 36,"salary": 10000,"department": "1001"}
并且会有一个单独的部门文档来存储部门的属性：
{"_id": "1001","dname": "Shoe","floor": 1,"budget": 1200}
实际上，这看起来很像表1中的关系表示。但是，程序员必须知道Department字段是指Department集合中的文档。换句话说，没有外键可以帮助程序员指定联接的概念。
现在，假设管理层决定员工可以在多个部门之间分配时间。没有标准的方法来表示这种情况，但通常会使用一系列引用。为了在给定部门中记录有关dedication_pct（时间分配）的信息，我们可以将Department字段转换为对象数组（从技术上讲是一个集合），该对象数组包含对部门集合的引用和dedication_pct信息，如下所述，用于描述员工比尔。
收集部门：
{"_id": "1001","dname": "Shoe","floor": 1,"budget": 1200},{"_id": "1002","dname": "Toy","floor": 2,"budget": 1400}
收集员工：
{"_id": "1","ename": "Bill","age": 36,"salary": 10000,"departments": [{ "dept": "1002", "dedication_pct": 60 },{ "dept": "1001", "dedication_pct": 40 }]}
当要获取JOIN（“相关”）信息时，她可以在应用程序中手动进行联接（容易出错，并给开发人员带来负担），也可以使用$ lookup聚合操作（有其自身的局限性） ，例如无法从分片集合中进行$ lookup，或者不支持正确或完全外部联接。
PostgreSQL中的JOIN
从理论上讲，可以在Postgres中使用嵌入式表示形式，因为Postgres jsonb数据类型允许这样做。但是，由于上述缺点，我们在实践中很少见到这种情况。相反，通常使用表1中的表示形式，该表示形式对应于MongoDB中的“引用”情况。
使用标准SQL，您可以按照表1的数据模型找到所有部门的列表及其员工的总薪水，（单击https://gitlab.com/ahachete/pgmongojoins/-/blob/master/post-examples/postgres.sql，可获取Postgres数据和示例的完整源代码）：
select dname, sum(salary)from employee as einner joindepartment as don e.department = d.dnamegroup by dname;┌───────┬───────┐│ dname │ sum │├───────┼───────┤│ Shoe │ 22000 ││ Toy │ 15000 │└───────┴───────┘
当员工可以在多个部门工作时，通常将使用表2中的表示。上面的查询可以轻松转换为：
select dname, sum(salary * dedication_pct * 0.01)from employeeinner joinworks_inusing (ename)group by dname;┌───────┬──────────┐│ dname │ sum │├───────┼──────────┤│ Shoe │ 16000.00 ││ Toy │ 21000.00 │└───────┴──────────┘
请注意，一个人只需要在聚合子句中添加dedication_pct并用works_in替换employee即可更改联接条件中的属性，从第一个联接移至第二个联接。
JOINS在MongoDB中很脆弱
现在重新考虑数据语义符合表1的情况，即员工与部门之间的联接为1：N。要构建一个包含每个部门总薪资的文档，MongoDB中的代码为：（请参见链接的完整源代码https://gitlab.com/ahachete/pgmongojoins/-/blob/master/post-examples/mongodb-model1.js）
db.employee.aggregate([{$lookup: {from: "department",localField: "department",foreignField: "_id",as: "dept"}},{$unwind: "$dept"},{$group: {"_id": "$dept.dname","salary": { "$sum": "$salary" },}}]);结果：{ "_id" : "Shoe", "totalsalary" : 22000 }{ "_id" : "Toy", "totalsalary" : 15000 }
请注意，该代码比Postgres代码复杂得多，因为MongoDB没有关系联接概念，并且使用的语言级别比SQL低。另外，它要求程序员以算法方式构造联接的查询计划。在这种情况下，是将$ unwind，$ lookup和$ group组合在一起以提取所需的信息。更糟糕的是，当我们移到表2的语义时（添加了一个字段，使员工可以在多个部门中工作），MongoDB的连接代码必须进行重大重写，以添加两个聚合阶段来展开“works_in”的隐式关系:
db.employee.aggregate([{$unwind: "$departments"},{$project: {"_id": 0,"salary": 1,"department": "$departments.dept","dedication_pct": "$departments.dedication_pct"}},{$lookup:{from: "department",localField: "department",foreignField: "_id",as: "dept"}},{$unwind: "$dept"},{$group:{_id: "$dept.dname",totalsalary: { $sum: { $multiply: [ "$salary", "$dedication_pct", 0.01 ] } }}}]);Result:{ "_id" : "Shoe", "totalSalary" : 16000 }{ "_id" : "Toy", "totalSalary" : 21000 }
因此，在将连接的语义从1：N更改为M：N时，必须对应用程序进行重大重写。相反，在Postgres中，查询几乎保持不变且简单。
但是，以前的所有结果都有些误导，因为Candy部门没有员工，也没有出现在联接中。假设用户实际上想查看所有三个部门的工资总额。换句话说，除了其他两个部门，她希望看到Candy部门的总工资为零。在Postgres中，这需要对查询进行简单的更改，即将正确的外部联接添加到department表：
select dname, sum(coalesce(salary * dedication_pct * 0.01, 0))from employeeinner joinworks_inusing (ename)right outer joindepartmentusing (dname)group by dname;┌───────┬──────────┐│ dname │ sum │├───────┼──────────┤│ Shoe │ 16000.00 ││ Toy │ 21000.00 ││ Candy │ 0 │└───────┴──────────┘
但是，在MongoDB中，不支持正确的外部联接。因此，您需要在应用程序中手动添加“ 0”。当然，这是开发人员的负担，并且麻烦也容易出错。
众所周知（在每本DBMS教科书中都有介绍），相对于MongoDB等较低级别的解决方案，RDBMS具有更高的数据独立性。因为数据库持续很长时间，并且语义可能会不时发生变化，所以优秀的数据独立性是非常可取的功能，它存在于Postgres中，但在MongoDB中不存在。因此，MongoDB联接是一种脆弱的解决方案。
PostgreSQL与MongoDB的性能比较
在本节中，我们报告上一节中两个查询的执行情况，即查找每个部门的总薪资，无论部门有没有员工。
本节中的所有源代码都出现在这里（https://gitlab.com/ahachete/pgmongojoins），有兴趣的读者可以在其中找到一个在Postgres中生成合成数据的加载程序，然后使用Postgres JSON函数以适合导入Mongo的格式导出数据。然后，我们对两个数据集运行两个查询并比较执行时间。
在AWS上使用XFS格式化的本地NVMe磁盘上，使用EC2 i3.xlarge实例（4核，32 GB RAM）对基准进行了测试。在Postgres实例上执行了基本调整，并遵循了Mongo生产最佳实践。该基准测试是由4000个部门和2000万名员工执行的，给定的员工在1至3个部门之间工作。数据大小在Postgres中为6.1 GB，在Mongo中为1.6GB（使用默认压缩）。总执行时间如下表所示。
MongoDB比Postgres慢130倍，因为唯一可用的加入策略是遍历员工，因为每个人都在部门表中执行查找。相反，Postgres可以使用这种策略（称为迭代替换）以及合并联接和哈希联接，并且Postgres查询优化器将选择预期的最佳策略。MongoDB仅限于单一策略。无论何时（几乎总是如此），该单一策略都会导致性能下降。
更改MongoDB查询执行策略涉及重组数据库（从而需要重写所有查询）或在应用程序中实现查询优化器（这是大量工作）。
在M：N情况下，事情也很糟糕。同样，MongoDB有一个单一策略，被硬编码到应用程序中。相反，Postgres可以从所有可用选项中进行选择。表4显示了结果性能比较。
总而言之，相对于Postgres，MongoDB的JOINS：
如第4节所述，MongoDB的JOINS很脆弱。如果在数据库的整个生命周期中发生任何更改，则MongoDB需要进行大量的重新编码，而Postgres则需要进行一些适度的更改。
通常，执行效果不佳，如第5节所述。MongoDB没有查询优化器，并且执行策略已硬编码到应用程序中。每当merge-sort或hash-join是最佳选择时，Mongo的性能就会受到影响。
当然，您应该怀疑“也许我选择了错误的文档作为“外部”。如果要更改为使Department成为外部文件的设计会发生什么。我们将在下一篇博客文章中探讨这种情况。
I Love PG
每个PostgreSQL版本发布都有他的名字
永远打着红色领结的老顽童
PostgreSQL界的领袖级人物
经过大家的投票选择（PostgreSQL国际专家系列直播：请选出你最想听的分享主题），《PostgreSQL大咖面对面：Bruce Momjian》第一期的分享主题选定为 “未来的PostgreSQL分片——探讨分片的优点和未来PostgreSQL分片实现的需求” 。除了原汁原味的英文技术分享（聊天栏内实时同步中文概要），我们还准备了10个PostgreSQL定制迷你加湿器，直播现场抽奖送出。
北京时间5月16日（周六）上午9点
跨越太平洋的技术直播
扫码报名，抓紧上车
关于我们
中国开源软件推进联盟PostgreSQL分会（简称：中国PG分会）于2017年成立，由国内多家PostgreSQL生态企业所共同发起，业务上接受工信部中国电子信息产业发展研究院指导。中国PG分会是一个非盈利行业协会组织。我们致力于在中国构建PostgreSQL产业生态，推动PostgreSQL产学研用发展。
技术文章精彩回顾 PostgreSQL学习的九层宝塔 PostgreSQL职业发展与学习攻略 搞懂PostgreSQL数据库透明数据加密之加密算法介绍 一文读懂PostgreSQL-12分区表 一文搞懂PostgreSQL物化视图 PostgreSQL源码学习之：RegularLock Postgresql源码学习之词法和语法分析 2019，年度数据库舍 PostgreSQL 其谁? Postgres是最好的开源软件 PostgreSQL是世界上最好的数据库 从Oracle迁移到PostgreSQL的十大理由 从“非主流”到“潮流”，开源早已值得拥有 PG活动精彩回顾 创建PG全球生态！PostgresConf.CN2019大会盛大召开 首站起航！2019“让PG‘象’前行”上海站成功举行 走进蓉城丨2019“让PG‘象’前行”成都站成功举行 中国PG象牙塔计划发布，首批合作高校授牌仪式在天津举行 群英论道聚北京，共话PostgreSQL 相聚巴厘岛| PG Conf.Asia 2019 DAY0、DAY1简报 相知巴厘岛| PG Conf.Asia 2019 DAY2简报 独家|硅谷Postgres大会简报 PostgreSQL线上沙龙第一期精彩回顾 PostgreSQL线上沙龙第二期精彩回顾 PostgreSQL线上沙龙第三期精彩回顾 PostgreSQL线上沙龙第四期精彩回顾 PostgreSQL线上沙龙第五期精彩回顾 PostgreSQL线上沙龙第六期精彩回顾 PG培训认证精彩回顾 中国首批PGCA认证考试圆满结束，203位考生成功获得认证！ 中国第二批PGCA认证考试圆满结束，115位考生喜获认证！ 重要通知：三方共建，中国PostgreSQL认证权威升级！ 近500人参与！首次PGCE中级、第三批次PGCA初级认证考试落幕！ 通知：PostgreSQL技术能力电子证书上线！ 2020年首批 | 中国PostgreSQL初级认证考试圆满结束
