既然MySQL8和PostgreSQL10已经发布，是时候回顾一下这两个开源关系数据库如何相互竞争了。在这些版本之前，人们普遍认为Postgres更擅长特性集并因其“学术”风格而受到称赞，而MySQL更擅长大规模并发读/写。但随着他们的最新版本，两者之间的差距已大大缩小。功能比较让我们从大家都喜欢谈论的“时髦”功能说起。特性MySQL8PostgreSQL10查询和分析公用表表达式(CTE)?新?窗口函数?新?数据类型JSON支持?改进?GIS/SRS?改进?全文搜索??可扩展性逻辑复制??新的半同步复制??新的声明性分区??新曾经有人说MySQL最适合在线交易，而PostgreSQL最适合分析过程，但现在不是了。公用表表达式(CTE)和窗口函数是选择PostgreSQL的主要原因。但是现在，通过引用同一张表中的boss_id递归遍历employee表，或者在排序结果中找到中位数（或50%）在MySQL上不再是问题。PostgreSQL中的复制缺乏配置灵活性，这就是Uber转向MySQL的原因。但是现在，有了逻辑复制功能，可以通过创建新版本的Postgres并切换到它来进行零停机升级。在巨大的时间序列事件表中截断陈旧的分区也容易得多。在功能方面，这两个数据库现在是一致的。差异现在，我们只剩下一个问题-选择一个而不是另一个的原因是什么？生态系统是一个因素。MySQL有一个充满活力的生态系统，包括MariaDB、Percona、Galera等，以及InnoDB以外的存储引擎，但这也可能令人困惑。Postgres的高端选项有限，但这会随着最新版本中引入的新功能而改变。治理是另一个因素。当Oracle（或最初的SUN）收购MySQL时，每个人都担心它会扼杀该产品，但在过去十年中，情况并非如此。事实上，收购之后，MySQL的发展加速了。Postgres在工作管理和协作社区方面拥有丰富的经验。基础架构不会经常更改，虽然最近没有对此进行详细讨论，但值得重新考虑。回顾一下：特性MySQL8PostgreSQL10架构单进程多进程并发多线程fork(2)表结构聚簇索引堆页压缩TransparentTOASTupdateIn-Place/RollbackSegmentsAppendOnly/HOTGarbagecollectionclearthreadautomaticallyclearsprocesstransactionsLogREDOLog(WAL)WALCopyLogSeparate(Binlog)WALProcessvsThread当Postgres生成一个子进程来建立连接时，每个连接最多可以占用10MB。相比MySQL的threadedjoin模型，内存压力更大，而且在64位平台上，线程默认栈大小为256KB（当然，thread-localsortbuffers等使得这个开销不那么显着，即使在这仍然是不能忽略的情况）。尽管“写时复制”保留了一些与父进程共享的不可变内存状态，但是当您有1000多个并发连接时，基于进程的体系结构的基本开销会很大，并且它可能是容量规划最重要的因素之一.也就是说，如果您在30台服务器上运行Rails应用程序，每台服务器有16个CPU核心和32个线程，那么您有960个连接。可能只有不到0.1%的应用程序会落在这个范围之外，但要记住这一点。聚簇索引与堆表聚簇索引是一种表结构，其中的行直接嵌入到其主键的b树结构中。（非集群）堆是一种常规表结构，其中填充了与索引分开的数据行。使用聚集索引，当您通过其主键查找记录时，可以在单个I/O中检索整行，而使用非聚集索引，您总是需要查找引用，至少需要两次I/O奥斯。影响可能非常大，因为外键引用和JOIN会触发主键查找，这将导致大量查询。聚簇索引的一个理论上的缺点是，当你使用二级索引进行查询时，需要遍历两倍的树节点，首先扫描二级索引，然后遍历聚簇索引，它也是一棵树。但是，如果您遵循现代表设计的惯例，将自动递增整数作为主键[1]——它被称为代理键——那么几乎总是需要聚集索引。更重要的是，如果您执行大量ORDERBYid操作来检索最近（或最旧）的N条记录，我认为这是适用的。Postgres不支持聚集索引，MySQL(InnoDB)不支持堆。但无论如何，如果你有很多内存，差异应该很小。页面结构和压缩Postgres和MySQL都有基于页面的物理存储。(8KBvs16KB)PostgreSQL物理存储的介绍页面结构如上图所示。它包含一些我们不打算在这里讨论的条目，但它们包含有关该页面的元数据。条目后面的项目是一个数组标识符，由指向元组或数据行的(offset,length)对组成。在Postgres中，同一条记录的多个版本可以通过这种方式存储在同一个页面中。MySQL的表空间结构与Oracle类似，有层、节、页、行层等多个层次。此外，它还有一个单独的撤消段，称为“回滚段”。与Postgres不同，MySQL会将同一记录的多个版本保存在一个单独的区域中。如果有一个页面，其中一行必须适合两个数据库，这意味着一行必须小于8KB（至少2行必须适合MySQL的页面，恰好是16KB/2=8KB）。那么当列中有一个大的JSON对象时会发生什么？Postgres使用TOAST，这是一种专用的影子表存储。选择行和列时，会拉出大对象。换句话说，大量的黑匣子不会污染您宝贵的缓存。它还支持TOAST对象的压缩。得益于高端SSD存储提供商Fusio-io的贡献，MySQL有一个更复杂的功能，称为透明页面压缩。它旨在更好地利用SSD，其中写入量与设备的使用寿命直接相关。MySQL的压缩不仅适用于页面外的大对象，而且适用于所有页面。它通过在稀疏文件中使用打孔来实现这一点，现代文件系统（如ext4或btrfs）支持这种打孔。有关更多详细信息，请参阅：在FusionIO上使用新的MariaDB页面压缩显着提高性能。(https://mariadb.org/significant-performance-boost-with-new-mariadb-page-compression-on-fusionio/)updateoverhead另一个经常被忽视但对性能有很大影响的特性，可能还有最有争议的话题，是更新。这是Uber放弃Postgres的另一个原因，这引起了很多Postgres支持者的反驳。MySQL可能适合Uber，但它可能不适合你https://dzone.com/articles/on-ubers-choice-of-databasesPostgreSQL对Uber的回应(PDF)http://thebuild.com/presentations/uber-perconalive-2017.pdf都是MVCC数据库，可以隔离多个版本的数据。为此，Postgres将旧数据保留在堆上直到它被清空，而MySQL将旧数据移动到称为回滚段的单独区域。在Postgres中，当您尝试更新时，必须复制整行，以及指向它的索引条目。这部分是因为Postgres不支持聚集索引，所以从索引引用的行的物理位置不是由逻辑键抽象的。为了解决这个问题，Postgres使用on-heaptuples(HOT)并且尽可能不更新索引。但是，如果更新足够频繁（或者元组相对较大），元组历史记录很容易超过8KB页面大小，跨越多个页面并限制此功能的实用性。修剪和/或碎片整理的时间取决于启发式解决方案。此外，将填充参数设置为不高于100会降低空间效率——这是创建表时很难考虑的权衡。这个限制更进一步，因为索引元组没有任何关于事务的信息，直到9.2才支持仅索引扫描。它是所有主要数据库（包括MySQL、Oracle、DB2和SQLServer）都支持的最古老和最重要的优化方法之一。但是即使是最近的版本，当有很多UPDATE设置可见性映射中的脏位时，Postgres也不完全支持仅索引扫描，并且在我们不需要时经常选择Seq扫描。在MySQL上，更新就地发生，旧行数据缓存在称为回滚段的单独区域中。结果是您不需要VACUUM，并且提交非常快，而回滚相对较慢，这是大多数用例的理想折衷。它也足够聪明，可以尽快清除历史记录。如果事务的隔离级别设置为READ-COMMITTED或更低，则在语句完成时清除历史记录。交易记录的大小不影响主页。碎片化是一个伪命题。因此，MySQL的整体性能更好、更可预测。GarbageCollection垃圾收集在Postgres中对VACUUM的开销很大，因为它主要工作在堆区，造成直接的资源竞争。感觉就像编程语言中的垃圾回收——它随时会挡路并阻止你。为具有数十亿条记录的表配置autovacuum仍然是一个挑战。Purge在MySQL上也可能非常繁重，但由于它使用专用线程在单独的回滚段中运行，因此它不会以任何方式影响读取并发性。即使使用默认配置，膨胀的回滚段减慢执行速度的可能性也非常低。拥有数十亿条记录的繁忙表不会导致MySQL上的历史数据膨胀，文件大小对存储和查询性能的影响几乎是可预测和稳定的。日志和副本Postgres具有称为预写日志(WAL)的单源事务历史记录。它一直被用于副本，一种称为逻辑复制的新功能可以快速将二进制内容解码为更易消化的逻辑语句，从而允许对数据进行细粒度控制。MySQL维护两个独立的日志：1.InnoDB特定的重做日志，用于崩溃恢复；2.用于复制和增量备份的二进制日志。InnoDB上的重做日志与Oracle一致，是一个免维护的循环缓冲区，不会随时间增长，只在启动时创建，大小固定。这种设计保证了在物理设备上保留了一个连续的连续区域，从而提高了性能。更大的重做日志以崩溃恢复时间为代价产生更高的性能。将新的复制功能添加到Postgres后，我觉得它们势均力敌。总结令人惊讶的是，它表明主流意见仍然存在。MySQL最适合在线交易，而PostgreSQL最适合像数据仓库一样的appendonly模式和分析过程。[2]正如我们在这篇文章中看到的，Postgres的大部分困难来自appendonly模式和冗余堆结构。Postgres的未来版本可能需要对其存储引擎进行重大改进。您不必接受我所说的-实际上在官方wiki上有关于它的讨论，这表明是时候从InnoDB中学习一些好的想法了。一次又一次人们说MySQL正在赶上Postgres，但这一次，潮流已经逆转。注意：[1]UUID作为主键是一个糟糕的想法，顺便说一句-加密随机性完全是为了消除引用的局部性而设计的，因此会降低性能。[2]当我说Postgres非常适合分析时，我是认真的：如果您不知道TimescaleDB，它是PostgreSQL之上的包装器，允许您每秒插入100万行，每台服务器1000亿行。多么疯狂的事情。难怪亚马逊选择PostgreSQL作为Redshift的基础。
