导读：用PostgreSQL还是MySQL，现在似乎越来越不好选择。本文能给大家一个清晰的选型描述。

但是开发者的核心挑战是：如何管理两者之间安全访问的场景。

无论你的团队是使用 PostgreSQL 进行财务和分析，还是使用 MySQL 进行电子商务和仪表板管理，需要确保跨环境的一致、安全和可审计的访问都是现代技术团队的首要之关注点。

数据库管理系统概述

数据库管理系统是一种允许用户创建、管理和与数据库交互的软件系统。虽然PostgreSQL和 MySQL 都属于这一类，但它们在本质上是不同的。 

MySQL是纯关系型数据库，它将简单性和速度放在首位。值得我们注意的是，新的JSON 支持赋予了它半结构化功能。

PostgreSQL 则更为复杂些，它是一个对象关系型数据库。由于其功能极其丰富且可扩展性较好，PostgreSQL 通常是处理复杂查询和写入密集型工作负载的首选。

下面，我们将用更详细地文字来介绍。

核心特性和功能

标准合规性

在所有开源关系数据库管理系统 (RDBMS) 中，PostgreSQL 可以说是最符合 SQL 规范的。它不仅严格遵循 ANSI SQL 标准，而且还原生支持高级 SQL 功能，例如窗口函数、递归查询、部分索引和全文搜索等。

MySQL 虽然广受欢迎，但众所周知的是，它为了性能和易用性而在 SQL 兼容性方面做出了一些取舍。最近的版本虽然有了显著的改进，但仍然明显缺乏对一些高级 SQL 结构的支持。开发者在使用非 InnoDB 存储引擎时，这一点尤其明显。

可扩展性

可扩展性是 PostgreSQL 的核心能力。想要定义自定义数据类型、运算符、索引方法，甚至加载 PostGIS 等用于空间数据的扩展程序吗？在Postgre这里很容易。

相比之下，My SQL 的扩展性略差一些。虽然有插件，但创建和部署它们需要更深层次的内部知识，甚至可能限制你使用内置语法和存储引擎。

索引选项

PostgreSQL 支持多种索引类型，包括如下：

• B 树（默认）
• GIN（广义倒排索引）
• GiST、BRIN、SP-GiST
• 部分索引和基于表达式的索引

MySQL 支持两种常见的索引：B 树和全文索引（例如，InnoDB 支持 FULLTEXT）。不过，后者可能存在一些限制，具体取决于所使用的存储引擎。

视图功能

PostgreSQL 同时支持常规视图和物化视图。两者都是预先计算并存储的，以便快速读取。因为可以按需刷新，因此它们特别适合用于执行昂贵的连接或聚合操作。

至于 MySQL，它仅支持非物化访问，这表示着查询是在访问时执行的。

ACID 合规性和事务支持

PostgreSQL 非常重视合规性，严格遵守ACID 原则（原子性、一致性、隔离性和持久性）。这意味着无论发生错误还是故障，PostgreSQL 都能提供完全的事务隔离。是的，即使是在最苛刻的软件用例中，例如金融系统、合规性要求严格的环境以及企业级审计跟踪，PostgreSQL 都能实现序列化。

MySQL 也支持使用 InnoDB 存储引擎的 ACID 事务。但是，它的 MyISAM 存储引擎并不兼容 ACID，如果切换过来，用户可能会感到困惑。MySQL 的默认配置更倾向于速度，而非严格的隔离性。 

架构与设计哲学

PostgreSQL 与 MySQL 的争论应该涵盖一些相互竞争的 DBMS 真正核心。 

前者遵循基于进程的架构，每个连接都会生成一个新的操作系统进程。这听起来可能复杂，但 PostgreSQL 设计的优势在于它提供的稳定性。换句话说，错误的连接根本不会导致服务器崩溃。唯一的缺点是它需要大量的内存。

