MySQL和PostgreSQL是两款最流行的开源SQL数据库，两者都可以很好地作为通用数据库。在这两者之间，你该如何做出选择？下面让我们来看看这两款数据库的相对优势和劣势。

关于MySQL和Postgres

MySQL最初是开源的，但在2010年被甲骨文收购并部分商业化。为了确保它仍然是免费且独立的开源项目，MySQL开发人员创建了一个MySQL分叉，名为MariaDB。

MariaDB和MySQL大多向后兼容。最初，它们是二进制兼容的；你完全可以用MariaDB来替换MySQL，而无需做任何更改。然而，对于某些工作负载，MariaDB可提供更好的性能，它还提供更多存储引擎，包括Aria和ColumnStore。

PostgreSQL（通常称为Postgres）由数据库先驱Michael Stonebraker创建，并于1996年成为开源。它通常被认为是专有甲骨文系统的替代品。

Postgres可以说是一种更复杂和企业就绪的服务，以其有效的性能而闻名，特别是在处理复杂的查询、大型数据集和并发连接方面。但让我们仔细对比一下MySQL和Postgres，从重要数据库类别：性能；SQL合规性；复杂查询和数据处理；复制；可扩展性和安全性。

性能

数据库性能包含很多因素，例如查询优化、配置、索引和缓存。

MySQL

除了这些因素之外，MySQL的主要卖点之一是能够使用不同的存储引擎，并且，选择正确的引擎很重要。现在主要有三个选择：

• InnoDB。这是默认引擎，提供事务、高性能数据完整性和行锁。
• MyISAM。这是较旧的引擎，它的读取操作非常快，但缺乏数据事务，写入速度不如InnoDB。
• In-memory。它将所有数据存储在RAM中，性能非常快；主要用于临时表。

默认存储引擎InnoDB非常适合处理强调数据完整性和速度的工作负载，例如快速查找引用完整性。该引擎的读取性能也非常好，可配合大多数SQL服务器的常用方法和MySQL特有的方法，包括以下：

• 索引。在列中添加索引可以加快数据检索速度，例如B树。
• 缓存。查询缓存和TTL到期以及缓存驱逐减少了执行时间，并减少了对底层持久性模型的调用次数。
• 分区。将大表分割成小块可以加快查询速度。
• 读取复制。与SQL的负载平衡器类似，读取请求可以卸载到副本，以提高在大量读取流量下的性能。

PostgreSQL

PostgreSQL中的性能受到各种因素的影响，最明显的是以下因素：

• 查询优化。Postgres支持索引，但它也通过EXPLAIN和ANALYZE等执行策略来增强这一点，以实现实时查询优化。
• 配置调整。这使得能够根据系统硬件、共享缓冲区的使用和有效的缓存大小调整工作负载。
• 并发。PostgreSQL使用多版本并发控制（MVCC）来确保一致的数据处理，而不会出现锁定问题，这在高度并发环境中提供了出色的性能。MySQL的部署与MVCC相似，但Postgres的MVCC部署要激进得多。
• 并行性。PostgreSQL支持并行查询执行，其中某些查询可以拆分到多个CPU内核，以加快处理速度。

PostgreSQL擅长处理复杂的查询。它有很多独特的数据类型和查询，以支持处理庞大的数据集，例如：

• 窗口函数。这个强大的工具支持复杂的数据分析任务，例如运行总计和移动平均线，以便用户可以将一些分析从代码转移到DB引擎。
• 通用表表达式（CTE）。PostgreSQL提供递归和非递归CTE，这有助于将大型查询分解为更易于管理的组件。这在查询执行层内自动发生。
• 全文搜索。PostgreSQL支持全文搜索功能，这可支持复杂的搜索功能，因此用户可以将文本搜索直接加载到DB引擎中。
• JSON和JSONB。PostgreSQL支持存储和查询JSON数据的能力，因此它适用于需要结构化和半结构化数据查询的混合应用程序。

在性能方面，让PostgreSQL真正发挥优势的是在重写入应用程序，而MySQL则无法处理。PostgreSQL写入通过几个功能进行优化：

• 缓冲和缓存。PostgreSQL使用内部缓冲区和缓存机制来优化写入性能。
• 批量处理。对于高写入吞吐量，PostgreSQL支持批处理插入和更新，这在处理大量数据时可以带来巨大的性能提升。
• 并发控制。MVCC确保同时多次写入，没有冲突，这减少了多租户环境中的争执，并提高吞吐量。

合规性

MySQL和PostgreSQL都支持SQL标准，以及其他各种功能。

MySQL

MySQL支持SQL:2003标准，包括存储过程、触发器和视图等功能。它提供一系列标准数据类型，包括INT、VARCHAR、DATE、CHAR和FLOAT，并支持ENUM和SET等专业数据类型，这些数据类型允许预定义的值列表。

PostgreSQL

Postgres完全符合SQL:2011，并添加很多自定义数据类型。它为ANSI SQL功能以及一些自定义扩展提供了广泛的支持，例如数组支持、自定义数据类型和高级索引机制。

