PostgreSQL与MySQL：如何选择？

市场上有许多关系型数据库管理系统（RDBMS），PostgreSQL和MySQL是其中最受欢迎的两个。这两种选择都提供了许多优势，并具有很强的竞争力。因此，必须了解它们的差异，以便为每个案例选择最合适的方案。

在这个意义上，这篇文章提供了PostgreSQL和MySQL之间的深入比较，考虑到了诸如数据类型、ACID兼容性、索引、复制等方面。此外，它还介绍了应该选择哪一种，并强调了考虑应用需求的重要性。

目录

什么是PostgreSQL？
什么是MySQL？
RDBMS vs ORDBMS
PostgreSQL vs MySQL：哪个更受欢迎？
PostgreSQL vs MySQL：关键差异
➤数据类型
➤编码
➤SQL
➤索引
➤ACID
➤MVCC特性
➤复制和集群
PostgreSQL vs MySQL：比较表PostgreSQL vs MySQL
如何选择？
结论

什么是PostgreSQL？

PostgreSQL 于1996 年首次发布，创建于加利福尼亚大学计算机科学系。目前，它的开发由PostgreSQL全球开发组负责。

PostgreSQL是一个开源的关系型数据库管理系统**（RDBMS**），也可以被认为是一个对象关系型数据库管理系统**（ORDBMS**），因为它支持一些面向对象的功能，如表继承和函数重载。

什么是MySQL？

MySQL于1995年被引入市场，比PostgreSQL早了不久。它是一个开源的（在GNU GLP下可用）关系型数据库管理系统（RDBMS）。此外，这个数据库是由甲骨文公司管理和拥有的。

多年来，MySQL已经建立了相当令人印象深刻和可靠的声誉。此外，它还因其易于使用而在社区中脱颖而出。

RDBMS vs ORDBMS

在继续比较PostgreSQL与MySQL之前，让我们先了解ORDBMS与RDBMS的区别。MySQL是一个纯粹的关系型数据库。因此，数据是以结构化格式（有列和行）存储的。另外，每个表内的值可以相互关联，表甚至可以与其他表关联。

PostgreSQL是一个ORDBMS。这些系统由关系模型组成，意味着在遵循面向对象模型的原则的同时，仍然可以将值和表联系起来。因此，ORDBMS可以包括类、对象和继承的概念。

就结构而言，MySQL和PostgreSQL实际上是非常相似的。它们都使用表作为其核心组件，这意味着数据被组织成行和列。此外，PostgreSQL还集成了存储过程、视图、约束、触发器、角色，并进一步支持NoSQL。反过来，MySQL提供了几乎相同的功能（或非常相同的功能），自从MySQL 5.7版本发布后（2015年），它也开始包括NoSQL功能。

PostgreSQL与MySQL：哪一个更受欢迎？

PostgreSQL和MySQL都是市场上最流行的数据库之一。然而，更准确地说，根据2021年5月的人气统计，MySQL仍然比PostgreSQL更受欢迎，正如DB-Engines排名所强调的。

此外，这一数据与基于谷歌搜索结果的TOPDB顶级数据库指数是一致的。根据该指数，MySQL排在第二位，PostgreSQL排在第六位。

关于社区支持，PostgreSQL和MySQL都得益于活跃的社区以及广泛的文档支持。

PostgreSQL与MySQL：主要区别

数据类型

目前，PostgreSQL和MySQL都能让开发者在表内使用JSON作为数据类型。然而，事情并不总是这样。直到MySQL 5.7.8版本的推出，该数据库系统并不支持JSON文件。

到目前为止，JSON支持仍然是MySQL集成的领先NoSQL功能之一。相比之下，PostgreSQL进一步支持XML、数组、用户定义类型和hstore，从而提供了比MySQL更多数据类型的操作能力。有多种选择的主要好处是，它可以增加功能。例如，通过接受数组作为一种数据类型，PostgreSQL也可以提供与这些数组兼容的主机函数。

尽管如此，尽管使用替代格式来存储数据有很多好处，但考虑到这些数据格式并不遵循一个完善的标准，所以实现这些数据格式也可能会更加复杂。因此，与数据库一起使用的组件可能不符合PostgreSQL的格式。这并不一定是一个缺点，而是需要注意的事情。

编码

当涉及到PostgreSQL与MySQL的编码时，应该考虑一些差异。让我们从大小写的敏感性开始。一方面，PostgreSQL是大小写敏感的。这意味着开发人员必须将数据库中出现的字符串大写，否则，查询将失败。另一方面，MySQL是不区分大小写的。因此，在查询时不需要对字符串进行大写。

编码方面的另一个区别在于字符集和字符串。PostgreSQL不允许UTF-8语法；因此，没有必要将字符集和字符串转换为这种语法。相反，某些版本的MySQL需要这种转换。

