比较可靠性和速度

这是一篇尚未完工的草稿；请参阅2007 年版本以获得一致但更早的版本。

简介

多年来，行业普遍认为 MySQL 比 PostgreSQL 更快、更易于使用。PostgreSQL 被认为功能更强大，更注重数据完整性，并且在遵守 SQL 规范方面更加严格，但相应地速度更慢、使用起来更复杂。

与过去形成的许多观念一样，随着当前一代版本的发布，这些观念不再像以前那样准确。这两个系统都随着里程碑式的版本发布而发展，使得比较两者变得更加复杂。

MySQL 5.1（2008 年 12 月）发布，但仍存在许多未解决的 bug。在 2008 年，除了 Sun 的版本之外，MySQL 分裂成 Drizzle、Percona、MariaDB 和 OurDelta。

PostgreSQL 8.3（2008 年 2 月）带来了重大的性能改进，特别是在可扩展性方面。专注于提高性能一直是所有 8.X 版本的核心，直至目前的 8.3。

随着这些数据库的创新发展，每个开发社区都积极进行了更改，以解决他们各自的感知缺陷。结果是，客观地确定哪种数据库更适合特定应用程序变得更加困难。本文旨在阐明在哪些情况下 PostgreSQL 比 MySQL 更合适，尝试公平地比较这两个系统的当前生产版本，并讨论它们的优缺点。这里涵盖的主要领域是核心数据库软件的基本数据完整性和速度问题。由于你通常可以牺牲性能以换取可靠性，因此这两个主题需要一起考虑，以便对现状有一个准确的了解。

本文的观点是，当使用严肃的事务性数据库应用程序要求的高水平数据完整性比较这两个系统时，当前一代 PostgreSQL 的性能比 MySQL 更好（特别是在高用户负载和复杂查询情况下），同时在 SQL 标准合规性和丰富的功能集方面保持领先地位。我们也希望通过探讨这两个系统之间的差异，你能够理解 PostgreSQL 设计团队的基本方法是如何全面地优先考虑可靠和可预测的行为的。MySQL 实现的类似部分存在一些缝隙，这些缝隙是由于事务支持和严格模式等功能是在软件设计周期结束时添加的，而不是从一开始就作为核心部分存在的。

比较版本、功能集和重点

截至本文撰写时（2009 年 3 月），当前的生产就绪版本是 PostgreSQL 8.3 和 MySQL 5.1，本文将比较这两个版本。由于 PostgreSQL 8.1 和 8.2 目前都是支持的版本，并且具有不错的性能，因此本文中的一些评论可能将它们统称为 8.1 和 8.2。8.3 的速度略快（在某些工作负载下可能快 30%），但部署 8.2 现在仍然是一个完全可行的选择，特别是因为 8.3 在正确性方面有所提高，取消了自动转换为和从文本的转换，这要求人们测试任何遗留数据库以找出这些情况。

这两个系统目前都在测试中推出了新版本（PostgreSQL 8.4 和 MySQL 6.0），它们在本文所述的一些领域提供了增量改进，但这两个新版本中都没有出现如此重大的基本差异，因此认为本文中的通用指南不会失效。

本文没有具体讨论的是这两个产品在这些基本功能之外的领域的功能集。由于 PostgreSQL 8.3 和 MySQL 5.1 中的更改规模很大，因此许多关于此主题的文档已经过时，无法推荐。以下是一些可能有所帮助的页面：

虽然功能清单很有用，但要真正理解某些系统行为，需要对各个系统有相当深入的了解。例如，PostgreSQL 如何压缩 TOAST 数据的内部机制对用户是不可见的，但它可以在某些类型的数据中显著提高系统性能。

本文没有涉及的另一个领域是，与 PostgreSQL 相比，更多应用程序支持 MySQL 作为首选数据库，这无疑是决定这两个数据库中哪一个更适合特定情况的重要因素。可以在软件移植中跟踪将 PostgreSQL 支持添加到一些流行应用程序的工作。在考虑应用程序如何使用 MySQL 时，你应该评估的一件事是，如果它们最初的目标版本是 5.0 之前的版本，那么它们可能与该版本中引入的严格模式等较新功能不兼容。如果是这种情况，此类应用程序可能仅限于它们针对其编写的老版本的功能，可能需要进行某种移植工作才能利用现代的 MySQL 功能。

