主要区别及示例

简而言之，Postgres和MySQL之间的主要区别实际上归结为主索引和辅助索引的实现方式以及数据的存储和更新方式。

让我们进一步探讨这个问题。

但首先...基础知识

索引是一种数据结构（主要是B+树），允许通过多层节点进行键的搜索，数据库将其实现为页面。树的遍历允许消除不包含结果的页面，并缩小包含结果的页面的范围。这一过程一直持续到找到包含键的叶子页面。

叶子节点或页面包含有序键及其值的列表。当找到一个键时，可以获取其值，并且页面会被缓存在数据库的共享缓冲区中，希望未来的查询可能会请求相同页面中的键。

这最后一句是理解数据库工程、管理、编程和建模的基本原则。了解查询是否命中页面中相邻的键将最大程度地减少I/O并提高性能。

B+树索引中的键是创建索引所在表的列（或多个列），而值在Postgres和MySQL中的实现方式有所不同。让我们探讨一下Postgres和MySQL中值的含义。

MySQL

在主索引中，值是带有所有属性*的完整行对象。这就是为什么主索引通常被称为聚簇索引或我更喜欢的术语"索引组织表"。这意味着主索引就是表本身。

*注意，对于行存储，这是正确的。数据库可能使用不同的存储模型，如列存储、图形或文档存储，从根本上讲，这些也可以作为潜在的值。

如果在主索引中查找一个键，你会找到包含该键的页面和它的值，该值是该键对应的完整行，不需要额外的I/O操作来获取其他列。

在二级索引中，键是你索引的列（或多个列），而值是指向实际存储完整行位置的指针。二级索引叶子页面的值通常是主键。

这就是MySQL的情况。在MySQL中，所有的表都必须有一个主索引，而所有额外的二级索引都指向主键。如果你在MySQL表中不创建主键，系统会为你自动创建一个。

Postgres

在Postgres中，严格来说没有主索引，所有的索引都是二级索引，它们都指向加载在堆中的数据页中由系统管理的元组标识符（tuple ids）。堆中的表数据是无序的，不像主索引叶子页是有序的。因此，如果你插入了1-100行，并且它们都在同一页中，然后后来更新了1-20行，这20行可能会跳转到另一页，并且变得无序。而在聚簇主索引中，插入操作必须按照键的顺序插入到相应的页中。这就是为什么Postgres表通常被称为"堆有序表"而不是"索引组织表"。

需要注意的是，在Postgres中，更新和删除实际上是插入操作。每次更新或删除都会创建一个新的元组标识符（tuple id），而旧的元组标识符则保留为了多版本并发控制（MVCC）的原因。我稍后会在本文中探讨这个问题。

事实上，仅仅使用元组标识符是不够的。实际上，我们需要同时知道元组标识符和页面编号，这被称为c_tid。想一想，仅仅知道元组标识符是不够的，我们需要知道元组所在的页。这是在MySQL中不需要做的事情，因为我们实际上是通过查找来找到主键所在的页。而在Postgres中，我们只需要进行一次I/O操作就可以获取到完整的行数据。

查询费用

请参考以下示例中的表格。

#TABLE T;
#PRIMARY INDEX ON PK AND SECONDARY INDEX ON C2, NO INDEX ON C1
# C1 and C2 are text
# PK is integer

| PK | C1 | C2 |
|----|----|----|
| 1  | x1 | x2 |
| 2  | y1 | y2 |
| 3  | z1 | z1 |

让我们比较一下MySQL和Postgres中发生的情况。

SELECT * FROM T WHERE C2 = 'x2';

在MySQL中，执行这个查询将会产生两次B+树查找。首先，我们需要使用二级索引查找x2的主键，找到主键值为1，然后再使用主索引进行另一次查找，找到完整的行数据，因此返回了所有属性（因此有*号）。

在Postgres中，查找任何二级索引只需要进行一次索引查找，然后进行一次常量的单个I/O操作，以获取包含完整行数据的页。一次B+树查找要比两次查找好。

为了使这个示例更加有趣，假设C2不是唯一的，并且有多个x2的条目，那么我们将会找到匹配x2的大量tids（或在MySQL中的PK）。问题是这些行标识符将位于不同的页面，导致随机读取。在MySQL中，这将导致索引查找（根据这些键的数量，查询优化器可能会选择索引扫描还是基于seek的操作），但是两个数据库都会导致许多随机I/O。

Postgres尝试通过使用位图索引扫描来最小化随机读取，将结果分组为页面而不是元组，并以尽可能少的I/O操作从堆中获取页面。然后应用额外的过滤来呈现候选行。

