MySQL在Uber的华丽转身:Postgres输在哪?【转】
个人其它平台技术文章:
- 知乎ID:砖一块一块搬
- 小红书ID:码农有道
在知乎上有同学提问关于 Postgres 和 MySQL 的区别,原本只是简单的回答,结果越讲越多,干脆整理成一篇文章分享出来。
简单来说,这两种数据库的核心差异,主要体现在主键索引和二级索引的实现方式,以及底层的数据存储与更新机制上。
接下来,我们就来详细看看这两者的不同之处。
索引
索引是一种用于加速查询的数据结构,通常采用 B+ 树实现。这种结构通过多层节点进行键值查找,数据库内部通常以“页(page)”的形式组织这些节点。查找过程中,系统会从根节点出发,逐层遍历树结构,逐步排除不包含目标数据的页面,直到最终定位到包含目标键的叶子页面。
叶子节点中存储的是有序的键(key)以及对应的值(value)。一旦定位到目标键,便可直接获取相应的值。与此同时,该页面会被缓存在数据库的共享缓冲区中,以便后续查询可以重用,从而提升查询效率。
在 B+ 树索引中,“键”指的是创建索引时指定的列,而“值”是什么,不同的数据库有不同的实现方式。接下来,我们就来看看 Postgres 和 MySQL 在这方面的具体差异。
MySQL
在 MySQL 中,主键索引的“值”其实就是整行数据本身,即该行的所有字段内容。这也是为什么主键索引通常被称为“聚簇索引”。
需要说明的是,这里的描述是基于行存储的数据库系统。对于采用列存储、图数据库或文档型数据库的系统来说,由于存储模型不同,“值”的定义也会有所区别。
当通过主键索引查找某个键对应的数据时,只需定位到该键所在的页面,就可以直接获取整行数据,无需额外的 I/O 操作去读取其他列,因为其值包含了整行数据本身。
而在二级索引中,键是你创建索引时指定的列,但“值”不再是整行数据,而是一个指针,用于定位这条完整数据所在的位置。通常来说,二级索引叶子节点中的值就是对应行的主键值。
也正因如此,MySQL 要求每张表必须有一个主键索引,所有二级索引最终都要通过主键来定位数据。如果没有显式定义主键,MySQL 会自动为你生成一个隐藏主键。
Postgres
在 Postgres 中,从技术上讲并没有“主键索引”的概念,所有索引本质上都是二级索引。它们都指向系统管理的 tuple ID(元组 ID),根据这些 ID 再定位到实际存储在堆中的数据页。需要注意的是,堆中的表数据是无序的,并不像聚簇索引的叶子节点那样按键值顺序排列。
举个例子,如果你依次插入第 1 到第 100 行数据,这些行可能会被存储在同一个页面中;但如果你随后更新了第 1 到第 20 行,这些更新后的数据很可能会被写入到其他页面,从而导致数据的物理分布变得无序。
而在 MySQL 的聚簇索引中,插入操作需要保证数据按键值(也就是索引字段值)顺序写入,这就限制了数据的物理排列方式。因此,Postgres 中的表通常被称为“堆组织表(heap organized tables)”,而不是“索引组织表(index organized tables)”。
此外需要特别注意的是,在 Postgres 中,更新和删除操作本质上其实都是插入操作。每次执行更新或删除时,系统都会生成一个新的元组 ID(tuple ID),而原有的元组 ID 会被保留下来,用于支持 MVCC(多版本并发控制)机制。这个细节我们稍后会在文章中进一步探讨。
实际上,仅有元组 ID 并不足以定位具体数据,还需要知道该元组所在的数据页编号。这两个信息组合在一起,称为 c_tid。想一想,如果只有元组 ID 而不知道它在哪一页,是无法直接定位数据的。
由于在 Postgres 中,索引只保存元组的位置信息,因此必须多执行一次 I/O 操作来加载对应的数据页,才能获取完整的行数据。
查询代价
我们来看下面这张示例表:
# 表 T;
# 主键列 PK 上有主索引,C2 列上有二级索引,C1 没有索引;
# C1 和 C2 是文本类型,PK 是整数。
| PK | C1 | C2 |
|----|----|----|
| 1 | x1 | x2 |
| 2 | y1 | y2 |
| 3 | z1 | z1 |
现在,我们对比在 MySQL 和 Postgres 中执行以下 SQL 查询时的差异:
SELECT * FROM T WHERE C2 = 'x2';
在 MySQL 中,这条查询会涉及两次 B+ 树查找操作。首先,通过 C2 列的二级索引查找字段 C2 值为 'x2' 的记录,获取其对应的主键值 1;接着再通过主键索引查找主键为 1 的那一行数据,从而获取整行记录( * 表示所有的字段)。
有些人可能会认为,这只是两次 I/O 操作,其实并非如此。B+ 树查找的时间复杂度是 O(logN),当索引规模较大时,一次查找可能涉及多个节点,每个节点对应一个页面,因此可能触发多次 I/O 操作。
而在 Postgres 中,执行这条查询时,首先通过 C2 列的二级索引查找匹配的元组 ID,然后进行一次堆访问,从堆中加载包含完整行数据的页面。
从访问路径来看,大多数情况下,一次索引查找加一次堆访问要比两次 B+ 树查找更高效,也就是说,Postgres 在这种场景下的查询性能往往优于 MySQL。
为了让这个例子更贴近实际,我们进一步假设:C2 列的值并不唯一,也就是说,可能有多行记录的 C2 值都是 'x2'。在这种情况下,查询过程中会返回多个匹配的元组 ID(在 MySQL 中则是多个主键值)。
问题在于,这些匹配的行可能分布在多个不同的数据页上,导致大量随机读操作。在 MySQL 中,这意味着要重复进行多次主键索引查找(当然,查询优化器也可能根据匹配记录数量,选择走索引扫描而非逐条查找),但无论是在 MySQL 还是 Postgres 中,这种情况最终都不可避免地带来频繁的随机 I/O。
为尽量减少这种随机访问,Postgres 会采用 位图索引扫描(Bitmap Index Scan) 的方式来优化查询流程:首先将所有匹配结果按照页面而非单条元组进行聚合,然后一次性批量加载这些数据页,尽可能降低 I/O 次数。接下来再在内存中进行过滤,最终返回满足条件的记录。
接下来,我们来看一个查询的例子。
SELECT * FROM T WHERE PK BETWEEN 1 AND 3;
在主键索引上的范围查询方面,MySQL 的表现更为出色。它只需进行一次查找,定位到第一个匹配的键,然后沿着 B+ 树叶子节点的链表依次向后遍历,获取相邻的键,并在遍历过程中直接读取对应的整行数据。
相比之下,Postgres 在这方面就显得吃力一些。虽然它的二级索引同样会在 B+ 树的叶子节点上进行遍历,找到所有匹配的键,但它只会收集对应的元组 ID 和页面信息,但此时工作并未就此完成。Postgres 还需要执行额外的随机读操作,从堆中加载这些元组对应的完整行数据。而这些数据行很可能分布在堆的不同位置,尤其是在频繁更新的情况下,数据往往不会连续、紧凑地存放在一起。
对于更新频繁的场景来说,这正是 Postgres 的一项劣势。因此,为表设置合适的 FillFactor(填充因子)非常重要,它可以在一定程度上可以缓解数据分散带来的性能问题。
接着,我们来看一个更新操作的例子:
UPDATE T SET C1 = 'XX1' WHERE PK = 1;
在 MySQL 中,如果更新的是一个未被索引的列,那么只需在主键索引的叶子节点中,直接修改该行对应的字段值即可。由于所有的二级索引都通过主键进行定位,而主键值本身没有发生变化,因此不需要更新任何索引结构。
而在 Postgres 中,即使更新的是一个没有索引的列,系统也会生成一个新的元组并赋予新的元组 ID,这意味着,所有原本指向旧元组的二级索引项都需要更新为指向新元组的位置。换句话说,虽然索引列本身并未发生变化,但由于底层的元组位置发生了改变,相关索引也“可能”需要更新,从而引发大量的写入 I/O。
早在 2016 年,Uber 就曾明确表达过对这一机制的不满,这也是他们将数据库从 Postgres 迁移到 MySQL 的主要原因之一。
这里之所以说“可能”需要更新所有二级索引,是因为 Postgres 中存在一个名为 HOT(Heap Only Tuple)的优化机制。
这个优化的原理是:在满足一定条件的情况下,允许二级索引保留原有的元组 ID,而不立即更新为新生成的元组 ID。此时,Postgres 会在堆页面内,通过在旧元组和新元组之间建立一个链表,从旧元组跳转到新元组,实现不同版本之间的关联。这样就可以避免更新索引,从而减少写入 I/O 的开销。
数据类型很重要
在 MySQL 中,主键的数据类型至关重要,因为所有二级索引都需要存储对应的主键信息。举例来说,如果主键使用的是 UUID,那么每条二级索引记录中都要包含这个较长的主键值,导致二级索引变得臃肿,不仅占用更多的存储空间,也会带来更高的读 I/O 开销。
而在 Postgres 中,二级索引并不存储主键的实际值,而是统一使用固定大小为 4 字节的元组 ID(tid)来指向堆中的数据。正因如此,即使主键是 UUID,二级索引的大小也不会受到影响。
撤销日志(Undo Logs)
几乎所有现代数据库都支持多版本并发控制(MVCC)。以最常见的“读已提交”(Read Committed)隔离级别为例,如果一个事务 tx1 对某一行数据进行了更新但尚未提交,而此时另一个并发事务 tx2 试图读取这行数据,那么它应当读取更新前的旧值,而不是尚未提交的新值。
大多数数据库(包括 MySQL)通过撤销日志(Undo Logs)来实现这一机制。
当某个事务修改了一行数据时,变更会直接写入共享缓冲池中的页面,也就是说,该页面始终保存的是最新的数据。随后,事务会将“如何撤销这次变更”的信息写入撤销日志(Undo Log),以便在需要时可以还原该行数据的旧状态。这样,当并发事务基于自身的隔离级别需要读取旧版本数据时,系统就可以通过解析 Undo Log 构造出对应的旧数据行。
你可能会疑惑:将未提交的修改直接写入页面,真的安全吗?如果后台进程把这个页面刷入磁盘,而事务还没提交,此时数据库突然崩溃了怎么办?
这正是Undo Log存在的意义。在数据库启动时,如果检测到之前发生了崩溃,会在恢复阶段利用 Undo Log 回滚那些未提交的变更,从而确保数据一致性不被破坏。
当然,Undo Log 会对并发事务带来额外的开销,尤其是在存在长事务的情况下。为了构造旧版本数据,系统需要执行额外的 I/O 操作,而且Undo Log 也有可能被写满,从而导致事务失败。
我曾遇到过一个真实案例:某数据库系统在崩溃恢复时,光是回滚一个运行了 3 小时但未提交的长事务,,就耗费了一个多小时。由此可见,尽量避免长事务,是非常有必要的。
而 Postgres 的处理方式则完全不同。每一次更新、插入或删除操作,都会生成该行数据的一个新副本,并分配一个新的元组 ID(tuple ID),同时记录创建和删除该元组的事务 ID。借助这些信息,Postgres 可以安全地将变更写入数据页,并允许并发事务根据自身的事务 ID 判断应读取旧版本还是新版本的数据。这个设计非常巧妙。
当然,再巧妙的方案也并非没有代价。我们之前就提到过,为每次变更生成新的元组 ID,会对二级索引带来额外开销。此外,Postgres 还需要在适当时机清理那些不再需要的旧元组,这项清理工作则由 Vacuum 机制负责执行。
Processes vs Threads
MySQL 使用的是线程模型,而 Postgres 则采用的是进程模型。两者各有优劣,我在另一篇文章中已做过详细分析。就个人而言,在数据库系统中,我更倾向于使用线程。原因很简单,线程更轻量,而且能共享父进程的虚拟内存地址空间。而进程则需要独立的虚拟内存,其进程控制块(PCB)也比线程控制块(TCB)更大,资源开销更高。
转自
MySQL在Uber的华丽转身:Postgres输在哪?
https://mp.weixin.qq.com/s/BEoOsLXQtwKchB_Wk45Bvw?clicktime=1753496836&enterid=1753496836&exptype=unsubscribed_card_recommend_article_u2i_mainprocess_coarse_sort_pcfeeds&ranksessionid=1753496829_1&scene=169&subscene=200