mongodb $lookup 之后再$project_比较MongoDB与PostgreSQL的JOIN

作者：阿尔瓦罗•埃尔南德斯( Álvaro Hernández)

1.OnGres首席执行官，2019年被AWS授予数据英雄。 2.Alvaro是西班牙马德里一个热情的数据库和软件开发人员。 3.他创立并担任OnGres的首席执行官，OnGres是一家旨在颠覆数据库市场的PostGres初创公司。 4.二十年来，他一直致力于PostgreSQL和数据库的研发。研发包括一些“疯狂”的项目，比如花4万美元在AWS上测试和比较PostgreSQL和MongoDB。 5.Alvaro是开源倡导者和开发者，是PostgreSQL社区的知名成员，他为PostgreSQL基金会和西班牙PostgreSQL用户组的建立做出了贡献。 6.你可以发现他经常在PostgreSQL、数据库、云计算和Java会议上发言。 7.他定期参加AWS的当地聚会，并在会上发言。阿尔瓦罗每年大约环游地球3到4次。 8.到2020年，他将完成100次科技演讲的里程碑。 翻译：魏 波   编辑：孙祥斌

准备工作

在这篇博客中，我们对比了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 e                        inner join                        department as d                        on e.department = d.dnamegroup by        dname;┌───────┬───────┐│ dname │  sum │├───────┼───────┤│ Shoe  │ 22000 ││ Toy   │ 15000 │└───────┴───────┘

当员工可以在多个部门工作时，通常将使用表2中的表示。上面的查询可以轻松转换为： 

select          dname, sum(salary * dedication_pct * 0.01)from            employee                        inner join                        works_in                        using (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            employee                        inner join                        works_in                        using (ename)                                right outer join                                department                                using (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产学研用发展。

欢迎投稿

做你的舞台，show出自己的才华 。

投稿邮箱：partner@postgresqlchina.com

                                 ——愿能安放你不羁的灵魂

技术文章精彩回顾PostgreSQL学习的九层宝塔PostgreSQL职业发展与学习攻略搞懂PostgreSQL数据库透明数据加密之加密算法介绍一文读懂PostgreSQL-12分区表一文搞懂PostgreSQL物化视图PostgreSQL源码学习之：RegularLockPostgresql源码学习之词法和语法分析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初级认证考试圆满结束