MySQL8与PG10：新版本下的较量谁更胜一筹？

作者：Kenn Ejima

原文地址：https://blog.dumper.io/showdown-mysql-8-vs-postgresql-10/

译者：雪落无痕xdj、无若、LinuxTech、Tocy、kevinlinkai

注：本文转自开源中国订阅号（oschina2013），经平台授权转载

既然MySQL 8和PostgreSQL 10已经发布了，现在是时候回顾一下这两大开源关系型数据库是如何彼此竞争的。

在这些版本之前，人们普遍认为，Postgres在功能集表现更出色，也因其“学院派”风格而备受称赞，MySQL则更善长大规模并发读/写。

但是随着它们最新版本的发布，两者之间的差距明显变小了。

特性比较

首先来看看我们都喜欢谈论的“时髦”功能。

特性 MySQL 8 PostgreSQL 10 查询 & 分析

公用表表达式 (CTEs) ✔ New ✔ 窗口函数 ✔ New ✔ 数据类型

JSON支持 ✔ Improved ✔ GIS / SRS ✔ Improved ✔ 全文检索 ✔ ✔ 可扩展性

逻辑复制 ✔ ✔ New 半同步复制 ✔ ✔ New 声明式分区 ✔ ✔ New

过去经常会说MySQL最适合在线事务，PostgreSQL最适合分析流程，但现在不是了。

公共表表达式（CTEs）和窗口函数是选择PostgreSQL的主要原因。但是现在，通过引用同一个表中的boss_id来递归地遍历一张雇员表，或者在一个排序的结果中找到一个中值（或50%），这在MySQL上不再是问题。

在PostgreSQL中进行复制缺乏配置灵活性，这就是Uber转向MySQL的原因。但是现在，有了逻辑复制特性，就可以通过创建一个新版本的Postgres并切换到它来实现零停机升级。在一个巨大的时间序列事件表中截断一个陈旧的分区也要容易得多。

就特性而言，这两个数据库现在都是一致的。

不同之处

现在，我们只剩下一个问题——选择这一个而不选另一个的原因是什么呢？

生态系统是其中一个因素。MySQL有一个充满活力的生态系统，包括MariaDB、Percona、Galera等等，以及除了InnoDB以外的存储引擎，但这也可能会令人困惑。Postgres的高端选择有限，但随着最新版本引入的新功能，这个情况会有所改变。

治理是另一个因素。当Oracle（或最初的SUN）收购MySQL时，每个人都担心会毁掉这个产品，但在过去的十年里，这并不是事实。事实上，在收购之后，MySQL的发展反而加速了。而Postgres在工作管理和协作社区方面有着丰富的经验。

基础架构不会经常改变，虽然最近没有对这方面的详细讨论，但这也是值得再次考虑的。

来复习一下：

特性 MySQL 8 PostgreSQL 10 架构 单进程 多进程 并发 多线程 fork(2) 表结构 聚簇索引 堆 页压缩 Transparent TOAST 更新 In-Place / Rollback Segments Append Only / HOT 垃圾回收 清除线程 自动清空进程 事务日志 REDO Log (WAL) WAL 复制日志 Separate (Binlog) WAL 

进程 vs 线程

当Postgres派生出一个子进程来建立连接时，每个连接最多可以占用10MB。与MySQL的线程连接模型相比，它的内存压力更大，在64位平台上，线程的默认堆栈大小为256KB（当然，线程本地排序缓冲区等使这种开销变得不那么重要，即使在不可以忽略的情况下，仍然如此）。

尽管“写时复制”保存了一些与父进程共享的、不可变的内存状态，但是当你有1000多个并发连接时，基于流程的架构的基本开销是很繁重的，而且它可能是容量规划的最重要因素之一。

也就是说，如果你在30台服务器上运行一个Rails应用，每个服务器都有16个CPU核心32线程，那么你有960个连接。可能只有不到0.1%的应用会超出这个范围，但这是需要记住的。

聚簇索引 vs 堆表

