Postgres 和 MySQL 有什么区别？炒股炒股配资
简单来说，这两种数据库的核心差异，主要体现在主键索引和二级索引的实现方式，以及底层的数据存储与更新机制上。
接下来，我们就来详细看看这两者的不同之处。
一、索引
索引是一种用于加速查询的数据结构，通常采用 B+ 树实现。这种结构通过多层节点进行键值查找，数据库内部通常以“页（page）”的形式组织这些节点。查找过程中，系统会从根节点出发，逐层遍历树结构，逐步排除不包含目标数据的页面，直到最终定位到包含目标键的叶子页面。
叶子节点中存储的是有序的键（key）以及对应的值（value）。一旦定位到目标键，便可直接获取相应的值。与此同时，该页面会被缓存在数据库的共享缓冲区中，以便后续查询可以重用，从而提升查询效率。
在 B+ 树索引中，“键”指的是创建索引时指定的列，而“值”是什么，不同的数据库有不同的实现方式。接下来，我们就来看看 Postgres 和 MySQL 在这方面的具体差异。
1、MySQL
在 MySQL 中，主键索引的“值”其实就是整行数据本身，即该行的所有字段内容。这也是为什么主键索引通常被称为“聚簇索引”。
需要说明的是，这里的描述是基于行存储的数据库系统。对于采用列存储、图数据库或文档型数据库的系统来说，由于存储模型不同，“值”的定义也会有所区别。
当通过主键索引查找某个键对应的数据时，只需定位到该键所在的页面，就可以直接获取整行数据，无需额外的 I/O 操作去读取其他列，因为其值包含了整行数据本身。
而在二级索引中，键是你创建索引时指定的列，但“值”不再是整行数据，而是一个指针，用于定位这条完整数据所在的位置。通常来说，二级索引叶子节点中的值就是对应行的主键值。
也正因如此，MySQL 要求每张表必须有一个主键索引，所有二级索引最终都要通过主键来定位数据。如果没有显式定义主键，MySQL 会自动为你生成一个隐藏主键。
2、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 是整数。
现在，我们对比在 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）更大，资源开销更高。
作者丨上善若水炒股炒股配资
科元网配资提示：文章来自网络，不代表本站观点。
相关文章
热点资讯