SQL

SQL是结构化查询语言的缩写，当涉及到查询数据语言时，它被认为是标准。然而，它在所有数据库系统中的应用方式并不一定相同。

SQL的基本原理是SELECT、INSERT、DELETE和UPDATE。此外，它还可能涉及一些额外的功能和语法方面的差异。

MySQL只是部分地符合SQL，因为它不支持所有的功能（例如，没有检查约束）。然而，它确实提供了许多扩展功能。反过来，PostgreSQL比MySQL更符合SQL标准，符合大部分的主要特性。更准确地说，PostgreSQL至少支持179个强制性特征中的160个。

索引

一个数据库的范围越大，索引就越关键。当处理有数百万行的表时，索引会非常有帮助，可以提高数据库的性能。在看每个数据库系统的特殊性之前，让我们首先指出它们的共同点：PostgreSQL和MySQL提供对B树和哈希索引的支持。既然如此，我们就来深入了解一下它们对索引的处理方法。

一方面，在MySQL中，大多数索引（PRIMARY KEY、UNIQUE、INDEX和FULLTEXT）都存储在B树中。然而，也有一些例外情况:

• 空间数据的索引被存储在R树中。
• MEMORY表也支持哈希索引。
• InnoDB为FULLTEXT索引采用了倒置的列表。

另一方面，在PostgreSQL中，索引被认为是二级索引。因此，索引是与表的堆分开存储的，而表的堆是主要的数据区域。因此，当执行索引扫描时，必须同时从堆和索引中获取数据。为了解决这种不便，PostgreSQL开发了对纯索引扫描的支持，这意味着开发人员不再需要要求对堆的访问来查询一个索引。应用这种方法必须遵循两个措施：索引类型必须支持只读索引扫描，而且查询只能引用存储在索引中的列。

为了最大限度地利用只读索引的扫描方法，开发者可以创建一个覆盖索引。覆盖索引会检索每一个需要的列。因此，它包括经常运行的特定类型的查询所需要的列。

覆盖索引在PostgreSQL中从9.2版（2012）开始才可用。然而，到那时，MySQL已经在使用它们，通过扫描索引来检索数据，而不需要接触表的数据。最后但并非最不重要的是，PostgreSQL的索引支持MySQL尚未开发的额外功能，如部分索引、表达式索引和位图索引。

ACID

ACID是指原子性、一致性、隔离性和耐久性。它描述了一个强大的数据库系统必须具备的属性，以确保交易的可靠性和一致性。正如我们在《SQL与NoSQL》一文中所解释的，许多（不是说大多数）关系型数据库管理系统是符合ACID的。这并不意味着NoSQL数据库不能兼容ACID。事实上，MongoDB、Apache的CouchDB和IBM Db2作为NoSQL数据库系统的一个例子，可以整合并遵循ACID原则。

MySQL则不然，因为它不支持一些原则，如一致性、隔离性和耐久性。然而，MySQL集成了InnoDB和NDB Cluster存储引擎等组件，如果开发者愿意，他们可以严格遵守ACID模型。

相比之下，PostgreSQL是符合ACID的，因为它提供了完全采用ACID模型所需的所有功能。尽管如此，实现这些功能并遵循各自的属性会降低性能。

如前所述，ACID中的 "I "代表 "隔离"，这不是很容易实现的。对于真正正确的隔离，开发者需要确保事务是可序列化的，这意味着执行一组事务的结果应该与这些事务的一些序列执行相同。因此，具有可序列化的数据库提供了任意的读/写事务，因此能够保证一致性。不幸的是，即使ACID属性保证了可靠性和一致性（对PostgreSQL来说是个优点），完全隔离也会限制并发性和整体性能较慢（PostgreSQL的缺点）。

MVCC特性

PostgreSQL比MySQL首先引入了多版本并发控制（MVCC）功能，这曾经是它最重要的优势之一。

MVCC功能为开发人员提供了对数据库的并发访问**，而不需要锁定数据**。因此，每个连接到数据库的开发人员在查询数据时看到的是数据的 "快照"。 在一个事务完全执行之前，其他用户/开发人员看不到数据库中的变化。简单地说，读者和写作者不会互相阻拦，MVCC使他们更容易进行互动。这个功能在整个数据库会话中提供了*"事务隔离*"（或*"快照隔离*"，如Oracle的名称），从而避免了事务看起来不一致和可能与锁发生冲突。

在MySQL中，通过使用InnoDB可以从MVCC功能中获益。InnoDB是默认的MySQL引擎，它使数据库系统符合ACID标准并具有MVCC。开发人员可以选择使用其他引擎；但是，这可能意味着失去这两个特性。

复制和集群