MySQL基于线程架构构，这能让它能够很好地扩展数千个轻量级连接。由于 MySQL 的设计减少了每个连接的内存消耗，因此它非常适合具有很多个并发用户的 Web 应用。

PostgreSQL 与 MySQL 的主要区别

接下来，让我们来谈谈 MySQL 和 PostgreSQL 之间的主要区别。 

数据类型和存储选项

在这里，MySQL 和 PostgreSQL 之间的区别非常微妙。PostgreSQL 要更胜一筹，因为它支持更广泛的数据类型，包括原生和自定义数据类型：

• JSON 和 JSONB（支持索引的二进制 JSON）
• 数组、HSTORE（键值存储）、UUID、XML、IP 地址类型
• 用于特定于架构的验证的自定义类型和域

这是否意味着 MySQL 的选择有限？

事实并非如此，MySQL支持大多数常见类型（INT、VARCHAR、DATE 等），它在最近于 5.7 版本中也引入了 JSON类型。

即便如此，它在两个关键方面存在着不足：JSON索引 和原生支持数组的数据类型。

查询处理和优化

如果不深研究 PostgreSQL 和 MySQL 在查询方面的比较，那就太失礼了，这可以说是关系 DBMS 领域中最重要的方面。

PostgreSQL 使用功能强大、功能全面的查询规划器与优化器。该优化器支持从 CTE、窗口函数到自定义索引（例如 GIN、GiST、BRIN）的所有内容。

相比之下，MySQL 的查询优化器对于简单的查询效果非常好，并且受益于一致的模式使用。然而，随着表数据的增长，它可能会难以处理更复杂的连接。

简单来说，PostgreSQL 可以更好地处理复杂的分析工作负载，而 MySQL 最适合简单、简单的在线事务处理 (OLTP)。 

并发控制方法

两个操作系统都使用多版本并发控制（MVCC）。然而，它们的技术实现存在显著差异。 

PostgreSQL 的 MVCC 非常健壮。它不仅支持细粒度的行级锁和快照隔离，还支持更高级的死锁控制和事务一致性。

MySQL 的 MVCC（通过 InnoDB）在撤消日志上蓬勃发展，但与同类产品相比，其隔离级别能力有限。

性能比较和基准测试

让我们对每个系统在负载下的表现进行基准测试，并讨论影响实际性能的因素。

读取与写入操作

就读写性能而言，MySQL 的速度非常快。它尤其适用于读取量大、查询量高、写入率低的应用，例如 CMS、论坛和仪表盘。

对于写入密集型工作负载，PostgreSQL 无疑是赢家。与 MySQL 相比，它在处理复杂事务、索引和触发器方面表现更佳。

复杂查询处理

与 MySQL 相比，PostgreSQL 的执行引擎和优化器在处理以下方面遥遥领先：

• 递归查询
• 多级连接
• 子查询和嵌套 SELECT
• 物化视图

为了使 MySQL 达到类似的性能水平，通常需要非规范化或应用层解决方法。

资源利用率指标

由于 PostgreSQL 架构复杂，每个进程可能会消耗更多内存，但它的进程隔离机制弥补了这一点。怎么做到的？通过提升稳定性。

值得一提的是，MySQL 的内存占用非常小。这使得它非常适合资源有限的共享主机或容器。

数据库特性和功能

本节中的功能定义了数据库如何支持现代开发需求。让我们看看它有哪些内置功能、哪些功能可扩展以及哪些功能缺失。

JSON 和 NoSQL 支持

PostgreSQL 的 JSONB堪称优秀的二进制数据类型。它功能多样，除了作为混合关系数据库管理系统 (RDBMS) 之外，还可以兼作文档存储。有了它，我们可以：

• 索引深层嵌套的键
• 运行 JSONPath 查询
• 无需更改架构即可存储日志、遥测或用户设置

MySQL 的 JSON 支持正在改进，但仍然缺乏原生索引和高级查询功能。

存储过程和函数

PostgreSQL 支持多种语言的存储过程，包括：