聚簇索引是一种表结构，其中的行直接嵌入其主键的b树结构中。一个（非聚集）堆是一个常规的表结构，它与索引分别填充数据行。

有了聚簇索引，当你通过主键查找记录时，单次I/O就可以检索到整行，而非集群则总是需要查找引用，至少需要两次I/O。由于外键引用和JOIN将触发主键查找，所以影响可能非常大，这将导致大量查询。

聚簇索引的一个理论上的缺点是，当你使用二级索引进行查询时，它需要遍历两倍的树节点，第一次扫描二级索引，然后遍历聚集索引，这也是一棵树。

但是，如果按照现代表设计的约定，将一个自动增量整数作为主键[1]——它被称为代理键——那么拥有一个聚集索引几乎总是可取的。更重要的是，如果你做了大量的ORDER BY id来检索最近的（或最老的）N个记录的操作，我认为这是很适用的。

Postgres不支持聚集索引，而MySQL(InnoDB)不支持堆。但不管怎样，如果你有大量的内存，差别应该是很小的。

页结构与压缩

Postgres和MySQL都有基于页面的物理存储。(8KB vs 16KB)

PostgreSQL物理存储的介绍

页结构看起来就像上图所示。它包含一些我们不打算在这里讨论的条目，但是它们包含关于页的元数据。条目后面的项是一个数组标识符，由指向元组或数据行的（偏移、长度）对组成。在Postgres中，相同记录的多个版本可以以这种方式存储在同一页面中。

MySQL的表空间结构与Oracle相似，它有多个层次，包括层、区段、页面和行层。

此外，它还有一个用于撤销的单独段，称为“回滚段”。与Postgres不同的是，MySQL将在一个单独的区域中保存同一记录的多个版本。

如果存在一行必须适合两个数据库的单个页面，这意味着一行必须小于8KB（至少有2行必须适合MySQL的页面，恰巧是16KB/2 = 8KB）。

那么，当你在一个列中有一个大型JSON对象时会发生什么呢？

Postgres使用TOAST，这是一个专用的影子表（shadow table）存储。当行和列被选中时，大型对象就会被拉出。换句话说，大量的黑盒不会污染你宝贵的缓存。它还支持对TOAST对象的压缩。

MySQL有一个更复杂的特性，叫做透明页压缩，这要归功于高端SSD存储供应商Fusio-io的贡献。它设计目的是为了更好地使用SSD，在SSD中，写入量与设备的寿命直接相关。

对MySQL的压缩不仅适用于页面外的大型对象，而且适用于所有页面。它通过在稀疏文件中使用打孔来实现这一点，这是被ext4或btrfs等现代文件系统支持的。

有关更多细节，请参见：在FusionIO上使用新MariaDB页压缩获得显著的性能提升。（https://mariadb.org/significant-performance-boost-with-new-mariadb-page-compression-on-fusionio/）

更新的开销

另一个经常被忽略的特性，但是对性能有很大的影响，并且可能是最具争议的话题，是更新。

这也是Uber放弃Postgres的另一个原因，这激起了许多Postgres支持者的反驳。

• MySQL对Uber可能是合适的，但是未必对你合适

  https://dzone.com/articles/on-ubers-choice-of-databases

• 一篇PostgreSQL对Uber的回应（PDF）

  http://thebuild.com/presentations/uber-perconalive-2017.pdf

两者都是MVCC数据库，它们可以隔离多个版本的数据。

为了做到这一点，Postgres将旧数据保存在堆中，直到被清空，而MySQL将旧数据移动到一个名为回滚段的单独区域。

在Postgres中，当你尝试更新时，整个行必须被复制，以及指向它的索引条目也被复制。这在一定程度上是因为Postgres不支持聚集索引，所以从索引中引用的一行的物理位置不是由逻辑键抽象出来的。

为了解决这个问题，Postgres使用了堆上元组（HOT），在可能的情况下不更新索引。但是，如果更新足够频繁（或者如果一个元组比较大），元组的历史可以很容易地超过8KB的页面大小，跨越多个页面并限制该特性的有效性。修剪和/或碎片整理的时间取决于启发式解决方案。另外，设置不超过100的填充参数会降低空间效率——这是一种很难在创建表时考虑的折衷方案。

