188bet.com社区 主页 业界资讯 开源资讯 检查内容

“王者对战”之 MySQL 8 vs PostgreSQL 10

2018-6-18 18:24| 发布者: joejoe0332| 检查: 59820| 议论: 1|原作者: 雪落无痕xdj, 无若, LinuxTech, Tocy, kevinlinkai|来自: oschina

摘要: 已然 MySQL 8 和 PostgreSQL 10 现已发布了,现在是时分回忆一下这两大开源联系型数据库是怎么互相竞赛的。在这些版别之前,人们遍及以为,Postgres 在功用集体现更超卓,也因其“学院派”风格而备受称誉,MySQL 则 ...
已然 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时,每个人都忧虑他们会销毁这个产品,但在曩昔的十年里,这并不是现实。现实上,在收买之后,开展反倒加快了。而 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 等现代文件体系支撑的。

更新的开支

另一个常常被疏忽的特性,可是对功用有很大的影响,而且或许是最具争议的论题,是更新。

这也是Uber抛弃Postgres的另一个原因,这激起了许多Postgres的支撑者来辩驳它。

两者都是MVCC数据库,它们能够阻隔多个版别的数据。

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

在Postgres中,当您测验更新时,整个行有必要被仿制,以及指向它的索引条目也被仿制。这在必定程度上是由于Postgres不支撑集合索引,所以从索引中引证的一行的物理方位不是由逻辑键笼统出来的。

为了处理这个问题,Postgres运用了堆上元组(HOT),在或许的状况下不更新索引。可是,假如更新满足频频(或许假如一个元组比较大),元组的前史能够很简单地超越8 KB的页面巨细,跨过多个页面并约束该特性的有效性。修剪和/或碎片收拾的时刻取决于启发式处理方案。别的,设置不超越100的填充参数会下降空间功率——这是一种很难在创立表时考虑的折衷方案。

这种约束更深化; 由于索引元组没有关于业务的任何信息,所以直到9.2之前一向不能支撑仅索引扫描。 它是一切首要数据库(包含MySQL,Oracle,IBM DB2和Microsoft 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的根底

  • 快毕业了,没作业经历,
    找份作业好难啊?
    赶忙去人才芯片公司锻炼吧!!

最新议论

关于LUPA|人才芯片工程|人才招聘|LUPA认证|LUPA教育|188bet.com社区 ( 浙B2-20090187  

回来顶部