可靠性

数据完整性

在 5.0 版本之前，MySQL 当之无愧地拥有允许将不一致数据插入数据库的声誉。保证数据 MySQL 5.0 中的完整性解释了老版本 MySQL 中的问题，以及如何使用当前版本中提供的严格的 SQL 模式解决这些问题。当然，任何 MySQL 客户端都可以更改自己的 SQL 模式以覆盖此设置，结果是服务器仍然不一定强制执行这些验证约束。在当 MySQL 出现问题：需要注意的怪癖中可以找到一些关于此主题的良好示例。即使在严格模式下，仍然可以通过一些方法在 MySQL 中创建数据不一致。

PostgreSQL 一直以来都严格要求在允许数据进入数据库之前确保数据有效，客户端无法绕过这些检查。

事务和数据库引擎核心

MySQL 依赖于所谓的存储引擎来实际存储和检索来自表格的数据，每个存储引擎都有自己的一组功能和特性。让 MySQL 最初以速度而闻名的存储引擎是 MyISAM。该引擎在处理简单的查询时具有出色的读取性能，这使其在读取密集型应用程序（如涉及简单 SELECT 的 Web 应用程序）中非常快。但是，众所周知，MyISAM 比大多数严肃的数据库应用程序所能容忍的更容易出现数据损坏，并且在崩溃后，修复表格可能需要很长时间，在此期间服务器会关闭。此外，它不支持外键或允许数据库具有 ACID 属性的事务。MyISAM 在处理并发读取和更新时也存在问题，因为它只提供表格级锁定。

将 InnoDB 存储引擎集成到 MySQL 中，在数据完整性方面比 MyISAM 大大改进，添加了更强大的日志重放机制用于崩溃恢复，并启用了符合 ACID 的事务。但是，这种新方法会带来更多开销，InnoDB 表格在某些工作负载下没有 MyISAM 表格那么快（尽管 InnoDB 引擎具有一些功能，例如聚簇主键、自适应哈希索引和插入缓冲区，这些功能使其能够设计表格，使其在某些实际使用中比 MyISAM 的性能快得多）。此外，内部 MySQL 元数据表格仍然使用 MyISAM 存储，这意味着它们仍然容易受到与该存储引擎相关的传统损坏问题的困扰。这个问题通过一些复杂的锁定方法来解决，这些方法有可能使表格更改被阻塞一段时间。

你还要注意，在某些环境（通常是共享 Web 托管）中，你可能创建了一个你认为是事务安全 InnoDB 表格，但实际上却得到了非 ACID MyISAM 表格。与 MySQL 中经常发生的情况一样，这不会产生错误，而是会悄无声息地做错事。有关如何在可能运行老版本 MySQL 的系统上创建表格时确认你得到了你想要的东西的详细信息，请参阅哎呀，不支持 InnoDB 表格。

MySQL 的另一个复杂之处在于，有时不同类型的功能会发生冲突。例如，MySQL 的经典优势之一是它内置了全文索引和搜索功能，而 PostgreSQL 在 8.3 版本之前，这只是一项附加功能。不幸的是，MySQL 全文搜索只能与 MyISAM 存储引擎一起使用，结果是，如果你需要此功能，这些表格将无法参与事务或外键关系。

PostgreSQL 一直以来都专注于事务级的数据完整性，将锁定问题降到最低，并且除了硬件故障或严重配置不当之外，很难损坏数据库。

值得注意的是，数据库引擎是 PostgreSQL 的核心部分，而 InnoDB 则是目前从甲骨文公司获得许可的双重许可产品。尚不清楚甲骨文在未来会如何改变 InnoDB，因为他们与 MySQL AB 存在竞争关系，而 PostgreSQL 则不存在这种利益冲突。MySQL AB 一直致力于开发一个名为 Falcon 的新数据库引擎核心，以摆脱这种情况，但从历史上看，开发一个既快又可靠的数据库核心引擎需要多年的工作和测试，才能获得适合生产使用的成熟产品。初步基准测试表明 Falcon 存在很多需要解决的粗糙边缘。