Postgres还通过CHECK约束、域约束和排除约束等功能确保数据完整性，这些功能通常比其他SQL数据库更先进。

复制

对于复制，MySQL和Postgres采用的是不同的方法。

MySQL

MySQL中的复制设置简单，非常灵活。它可以根据系统的特定需求，以不同的拓扑结构进行配置，例如主副本（一个主副本、多个副本）、主主服务器（两个服务器同时充当主服务器和副本）或多源（单个副本服务器从多个主服务器收集数据）。

MySQL使用三种主要复制模式：

• 异步复制。主服务器将更改写入二进制条目，并将它们发送到复制的服务器。这提供了类似delta的复制，并且是完全异步的；然而，副本之间可能会有延迟。
• 半同步复制。这在事务完成之前，至少等待一个副本来确认对更改的复制。
• 组复制。这种完全容错和高度可用的多主复制服务使MySQL能够在多个服务器上分发数据，从而提高可用性和性能，特别是在读取重的环境中。

PostgreSQL

PostgreSQL中的复制比MySQL更复杂、更难设置，但它也更灵活、更强大。Postgres提供各种复制选项，以提高可扩展性和容错性，例如：

• 流媒体复制。PostgreSQL支持异步流复制，其中主节点的更改持续发送到复制节点。这类似于MySQL异步复制。
• 同步复制。通过这个，只有在事务被写入主节点和副本节点后，才被视为已提交。这类似于MySQL的半同步复制，只不过MySQL是当主节点和复制节点都有数据时，确保事务完成。
• 逻辑复制。逻辑复制允许在更精细的层面上复制数据，例如特定的表或数据集，而不是整个数据库。
• 热备份。PostgreSQL允许在热备份模式下在副本节点上执行读取查询，这有助于在保持高可用性的同时分配负载。

安全性

MySQL和PostgreSQL都提供了一系列安全功能，从身份验证到加密，再到日志记录和审计。

MySQL

MySQL中的安全性功能强大且易于设置。它提供有效的用户身份验证机制和强大的加密功能，例如：

• 身份验证。支持强大的身份验证机制，包括本机密码加密以及与LDAP和PAM的集成。
• 角色和特权。用户可以基于角色访问表和模型，这允许对数据库和操作进行查找粒度访问。
• 加密。MySQL支持SSL/TLS来保护连接和静态数据加密，以保护敏感数据。
• 审计日志。监控用户活动，并确保遵守安全策略。

PostgreSQL

Postgres同样具有广泛的安全功能，包括以下内容：

• 身份验证。PostgreSQL支持各种身份验证方法，包括基于密码的方法（例如MD5和SCRAM-SHA-256）、Kerberos、GSSAPI、LDAP和基于证书的身份验证。
• 基于角色的访问控制。与MySQL一样，Postgres也使用角色来管理访问和权限。
• 数据加密。和MySQL一样，PostgreSQL支持SSL/TLS加密。对于静态数据，PostgreSQL本机不支持透明数据加密，但可以实现应用程序级解决方案或磁盘级加密。
• 行级安全性。使用RLS，你可以定义控制表内特定行访问的策略，为多租户或高度敏感的应用程序提供精细的访问控制。
• 审计。PostgreSQL支持通过扩展程序记录和审计，该扩展程序可以跟踪和记录所有数据库活动，帮助监控和安全合规性。

结论：不同数据库满足不同需求

MySQL和PostgreSQL都是强大的开源关系数据库，但它们在不同领域表现出色，这使得它们都更适合特定的用例。

性能

MySQL以其速度而闻名，特别是在读取重的工作负载和简单的查询中。PostgreSQL虽然在基本操作中略慢，但由于具有MVCC、并行查询执行和索引选项等高级功能，在重写入工作负载和复杂查询方面表现出色。

SQL合规性

PostgreSQL高度遵守SQL，支持高级功能，包括窗口函数、CTE和复杂数据类型。这使得它非常适合需要复杂查询和遵守SQL标准的系统。MySQL虽然合规，但往往更灵活，为了性能牺牲一些严格性。

复杂的查询和数据处理

PostgreSQL在处理复杂的查询和数据分析方面大放异彩。当涉及高级分析或需要复杂计算的科学应用的用例时，PostgreSQL无疑是首选。MySQL虽然能够进行复杂的查询，但通常更适合快速、简单的查询占主导地位的Web应用程序。

复制和可扩展性

这两个数据库都提供强大的复制选项，但MySQL在高可用性系统中具有多主复制工具的优势。

安全性

PostgreSQL提供更高级的安全功能，例如行级安全和审计，使其适用于需要精细访问控制的用例，例如多租户应用程序或敏感数据处理。

总体而言，MySQL因其简单性和性能而通常用于Web和事务应用程序，而PostgreSQL则更适合需要高级SQL功能和安全性的数据密集型、分析或高完整性系统。