为什么选择 PostgreSQL 而不是 MySQL：比较 2007 年的可靠性和速度
此资料的更新正在进行中，请访问 为什么选择 PostgreSQL 而不是 MySQL 2009
介绍
多年来，行业普遍认为 MySQL 比 PostgreSQL 更快、更容易使用。PostgreSQL 被认为功能更强大，更注重数据完整性，并且在遵守 SQL 规范方面更严格，但相应地速度更慢，使用起来也更复杂。
像过去形成的许多观念一样，这些观念在当前一代的版本中不再像过去那样准确。这两个系统都随着里程碑式的发布而发展，使得两者之间的比较变得更加复杂。
- MySQL 5.0（2005 年 10 月）终于添加了“严格模式”，缩小了数据完整性和规范合规性方面的差距。它还添加了对存储过程、视图、触发器和游标的支持，所有这些都被认为是某些类别的数据库部署必不可少的特性。
- PostgreSQL 8.1（2005 年 11 月）的特点是性能大幅提升，尤其是在可扩展性方面。从 8.1 版本到现在的 8.2 版本，提高性能一直是 8.X 版本的核心目标。
随着这些数据库的创新不断发展，每个开发社区都积极地做出改变，以解决他们各自的感知缺陷。其结果是，客观地确定哪个数据库更适合特定应用程序变得更加困难。本文旨在阐明 PostgreSQL 在哪些情况下比 MySQL 更合适，尝试公平地比较两者的当前生产版本，并讨论它们的优缺点。本文主要介绍的核心数据库软件的基本数据完整性和速度问题。由于通常情况下可以牺牲性能以换取可靠性，因此为了准确了解情况，必须将这两个主题结合起来考虑。
本文的观点是，当使用严肃的事务性数据库应用程序所需的高数据完整性标准比较这两个数据库时，当前一代的 PostgreSQL 在性能方面与 MySQL 相当或更好（尤其是在用户负载较重且查询复杂的情况下），同时在 SQL 标准合规性和丰富功能集方面保持领先优势。同时，通过探索这两个系统之间的差异，您也可能会意识到 PostgreSQL 设计团队的基本方法是如何普遍地优先考虑可靠和可预测的行为。MySQL 实现中的类似部分有一些缝隙，这些缝隙源于像事务性支持和严格模式这样的特性是在软件设计生命周期的后期添加的，而不是从一开始就作为不可分割的一部分。
比较版本、功能集和重点
在撰写本文的 2007 年 8 月，当前的生产就绪版本是 PostgreSQL 8.2 和 MySQL 5.0，本文就是比较这两个版本。由于 PostgreSQL 8.1 和 8.2 都是当前支持的版本，并且性能良好，因此本文中的一些评论可能会同时提到这两个版本。8.2 版本速度略快（在某些工作负载下可能快 30%），但部署 8.1 仍然是一个完全可行的选择，尤其是因为与较新的 8.2 版本相比，更多操作系统供应商捆绑和支持 8.1 版本。
这两个系统在撰写本文时都有新的测试版本（PostgreSQL 8.3 和 MySQL 5.1），它们对本文中涉及的某些领域进行了增量改进，但这两个新版本中没有根本性的差异，因此本文中的一般指南不会失效。未来 MySQL Falcon 引擎的正式发布是未来可能出现的一个颠覆性事件。PostgreSQL 8.3 中的 异步提交 功能是另一个即将发生的变化，它将显着改变可用的可靠性/性能权衡选择。
本文没有具体讨论的是这两个产品在这些基本功能以外的领域的功能集。由于 PostgreSQL 8.1 和 MySQL 5.0 发生了重大的变化，许多涵盖此主题的文档都过时了，不建议使用。一些可能有所帮助的页面包括
虽然功能清单很有用，但要理解某些系统行为，需要对相应系统有相当深入的了解。例如，PostgreSQL 如何压缩 TOAST 数据的内部机制对用户来说是不可见的，但在处理某些类型的数据时，它可以显著提高系统性能。
本文未涵盖的另一个领域是，与 PostgreSQL 相比，更多应用程序支持 MySQL 作为首选数据库，当然，这对决定哪一个数据库更适合特定情况来说是一个重要的因素。有关在某些流行应用程序中添加 PostgreSQL 支持的进展情况，可以跟踪 软件端口。在考虑应用程序如何使用 MySQL 时，您应该评估的一件事是，如果它们最初的目标是 5.0 之前的版本，那么它们可能不兼容该版本中引入的新特性，比如严格模式。如果是这样的话，此类应用程序可能仅限于它们针对的旧版本的功能，并且可能需要进行某种移植工作才能利用现代 MySQL 功能。
可靠性
数据完整性
在 5.0 版本之前，MySQL 当之无愧地以允许将不一致数据插入数据库而闻名。 保证数据 使用 MySQL 5.0 的完整性解释了旧版 MySQL 版本存在的问题，以及如何使用当前版本中提供的严格 SQL 模式 来解决这些问题。当然，任何 MySQL 客户端都可以更改自己的 SQL 模式以覆盖此模式，结果是这些验证约束可能仍然不会由服务器强制执行。有关此主题的一些很好的示例可以在 MySQL 咬人：需要注意的怪癖 中找到。
PostgreSQL 一直以来都非常严格地确保数据在允许进入数据库之前是有效的，并且没有办法让客户端绕过这些检查。
事务和数据库引擎核心
为 MySQL 带来了最初速度声誉的数据库核心是 MyISAM。该引擎具有出色的读取性能，并且其解析器对于简单的查询非常高效，这使得它在读取密集型应用程序（例如涉及简单 SELECT 的 Web 应用程序）中非常快。但是，众所周知，MyISAM 比大多数严肃的数据库应用程序所能容忍的更容易出现数据 损坏，并且在崩溃后，可能需要花费大量时间重建其索引才能让服务器重新启动。此外，它不支持外键或允许数据库具有 ACID 属性的事务。MyISAM 在处理并发读写方面也存在问题，因为它只提供表级锁定。
在 MySQL 中集成 InnoDB 存储引擎在数据完整性方面大大优于 MyISAM，它添加了一个更健壮的日志重放机制以实现崩溃恢复，并支持符合 ACID 的事务。但是，这种新方法带来了更大的开销，并且 InnoDB 表在纯读取负载方面的速度不如 MyISAM 表。此外，内部 MySQL 元数据表仍然使用 MyISAM 存储，这意味着它们仍然容易受到与该存储引擎相关的传统损坏问题的影响。这个问题通过使用一些复杂的 锁定方法 来解决，这些方法可能会导致表更改阻塞一段时间。
您还应该注意，在某些环境（通常是共享 Web 托管）中，您可以创建您认为是事务安全的 InnoDB 表，但实际上却获得了非 ACID 的 MyISAM 表。就像 MySQL 经常发生的那样，这不会产生错误，它会默默地做错事。有关如何在可能运行旧版 MySQL 版本的系统上确认您是否获得了您想要的内容的详细信息，请参阅 糟糕，不支持 InnoDB 表。
PostgreSQL 一直以来都专注于事务级的数据完整性，将锁定问题降至最低，并且除了硬件故障或配置严重错误外，很难损坏数据库。
值得注意的是，数据库引擎是 PostgreSQL 核心的一部分，而 InnoDB 则是目前从 Oracle Corporation 获得双重许可的产品。目前尚不清楚 Oracle 如何在未来改变 InnoDB，因为它们与 MySQL AB 存在竞争关系，而 PostgreSQL 则没有这种利益冲突。MySQL AB 一直致力于开发一个名为 Falcon 的新数据库引擎核心，以摆脱这种情况，但从历史上看，开发既快速又可靠的数据库引擎核心需要多年的工作和测试才能获得适合生产使用的成熟产品。 最初的基准测试 表明 Falcon 还有很多需要解决的粗糙边缘。
外键
设计技术（如数据库规范化）的正确实现依赖于数据库使用外键映射表之间关系的能力。在 MySQL 中，外键仅在使用InnoDB时才受支持。其实现的一个问题是，它存在限制，并且会默默地忽略一些标准语法。例如，在创建表时，即使在即将发布的 MySQL 5.1 版本中，““CHECK 子句被解析但被所有存储引擎忽略”。PostgreSQL 的基本设计理念是在操作不明确或不受支持的情况下产生错误或警告。
事务性 DDL
在 PostgreSQL 中，当您在事务中时，几乎所有操作都可以撤销。有一些不可逆转的操作（如创建或销毁数据库或表空间），但通过其预写日志设计，通过发出ROLLBACK，可以撤消正常的表修改。这支持撤消对DDL（如表创建）的重大更改。
MySQL 在使用 MyISAM 时不支持任何形式的回滚。使用 InnoDB 时，服务器具有隐式提交，即使关闭了正常的自动提交行为，也会发生。这意味着任何单个表更改或类似更改都会立即提交。
经验丰富的 PostgreSQL DBA 知道如何利用其功能来保护他们在执行复杂工作（如模式升级）时的安全。如果将所有此类更改放入事务块中，则可以确保它们以原子方式全部应用，或者根本不应用。这大大降低了数据库因模式更改中的类型错误或其他此类错误而损坏的可能性，这在修改多个相关表时尤为重要，因为错误可能会破坏关系键。没有类似的方法可以安全地调整 MySQL 的多个模式部分。
查看PostgreSQL 中的事务性 DDL：竞争分析以获取演示这些差异的详细示例。
速度
默认配置
从历史上看，PostgreSQL 的初始配置旨在支持较旧的 UNIX 版本，在这些版本中，分配大量内存并不一定可行。结果是，它用于缓存结果的内存使用量在默认情况下非常悲观。在具有大量可用内存的现代系统上，这会严重影响未调整的 PostgreSQL 性能。
默认值在最近的版本中变得不那么悲观。现在在数据库初始化时检查系统配置，如果可能，分配更多内存。结果，最近 PostgreSQL 版本上未调整的默认配置比旧版本运行得明显更好。此外，8.1 和 8.2 版本中的缓存管理更改允许即使是少量缓存也能比以前更有效地使用。
两个数据库系统的主要可调整参数的工作方式类似，即分配一块专门用于数据库的共享内存。MySQL 使用key_buffer_size（使用 MyISAM 时）和innodb_buffer_pool_size（使用 InnoDB 时）来调整此参数（请注意，即使 InnoDB 是常规表的 utama penyimpanan engine，您仍然需要一些 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 指南建议即使 80% 也不算过分。使用任一数据库的默认值进行的性能比较完全不符合真实系统配置的方式。该领域的初始指南可以在调整您的 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 的领先优势更大，如果你选择示例来让它更突出，但一定要阅读基准测试闹剧以了解有关实际定价的一些评论（以及了解有关使用较小服务器的第二个 PostgreSQL 结果的一些评论）。请注意，Josh Berkus 是一名 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。用书中使用的术语来说，PostgreSQL 使用多版本时间戳排序 (MVTO)，而 InnoDB 和 Oracle 使用多版本读一致性 (MVRC)。主要区别在于 PostgreSQL 是带重做/不回滚，因为它将每个行版本存储在主表中，而 Oracle/InnoDB 实现带重做/带回滚，它们从日志中重建块和/或行镜像以提供读一致性。如果您愿意考虑第三种架构，即 IBM 的 DB2，作为比较点，关于此主题的其他好的参考资料是 关于多版本并发控制的非技术性讨论 和 利用您的 PostgreSQL V8.1 技能学习 DB2。IBM 显然不是 MVCC 方法的粉丝。
部分原因是 PostgreSQL 锁定实现非常成熟（它始终处于活动状态，并且相关代码的性能至关重要），即使在 MySQL 最初看起来速度更快的场景中，当同时用户的数量变得很大时，PostgreSQL 也可以超越并扩展到更高的吞吐量。一个很好的例子是 tweakers.net 数据库测试 中展示的场景。
计算表中的行数
PostgreSQL 众所周知的一个执行缓慢的操作是在表中执行完整行数计数，通常使用以下 SQL
SELECT COUNT(*) FROM table
这之所以慢，与 PostgreSQL 中的 MVCC 实现有关。多个事务可以查看数据的不同状态，这意味着 “COUNT(*)” 无法以直接的方式汇总整个表的 data；PostgreSQL 必须以某种意义上遍历所有行。这通常会导致顺序扫描，读取表中每一行的信息。
一些 DBMS 提供 “COUNT(*)” 查询通过咨询索引来工作的能力。不幸的是，在 PostgreSQL 中，这种策略行不通，因为 MVCC 可见性信息不会存储在索引级别。需要实际检查行本身才能确定它们对事务是否可见。
在 MySQL 中，MyISAM 表缓存行计数信息，使这种类型的计数操作几乎是即时的。这就是为什么存在如此多的 MySQL 代码使用这种结构，假设它是一个微不足道的操作。但是，如果您使用的是 InnoDB，情况就不再如此了。有关此领域中 MySQL 的限制，请参阅 Innodb 表的 COUNT(*) 和 COUNT(*) vs COUNT(col)。可能部署在 InnoDB 上的 MySQL 设计不能假定完整行计数会很快，因此会受到与 PostgreSQL 中存在的类似限制的阻碍。
值得注意的是，只有这种精确形式的聚合必须如此悲观；如果用 “WHERE” 子句进行扩充，例如
SELECT COUNT(*) FROM table WHERE status = 'something'
PostgreSQL、MySQL 和大多数其他数据库实现将利用针对受限字段的可用索引来限制必须计数的记录数，这可以大大加快此类查询。PostgreSQL 仍然需要读取结果行以验证它们是否存在；其他数据库系统在这种情况下可能只需要引用索引。
对于需要行计数但可以容忍它不包括正在提交过程中的事务的应用程序，一种流行的方法是使用基于触发器的机制来计算表中的行数。在 PostgreSQL 中，当只需要近似计数时，另一种选择是使用 pg_class 目录表中的 reltuples 字段。
连接复杂性
PostgreSQL 使用基于成本的查询优化方法，以获得各种类型的连接的良好性能。查询成本是根据分析表时收集的 规划器统计信息 以及可调整的 规划器成本 进行评估，并且诸如 遗传查询优化器 之类的先进功能允许有效地优化即使是非常复杂的连接。
MySQL 在其规划器中没有这种复杂性，并且 控制查询优化器性能 的可调参数很粗糙。开发人员改为执行诸如显式提供 索引提示 之类的事情，以确保连接正确执行。为了使此任务更容易，MySQL 提供了一个 查询分析器，它比典型的 EXPLAIN 数据更容易使用。无论是否提示，子查询优化 都是 MySQL 中已知的一个弱点。MySQL 5.0 中还有一个相当严重的子查询 空值处理错误（在此时看来，该错误在 5.1 中 仍然存在）。
查找执行顺序 提供了两种数据库如何以不同方式处理查询的多个比较。由于其更强大的自动优化，PostgreSQL 通常在处理复杂连接方面比 MySQL 做得更好——但前提是规划器配置正确（将 effective_cache_size 可调参数设置得太小是一个常见的错误）并且表有关的统计信息保持最新（通常通过 自动真空）。您必须为 PostgreSQL 优化器提供正确的信息才能使用，并且不能明确控制它使用哪个连接，这是一个有点争议的设计决策。核心 PostgreSQL 开发人员认为，专注于改进优化器使其在所有情况下都能正常工作，而不是仅仅允许查询提示计划作为解决问题的变通方法，这一点更为重要。
有一些附加工具，有些人发现它们对探索 PostgreSQL 规划器很有用。 pgAdmin 包括一个解释计划查看器 (示例)。另一个选择是 Visual Explain，它最初是 RedHat 的一个组件，由 Enterprise DB 保持最新并进行了改进。它与 EnterpriseDB Advanced Server 包捆绑在一起，并且可以使用其 开发人员工作室 包的源代码构建以针对其他 PostgreSQL 安装运行。
版权和反馈
本文档由 Greg Smith 编写，Christopher Browne、Lukas Kahwe Smith 和 PostgreSQL 倡导邮件列表的其他成员做出了重大贡献。本文档中链接到的某些参考资料也指向这些作者撰写的文章。
更正、建议、火焰和类似的反馈应发送给 Greg，他是一名独立顾问，他与 PostgreSQL 全球开发组的唯一关系是提交补丁以改进即将发布的 8.3 版本。他认为 PostgreSQL 凭借其自身的优势，与 MySQL 的比较应该尽可能地客观，并且将根据此原则处理反馈。