外键

正确实现诸如数据库规范化等设计技术依赖于数据库使用外键来映射表之间关系的能力。在 MySQL 中，外键仅在 InnoDB 中受支持（以及一些处于早期开发阶段且通常不被认为已准备好投入生产使用的较新的存储引擎，例如 PBXT 和 Falcon）。PostgreSQL 的基本设计理念是在操作不明确或不受支持的情况下产生错误或警告。

约束

MySQL 对约束检查有限制。尤其是在上述严格模式下，可以要求服务器将列值约束在数据类型可以存储的值范围内（例如，不允许插入大于类型所能容纳的最大整数；在不使用严格模式的情况下，MySQL 允许插入，但会存储与插入值不同的内容）。但是，除此之外，通常无法编写约束。支持约束检查的功能是外键（但仅在使用支持外键的存储引擎时）、ENUM 列类型和触发器。触发器在 MySQL 中实现得很粗糙。服务器确实接受 CHECK 子句，但 “CHECK 子句被解析，但所有存储引擎都忽略它”.

PostgreSQL 支持多种形式的约束

表属性约束: 例如 CHECK (discounted_price > 0)
表约束: 例如 CONSTRAINT valid_discount CHECK (price > discounted_price)
唯一性约束: 例如 UNIQUE(product_no) 或 UNIQUE(col1, col2, col3); 这些类型的约束会自动添加一个 UNIQUE 索引，并带来相应的成本和潜在好处。
外键: 这些功能得到了完全支持，通过添加触发器来验证表间完整性，并支持 ON DELETE RESTRICT、ON DELETE CASCADE、ON DELETE SET NULL、ON DELETE SET DEFAULT。; 曾经，外键检查持有的行锁是性能的重大障碍，特别是在涉及多个连接访问同一个“父”元素的引用时，但共享锁在很大程度上消除了这个问题。

事务性 DDL

在 PostgreSQL 中，当您处于事务中时，几乎所有操作都可以撤消。有一些不可逆操作（如创建或销毁数据库或表空间），但正常表修改可以通过发出 ROLLBACK 命令来撤消，该命令通过其预写日志设计实现。这支持撤消对 DDL 的重大更改，例如表创建。

MySQL 在使用 MyISAM 时不支持任何形式的回滚，因为它不是事务性的。即使 InnoDB 也不提供事务性 DDL，因为 DDL 操作会导致隐式提交，提交当前打开的事务。

经验丰富的 PostgreSQL DBA 知道如何利用这里的功能来保护自己，尤其是在执行复杂工作（如模式升级）时。如果您将所有此类更改放在一个事务块中，则可以确保它们全部原子地应用或完全不应用。这极大地降低了数据库因模式更改中的错字或其他错误而损坏的可能性，这在修改多个相关表时尤为重要，因为错误可能会破坏关系键。没有类似的方法可以安全地使用 MySQL 调整多个模式部分。

有关详细示例，请参阅 PostgreSQL 中的事务性 DDL：竞争性分析，这些示例演示了这些差异。

速度

默认配置

从历史上看，最初的 PostgreSQL 配置旨在支持较旧的 UNIX 版本，这些版本可能无法分配大量内存。结果是，它默认情况下非常悲观地使用内存来缓存结果。在拥有大量可用内存的现代系统上，这严重阻碍了未经调优的 PostgreSQL 性能。

在最近的版本中，默认设置变得不那么悲观。现在，系统配置在数据库初始化时进行检查，如果可以，则会分配更多内存。结果，最近 PostgreSQL 版本的未经调优的默认配置比旧版本性能好得多。此外，版本 8.1 和 8.2 中缓存管理的更改允许以比以前更有效的方式使用即使是少量缓存。

这两个数据库系统的主要可调参数的工作方式类似，即分配一块专用于数据库的共享内存。MySQL 在使用 MyISAM 时使用 key_buffer_size 对其进行调整，而在使用 InnoDB 时使用 innodb_buffer_pool_size 进行调整（请注意，即使 InnoDB 是常规表的主要存储引擎，您仍然需要一些 MyISAM 空间用于系统表）。PostgreSQL 使用 shared_buffers 对其主内存空间进行大小调整。