• PL/pgSQL
• Python
• JavaScript
• SQL

虽然 MySQL 支持 SQL 中的存储功能，但其功能和语言支持有限。

所以，PostgreSQL在数据库层内部的高级逻辑封装方面具有优势。

复制和高可用性

PostgreSQL 再次在此领域大放光彩，它支持流复制、逻辑复制和热备。它还支持多主服务器设置和零停机模式更改。PostgreSQL 的多主服务器功能需要第三方工具，例如 BDR 或 Citus。

MySQL 支持组复制、多源复制和只读副本，但在逻辑复制方面却略显不足。后者的应用范围也较为有限。

简而言之，PostgreSQL 和 MySQL 都可以实现高可用性。但 PostgreSQL 可以实现更精细的控制。

可扩展性和企业就绪性

随着组织规模的扩大，数据库需求也会随之增长。让我们来探讨一下每个平台在垂直扩展、分片和大规模部署方面的表现。

水平与垂直扩展

当你需要支持表分区、分片（通过 Citus 或本机功能）和并行查询时，可以调用 PostgreSQL。 

MySQL 是一个不错的选择，可以通过读取副本和集群（MySQL Cluster）实现水平扩展。但是，多写入器的设置更为复杂。

云平台集成

PostgreSQL 和 MySQL 都通过 RDS、Cloud SQL 和 Aurora 等托管服务获得 AWS、Azure 和 GCP 的全力支持。

PostgreSQL 因其更好的标准合规性和与 Kubernetes 和 Terraform 等云原生工具的集成而脱颖而出。

MySQL 同样令人印象深刻，它为 LAMP 技术堆栈和轻量级云部署提供了强有力的支持。

AWS Aurora 的兼容性

PostgreSQL 和 MySQL 都与 AWS Aurora 兼容。

如果想要一个具有卓越性能输出的 PostgreSQL 托管版本，那么 Aurora PostgreSQL 就是你的不二之选。另一方面，Aurora MySQL 则以增强的复制功能和更快的故障转移速度而闻名。

两者都支持开箱即用的高可用性吗？是的，但有一个开发者“警告”：Aurora 中 PostgreSQL 的功能集更接近原始 PostgreSQL，而 MySQL 的 Aurora 更接近普通 MySQL。

框架集成和支持

如果说有一件事可以决定开发人员的效率，那就是框架兼容性。每个数据库与流行的后端框架和 ORM 的集成程度如何？

Django 数据库后端

PostgreSQL 拥有功能丰富的 SQL 方言，现在它已成为Django 的默认和推荐后端。

虽然 Django 确实很好地支持 MySQL，但两者之间的关系并非一帆风顺。某些 ORM 功能需要 PostgreSQL 才能完全运行。

Laravel 应用开发

Laravel 能够有效地支持这两种数据库。然而，PostgreSQL 略胜 MySQL 一筹，因为它拥有更强大的 ORM 功能，并且能够更好地处理 JSON 和约束。

流行的 ORM 兼容性

PostgreSQL 和 MySQL 均享有以下方面的充分、持续的支持：

• SQLAlchemy
• 休眠
  
  

也就是说，由于其更广泛的 SQL 兼容性，PostgreSQL 不断解锁更高级的 ORM 功能。

安全功能比较

• SQLAlchemy

• Hibernate

• Doctrine

• Prisma

但是，无论一个数据库管理系统（DBMS）多么强大、功能多么丰富，如果它轻视安全性，那么用户肯定会在第一次尝试时就放弃它。让我们来探讨一下 MySQL 和 PostgreSQL 在这方面的区别。

身份验证方法

• PostgreSQL：
   密码、LDAP、Kerberos、SSL 证书等
• MySQL：密码、LDAP（企业）、PAM

用户角色系统

• PostgreSQL： 细粒度角色、行级安全性、策略实施
• MySQL：模式/表级别的用户权限

加密功能