让我们看一个不同的查询。

SELECT * FROM T WHERE PK BETWEEN 1 AND 3;

对于对主键索引的范围查询，我认为MySQL在这方面是更好的选择，通过一次查找，我们可以找到第一个键，并在B+树链接的叶子页上遍历以找到附近的键，当我们遍历时，我们找到完整的行数据。

Postgres在这方面可能会遇到一些困难，确实，二级索引查找将在叶子页上进行相同的B+树遍历，并找到键，但它只会收集tids和页码。它的工作并没有结束。Postgres仍然需要在堆中进行随机读取，以获取完整的行数据，而这些行数据可能分布在堆中的各个位置，而不是紧凑地排列在一起，特别是如果这些行数据被更新过。

好的，我们来进行一次更新操作。

UPDATE T SET C1 = ‘XX1’ WHERE PK = 1;

在MySQL中，更新一个未建立索引的列只会导致更新包含该行的叶子页，并将其更新为新值。不需要更新其他任何二级索引，因为它们都指向的是未发生变化的主键。

在Postgres中，更新一个未建立索引的列将生成一个新的元组，并可能需要更新所有的二级索引以使用新的元组ID，因为它们只知道旧的元组ID。这会导致许多写入I/O操作。Uber在2016年对此不太满意，这也是他们从Postgres切换到MySQL的主要原因之一。

我在这里说“可能”是因为在Postgres中有一种优化方法称为HOT（仅堆元组），不要与（堆组织表）混淆，它会在二级索引中保留旧的元组ID，并在堆页头上放置一个指向新元组的链接。

数据类型的重要性

在MySQL中，选择主键数据类型非常重要，因为该键将出现在所有的二级索引中。例如，如果使用UUID作为主键，会导致所有二级索引的大小膨胀，增加存储和读取I/O操作的开销。

在Postgres中，元组ID固定为4个字节，因此二级索引中不会包含UUID值，而只包含指向堆的元组ID。

Undo日志

所有现代数据库都支持多版本并发控制（MVCC）。在简单的读已提交隔离级别中，如果事务 tx1 更新了一行但尚未提交，而同时另一个并发事务 tx2 想要读取该行，它必须读取旧的行而不是更新后的行。大多数数据库（包括MySQL）使用undo日志来实现此功能。

当事务对一行进行更改时，更改会被写入共享缓冲池中的页面，因此包含该行的页面始终具有最新的数据。然后，事务会在undo日志中记录如何撤消对行的最新更改的信息（足够构建旧状态的信息），这样基于其隔离级别仍需要旧状态的并发事务必须解析undo日志并构建旧行。

你可能会想知道将未提交的更改写入页面是否是一个好主意。如果后台进程在事务提交之前将页面刷新到磁盘，然后数据库崩溃会发生什么？这就是undo日志至关重要的地方。在崩溃后，会使用undo日志在数据库启动时撤消未提交的更改。

不可否认，对于长时间运行的事务，undo日志会对其他正在运行的事务产生影响。需要更多的I/O操作来构建旧状态，并且undo日志可能会满，导致事务失败的可能性。

在某种情况下，我曾经看到一个数据库系统在运行了一个持续3小时的未提交长事务后，需要一个多小时才能从崩溃中恢复。是的，要尽量避免长时间的事务。

Postgres在这方面处理方式完全不同，每次更新、插入和删除都会得到一份具有新的元组ID的新行副本，并附带有关创建该元组的事务ID和删除该元组的事务ID的提示。因此，Postgres可以安全地将更改写入数据页面，并且并发事务可以根据其事务ID读取旧的或新的元组。聪明的设计。

当然，没有解决方案是没有问题的。我们实际上已经谈论了在二级索引上创建新元组ID的代价。此外，如果所有正在运行的事务ID都大于删除元组的事务ID，则Postgres需要清除不再需要的旧元组。

进程与线程

MySQL使用线程，Postgres使用进程，在这两种选择中都有各自的优缺点。

在数据库系统中，我更喜欢线程而不是进程。因为线程更轻量级，并共享其父进程的虚拟内存地址。与较小的线程控制块（TCB）相比，进程带来了专用虚拟内存和更大的控制块（PCB）的开销。

如果我们最终要共享内存并处理互斥锁和信号量，为什么不使用线程呢？这只是我的个人观点。

总结

你可以选择适合你的数据库系统。真正重要的是将你的使用情况和查询进行分解，了解每个数据库的功能，看看哪些适合你，哪些不适合你。

这里没有对错之分。