MySQL key_buffer_size 默认使用 8MB 内存。较早的 PostgreSQL 配置也会在数据库集群创建时，如果可能，为 shared_buffers 缓存分配 8MB 内存。在当前一代 Linux 系统等服务器上，预计最近的 PostgreSQL 版本会将 shared_buffers 默认设置为至少 24MB。

仍然值得仔细查看配置文件，以将它们调整为匹配数据库服务器上的可用内存，因为所有这些默认值与当前系统中的 RAM 量相比都明显不足。对于现代专用服务器，PostgreSQL 和 MySQL 的经验法则都是将专用内存大小设置为机器总 RAM 的至少 1/4，在正常范围的较高端，甚至可以增加到 RAM 的 1/2。当使用具有大量 RAM 的系统时，将此百分比推高也不是不可能的；对于 MySQL，InnoDB 缓冲池可能最适合配置为使用尽可能多的内存，为正常操作系统和 MySQL 服务器操作保留足够的内存（调整缓冲池大小很复杂；最好的建议写在了高性能 MySQL 第二版中，因为即使 MySQL 手册也只提供了在现实世界中会给出错误答案的幼稚公式）。使用这两个数据库的默认配置进行的性能比较完全不切实际，因为它们没有反映真实系统是如何配置的。有关该方面的初始指南可以在调优您的 PostgreSQL 服务器、优化 mysqld 变量和优化 MySQL 服务器中找到。

基准测试

基准测试很难做好；创建真正可比较的基准测试是一门复杂的艺术。许多显示 MySQL 比 PostgreSQL 快得多的旧性能基准测试存在一些问题

配置：看到调优后的 MySQL 与未经调优的 PostgreSQL 实例进行比较的情况并不少见。如上所述，未经调优的 PostgreSQL 过去对它拥有的资源过于悲观。真正公平的比较会匹配每个系统使用的内存量。
事务支持：MyISAM 基准测试涉及“事务”，这些事务不提供 PostgreSQL 提供的任何 ACID 保证。这通常意味着苹果与橙子进行了比较。
事务分组：与上述内容相关，PostgreSQL 有时会在幼稚的基准测试中受到阻碍，因为这些基准测试没有像真实应用程序那样适当地对事务进行分组。这可能会将更新的成本增加到不仅仅一个事务，而是可能数十万个事务的开销。
串行与并发行为：MyISAM 的许多行为都针对单个用户访问数据库进行了优化。例如，它使用表锁来控制对表的访问，这意味着在大量并发写入和读取的情况下，它会显著变慢。PostgreSQL 在大量同时连接的情况下降级得更加平稳。注意幼稚的基准测试，这些基准测试涉及跨单个连接的简单数据库请求流。

Sun Microsystems 2007 jAppServer2004 基准测试结果

Sun Microsystems 是一家中立的供应商，销售运行许多数据库类型的硬件，最近提交了在经过良好规范的 SPECjAppServer2004 上使用 PostgreSQL 和 MySQL 的测试结果。这两个系统之间存在足够的硬件差异，因此直接比较这两个结果是不公平的。但这两个分数都彼此接近，配置也相似，这表明虽然这两个数据库系统之间可能存在性能差异，但这种差异的幅度在这种应用程序类型中并不特别大。

为了进行比较，在 HP 上运行的 Oracle 结果在性能上表现出相似的幅度，但在硬件上却不如人意，这表明这两个开源数据库在绝对性能效率上仍然落后于最好的专有产品。有些人认为 Oracle 的领先优势更大，如果您选择一些示例来突出显示 Oracle 的优势，但请务必阅读基准测试大爆炸，了解有关实际定价的一些评论（以及关于第二个 PostgreSQL 结果的一些评论，该结果使用的是更小的服务器）。请注意，Josh Berkus 是 Sun 的员工，他在 Sun 的职责包括成为 PostgreSQL 核心团队的成员，因此他的评论应该相应地进行评估。

如果您进行公平的比较，包括软件许可成本，PostgreSQL 和 MySQL 的每美元性能数字都彼此相似，并且相对于数据库行业的平均水平而言非常出色。但是，仅凭这一点就说这些开源解决方案总是比 Oracle 等专有解决方案更好是不正确的；当然，还需要考虑每个解决方案的功能集和绝对性能。