顾名思义，复制包括一个过程，允许开发人员将数据从数据库复制到副本数据库，使每个用户拥有相同水平的信息。另外，复制带来了一些好处，如自动备份、容错、可扩展性，以及在不干扰主集群的情况下进行长时间查询的能力。

PostgreSQL和MySQL都支持复制。在MySQL中，复制是单向异步的；因此，一个数据库服务器作为主服务器，而其他的是 "从服务器"（副本）。相比之下，PostgreSQL提供同步复制，意味着它有两个数据库同时运行，主数据库与副本数据库同步。此外，使用PostgreSQL时也可以进行级联和同步复制。

PostgreSQL和MySQL都支持的另一个方面是集群。集群利用共享存储将一组相等的数据复制到环境中的每个节点。由于在环境中的多个节点上复制数据所产生的冗余，这使数据库能够容忍故障。

尽管有单向异步复制，但MySQL集群内部采用了同步复制。 这样，MySQL从系统中消除了单点故障，并确保数据被写入各个节点，避免了对事务的任何负面影响和故障。另外，MySQL开发者还可以利用MySQL Cluster，这是一种优先考虑线性扩展的多主机技术。

关于集群，PostgreSQL支持流式或同步复制，也有Postgres-XL，它是一个数据库集群环境。

比较表

数据库系统	PostgreSQL	MySQL
RDBMS vs ORDBMS	ORDBMS - 关系型数据库，支持一些面向对象的特性，如表继承和函数重载。	RDBMS - MySQL是一个纯粹的关系型数据库。
数据类型	支持JSON、XML、数组、用户定义的类型和hstore。	支持JSON。
编码	PostgreSQL 是区分大小写的，并且不允许 PostgreSQL 不允许 UTF-8 语法。	MySQL不区分大小写，某些版本要求集合和字符串转换为UTF-8语法。
SQL	PostgreSQL支持179个强制性SQL功能中的至少160个；比MySQL更兼容SQL。	MySQL只有部分的SQL兼容。
索引	索引与表的堆分开存储，但开发者可以使用仅有索引的扫描，也可以创建覆盖索引。	大多数索引被存储在B树中。
ACID	符合ACID标准。	MySQL结合了InnoDB和NDB Cluster存储引擎等组件，允许开发人员密切遵守ACID模型。
MVCC功能	比MySQL首先引入了MVCC功能。	通过使用InnoDB实现MVCC功能。
复制	级联和同步复制	复制是单向的异步的
集群复制	支持流式或同步复制，也有Postgres-XL。	内部采用同步复制；也可以使用MySQL Cluster，一种多主机技术。

PostgreSQL与MySQL：如何选择？

关于到目前为止讨论的差异，在这两个数据库系统之间的选择并不总是那么明确。有一件事我们知道可以肯定的是，无论如何，没有一个错误的答案。这两个数据库系统都很受欢迎，并且有可靠的声誉。然而，一个可能比另一个更适合，这取决于背景。

如前所述，MySQL是一个RDBMS，而PostgreSQL是一个ORDBMS，因为它包括面向对象的功能，如函数重载和表继承。这种差异本身可能足以让一些开发者选择PostgreSQL，考虑到它使开发者更容易对复杂的应用对象结构进行建模。

此外，PostgreSQL比竞争者的替代品更符合SQL标准，并且以在事务中保持数据完整性而闻名，采用ACID模型。相反，为了兼容ACID，MySQL需要使用InnoDB和NDB Cluster存储引擎。然而，不一定要符合ACID标准，也可以使MySQL在读取数据时更快。

事实上，选择MySQL也有其优势。到目前为止，它仍然比PostgreSQL更受欢迎，并受益于一个广泛的社区以及大量的第三方工具。另一个巨大的优势是，MySQL以其快速、可靠和不复杂的数据库系统而脱颖而出，易于理解和设置。此外，在最近几年中，MySQL不断推出相关的功能（如MVCC）。

综上所述，PostgreSQL在内置功能方面更加丰富，它已经证明了其处理复杂查询（如子查询、过滤结果、连接等）以及大量数据库的能力。然而，如果优先考虑的是拥有一个快速、可靠、相当容易管理的数据库系统，那么MySQL也是一个很好的选择。

最终，在PostgreSQL与MySQL之间的选择将始终取决于应用程序的要求。例如，当处理一个有大量非结构化数据的数据库时，选择PostgreSQL可能更有利，因为它支持更多的数据类型。

总结

PostgreSQL和MySQL之间的比较不应该集中在哪一个更好，而是在特定的应用中选择一个或另一个。换句话说，应用程序的要求应该始终与数据库系统的特点和能力相一致。

因此，为了明智地选择，关键是要了解PostgreSQL和MySQL在关键方面的差异，以及开发人员如何充分利用每个选项。