• PostgreSQL： 传输中的 SSL；通过文件系统或扩展进行静态传输
• MySQL： 传输中 SSL；静态加密仅在企业版中可用

迁移和兼容性

考虑近期迁移吗？本节将带你了解具体细节，包括如何将流行的 MySQL 迁移到 PostgreSQL。

从 MySQL 迁移到 PostgreSQL

你或许可能想要迈出这一步的原因完全合理：与 MySQL 相比，PostgreSQL 享有卓越的数据完整性、更大的可扩展性和高级 SQL 支持。

这是否意味着您将不会面临任何迁移挑战？当然，你会遇到，包括数据类型的差异、索引行为的变化以及不同的 SQL 方言。 

好消息是，如果你使用这些迁移工具，这些问题都是可以克服的：

• 加载器
• AWS 数据库迁移服务 (DMS)
• Ora2Pg

你的团队可能也需要投入一些人力。转换存储过程和触发器以及调整应用程序逻辑确实需要一些脑力劳动。

总而言之，PostgreSQL 的严格类型系统要求在迁移过程中进行仔细的验证。因此，要以极其细致的态度对待整个阶段。

与其他数据库集成

虽然 PostgreSQL 和 MySQL 都支持跨数据库功能，但它们的处理方式却大不相同。

PostgreSQL 使用一组独特的库（称为外部数据包装器 (FDW)）来方便连接到其他 PostgreSQL 实例。在某些情况下，它甚至可以连接到 MongoDB 或 Redis 等非关系型数据库。FDW 在实现高级查询下推方面表现出色，从而将操作卸载到远程系统。

相比之下，MySQL 包含联合表。表可以利用此本地化功能来引用远程 MySQL 实例中的表。它是否像 PostgreSQL 的 FDW 生态系统一样强大或积极开发？这不完全是。

选择 PostgreSQL 还是 MySQL

MySQL 和 PostgreSQL 之间的选择并不像乍看起来那么简单。最终，它取决于你的工作负载、数据复杂性和合规性需求。

用例和场景

在以下情况下选择 PostgreSQL：

• 处理需要严格遵守 ACID 和强数据一致性的应用程序
• 寻求对复杂查询、JSON/NoSQL 数据或地理空间分析的支持
• 使用包含 Django、Rails 或高级分析工具的技术栈，可以极大地受益于 PostgreSQL 的可扩展性

在以下情况下选择 MySQL：

• 优先考虑 CMS 平台、博客或电子商务等读取密集型工作负载的性能
• 需要简单且快速的设置，例如开发 MVP 或启动初创公司时
• 使用严重依赖 LAMP 堆栈或广泛使用的工具（如 WordPress）的应用程序

行业特定要求

尽管存在着一些缺陷，PostgreSQL 和 MySQL 多年来仍在各行各业蓬勃发展。其合规性、可扩展性和性能优势使其在以下领域发挥了不可估量的价值：

• 金融科技和保险业经常选择 PostgreSQL，因为它具有严格的一致性和审计功能。
• 医疗保健组织非常重视 PostgreSQL 对其部分加密和行级安全性 (RLS) 的支持。
• 政府和公共部门组织需要开源透明度、细粒度的访问控制和强大的灾难恢复——这正是 PostgreSQL 蓬勃发展的领域。
• 媒体和零售型公司更喜欢 MySQL，因为它速度快、规模简单。
• 初创公司和 SaaS 公司需要针对 MVP 或事务应用程序的快速、简单和轻量级解决方案，而这些都是 MySQL 的强项。
• 营销和广告技术团队倾向于使用 MySQL，因为它能够很好地支持高流量仪表板和实时报告系统。

PostgreSQL 和 MySQL 系统各有千秋，功能复杂势必带来性能的损失，功能的简洁一定会提升速度。

相信本文内容已经告诉大家如何做数据库的选型了。欢迎你点赞、转发和评论～

作者：行动中的大雄