这种限制更深入，因为索引元组没有关于事务的任何信息，所以直到9.2之前一直不能支持仅索引扫描。 它是所有主要数据库（包括MySQL、Oracle、DB2和SQL Server）支持的最古老，最重要的优化方法之一。 但即使使用最新版本，当有许多UPDATE在可见性映射中设置脏位时，Postgres也不能完全支持仅索引扫描，并且在我们不需要时经常选择Seq扫描。

在MySQL上，更新发生在原地，旧的行数据被封存在一个称为回滚段的独立区域中。 结果是你不需要VACUUM，并且提交非常快，而回滚相对较慢，这对于大多数用例来说是一个可取的折衷。

它也足够聪明，尽快清除历史。 如果事务的隔离级别设置为READ-COMMITTED或更低，则在语句完成时清除历史记录。

事务记录的大小不会影响主页面。碎片化是一个伪命题。因此，在MySQL上能更好、更可预测整体性能。

Garbage Collection垃圾回收

在Postgres中VACUUM上开销很高，因为它主要工作在堆区，造成了直接的资源竞争。它感觉就像是编程语言中的垃圾回收——它会挡在路上，并随时让你停下来。

为具有数十亿记录的表配置autovacuum仍然是一项挑战。

在MySQL上清除（Purge）也可能相当繁重，但由于它是在单独的回滚段中使用专用线程运行的，因此它不会以任何方式影响读取的并发性。即使使用默认配置，变膨胀的回滚段使你执行速度减慢的可能性也是很低的。

拥有数十亿记录的繁忙表不会导致MySQL上的历史数据膨胀，诸如存储上的文件大小和查询性能等事情上几乎是可以预测的并且很稳定。

日志与副本

Postgres拥有被称作预写日志（WAL）的单信源事务历史。它一直被用于副本，并且称为逻辑复制的新功能可将二进制内容快速解码为更易消化的逻辑语句，从而可对数据进行细粒度控制。

MySQL维护两个单独的日志：1、用于崩溃恢复的InnoDB特定的重做日志；2、用于复制和增量备份的二进制日志。

InnoDB上的重做日志与Oracle一致，它是一个免维护的循环缓冲区，不会随着时间的推移而增长，只在启动时以固定大小创建。 这种设计保证在物理设备上保留一个连续的连续区域，从而提高性能。 更大的重做日志产生更高的性能，但要以崩溃恢复时间为代价。

随着新的复制功能添加到Postgres，我觉得他们不分伯仲。

总结

令人惊讶的是，它证明了普遍的观点依然存在。MySQL最适合在线交易，而PostgreSQL最适合仅用于append only模式，像数据仓库一样分析过程。[2]

正如我们在这篇文章中看到的，Postgres的绝大多数难题都来自于append only模式，过于冗余的堆结构。

Postgres的未来版本可能需要对其存储引擎进行重大改进。你不必接受我所说的——实际上在官方wiki上已经有对它的讨论，这表明现在是时候从InnoDB身上学回来一些好的想法了。

人们一次又一次地说MySQL正在追赶Postgres，但是这一次，潮流已经改变。

注解：

[1] UUID作为主键是一个可怕的想法，顺便说一句——密码随机性完全是为了杀死引用的局部性而设计，因此性能会损失。

[2] 当我说Postgres特别适合分析时，我是认真的：万一你不知道TimescaleDB，它是PostgreSQL上边的一个封装，允许你每秒插入100万条数据，每台服务器有1000亿行。多么疯狂的事情。难怪Amazon会选择PostgreSQL作为Redshift的基础。

近期热文

腾讯云数据库智能化海量运维的建设与实践

Ceph运维告诉你分布式存储的那些“坑”

教你一些MySQL数据库入侵及防御方法

电商平台迎战大促季峰值的运维技巧

开源的Trafodion如何实现事务与分析一体化？

近期活动

2018 DAMS中国数据资产管理峰会