事务锁定和可扩展性

PostgreSQL 使用一种称为 MVCC 的稳健锁定模型，它限制了单个客户端相互干扰的情况。MVCC 主要优势的简短总结是“读取器永远不会被写入器阻塞”。MVCC 用于实现 SQL 标准事务隔离级别的悲观实现：“当您选择 Read Uncommitted 级别时，您实际上获得了 Read Committed，当您选择 Repeatable Read 时，您实际上获得了 Serializable，因此实际的隔离级别可能比您选择的更严格。”默认的事务隔离级别是“read committed”。

MySQL 的 InnoDB 使用回滚段实现了 MVCC，灵感来自 Oracle 的设计；他们的新 Falcon 引擎的工作原理类似。InnoDB 数据库支持所有四个 SQL 标准事务隔离级别，默认级别为“repeatable read”。

比较这两个模型时，PostgreSQL 强制执行客户端分离，在所有情况下操作的数据始终保持一致；正如 MVCC 文档所述，“PostgreSQL 仅提供两个隔离级别的原因是，这是将标准隔离级别映射到多版本并发控制架构的唯一合理方式。”MySQL 允许进行配置，在这些配置中，未正确提交事务的客户端代码会导致数据视图，而 PostgreSQL 的更严格标准会认为这些视图不一致。但是，在允许读取的数据存在细微不一致的情况下，能够使用较不严格的锁定可能是 MySQL 的性能优势。

即使在将两个系统都配置为严格的事务锁定级别时，两种实现之间的差异也很微妙，以至于很难明确说明哪种实现对于特定应用程序效果更好。建议阅读 Weikum & Vossen 编写的《"Transactional Information Systems: Theory, Algorithms, and the Practice of Concurrency Control"》来了解这个复杂的主题。用那里的术语来说，PostgreSQL 使用多版本时间戳排序 (MVTO)，而 InnoDB 和 Oracle 使用多版本读取一致性 (MVRC)。主要区别在于 PostgreSQL 使用 with-REDO/no-UNDO，因为它将每个行版本存储在主表中，而 Oracle/InnoDB 实现 with-REDO/with-UNDO，它们从日志中重建块和/或行图像以提供读取一致性。如果您愿意将 IBM 的 DB2 作为第三种架构进行比较，那么关于此主题的其他良好参考资料是《A not-so-very technical discussion of Multi Version Concurrency Control》和《Leverage your PostgreSQL V8.1 skills to learn DB2》。IBM 显然不是 MVCC 方法的粉丝。

部分原因是 PostgreSQL 锁定实现非常成熟（它始终处于活动状态，并且相关代码的性能至关重要），即使在 MySQL 最初看起来更快的场景中，当并发用户数量变大时，PostgreSQL 也可以领先并扩展到更高的吞吐量。在 tweakers.net 数据库测试中展示了这种情况的一个很好的例子。

计算表中的行数

PostgreSQL 众所周知执行缓慢的一项操作是执行表中行的完整计数，通常使用以下 SQL

SELECT COUNT(*) FROM table

这之所以缓慢，与 PostgreSQL 中的 MVCC 实现有关。由于多个事务可以看到数据的不同状态，因此“COUNT(*)”无法直接汇总整个表中的数据；PostgreSQL 必须以某种方式遍历所有行。这通常会导致顺序扫描，读取表中每一行的信息。

一些 DBMS 提供了通过查询索引来执行“COUNT(*)”查询的功能。不幸的是，在 PostgreSQL 中，这种策略不起作用，因为 MVCC 可见性信息不会存储在索引级别。必须实际检查行本身，才能确定它们是否对事务可见。

在 MySQL 中，MyISAM 表缓存行计数信息，这使得这种类型的计数操作几乎可以立即完成。这就是为什么存在如此多的 MySQL 代码使用这种结构，因为它们认为这是一个微不足道的操作。但是，如果您使用的是 InnoDB，则情况不再如此。有关该领域中 MySQL 限制的说明，请参阅《COUNT(*) for Innodb Tables》和《COUNT(*) vs COUNT(col)》。可能部署在 InnoDB 上的 MySQL 设计不能假定完整的行计数速度很快，因此受到与 PostgreSQL 中存在的类似限制的阻碍。但是，InnoDB 的 MVCC 信息存在于其索引中，因此可以使用索引来满足 COUNT(*) 查询，即使没有 WHERE 子句；也不需要进行全表扫描。

