MySQL8与PG10:新版本下的较量谁更胜一筹?

风斗 等级 441 0 0

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

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

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

特性比较

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

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

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

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

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

不同之处

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

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

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

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

来复习一下:

1

进程 vs 线程

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

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

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

2

聚簇索引 vs 堆表

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

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

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

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

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

3

页结构与压缩

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

PostgreSQL物理存储的介绍

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

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

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

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

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

PostgreSQL使用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/)

4

更新的开销

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

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

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

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

一篇PostgreSQL对Uber的回应(PDF)

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

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

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

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

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

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

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

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

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

5

Garbage Collection垃圾回收

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

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

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

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

6

日志与副本

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

MySQL维护两个单独的日志:

1、用于崩溃恢复的InnoDB特定的重做日志;

2、用于复制和增量备份的二进制日志。

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

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

总结

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

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

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

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

注解:

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

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

收藏
评论区

相关推荐

postgresql和mysql哪个好
postgresql和mysql都是免费且功能强大的开源数据库,很多用户面对这两个库都会有一个问题,那就是哪一个才是最好的开源数据库,MySQL还是PostgreSQL呢?该选择哪一个开源数据库呢? postgresql和mysql哪个好 一.PostgreSQL相对于MySQL的优势 1、在SQL的标准实现上要比MySQL完善,而且功能实现比较严谨;
[DB]PostgreSQL 与 MySQL 相比,优势何在?
PostgreSQL 与 MySQL 相比,优势何在? 数据库 知乎 Pg 没有 MySQL 的各种坑 MySQL 的各种 text 字段有不同的限制, 要手动区分 small text, middle text, large text... Pg 没有这个限制, text 能支持各种大小. 按照 SQL 标准, 做 null 判断不能用
MySQL的语句执行顺序
今天遇到一个问题就是mysql中insert into 和update以及delete语句中能使用as别名吗?目前还在查看,但是在查阅资料时发现了一些有益的知识,给大家分享一下,就是关于sql以及MySQL语句执行顺序: sql和mysql执行顺序,发现内部机制是一样的。最大区别是在别名的引用上。  一、sql执行顺序 
golang实现MySQL数据库事物的提交与回滚
MySQL 事务主要用于处理操作量大,复杂度高的数据。在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。事务用来管理 insert,update,delete 语句,事务处理可以用来维护数据库的完整性,保证成批的 SQL 语句要么全部执行,要么全部不执行。 一般来说,事务是必须满足4个条件(ACID)::原子性(Atomicit
nodejs环境快速操作mysql数据库
github地址 引入依赖包 npm install dmhsqmysqldb可用于腾讯云SCF 云函数 云开发环境 效果如下 简化了mysql的使用 未经本人允许,禁止转载 安装npm install dmhsqmysqldb 使用示例快速操作mysql 错误处理尚未完善 部分错误参考mysql错误返回的均为Prom
MySQL8与PG10:新版本下的较量谁更胜一筹?
既然MySQL 8和PostgreSQL 10已经发布了,现在是时候回顾一下这两大开源关系型数据库是如何彼此竞争的。在这些版本之前,人们普遍认为,PostgreSQL在功能集表现更出色,也因其“学院派”风格而备受称赞,MySQL则更善长大规模并发读/写。但是随着它们最新版本的发布,两者之间的差距明显变小了。特性比较首先来看看我们都喜欢谈论的“时髦”功能。过去
47 张图带你 MySQL 进阶!
我们在 MySQL 入门篇主要介绍了基本的 SQL 命令、数据类型和函数,在局部以上知识后,你就可以进行 MySQL 的开发工作了,但是如果要成为一个合格的开发人员,你还要具备一些更高级的技能,下面我们就来探讨一下 MySQL 都需要哪些高级的技能
21分钟 MySQL 入门教程
21分钟 MySQL 入门教程 目录 一、MySQL的相关概念介绍(c1) 二、Windows下MySQL的配置(c2) 配置步骤(d1) MySQL服务的启动、停止与卸载(d2) 三、
MySQL基础(非常全)
MySQL基础一、MySQL概述1、什么是数据库 ? 答:数据的仓库,如:在ATM的示例中我们创建了一个 db 目录,称其为数据库2、什么是 MySQL、Oracle、SQLite、Access、MS SQL Server等 ? 答:他们均是一个软件,都有两个主要的功能: a. 将数据保存到文件或内存 b. 接收特定的命令,然后对文件进行相应
MySQL(一)MySQL基础介绍
最近的学习内容是数据库相关的一些知识,主要以MySQL为主,参考书籍——《MySQL必知必会》MySQL学习及下载地址:https://dev.mysql.com/MySQL学习使用注意事项:1、必须访问一个已有的MySQL服务器,需要一个服务器账号(一个登录名和一个口令)2、MySQL运行在所有主要平台上,包括Windows、Linux、Solaris、M
Mysql Workbench使用教程
<1 MySQL WorkbenchMySQL Workbench 为数据库管理员、程序开发者和系统规划师提供可视化的Sql开发、数据库建模、以及数据库管理功能。 <2.MySQL Workbench 的下载和安装 (1)安装最新MySql时,有是否安装MySql Workbench的选项,可选择安装。 (2)可以独立安装MySql Workbench。
Mysql中MVCC的使用及原理详解
数据库默认隔离级别:RR(Repeatable Read,可重复读),MVCC主要适用于Mysql的RC,RR隔离级别 创建一张存储引擎为testmvcc的表,sql为:CREATE TABLE testmvcc ( id int(11) DEFAULT NULL, name varchar(11) DEFAULT NULL) ENGINE\InnoDB
一文读懂一条 SQL 查询语句是如何执行的
2001 年 MySQL 发布 3.23 版本,自此便开始获得广泛应用,随着不断地升级迭代,至今 MySQL 已经走过了 20 个年头。为了充分发挥 MySQL 的性能并顺利地使用,就必须正确理解其设计思想,因此,了解 MySQL 的逻辑架构是必要的。本文将通过一条 SQL 查询语句的具体执行过程来详细介绍 MySQL 架构中的各个组件。MySQL 逻辑架构
Python中如何判断表的存在
MySQL如何判断表是否存在MySQL判断表是否存在可以使用下面语句:select from informationschema.tables where tablename 'student';select from informationschema.tables where tablename 'teacher';SQL语句说明:informati
OMG!Java高级开发岗必问知识点
目录 1.Mysql 2.CHAR 与 VARCHAR 的区别? 3.能说下myisam 和 innodb的区别吗? 4.你能说下事务的基本特性和隔离级别吗? 5.并发问题 脏读、不可重复读、幻读? 6.事务的隔离级别? 7.说说自增主键、UUID? 8.mysql 的约束分类? 9.drop、delete 与 tru