值得注意的是，只有这种确切的聚合形式必须如此悲观；如果用“WHERE”子句进行补充，例如

SELECT COUNT(*) FROM table WHERE status = 'something'

PostgreSQL、MySQL 和大多数其他数据库实现将利用针对受限字段的可用索引来限制必须计数的记录数量，这可以极大地加快此类查询的速度。PostgreSQL 仍然需要读取结果行以验证它们是否存在；MySQL 可能需要也可能不需要，具体取决于存储引擎和事务隔离级别。InnoDB 通常不需要读取行，并且可以仅从索引中满足操作。

一种流行的方法是使用基于触发器的机制来计算表中的行数。在 PostgreSQL 中，另一种仅需要近似计数的替代方法是使用 pg_class 目录表中的 reltuples 字段。

联接复杂度

PostgreSQL 使用基于成本的查询优化方法，以便为多种类型的联接获得良好的性能。查询成本是根据在分析表时收集的规划器统计信息以及可调整的规划器成本来评估，而高级功能（如遗传查询优化器）允许高效地优化非常复杂的联接。

MySQL 的查询优化器没有那么复杂，因为它通过估计可能需要读取的磁盘块数量来估计成本。不幸的是，由于查询优化器是在服务器而不是存储引擎中实现的，因此并非所有相关信息都可供优化器使用，尤其是块是否缓存在内存中或是否需要从磁盘读取。有关这如何导致优化器选择错误计划的示例，请参阅有关随机与顺序 I/O 的帖子。当优化器选择错误时，只有几个控制查询优化器性能的可调参数。开发人员必须执行诸如明确提供索引提示之类的事情，以确保联接正确执行。为了使此任务更轻松，MySQL 提供了一个查询分析器，它比典型的 EXPLAIN 数据更容易使用。无论提示如何，子查询优化是 MySQL 中一个众所周知的弱点。MySQL 5.0 中还有一个相当严重的子查询空值处理错误（目前看来在 5.1 中仍然存在）。

Finding order in execution 提供了几个关于两个数据库如何不同地处理查询的比较。由于其更强大的自动优化功能，PostgreSQL 通常在处理复杂联接方面做得比 MySQL 好——但这只有在规划器配置正确的情况下才能实现（将 effective_cache_size 可调参数设置得太小是一个常见错误），并且表上的统计信息保持最新（通常通过自动清理）。您必须为 PostgreSQL 优化器提供正确的信息才能使用它，并且不能明确控制它使用哪个联接，这是一个有点争议的设计决策。PostgreSQL 核心开发人员认为，专注于改进优化器以使其在所有情况下都能正确工作，而不是仅仅允许查询提示计划作为问题的解决方法，这一点更为重要。

有一些附加工具被认为对探索 PostgreSQL 规划器很有用。pgAdmin 包含一个解释计划查看器（示例）。另一个选择是 Visual Explain，最初是 RedHat 的一个组件，由 Enterprise DB 保持更新和改进。它与 EnterpriseDB Advanced Server 软件包捆绑在一起，可以使用其 Developer Studio 软件包的源代码构建以针对其他 PostgreSQL 安装运行。

署名和反馈

本文档由 Greg Smith 撰写，Christopher Browne、Lukas Kahwe Smith 和 PostgreSQL 倡导邮件列表的其他成员做出了重大贡献。本文档链接的一些参考资料指向由这些作者撰写的文章。MySQL 行为的一些细微之处由 Baron Schwartz 澄清。

更正、建议、火焰和其他类似反馈应发送给 Greg，他是一位独立顾问，与 PostgreSQL 全球开发组唯一的联系是提交补丁以改进即将发布的 8.3 版本。他认为 PostgreSQL 凭借其自身优点而存在，与 MySQL 的比较应尽可能客观，反馈将相应对待。

为什么选择 PostgreSQL 而不是 MySQL (2009)

目录

简介